Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak shrnout výsledky více funkcí VLOOKUP v jednom kroku v Excelu a Tabulkách Google.
Použití SUM s funkcí VLOOKUP
Funkci VLOOKUP lze použít k vyhledání jedné hodnoty, ale můžete také vyhledat a sečíst více hodnot vnořením funkce VLOOKUP do funkce SUM.
Tento příklad ukáže, jak vypočítat Celkové tržby z prodeje konkrétního Obchod více než 3 měsíce pomocí funkce pole s SUM a VLOOKUP:
| 1 | {= SUM (VLOOKUP (P3, B3: N6, {2,3,4}, FALSE))} |
To je ekvivalentní použití následujících 3 pravidelných funkcí VLOOKUP k součtu příjmů za měsíce leden, únor a březen.
| 1 | = VLOOKUP (P3, B3: N6,2, FALSE)+VLOOKUP (P3, B3: N6,3, FALSE)+VLOOKUP (P3, B3: N6,4, FALSE) |
Tyto funkce můžeme kombinovat následujícím způsobem:
Nejprve nastavíme funkci VLOOKUP tak, aby vracela sloupce 2, 3 a 4 jako výstup pole:
| 1 | = VLOOKUP (P3, B3: N6, {2,3,4}, FALSE) |
Výsledkem bude výsledek pole:
| 1 | {98, 20, 76} |
Dále, abychom shrnuli výsledek pole dohromady, použijeme funkci SUMA.
Důležité! Pokud používáte Excel verze 2022 nebo starší, musíte vzorec zadat stisknutím kláves CTRL + SHIFT + ENTER a vytvořit vzorec pole. Až se kolem vzorce objeví složené závorky, budete vědět, že jste to udělali správně. V aplikaci Excel 365 (nebo novějších verzích aplikace Excel) to není nutné.
Použití větších velikostí polí ve funkci VLOOKUP
Můžeme rozšířit velikost vstupu pole, aby reprezentoval více dat. Tento další příklad vypočítá Celkové tržby z prodeje konkrétního Obchod po dobu 12 měsíců pomocí funkce pole obsahujícího funkci SUMA ke spojení 12 použití funkce VLOOKUP do jedné buňky.
| 1 | {= SUM (VLOOKUP (P3, B3: N6, {2,3,4,5,6,7,8,9,9,10,11,12,13}, FALSE))} |
Další souhrnné funkce a VLOOKUP
Jiné souhrnné funkce lze použít stejným způsobem jako funkce SUM k vytvoření alternativní souhrnné statistiky. K shrnutí můžeme například použít funkce MAX, MIN, PRŮMĚR, MEDIAN, SUM a COUNT Tržby od ledna do března:
| 1 | = MAX (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = MIN (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = PRŮMĚR (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = MEDIAN (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = SUM (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = COUNT (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
Zamykání odkazů na buňky
Aby byly naše vzorce čitelnější, ukázali jsme vzorce bez zamčených odkazů na buňky:
| 1 | = SUM (VLOOKUP (P3, B3: N6, {2,3,4}, FALSE)) |
Tyto vzorce ale nebudou správně fungovat, když je zkopírujete a vložíte jinam do souboru. Místo toho byste měli použít uzamčené odkazy na buňky takto:
| 1 | {= SUM (VLOOKUP (P3, $ B $ 3: $ N $ 6, {2,3,4}, FALSE))} |
Další informace najdete v našem článku o zamykání odkazů na buňky.
Použití SUMU s funkcí VLOOKUP v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google stejně jako v Excelu, kromě toho, že pro správné vyhodnocení výsledků je v Tabulkách Google nutné použít funkci ARRAYFORMULA. To lze automaticky přidat stisknutím kláves CTRL + SHIFT + ENTER při úpravách vzorce.
| 1 | =ArrayFormula(SOUČET(VLOOKUP(O2,A2: M5,{2,3,4},NEPRAVDIVÉ))) |
