Katso video - etsi toinen, kolmas tai yhdeksäs vastaava arvo
Mitä tulee tietojen etsimiseen Excelistä, käytän usein hämmästyttäviä toimintoja - VLOOKUP ja INDEX (enimmäkseen yhdessä MATCH -toiminnon kanssa).
Nämä kaavat on kuitenkin suunniteltu etsimään vain hakuarvon ensimmäinen esiintymä.
Mutta entä jos haluat etsiä toisen, kolmannen, neljännen tai n: nnen arvon.
No, se onnistuu pienellä lisätyöllä.
Tässä opetusohjelmassa näytän sinulle erilaisia tapoja (esimerkein) toisen tai n: nnen arvon etsimisestä Excelissä.
Etsi toinen, kolmas tai n. Arvo Excelistä
Tässä opetusohjelmassa käsitellään kahta tapaa etsiä toinen tai n-arvo Excelistä:
- Käyttämällä auttajapylvästä.
- Matriisikaavojen käyttäminen.
Aloitetaan ja sukellamme suoraan sisään.
Helper -sarakkeen käyttäminen
Oletetaan, että olet koulutuskoordinaattori organisaatiossa ja sinulla on alla esitetyt tiedot. Haluat luetella kaikki koulutukset työntekijän nimen eteen.
Yllä olevassa tietojoukossa työntekijät ovat saaneet koulutusta eri Microsoft Office -työkaluista (Excel, PowerPoint ja Word).
Nyt voit etsiä työntekijän suorittaman koulutuksen VLOOKUP -toiminnon tai INDEX/MATCH -yhdistelmän avulla. Se palauttaa kuitenkin vain ensimmäisen vastaavan esiintymän.
Esimerkiksi Johnin tapauksessa hän on käynyt kaikki kolme koulutusta, mutta kun etsin hänen nimeään VLOOKUP tai INDEX/MATCH, se palauttaa aina 'Excelin', joka on hänen nimensä ensimmäinen koulutus luettelossa .
Saavuttaaksemme tämän, voimme käyttää apusaraketta ja luoda siihen ainutlaatuisia hakuarvoja.
Tässä on vaiheet:
- Lisää sarake harjoituksen luettelon sarakkeen eteen.
- Kirjoita soluun B2 seuraava kaava:
= A2 & LASKE ($ A $ 2: $ A2, A2)
- Kirjoita soluun F2 seuraava kaava ja kopioi ja liitä kaikki muut solut:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Yllä oleva kaava palauttaa jokaisen työntekijän koulutuksen siinä järjestyksessä kuin se näkyy luettelossa. Jos työntekijälle ei ole ilmoitettu koulutusta, se palauttaa tyhjän kohdan.
Kuinka tämä kaava toimii?
Auttaja -sarakkeen COUNTIF -kaava tekee jokaisen työntekijän nimen ainutlaatuiseksi lisäämällä siihen numeron. Esimerkiksi Johanneksen ensimmäisestä esiintymästä tulee Johannes 1, toisesta esiintymästä tulee Johannes 2 ja niin edelleen.
VLOOKUP -kaava käyttää nyt näitä yksilöllisiä työntekijöiden nimiä löytääkseen vastaavan koulutuksen.
Huomaa, että $ E2 & COLUMNS ($ F $ 1: F1) ovat kaavan hakuarvo. Tämä lisäisi numeron työntekijän nimeen sarakkeen numeron perusteella. Esimerkiksi kun tätä kaavaa käytetään solussa F2, hakuarvosta tulee "John1". Solussa G2 siitä tulee ”John2” ja niin edelleen.
Array -kaavan käyttö
Jos et halua muuttaa alkuperäistä tietojoukkoa lisäämällä auttajasarakkeita, voit myös etsiä toisen, kolmannen tai n: nnen arvon taulukkokaavalla.
Oletetaan, että sinulla on sama aineisto kuin alla:
Tässä on kaava, joka palauttaa oikean hakuarvon:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")), SARAKKEET ($ E $ 1 : E1))), "")
Kopioi tämä kaava ja liitä se soluun E2.
Huomaa, että tämä on taulukkokaava ja sinun on käytettävä Control + Shift + Enter -näppäimiä (pidä Control- ja Shift -näppäimiä painettuna ja paina Enter -näppäintä) sen sijaan, että painat vain Enter -näppäintä.
Lataa esimerkkitiedosto napsauttamalla tätä.
Kuinka tämä kaava toimii?
Jaetaan tämä kaava osiin ja katsotaan miten se toimii.
$ A $ 2: $ A $ 14 = $ D2
Kaavan yllä oleva osa vertaa A2: A14: n jokaista solua arvoon D2. Tässä tietojoukossa se tarkistaa, sisältääkö solu nimen "John" vai ei.
Se palauttaa joukon TRUE of FALSE. Jos solulla on nimi "John", se olisi totta, muuten se olisi epätosi.
Alla on taulukko, jonka saat tässä esimerkissä:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Huomaa, että sillä on TOSI 1., 7. ja 111. sijalla, koska siellä nimi John esiintyy tietojoukossa.
JOS ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Yllä oleva IF-kaava käyttää TOSI- ja EPÄTOSI-matriisia ja korvaa TOSI sen esiintymispaikalla luettelossa (ROW ($ A $ 2: $ A $ 14) -1) ja EPÄTOSI "" (tyhjät). Seuraavassa on tuloksena oleva taulukko, jonka saat tällä IF -kaavalla:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Huomaa, että 1, 7 ja 11 ovat Johanneksen esiintymispaikka luettelossa.
PIENI (JOS ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), SARAKKEET ($ E $ 1: E1))
SMALL -toiminto valitsee nyt ensimmäisen pienimmän, toiseksi pienimmän, kolmannen pienimmän numeron tästä taulukosta. Huomaa, että se käyttää COLUMNS -funktiota sarakkeen numeron luomiseen. Solussa E2 COLUMNS -funktio palauttaa arvon 1 ja SMALL -funktio palauttaa 1. Solussa F2 COLUMNS -funktio palauttaa 2 ja SMALL -funktio palauttaa 7.
INDEKSI ($ B $ 2: $ B $ 14, PIENI (JOS ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), SARAKKEET ($ E $ 1: E1) ))
INDEX -funktio palauttaa nyt arvon sarakkeen B luettelosta SMALL -funktion palauttaman sijainnin perusteella. Siksi solussa E2 se palauttaa Excelin, joka on B2: B14: n ensimmäinen kohde. Solussa F2 se palauttaa PowerPointin, joka on luettelon seitsemäs kohde.
Koska joissakin tapauksissa on vain yksi tai kaksi koulutusta joillekin työntekijöille, INDEX -toiminto palauttaa virheen. IFERROR -toimintoa käytetään palauttamaan tyhjä virheen tilalle.
Huomaa, että tässä esimerkissä olen käyttänyt alueviittauksia. Käytännön esimerkeissä on kuitenkin hyödyllistä muuntaa tiedot Excel -taulukkoksi. Muuntamalla Excel -taulukkoksi voit käyttää strukturoituja viittauksia, mikä helpottaa kaavojen luomista. Lisäksi Excel -taulukko voi automaattisesti ottaa huomioon kaikki uudet luetteloon lisätyt harjoituskohteet (joten sinun ei tarvitse säätää kaavoja joka kerta).
Mitä teet, kun sinun on etsittävä toinen, kolmas tai n. Arvo? Olen varma, että tähän on enemmän tapoja. Jos käytät jotain helpompaa kuin tässä lueteltu, jaa meille kaikki kommentit -osiossa.
Lataa esimerkkitiedosto napsauttamalla tätä.
