Excel

Laske yksilölliset arvot kriteereillä

Count Unique Values With Criteria

Excel-kaava: Laske yksilölliset arvot kriteereillä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 AINUTLAATUINEN ja SUODATTAA . 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 , saatavana vain Excel 365 . Vanhemmalla Excel-versiolla voit käyttää monimutkaisemmat vaihtoehtoiset kaavat .



Selitys

Ytimessä tämä kaava käyttää UNIQUE-funktiota ainutlaatuisten arvojen purkamiseen, ja FILTER-funktio käyttää ehtoja.

missä on vierityslukonäppäin

Työskentelemällä sisältä ulos, SUODATIN-toiminto käytetään kriteerien soveltamiseen ja vain Omega-projektiin liittyvien nimien purkamiseen:



 
 FILTER (B6:B15,C6:C15=H6) // Omega names only

Suodattimen tulos on taulukko kuten tämä:

 
{'Jim''Jim''Carl''Sue''Carl'}

Seuraavaksi UNIQUE-toiminto käytetään kaksoiskappaleiden poistamiseen:

 
 UNIQUE ({'Jim''Jim''Carl''Sue''Carl'})

mikä saa aikaan uuden tällaisen 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- ja SUM-toiminnoilla. Jotta asiat olisivat selkeät, kirjoitamme kaava ensin uudelleen sisällyttämään ainutlaatuisen luettelon:

 
= SUM (--( LEN ({'Jim''Carl''Sue'})>0))

LEN-toiminto saa luettelon jokaisen kohteen pituuden ja palauttaa matriisipituuden:

 
 LEN ({'Jim''Carl''Sue'}) // returns {343}

Seuraavaksi tarkistamme, ovatko pituudet suurempia kuin nolla:

 
 LEN ({343)>0 // returns {TRUETRUETRUE}

Ja käytä a kaksinkertainen negatiivinen pakottaa TOSI- ja EPÄTOSI-arvot 1- ja 0-arvoiksi:

 
--({TRUETRUETRUE}) // returns {111}

Lopuksi lisätään tulokset SUM-toiminto :

kuinka valintamerkki kirjoitetaan Excelissä
 
= SUM ({111}) // 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 välittömästi uudelleen, 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 käytöstä looginen logiikka soveltaa useampaa kuin yhtä ehtoa. Lähestymistapa on selitetään tarkemmin täällä .

kuinka tehdä 10 ^ Excelissä

Katso lisätietoja tästä koulutusvideosta: Kuinka suodattaa useilla ehdoilla .

COUNTA

On mahdollista kirjoittaa yksinkertaisempi kaava, joka vastaa COUNTA-toiminto . 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ää a monimutkaisempi kaava . Yleisempi keskustelu dynaamisista matriisivaihtoehdoista: Vaihtoehdot dynaamisille matriisikaavoille .

Dynaamiset taulukot ovat saatavilla Office 365 vain. Kirjailija Dave Bruns


^