Použití funkce VLOOKUP k nahrazení hodnot

Kdo je líný nebo nemá čas číst – podívejte se na video. Podrobnosti a nuance jsou v textu níže.

Formulace problému

Takže máme dva stoly - objednávková tabulka и ceník:

Úkolem je automaticky dosazovat ceny z ceníku do tabulky objednávek se zaměřením na název produktu, abyste si později mohli spočítat náklady.

Řešení

V sadě funkcí aplikace Excel pod kategorií Reference a pole (Vyhledávání a reference) existuje funkce VPR (VYHLEDAT).Tato funkce hledá danou hodnotu (v našem příkladu je to slovo „Jablka“) v levém sloupci zadané tabulky (ceníku) a pohybuje se shora dolů a po jejím nalezení zobrazí obsah sousední buňky. (23 rublů) Schematicky lze provoz této funkce znázornit Takže:

Pro usnadnění dalšího použití funkce udělejte jednu věc najednou – dejte rozsahu buněk v ceníku vlastní název. Chcete-li to provést, vyberte všechny buňky ceníku kromě „záhlaví“ (G3: H19), vyberte z nabídky Vložit – Název – Přiřadit (Vložit — Název — Definovat) nebo stiskněte tlačítko CTRL + F3 a zadejte libovolný název (bez mezer). Cena… Nyní, v budoucnu, můžete tento název použít pro odkaz na ceník.

Nyní použijeme funkci VPR… Vyberte buňku, do které bude zadána (D3) a otevřete záložku Vzorce – vkládání funkcí (Vzorce – funkce vložení)… V kategorii Reference a pole (Vyhledání a reference) najít funkci VPR (VYHLEDAT) a stiskněte OK… Zobrazí se okno pro zadání argumentů funkce:

Použití funkce VLOOKUP k nahrazení hodnot

Naplňujeme je postupně:

  • Požadovaná hodnota (Hodnota vyhledávání) – název produktu, který má funkce najít v levém sloupci ceníku. V našem případě slovo „Jablka“ z buňky B3.
  • Tabulka (Pole tabulky) – tabulka, ze které jsou převzaty požadované hodnoty uXNUMXbuXNUMXbare, tedy náš ceník. Pro informaci používáme náš vlastní název „Cena“, který jsme uvedli dříve. Pokud jste nezadali jméno, stačí vybrat tabulku, ale nezapomeňte stisknout tlačítko F4připnout odkaz se znaky dolaru, protože jinak se posune dolů při kopírování našeho vzorce dolů do zbytku buněk ve sloupci D3:D30.
  • Číslo_sloupce (Indexové číslo sloupce) – sériové číslo (ne písmeno!) sloupce v ceníku, ze kterého budeme přebírat cenové hodnoty. První sloupec ceníku s názvy je očíslován 1, proto potřebujeme cenu ze sloupce 2.
  • interval_lookup (Vyhledání rozsahu) – do tohoto pole lze zadat pouze dvě hodnoty: FALSE nebo TRUE:
      • Pokud je zadána hodnota 0 or LEŽÍCÍ (NEPRAVDIVÉ), pak to ve skutečnosti znamená, že je povoleno pouze vyhledávání přesná shoda, tj. pokud funkce nenajde nestandardní položku uvedenou v tabulce objednávek v ceníku (pokud je zadáno např. „Kokos“), vygeneruje chybu #N/A (bez údajů).
      • Pokud je zadána hodnota 1 or TRUE (SKUTEČNÝ), pak to znamená, že povolíte hledání nikoli přesného, ​​ale přibližná shoda, tedy v případě „kokos“ se funkce pokusí najít produkt s názvem co nejblíže „kokosu“ a vrátí cenu za tento název. Ve většině případů může taková přibližná náhrada zahrát na uživatele trik tím, že dosadí hodnotu nesprávného produktu, který tam skutečně byl! Takže u většiny skutečných obchodních problémů je lepší přibližné vyhledávání nepovolit. Výjimkou je situace, kdy hledáme čísla a ne text – například při výpočtu Krokových slev.

Všechno! Zbývá stisknout OK a zkopírujte zadanou funkci do celého sloupce.

# N / A chyby a jejich potlačení

funkce VPR (VYHLEDAT) vrátí chybu #N/A (#N/A) Pokud:

  • Přesné vyhledávání povoleno (argument Intervalové zobrazení = 0) a požadované jméno není in Tabulka.
  • Včetně hrubého vyhledávání (Intervalové zobrazení = 1), ale Tabulka, ve kterém vyhledávání probíhá, není seřazeno vzestupně podle jmen.
  • Formát buňky, odkud pochází požadovaná hodnota názvu (v našem případě například B3) a formát buněk prvního sloupce (F3: F19) tabulky se liší (například číselný a textový ). Tento případ je typický zejména při použití číselných kódů (čísla účtů, identifikátory, data atd.) namísto textových názvů. V tomto případě můžete použít funkce Ч и TEXT pro převod datových formátů. Bude to vypadat nějak takto:

    =VLOOKUP(TEXT(B3);cena;0)

    Více si o tom můžete přečíst zde.

  • Funkce nemůže najít požadovanou hodnotu, protože kód obsahuje mezery nebo neviditelné netisknutelné znaky (zalomení řádků atd.). V tomto případě můžete použít textové funkce TRIM (TRIM) и TISK(ČISTÝ) jak je odstranit:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),cena,0)

    =VLOOKUP(TRIM(CLEAN(B3));cena;0)

Chcete-li potlačit chybovou zprávu # N / A (#N/A) v případech, kdy funkce nemůže najít přesnou shodu, můžete funkci použít IFERROR (IFERROR)… Tak například tato konstrukce zachytí všechny chyby generované funkcí VLOOKUP a nahradí je nulami:

= IFERROR (VLOOKUP (B3, cena, 2, 0), 0)

= IFERROR (VLOOKUP (B3; cena; 2; 0); 0)

PS

Pokud potřebujete extrahovat ne jednu hodnotu, ale celou sadu najednou (pokud existuje několik různých), pak budete muset šamanizovat s maticovým vzorcem. nebo použijte novou funkci XLOOKUP z Office 365.

 

  • Vylepšená verze funkce VLOOKUP (SVYHLEDAT 2).
  • Rychlý výpočet krokových (rozsahových) slev pomocí funkce VLOOKUP.
  • Jak vytvořit „VLOOKUP vlevo“ pomocí funkcí INDEX a MATCH
  • Jak používat funkci SVYHLEDAT k vyplnění formulářů údaji ze seznamu
  • Jak vytáhnout z tabulky ne první, ale všechny hodnoty najednou
  • Funkce VLOOKUP2 a VLOOKUP3 z doplňku PLEX

 

Napsat komentář