VLOOKUP két vagy több kritérium oszlopon – Excel Egyetem

Ha valaha is megpróbált egy VLOOKUP függvényt használni két vagy több kritérium oszloppal, gyorsan felfedezte, hogy csak nem erre a célra készült. Szerencsére van még egy olyan funkció, amely a VLOOKUP alternatívájaként működhet, attól függően, hogy mit szeretne visszaadni.

Többoszlopos keresési cél

Először is erősítsük meg célkitűzésünket a következővel: egy munkafüzet mintát. Exportáltunk néhány információt a számviteli rendszerünkből, és alapvetően a hónap tranzakcióinak összesítését foglalja össze osztályonként és számlánként. Az exportminta az alábbiakban látható:

Ezekből az exportált adatokból a kiválasztott összegeket szeretnénk beolvasni a osztály és fiók oszlopok. Szeretnénk beolvasni az összegeket, és beilleszteni az alábbi képen látható kis jelentésünkbe:

Ha ismeri a VLOOKUP-ot függvény, természetesnek tűnik a jelentést ezzel a függvénnyel felépíteni, mert végül is ez egy keresési feladat. És a keresési feladatokat a hagyományos keresési funkciókkal lehet a legjobban megoldani … ugye? Nos, ez attól függ. Attól függ, hogy mit próbál lekérni.

Feltételes összegzés a keresésekhez

Ha numerikus értéket, például összeget próbál beolvasni, akkor előfordulhat, hogy a hagyományos keresési funkció nem legyen a legjobb fogadás. Itt van miért. Az Excel 2007-től kezdődően a Microsoft felvette a SUMIFS feltételes összegző függvényt. Ez a többszörös feltétel összegző függvény úgy van kialakítva, hogy összeadja a számok oszlopát, és csak egy vagy több feltételnek megfelelő sorokat tartalmaz. A pontok már kapcsolódni kezdenek?

Ha ezt az ötletet alkalmazzuk a feladatunkra, gyorsan rájönnénk, hogy ezt a feltételes összegző függvényt használhatnánk a jelentés értékeink lekérésére.

A SUMIFS függvény első argumentuma az összegtartomány, vagyis a hozzáadandó számok oszlopa. Esetünkben az az oszlop, amelynek értéke van, amelyet vissza akarunk adni. A többi argumentum párban érkezik: a kritériumtartomány és a kritériumérték.

Hasznos ezekre a kifejezésekre gondolni a függvényről: adja hozzá ezt az oszlopot (1. argumentum), csak azokat a sorokat vegye fel, ahol ez az oszlop (2. érv) megegyezik ezzel az értékkel (3. érv), és ahol ez az oszlop (4. érv) megegyezik ezzel az értékkel (5. érv), és ahol… és így tovább, legfeljebb 127 pár.

Így a jelentésünk feltöltéséhez be kell szereznünk az összegértékeket az exportból, és illesztenünk kell az osztály és a fiók oszlopokat, az alábbiak szerint.

Ha véletlenül több sor van ugyanazzal az osztállyal és fiókkal, akkor a SUMIFS függvény az összes megfelelő elem összegét adja vissza.

Amint láthatja, ha az érték A visszatérési próbálkozások száma, akkor a SUMIFS függvény megkönnyíti a többoszlopos keresések végrehajtását. De mi van akkor, ha a visszaadni próbált érték nem szám? Nos, akkor egy hagyományos keresési funkciót kell használnia, az alábbiakban leírtak szerint.

A VLOOKUP használata a SUMIFS módszerrel

Az egyik módszer a VLOOKUP és a SUMIFS egyetlen képletben történő használata. Lényegében a SUMIFS-ot használja a VLOOKUP első argumentumaként. Ezt a módszert az Excel University bejegyzés teljes körűen feltárja:

A VLOOKUP használata a CONCATENATE módszerrel

Ha nem szöveges, hanem szöveges karakterláncot próbál visszaadni, vagy a Az Excel, amely nem rendelkezik SUMIFS-szal, akkor valószínűleg elakad egy hagyományos keresési funkció, például a VLOOKUP és a CONCATENATE funkció mellett, hogy egyetlen egyedi keresési oszlopot hozzon létre. Ez a megközelítés meglehetősen jól dokumentált, de az alapötlet így hangzik: először hozzon létre egy keresési oszlopot, majd használja a VLOOKUP-ot.

Példánk egy alkalmazotti lista lesz, amint az alábbiakban látható:

Le kell töltenünk az állapotot az alkalmazottak listájáról az alább látható kis jelentésünkhöz:

Mivel az általunk visszaadni kívánt érték, az állapot, egy szöveges karakterlánc, nem pedig szám, ezért kizárjuk a SUMIFS függvény használatát. Ezért a VLOOKUP és a CONCATENATE programmal old school-ba kell mennünk.

Először egy segítő oszlopot építünk, amely alapvetően létrehozza az összesített keresési értékeket. Ez könnyen megvalósítható a CONCATENATE függvénnyel vagy a összefűzés operátorral (&). Ezt az új keresési oszlopot az alábbi B oszlop szemlélteti:

Most egyetlen keresési oszlopunk van, amelyet egy hagyományos keresési funkció, például VLOOKUP. A jelentés kitölthető úgy, hogy az új keresési tartományban található kombinált neveket keresi meg, az alábbiak szerint:

Ugyanez a megközelítés akkor használható, ha két, három vagy több keresési oszlopot kell figyelembe venni.

Következtetés

Amellett, hogy több oszlopos lekérdezéseket hajthat végre, amikor a visszatérési érték numerikus, a SUMIFS függvénynek további előnyei vannak a hagyományos keresési funkciókhoz képest. Például nullát ad vissza, ha nem található egyező érték, visszaadja az összes egyezés összegét, támogatja az összehasonlító operátorokat, és nem szakad meg, ha új oszlop kerül be a keresési és visszatérési oszlopok közé.

Tehát, ha a VLOOKUP függvényt egy keresési feladat elvégzésére szánja, fontolja meg a SUMIFS használatát. Ha hiszed, ha nem, a SUMIFS funkció csodálatos keresési funkciót biztosít.

Ha bármilyen más preferált megközelítésed van a többoszlopos keresésekre, akkor szívesen hallanánk többet … kérjük, tegyen egy megjegyzést alább.

Mintafájl

Ha a fenti képernyőképek létrehozásához használt munkafüzettel szeretne játszani, töltse le bátran:

MultColumnLookup