VLOOKUP ir viena no visbiežāk izmantotajām funkcijām programmā Excel. Tā meklē vērtību diapazonā un atgriež atbilstošu vērtību noteiktā kolonnas numurā.
Tagad es saskāros ar problēmu, kad man bija jāmeklē visa rinda un jāatgriež vērtības visās šīs rindas kolonnās (nevis jāatgriež viena vērtība).
Tātad, lūk, kas man bija jādara. Zemāk esošajā datu kopā man bija pārdošanas pārstāvju vārdi un pārdošanas apjomi, ko viņi veica 2012. gada 4 ceturkšņos. Man bija nolaižamā izvēlne ar viņu vārdiem, un es vēlējos iegūt maksimālo pārdošanas pārdošanas apjomu šajos četros ceturkšņos.
Es varētu izdomāt 2 dažādus veidus, kā to izdarīt - izmantojot INDEX vai VLOOKUP.
Meklēt visu rindu / kolonnu, izmantojot INDEX formulu
Šeit ir formula, kuru es izveidoju, lai to izdarītu, izmantojot indeksu
= LIELS (INDEKSS ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Kā tas strādā:
Vispirms apskatīsim funkciju INDEX, kas ir ietverta LARGE funkcijā.
= INDEKSS ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Rūpīgi analizēsim INDEX funkcijas argumentus:
- Masīvs - $ B $ 4: $ F $ 1
- Rindas numurs - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Kolonnas numurs - 0
Ievērojiet, ka kolonnas numuru izmantoju kā 0.
Šeit triks ir tāds, ka, izmantojot kolonnas numuru kā 0, tas atgriež visas vērtības visās kolonnās. Tātad, ja nolaižamajā izvēlnē atlasīšu Jāni, indeksa formula atgrieztu visas četras Jāņa pārdošanas vērtības {91064,71690,67574,25427}.
Tagad es varu izmantot funkciju Liela, lai iegūtu lielāko vērtību
Pro padoms - izmantojiet kolonnas/rindas numuru kā 0 indeksa formulā, lai atgrieztu visas kolonnās/rindās esošās vērtības.
Meklēt visu rindu / kolonnu, izmantojot VLOOKUP formulu
Lai gan indeksa formula ir glīta, tīra un izturīga, VLOOKUP veids ir nedaudz sarežģīts. Tas arī padara funkciju nepastāvīgu. Tomēr ir pārsteidzošs triks, ar kuru es dalīšos šajā sadaļā. Šeit ir formula:
= LARGE (VLOOKUP (H3, B4: F13, ROW (NETIEŠS ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Kā tas strādā
- ROW (NETIEŠA (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - šī formula atgriež masīvu {2; 3; 4; 5}. Ņemiet vērā, ka, tā kā tā izmanto INDIRECT, šī formula ir nepastāvīga.
- VLOOKUP (H3, B4: F13, ROW (NETIEŠS (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Šeit ir labākā daļa. Saliekot tos kopā, tas kļūst par VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Tagad ievērojiet, ka viena kolonnas numura vietā esmu piešķīris kolonnu numuru masīvu. Un VLOOKUP paklausīgi meklē vērtības visās šajās kolonnās un atgriež masīvu.
- Tagad izmantojiet funkciju LARGE, lai iegūtu lielāko vērtību.
Lai izmantotu šo formulu, neaizmirstiet izmantot Control + Shift + Enter.
Profesionāļa padoms - VLOOKUP, nevis vienas slejas numura izmantošana, ja izmantojat kolonnu numuru masīvu, tas atgriezīs uzmeklēšanas vērtību masīvu.
