Az Excel VLOOKUP függvényének a COLUMN függvénnyel való kombinálásával létrehozhatunk egy keresési képletet, amely lehetővé teszi, hogy több értéket adjon vissza egy adatbázison vagy adatlap egyetlen sorában.
A fenti képen bemutatott példában a keresési képlet megkönnyíti az összes érték - például ár, alkatrészszám és szállító - visszaszolgáltatását különböző hardverelemekhez.
01. oldal, 10
Visszatérő több érték az Excel VLOOKUP segítségével
Az alábbi lépéseket követve létrehozza a fenti képen látható keresési képletet, amely több értéket ad vissza egyetlen adatrekordból.
A keresési képlet megköveteli, hogy a COLLOU függvény beágyazódjon a VLOOKUP belsejébe.
A függvény beágyazása magában foglalja a második funkció beillesztését az első funkció egyik argumentumaként .
Ebben a bemutatóban a Column függvényt a VLOOKUP oszlop indexszám argumentumaként adjuk meg.
A tutorial utolsó lépése a keresési képlet átmásolása további oszlopokra, hogy a kiválasztott rész további értékeit lekérdezze.
Tartalomjegyzék
- A bemutatóadatok beírása
- Megnevezett tartomány létrehozása az adat táblázathoz
- A VLOOKUP funkció indítása
- A Lookup Value argumentum megadása abszolút sejtes referenciák használatával
- Belépés a táblagörbe argumentumba
- A beágyazott COLUMN függvény beírása
- A VLOOKUP funkció befejezése
- A keresési képlet másolása a kitöltő fogantyúval
- Az adatok lekérése a keresési képlet segítségével
02. oldal, 10
Adja meg a bemutatóadatokat
A tutorial első lépése az adatok Excel munkalapba történő beírása .
A tutorial lépéseinek követéséhez írja be a fenti képen látható adatokat a következő cellákba .
- Adja meg az adatok felső tartományát a D1-G1 cellákba
- Adja meg a második tartományt a D4-G10 cellákba
Az ebben a bemutatóban létrehozott keresési feltételeket és keresési képletet a munkalap 2. sorában kell megadni.
A bemutató nem tartalmazza a képen látható formázást, de ez nem befolyásolja a keresési képlet működését.
A fentiekhez hasonló formázási lehetőségekkel kapcsolatos információk megtalálhatók ebben a Basic Excel Formatting Tutorialban .
Bemutató lépések
- Adja meg a fenti képen látható adatokat a D1-G10 cellákba
03. oldal, 10
Megnevezett tartomány létrehozása az adat táblázathoz
A megnevezett tartomány egy egyszerű módja annak, hogy egy adott képlet tartományára utaljon. Ahelyett, hogy beírná az adatok cella hivatkozásait , csak beírhatja a tartomány nevét.
A második előny a megadott tartomány használatára az, hogy az adott tartományhoz tartozó cellahivatkozások soha nem változnak még akkor sem, ha a képlet átmásolódik a munkalap más celláira.
A tartománynevek ezért alternatívak az abszolút sejtek referenciáinak használatára, amelyek megakadályozzák a hibákat a képletek másolásakor.
Megjegyzés: A tartománynév nem tartalmazza az adatok címét vagy mezőnevét ( 4. sor), de csak az adatokat.
Bemutató lépések
- A munkalapon jelölje ki a D5-G10 cellákat a munkalap kiválasztásához
- Kattintson az A oszlop felett található Név mezőre
- Írja be a "Táblázat" (nincs idézet) a Név mezőbe
- Nyomja meg az ENTER gombot a billentyűzeten
- A D5-G10 cellák rendelkeznek a "Táblázat" tartománynevével. A VLOOKUP tábla-array argumentum nevét később a tutorialban fogjuk használni
04. oldal, 10
A VLOOKUP párbeszédpanel megnyitása
Bár lehet, hogy a keresési képletünket közvetlenül a munkalap egyik cellájába írjuk, sokan nehezen tartják a szintaxist egyenesen - különösen olyan összetett képlet esetén, mint amilyet ebben a leírásban használunk.
Ebben az esetben alternatívaként a VLOOKUP párbeszédablakot kell használni. Az Excel összes funkciójának szinte minden olyan párbeszédpanelén keresztül, amely lehetővé teszi, hogy mindegyik függvény argumentumát egy külön sorra írja be.
Bemutató lépések
- Kattintson a munkalap E2 cellájára - a hely, ahol megjelenik a kétdimenziós lekérdezési képlet eredményei
- Kattintson a szalag Formulák lapjára
- Kattintson a Keresés és referencia lehetőségre a szalagon, hogy megnyissa a funkció legördülő listáját
- Kattintson a VLOOKUP elemre a listában, hogy megnyissa a funkció párbeszédablakát
05. oldal, 10
A Lookup Value argumentum megadása abszolút sejtes referenciák használatával
Általában a keresési érték megegyezik az adatlap első oszlopának adat mezőjével .
Példánkban a keresési érték azt a hardverrész nevét jelenti, amelyre információt akarunk találni.
A keresési érték megengedhető adatfajtái a következők:
- szöveges adatok
- logikai érték (csak TRUE vagy FALSE)
- egy szám
- egy cella hivatkozás a munkalap egy értékére
Ebben a példában meg kell adni a cella hivatkozást, ahol a rész nevét találjuk - D2 cellát.
Abszolút Cell Referenciák
A bemutató későbbi szakaszában a keresési képletet másolja az E2 cellában az F2 és G2 cellákra.
Normális esetben, ha a képleteket Excel-be másolták, a cellák referenciái megváltoztak, hogy azok új helyet tükrözzenek.
Ha ez megtörténik, a D2 - a keresési érték cellahivatkozása - a képlet átmásolásakor megváltozik, hibát okozva az F2 és G2 cellákban.
A hibák megelőzése érdekében a D2 cellahivatkozást abszolút cella-referenciává alakítjuk.
Az abszolút sejtes hivatkozások nem változnak a képletek másolásakor.
Az abszolút cellahivatkozásokat a billentyűzet F4 billentyűjének megnyomásával hozza létre. Ehhez dollárjeleket ad a cella hivatkozás, például $ D $ 2
Bemutató lépések
- Kattintson a lookup_value sorra a párbeszédpanelen
- Kattintson a D2 cellára, ha ezt a cellahivatkozást hozzáadja a lookup_value vonalhoz. Ez a cella, ahol beírjuk a rész nevét, amelyről információkat keresünk
- A beillesztési pont mozgatása nélkül nyomja meg az F4 billentyűt a billentyűzeten, hogy a D2 értéket a $ D $ 2 abszolút cellahivatkozásra konvertálja
- Hagyja a VLOOKUP függvény párbeszédpanelt nyitva a következő lépéshez a bemutatóban
06. oldal, 10
Belépés a táblagörbe argumentumba
A táblázat-tömb az adatok táblázata , amelyet a keresési képlet keresi a kívánt információ megtalálására.
A táblázatos tömbnek legalább két oszlopadatot kell tartalmaznia.
- az első oszlop tartalmazza a keresési érték argumentumát (az előző lépés a tutorialban)
- a második és minden további oszlopot a keresési képlet keresi meg a megadott információk megtalálásához.
A táblázat-tömb argumentumot úgy kell megadni, hogy az adattáblázat cellahivatkozásait vagy tartománynevét tartalmazza.
Ehhez a példához a bemutató 3. lépésben létrehozott tartománynevet fogjuk használni.
Bemutató lépések
- Kattintson a párbeszédpanelen a table_array sorra
- Írja be a "Táblázat" (nincs idézet) mezőbe az argumentum tartománynevét
- Hagyja a VLOOKUP függvény párbeszédpanelt nyitva a következő lépéshez a bemutatóban
07. oldal, 10
A COLUMN funkció beágyazása
Normál esetben a VLOOKUP csak az adattáblázat egyik oszlopából származó adatokat adja vissza, és ezt az oszlopot az oszlop indexszám argumentum határozza meg.
Ebben a példában azonban három oszlopunk van, amelyekből vissza szeretnénk adni az adatokat, így szükségünk van arra, hogy egyszerűen változtassuk meg az oszlop indexszámát a keresési képlet szerkesztése nélkül.
Ez az a hely, ahol a COLUMN függvény bejövő. Az oszlop index szám argumentumának bevitelével megváltozik, ahogy a keresési képletet átmásolja a D2 celláról az E2 és F2 cellákra a tutorial későbbi részében.
Fészkelő funkciók
A COLUMN függvény tehát a VLOOKUP oszlop indexszámú argumentumaként működik .
Ezt a VLOOKUP belső COLUMN funkciójának fészkelésével lehet elérni a párbeszédpanel Col_index_num sorában.
A COLUMN funkció beírása manuálisan
A fészkelő funkciók esetén az Excel nem engedi meg a második függvény párbeszédablakának megnyitását az argumentumok beadásához.
A COLUMN függvényt ezért kézzel kell megadni a Col_index_num sorban.
A COLUMN függvény csak egy argumentummal rendelkezik - a Referencia argumentum, amely cellahivatkozás.
A COLUMN függvény referencia- argumentuma kiválasztása
A COLUMN függvény feladata, hogy a Referencia argumentumként megadott oszlop számát adja vissza.
Más szavakkal, az oszlop betűjét olyan számmal alakítja át, amelynek az A oszlopa az első oszlop, a B oszlop második, és így tovább.
Mivel az első adatmezőt, amelyet vissza szeretnénk küldeni, az elem árát - amely az adatlap 2. oszlopában található - választhatjuk a B oszlop bármely cellájára vonatkozó referenciaadat referencia argumentumként annak érdekében, hogy megkapjuk a 2. számot a Col_index_num argumentum.
Bemutató lépések
- A VLOOKUP függvény párbeszédpanelen kattintson a Col_index_num sorra
- Írja be a függvénynév oszlopot , amelyet egy nyitott kerek " ( "
- Kattintson a B1 cellára a munkalapon, és adja meg ezt a cella hivatkozást Referencia argumentumként
- A COLUMN funkció befejezéséhez írja be a záró kerek zárójelét " )
- Hagyja a VLOOKUP függvény párbeszédpanelt nyitva a következő lépéshez a bemutatóban
08. oldal, 10
Belépés a VLOOKUP területkeresési argumentumba
A VLOOKUP Range_lookup argumentuma logikai érték (csak TRUE vagy FALSE), amely jelzi, hogy a VLOOKUP-nak pontos vagy hozzávetőleges egyezést kell találnia a Lookup_value értékkel.
- Ha TRUE, vagy ha ez az argumentum kihagyásra kerül, a VLOOKUP vagy egy pontos egyezést ad a Lookup_value értékkel, vagy ha nem talál egy pontos egyezést, a VLOOKUP visszatér a következő legnagyobb értékhez. Ennek a képletnek a megadásához a Table_array első oszlopában lévő adatokat növekvő sorrendbe kell sorolni.
- Ha FALSE, akkor a VLOOKUP csak a Lookup_value kifejezést használja. Ha a Table_array első oszlopában két vagy több érték található, amelyek megfelelnek a keresési értéknek, akkor az első talált érték kerül felhasználásra. Ha pontos egyezést nem talál, akkor # N / A hibát kap.
Ebben a leírásban, mivel konkrét hardverelemre vonatkozó konkrét információt keresünk, a Range_lookup egyenlő False értékkel.
Bemutató lépések
- Kattintson a párbeszédpanel Range_lookup sorára
- Írja be a False szót ebben a sorban, jelezve, hogy azt szeretnénk, hogy a VLOOKUP pontosan illeszkedjen a keresett adatokhoz
- Az OK gombra kattintva töltse ki a keresési képletet és zárja be a párbeszédpanelt
- Mivel még nem adtuk meg a keresési kritériumot a D2 cellába, a # N / A hiba az E2 cellában jelenik meg
- Ez a hiba akkor kerül kijavításra, amikor hozzáadjuk a keresési feltételeket a bemutató utolsó lépéseként
09. oldal, 10
A keresési képlet másolása a kitöltő fogantyúval
A keresési képlet célja, hogy egyszerre tölti le az adatokat az adatlap több oszlopából .
Ehhez a keresési képletnek minden olyan mezőben meg kell határoznia, ahonnan információt akarunk.
Ebben a leírásban azt szeretnénk, hogy az adattáblázat 2., 3. és 4. oszlopából adatokat szerezzen be, azaz az árat, a cikkszámot és a szállító nevét, amikor a résznév nevét Lookup_value-nak nevezzük.
Mivel az adatokat a munkalapban rendszeres mintázattal látjuk el, a keresési képletet másolhatjuk az E2 cellában az F2 és G2 cellákra.
A képlet átmásolásakor az Excel frissíti a relatív cellahivatkozást a COLUMN függvényben (B1), hogy tükrözze a képlet új helyét.
Ezenkívül az Excel nem változtatja meg a $ D $ 2 abszolút cellahivatkozást és a megadott tartománytáblát a képlet átmásolásakor.
Az adatok Excel-ben történő másolásának több módja van, de a legegyszerűbb módja a Fill Handle használata .
Bemutató lépések
- Kattintson az E2 cellára - ahol a keresési képlet található -, hogy az aktív cella legyen
- Helyezze az egérmutatót a jobb alsó sarokban lévő fekete négyzet fölé. A mutató " + " pluszjelre változik - ez a kitöltő fogantyú
- Kattintson a bal egérgombbal, és húzza át a kitöltőt a G2 cellára
- Engedje fel az egérgombot, és az F3 cellának tartalmaznia kell a kétdimenziós keresési képletet
- Ha helyesen történik, akkor az F2 és a G2 celláknak tartalmazniuk kell az E2 cellában jelen lévő #N / A hibát is
10/10
A keresési feltételek megadása
Ha a keresési képletet átmásoltuk a szükséges cellákra , akkor az adatokat az adatbázistől lehet letölteni.
Ehhez írja be a lekérni kívánt elem nevét a Lookup_value cellába (D2), majd nyomja meg az ENTER billentyűt.
Miután elkészült, a keresési képletet tartalmazó minden cellának tartalmaznia kell egy másik adatot a keresett hardverelemre vonatkozóan.
Bemutató lépések
- Kattintson a D2 cellára a munkalapon
- Írja be a Widgetet a D2 cellába, majd nyomja meg az ENTER billentyűt a billentyűzeten
- Az E2-G2 cellákban az alábbi információkat kell megjeleníteni:
- E2 - $ 14.76 - a widget ára
- F2 - PN-98769 - egy widget részszáma
- G2 - Widgets Inc. - a géprészek szállítójának neve
- A VLOOKUP tömb-formula további tesztelésével más részek nevét D2-es cellába írjuk, és az eredményeket az E2-G2 cellákban
Ha egy hibaüzenet, például a #REF! megjelenik az E2, F2 vagy G2 cellákban, ez a VLOOKUP hibaüzenetek listája segíthet meghatározni a probléma helyét.