Az Excel-ek kombinálásávalVLOOKUP funkció aOSZLOP funkcióval létrehozhatunk egy keresési képletet, amely lehetővé teszi, hogy több értéket adjon vissza egy adatbázison vagy adatlap egy sorában.
01. oldal, 10Visszaté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 OSZLOP funkció be van ágyazva VLOOKUP. 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 OSZLOP funkció beíródik oszlop index szám érv VLOOKUP. 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.
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 a legmagasabb adattartományt sejtek D1 nak nek G1.
- Adja meg a második tartományt sejtek D4 nak nek G10.
A keresési feltételeket és a lekérdezési képletet, amelyet ebben a bemutatóban hoztak létre, bekerülnek 2. sor a munkalapon.
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.
03. oldal, 10Megnevezett 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.
A tartománynév nem tartalmazza az adatok címét vagy mezőnevét (4. sor), de csak az adatokat.
- Kiemel sejtek D5 nak nek G10 a munkalapon kiválasztani őket.
- Kattintson a Név mező fent található oszlopban.
- típus asztal ba,-be Név mező.
- megnyomni a Belép gombot a billentyűzeten.
- D5 sejtek nak nek G10 most már a tartomány neve asztal. A nevet a VLOOKUP táblázat tömb argumentum később a bemutatóban.
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.
Egy alternatíva, ebben az esetben a VLOOKUP párbeszédablak. 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 E2 sejtet a munkalapon - a hely, ahol megjelenik a kétdimenziós lekérdezési képlet eredményei.
- Kattintson a Formulák fülön a szalag.
- Kattintson a Keresés és referencia lehetőség a szalag megnyitni a funkció legördülését.
- Kattintson VLOOKUP a listában megnyithatja a funkció párbeszédablakát.
A Lookup Value argumentum megadása
Általában a Nézz fel érték megegyezik az adattáblázat első oszlopában lévő adatmezővel. Példánkban a Nézz fel érték a hardverrész nevét jelenti, amelyre információt akarunk találni. A megengedett adatok típusa a Nézz fel érték szöveges adatok, logikai értékek, számok és cellahivatkozások.
Abszolút Cell Referenciák
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, D2, a cella referenciája a Nézz fel érték, a formátum másolásakor megváltozik, és hibákat okoz sejtek F2 és G2.
Az abszolút sejtes hivatkozások nem változnak a képletek másolásakor.
A hibák elkerülése érdekében konvertálni fogjuk a cellahivatkozást D2 egy abszolút cella referenciává. Az abszolút sejtes hivatkozásokat a F4 gombot a billentyűzeten. Ehhez dollárjeleket ad a cella-referencia körül, például $ D $ 2.
- Kattintson a keresési_érték a párbeszédablakban.
- Kattintson sejt D2 hogy ezt a cella hivatkozást hozzáadja keresési_érték line - 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 a gombot F4 billentyűt a billentyűzeten konvertálni D2 az abszolút cella referenciájába $ D $ 2.
- Hagyd a VLOOKUP funkció párbeszédpanel megnyílik a következő lépéshez a bemutatóban.
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), míg a második 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.
- Kattintson a tábla_tömb a párbeszédablakban.
- típus asztal adja meg az argumentum tartománynevét.
- Hagyd a VLOOKUP funkció párbeszédpanel megnyílik a következő lépéshez a bemutatóban.
A COLUMN funkció beágyazása
Normális esetben VLOOKUP csak adatokat ad vissza egy adattáblázat egy oszlopából, és ezt az oszlopot a oszlop index száma érv.
Ebben a példában azonban három oszlopunk van, amelyekből vissza szeretnénk adni az adatokat, így egyszerűen meg kell változtatnunk az adatokat oszlop index száma anélkül, hogy szerkesztenénk a keresési képletünket - ez az, ahol a OSZLOP funkció jön be.
Azáltal, hogy beírja oszlop index száma argumentum, akkor megváltozik, ha a keresési képlet átmásolódik D2 sejt nak nek E2 sejtek és F2 később a bemutatóban.
Fészkelő funkciók
A OSZLOP ezért a funkciója így működik VLOOKUP„s oszlop index száma érv; ezt a fészkelés fészkével végezzük OSZLOP funkciója belsejében VLOOKUP ban,-ben oszlopszám párbeszédablak sorát.
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 OSZLOP A funkciót tehát kézzel kell beírni a oszlopszám vonal. A OSZLOP funkció csak egy érv - a Referencia érv, amely cellahivatkozás.
A COLUMN függvény referencia-argumentuma kiválasztása
A OSZLOP A függvény feladata az, hogy visszaadja az adott oszlop számát Referencia érv. Más szavakkal, az oszlop betűt számjegygé alakítja oszlopban az első oszlop, B oszlop a második és így tovább.
Az első adatmező, amit vissza akarunk küldeni, az elem árát jelenti, amely az adattáblázat 2. oszlopában található. Kiválaszthatjuk a cella-referenciát bármelyik cella számára B oszlop mint a Referencia Érv, hogy megkapja a számot 2 a oszlopszám érv.
- Ban,-ben VLOOKUP funkció párbeszédablakban kattintson a oszlopszám vonal.
- Írja be a funkció nevét oszlop majd egy nyitott kerek tartó.
- Kattintson sejt B1 a munkalapon a cella hivatkozásként történő beírásához Referencia érv.
- A típus záró kerekek befejezni a OSZLOP funkció
- Hagyd a VLOOKUP funkció párbeszédpanel megnyílik a következő lépéshez a bemutatóban.
Belépés a VLOOKUP területkeresési argumentumba
VLOOKUP„s tartományban_keres logikai érték (IGAZ vagy HAMIS), amely jelzi, hogy szeretné-e VLOOKUP hogy pontos vagy hozzávetőleges egyezést találjon a keresési_érték.
- Ha IGAZ vagy ha ezt az érvet elhagyják: VLOOKUP visszaadja vagy egy pontos egyezést a keresési_értékVagy ha pontos egyezés nem található, VLOOKUP a következő legnagyobb értéket adja vissza. Ennek a képletnek a megadásához az első oszlopban lévő adatokat tábla_tömb növekvő sorrendben kell rendezni.
- Ha HAMIS: VLOOKUP csak a pontos egyezést használhatja a keresési_érték. Ha két vagy több érték van az első oszlopban tábla_tömb amelyek megfelelnek a keresési értéknek, az első talált értéket használják. Ha pontos egyezést nem talál, a # N / A hiba kerül vissza.
Ebben a bemutatóban, mivel konkrét hardverelemre vonatkozó konkrét információt keresünk, meg fogjuk határozni tartományban_keres egyenlő Hamis.
- Kattintson a tartományban_keres a párbeszédablakban.
- Írja be a szót Hamis ebben a sorban annak jelzésére, hogy azt akarjuk VLOOKUP hogy pontosan egyezzen meg a keresett adatokkal.
- Kattints rendben a lekérdezési képlet befejezéséhez és a párbeszédpanel bezárásához.
- Mivel még nem léptünk be a keresési kritériumba sejt D2 egy # N / A hiba jelen lesz E2 sejtet.
- Ez a hiba akkor kerül kijavításra, amikor hozzáadjuk a keresési feltételeket a bemutató utolsó lépéseként.
A keresési képlet másolása
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 bemutatóban azt szeretnénk, hogy az adatokat letöltsük 2. oszlop, 3, és 4 az adattáblázat - azaz az ár, a cikkszám és a szállító neve, amikor részleges nevet adunk be keresési_érték.
Mivel az adatokat a munkalapban rendszeres mintázattal látjuk el, a keresési képletet másolhatjuk be E2 sejtet nak nek sejtek F2 és G2.
A képlet átmásolásakor az Excel frissíti a relatív cellahivatkozást a OSZLOP funkció (sejt B1), hogy tükrözze a képlet új helyét.
Ezenkívül az Excel nem változtatja meg az abszolút cellahivatkozást $ D $ 2 és a megnevezett tartomány asztal a képletet másolja.
Az adatok Excelben történő másolásának több módja van, de valószínűleg a legegyszerűbb módja az Fill Handle.
- Kattintson E2 sejtet, ahol a keresési képlet található, hogy az aktív cellát.
- Helyezze a egér mutató át a fekete négyzet a jobb alsó sarokban - a mutató egy plusz jelre változik, hogy megjelölje a kitöltő fogantyút.
- Kattintson a bal egérgombbal és a húzza a kitöltő fogantyút egészen át sejt G2.
- Kiadás az egérgombot és sejt F3 tartalmaznia kell a kétdimenziós keresési formulát.
- Ha helyesen történik, sejtek F2 és G2 most tartalmaznia kell a # N / A hiba, ami jelen van E2 sejtet.
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 az elem neve szeretné letölteni a keresési_érték sejt (D2) és nyomja meg a gombot Belép gombot a billentyűzeten.
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.
- Kattintson D2 sejt a munkalapon.
- típus Widget -ba D2 sejt és nyomja meg a gombot Belép gombot a billentyűzeten.
- A következő információkat kell megjeleníteni E2 sejtek nak nek G2.
- E2: 14,76 dollár - egy widget ára
- F2: PN-98769 - egy widget részszáma
- G2: Widgets Inc. - a widgetek szállítójának neve
- Teszteld a VLOOKUP töredék formula további beírásával a többi alkatrész nevét D2 sejt és figyelemmel kíséri az eredményeket E2 sejtek nak nek G2
