Excel

Pura ainutlaatuiset kohteet luettelosta

Extract Unique Items From List

Excel-kaava: Pura ainutlaatuiset kohteet luettelostaYleinen 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:D4,list),0))}

missä 'lista' on nimetty alue B5: B11.



Huomaa: tämä on taulukon kaava ja ne on syötettävä painamalla ohjainta + vaihto + enter.



ikä syntymäpäivästä excel
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 tärkein temppu on täällä:

 
 COUNTIF ($D:D4,list)

Täällä, COUNTIF laskee, kuinka monta kertaa ainutlaatuisessa luettelossa olevat kohteet näkyvät pääluettelossa käyttämällä laajeneva viite alueelle,$ D $ 4: D4.

Laajentuva viite on abliuotin toisella puolella, suhteellinen toisella. Tässä tapauksessa, kun kaava kopioidaan alaspäin, viite laajenee sisällyttämällä useampia rivejä ainutlaatuiseen luetteloon.



Huomaaviitealkaa D4: stä,yksi rivi edellä ensimmäinen ainutlaatuinen luettelo. 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 annettu useita ehtoja, COUNTIF palauttaa useita tuloksia hakutermillä taulukko . Jokaisella uudella rivillä meillä on erilainen tällainen taulukko:

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8

Huomaa: COUNTIF käsittelee useita ehtoja OR-suhteella (eli COUNTIF (alue, {'punainen', 'sininen', 'vihreä'}) laskee punaisen, sinisen tai vihreän.

kuinka poistaa muoto taulukoksi Excelissä

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,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},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.

Lopuksi paikat syötetään INDEXiin rivinumeroina, ja INDEX palauttaa nimen kyseiseen sijaintiin.

Ei matriisiversio, LOOKUP

Voit rakentaa ei-matriisikaavan ainutlaatuisten kohteiden purkamiseksi joustavan LOOKUP-toiminnon avulla:

 
= LOOKUP (2,1/( COUNTIF ($D:D4,list)=0),list)

Kaavan rakenne on samanlainen kuin yllä oleva INDEX MATCH -kaava, mutta LOOKUP pystyy käsittelemään matriisitoimintaa natiivisti.

  • COUNTIF palauttaa kunkin arvon luettelon luettelon arvot laajeneva valikoima $ D $ 4: D4
  • Verrattuna nollaan luodaan TOSI- ja EPÄTOSI-arvot
  • Numero 1 on jaettu matriisilla, jolloin muodostuu 1s ja # DIV / 0 -virheiden taulukko
  • Tästä taulukosta tulee haku_vektori LOOKUP: n sisällä
  • Haun arvo 2 on suurempi kuin mikään arvo hakuvektorissa
  • LOOKUP vastaa hakutaulukon viimeistä ei-virhearvoa
  • LOOKUP palauttaa vastaavan arvon tulos_vektorissa, nimetty alue 'lista'

Pura vain kerran näkyvät kohteet

Yllä olevaa LOOKUP-kaavaa on helppo laajentaa looginen logiikka . Voit purkaa luettelon ainutlaatuisista kohteista, jotka näkyvät vain kerran lähdetiedoissa, käyttämällä seuraavaa kaavaa:

 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Ainoa lisäys on toinen COUNTIF-lauseke:

kuinka laskea irr on excel
 
 COUNTIF (list,list)=1

Tässä COUNTIF palauttaa joukon tuotemääriä seuraavasti:

 
{2222212}

joita verrataan arvoon 1, jolloin saadaan TOSI / EPÄTOSI-arvot:

 
{FALSEFALSEFALSEFALSEFALSETRUEFALSE}

jotka toimivat 'suodattimena' rajoittaakseen lähdön kohteisiin, jotka esiintyvät vain kerran lähdetiedoissa.

Sisään Excel 365 , UNIQUE-toiminto on paras tapa poimia ainutlaatuiset arvot.

Kirjailija Dave Bruns


^