В этом уроке вы найдете несколько интересных примеров, демонстрирующих, как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel для поиска и суммирования значений на основе одного или нескольких критериев.
Вы пытаетесь создать сводный файл в Excel, который будет определять все экземпляры определенного значения и суммировать другие значения, связанные с ним? Или вам нужно найти все значения в массиве, которые удовлетворяют заданному условию, а затем просуммировать связанные значения с другого листа? А может быть, перед вами стоит еще более сложная задача, например, просмотреть таблицу всех счетов-фактур вашей компании, найти среди них счета-фактуры конкретного продавца и суммировать их?
Задачи могут быть разными, но смысл их один — вам нужно найти и просуммировать значения на основе одного или нескольких критериев в Excel. Что это за значения? Любое числовое. Что это за критерии? Любой… От числа или ссылки на ячейку, содержащей желаемое значение, до логических операторов и результатов формул Excel.
Итак, есть ли в Microsoft Excel функция, которая может справиться с этими задачами? Конечно! Решение состоит в том, чтобы объединить функции ВПР или ПРОСМОТР с функциями СУММ или СУММЕСЛИ. Следующие ниже примеры формул помогут вам понять, как работают эти функции и как их использовать с реальными данными.
Обратите внимание, что представленные примеры предназначены для опытного пользователя, который знаком с основными принципами и синтаксисом функции ВПР. Если вам еще далеко до этого уровня, рекомендуем обратить внимание на первую часть учебника — функцию ВПР в Excel: синтаксис и примеры.
- ВПР и СУММ: суммируйте все найденные совпадающие значения
- Прочие вычисления ВПР (СРЕДНЕЕ, МАКС., МИН)
- ПОИСК И СУММА: поиск в массиве и суммирование связанных значений
- ВПР и СУММЕСЛИ: суммирует значения, соответствующие определенному критерию
ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений
Если вы работаете с числовыми данными в Excel, очень часто вам нужно не только извлечь связанные данные из другой таблицы, но и суммировать несколько столбцов или строк. Для этого вы можете комбинировать функции СУММ и ВПР, как показано ниже.
Допустим, у вас есть список продуктов с данными о продажах за несколько месяцев с отдельным столбцом для каждого месяца. Источник данных — ежемесячный отчет о продажах:
Теперь нам нужно создать итоговую таблицу с итогами продаж для каждого продукта.
Решение этой проблемы — использовать массив констант в аргументе col_index_num (column_number) функции ВПР. Вот пример формулы:
= СУММ (ВПР (значение поиска; диапазон поиска; {2,3,4}; ЛОЖЬ))
= СУММ (ВПР (найти_значение; таблица; {2; 3; 4}; ЛОЖЬ))
Как видите, мы использовали массив {2,3,4} для третьего аргумента для многократного поиска в одной и той же функции ВПР, чтобы получить сумму значений в столбцах 2, 3 и 4.
Теперь давайте применим эту комбинацию ВПР и СУММ к данным в нашей таблице, чтобы найти общие продажи в столбцах от B до M:
= СУММ (ВПР (B2; «Ежемесячные продажи»! 2 доллара США: 9 млн долларов США; {2,3,4,5,6,7,8,9,10,11,12,13}; ЛОЖЬ))
= СУММ (ВПР (B2; ‘Ежемесячные продажи’! $ A $ 2: $ M $ 9; {2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}; ЛОЖЬ))
Важно! Если вы вводите формулу массива, обязательно нажмите Ctrl + Shift + Enter вместо обычного нажатия Enter. Microsoft Excel заключит вашу формулу в фигурные скобки:
{= SUM (VLOOKUP (B2, ‘Ежемесячные продажи’! $ A $ 2: $ M $ 9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}
{= СУММ (ВПР (B2, ‘Ежемесячные продажи’! $ A $ 2: $ M $ 9; {2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}; FALSE))}
Если ограничиться простым нажатием Enter, расчет будет производиться только по первому значению массива, что приведет к неверному результату.
Вам может быть интересно, почему в приведенной выше формуле в качестве значения поиска отображается [@Product]. Это потому, что мои данные были преобразованы в таблицу с помощью команды «Таблица» на вкладке «Вставка». Мне удобнее работать с полными таблицами Excel, чем с диапазонами. Например, когда вы вводите формулу в одну из ячеек, Excel автоматически копирует ее во весь столбец, экономя вам несколько драгоценных секунд.
Как видите, использовать функции ВПР и СУММ в Excel довольно просто. Однако это далеко не идеально, особенно если вам приходится работать с большими таблицами. Дело в том, что использование формул массива может замедлить работу приложения, поскольку каждое значение в массиве делает отдельный вызов функции ВПР. Оказывается, чем больше значений в массиве, тем больше формул массива в книге и тем медленнее работает Excel.
вы можете решить эту проблему, используя комбинацию функций ИНДЕКС и ПОИСКПОЗ вместо ВПР и СУММ. Позже в этой статье вы увидите несколько примеров таких формул.
Выполняем другие вычисления, используя функцию ВПР в Excel
Мы только что рассмотрели пример того, как можно извлечь значения из нескольких столбцов таблицы и вычислить их сумму. Точно так же вы можете выполнять другие математические операции с результатами, возвращаемыми функцией ВПР. Вот несколько примеров формул:
Вычисляем среднее:
{= СРЕДНЕЕ (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= СРЕДНЕЕ (ВПР (A2; ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}
Формула ищет значение из ячейки A2 в таблице поиска и вычисляет среднее арифметическое значений, найденных на пересечении найденной строки и столбцов B, C и D.
Находим максимум:
{= МАКС (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= МАКС (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}
Формула ищет значение из ячейки A2 в таблице поиска и возвращает максимум значений, найденных на пересечении строки и столбцов, найденных B, C и D.
Находим минимум:
{= MIN (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= МИН (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}
Формула ищет значение из ячейки A2 в таблице поиска и возвращает минимум значений, найденных на пересечении строки и найденных столбцов B, C и D.
Вычисляем % от суммы:
{= 0,3 * СУММ (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= 0,3 * СУММ (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}
Формула ищет значение из ячейки A2 в поисковой таблице, затем суммирует значения, найденные на пересечении строки и столбцов, найденных B, C и D, и только затем вычисляет 30% от общей суммы.
Если мы добавим приведенные выше формулы в таблицу из предыдущего примера, результат будет выглядеть так:
ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений
В случае, если желаемое значение представляет собой массив, функция ВПР становится бесполезной, так как не умеет работать с массивами данных. В такой ситуации вы можете использовать функцию ПРОСМОТР в Excel, которая похожа на ВПР и также работает с массивами так же, как с отдельными значениями.
Давайте рассмотрим пример, чтобы было понятнее, о чем идет речь. Предположим, у нас есть таблица, в которой перечислены имена клиентов, приобретенные товары и их количество (основная таблица). Кроме того, есть вторая таблица, содержащая цены на товары (Таблица поиска). Наша задача — написать формулу, которая находит сумму всех заказов для конкретного клиента.
Как вы помните, функцию ВПР нельзя использовать, если запрошенное значение встречается несколько раз (это массив данных). Вместо этого используйте комбинацию функций СУММ и ПОИСК:
= СУММ (ПОИСК ($ C $ 2: $ C $ 10, ‘Таблица поиска’! $ A $ 2: $ A $ 16, ‘Таблица поиска’! $ B $ 2: $ B $ 16) * $ D $ 2 : $ D $ 10 * ($ B $ 2: $ B $ 10 = $ G $ 1))
= СУММ (ПОИСК ($ C $ 2: $ C $ 10; ‘Таблица поиска’! $ A $ 2: $ A $ 16; ‘Таблица поиска’! $ B $ 2: $ B $ 16) * $ D $ 2 : $ D $ 10 * ($ B $ 2: $ B $ 10 = $ G $ 1))
Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, когда закончите вводить текст.
Таблица поиска — это имя листа, на котором находится диапазон поиска.
Мы разбиваем составные части формулы, чтобы вы понимали, как она работает, и могли настроить ее в соответствии с вашими потребностями. Оставим пока функцию СУММ в стороне, так как ее назначение очевидно.
-
ПОИСК ($ C $ 2: $ C $ 10, «Таблица поиска»! $ A $ 2: $ A $ 16, «Таблица поиска»! $ B $ 2: $ B $ 16)
ПОИСК ($ C $ 2: $ C $ 10; «Таблица поиска»! $ A $ 2: $ A $ 16; «Таблица поиска»! $ B $ 2: $ B $ 16)ПРОСМОТР проверяет элементы, перечисленные в столбце C основной таблицы, и возвращает соответствующую цену из столбца B таблицы поиска).
- $ D $ 2: $ D $ 10 — это количество товаров, приобретенных каждым покупателем, имя которого указано в столбце D основной таблицы. Умножая количество товара на цену, возвращаемую функцией ПОИСК, мы получаем стоимость каждого купленного товара.
- $ B $ 2: $ B $ 10 = $ G $ 1 — формула сравнивает имена клиентов в столбце B основной таблицы с именем в ячейке G1. Если есть совпадение, возвращается 1, в противном случае — 0. Следовательно, имена покупателей, которые отличаются от имени, указанного в ячейке G1, отбрасываются, потому что все мы знаем, что умножение на ноль дает ноль.
Поскольку наша формула является формулой массива, она повторяет описанные выше шаги для каждого значения в массиве поиска. Наконец, функция СУММ вычисляет сумму значений, полученных в результате умножения. Совсем не сложно, согласны?
Комментарий. Для правильной работы ПРОСМОТРА отображаемый столбец должен быть отсортирован в порядке возрастания.
ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию
Функция СУММЕСЛИ в Excel похожа на СУММ, которую мы только что видели, потому что она также складывает значения. Единственное отличие состоит в том, что СУММЕСЛИ суммирует только те значения, которые соответствуют указанным критериям. Например, простейшая формула с СУММЕСЛИ:
= СУММЕСЛИ (LA2: LA10; «> 10»)
= СУММЕСЛИ (LA2: LA10; «> 10»)
— суммирует все значения ячеек в диапазоне A2: A10 больше 10.
Очень просто, правда? Теперь давайте посмотрим на чуть более сложный пример. Предположим, у нас есть таблица, в которой перечислены названия поставщиков и их идентификационные номера (таблица поиска). Кроме того, есть еще одна таблица, в которой те же идентификаторы связаны с данными продаж (основная таблица). Наша задача — найти сумму продаж для конкретного продавца. Здесь 2 отягчающих обстоятельства:
- Основная таблица содержит множество записей для идентификатора в случайном порядке.
- Невозможно добавить столбец с названиями продавцов в основную таблицу.
Мы пишем формулу, которая находит все продажи, сделанные данным продавцом, а также суммирует найденные значения.
Прежде чем мы начнем, позвольте мне напомнить вам синтаксис функции СУММЕСЛИ):
СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])
СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])
- range (диапазон) — аргумент не требует пояснений. Это просто диапазон ячеек, который вы хотите оценить по определенному критерию.
- критерии — условие, указывающее формуле, какие значения следует добавить. Это может быть число, ссылка на ячейку, выражение или другая функция Excel.
- sum_range (диапазон_суммы) — необязательный, но очень важный для нас аргумент. Определяет диапазон связанных ячеек для подведения итогов. Если не указано иное, Excel добавляет значения ячеек в первый аргумент функции.
Собирая все вместе, давайте определим третий аргумент для нашей функции СУММЕСЛИ. Как вы помните, мы хотим суммировать все продажи, сделанные конкретным продавцом, имя которого указано в ячейке F2 (см. Изображение выше).
- диапазон — поскольку мы ищем идентификатор продавца, значения этого аргумента будут значениями в столбце B основной таблицы. Вы можете указать диапазон B: B (весь столбец) или после преобразования данных в таблицу использовать имя столбца Main_table [ID].
- критерии — поскольку имена продавцов зарегистрированы в таблице поиска, мы используем функцию ВПР, чтобы найти идентификатор, соответствующий указанному продавцу. Имя написано в ячейке F2, поэтому для поиска используем формулу:
ВПР ($ F $ 2; Таблица_просмотр; 2; ЛОЖЬ)
ВПР ($ F $ 2; Lookup_table; 2; FALSE)Конечно, вы можете ввести имя в качестве искомого значения непосредственно в функцию ВПР, но лучше использовать абсолютную ссылку на ячейку, поскольку именно так мы создаем универсальную формулу, которая будет работать для любого значения, введенного в нее клетка.
- sum_range (диапазон_суммы) — самая простая часть. Поскольку данные о продажах записываются в столбец C с именем «Продажи», мы просто пишем Main_table [Sales].
Все, что вам нужно сделать, это соединить части вместе, и формула СУММЕСЛИ + ВПР будет готова:
= СУММЕСЛИ (Главная_таблица [ID], ВПР ($ F $ 2, Lookup_table, 2, FALSE), Main_table [Продажи])
= СУММЕСЛИ (Главная_таблица [ID]; ВПР ($ F $ 2; Lookup_table; 2; FALSE); Main_table [Sales])
