Obsah
V praxi velmi často nastávají případy, kdy vy i já potřebujeme najít nejbližší hodnotu v množině (tabulce) ve vztahu k danému číslu. Může to být například:
- Výpočet slevy v závislosti na objemu.
- Výpočet výše bonusů v závislosti na realizaci plánu.
- Výpočet ceny dopravy v závislosti na vzdálenosti.
- Výběr vhodných kontejnerů pro zboží atd.
Kromě toho může být v závislosti na situaci vyžadováno zaokrouhlení nahoru i dolů.
Existuje několik způsobů – samozřejmých i ne tak zřejmých –, jak takový problém vyřešit. Podívejme se na ně postupně.
Pro začátek si představme dodavatele, který poskytuje slevy ve velkoobchodě a procento slevy závisí na množství nakoupeného zboží. Například při nákupu nad 5 kusů sleva 2% a při nákupu od 20 kusů již 6% atd.
Jak rychle a krásně vypočítat procento slevy při zadávání množství nakupovaného zboží?
Metoda 1: Vnořené IF
Metoda ze série „co si myslet – musíš skočit!“. Použití vnořených funkcí IF (LI) k postupné kontrole, zda hodnota buňky spadá do každého z intervalů, a zobrazení slevy pro odpovídající rozsah. Ale vzorec v tomto případě se může ukázat jako velmi těžkopádný:
Myslím, že je zřejmé, že ladění takové „panenky monster“ nebo pokus o přidání pár nových podmínek do ní po nějaké době je zábava.
Kromě toho má Microsoft Excel limit vnoření pro funkci IF – 7krát ve starších verzích a 64krát v novějších verzích. Co když potřebujete víc?
Metoda 2. SVYHLEDAT s intervalovým zobrazením
Tato metoda je mnohem kompaktnější. Pro výpočet procenta slevy použijte legendární funkci VPR (VYHLEDAT) v režimu přibližného vyhledávání:
kde
- B4 – hodnota množství zboží v první transakci, na kterou hledáme slevu
- $ G $ 4: $ H $ 8 – odkaz na tabulku slev – bez „hlavičky“ a s adresami pevnými se znakem $.
- 2 — pořadové číslo sloupce v tabulce slev, ze kterého chceme získat hodnotu slevy
- TRUE – tady je zakopaný „pes“. If jako poslední argument funkce VPR specifikujte LEŽÍCÍ (NEPRAVDIVÉ) nebo 0, pak funkce vyhledá přísná shoda ve sloupci množství (a v našem případě zobrazí chybu #N/A, protože v tabulce slev není žádná hodnota 49). Ale kdyby místo toho LEŽÍCÍ zapsat TRUE (SKUTEČNÝ) nebo 1, pak funkce nebude hledat přesné, ale nejbližší nejmenší hodnotu a dá nám procento slevy, které potřebujeme.
Nevýhodou této metody je nutnost seřadit tabulku slev vzestupně podle prvního sloupce. Pokud takové třídění neexistuje (nebo se provádí v opačném pořadí), náš vzorec nebude fungovat:
V souladu s tím lze tento přístup použít pouze k nalezení nejbližší nejmenší hodnoty. Pokud potřebujete najít nejbližší největší, musíte použít jiný přístup.
Metoda 3. Nalezení nejbližší největší pomocí funkcí INDEX a MATCH
Nyní se podívejme na náš problém z druhé strany. Předpokládejme, že prodáváme několik modelů průmyslových čerpadel různých kapacit. Prodejní tabulka vlevo ukazuje výkon požadovaný zákazníkem. Musíme vybrat čerpadlo s nejbližším maximálním nebo stejným výkonem, ale ne menším, než vyžaduje projekt.
Funkce VLOOKUP zde nepomůže, takže budete muset použít její analog – spoustu funkcí INDEX (INDEX) a VÍCE EXPOZOVANĚ (ZÁPAS):
Zde funkce MATCH s posledním argumentem -1 funguje v režimu hledání nejbližší největší hodnoty a funkce INDEX pak ze sousedního sloupce vyextrahuje název modelu, který potřebujeme.
Metoda 4. Nová funkce VIEW (XLOOKUP)
Pokud máte verzi Office 365 se všemi nainstalovanými aktualizacemi, pak místo VLOOKUP (VYHLEDAT) můžete použít jeho analog – funkci VIEW (XLOOKUP), kterou jsem již podrobně analyzoval:
Zde:
- B4 – počáteční hodnota množství produktu, na které hledáme slevu
- $ G $ 4: $ G $ 8 – rozsah, kde hledáme zápasy
- $H$4: $H$8 – rozsah výsledků, ze kterých chcete slevu vrátit
- čtvrtý argument (-1) obsahuje místo přesné shody hledání nejbližšího nejmenšího čísla, které chceme.
Výhody této metody jsou v tom, že není potřeba třídit tabulku slev a možnost vyhledávat v případě potřeby nejen nejbližší nejmenší, ale i nejbližší největší hodnotu. Poslední argument v tomto případě bude 1.
Tuto funkci však bohužel zatím nemají všichni – pouze šťastní majitelé Office 365.
Metoda 5. Power Query
Pokud ještě neznáte výkonný a zcela bezplatný doplněk Power Query pro Excel, pak jste tady. Pokud jste již obeznámeni, zkuste to použít k vyřešení našeho problému.
Nejprve udělejme nějaké přípravné práce:
- Převedeme naše zdrojové tabulky na dynamické (chytré) pomocí klávesové zkratky Ctrl+T nebo tým Domů – Formát jako tabulka (Domů — Formátovat jako tabulku).
- Pro názornost jim uveďme jména. Prodej и Slevy Karta Stavitel (Design).
- Postupně načtěte každou z tabulek do Power Query pomocí tlačítka Z tabulky/rozsahu Karta Data (Data – z tabulky/rozsahu). V posledních verzích aplikace Excel bylo toto tlačítko přejmenováno na S listy (Z listu).
- Pokud mají tabulky různé názvy sloupců s množstvím, jako v našem příkladu („Množství zboží“ a „Množství od…“), musí být v Power Query přejmenovány a pojmenovány stejně.
- Poté se můžete vrátit zpět do Excelu výběrem příkazu v okně editoru Power Query Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít&Načíst — Zavřít&Načíst do…) a poté možnost Stačí vytvořit spojení (Pouze vytvořit připojení).
- Pak začíná to nejzajímavější. Pokud máte zkušenosti s Power Query, tak předpokládám, že další myšlenkový směr by měl směřovat ke sloučení těchto dvou tabulek se spojovacím dotazem (merge) a la VLOOKUP, jako tomu bylo v předchozí metodě. Ve skutečnosti se budeme muset sloučit v režimu přidávání, což není na první pohled vůbec zřejmé. Vyberte na kartě Excel Data – Získat data – Kombinovat požadavky – Přidat (Data — Získat data — Kombinovat dotazy — Připojit) a pak naše stoly Prodej и Slevy v okně, které se objeví:
- Po kliknutí na tlačítko OK naše stoly budou slepeny do jednoho celku – pod sebe. Upozorňujeme, že sloupce s množstvím zboží v těchto tabulkách spadaly pod sebe, protože. mají stejné jméno:
- Pokud je pro vás důležitá původní posloupnost řádků v prodejní tabulce, tak abyste ji po všech následných transformacích mohli následně obnovit, přidejte do naší tabulky očíslovaný sloupec pomocí příkazu Přidání sloupce – Indexový sloupec (Přidat sloupec — Indexový sloupec). Pokud vám na pořadí řádků nezáleží, můžete tento krok přeskočit.
- Nyní pomocí rozevíracího seznamu v záhlaví tabulky seřaďte tabulku podle sloupců Množství Vzestupně:
- A hlavní trik: klikněte pravým tlačítkem na záhlaví sloupce Sleva vybrat tým Vyplnit – Dolů (Vyplnit – dolů). Prázdné buňky s null automaticky vyplněno předchozími hodnotami slev:
- Zbývá obnovit původní posloupnost řádků řazením podle sloupce index (můžete to později klidně smazat) a nepotřebných čar se zbavíte filtrem null podle sloupce Kód transakce:
- Použití funkce VLOOKUP k vyhledávání a vyhledávání dat
- Při použití funkce SVYHLEDAT (VLOOKUP) se rozlišují velká a malá písmena
- XNUMXD SVYHLEDAT (SVYHLEDAT)