Статья объясняет принципы применения функций для поиска и суммирования, а также их комбинации. Предлагаемые к рассмотрению формулы работают с одним или несколькими критериями и различными видами данных для их сведения в одну таблицу из нескольких, подведения общих итогов на основе данных и поиска информации в таблицах с большим количеством данных по указанным в условии аргументам. Эти сочетания функций:

1. Используются для создания сводных таблиц в Excel, которые производят вычисления необходимых данных по указанному условию, после чего суммируют данные, связанные с полученными результатами.
2. Также они применяются для поиска (по обозначенным критериям) значения в массиве данных с последующим суммированием выделенных по общим признакам значений со второго листа.
3. Кроме того, сочетания упомянутых функций применяются для поиска конкретного исполнителя из общего списка счетов и сложения всех значений по итогам поиска.

Задачи перед пользователем могут стоять различные, но суть сводится:

• к поиску по заданным условиям и суммированию полученных значений
• или к сведению большого объема данных в единую таблицу в Excel.

Условия поиска могут указываться абсолютно разные: от цифр и ссылок на конкретные ячейки с необходимыми данными до логических функций и полученных при помощи других формул данных.

Неужели Microsoft Excel создал функцию, способную справляться с упомянутыми выше задачами? Аналогичные задачи решаются путем составления сложных формул с ВПР() или ПРОСМОТР() и СУММ() или СУММЕСЛИ(). Далее приведены примеры, в которых показаны случаи и способы применения этих функций.

Следует учитывать, что такие развернутые формулы подразумевают, что пользователь знает общие правила применения функции ВПР() и ее ввода. В противном случае, рекомендуется вернуться к начальному уроку по функции ВПР(), ее базовому использованию и составу.

Суммирование значений по заданным аргументам при помощи функций ВПР и СУММ

При работе с числами часто необходимо не только извлечь цифры из таблицы, но вместе с тем и сложить числа из выбранных строк или столбцов. В таких случаях применяется сочетание функций ВПР() и СУММ().

Применение сочетания двух этих функций здесь рассматривается на примере таблицы со списком продуктов и итогами их продаж за определенный промежуток времени, с разбивкой на каждый месяц. Итак, нужно посчитать размеры продаж по всем продуктам по отдельности за весь период.

Таблица данных — ежемесячные продажи:

Эта задача решается через использование в функции ВПР() массива в третьем аргументе (номер_столбца).

=СУММ (ВПР (искомое значение, интервальный просмотр, {2,3,4}, ЛОЖЬ)).

Исходя из примера выше, в массиве {2, 3, 4} рассматриваемого аргумента для суммирования чисел из столбцов 2,3 и 4 используются функции ВПР().

Для получения искомого числа из столбцов от B до M, сочетание функций ВПР() и СУММ() выглядит так:

=СУММ (ВПР (B2, ‘Monthly sales’! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))

ВНИМАНИЕ! Формулы, содержащие массивы данных, закрываются через сочетание клавиш Ctrl+Shift+Enter, а не привычным Enter. Формула закроется в фигурные скобки, если произвести манипуляции таким образом.

{=СУММ (ВПР(B2, ‘Monthly sales’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))}

В случае закрытия функции через Enter, расчет будет сделан не полностью, а только по первому аргументу массива.

СОВЕТ. Искомое значение может указываться при помощи ссылки на столбец [@Product], как показано на рисунке 2. Поскольку рассматриваемая в примере таблица создана посредством инструмента «Вставка” → «Таблица», она стала полнофункциональной. В такой таблице внесенные изменения по умолчанию дублируются на выбранный столбец в вышестоящую ячейку.
Исходя из указанной информации, можно сделать вывод о том, что совмещать функции ВПР() и СУММ() в работе достаточно просто. Хотя предложенный способ — не единственно верный путь, в частности, при работе с таблицами с большим количеством данных. В связи с тем, что каждый аргумент делает запрос к функции ВПР(), применение большого количества формул массива отрицательно сказывается на скорости работы книги Excel. Отсюда вывод: большое количество формул в книге и аргументов в массиве может существенно замедлить работу Excel.

Применение сочетания функций ПОИСКПОЗ() и ИНДЕКС() вместо ВПР() и СУММ() поможет обойти эту проблему. Использование предложенных формул будет изучаться в другой обучающей статье.

Другие виды задач с использованием функции ВПР в Excel

С использованием функции ВПР(), кроме выполнения математических вычислений из нескольких столбцов с использованием извлеченных по аргументам данных, аналогичным образом можно решить другие математические задачи совместно с другими дополнительными функциями:

Операция	Пример	Описание
Расчет среднего значения	{=СРЗНАЧ(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2, 3, 4}, ЛОЖЬ))}	Формула находит среднее значение из столбцов B, C и D таблицы ‘Lookup table’ в той же строке для ячейки A2.
Поиск максимального значения	{=МАКС(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2,3,4}, ЛОЖЬ}	По значению ячейки A2 формула находит максимальное число в столбцах B, C и D таблицы ‘Lookup Table’ в этой же строке.
Поиск минимального значения	{=МИН(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2,3,4}, ЛОЖЬ))}	Для ячейки A2 формула находит минимальное число в столбцах B, C и D этой же строки таблицы ‘Lookup Table’.
Расчет процента от суммы	{=0.3*СУММ(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2,3,4}, ЛОЖЬ))}	Формула подбирает значение, равное указанному в ячейке A2 из таблицы ‘Lookup Table’, далее суммирует значения в столбцах B, C и D в этой же строке, далее высчитывает из полученного числа 30%.

Внимание! Так как указанные выше функции могут содержать в себе массивы данных, заканчиваться они должны через сочетание клавиш Ctrl+Shift+Enter для правильного отображения результата.

Если рассматривать вышеуказанные формулы по образцу таблицы из первого примера, полученные результаты будут выглядеть вот так:

ПРОСМОТР и СУММ – просмотр массива данных и суммирования соответствующих критериям данных

При необходимости поиска значений среди большого количества данных, а не отдельного числа, функция ВПР() окажется безрезультатной, так как она не предусматривает работу с массивом данных. Тогда следует применять функцию ПРОСМОТР(). Она аналогична функции ВПР(), но подходит для работы с массивами и отдельными аргументами.

Например, есть две таблицы: в первой — список покупателей, список купленных ими продуктов и их количество (основная таблица), во второй — стоимость за единицу каждого продукта (таблица поиска). Задача — узнать общую стоимость покупок конкретного покупателя.

Так как здесь множественный выбор из нескольких данных, применение функции ВПР() становится невозможным. Вместо нее используем сочетание функций СУММ() и ПРОСМОТР():

=СУММ (ПРОСМОТР ($C$2:$C, ‘Lookup table’!$A$2:$A$16, ‘Lookup table’! $B$2:$B$16)*$D$12:$D$10*($B$2:$B$10=$G$1))

Поскольку в состав формулы входит массив данных, заканчивать ее необходимо сочетанием клавиш Ctrl+Shift+Enter.

Подробный анализ этой формулы с разбором составляющих поможет применять ее и для выполнения других задач, связанных с подбором данных и их суммированием из разных таблиц.

Назначение формулы СУММ() очевидно и рассматриваться будет в последнюю очередь. Проведем анализ 3 сложных аргументов, входящих в состав формулы:

1. =ПРОСМОТР ($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)

В данном случае функция ПРОСМОТР() ищет товары из списка столбца C в основной таблице и указывает их цену за единицу товара из столбца B в таблице поиска:

2. $D$2:$D$10

Этот пример возвращает количество купленного каждым покупателем товара из списка в столбце D основной таблицы. Умноженное на цену, возвращенное функцией ПРОСМОТР() — считает итоговую стоимость каждого купленного товара:

3. $B$2:$B$10=$G$1

Здесь анализируется список покупателей в столбце B с заданным значением к ячейке G1. В случае соответствия, он указывает значение «1», если соответствия нет – «0». Это условие применяется только для того, чтобы «отсечь» других покупателей. Так как всем известно, что результат любого умножения на 0 дает 0.

В связи с тем, что эта формула производит упомянутый выше процесс для каждого отдельного аргумента в массиве поиска, последним действием осуществляется суммирование полученных результатов приведенных выше математических вычислений через функцию СУММ().

Внимание! Чтобы формула ПРОСМОТР() корректно работала, разместить данные в таблице просмотра необходимо по возрастанию (от А до Я). Если сортировка недоступна для указанных аргументов, применяется функция ТРАНСП.

ВПР() и СУММЕСЛИ() находит и суммирует данные, соответствующие указанному условию.

Функция СУММЕСЛИ() аналогична СУММ(), которая описывалась выше, используется для суммирования чисел. Различие состоит в том, что при функции СУММЕСЛИ() суммируются значения, соответствующие определенным пользователем критериям. Самый простой пример функции: СУММЕСЛИ = СУММЕСЛИ (A2:A10, “>10”) складывает числа больше 10 из ячеек от A2 до A10.

Также эта функция подходит для более сложных задач. Например, есть таблица поиска со списком имен продавцов и их идентификационные номера. Во второй таблице (основная таблица) — те же ID и итоги продаж по всем продавцам по отдельности. Задача — определить размер продаж по ID конкретного продавца. Однако, есть два усложняющих фактора:

•  Во второй таблице один и тот же ID повторяется.
•  Добавлять имена в основную таблицу нельзя.

Для составления формулы вначале надо найти все суммы продаж по конкретному человеку, и во-вторых — суммировать найденные цифры.

Синтаксис формулы СУММЕСЛИ (диапазон, критерий, [диапазон суммирования])

Диапазон – значение параметра понятно из наименования. Желаемые ячейки, выбранные для сравнения по указанным параметрам.

• Критерий – аргумент, определяющий значения из диапазона для произведения вычислений. Выражение данного аргумента допускается в числовой форме, в форме функции или ссылки.
• Диапазон_суммирования – этот аргумент обязателен для ввода, но может оказаться важным. Это условие означает диапазон ячеек, где будет выводиться результат. Если оставить его по умолчанию, результаты отобразятся в ячейках диапазона (1-ый параметр).

Принимая во внимание определения трех параметров, задача об определении общего количества продаж каждого отдельного продавца, заданного в ячейке F2 основной таблицы (см. рисунок выше).

1. Диапазон — поиск продавца происходит по ID, параметр ‘диапазон’ в функции СУММЕСЛИ() определяется как столбец B в первой таблице. Таким образом, можно указать B:B, или, если показатели переведены в формат таблицы, возможно использование название столбца: Main_table [ID].

2. Критерий — продавцы представлены списком в таблице поиска, нужно применять функцию ВПР() для соотношения ID с именем продавца. Нужный продавец выбирается в ячейке F2 основной таблицы. Полученная формула выглядит следующим образом: ВПР ($F$2, Lookup_table, 2, ЛОЖЬ).

Разумеется, в этой формуле можно указать имя конкретного продавца, но рациональнее применять ссылку на ячейку, потому что при таком подходе формула становится универсальной, появляется возможность поиска любого сотрудника, указанного по ссылке.

3. Диапазон суммирования — так как суммы продаж находятся в столбце C с названием «Sales», этот параметр указывается ссылкой: Main_table [Sales].

Теперь из двух составляющих СУММЕСЛИ и ВПР собирается единая формула:

=СУММЕСЛИ(Main_table[ID], ВПР($F$2, Lookup_table, 2, ЛОЖЬ), Main_table[Sales])

Использование ВПР без формул в Excel

Сведения из таблиц можно просматривать, сравнивать и объединять в Excel без помощи формул. Создана специальная надстройка Merge Tables Wizard. Она создана с целью сэкономить время пользователей и в качестве простой замены формул ВПР() и ПРОСМОТР(), что может оказаться очень полезно как для начинающих пользователей, так и для продвинутых.

Вместо составления длинных формул в плагине вносятся основные и поисковые таблицы, определяется общий столбец или столбцы, и указывается, какие данные должны вернуться.

Программа автоматически сравнивает две выбранные пользователем таблицы по принципу формулы ВПР, находит сходства в двух таблицах и обновляет первую таблицу. Кроме этого, здесь есть возможность выбирать столбцы, которые нужно сравнить, обновлять информацию, выделять или отфильтровывать обновленные данные.

Можно сделать заключение, что использование функций, описанных выше (или плагина для замены ВПР), можно создать многофункциональную таблицу для сведения информации из нескольких таблиц в одну, редактировать ее и подводить итоги и расчеты числовых и текстовых данных из таблицы, а также осуществлять их поиск.