Az Excel VLOOKUP funkciójának összes titka, amellyel egy táblázatban találhat adatokat, és kivonhatja azokat egy másikba

2024 Szerző: Malcolm Clapton | [email protected]. Utoljára módosítva: 2023-12-17 03:59

A cikk elolvasása után nemcsak az Excel-táblázatban található adatok megkeresését és egy másikba való kibontását ismerheti meg, hanem a VLOOKUP függvénnyel együtt használható technikákat is.

Amikor Excelben dolgozik, nagyon gyakran meg kell találni az adatokat az egyik táblában, és ki kell bontani egy másikba. Ha még mindig nem tudja, hogyan kell ezt megtenni, akkor a cikk elolvasása után nemcsak megtanulja, hogyan kell ezt megtenni, hanem azt is megtudhatja, milyen feltételek mellett tudja kinyomni a rendszerből a maximális teljesítményt. A legtöbb nagyon hatékony technikát, amelyeket a VLOOKUP funkcióval együtt kell használni, megvizsgáljuk.

Még ha évek óta használja a VLOOKUP funkciót, nagy valószínűséggel ez a cikk hasznos lesz az Ön számára, és nem hagy közömbösen. Például informatikusként, majd informatikusként 15 éve használom a VLOOKUP-ot, de minden árnyalattal csak most tudtam megbirkózni, amikor elkezdtem professzionálisan Excelt tanítani.

VLOOKUP a rövidítése vfüggőleges NSellenőrzés. Hasonlóképpen, VLOOKUP – Függőleges KERESÉS. Már a függvény neve is arra utal, hogy a táblázat soraiban keres (függőlegesen - a sorok felett iterál és az oszlopot rögzíti), és nem az oszlopokban (vízszintesen - az oszlopok felett iterál és a sort rögzíti). Meg kell jegyezni, hogy a VLOOKUP-nak van egy húga - egy csúnya kiskacsa, akiből soha nem lesz hattyú - ez a HLOOKUP funkció. A HLOOKUP, ellentétben a VLOOKUP-pal, vízszintes keresést végez, de az Excel koncepciója (és valójában az adatszervezés fogalma) azt jelenti, hogy a táblázatok kevesebb oszlopot és sokkal több sort tartalmaznak. Éppen ezért sokszor gyakrabban kell keresnünk a sorok, mint az oszlopok szerint. Ha túl gyakran használja a HLO függvényt az Excelben, akkor valószínűleg nem értett valamit ebben az életben.

Szintaxis

A VLOOKUP funkciónak négy paramétere van:

= VLOOKUP (;; [;]), itt:

- a kívánt érték (ritkán) vagy hivatkozás a kívánt értéket tartalmazó cellára (az esetek túlnyomó többségében);

- hivatkozás egy cellatartományra (kétdimenziós tömb), amelynek ELSŐ (!) oszlopában a paraméterértéket keresi;

- oszlopszám abban a tartományban, ahonnan az érték visszakerül;

- ez egy nagyon fontos paraméter, amely választ ad arra a kérdésre, hogy a tartomány első oszlopa növekvő sorrendben van-e rendezve. Ha a tömb rendezett, akkor az IGAZ vagy 1 értéket adjuk meg, ellenkező esetben - FALSE vagy 0. Ha ezt a paramétert kihagyjuk, akkor az alapértelmezett értéke 1.

Lefogadom, hogy azok közül, akik a VLOOKUP függvényt pelyhesnek ismerik, a negyedik paraméter leírásának elolvasása után kényelmetlenül érezhetik magukat, hiszen hozzászoktak, hogy kicsit más formában látják: általában pontos egyezésről beszélnek, amikor keresés (HAMIS vagy 0) vagy tartománykeresés (IGAZ vagy 1).

Most meg kell erőltetnie magát, és többször el kell olvasnia a következő bekezdést, amíg a végére nem érzi az elhangzottak értelmét. Ott minden szó fontos. A példák segítenek kitalálni.

Hogyan működik pontosan a VLOOKUP képlet?

• I. képlettípus. Ha az utolsó paramétert kihagyjuk, vagy 1-gyel adjuk meg, akkor a VLOOKUP azt feltételezi, hogy az első oszlop növekvő sorrendben van rendezve, így a keresés abba a sorban áll le, közvetlenül megelőzi a kívántnál nagyobb értéket tartalmazó sort … Ha nem található ilyen karakterlánc, akkor a tartomány utolsó sora kerül visszaadásra.

  

• Forma II. Ha az utolsó paraméter 0, akkor a VLOOKUP szekvenciálisan megvizsgálja a tömb első oszlopát, és azonnal leállítja a keresést, amikor megtalálja az első pontos egyezést a paraméterrel, ellenkező esetben a # N / A (# N / A) hibakód visszakerül.

  

Képletek munkafolyamatok

VPR I. típus

VPR II

Következmények az I. formájú képletekhez

1. A képletek felhasználhatók az értékek tartományok közötti elosztására.
2. Ha az első oszlop ismétlődő értékeket tartalmaz, és megfelelően van rendezve, akkor az utolsó ismétlődő értékeket tartalmazó sor kerül visszaadásra.
3. Ha olyan értéket keresünk, amely nyilvánvalóan nagyobb, mint amit az első oszlop tartalmazhat, akkor könnyen megtalálhatja a táblázat utolsó sorát, amely igen értékes lehet.
4. Ez a nézet csak akkor adja vissza a # N / A hibát, ha nem talál a kívánt értéknél kisebb vagy azzal egyenlő értéket.
5. Meglehetősen nehéz megérteni, hogy a képlet rossz értékeket ad vissza, ha a tömb nincs rendezve.

Következmények a II. típusú képletekhez

Ha a kívánt érték többször előfordul a tömb első oszlopában, akkor a képlet az első sort választja ki a későbbi adatlekéréshez.

VLOOKUP teljesítmény

Elérkezett a cikk csúcspontjához. Úgy tűnik, mi a különbség, ha nullát vagy egyet adok meg utolsó paraméterként? Alapvetően természetesen mindenki nullát jelöl, hiszen ez elég praktikus: nem kell törődni a tömb első oszlopának rendezésével, azonnal láthatja, hogy az értéket megtaláltuk-e vagy sem. De ha több ezer VLOOKUP képlet van a lapján, akkor észre fogja venni, hogy a VLOOKUP II lassú. Ugyanakkor általában mindenki azt gondolja:

• Szükségem van egy erősebb számítógépre;
• Kell egy gyorsabb képlet, pl sokan ismerik az INDEX + MATCH-t, ami állítólag 5-10%-al gyorsabb.

És kevesen gondolják, hogy amint elkezdi használni az I. típusú VLOOKUP-ot, és gondoskodik arról, hogy az első oszlop bármilyen módon rendezve legyen, a VLOOKUP sebessége 57-szeresére nő. Szavakkal írok - ÖTVENHÉTSZER! Nem 57%, hanem 5700%. Ezt a tényt meglehetősen megbízhatóan ellenőriztem.

Az ilyen gyors munka titka abban rejlik, hogy egy rendkívül hatékony keresési algoritmus alkalmazható egy rendezett tömbön, amit bináris keresésnek neveznek (felező módszer, dichotómia módszer). Tehát az I. típusú VLOOKUP alkalmazza, a II. típusú VLOOKUP pedig mindenféle optimalizálás nélkül keres. Ugyanez igaz a MATCH függvényre, amely hasonló paramétert tartalmaz, és a LOOKUP függvényre, amely csak rendezett tömbökön működik, és a Lotus 1-2-3-mal való kompatibilitás érdekében szerepel az Excelben.

A képlet hátrányai

A VLOOKUP hátrányai nyilvánvalóak: egyrészt csak a megadott tömb első oszlopában keres, másrészt csak ettől az oszloptól jobbra. És amint érti, megtörténhet, hogy a szükséges információkat tartalmazó oszlop attól az oszloptól balra lesz, amelyben keresni fogunk. A már említett INDEX + MATCH képletek kombinációja mentes ezt a hátrányt, ami a VLOOKUP-hoz (VLOOKUP) képest a legrugalmasabb megoldássá teszi a táblákból történő adatkinyerést.

A képlet gyakorlati alkalmazásának néhány vonatkozása

Tartomány keresés

A tartománykeresés klasszikus szemléltetése a rendelési méret alapján történő kedvezmény meghatározása.

Szöveges karakterláncok keresése

Természetesen a VLOOKUP nem csak számokat, hanem szöveget is keres. Szem előtt kell tartani, hogy a képlet nem tesz különbséget a karakterek kis- és nagybetűi között. Ha helyettesítő karaktereket használ, megszervezheti a fuzzy keresést. Két helyettesítő karakter van: "?" - bármely karaktert helyettesít egy szöveges karakterláncban, "*" - tetszőleges számú karaktert helyettesít.

Harci terek

Gyakran felvetődik a kérdés, hogyan lehet megoldani a többletköz problémáját a keresés során. Ha a keresőtábla még törölhető tőlük, akkor a VLOOKUP képlet első paramétere nem mindig Önön múlik. Ezért, ha fennáll a veszélye annak, hogy a cellák eltömődnek többletszókkal, akkor a TRIM funkció segítségével törölheti azt.

Különböző adatformátum

Ha a VLOOKUP függvény első paramétere egy számot tartalmazó, de a cellában szövegként tárolt cellára vonatkozik, és a tömb első oszlopa a megfelelő formátumú számokat tartalmazza, akkor a keresés sikertelen lesz. Az ellenkező helyzet is lehetséges. A probléma könnyen megoldható, ha az 1-es paramétert lefordítja a kívánt formátumra:

= VLOOKUP (−− D7; Termékek! $ A $ 2: $ C $ 5; 3; 0) - ha a D7 szöveget, a táblázat pedig számokat tartalmaz;

= VKERESÉS (D7 & ""); Termékek!$ A $ 2: $ C $ 5; 3; 0) – és fordítva.

Mellesleg, egyszerre többféleképpen is lefordíthatja a szöveget számmá, válasszon:

• Kettős tagadás -D7.
• Szorzás eggyel D7 * 1.
• Nulla összeadás D7 + 0.
• Emelés az első hatványra D7 ^ 1.

A számok szöveggé konvertálása üres karakterlánccal történő összefűzéssel történik, ami arra kényszeríti az Excelt, hogy konvertálja az adattípust.

Hogyan lehet elnyomni # N / A

Ez nagyon kényelmes az IFERROR funkcióval.

Például: = IFERROR (VLOOKUP (D7; Termékek! $ A $ 2: $ C $ 5; 3; 0); "").

Ha a VLOOKUP a # N / A hibakódot adja vissza, akkor az IFERROR elfogja és behelyettesíti a 2-es paramétert (ebben az esetben egy üres karakterláncot), és ha nem történik hiba, akkor ez a függvény úgy tesz, mintha egyáltalán nem létezne, de csak a VLOOKUP van, amely normál eredményt adott vissza.

Sor

Gyakran elfelejtik abszolút a tömb hivatkozását megadni, és nyújtáskor a tömb "lebeg". Ne felejtse el használni a $ A $ 2: $ C $ 5 helyett az A2: C5.

Célszerű a referenciatömböt a munkafüzet külön lapjára helyezni. Nem kerül a láb alá, és biztonságosabb lesz.

Még jobb ötlet lenne ezt a tömböt elnevezett tartományként deklarálni.

Sok felhasználó egy tömb megadásakor olyan konstrukciót használ, mint az A: C, amely teljes oszlopokat ad meg. Ennek a megközelítésnek van létjogosultsága, mivel nem kell nyomon követnie, hogy a tömb tartalmazza-e az összes szükséges karakterláncot. Ha sorokat ad a laphoz az eredeti tömbbel, akkor az A: C-ként megadott tartományt nem kell módosítani. Természetesen ez a szintaktikai konstrukció egy kicsit több munkára kényszeríti az Excelt, mint a tartomány pontos megadása, de ez a többletköltség elhanyagolható. századmásodpercekről beszélünk.

Nos, a zsenialitás határán - a tömb formába rendezése.

Az OSZLOP függvény használatával adja meg a kivonandó oszlopot

Ha a tábla, amelybe a VLOOKUP segítségével adatokat kér le, ugyanolyan felépítésű, mint a keresőtábla, de egyszerűen csak kevesebb sort tartalmaz, akkor a VLOOKUP OSZLOP () funkciójával automatikusan kiszámíthatja a lekérendő oszlopok számát. Ebben az esetben az összes VLOOKUP képlet ugyanaz lesz (az első paraméterhez igazítva, amely automatikusan változik)! Vegye figyelembe, hogy az első paraméternek abszolút oszlopkoordinátája van.

Összetett kulcs létrehozása & "|" &

Ha egyidejűleg több oszlop alapján kell keresni, akkor a kereséshez összetett kulcsot kell készíteni. Ha a visszatérési érték nem szöveges lenne (mint a "Kód" mezőnél), hanem numerikus, akkor erre a kényelmesebb SUMIFS képlet megfelelő lenne, és egyáltalán nem lenne szükség az összetett oszlopkulcsra.

Ez az első cikkem a Lifehackernek. Ha tetszett, látogassa meg Önt, és szívesen olvassa el a megjegyzésekben a VLOOKUP funkció és hasonlók használatának titkait. Kösz.:)