Yleinen kaava
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Yhteenveto
Voit laskea yksilölliset arvot yhdellä tai useammalla ehdolla käyttämällä kaavaa, joka perustuu UNIQUE ja FILTER. Esitetyssä esimerkissä H7: n kaava on:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
joka palauttaa 3, koska B6: ssä on kolme ainutlaatuista nimeä: B15, joka liittyy Omega-projektiin.
Huomaa: tämä kaava vaatii dynaamiset taulukot, jotka ovat käytettävissä vain Excel 365: ssä. Excelin vanhemmassa versiossa voit käyttää monimutkaisempia vaihtoehtoisia kaavoja.
Selitys
Ytimessä tämä kaava käyttää UNIQUE-funktiota ainutlaatuisten arvojen purkamiseen, ja FILTER-funktio käyttää ehtoja.
Työskentely sisältä ulospäin, SUODATIN-toimintoa käytetään kriteerien soveltamiseen ja vain "Omega" -projektiin liittyvien nimien purkamiseen:
FILTER(B6:B15,C6:C15=H6) // Omega names only
FILTERin tulos on tällainen taulukko:
("Jim";"Jim";"Carl";"Sue";"Carl")
Seuraavaksi UNIQUE-toimintoa käytetään kaksoiskappaleiden poistamiseen:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
mikä saa aikaan uudenlaisen taulukon:
("Jim";"Carl";"Sue") // after UNIQUE
Tässä vaiheessa meillä on ainutlaatuinen luettelo Omegaan liittyvistä nimistä, ja meidän on vain laskettava ne. Alla selitetyistä syistä teemme tämän LEN-toiminnolla ja SUM-toiminnolla. Jotta asiat olisivat selkeät, kirjoitamme kaavan ensin sisällyttämään ainutlaatuisen luettelon:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
LEN-funktio saa luettelon jokaisen kohteen pituuden ja palauttaa matriisipituuden:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Seuraavaksi tarkistamme, ovatko pituudet suurempia kuin nolla:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Käytä kaksoisnegatiivia pakottaaksesi TOSI- ja EPÄTOSI-arvot arvoksi 1s ja 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Lopuksi lisätään tulokset SUM-funktiolla:
=SUM((1;1;1)) // returns 3
Tämä taulukko toimitetaan suoraan COUNTA-funktiolle, joka palauttaa lopullisen määrän:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Huomaa, että koska tarkistamme jokaisen UNIQUE palauttaman kohteen pituuden, tyhjät tai tyhjät solut, jotka täyttävät ehdot, jätetään huomioimatta. Tämä kaava on dynaaminen ja laskee uudelleen heti, jos lähdetietoja muutetaan.
Laske ainutlaatuinen useilla ehdoilla
Jos haluat laskea yksilölliset arvot useiden ehtojen perusteella, voit laajentaa "include" -logiikkaa FILTER-sisälle. Esimerkiksi laskeaksesi yksilöllisiä nimiä Omega-projektille vain kesäkuussa, käytä:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Tämä on esimerkki loogisen logiikan käyttämisestä useamman kuin yhden ehdon käyttämiseen. Lähestymistapa on selitetty tarkemmin täällä.
Katso lisätietoja tästä koulutusvideosta: Kuinka suodattaa useilla ehdoilla.
COUNTA
On mahdollista kirjoittaa yksinkertaisempi kaava, joka vastaa COUNTA-funktioon. Tärkeä huomautus on kuitenkin, että COUNTA palauttaa arvon 1, kun vastaavia arvoja ei ole. Tämä johtuu siitä, että FILTER-toiminto palauttaa virheen, kun mikään tieto ei täytä ehtoja, ja COUNTA-toiminto laskee tämän virheen. COUNTA-peruskaava näyttää tältä:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Jälleen tämä kaava palauttaa arvon 1, kun vastaavia tietoja ei ole. Se sisältää myös tyhjät solut, jotka täyttävät ehdot. LEN- ja SUM-pohjainen kaava on parempi vaihtoehto.
Ei dynaamisia taulukoita
Jos käytät vanhempaa Excel-versiota ilman dynaamista matriisitukea, voit käyttää monimutkaisempaa kaavaa. Yleisempi keskustelu dynaamisista matriisivaihtoehdoista on kohdassa Vaihtoehdot dynaamisille matriisikaavoille.
