Generička formula
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Sažetak
Da biste brojali stanice koje nisu jednake ni od čega, možete upotrijebiti formulu koja se temelji na funkcijama MATCH, ISNA i SUMPRODUCT. U prikazanom primjeru formula u ćeliji F5 je:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
gdje je "podatak" imenovani raspon B5: B16, a "izuzeti" imenovani raspon D5: D7.
Obrazloženje
Prvo, malo konteksta. Uobičajeno, ako imate samo nekoliko stvari koje ne želite brojati, možete koristiti COUNTIFS poput ovog:
=COUNTIFS(range,"apple",range,"orange")
Ali ovo se ne prilagođava dobro ako imate popis mnogih stvari, jer ćete morati dodati dodatni raspon / kriterij za svaku stvar koju ne želite računati. Bilo bi puno lakše sastaviti popis i proslijediti referencu na ovaj popis kao dio kriterija. Upravo to čini formula na ovoj stranici.
U osnovi, ova formula koristi funkciju MATCH za pronalaženje stanica koje nisu jednake "a", "b" ili "c" s ovim izrazom:
MATCH(data,exclude,0)
Imajte na umu da su vrijednost pretraživanja i polje pretraživanja "preokrenute" u odnosu na normalnu konfiguraciju - mi pružamo sve vrijednosti iz imenovanog raspona "podaci" kao vrijednosti pretraživanja i dajemo sve vrijednosti koje želimo izuzeti u navedenom rasponu "izuzmi". Budući da MATCH-u dajemo više od jedne vrijednosti pretraživanja, u nizu poput ovog dobivamo više rezultata:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
U osnovi, MATCH nam daje položaj odgovarajućih vrijednosti kao broj, a za sve ostale vrijednosti vraća # N / A.
# N / A rezultati su oni koji nas zanimaju jer predstavljaju vrijednosti koje nisu jednake "a", "b" ili "c". U skladu s tim, koristimo ISNA da prisilimo ove vrijednosti na TRUE, a brojeve na FALSE:
ISNA(MATCH(data,exclude,0)
Tada koristimo dvostruku negativu da prisilimo TRUE na 1 i FALSE na nulu. Dobiveni niz unutar SUMPRODUCT izgleda ovako:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Sa samo jednim nizom za obradu, SUMPRODUCT zbraja i vraća konačni rezultat, 4.
Napomena: Korištenje SUMPRODUCT umjesto SUM izbjegava potrebu za korištenjem control + shift + enter.
Brojanje minus podudaranje
Drugi način za brojanje stanica koje nisu jednake ni jednoj od nekoliko stvari jest brojanje svih vrijednosti i oduzimanje podudaranja. To možete učiniti s formulom poput ove:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Ovdje COUNTA vraća broj svih praznih ćelija. Funkcija COUNTIF, s obzirom na imenovani raspon "izuzeti", vratit će tri brojača, po jedan za svaku stavku na popisu. SUMPRODUCT zbraja zbroj i taj se broj oduzima od broja svih praznih ćelija. Konačni rezultat je broj ćelija koje nisu jednake vrijednosti u "izuzeti".
Doslovno sadrži logiku tipa
Formula na ovoj stranici broji se logikom "jednako". Ako trebate izbrojati stanice koje ne sadrže mnogo žica, a gdje sadrži znači da se niz može pojaviti bilo gdje u ćeliji, trebat će vam složenija formula.
