Yleinen kaava
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Yhteenveto
Voit poimia vain yksilöllisiä arvoja luettelosta tai sarakkeesta käyttämällä taulukkoa, joka perustuu INDEX, MATCH ja COUNTIF. Esitetyssä esimerkissä D5: n kaava, kopioitu alaspäin, on:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
missä "lista" on nimetty alue B5: B11.
Huomaa: tämä on matriisikaava, ja se on syötettävä painamalla ohjainta + vaihto + enter.
Selitys
Tämän kaavan ydin on perushaku, jossa on INDEX:
=INDEX(list,row)
Toisin sanoen, anna luettelolle INDEX luettelo ja rivinumero, ja INDEX hakee arvon, joka lisätään ainutlaatuiseen luetteloon.
Kova työ on ROW-numeron selvittäminen, jotta saadaan INDEX, jotta saisimme vain ainutlaatuiset arvot. Tämä tehdään MATCH ja COUNTIF, ja pää temppu on täällä:
COUNTIF($D$4:D4,list)
Tässä COUNTIF laskee, kuinka monta kertaa ainutlaatuisessa luettelossa olevat kohteet näkyvät pääluettelossa, käyttämällä alueen laajentavaa viitettä $ D $ 4: D4.
Laajentuva viite on absoluuttinen toisella puolella, suhteessa toisella. Tässä tapauksessa, kun kaava kopioidaan alaspäin, viite laajenee sisällyttämällä useampia rivejä ainutlaatuiseen luetteloon.
Huomaa, että viite alkaa D4: stä, yksi rivi ensimmäisen yksilöllisen merkinnän yläpuolella, ainutlaatuisessa luettelossa. Tämä on tarkoituksellista - haluamme laskea kohteet * jo * ainutlaatuiseen luetteloon, emmekä voi sisällyttää nykyistä solua luomatta pyöreää viittausta. Joten aloitamme yllä olevalla rivillä.
Tärkeää: Varmista, että yksilöllisen luettelon otsikko ei näy pääluettelossa.
COUNTIF-kriteereissä käytämme itse pääluetteloa. Kun annetaan useita ehtoja, COUNTIF palauttaa useita tuloksia taulukossa. Jokaisella uudella rivillä meillä on erilainen tällainen taulukko:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Huomaa: COUNTIF käsittelee useita ehtoja "OR" -suhteella (eli COUNTIF (alue, ("punainen", "sininen", "vihreä")) laskee punaisen, sinisen tai vihreän.
Nyt meillä on taulukot, jotka meidän on löydettävä sijainnit (rivinumero). Tätä varten käytämme tarkalle haulle määritettyä MATCHia nolla-arvojen löytämiseen. Jos laitamme COUNTIF: n luomat taulukot yllä MATCH: iin, saat tämän:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH etsii kohteet etsimällä nollamäärää (eli etsimällä kohteita, jotka eivät vielä näy ainutlaatuisessa luettelossa). Tämä toimii, koska MATCH palauttaa aina ensimmäisen ottelun, kun kopioita on.
Finally, the positions are fed into INDEX as row numbers, and INDEX returns the name at that position.
Non array version with LOOKUP
You can build a non-array formula to extract unique items using the flexible LOOKUP function:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
The formula construction is similar to the INDEX MATCH formula above, but LOOKUP can handle the array operation natively.
- COUNTIF returns counts of each value from "list" in the expanding range $D$4:D4
- Comparing to zero creates an array of TRUE and FALSE values
- The number 1 is divided by the array, creating an array of 1s and #DIV/0 errors
- This array becomes the the lookup_vector inside LOOKUP
- The lookup value of 2 is larger than any values in the lookup_vector
- LOOKUP will match the last non-error value in the lookup array
- LOOKUP returns the corresponding value in result_vector, the named range "list"
Pura vain kerran näkyvät kohteet
Yllä olevaa LOOKUP-kaavaa on helppo laajentaa totuuslogiikalla. Voit purkaa luettelon ainutlaatuisista kohteista, jotka näkyvät vain kerran lähdetiedoissa, käyttämällä seuraavaa kaavaa:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Ainoa lisäys on toinen COUNTIF-lauseke:
COUNTIF(list,list)=1
Tässä COUNTIF palauttaa joukon tuotemääriä seuraavasti:
(2;2;2;2;2;1;2)
joita verrataan arvoon 1, jolloin saadaan TOSI / EPÄTOSI-arvot:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
jotka toimivat "suodattimena" rajoittaakseen lähdön kohteisiin, jotka esiintyvät vain kerran lähdetiedoissa.
