VLOOKUP yra viena iš dažniausiai naudojamų „Excel“ funkcijų. Ji ieško vertės diapazone ir grąžina atitinkamą reikšmę nurodytame stulpelio numeryje.
Dabar susidūriau su problema, kai turėjau ieškoti visos eilutės ir grąžinti visų tos eilutės stulpelių reikšmes (užuot grąžinę vieną vertę).
Taigi štai ką turėjau padaryti. Žemiau esančiame duomenų rinkinyje turėjau pardavimų atstovų pavadinimus ir pardavimus, kuriuos jie atliko 2012 m. 4 ketvirčius. Turėjau išskleidžiamąjį meniu su jų pavadinimais ir norėjau išgauti maksimalius pardavimo pardavėjų pardavimus per tuos keturis ketvirčius.
Galėčiau sugalvoti 2 skirtingus būdus tai padaryti - naudojant INDEX arba VLOOKUP.
Peržiūrėkite visą eilutę / stulpelį naudodami INDEX formulę
Štai formulė, kurią sukūriau tam, naudodamas indeksą
= DIDELIS (INDEKSAS ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Kaip tai veikia:
Pirmiausia pažvelkime į funkciją INDEX, esančią LARGE funkcijos viduje.
= INDEKSAS ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Atidžiai išanalizuokime INDEX funkcijos argumentus:
- Masyvas - $ B $ 4: $ F $ 1
- Eilutės numeris - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Stulpelio numeris - 0
Atkreipkite dėmesį, kad aš naudoju stulpelio numerį kaip 0.
Apgaulė yra ta, kad kai stulpelio numerį naudojate kaip 0, jis grąžina visas visų stulpelių reikšmes. Taigi, jei išskleidžiamajame meniu pasirinksiu Joną, indekso formulė grąžins visas 4 „John“ pardavimo vertes {91064,71690,67574,25427}.
Dabar galiu naudoti funkciją Didelė, kad išgautume didžiausią vertę
Patarimas profesionalams - naudokite stulpelio/eilutės numerį kaip 0 indekso formulėje, kad grąžintumėte visas stulpelių/eilučių reikšmes.
Peržiūrėkite visą eilutę / stulpelį naudodami „VLOOKUP“ formulę
Nors indekso formulė yra tvarkinga, švari ir tvirta, VLOOKUP būdas yra šiek tiek sudėtingas. Tai taip pat daro funkciją nepastovią. Tačiau yra nuostabus triukas, kuriuo norėčiau pasidalinti šiame skyriuje. Čia yra formulė:
= DIDELIS (VLOOKUP (H3, B4: F13, ROW (NETIESIOGINIS ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Kaip tai veikia
- ROW (NETIESIOGINĖ („2:“ & COUNTA ($ B $ 4: $ F $ 4))) - Ši formulė grąžina masyvą {2; 3; 4; 5}. Atminkite, kad kadangi ši formulė naudojama netiesiogiai, ši formulė tampa nepastovi.
- VLOOKUP (H3, B4: F13, ROW (NETIESIOGINIS („2:“ & COUNTA ($ B $ 4: $ F $ 4))), NETIESA) - Čia yra geriausia dalis. Sujungus juos, jis tampa VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Dabar atkreipkite dėmesį, kad vietoj vieno stulpelio numerio pateikiau stulpelių skaičių masyvą. O VLOOKUP paklusniai ieško verčių visuose šiuose stulpeliuose ir grąžina masyvą.
- Dabar tiesiog naudokite LARGE funkciją, kad išgautumėte didžiausią vertę.
Nepamirškite naudoti „Control“ + „Shift“ + „Enter“, kad galėtumėte naudoti šią formulę.
Patarimas „Pro“ - VLOOKUP, o ne vieno stulpelio numerio naudojimas, jei naudojate stulpelių skaičių masyvą, grąžins paieškos verčių masyvą.
