VLOOKUP kahdessa tai useammassa ehtosarakkeessa – Excel University

Jos olet joskus yrittänyt käyttää VLOOKUP-funktiota kahdella tai useammalla ehtosarakkeella, olet nopeasti huomannut, että sitä ei vain rakennettu tähän tarkoitukseen. Onneksi on olemassa toinen toiminto, joka voi toimia vaihtoehtona VLOOKUPille sen mukaan, mitä haluat palauttaa.

Monisarakkeinen hakutavoite

Vahvistetaan ensin tavoitteemme tarkastelemalla esimerkki työkirjasta. Olemme vieneet joitain tietoja kirjanpitojärjestelmästämme, ja se on periaatteessa yhteenveto kuukauden tapahtumien kokonaismäärästä luokittain ja tilittäin. Alla on esimerkki viennistä:

Näistä viedyistä tiedoista haluamme noutaa valitut määrät luokan ja tilin sarakkeet. Haluamme hakea summat ja sijoittaa ne pieneen raporttiin, joka on alla:

Jos tunnet VLOOKUPin on luonnollista yrittää rakentaa raportti tällä toiminnolla, koska loppujen lopuksi tämä on hakutehtävä. Ja hakutehtävät voidaan ratkaista parhaiten perinteisillä hakutoiminnoilla … eikö? No, se riippuu. Se riippuu siitä, mitä yrität noutaa.

Ehdollinen summaus hauille

Jos yrität noutaa numeerisen arvon, kuten summan, perinteinen hakutoiminto ei välttämättä olla paras veto. Tästä syystä. Excel 2007: stä lähtien Microsoft sisälsi ehdollisen summatoiminnon SUMIFS. Tämä usean ehdon yhteenlaskutoiminto on suunniteltu lisäämään numerosarakkeeseen, ja se sisältää vain rivit, jotka täyttävät yhden tai useamman ehdon. Aloittavatko pisteet vielä muodostaa yhteyden?

Jos sovellamme tätä ajatusta käsillä olevaan tehtäväämme, huomasimme nopeasti, että voimme käyttää tätä ehdollista summaustoimintoa raporttien arvojen noutamiseen.

SUMIFS-funktion ensimmäinen argumentti on summa-alue eli lisättävä numero sarake. Meidän tapauksessamme sarake, jolla on arvo, jonka haluamme palauttaa. Loput argumentit tulevat pareittain: ehtoalue ja kriteeriarvo.

On hyödyllistä ajatella funktiota näillä termeillä: lisää tämä sarake (argumentti 1), lisää vain ne rivit, joissa tämä sarake (argumentti 2) on yhtä suuri kuin tämä arvo (argumentti 3), ja jos tämä sarake (argumentti 4) on yhtä suuri kuin tämä arvo (argumentti 5), ja missä… ja niin edelleen, enintään 127 paria.

Täten raportin täyttämiseksi haemme määrän arvot viennistä ja sovitamme luokan ja tilin sarakkeet alla olevan kuvan mukaisesti.

Jos sattuu olemaan useita rivejä samalla luokalla ja tileillä, SUMIFS-funktio palauttaisi kaikkien vastaavien kohteiden summan.

Kuten näette, jos arvo palata on luku, SUMIFS-funktio tekee monisarakkeisten hakujen suorittamisesta helppoa. Mutta entä jos arvo, jota yrität palauttaa, ei ole numero? No, sinun on sitten käytettävä perinteistä hakutoimintoa alla kuvatulla tavalla.

VLOOKUPin käyttäminen SUMIFS-menetelmällä

Yksi tapa on käyttää VLOOKUP ja SUMIFS yhdessä kaavassa. Pohjimmiltaan käytät SUMIFSia VLOOKUPin ensimmäisenä argumenttina. Tätä menetelmää tutkitaan täydellisesti tässä Excel University -viestissä:

VLOOKUPin käyttö CONCATENATE-menetelmällä

Jos yrität palauttaa tekstimerkkijonon numeron sijaan tai käytät version Excel, jolla ei ole SUMIFS-tietoja, olet todennäköisesti juuttunut käyttämään perinteistä hakutoimintoa, kuten VLOOKUP, yhdessä CONCATENATE-toiminnon kanssa, jotta voit luoda yhden ainutlaatuisen hakusarakkeen. Tämä lähestymistapa on melko hyvin dokumentoitu, mutta perusajatus on seuraava: luo ensin yksi hakusarake ja käytä sitten VLOOKUP.

Esimerkkimme on työntekijäluettelo, kuten alla on esitetty:

Meidän on haettava tila työntekijäluettelosta alla näkyvää pientä raporttia varten:

Koska arvo, jota yritämme palauttaa, tila, on tekstimerkkijono eikä luku, emme saa käyttää SUMIFS-funktiota. Siksi meidän on mentävä vanhaan kouluun VLOOKUP- ja CONCATENATE-palvelujen kanssa.

Aloitamme rakentamalla auttaja-sarakkeen, joka luo pohjimmiltaan yhdistetyt hakuarvot. Tämä voidaan helposti toteuttaa CONCATENATE-toiminnolla tai ketjutusoperaattorilla (&). Tämä uusi hakusarake on esitetty alla olevassa sarakkeessa B:

Nyt meillä on yksi hakusarake, jota voidaan käyttää perinteinen hakutoiminto, kuten VLOOKUP. Raportti voidaan täyttää etsimällä yhdistettyjä nimiä uudesta hakualueesta alla olevan kuvan mukaisesti:

Sama lähestymistapa voidaan käyttää, kun on harkittava kahta, kolmea tai useampaa hakusaraketta.

Päätelmä

Sen lisäksi, että SUMIFS-funktiolla voidaan suorittaa monisarakkeisia hakuja, kun paluuarvo on numeerinen, sillä on lisäetuja perinteisiin hakutoimintoihin verrattuna. Esimerkiksi, se palauttaa nollan, kun vastaavaa arvoa ei löydy, se palauttaa kaikkien vastaavuuksien summan, se tukee vertailuoperaattoreita eikä se hajoa, kun uusi sarake lisätään haku- ja palautussarakkeiden väliin.

Joten, kun aiot poistaa VLOOKUP-toiminnon hakutehtävän suorittamiseksi, harkitse sen sijaan SUMIFS-toimintoa. Uskokaa tai älkää, SUMIFS-funktio tekee upean hakutoiminnon.

Jos sinulla on muita ensisijaisia lähestymistapoja monisarakkeisiin hakuihin, haluaisimme kuulla lisää … lähetä kommentti alla.

Esimerkkitiedosto

Jos haluat pelata työkirjalla, jota käytetään yllä olevien kuvakaappausten luomiseen, lataa esimerkkitiedosto:

MultColumnLookup