Splošna formula
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Povzetek
Če želite prešteti celice, ki ne vsebujejo veliko različnih nizov, lahko uporabite precej zapleteno formulo, ki temelji na funkciji MMULT. V prikazanem primeru je formula v F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
kjer je "podatki" imenovani obseg B5: B14, "izključi" pa imenovani obseg D5: D7.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter
Predgovor
Ta formula je zapletena zaradi zahteve "vsebuje". Če potrebujete samo formulo za štetje celic, ki * niso enake * marsičemu, lahko uporabite bolj preprosto formulo, ki temelji na funkciji MATCH. Če imate omejeno število nizov, ki jih želite izključiti, lahko uporabite funkcijo COUNTIFS, kot je ta:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Vendar pa morate pri tem pristopu vnesti nov par argumentov obsega / merila za vsak niz, ki ga želite izključiti. Nasprotno pa lahko obrazložitev spodaj obravnava veliko število nizov, ki jih je treba izključiti, vnesene neposredno na delovni list.
Končno je ta formula zapletena. Sporočite mi, če želite predlagati enostavnejšo formulo :)
Pojasnilo
Jedro te formule je ŠTEVILO in ISKANJE:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Tu prenesemo elemente iz imenovanega obsega "izključi", nato rezultat pošljemo v ISKANJE kot "poišči besedilo", pri "podatki" pa kot "znotraj besedila". Funkcija SEARCH vrne 2d matriko TRUE in FALSE vrednosti, 10 vrstic s 3 stolpci, kot je ta:
(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)
Za vsako vrednost v "podatkih" imamo 3 rezultate (po en na iskalni niz), ki so napake #VREDNOST ali številke. Številke predstavljajo položaj najdenega besedilnega niza, napake pa besedilne nize, ki jih ni mogoče najti. Mimogrede, funkcija TRANSPOSE je potrebna za ustvarjanje 10 x 3 matrike popolnih rezultatov.
Ta matrika se vnese v ISNUMBER, da dobi TRUE FALSE vrednosti, ki jih pretvorimo v 1s in 0s z dvojnim negativnim (-) operatorjem. Rezultat je matrika, kot je ta:
(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)
ki gre v funkcijo MMULT kot matrika1. V skladu s pravili množenja matric mora biti število stolpcev v matriki1 enako številu vrstic v matriki2. Za generiranje array2 uporabimo funkcijo ROW, kot je ta:
ROW(exclude)^0
Tako dobimo polje 1s, 3 vrstice z 1 stolpcem:
(1;1;1)
ki gre v MMULT kot array2 . Po množenju matrike imamo matriko, dimenzionirano tako, da se ujema z izvirnimi podatki:
(2;1;0;0;1;1;0;0;0;2)
V tej matriki poljubno število, ki ni nič, predstavlja vrednost, v kateri je bil najden vsaj eden od izključenih nizov. Ničlice pomenijo, da niso bili najdeni izključeni nizi. Za vsiljevanje vseh ničelnih vrednosti na 1 uporabimo večje od nič:
(2;1;0;0;1;1;0;0;0;2)>0
ki ustvari še eno matriko ali vrednosti TRUE in FALSE:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Naš končni cilj je šteti samo besedilne vrednosti, kjer ni bilo mogoče najti izključenih nizov, zato moramo te vrednosti obrniti. To naredimo tako, da matriko odštejemo od 1. To je primer logične logike. Matematična operacija samodejno prisili vrednosti TRUE in FALSE na 1s in 0s in končno imamo matriko za vrnitev v funkcijo SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funkcija SUM vrne končni rezultat 5.
