![](https://cdn.wiki-base.com/9346687/excel_formula_count_cells_that_do_not_contain_many_strings__2.png.webp)
Üldine valem
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Kokkuvõte
Lahtrite loendamiseks, mis ei sisalda palju erinevaid stringe, võite kasutada üsna keerukat valemit, mis põhineb funktsioonil MMULT. Näidatud näites on valem F5-s järgmine:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
kus "andmed" on nimega vahemik B5: B14 ja "välista" on nimega vahemik D5: D7.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter
Eessõna
Selle valemi muudab keerukaks nõue "sisaldab". Kui vajate lihtsalt valemit lahtrite loendamiseks, mis ei võrdu paljude asjadega, võite kasutada funktsiooni MATCH põhjal sirgemat valemit. Kui teil on piiratud arv stringe, mida välistada, saate funktsiooni COUNTIFS kasutada järgmiselt:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Selle lähenemisviisi korral peate aga iga välistatava stringi jaoks sisestama uue paari vahemiku / kriteeriumide argumente. Seevastu allpool selgitatud valemiga saab töödelda suure hulga stringidega, et välistada otse töölehele sisestatud stringid.
Lõpuks on see valem keeruline. Andke mulle teada, kui teil on lihtsam valem pakkuda :)
Selgitus
Selle valemi tuum on ISNUMBER ja OTSI:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Siinkohal võtame üle nimega vahemikus olevad üksused "välista", seejärel edastame tulemuse otsingule kui otsingu tekst, kus "andmed" on "teksti sees". Funktsioon SEARCH tagastab väärtuste 2D väärtused TÕENE ja VALE, 10 rida 3 veeru kaupa järgmiselt:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Iga "data" väärtuse kohta on meil 3 tulemust (üks otsingustringi kohta), mis on kas #VALUE tõrkeid või numbreid. Numbrid tähistavad leitud tekstistringi positsiooni ja vead tähistavad tekstistringi, mida ei leitud. Muide, funktsiooni TRANSPOSE on vaja 10 x 3 massiivi täielike tulemuste genereerimiseks.
See massiiv sisestatakse ISNUMBER-i, et saada TÕELISED VÄÄR-väärtused, mille teisendame kahekordse negatiivse (-) operaatoriga 1-ks ja 0-ks. Tulemuseks on selline massiiv:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
mis läheb funktsiooni MMULT massiivina1. Maatriksi korrutamise reegleid järgides peab massiivi 1 veergude arv olema võrdne massiivi 2 ridade arvuga. Massiivi2 genereerimiseks kasutame funktsiooni ROW niimoodi:
ROW(exclude)^0
See annab massiivi 1s, 3 rida 1 veeru kaupa:
(1;1;1)
mis läheb MMULT-i massiivina2 . Pärast massiivide korrutamist on meil massiiv mõõtmetega, et need vastaksid algandmetele:
(2;1;0;0;1;1;0;0;0;2)
Selles massiivis tähistab mis tahes nullist erinev arv väärtust, kus on leitud vähemalt üks välistatud stringidest. Nullid näitavad, et välistatud stringi ei leitud. Kõigi nulliväliste väärtuste sundimiseks väärtuseks 1 kasutame suuremat kui null:
(2;1;0;0;1;1;0;0;0;2)>0
mis loob veel ühe massiivi või TRUE ja FALSE väärtused:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Meie lõplik eesmärk on loendada ainult tekstiväärtusi, kus välistatud stringe ei leitud, seega peame need väärtused ümber pöörama. Me teeme selle, lahutades massiivi väärtusest 1. See on tõeväärtuse loogika näide. Matemaatikaoperatsioon sunnib TÕENE ja VALE väärtused automaatselt väärtusele 1 ja 0 ning lõpuks on meil funktsioon SUM-i naasmiseks massiiv:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funktsioon SUM tagastab lõpptulemuse 5.