A VLOOKUP az Excel egyik leggyakrabban használt funkciója. Keres egy értéket egy tartományban, és egy megfelelő értéket ad vissza egy megadott oszlopszámban.
Most egy olyan problémával találkoztam, amikor meg kellett keresnem a teljes sort, és vissza kellett adnom az értékeket az adott oszlop összes oszlopában (ahelyett, hogy egyetlen értéket adnék vissza).
Tehát itt van, amit tennem kellett. Az alábbi adatkészletben az értékesítési képviselők nevei és az értékesítések voltak, amelyeket 2012 negyedik negyedévében végeztek. A legördülő listában a nevük volt, és ki akartam venni az adott értékesítési képviselő maximális eladásait ebben a négy negyedévben.
Erre kétféle módszert tudtam kitalálni - az INDEX vagy a VLOOKUP használatával.
Keresse meg a teljes sort / oszlopot az INDEX képlet használatával
Íme a képlet, amelyet erre készítettem az Index segítségével
= NAGY (MUTATÓ ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Hogyan működik:
Nézzük először a LARGE funkcióba csomagolt INDEX funkciót.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Elemezzük alaposan az INDEX függvény érveit:
- Tömb - $ B $ 4: $ F $ 1
- Sorszám - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Oszlopszám - 0
Vegye figyelembe, hogy az oszlopszámot 0 -ként használtam.
A trükk itt az, hogy ha az oszlopszámot 0 -ként használja, akkor az összes oszlop összes értékét visszaadja. Tehát ha a Jánost választom a legördülő menüben, akkor az indexképlet visszaadja John összes 4 értékesítési értékét {91064,71690,67574,25427}.
Most már használhatom a Nagy funkciót a legnagyobb érték kinyerésére
Profi tipp - Használja az Oszlop/Sor számát 0 -ként az Indexképletben, hogy visszaadja az oszlopok/sorok összes értékét.
A teljes sor / oszlop megkeresése a VLOOKUP képlet használatával
Míg az Index képlet ügyes, tiszta és robusztus, a VLOOKUP módja kissé összetett. Végül a függvényt is ingadozóvá teszi. Van azonban egy csodálatos trükk, amelyet megosztanék ebben a részben. Íme a képlet:
= NAGY (VLOOKUP (H3, B4: F13, ROW (KÖZVETLEN ("2:" & COUNTA ($ B $ 4: $ F $ 4))), HAMIS), 1)
Hogyan működik
- ROW (KÖZVETLEN („2:” & COUNTA ($ B $ 4: $ F $ 4)))) - Ez a képlet egy {2; 3; 4; 5} tömböt ad vissza. Ne feledje, hogy mivel INDIRECT -et használ, ez a képletet illékonyá teszi.
- VLOOKUP (H3, B4: F13, ROW (KÖZVETLEN („2:” & COUNTA ($ B $ 4: $ F $ 4))), HAMIS) - Itt a legjobb rész. Ha ezeket összerakja, VLOOKUP lesz (H3, B4: F13, {2; 3; 4; 5}, FALSE). Most vegye észre, hogy egyetlen oszlopszám helyett oszlopszámok tömbjét adtam meg neki. A VLOOKUP pedig engedelmesen megkeresi az értékeket ezekben az oszlopokban, és tömböt ad vissza.
- Most csak használja a LARGE funkciót a legnagyobb érték kinyeréséhez.
Ne felejtse el használni a Control + Shift + Enter billentyűket a képlet használatához.
Profi tipp - A VLOOKUP -ban az egyetlen oszlopszám használata helyett, ha oszlopszám -tömböt használ, a keresési értékek tömbjét adja vissza.
