Excel Vlookup za vrnitev več vrednosti
Ena ključnih funkcij funkcije VLOOKUP je, da bo delovala za unikatne vrednosti in če obstajajo podvojene vrednosti, bo vrnjena kakršna koli prva najdena vrednost tudi za vse druge iskalne vrednosti. To je ena ključnih stvari, ki jo moramo upoštevati pri uporabi formule VLOOKUP. Ko se iskalna vrednost pojavi večkrat in če ima več vrednosti, moramo vključiti različne strategije. V tem članku vam bomo pokazali, kako vrniti več vrednosti s funkcijo VLOOKUP.
Kako vrniti več vrednosti z uporabo funkcije Vlookup?
Kot smo že povedali, VLOOKUP deluje za unikatne vrednosti, za podvojene vrednosti pa bo vrnil prvo najdeno vrednost.
Na primer, poglejte spodnje podatke.
V tabeli 1 imamo tabelo 1 in tabelo 2, sadje in njihove cene imamo v različnih mestih, za tabelo 2 pa moramo s funkcijo VLOOKUP pridobiti stroškovno ceno iz tabele 1. Najprej uporabite funkcijo VLOOKUP.
- Poglejte, za vsa mesta imamo enako ceno. Na primer, v »Tabeli 1« za »Apple« v mestu »Bangalore« jih imamo 108, saj je to prva vrednost, najdena v tabeli za »Apple«, in je vrnila enako za vsa mesta.
- Podobno je za "Grozdje" prva vrednost 79, enako je bilo vrnjeno za vsa mesta, v primeru "Orange" pa 56 za vsa mesta.
V teh primerih moramo torej ustvariti pomožni stolpec za ustvarjanje edinstvenega iskalnega seznama vrednosti. Vsako sadje ima različne cene za vsako mesto, zato lahko kombinacija imena sadja in mesta ustvari edinstven seznam, vstavi pomožni stolpec ter kombinira ime sadja in ime mesta.
Vsako ime sadja je torej združeno z mestom, tako da je ločnica med imenom sadja in imenom mesta vključena poševnica nazaj (/).
Zdaj se vrnite na »Tabelo 2« in odprite funkcijo VLOOKUP.
Zdaj moramo tu vključiti isto strategijo stolpca za pomoč, da izberemo iskalno vrednost, najprej izberite ime sadja.
Nato kombinirajte poševnico nazaj, preden jo združite z imenom mesta.
Zdaj združite ime mesta.
Zdaj je vrednost iskanja podobna pomožnemu stolpcu. Zdaj izberite matrično matriko, začenši s pomožnim stolpcem.
Zdaj navedite številko stolpca kot 4, iskanje obsega pa kot FALSE ali 0.
Tukaj je, imamo nov cenik z natančnimi številkami, na primer zahvaljujoč stolpcu za pomoč ali kombinacijo imena in mesta sadja.
Uporabite nadomestne metode za več vrednosti
Videli smo, kako je pomožni stolpec lahko koristen pri pridobivanju več vrednosti z uporabo formule VLOOKUP. Toda predstavljajte si situacijo spodaj.
Pri tem nimamo nobenega mesta, da bi ustvarili stolpec za združevanje, zato bomo morda morali uporabiti različne strategije. Spodaj je zapletena formula, s katero lahko dobimo več vrednosti podvojenih unikatnih vrednosti.
= INDEX ($ B $ 2: $ B $ 11, SMALL (IF (E3 = $ A $ 2: $ A $ 11, ROW ($ A $ 2: $ A $ 11) - ROW ($ A $ 2) +1), ROW (1: 1)))
Ta formula je videti dolgotrajna, kajne? Vendar pa imamo še eno alternativno metodo, tj. Kombiniranje imena sadja z njihovim številom na seznamu.
Uporabite spodnjo funkcijo COUNTIF, da ustvarite pomožni stolpec.
Zgornja funkcija nam bo dala število vsakega sadja v kombinaciji s samim imenom sadja. Za primer si oglejte vrstico številka 4 v tem, dvakrat štejemo "jabolko", tako da štetje pravi 2 in v kombinaciji z imenom sadja dobi "2 jabolka". Tako boste ustvarili edinstven seznam sadja.
Zdaj ustvarite iskalno tabelo, kot je spodnja.
Zdaj odprite funkcijo VLOOKUP v iskalni tabeli, tj. V celici H3.
V pomožnem stolpcu šteje prva kombinirana vrednost, zato tukaj izberemo številčno vrednost in nato združimo z imenom sadja.
Zdaj izberite tabelo in vnesite številko indeksa stolpca, da dobite rezultat.
Stvari, ki si jih je treba zapomniti
- VLOOKUP vrne enako vrednost za iskalne vrednosti, če ima iskalna vrednost podvojena imena.
- Če želite pridobiti več vrednosti iste iskalne vrednosti, moramo z uporabo katerega koli od zgornjih 3 načinov ustvariti pomožne stolpce.
Priporočeni članki
To je bil vodič za VLOOKUP za vrnitev več vrednosti. Tu razpravljamo o tem, kako vrniti več vrednosti s funkcijo VLOOKUP in uporabiti alternativne metode za več vrednosti in predlogo Excel, ki jo je mogoče naložiti. Te koristne funkcije si lahko ogledate tudi v Excelu -
- Uporabite VLOOKUP z Power BI
- Uporabite VLOOKUP za besedilo
- Uporabite VLOOKUP z Match
- VLOOKUP Vadnica za Excel
