Kā lietot VLOOKUP ar vairākiem kritērijiem?
Dažreiz, strādājot ar datiem, kad dati tiek saskaņoti ar atsauces Vlookup, ja vispirms tiek atrasta vērtība, tas parāda rezultātu un nemeklē nākamo vērtību, bet kas notiek, ja lietotājs vēlas otro rezultātu, tas ir vēl viens kritērijs, lai izmantotu Vlookup ar vairākiem kritērijiem mums jāizmanto citas funkcijas, piemēram, jāizvēlas funkcija.
VLOOKUP formula programmā Excel
Apskatīsim dažus funkcijas VLOOKUP ar vairāku kritēriju meklēšanu piemērus.
1. piemērs
Pieņemsim, ka jums ir sava uzņēmuma darbinieku dati. Datos ir vārds, viņu pašreizējā alga, nodaļa, darbinieka ID, kā parādīts zemāk.
Jūs vēlaties meklēt darbinieku pēc viņa vārda un nodaļas. Šeit meklēšana ietvers divas detaļas: nosaukums un nodaļa. Nosaukums un nodaļa, kas jāmeklē, ir norādīta šūnās G6 un G7.
Lai meklētu “Dhruv” “Pārdošanas” nodaļā, vispirms izveidojiet atsevišķu kolonnu ar visiem darbiniekiem “Name” un “Department”.
Lai to izdarītu pirmajam darbiniekam, izmantojiet Excel VLOOKUP formulu:
= C3 un D3
un nospiediet Enter. Šūnā tagad būs “ManishIT”. Ir svarīgi pievienot šo kolonnu pa kreisi no datiem, jo uzmeklēšanai tiek ņemta vērā masīva diapazona pirmā kolonna. Tagad vienkārši velciet to uz pārējām šūnām.
Lai meklētu “Dhruv” un “Sales” vērtību šūnās G6 un G7, varat izmantot Excel VLOOKUP formulu:
= VLOOKUP (H6 un H7, A3: E22, 5, FALSE)
Tas atgriezīs meklētā darbinieka Dhruv atalgojumu no Tirdzniecības departamenta.
2. piemērs
Pieņemsim, ka jums ir divu dažādu produktu pārdošanas dati par 12 mēnešiem, kā parādīts zemāk.
Jūs vēlaties izveidot Excel programmā uzmeklēšanas tabulu, kurā ievadāt mēnesi un produkta ID, un tas atgriež šī produkta pārdošanas apjomus attiecīgajā mēnesī.
Lai to izdarītu, programmā Excel varat izmantot VLOOKUP un atbilstības formulu:
= VLOOKUP (F4, A3: C14, MATCH (F5, A2: C2, 0), 0)
kur mēnesis, kuru vēlaties meklēt, ir norādīts F4, bet produkta nosaukums meklēšanai - F5.
Šajā gadījumā tas atgriezīs 13 000 .
3. piemērs
Pieņemsim, ka jums ir apkopoti pārdošanas dati par vienu no produktiem visa gada laikā četrās dažādās pilsētas zonās, kā parādīts zemāk.
Tagad vēlaties pārbaudīt, vai mēnesis, kurā Austrumu zonā bija maksimāli noiets, ir arī mēnesis, kurā maksimālais pārdošanas apjoms bija Rietumu zonā. Lai to pārbaudītu, vispirms jāizveido papildu kolonna, kurā norādīti pārdošanas apjomi austrumu un rietumu zonai. Šajā gadījumā vērtības nošķiram ar.
Lai kreisajā pusē pievienotu papildu kolonnu, izmantojiet Excel VLOOKUP formulu:
= D3 & ““ & E3
tabulas pirmajai šūnai un nospiediet taustiņu Enter. Pēc tam velciet to uz pārējām šūnām.
Tagad atsevišķi aprēķiniet maksimālo pārdošanas apjomu Austrumu un Rietumu zonām. Lai aprēķinātu maksimālo vērtību, izmantojiet Excel VLOOKUP formulu:
= MAX (D3: D14) Austrumu zonai
(Uzziniet vairāk par Max funkciju programmā Excel)
un = MAX (E3: E14) rietumu zonai.
Tagad, lai pārbaudītu, vai mēnesis, kurā Austrumu zonā bija maksimāli noiets, ir arī mēnesis, kurā maksimālais pārdošanas apjoms bija Rietumu zonā, varat izmantot:
= KĻŪDA (VLOOKUP (J4 & ”” & J5, B3: C14, 2, 0), “NĒ”)
(Uzziniet vairāk par IFERROR funkciju programmā Excel)
VLOOKUP (J4 & ”” & J5, B3: C14, 2, 0) papildu kolonnā meklēs Austrumu un Rietumu zonas maksimālo vērtību. Ja tas spēj atrast spēli, tas atgriezīs attiecīgo mēnesi. Citādi tas radīs kļūdu.
IFERROR ((VLOOKUP (…)), “NĒ”): Ja funkcijas VLOOKUP izvade ir kļūda, tā atgriezīs “NO” citu. Tas atgriezīs attiecīgo mēnesi.
Tā kā šāds mēnesis nepastāv, pārbaudīsim, vai mēnesis, kurā Austrumu zonā bija maksimāli noiets, ir mēnesis, kurā rietumu zonā pārdošanas apjoms bija otrais lielākais. Vispirms aprēķiniet otro lielāko pārdošanas apjomu rietumu zonai, izmantojot.
= LIELS (E3: E14, 2)
(Uzziniet vairāk par LARGE Function Excel)
Tagad izmantojiet sintaksi: = IFERROR (VLOOKUP (K4 & ”“ & K5, B3: C14, 2, 0), “NO”)
Tas atgriezīsies jūnijā .
Šeit ir svarīgi atzīmēt, ka var būt arī vairāk nekā viens mēnesis, kurā maksimālais pārdošanas apjoms bija austrumu un rietumu zonā, taču Excel VLOOKUP formula atgriezīs tikai vienu no šiem mēnešiem.
Domā atcerēties
- Funkcija VLOOKUP ar vairākiem kritērijiem tiek izmantota, lai meklētu vērtību kolonnā un atgrieztu vērtību no atbilstošās kolonnas.
- Funkcija VLOOKUP ar vairākiem kritērijiem meklē uzmeklēšanas vērtību norādītā masīva / tabulas pirmajā kolonnā.
- Ja jūs vēlaties meklēt VLOOKUP funkciju ar vairākiem kritērijiem, piemēram, vērtību1 no 1 st kolonnas un VALUE2 no 2 nd slejā, jums ir nepieciešams, lai pievienotu papildu aili meklēšanu. Šī papildu kolonna jāpievieno datu kreisajā pusē tā, lai tā būtu redzama kā uzmeklēšanas tabulas pirmā kolonna.
