Суммирование значений с учетом нескольких условий. Как использовать функцию впр вместе с сумм или суммесли в excel

Функция БДСУММ() , английский вариант DSUM(), суммирует числа в таблице данных, которые удовлетворяют заданным условиям.

Рассмотрим мощную функцию суммирования БДСУММ() , английский вариант DSUM(database, field, criteria ). Эту функцию имеет смысл использовать, когда необходимо просуммировать значения с учетом нескольких условий. Подробный анализ этих задач приводится в группе статей .

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

Синтаксис функции БДСУММ()

Для использования этой функции требуется чтобы:

• исходная таблица имела заголовки столбцов;
• критерии должны были оформлены в виде небольшой таблицы с заголовками;
• заголовки таблицы критериев совпадали с заголовками исходной таблицы (если критерий не задается формулой).

БДСУММ(база_данных;поле;условия )
База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. Верхняя строка таблицы должна содержать заголовки всех столбцов.
Поле - Заголовок столбца, по которому производится суммирование (т.е. столбец с числами). Аргумент Поле можно заполнить введя:

• текст с заголовком столбца в двойных кавычках, например "Возраст" или "Урожай",
• число (без кавычек), задающее положение столбца в таблице (указанной в аргументе база_данных ): 1 - для первого столбца, 2 - для второго и т.д.
• ссылку на заголовок столбца.

Условия - интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для БДСУММ() аналогична структуре для .

Задачи

Предположим, что в диапазоне A 8:С13 имеется таблица продаж, содержащая поля (столбцы) Товар , Продавец и Продажи (см. рисунок выше и файл примера ).

Задача 1 (с одним числовым критерием).

Просуммируем все продажи, которые >3000.

Алгоритм следующий:

Альтернативное решение - =СУММЕСЛИ(C9:C13;F3) или =СУММЕСЛИ(C9:C13;">3000")

Задача 2 (с одним текстовым критерием)

Просуммируем все значения продаж продавца Белов .

Алгоритм следующий:

• Создадим новую табличку критериев, состоящую из заголовка Продавец (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);

• Условие отбора должно быть записано в специальном формате: ="=Белов" (будут суммироваться Продажи только строк, у которых в столбце Продавец содержится точно слово Белов (или белов , беЛОв , т.е. без ). Если имеются строки с Продавцами «Иван Белов», «Белов Иван» и пр., то суммирование по ним производиться не будет.
  Примечание : Если в качестве критерия указать не ="= Белов" , а просто Белов , то, будут суммироваться Продажи строк, у которых в столбце Продавец содержатся значения, начинающиеся со слова Белов (например, «Белов Иван », Белов , белов ).
  Чтобы просуммировать продажи, в том числе и для продавца «Иван Белов », необходимо в качестве критерия указать ="=*Белов". Этот критерий учитывает значения, заканчивающиеся на Белов. Звездочка (*) - это .
  Если в качестве критерия указать *Белов (или ="=*Белов*"), то будут подсчитаны числа, в соответствующих ячейках которых содержится слово Белов.
• Теперь можно наконец записать саму формулу =БДСУММ(B8:C13;C8;B2:B3) Предполагая, что База_данных (исходная таблица) находится в B8:C13 (столбец А (Товар ) можно в данном случае не включать в Базу_данных, т.к. он не участвует в формировании условия и по нему не производится суммирование). С8 – это ссылка на заголовок столбца по которому будет производиться суммирование (т.е. столбец Продажи ). B2:B3 – ссылка на табличку критериев.

Альтернативное решение - =СУММЕСЛИ(B9:B13;"белов";C9:C13)

Задача 3 (Два критерия к разным столбцам строки, Условие И)

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

Формула для сложения: =БДСУММ(B8:C13;C8;F2:G3)

Альтернативное решение - =СУММЕСЛИМН(C9:C13;B9:B13;G3;C9:C13;F3) или =СУММЕСЛИМН(C9:C13;B9:B13;"белов";C9:C13;">3000")

Задача 4 (Два текстовых критерия к одному столбцу, условие отбора ИЛИ)

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

БДСУММ(B8:C13;C8;B2:B4)

Альтернативное решение - =СУММЕСЛИ(B9:B13;"белов";C9:C13)+СУММЕСЛИ(B9:B13;"батурин";C9:C13)

Задача 5 (Два критерия к разным столбцам, условие отбора ИЛИ)

Найдем сумму продаж Белова ИЛИ Продаж >6000 Т.е. нужно отобрать строки, в которых в столбце Продавец значится Белов ИЛИ в столбце Продажи имеется значение >6000 .

Критерии должны располагаться в разных строках и в разных столбцах, т.к. отбираются строки, у которых в поле Продавец значение Белов ИЛИ строки, у которых в поле Продажи значение >6000 (функция БДСУММ () как бы совершает 2 прохода по таблице с разными критериями для 2-х разных полей).

Записать саму формулу можно так =БДСУММ(B8:C13;C8;G2:H4)

Альтернативное решение - =СУММЕСЛИ(B9:B13;G3;C9:C13)+СУММЕСЛИ(C9:C13;H4)-СУММЕСЛИМН(C9:C13;B9:B13;G3;C9:C13;H4) или

=СУММЕСЛИ(B9:B13;"белов";C9:C13)+СУММЕСЛИ(C9:C13;">6000")-СУММЕСЛИМН(C9:C13;B9:B13;"белов";C9:C13;">6000")

Задача 6 (Два текстовых критерия к разным столбцам, условие отбора И)

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

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

Записать саму формулу можно так =БДСУММ(A8:C13;C8;A2:B3)

Альтернативное решение - =СУММЕСЛИМН(C9:C13;A9:A13;"фрукты";B9:B13;"белов")

Задача 7 (Условия отбора, созданные в результате применения формулы)

Просуммируем продажи, которые выше среднего.

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

Для этого введем в ячейку С3 файла примера формулу =C9>СРЗНАЧ($C$9:$C$13) , а в С2 вместо заголовка введем произвольный поясняющий текст, например, «Больше среднего » (заголовок не должен повторять заголовки исходной таблицы).

Обратите внимание на то, что диапазон нахождения среднего значения введен с использованием ссылок ($C$9:$C$13 ), а среднее значение всех продаж таблицы СРЗНАЧ($C$9:$C$13) сравнивается с первым значением диапазона, ссылка на который задана относительной адресацией (C9 ). При вычислении функции БДСУММ() EXCEL увидит, что С9 - это относительная ссылка, и будет перемещаться по диапазону вниз по одной записи и возвращать значение либо ИСТИНА, либо ЛОЖЬ (больше среднего или нет). Если будет возвращено значение ИСТИНА, то соответствующая строка таблицы будет учтена при суммировании. Если возвращено значение ЛОЖЬ, то строка учтена не будет.

Записать формулу можно так =БДСУММ(C8:C13;C8;C2:C3)

Альтернативное решение - =СУММЕСЛИ(C9:C13;">"&СРЗНАЧ($C$9:$C$13))

Задача 8 (Три критерия)

Найдем сумму продаж Белова , которые выше среднего, а также продажи Батурина .

Записать формулу можно так =БДСУММ(B8:C13;C8;B2:C4)

Альтернативное решение - =СУММЕСЛИМН(C9:C13;C9:C13;">"&СРЗНАЧ($C$9:$C$13);B9:B13;"Белов")+СУММЕСЛИ(B9:B13;"Батурин";C9:C13)

Задача 9 (Один текстовый критерий, учитывается РегиСТр)

Сумма продаж Товара ФРУкты (первые три буквы - ЗАГЛАВНЫЕ (т.е. прописные))

Записать формулу можно так =БДСУММ(A8:C13;C8;E2:E3)

Альтернативное решение - =СУММПРОИЗВ(СОВПАД("ФРУкты";A9:A13)*C9:C13)

Предположим, что вы хотите суммировать значения с несколькими условиями, например получить сумму продаж определенного продукта в определенном регионе. В этом случае в формуле следует использовать функцию СУММЕСЛИМН .

Вот пример с двумя условиями: необходимо узнать сумму продаж товара Мясо (из столбца C) в регионе Южный (из столбца A).

Вам понадобится такая формула:

=СУММЕСЛИМН(D2:D11,A2:A11,"Южный",C2:C11,"Мясо") Результат - 14719. Вот как работает эта формула.

СУММЕСЛИМН - это арифметическая формула. Она подсчитывает числа, которые в этом случае находятся в столбце D. Начнем с того, что укажем формуле на расположение чисел:

СУММЕСЛИМН(D2:D11,

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

Дальше вам нужно найти данные, которые соответствуют двум условиям, поэтому вы вводите первое условие, указывая функции, где расположены данные (A2:A11), и название условия («Южный»). Обратите внимание, что между аргументами ставится запятая.

СУММЕСЛИМН(D2:D11;A2:A11;"Южный";

Кавычки вокруг слова "Южный" указывают на то, что используются текстовые данные.

Наконец, вы вводите аргументы для второго условия - диапазон ячеек (C2:C11), которые содержат слово "Мясо", а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. Закончите формулу закрывающейся скобкой ) , затем нажмите клавишу ВВОД и вы получите результат 14 719.

СУММЕСЛИМН(D2:D11;A2:A11,"Южный";C2:C11,"Мясо")

Если вы вводите в Excel функцию СУММЕСЛИМН и не помните аргументов, помощь рядом. После введения выражения =СУММЕСЛИМН(под формулой появляется автозавершение формул со списком аргументов в правильном порядке.

Если посмотреть на изображение использования функции автозавершения формул и списка аргументов, то в нашем примере диапазон_суммирования - это диапазон D2:D11, представляющий собой столбец с числами, которые вы хотите просуммировать; диапазон_условия1 - это диапазон A2.A11, представляющий собой столбец с данными, в котором находится условие1 - “Южный”.

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 - это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 - “Мясо”.

Если вы нажмете СУММЕСЛИМН в автозавершении формулы, откроется статья со справкой.

Попробуйте попрактиковаться

Если вы хотите попрактиковаться с функцией СУММЕСЛИМН, воспользуйтесь предоставленными демонстрационными данными с формулой, в которой фигурирует эта функция.

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

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

В этом уроке Вы найдёте несколько интересных примеров, демонстрирующих как использовать функцию ВПР (VLOOKUP) вместе с СУММ (SUM) или СУММЕСЛИ (SUMIF) в Excel, чтобы выполнять поиск и суммирование значений по одному или нескольким критериям.

Вы пытаетесь создать файл-сводку в Excel, который определит все экземпляры одного конкретного значения и просуммирует другие значения, связанные с ним? Или Вам нужно найти все значения в массиве, удовлетворяющие заданному условию, а затем просуммировать связанные значения с другого листа? Или, может быть, перед Вами встала ещё более трудная задача, например, просмотреть таблицу всех счетов-фактур Вашей компании, найти среди них счета-фактуры определённого продавца и просуммировать их?

Задачи могут отличаться, но их смысл одинаков – необходимо найти и просуммировать значения по одному или нескольким критериям в Excel. Что это за значения? Любые числовые. Что это за критерии? Любые… Начиная с числа или ссылки на ячейку, содержащую нужное значение, и заканчивая логическими операторами и результатами формул Excel.

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

Обратите внимание, приведённые примеры рассчитаны на продвинутого пользователя, знакомого с основными принципами и синтаксисом функции ВПР . Если Вам еще далеко до этого уровня, рекомендуем уделить внимание первой части учебника – Функция ВПР в Excel: синтаксис и примеры .

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

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

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

Теперь нам необходимо сделать таблицу итогов с суммами продаж по каждому товару.

Решение этой задачи – использовать массив констант в аргументе col_index_num (номер_столбца) функции ВПР . Вот пример формулы:

SUM(VLOOKUP(lookup value, lookup range, {2,3,4}, FALSE))
=СУМ(ВПР(искомое_значение;таблица;{2;3;4};ЛОЖЬ))

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

Теперь давайте применим эту комбинацию ВПР и СУММ к данным в нашей таблице, чтобы найти общую сумму продаж в столбцах с B по M :

SUM(VLOOKUP(B2,"Monthly sales"!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))
=СУМ(ВПР(B2;"Monthly sales"! $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,"Monthly sales"!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}
{=СУМ(ВПР(B2;"Monthly sales"!$A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))}

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

Возможно, Вам стало любопытно, почему формула на рисунке выше отображает [@Product] , как искомое значение. Это происходит потому, что мои данные были преобразованы в таблицу при помощи команды Table (Таблица) на вкладке Insert (Вставка). Мне удобнее работать с полнофункциональными таблицами Excel, чем с простыми диапазонами. Например, когда Вы вводите формулу в одну из ячеек, Excel автоматически копирует её на весь столбец, что экономит несколько драгоценных секунд.

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

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

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

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

Вычисляем среднее:

{=AVERAGE(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,{2,3,4},FALSE))}
{=СРЗНАЧ(ВПР(A2;"Lookup Table"!$A$2:$D$10;{2;3;4};ЛОЖЬ))}

Lookup table и вычисляет среднее арифметическое значений, которые находятся на пересечении найденной строки и столбцов B, C и D.

Находим максимум:

{=MAX(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,{2,3,4},FALSE))}
{=МАКС(ВПР(A2;"Lookup Table"!$A$2:$D$10;{2;3;4};ЛОЖЬ))}

Формула ищет значение из ячейки A2 на листе Lookup table и возвращает максимальное из значений, которые находятся на пересечении найденной строки и столбцов B, C и D.

Находим минимум:

{=MIN(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,{2,3,4},FALSE))}
{=МИН(ВПР(A2;"Lookup Table"!$A$2:$D$10;{2;3;4};ЛОЖЬ))}

Формула ищет значение из ячейки A2 на листе Lookup table и возвращает минимальное из значений, которые находятся на пересечении найденной строки и столбцов B, C и D.

Вычисляем % от суммы:

{=0.3*SUM(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,{2,3,4},FALSE))}
{=0.3*СУММ(ВПР(A2;"Lookup Table"!$A$2:$D$10;{2;3;4};ЛОЖЬ))}

Формула ищет значение из ячейки A2 на листе Lookup table , затем суммирует значения, которые находятся на пересечении найденной строки и столбцов B, C и D, и лишь затем вычисляет 30% от суммы.

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

ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений

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

Давайте разберем пример, чтобы Вам стало понятнее, о чём идет разговор. Предположим, у нас есть таблица, в которой перечислены имена клиентов, купленные товары и их количество (таблица Main table). Кроме этого, есть вторая таблица, содержащая цены товаров (таблица Lookup table). Наша задача – написать формулу, которая найдёт сумму всех заказов заданного клиента.

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

SUM(LOOKUP($C$2:$C$10,"Lookup table"!$A$2:$A$16,"Lookup table"!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
=СУММ(ПРОСМОТР($C$2:$C$10;"Lookup table"!$A$2:$A$16;"Lookup table"!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))

Так как это формула массива, не забудьте нажать комбинацию Ctrl+Shift+Enter при завершении ввода.

Lookup table – это название листа, где находится просматриваемый диапазон.

Давайте проанализируем составные части формулы, чтобы Вы понимали, как она работает, и могли настроить её под свои нужды. Функцию СУММ пока оставим в стороне, так как её цель очевидна.

LOOKUP($C$2:$C$10,"Lookup table"!$A$2:$A$16,"Lookup table"!$B$2:$B$16)
ПРОСМОТР($C$2:$C$10;"Lookup table"!$A$2:$A$16;"Lookup table"!$B$2:$B$16)

Функция ПРОСМОТР просматривает товары, перечисленные в столбце C основной таблицы (Main table), и возвращает соответствующую цену из столбца B просматриваемой таблицы (Lookup table).

1. $ D$2:$D$10 – количество товаров, приобретенных каждым покупателем, чьё имя есть в столбце D основной таблицы. Умножая количество товара на цену, которую возвратила функция ПРОСМОТР , получаем стоимость каждого приобретенного продукта.
2. $B$2:$B$10=$ G$1 – формула сравнивает имена клиентов в столбце B основной таблицы с именем в ячейке G1. Если есть совпадение, возвращается 1 , в противном случае 0 . Таким образом, отбрасываются имена покупателей, отличающиеся от указанного в ячейке G1, ведь все мы знаем – умножение на ноль дает ноль.

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

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

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

Функция СУММЕСЛИ (SUMIF) в Excel похожа на СУММ (SUM), которую мы только что разбирали, поскольку она тоже суммирует значения. Разница лишь в том, что СУММЕСЛИ суммирует только те значения, которые удовлетворяют заданному Вами критерию. Например, простейшая формула с СУММЕСЛИ :

SUMIF(A2:A10,">10")
=СУММЕСЛИ(A2:A10;">10")

– суммирует все значения ячеек в диапазоне A2:A10 , которые больше 10 .

Очень просто, правда? А теперь давайте рассмотрим немного более сложный пример. Предположим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID (Lookup table). Кроме этого, есть ещё одна таблица, в которой те же ID связаны с данными о продажах (Main table). Наша задача – найти сумму продаж для заданного продавца. Здесь есть 2 отягчающих обстоятельства:

• Основная таблица (Main table) содержит множество записей для одного ID в случайном порядке.
• Вы не можете добавить столбец с именами продавцов к основной таблице.

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

Перед тем, как мы начнём, позвольте напомнить Вам синтаксис функции СУММЕСЛИ (SUMIF):

SUMIF(range,criteria,)
СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])

• range (диапазон) – аргумент говорит сам за себя. Это просто диапазон ячеек, которые Вы хотите оценить заданным критерием.
• criteria (критерий) – условие, которое говорит формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
• sum_range (диапазон_суммирования) – необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.

Собрав все воедино, давайте определим третий аргумент для нашей функции СУММЕСЛИ . Как Вы помните, мы хотим суммировать все продажи, совершённые определённым продавцом, чьё имя задано в ячейке F2 (смотрите рисунок, приведённый выше).

1. range (диапазон) – так как мы ищем по ID продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Можно задать диапазон B:B (весь столбец) или, преобразовав данные в таблицу, использовать имя столбца Main_table .

criteria (критерий) – так как имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР для поиска ID , соответствующего заданному продавцу. Имя записано в ячейке F2, поэтому для поиска используем формулу:

VLOOKUP($F$2,Lookup_table,2,FALSE)
ВПР($F$2;Lookup_table;2;ЛОЖЬ)

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