Sečteno a podtrženo: Naučte se, jak vypočítat provize v Excelu pro základní stupňovitý plán a tabulku sazeb pomocí funkcí IF, VLOOKUP nebo XLOOKUP.

úroveň dovedností: středně pokročilí

Video Tutorial

výpočet provizí v aplikaci Excel může být velmi složitý úkol. To platí zejména v případě, že jste se pokusili použít více příkazů IF k výpočtu provizí pro každou úroveň v tabulce sazeb.

tento článek vysvětlí, jak používat funkci VLOOKUP, aby byl tento proces mnohem jednodušší. Tajemství nastavuje poslední argument ve vlookup na TRUE, najít nejbližší shodu.

prosím, podívejte se na můj článek o tom, jak používat VLOOKUP k nalezení nejbližší shody pro podrobné vysvětlení nastavení posledního argumentu na hodnotu TRUE.

aktualizace: Příspěvek byl aktualizován, aby obsahoval pokyny pro výpočet provizí s novou funkcí XLOOKUP. Zde je článek & video na XLOOKUP.

stáhnout soubor

Stáhněte si soubor, který chcete následovat.

výpočty provizí-pokud Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)

výpočet provizí pomocí VLOOKUP

s jednoduchým plánem provize obvykle máte tabulku sazeb, která uvádí výplatní sazbu na každé úrovni prodeje. Vzhledem k tomu, že obchodní zástupce zvyšuje prodej, jeho Výplatní sazba se obvykle zvýší.

Výplatní sazba by mohla být paušální částka dolaru, procento příjmů, procento kvóty atd. Úkolem VLOOKUP je najít částku prodeje zástupce v tabulce sazeb a vrátit odpovídající výplatní sazbu.

pro tento příklad náš plán provize vypadá následovně:

• Rep prodává $ 0 – $ 50,000, vydělávají 5%
• Rep prodává $ 51,000 – $ 100,000, vydělávají 7%
• Rep prodává $ 100,001 – $ 150,000, vydělávají 10%
• Rep prodává přes $ 150,001, vydělávají 15%

zástupce provede určitou částku prodeje během měsíce. Získá určitou výplatní sazbu v závislosti na úrovni dosaženého prodeje.

můžeme použít vzorec VLOOKUP pro výpočet výplatní sazby pro danou částku prodeje(vyhledávací hodnota). Aby to fungovalo, musíme nastavit poslední argument ve vlookup na TRUE.

s posledním argumentem nastaveným na hodnotu TRUE najde vlookup nejbližší shodu s hodnotou vyhledávání, která je menší nebo rovna částce vyhledávání. To nám v podstatě umožňuje najít hodnotu mezi rozsahy dvou čísel (úrovní).

proto je poslední argument ve VLOOKUP pojmenován range_lookup. Pokud je tento argument pravdivý, VLOOKUP hledá mezi rozsahy hodnot ve sloupci minimální úrovně, aby našel přesnou shodu nebo hodnotu menší než hodnota vyhledávání.

jak nastavit tabulku sazeb

při vkládání této tabulky sazeb do aplikace Excel stačí uvést minimální úroveň pro rozsah vyhledávání. VLOOKUP opět vyhledá „nejbližší shodu“, která je menší nebo rovna hodnotě vyhledávání. Pokud najde hodnotu, která je větší než hodnota vyhledávání, vrátí předchozí řádek.

v tomto příkladu pro tabulku sazeb provizí musí být první řádek v rozsahu vyhledávání nulový. Je to proto, že obchodní zástupce by mohl mít prodej 0 $a hodnota vyhledávání by byla nulová. Pokud by hodnota vyhledávání (částka prodeje) byla záporným číslem, vlookup by vrátil chybu.

je důležité to vědět a nastavit tabulku sazeb pro všechny možné hodnoty vyhledávání.

pokud je částka prodeje větší než poslední řádek v rozsahu vyhledávání, vlookup vrátí poslední řádek. Například, pokud rep z prodeje $ 175,000 pak vlookup vrátí 15%.

Vypočítejte provize pro vrácení hodnoty dolaru

výplata může být také vrácena jako hodnota dolaru namísto procenta. Při tomto nastavení bude výplata paušální.

to znamená, že výplata bude stejná, bez ohledu na to, jaká je částka prodeje v rámci úrovně. V níže uvedeném příkladu, v případě, že výplata bude $ 1,000 v případě, že částka prodeje je $ 55,000 nebo $ 95,000 .

výplata není na posuvné stupnici. Jedná se o paušální sazbu pro každou úroveň.

pokud hledáte výpočet posuvného měřítka, podívejte se na můj článek o výpočtu provize s odstupňovanou strukturou sazeb pomocí SUMPRODUCT.

výpočty provizí pomocí XLOOKUP

pro tento výpočet můžeme také použít novou funkci XLOOKUP. Při hledání nejbližší shody funguje velmi podobně jako VLOOKUP.

výhody s XLOOKUP

existují dva hlavní rozdíly a výhody s XLOOKUP:

1. s XLOOKUP určíme vyhledávací pole a návratové pole jako samostatné rozsahy. Toto je B4: B7 a D4: D7 na obrázku výše. To je výhoda oproti VLOOKUP, protože můžeme vložit nebo odstranit řádky mezi sloupci B A D a vzorec bude stále fungovat.
   když přidáme / odstraníme sloupce v poli tabulky VLOOKUP, musíme ručně změnit číslo indexu sloupce nebo použít vzorec pro jeho dynamický výpočet.
2. XLOOKUP nevyžaduje třídění dat při použití přesné shody nebo další menší / větší položky pro režim shody. XLOOKUP bude skutečně hledat další menší / větší položku a vrátí výsledek z tohoto řádku / sloupce.
   VLOOKUP vyžaduje, aby data byla seřazena, když je poslední argument pravdivý. Obvykle budete chtít třídit data pro tyto typy tabulek stejně, ale je dobré vědět, že stačí dát nové úrovně sazeb v dolní části tabulky a XLOOKUP bude fungovat.

nevýhodou XLOOKUP

hlavní nevýhodou XLOOKUP je kompatibilita. Vy i všichni uživatelé vašeho souboru musíte být ve verzi Microsoft / Office 365, která má XLOOKUP. Není zpětně kompatibilní nebo je k dispozici ve starších verzích aplikace Excel.

proto VLOOKUP funguje skvěle v tomto scénáři a je mnohem snazší psát než vnořený vzorec IF.

Vyhněte se vnořeným vzorcům IF

běžný přístup k výpočtu provizí používá příkazy IF. S takovou tabulkou sazeb byste museli napsat více příkazů IF. V podstatě musíte napsat jeden IF příkaz pro každou úroveň (řádek) v tabulce.

pak musíte spojit všechny příkazy IF do jednoho dlouhého a ošklivého vzorce. Ty se nazývají vnořené příkazy IF.

vždy se snažím vyhnout vnořeným IFs, pokud je to možné, protože jsou obtížně čitelné a srozumitelné a mohou být pomalejší pro výpočet Excelu. Pokud máte v sešitu tisíce vnořených vzorců IF, může se Váš čas výpočtu zpomalit.

vzorec IF je mnohem obtížnější udržovat. Vyžaduje změnu, když jsou řádky přidány / odstraněny z tabulky. VLOOKUP a XLOOKUP nevyžadují tento typ údržby.

pokud používáte IF, ujistěte se, že každá buňka odkazuje na tabulku sazeb absolutní referencí (F4 na klávesnici). V opačném případě získáte nesprávné výsledky při kopírování vzorce dolů.

použití VLOOKUP je mnohem jednodušší a čistší než použití vnořených if vzorců. Jak můžete vidět v tomto příkladu, vlookup vám umožňuje použít jeden vzorec pro výpočet sazby výplaty provize pro danou částku prodeje.

další zdroje

• jak používat VLOOKUP k nalezení nejbližšího argumentu Match – Last se rovná TRUE
• Jak vypočítat kumulativní odstupňované provize s SUMPRODUCT
• VLOOKUP vysvětleno jednoduše na Starbucks
• VLOOKUP & MATCH – A Dynamic Duo
• nová funkce XLOOKUP pro Excel + Video Tutorial
• pokud funkce vysvětlena: jak napsat vzorec příkazu if V aplikaci Excel

jak používáte VLOOKUP s nejbližší shodou?

VLOOKUP s technikou nejbližší shody lze také použít pro výpočty daňových závorek, porovnávání cen atd. Co používáte tuto techniku k výpočtu?