Az Excel VLOOKUP függvényének és a COLUMN függvénynek a kombinálásával olyan keresési képletet hozhat létre, amely több értéket ad vissza egy adatbázis vagy adattábla egyetlen sorából. Ismerje meg, hogyan hozhat létre olyan keresési képletet, amely több értéket ad vissza egyetlen adatrekordból.
A cikkben található utasítások az Excel 2019, 2016, 2013, 2010; és Excel for Microsoft 365.
Bottom Line
A keresési képlet megköveteli, hogy a COLUMN függvény be legyen ágyazva a VLOOKUP-ba. Egy függvény beágyazása magában foglalja a második függvény beírását az első függvény egyik argumentumaként.
Adja meg az oktatóanyag adatait
Ebben az oktatóanyagban a COLUMN függvény a VLOOKUP oszlopindexszám argumentumaként van megadva. Az oktatóanyag utolsó lépése a keresési képlet további oszlopokba másolása a kiválasztott részhez tartozó további értékek lekéréséhez.
Az oktatóanyag első lépése az adatok bevitele egy Excel-munkalapra. Az oktatóanyag lépéseinek követéséhez írja be az alábbi képen látható adatokat a következő cellákba:
- Írja be az adatok felső tartományát a D1–G1 cellákba.
- Írja be a második tartományt a D4–G10 cellákba.
Az oktatóanyagban létrehozott keresési feltételek és a keresési képlet a munkalap 2. sorába kerülnek.
Ez az oktatóanyag nem tartalmazza a képen látható alapvető Excel formázást, de ez nem befolyásolja a keresési képlet működését.
Hozzon létre egy elnevezett tartományt az adattáblázathoz
A megnevezett tartomány egyszerű módja annak, hogy egy képletben lévő adattartományra hivatkozzon. Az adatok cellahivatkozásainak begépelése helyett írja be a tartomány nevét.
A megnevezett tartomány használatának másik előnye, hogy a tartomány cellahivatkozásai soha nem változnak, még akkor sem, ha a képletet a munkalap más celláiba másolják. A tartománynevek az abszolút cellahivatkozások alternatívája a képletek másolásakor előforduló hibák elkerülése érdekében.
A tartománynév nem tartalmazza az adatok fejléceit vagy mezőneveit (ahogy a 4. sorban látható), csak az adatokat.
-
Emelje ki a D5 cellákat G10-ra a munkalapon.
Image -
Vigye a kurzort az A oszlop felett található Névdobozba, írja be a Table, majd nyomja meg az Enter billentyűt. A D5–G10 cellák a következő tartománynévvel rendelkeznek: Table.
Image - A VLOOKUP táblatömb argumentumának tartománynevét később ebben az oktatóanyagban használjuk.
Nyissa meg a VLOOKUP párbeszédpanelt
Bár a keresési képletet beírhatja közvetlenül a munkalap cellájába, sokan nehezen tudják egyenesen tartani a szintaxist – különösen az olyan összetett képleteknél, mint amilyen az ebben az oktatóanyagban használt.
Alternatív megoldásként használja a VLOOKUP függvényargumentumok párbeszédpanelt. Az Excel szinte minden függvényében van párbeszédpanel, ahol a függvény minden argumentuma külön sorba kerül.
-
Válassza ki a munkalap E2 celláját. Ez az a hely, ahol a kétdimenziós keresési képlet eredményei jelennek meg.
Image -
A szalagon lépjen a Képletek fülre, és válassza a Keresés és hivatkozás.
Image -
Válassza ki a VLOOKUP lehetőséget a Funkcióargumentumok párbeszédpanel megnyitásához.
Image - A Függvényargumentumok párbeszédpanelen a VLOOKUP függvény paraméterei adhatók meg.
Írja be a keresési érték argumentumot
Általában a keresési érték megegyezik az adattábla első oszlopában lévő adatmezővel. Ebben a példában a keresési érték annak a résznek a nevére vonatkozik, amelyről információt szeretne találni. A keresési értékhez használható adattípusok szöveges adatok, logikai értékek, számok és cellahivatkozások.
Abszolút cellahivatkozások
Ha képleteket másol az Excel programban, a cellahivatkozások az új helynek megfelelően módosulnak. Ha ez megtörténik, a D2, a keresési érték cellahivatkozása megváltozik, és hibákat hoz létre az F2 és G2 cellákban.
Az abszolút cellahivatkozások nem változnak képletek másolásakor.
A hibák elkerülése érdekében alakítsa át a D2 cellahivatkozást abszolút cellahivatkozássá. Abszolút cellahivatkozás létrehozásához nyomja meg az F4 billentyűt. Ez dollárjeleket ad a cellahivatkozás köré, például $D$2.
-
A Függvényargumentumok párbeszédpanelen helyezze a kurzort a lookup_value szövegmezőbe. Ezután a munkalapon válassza ki a cella D2 elemet, hogy hozzáadja ezt a cellahivatkozást a lookup_value elemhez. A D2 cellába kerül az alkatrésznév beírása.
Image -
A beszúrási pont áthelyezése nélkül nyomja meg a F4 billentyűt a D2 $D$2 abszolút cellahivatkozássá konvertálásához.
Image - Hagyja nyitva a VLOOKUP függvény párbeszédpanelt az oktatóanyag következő lépéséhez.
Írja be a táblázattömb argumentumát
A táblázattömb az az adattábla, amelyben a keresési képlet megkeresi a kívánt információt. A táblázattömbnek legalább két adatoszlopot kell tartalmaznia.
Az első oszlop tartalmazza a keresési érték argumentumot (amelyet az előző szakaszban állítottunk be), míg a második oszlopban a keresési képlet keresi az Ön által megadott információkat.
A táblázattömb argumentumát vagy az adattábla cellahivatkozásait tartalmazó tartományként vagy tartománynévként kell megadni.
Ha hozzá szeretné adni az adattáblázatot a VLOOKUP függvényhez, helyezze a kurzort a table_array szövegmezőbe a párbeszédpanelen, és írja be a Tableaz argumentum tartománynevének megadásához.
Az OSZLOP funkció beágyazása
Általában a VLOOKUP csak az adattábla egy oszlopából ad vissza adatokat. Ezt az oszlopot az oszlopindexszám argumentum állítja be. Ebben a példában azonban három oszlop van, és az oszlop indexszámát a keresési képlet szerkesztése nélkül kell módosítani. Ennek eléréséhez helyezze be a COLUMN függvényt a VLOOKUP függvénybe Col_index_num argumentumként.
Függvények beágyazásakor az Excel nem nyitja meg a második függvény párbeszédpanelét az argumentumok megadásához. Az OSZLOP függvényt kézzel kell megadni. A COLUMN függvénynek csak egy argumentuma van, a Referencia argumentum, amely egy cellahivatkozás.
A COLUMN függvény a Referencia argumentumként megadott oszlop számát adja vissza. Az oszlop betűjét számmá alakítja.
Egy cikk árának megállapításához használja az adattábla 2. oszlopában található adatokat. Ez a példa a B oszlopot használja referenciaként, hogy 2-t szúrjon be a Col_index_num argumentumba.
-
A Funkció argumentumai párbeszédpanelen helyezze a kurzort a Col_index_num szövegmezőbe, és írja be a COLUMN(. (Ügyeljen bele a nyitott kerek zárójelbe.)
Image -
A munkalapon válassza a B1 cella elemet, hogy ezt a cellahivatkozást Referencia argumentumként adja meg.
Image - Írjon be egy záró kerek zárójelet a COLUMN funkció befejezéséhez.
Írja be a VLOOKUP tartománykeresési argumentumot
A VLOOKUP Range_lookup argumentuma egy logikai érték (IGAZ vagy HAMIS), amely jelzi, hogy a VLOOKUP pontos vagy közelítő egyezést talál-e a Lookup_value értékkel.
- TRUE vagy Kihagyott: A VLOOKUP a Lookup_value szoros egyezését adja vissza. Ha nem található pontos egyezés, a VLOOKUP a következő legnagyobb értéket adja vissza. A Table_array első oszlopában lévő adatokat növekvő sorrendben kell rendezni.
- FALSE: A VLOOKUP a Lookup_value pontos egyezését használja. Ha a Table_array első oszlopában két vagy több olyan érték található, amelyek megegyeznek a keresési értékkel, akkor a rendszer az első talált értéket használja. Ha nem található pontos egyezés, akkor N/A hibaüzenet jelenik meg.
Ebben az oktatóanyagban egy adott hardverelemre vonatkozó konkrét információkat keresünk, így a Range_lookup értéke FALSE.
A Függvényargumentumok párbeszédpanelen helyezze a kurzort a Range_lookup szövegmezőbe, és írja be a False parancsot, hogy a VLOOKUP utasítsa az adatok pontos egyezését.
A keresési képlet befejezéséhez és a párbeszédpanel bezárásához válassza az OK lehetőséget. Az E2 cella N/A hibát tartalmaz, mert a keresési feltételek nem kerültek beírásra a D2 cellába. Ez a hiba átmeneti. A hiba javításra kerül, amikor a keresési feltételeket hozzáadja az oktatóanyag utolsó lépéséhez.
Másolja ki a keresési képletet, és adja meg a kritériumokat
A keresési képlet az adattábla több oszlopából kéri le az adatokat egyszerre. Ehhez a keresési képletnek minden olyan mezőben szerepelnie kell, amelyről információt szeretne.
Az adattábla 2., 3. és 4. oszlopából való adatok lekéréséhez (ár, cikkszám és a szállító neve) írjon be egy részleges nevet a Lookup_value mezőbe.
Mivel az adatok szabályos mintában vannak elhelyezve a munkalapon, másolja a keresési képletet a E2 cellába cells F2 és G2 A képlet másolásakor az Excel frissíti a relatív cellahivatkozást az OSZLOP függvényben (B1 cella), hogy tükrözze a képlet új helyét. Az Excel nem változtatja meg az abszolút cellahivatkozást (például $D$2) és a megnevezett tartományt (Table) a képlet másolásakor.
Többféleképpen másolhat adatokat az Excel programban, de a legegyszerűbb a kitöltő fogantyú használata.
-
Válassza ki az E2 cellát, ahol a keresési képlet található, hogy ez legyen az aktív cella.
Image -
Húzza át a kitöltő fogantyút a G2 cellába. Az F2 és G2 cellák az E2 cellában található N/A hibát jelenítik meg.
Image -
Ha a keresőképletekkel szeretné lekérni az adatokat az adattáblázatból, válassza ki a munkalapon a D2 cella elemet, írja be a Widget Enter.
Image A következő információk jelennek meg az E2–G2 cellákban.
- E2: 14,76 USD – egy widget ára
- F2: PN-98769 – egy widget cikkszáma
- G2: Widgets Inc. – a widgetek szállítójának neve
-
A VLOOKUP tömbképlet teszteléséhez írja be a többi rész nevét a D2 cellába, és figyelje meg az eredményeket az E2 - G2 cellákban.
Image - A keresési képletet tartalmazó minden cella más-más adatot tartalmaz a keresett hardverelemről.
A VLOOKUP függvény olyan beágyazott függvényekkel, mint a COLUMN, hatékony módszert biztosít az adatok táblán belüli megkeresésére, más adatok referenciaként történő felhasználásával.
