Yleinen kaava | _+_ | YhteenvetoJos haluat poimia luettelosta tai sarakkeesta vain ainutlaatuisia arvoja, voit käyttää taulukkokaavaa, joka perustuu INDEX-, MATCH- ja COUNTIF -arvoihin. Esitetyssä esimerkissä D5: n kaava, kopioitu alas, on:
{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}
missä 'lista' on nimetty alue B5: B11.
Huomaa: tämä on taulukon kaava ja on syötettävä näppäimillä control + shift + enter.
ikä syntymäpäivästä excelSelitys
Tämän kaavan ydin on perushaku INDEX:
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}
Toisin sanoen, anna INDEXille luettelo ja rivinumero, ja INDEX hakee arvon, joka lisätään ainutlaatuiseen luetteloon.
Kova työ on selvittää RIVI -numero, jotta saadaan INDEX, jotta saisimme vain yksilöllisiä arvoja. Tämä tehdään MATCHilla ja COUNTIFilla, ja tärkein temppu on tässä:
= INDEX (list,row)
Tässä, C.OUNTIF laskee, kuinka monta kertaa yksilöllisessä luettelossa olevat kohteet näkyvät pääluettelossa käyttämällä laajeneva viittaus alueelle,$ D $ 4: D4.
Laajeneva viite on absolute toisella puolella, suhteellinen toisella puolella. Tässä tapauksessa, kun kaava kopioidaan alas, viite laajenee sisältämään lisää rivejä yksilölliseen luetteloon.
Huomaaviitealkaa D4: stä,yksi rivi edellä ensimmäinen ainutlaatuinen merkintä ainutlaatuisessa luettelossa. Tämä on tarkoituksellista - haluamme laskea kohteet * jo * yksilölliseen luetteloon, emmekä voi sisällyttää nykyistä solua luomalla pyöreää viitettä. Aloitamme siis yllä olevasta rivistä.
Tärkeää: varmista, että yksilöllisen luettelon otsikko ei näy pääluettelossa.
COUNTIF -kriteereissä käytämme itse pääluetteloa. Kun COUNTIF antaa useita ehtoja, se palauttaa useita tuloksia matriisi . Jokaisella uudella rivillä on erilainen taulukko, kuten tämä:
COUNTIF ($D:D4,list)
Huomautus: COUNTIF käsittelee useita ehtoja, joilla on TAI -suhde (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 (rivinumerot). Tätä varten käytämme MATCHia, joka on määritetty täsmälleen vastaavuudelle, nolla -arvojen löytämiseksi. Jos laitamme COUNTIF: n luomat taulukot yllä olevaan MATCH: iin, saadaan seuraava:
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8
MATCH etsii kohteita etsimällä lukua nolla (eli etsimällä kohteita, jotka eivät vielä näy ainutlaatuisessa luettelossa). Tämä toimii, koska MATCH palauttaa aina ensimmäisen osuman, kun kopioita on.
Lopuksi paikat syötetään INDEXiin rivinumeroina, ja INDEX palauttaa nimen kyseisessä paikassa.
Ei -matriisiversio, jossa on LOOKUP
Voit rakentaa ei-matriisikaavan poimiaksesi ainutlaatuisia kohteita joustavan LOOKUP-toiminnon avulla:
MATCH (0,{0000000},0) // 1 (Joe) MATCH (0,{1000100},0) // 2 (Bob) MATCH (0,{1100101},0) // 3 (Sue) MATCH (0,{1111101},0) // 6 (Aya)
Kaavarakenne on samanlainen kuin yllä oleva INDEX MATCH -kaava, mutta LOOKUP pystyy käsittelemään matriisitoimintaa natiivina.
- COUNTIF palauttaa jokaisen arvon laskut luettelon luettelosta laajentaa valikoimaa $ D $ 4: D4
- Vertailu nollaan luo joukon TOSI- ja EPÄTOSI -arvoja
- Numero 1 jaetaan taulukolla, jolloin syntyy 1s- ja #DIV/0 -virheiden taulukko
- Tästä taulukosta tulee lookup_vector LOOKUPin sisällä
- Hakuarvo 2 on suurempi kuin mikään lookup_vectorin arvo
- LOOKUP vastaa hakutoiminnon viimeistä ei-virhearvoa
- LOOKUP palauttaa vastaavan arvon tulos_vektorissa, nimetyllä alueella 'luettelo'
Pura kohteet, jotka näkyvät vain kerran
Yllä olevaa LOOKUP -kaavaa on helppo jatkaa boolen logiikka . Voit poimia 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),list)
Ainoa lisäys on toinen COUNTIF -lauseke:
kuinka laskea irr on excel
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)
Tässä COUNTIF palauttaa joukon nimikkeitä seuraavasti:
COUNTIF (list,list)=1
joita verrataan 1: een, jolloin saadaan joukko TRUE/FALSE -arvoja:
{2222212}
jotka toimivat 'suodattimena' rajoittaakseen tuotannon kohteisiin, joita esiintyy vain kerran lähdetiedossa.
Sisään Excel 365 , UNIQUE -toiminto on paras tapa poimia ainutlaatuisia arvoja.
Kirjailija Dave Bruns
