Splošna formula
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Povzetek
Če želite šteti celice, ki niso enake številnim stvarem, lahko uporabite formulo, ki temelji na funkcijah MATCH, ISNA in SUMPRODUCT. V prikazanem primeru je formula v celici F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
kjer je "podatki" imenovani obseg B5: B16, "izključi" pa imenovani obseg D5: D7.
Pojasnilo
Najprej malo konteksta. Običajno, če imate le nekaj stvari, ki jih ne želite šteti, lahko uporabite COUNTIFS, kot je ta:
=COUNTIFS(range,"apple",range,"orange")
Ampak to se ne prilega zelo dobro, če imate seznam številnih stvari, ker boste morali za vsako stvar, ki je ne želite šteti, dodati dodaten par obsega / meril. Veliko lažje bi bilo sestaviti seznam in kot del meril predati sklic na ta seznam. Točno to počne formula na tej strani.
V osnovi ta formula uporablja funkcijo MATCH za iskanje celic, ki niso enake "a", "b" ali "c" s tem izrazom:
MATCH(data,exclude,0)
Upoštevajte, da sta iskalna vrednost in iskalno polje »obrnjena« v običajni konfiguraciji - vse vrednosti iz imenovanega obsega »podatki« podajamo kot iskalne vrednosti in damo vse vrednosti, ki jih želimo izključiti v imenovanem obsegu, »izključi«. Ker damo MATCH več kot eno iskalno vrednost, dobimo več kot en rezultat v matriki, kot je ta:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
V bistvu nam MATCH daje položaj ujemajočih se vrednosti kot število in za vse druge vrednosti vrne # N / A.
Rezultati # N / A so tisti, ki nas zanimajo, saj predstavljajo vrednosti, ki niso enake "a", "b" ali "c". V skladu s tem uporabljamo ISNA, da prisilimo te vrednosti na TRUE in številke na FALSE:
ISNA(MATCH(data,exclude,0)
Nato z dvojnim negativom prisilimo TRUE na 1 in FALSE na nič. Nastala matrika znotraj SUMPRODUCT izgleda takole:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Z samo eno matriko, ki jo je treba obdelati, SUMPRODUCT sešteje in vrne končni rezultat, 4.
Opomba: Z uporabo SUMPRODUCT namesto SUM se izognete uporabi kontrolnika + shift + enter.
Štetje minus ujemanje
Drug način za štetje celic, ki niso enake nobeni od več stvari, je štetje vseh vrednosti in odštevanje ujemanj. To lahko storite s takšno formulo:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Tu COUNTA vrne število vseh nepraznih celic. Funkcija COUNTIF glede na imenovani obseg "izključi" vrne tri štetja, po eno za vsak element na seznamu. SUMPRODUCT sešteje seštevek in to število se odšteje od števila vseh nepraznih celic. Končni rezultat je število celic, ki niso enake vrednosti v "izključi".
Dobesedno vsebuje logiko tipa
Formula na tej strani šteje z logiko "enako". Če morate prešteti celice, ki ne vsebujejo veliko nizov, pri čemer vsebuje pomeni, da se niz lahko pojavi kjer koli v celici, boste potrebovali bolj zapleteno formulo.
