Generička formula
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Sažetak
Da biste brojali stanice koje ne sadrže mnogo različitih nizova, možete koristiti prilično složenu formulu koja se temelji na funkciji MMULT. U prikazanom primjeru formula u F5 je:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
gdje je "podatak" imenovani raspon B5: B14, a "izuzeti" imenovani raspon D5: D7.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter
Predgovor
Ova je formula komplicirana zahtjevom "sadrži". Ako vam je samo potrebna formula za brojanje stanica koje * nisu jednake * mnogim stvarima, možete koristiti jednostavniju formulu koja se temelji na funkciji MATCH. Također, ako imate ograničen broj žica koje želite izuzeti, možete koristiti funkciju COUNTIFS poput ove:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Međutim, s ovim pristupom morate unijeti novi par argumenata raspona / kriterija za svaki niz koji želite izuzeti. Suprotno tome, dolje objašnjena formula može obraditi velik broj nizova kako bi se izuzeli uneseni izravno na radnom listu.
Napokon, ova je formula složena. Javite mi ako imate jednostavniju formulu za predložiti :)
Obrazloženje
Jezgra ove formule je BROJ i TRAŽENJE:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Ovdje transportiramo stavke u imenovanom rasponu "izuzmi", a zatim rezultat šaljemo u SEARCH kao "pronađi tekst", a "data" kao "unutar teksta". Funkcija SEARCH vraća 2d niz TRUE i FALSE vrijednosti, 10 redaka po 3 stupca, ovako:
(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 svaku vrijednost u "podacima" imamo 3 rezultata (jedan po nizu za pretraživanje) koji su ili #Vrijednost pogreške ili brojevi. Brojevi predstavljaju položaj pronađenog tekstnog niza, a pogreške tekstualne nizove koji nisu pronađeni. Inače, funkcija TRANSPOSE potrebna je za generiranje niza 10 x 3 cjelovitih rezultata.
Ovaj se niz unosi u ISNUMBER da bi se dobile TRUE FALSE vrijednosti, koje pretvaramo u 1s i 0s s dvostrukim negativnim (-) operatorom. Rezultat je niz poput ovog:
(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)
koji ulazi u funkciju MMULT kao niz1. Slijedeći pravila množenja matrice, broj stupaca u polju1 mora biti jednak broju redaka u polju2. Za generiranje array2 koristimo funkciju ROW ovako:
ROW(exclude)^0
To daje niz od 1s, 3 retka po 1 stupac:
(1;1;1)
koji ide u MMULT kao array2 . Nakon množenja niza imamo niz dimenzioniran tako da odgovara izvornim podacima:
(2;1;0;0;1;1;0;0;0;2)
U ovom nizu bilo koji broj koji nije nula predstavlja vrijednost u kojoj je pronađen barem jedan od izuzetih nizova. Nula označavaju da nisu pronađeni izuzeti nizovi. Da bismo prisilili sve vrijednosti koje nisu nula na 1, koristimo veće od nule:
(2;1;0;0;1;1;0;0;0;2)>0
koji stvara još jedan niz ili TRUE i FALSE vrijednosti:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Krajnji nam je cilj brojati samo tekstualne vrijednosti u kojima nisu pronađene izuzete žice, pa ih moramo preokrenuti. To radimo oduzimanjem niza od 1. Ovo je primjer booleove logike. Matematička operacija automatski prisiljava vrijednosti TRUE i FALSE na 1s i 0s, a napokon imamo niz za povratak u funkciju SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funkcija SUM daje konačni rezultat 5.
