Загрузите образец книги
В этом руководстве будет показано, как суммировать результаты нескольких функций ВПР за один шаг в Excel и Google Таблицах.
Использование СУММ с функцией ВПР
Функцию ВПР можно использовать для поиска одного значения, но вы также можете найти и суммировать несколько значений, вложив функцию ВПР в функцию СУММ.
Этот пример покажет, как рассчитать Общий доход от продаж конкретного Магазин более 3 месяцев с использованием функции массива с СУММОМ и ВПР:
| 1 | {= СУММ (ВПР (P3; B3: N6; {2,3,4}; FALSE))} |
Это эквивалентно использованию следующих 3 обычных функций ВПР для суммирования доходов за январь, февраль и март.
| 1 | = ВПР (P3; B3: N6,2; ЛОЖЬ) + ВПР (P3; B3: N6,3; ЛОЖЬ) + ВПР (P3; B3: N6,4; ЛОЖЬ) |
Мы можем объединить эти функции вместе, выполнив следующие действия:
Во-первых, мы устанавливаем функцию ВПР для возврата столбцов 2, 3 и 4 в качестве вывода массива:
| 1 | = ВПР (P3; B3: N6; {2,3,4}; ЛОЖЬ) |
Это даст результат массива:
| 1 | {98, 20, 76} |
Затем, чтобы суммировать результат массива, мы используем функцию SUM.
Важный! Если вы используете Excel версии 2022 или более ранней, вы должны ввести формулу, нажав CTRL + SHIFT + ENTER, чтобы создать формулу массива. Вы узнаете, что сделали это правильно, когда вокруг формулы появятся фигурные скобки. Это не обязательно в Excel 365 (или более новых версиях Excel).
Использование больших размеров массивов в функции ВПР
Мы можем увеличить размер входного массива, чтобы представить больше данных. В следующем примере вычисляется Общий доход от продаж конкретного Магазин в течение 12 месяцев с использованием функции массива, содержащей функцию СУММ, для объединения 12 использований функции ВПР в одну ячейку.
| 1 | {= СУММ (ВПР (P3, B3: N6, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))} |
Другие сводные функции и ВПР
Другие сводные функции можно использовать так же, как и функцию СУММ, для создания альтернативной сводной статистики. Например, мы можем использовать функции MAX, MIN, AVERAGE, MEDIAN, SUM и COUNT, чтобы суммировать Выручка от продаж с января по март:
| 1 | = МАКС (ВПР (J3; B3: H6; {2,3,4}; ЛОЖЬ)) |
| 1 | = МИН (ВПР (J3; B3: H6; {2,3,4}; ЛОЖЬ)) |
| 1 | = СРЕДНИЙ (ВПР (J3; B3: H6; {2,3,4}; ЛОЖЬ)) |
| 1 | = МЕДИАНА (ВПР (J3; B3: H6; {2,3,4}; FALSE)) |
| 1 | = СУММ (ВПР (J3; B3: H6; {2,3,4}; ЛОЖЬ)) |
| 1 | = СЧЁТ (ВПР (J3; B3: H6; {2,3,4}; ЛОЖЬ)) |
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = СУММ (ВПР (P3; B3: N6; {2,3,4}; ЛОЖЬ)) |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | {= СУММ (ВПР (P3; $ B $ 3: $ N $ 6; {2,3,4}; FALSE))} |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Использование СУММ с функцией ВПР в Google Таблицах
Эти формулы работают в Google Таблицах так же, как и в Excel, за исключением того, что для правильной оценки результатов в Google Таблицах необходимо использовать функцию ARRAYFORMULA. Его можно автоматически добавить, нажав клавиши CTRL + SHIFT + ENTER во время редактирования формулы.
| 1 | =ArrayFormula(СУММ(ВПР(O2,A2: M5,{2,3,4},ЛОЖНЫЙ))) |
