Aki lusta vagy nincs ideje olvasni – nézze meg a videót. A részletek és az árnyalatok az alábbi szövegben találhatók.
A probléma megfogalmazása
Tehát két asztalunk van - rendelési táblázat и árlista:
A feladat az, hogy az árlistából az árakat automatikusan behelyettesítse a rendelési táblázatba, a termék nevére fókuszálva, hogy később ki tudja számolni a költséget.
Megoldás
Az Excel függvénykészletben, a kategória alatt Hivatkozások és tömbök (Keresés és hivatkozás) van egy funkció VPR (VLOOKUP).Ez a függvény felülről lefelé haladva a megadott táblázat (árlista) bal szélső oszlopában keres egy adott értéket (példánkban ez az „almák”), és miután megtalálta, megjeleníti a szomszédos cella tartalmát. (23 rubel) .Sematikusan ennek a függvénynek a működése ábrázolható Így:
A funkció további használatának megkönnyítése érdekében tegyen egy dolgot egyszerre – adja meg az árlista celláinak saját nevét. Ehhez válassza ki az árlista összes celláját a „fejléc” kivételével (G3: H19), válassza ki a menüből Beszúrás – Név – Hozzárendelés (Beszúrás — Név — Meghatározás) vagy nyomja meg a gombot CTRL + F3 és írjon be bármilyen nevet (szóközök nélkül), mint pl Ár… Mostantól a jövőben ezt a nevet használhatja az árlistához való hivatkozáshoz.
Most a függvényt használjuk VPR… Válassza ki a cellát, ahová beírja (D3), és nyissa meg a lapot Képletek – Függvénybeillesztés (Képletek – Függvény beszúrása)… A kategóriában Hivatkozások és tömbök (Keresés és hivatkozás) keresse meg a függvényt VPR (VLOOKUP) és nyomja meg az OK… Megjelenik egy ablak a függvény argumentumainak megadására:
Felváltva kitöltjük őket:
- Kívánt érték (Keresési érték) – a termék neve, amelyet a funkciónak az árlista bal szélső oszlopában kell megtalálnia. Esetünkben az „Alma” szó a B3 cellából.
- Táblázat (Táblázattömb) – egy táblázat, amelyből a kívánt uXNUMXbuXNUMX értékeket vettük, vagyis az árlistánkat. Referenciaként a saját, korábban megadott „Ár” nevünket használjuk. Ha nem adott nevet, egyszerűen kiválaszthatja a táblázatot, de ne felejtse el megnyomni a gombot F4hogy dollárjelekkel rögzítse a linket, mert ellenkező esetben lecsúszik, amikor a képletünket a D3:D30 oszlop többi cellájába másoljuk.
- Oszlop_szám (Oszlop indexszáma) – sorszám (nem betű!) Az árlista azon oszlopának, amelyből árértékeket veszünk. Az árlista első oszlopa a nevekkel 1-es számmal van ellátva, ezért a 2-es oszlopból kell az ár.
- intervall_lookup (Tartomány keresése) – ebben a mezőben csak két érték adható meg: HAMIS vagy IGAZ:
- Ha beírunk egy értéket 0 or FEKVŐ (HAMIS), akkor ez valójában azt jelenti, hogy csak a keresés engedélyezett pontos egyezés, azaz ha a függvény nem találja meg az árlistában a rendelési táblázatban megadott nem szabványos cikket (ha pl. „Coconut” van megadva), akkor #N/A (nincs adat) hibát generál.
- Ha beírunk egy értéket 1 or TRUE (IGAZ), akkor ez azt jelenti, hogy engedélyezed a keresést nem a pontos, hanem hozzávetőleges egyezés, azaz a „kókusz” esetében a funkció megpróbál olyan terméket keresni, amelynek a neve a lehető legközelebb áll a „kókusz”-hoz, és visszaadja ennek a névnek az árát. A legtöbb esetben egy ilyen hozzávetőleges helyettesítés trükköt űzhet a felhasználón azzal, hogy a nem megfelelő termék értékét helyettesíti, amely valójában ott volt! Tehát a legtöbb valós üzleti probléma esetén a hozzávetőleges keresést jobb nem engedélyezni. Kivétel az, amikor számokat keresünk, nem szöveget – például a Lépéskedvezmények kiszámításakor.
Minden! Marad a nyomás OK és másolja a beírt függvényt a teljes oszlopba.
# N / A hibák és elnyomásuk
Funkció VPR (VLOOKUP) #N/A hibát ad vissza (#N/A) Ha egy:
- Pontos keresés engedélyezve (argumentum Intervallum nézet = 0), és a kívánt név nincs benne Táblázat.
- Durva keresés tartalmazza (Intervallum nézet = 1), De a Táblázat, amelyben a keresés zajlik, nincs a nevek növekvő sorrendjében rendezve.
- Annak a cellának a formátuma, ahonnan a név szükséges értéke származik (esetünkben például B3) és a táblázat első oszlopának celláinak formátuma (F3: F19) eltérő (például numerikus és szöveges ). Ez az eset különösen akkor jellemző, ha szöveges nevek helyett numerikus kódokat (számlaszámok, azonosítók, dátumok stb.) használunk. Ebben az esetben használhatja a funkciókat Ч и TEXT adatformátumok konvertálásához. Valahogy így fog kinézni:
=KERESÉS(SZÖVEG(B3),ár,0)
Erről bővebben itt olvashat.
- A függvény nem találja a szükséges értéket, mert a kód szóközöket vagy láthatatlan, nem nyomtatható karaktereket (sortöréseket stb.) tartalmaz. Ebben az esetben szöveges függvényeket használhat TRIM (TRIM) и NYOMTATÁS(TISZTA) eltávolításukhoz:
=VLOOKUP(VÁGÁSOK(TISZTA(B3)),ár,0)
=VELKERESÉS(TRIM(TISZTA(B3));ár;0)
A hibaüzenet elnyomásához # N / A (#N/A) Azokban az esetekben, amikor a függvény nem talál pontos egyezést, használhatja a függvényt IFROROR (HIBÁS)… Tehát például ez a konstrukció elkapja a VLOOKUP által generált hibákat, és nullákkal helyettesíti őket:
= IFERROR (VLOOKUP (B3, ár, 2, 0), 0)
= IFERROR (VLOOKUP (B3; ár; 2; 0); 0)
PS
Ha nem egy értéket, hanem az egész halmazt kell kivonni egyszerre (ha több különböző van), akkor a tömbképlettel kell sámánizálni. vagy használja az Office 365 új XLOOKUP funkcióját.
- A VLOOKUP funkció továbbfejlesztett változata (VLOOKUP 2).
- Lépés (tartomány) kedvezmények gyors kiszámítása a VLOOKUP funkció segítségével.
- Hogyan készítsünk „bal oldali VLOOKUP”-ot az INDEX és a MATCH funkciók használatával
- A VLOOKUP funkció használata az űrlapok kitöltésére a listából származó adatokkal
- Hogyan lehet egyszerre kihúzni a táblázatból nem az elsőt, hanem az összes értéket
- VLOOKUP2 és VLOOKUP3 funkciók a PLEX kiegészítőből