Vispārēja formula
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Kopsavilkums
Lai uzskaitītu šūnas, kurās nav daudz dažādu virkņu, varat izmantot diezgan sarežģītu formulu, kuras pamatā ir funkcija MMULT. Parādītajā piemērā formula F5 ir:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
kur "dati" ir nosauktais diapazons B5: B14 un "izslēgt" ir nosauktais diapazons D5: D7.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter
Priekšvārds
Šo formulu sarežģī prasība "satur". Ja jums vienkārši nepieciešama formula, lai saskaitītu šūnas, kas * nav vienādas ar daudzām lietām, varat izmantot vienkāršāku formulu, kuras pamatā ir funkcija MATCH. Turklāt, ja jums ir ierobežots virkņu skaits, kuras izslēgt, varat izmantot funkciju COUNTIFS šādi:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Tomēr, izmantojot šo pieeju, jums jāievada jauns diapazona / kritēriju argumentu pāris katrai virknei, kuru izslēgt. Turpretī zemāk paskaidrotā formula var apstrādāt lielu virkņu skaitu, lai izslēgtu tieši ievadītas darblapā.
Visbeidzot, šī formula ir sarežģīta. Informējiet mani, ja jums ir vienkāršāka formula, ko ieteikt :)
Paskaidrojums
Šīs formulas kodols ir ISNUMBER un SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Šeit mēs transponējam vienumus nosauktajā diapazonā "izslēgt", pēc tam rezultātu padodam SEARCH kā "atrast tekstu", ar "dati" kā "teksta ietvaros". Funkcija MEKLĒŠANA atgriež TRUE un FALSE vērtību 2d masīvu 10 rindas pa 3 kolonnām šādi:
(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)
Par katru vērtību “datos” mums ir 3 rezultāti (viens katrā meklēšanas virknē), kas ir vai nu #VALUE kļūdas, vai skaitļi. Skaitļi attēlo atrastās teksta virknes pozīciju, un kļūdas - neatrastās teksta virknes. Starp citu, TRANSPOSE funkcija ir nepieciešama, lai ģenerētu 10 x 3 pilnīgu rezultātu masīvu.
Šis masīvs tiek ievadīts ISNUMBER, lai iegūtu TRUE FALSE vērtības, kuras mēs pārveidojam par 1s un 0s ar dubultnegatīvu (-) operatoru. Rezultāts ir šāds masīvs:
(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)
kas MMULT funkcijā nonāk kā masīvs1. Ievērojot matricas reizināšanas noteikumus, masīva1 kolonnu skaitam jābūt vienādam ar masīva2 rindu skaitu. Lai ģenerētu masīvu2 , mēs izmantojam funkciju ROW šādi:
ROW(exclude)^0
Tādējādi tiek iegūts masīvs 1s, 3 rindas pa 1 kolonnu:
(1;1;1)
kas nonāk MMULT kā masīvs2 . Pēc masīva reizināšanas mums ir masīvs, kas pielāgots sākotnējiem datiem:
(2;1;0;0;1;1;0;0;0;2)
Šajā masīvā jebkurš skaitlis, kas nav nulle, apzīmē vērtību, kurā ir atrasta vismaz viena no izslēgtajām virknēm. Nulles norāda, ka netika atrastas izslēgtas virknes. Lai visas vērtības, kas nav nulles vērtības, piespiestu 1, mēs izmantojam lielākas par nulli:
(2;1;0;0;1;1;0;0;0;2)>0
kas rada vēl vienu masīvu vai TRUE un FALSE vērtības:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Mūsu galīgais mērķis ir uzskaitīt tikai teksta vērtības, kurās netika atrastas izslēgtas virknes, tāpēc mums šīs vērtības ir jāmaina. Mēs to darām, no 1. atņemot masīvu. Šis ir Būla loģikas piemērs. Matemātikas operācija TRUE un FALSE vērtības automātiski piespiež uz 1s un 0s, un mums beidzot ir masīvs, lai atgrieztos funkcijā SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funkcija SUM atgriež gala rezultātu 5.
