Изтеглете примерната работна книга
Този урок ще покаже как да се сумират резултатите от множество функции VLOOKUP в една стъпка в Excel и Google Sheets.
Използване на SUM с функция VLOOKUP
Функцията VLOOKUP може да се използва за търсене на една стойност, но също така можете да търсите и сумирате множество стойности, като вложете функцията VLOOKUP вътре във функцията SUM.
Този пример ще покаже как да се изчисли Общи приходи от продажби на конкретен Магазин над 3 месеца, като използвате функция за масив с SUM и VLOOKUP:
| 1 | {= SUM (VLOOKUP (P3, B3: N6, {2,3,4}, FALSE))} |
Това е еквивалентно на използването на следните 3 редовни VLOOKUP функции за сумиране на приходите за месеците януари, февруари и март.
| 1 | = VLOOKUP (P3, B3: N6,2, FALSE)+VLOOKUP (P3, B3: N6,3, FALSE)+VLOOKUP (P3, B3: N6,4, FALSE) |
Можем да комбинираме тези функции заедно, като направим следното:
Първо, зададохме функцията VLOOKUP да връща колони 2, 3 и 4 като изход от масив:
| 1 | = VLOOKUP (P3, B3: N6, {2,3,4}, FALSE) |
Това ще произведе резултата от масива:
| 1 | {98, 20, 76} |
След това, за да сумираме резултата от масива заедно, използваме функцията SUM.
Важно! Ако използвате Excel версия 2022 или по -ранна, трябва да въведете формулата, като натиснете CTRL + SHIFT + ENTER, за да създадете формулата на масива. Ще разберете, че сте направили това правилно, когато къдравите скоби се появят около формулата. Това не е необходимо в Excel 365 (или по -новите версии на Excel).
Използване на по -големи размери на масиви във функция VLOOKUP
Можем да разширим размера на входа на масива, за да представим повече данни. Следващият пример ще изчисли Общи приходи от продажби на конкретен Магазин за 12 месеца, използвайки функция за масив, съдържаща функцията SUM, за да комбинира 12 употреби на функцията VLOOKUP в една клетка.
| 1 | {= SUM (VLOOKUP (P3, B3: N6, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))} |
Други обобщени функции и VLOOKUP
Други обобщени функции могат да се използват по същия начин като функцията SUM за получаване на алтернативна обобщена статистика. Например, можем да използваме функциите MAX, MIN, AVERAGE, MEDIAN, SUM и COUNT, за да обобщим Приходи от продажби от януари до март:
| 1 | = MAX (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = MIN (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = СРЕДНО (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = СРЕДНИ (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)) |
Заключване на препратки към клетки
За да направим нашите формули по -лесни за четене, показахме формулите без заключени клетъчни препратки:
| 1 | = SUM (VLOOKUP (P3, B3: N6, {2,3,4}, FALSE)) |
Но тези формули няма да работят правилно, когато копирате и поставите другаде във вашия файл. Вместо това трябва да използвате заключени клетъчни препратки по следния начин:
| 1 | {= SUM (VLOOKUP (P3, $ B $ 3: $ N $ 6, {2,3,4}, FALSE))} |
Прочетете нашата статия за Заключване на препратки към клетки, за да научите повече.
Използване на SUM с функция VLOOKUP в Google Sheets
Тези формули работят по същия начин в Google Sheets, както и в Excel, с изключение на това, че функцията ARRAYFORMULA се изисква да се използва в Google Sheets, за да оцени правилно резултатите. Това може да бъде добавено автоматично чрез натискане на клавишите CTRL + SHIFT + ENTER, докато редактирате формулата.
| 1 | =ArrayFormula(SUM(VLOOKUP(O2,А2: М5,{2,3,4},НЕВЯРНО))) |
