Yleinen kaava
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Yhteenveto
Voit laskea yksilölliset tekstiarvot alueella kriteereillä käyttämällä taulukkoa, joka perustuu FREQUENCY- ja MATCH-funktioihin. Esitetyssä esimerkissä G6: n kaava on:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
joka palauttaa 3, koska kolme eri ihmistä työskenteli Omega-projektissa.
Huomaa: tämä on matriisikaava ja se on syötettävä näppäimillä + + shift + enter.
Selitys
Tämä on monimutkainen kaava, joka käyttää FREQUENCY-funktiota laskemaan MATCH-funktiolla johdetut numeeriset arvot. Työskentelemällä sisältä ulospäin, MATCH-funktiota käytetään saamaan kunkin datassa näkyvän arvon sijainti:
MATCH(B5:B11,B5:B11,0)
MATCH: n tulos on tällainen taulukko:
(1;1;3;1;1;6;7)
Koska MATCH palauttaa aina ensimmäisen ottelun sijainnin, tiedoissa useammin kuin kerran näkyvät arvot palauttavat saman sijainnin. Esimerkiksi koska "Jim" näkyy 4 kertaa luettelossa, hän näkyy tässä taulukossa 4 kertaa numerona 1.
MATCH-funktion ulkopuolella IF-funktiota käytetään kriteerien soveltamiseen, johon tässä tapauksessa sisältyy testaus, jos projekti on "omega" (solusta G5):
IF(C5:C11=G5 // filter on "omega"
IF-toiminto toimii kuin suodatin, joka sallii MATCH: n arvojen kulkevan vain, jos ne liittyvät "omegaan". Tuloksena on tällainen taulukko:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Suodatettu taulukko toimitetaan suoraan FREQUENCY-funktiolle data_array- argumenttina. Seuraavaksi ROW-funktiota käytetään rakentamaan peräkkäinen numeroluettelo jokaiselle datan arvolle:
ROW(B3:B12)-ROW(B3)+1
Tämä luo tällaisen taulukon:
(1;2;3;4;5;6;7;8;9;10)
josta tulee suodattimen bin_array- argumentti. Tässä vaiheessa meillä on:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY palauttaa joukon numeroita, jotka ilmaisevat kunkin taulukon arvon laskemisen järjestyksessä. Kun numero on jo laskettu, FREQUENCY palauttaa nollan. FREQUENCY-tulos on tällainen taulukko:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Huomaa: FREQUENCY palauttaa aina matriisin, jossa on yksi kohde enemmän kuin bins_array .
Tässä vaiheessa voimme kirjoittaa kaavan uudelleen näin:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Tarkistamme arvot, jotka ovat suurempia kuin nolla, joka muuntaa luvut TOSI tai EPÄTOSI:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Sitten käytämme kaksoisnegatiivia pakottaaksemme loogiset arvot 1- ja 0-arvoiksi:
=SUM((1;0;0;0;0;1;1;0))
Lopuksi SUM-funktio palauttaa lopputulokseksi 3.
Huomaa: tämä on matriisikaava ja se on syötettävä painamalla Control + Vaihto + Enter.
Tyhjien solujen käsittely alueella
Jos jotkut alueen solut ovat tyhjiä, sinun on mukautettava kaava estämään tyhjiä soluja siirtymästä MATCH-toimintoon, mikä heittää virheen. Voit tehdä tämän lisäämällä toisen sisäkkäisen IF-toiminnon tarkistaaksesi tyhjät solut:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Kahdella kriteerillä
Jos sinulla on kaksi ehtoa, voit laajentaa kaavan logiikkaa lisäämällä toisen sisäkkäisen IF: n:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Missä c1 = kriteerit1, c2 = kriteerit2 ja vals = arvoalue.
Boolen logiikalla
Boolen logiikan avulla voit vähentää sisäkkäisiä IF: itä:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Tämä helpottaa lisäehtojen lisäämistä ja hallintaa.
