Kto je príliš lenivý alebo nemá čas čítať - pozrite si video. Podrobnosti a nuansy sú v texte nižšie.
Formulácia problému
Takže máme dva stoly - tabuľka objednávok и cenník:
Úlohou je automaticky dosadiť ceny z cenníka do tabuľky objednávok so zameraním na názov produktu, aby ste si neskôr mohli vypočítať náklady.
Riešenie
V sade funkcií Excel v kategórii Referencie a polia (Vyhľadávanie a referencie) existuje funkcia VPR (VLOOKUP).Táto funkcia hľadá danú hodnotu (v našom príklade je to slovo „Jablká“) v ľavom stĺpci zadanej tabuľky (cenníka) pri pohybe zhora nadol a po jej nájdení zobrazí obsah susednej bunky. (23 rubľov) Schematicky môže byť činnosť tejto funkcie znázornená Takže:
Pre uľahčenie ďalšieho používania funkcie urobte jednu vec naraz – pomenujte rozsah buniek v cenníku. Ak to chcete urobiť, vyberte všetky bunky cenníka okrem „hlavičky“ (G3: H19), vyberte z ponuky Vložiť – Názov – Priradiť (Vložiť — Názov — Definovať) alebo stlačte CTRL + F3 a zadajte ľubovoľný názov (bez medzier). Cena… Teraz, v budúcnosti, môžete tento názov použiť na prepojenie s cenníkom.
Teraz použijeme funkciu VPR… Vyberte bunku, do ktorej bude zadaná (D3) a otvorte kartu Vzorce – Vkladanie funkcií (Vzorce – funkcia vloženia)… V kategórii Referencie a polia (Vyhľadávanie a referencie) nájsť funkciu VPR (VLOOKUP) a stlačte OK… Zobrazí sa okno na zadanie argumentov pre funkciu:
Plníme ich postupne:
- Požadovaná hodnota (Hodnota vyhľadávania) – názov produktu, ktorý má funkcia nájsť v ľavom stĺpci cenníka. V našom prípade slovo „Jablká“ z bunky B3.
- Tabuľka (Pole tabuľky) – tabuľka, z ktorej sú prevzaté požadované hodnoty uXNUMXbuXNUMXbare, teda náš cenník. Pre referenciu používame náš vlastný názov „Cena“ uvedený vyššie. Ak ste neuviedli meno, môžete tabuľku jednoducho vybrať, ale nezabudnite stlačiť tlačidlo F4na pripnutie odkazu znakmi dolára, pretože inak sa pri kopírovaní nášho vzorca posunie nadol do zvyšku buniek v stĺpci D3:D30.
- Číslo_stĺpca (Indexové číslo stĺpca) – sériové číslo (nie písmeno!) stĺpca v cenníku, z ktorého budeme brať cenové hodnoty. Prvý stĺpec cenníka s názvami je očíslovaný 1, preto potrebujeme cenu zo stĺpca s číslom 2.
- interval_lookup (Vyhľadávanie rozsahu) – do tohto poľa je možné zadať iba dve hodnoty: FALSE alebo TRUE:
- Ak je zadaná hodnota 0 or KLAMEŤ (NEPRAVDA), potom to v skutočnosti znamená, že je povolené iba vyhľadávanie presná zhoda, tj ak funkcia nenájde neštandardnú položku uvedenú v tabuľke zákaziek v cenníku (ak je zadaný napr. „Kokos“), vygeneruje chybu #N/A (bez údajov).
- Ak je zadaná hodnota 1 or TRUE (PRAVDA), potom to znamená, že povolíte vyhľadávanie nie presného, ale približná zhoda, teda v prípade „kokos“ sa funkcia pokúsi nájsť produkt s názvom, ktorý je čo najbližšie k „kokosu“ a vráti cenu za tento názov. Vo väčšine prípadov môže takéto približné nahradenie používateľa oklamať tým, že nahradí hodnotu nesprávneho produktu, ktorý tam skutočne bol! Takže pri väčšine skutočných obchodných problémov je najlepšie nepovoliť približné vyhľadávanie. Výnimkou je, keď hľadáme čísla a nie text – napríklad pri výpočte Krokových zliav.
Všetko! Zostáva tlačiť OK a skopírujte zadanú funkciu do celého stĺpca.
# N / A chyby a ich potlačenie
Funkcie VPR (VLOOKUP) vráti chybu #N/A (#N/A) Ak:
- Povolené presné vyhľadávanie (argument Intervalové zobrazenie = 0) a požadované meno nie je in Tabuľka.
- Vrátane hrubého vyhľadávania (Intervalové zobrazenie = 1), Ale Tabuľka, v ktorom prebieha vyhľadávanie, nie je zoradené vzostupne podľa mien.
- Formát bunky, z ktorej pochádza požadovaná hodnota názvu (v našom prípade napríklad B3) a formát buniek prvého stĺpca (F3: F19) tabuľky sú rôzne (napríklad číselný a textový ). Tento prípad je typický najmä pri použití číselných kódov (čísla účtov, identifikátory, dátumy a pod.) namiesto textových názvov. V tomto prípade môžete použiť funkcie Ч и TEXT na konverziu dátových formátov. Bude to vyzerať asi takto:
=VLOOKUP(TEXT(B3);cena;0)
Viac si o tom môžete prečítať tu.
- Funkcia nemôže nájsť požadovanú hodnotu, pretože kód obsahuje medzery alebo neviditeľné netlačiteľné znaky (konce riadkov atď.). V tomto prípade môžete použiť textové funkcie TRIM (TRIM) и TLAČ(ČISTÝ) na ich odstránenie:
=VLOOKUP(TRIMSPACES(CLEAN(B3)),cena,0)
=VLOOKUP(TRIM(CLEAN(B3));cena;0)
Na potlačenie chybového hlásenia # N / A (#N/A) v prípadoch, keď funkcia nemôže nájsť presnú zhodu, môžete funkciu použiť IFERROR (IFERROR)... Takže napríklad táto konštrukcia zachytáva všetky chyby generované funkciou VLOOKUP a nahrádza ich nulami:
= IFERROR (VLOOKUP (B3, cena, 2, 0), 0)
= IFERROR (VLOOKUP (B3; cena; 2; 0); 0)
PS
Ak potrebujete extrahovať nie jednu hodnotu, ale celú množinu naraz (ak existuje niekoľko rôznych), potom budete musieť šamanizovať pomocou vzorca poľa. alebo použite novú funkciu XLOOKUP z Office 365.
- Vylepšená verzia funkcie VLOOKUP (VLOOKUP 2).
- Rýchly výpočet krokových (rozsahových) zliav pomocou funkcie VLOOKUP.
- Ako vytvoriť „ľavé VLOOKUP“ pomocou funkcií INDEX a MATCH
- Ako použiť funkciu VLOOKUP na vyplnenie formulárov údajmi zo zoznamu
- Ako vytiahnuť nie prvé, ale všetky hodnoty z tabuľky naraz
- Funkcie VLOOKUP2 a VLOOKUP3 z doplnku PLEX