Excel

Laske yksilölliset arvot kriteereillä

Count Unique Values With Criteria

Excel -kaava: Laske yksilölliset arvot kriteereilläYleinen kaava | _+_ | Yhteenveto

Voit laskea yksilöllisiä arvoja yhdellä tai useammalla ehdolla käyttämällä kaavaa, joka perustuu AINUTLAATUINEN ja SUODATTAA . Esitetyssä esimerkissä H7: n kaava on:





= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>0))

joka palauttaa 3, koska B6: ssa on kolme ainutlaatuista nimeä: B15 liittyy Omega -projektiin.

Huomaa: tämä kaava vaatii Dynaamiset matriisikaavat , saatavana vain Excel 365 . Voit käyttää Excelin vanhempaa versiota monimutkaisempia vaihtoehtoisia kaavoja .





Selitys

Pohjimmiltaan tämä kaava käyttää UNIQUE -funktiota ainutlaatuisten arvojen poimimiseen ja FILTER -funktio käyttää ehtoja.

missä on vierityslukonäppäin

Toimii sisältä ulospäin, FILTER -toiminto käytetään kriteerien soveltamiseen ja vain Omega -projektiin liittyvien nimien poimimiseen:



 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,C6:C15=H6,'')))>0))

FILTERin tulos on matriisi kuten tämä:

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

Seuraavaksi, UNIQUE -toiminto käytetään kopioiden poistamiseen:

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

tuloksena uusi tällainen taulukko:

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

Tällä hetkellä meillä on ainutlaatuinen luettelo nimistä, jotka liittyvät Omegaan, ja meidän on vain laskettava ne. Alla selitetyistä syistä teemme tämän LEN- ja SUM -toiminnolla. Jotta asiat olisivat selkeitä, kirjoitamme ensin kaavan uudelleen sisältämään ainutlaatuisen luettelon:

 
{'Jim''Carl''Sue'} // after UNIQUE

The LEN -toiminto hakee luettelon jokaisen kohteen pituuden ja palauttaa pituuden matriisin:

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

Seuraavaksi tarkistamme, ovatko pituudet suurempia kuin nolla:

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

Ja käytä a kaksinkertainen negatiivinen pakottaaksesi TOSI- ja EPÄTOSI -arvot arvoihin 1s ja 0s:

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

Lopuksi laskemme tulokset yhteen SUM -toiminto :

kuinka valintamerkki kirjoitetaan Excelissä
 
--({TRUETRUETRUE}) // returns {111}

Tämä taulukko toimitetaan suoraan COUNTA -funktiolle, joka palauttaa lopullisen laskelman:

 
= SUM ({111}) // returns 3

Huomaa, että koska tarkistamme jokaisen UNIQUE: n palauttaman tuotteen pituuden, tyhjät tai tyhjät solut, jotka täyttävät ehdot, jätetään huomiotta. Tämä kaava on dynaaminen ja laskee uudelleen heti, jos lähdetietoja muutetaan.

Laske ainutlaatuinen useilla kriteereillä

Jos haluat laskea ainutlaatuisia arvoja useiden ehtojen perusteella, voit laajentaa FILTERin sisällyttämislogiikkaa. Esimerkiksi, jos haluat laskea Omega -projektin ainutlaatuiset nimet vain kesäkuussa, käytä:

 
= COUNTA ({'Jim''Carl''Sue'}) // returns 3

Tämä on esimerkki käytöstä boolen logiikka soveltaa useampaa kuin yhtä ehtoa. Lähestymistapa on selitetty tarkemmin täällä .

kuinka tehdä 10 ^ Excelissä

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

COUNTA

On mahdollista kirjoittaa yksinkertaisempi kaava, joka vastaa kohtaan COUNTA -toiminto . Tärkeä varoitus on kuitenkin, että COUNTA palauttaa arvon 1, jos vastaavia arvoja ei ole. Tämä johtuu siitä, että SUODATIN -funktio palauttaa virheen, kun mikään data ei täytä ehtoja, ja COUNTA -funktio laskee tämän virheen. COUNTA -peruskaava näyttää tältä:

 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,(C6:C15=H6)*(D6:D15='june'))))>0))

Tämä kaava palauttaa jälleen arvon 1, jos 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 matriiseja

Jos käytät vanhempaa Excel -versiota ilman dynaamisten matriisien tukea, voit käyttää a monimutkaisempi kaava . Yleisempää keskustelua dynaamisista matriisivaihtoehdoista on osoitteessa: Vaihtoehtoja dynaamisille matriisikaavoille .

Dynaamiset matriisikaavat ovat saatavilla Office 365 vain. Kirjailija Dave Bruns


^