Как найти значение в таблице excel по двум условиям

Суммирование значений с учетом нескольких условий

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

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

Вот формула, с помощью которая можно сопровождать эту формулу:

=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)

Результат — значение 14 719.

Рассмотрим каждую часть формулы более подробно.

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

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

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

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

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

Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.

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

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

На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).

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

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

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

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

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

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

Что следует ввести

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.

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

Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.

Примечание: Мастер подметок больше не доступен в Excel.

Ниже в качестве примера по выбору вы можете найти пример использования в этой области.

=ВПР(B2;C2:E7,3,ИСТИНА)

В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «кузьмина» или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.

Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.

В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).

Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.

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

Использование индекса и MATCH вместо ВРОТ

При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.

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

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

Пример работы с ВЛОКОНПОМ

Скопируйте следующие данные в пустую таблицу.

Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).

Двумерный поиск в таблице (ВПР 2D)

Если вы знакомы с функцией ВПР (VLOOKUP) или ее горизонтальным аналогом ГПР (HLOOKUP), то должны помнить, что эта замечательные функции ищут информацию только по одному параметру, т.е. в одномерном массиве — по строке или по столбцу. А если нам необходимо выбирать данные из двумерной таблицы по совпадению сразу двух параметров — и по строке и по столбцу одновременно? Давайте рассмотрим несколько жизненных примеров таких задач и их решения.

Пример 1. Найти значение по товару и городу

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

• Во-первых, нам нужно определить номер строки, соответствующей выбранному пользователем в желтой ячейке товару. Это поможет сделать функция ПОИСКПОЗ (MATCH) из категории Ссылки и массивы (Lookup and Reference) . В частности, формула ПОИСКПОЗ(J2; A2:A10; 0) даст нам нужный результат (для Яблока это будет число 6). Первый аргумент этой функции — искомое значение (Яблоко из желтой ячейки J2), второй — диапазон ячеек, где мы ищем товар (столбец с товарами в таблице — A2:A10), третий аргумент задает тип поиска (0 — точное совпадение наименования, приблизительный поиск запрещен).
• Во-вторых, совершенно аналогичным способом мы должны определить порядковый номер столбца в таблице с нужным нам городом. Функция ПОИСКПОЗ(J3; B1:F1; 0) сделает это и выдаст, например, для Киева, выбранного пользователем в желтой ячейке J3 значение 4.
• И, наконец, в-третьих, нам нужна функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца — функция ИНДЕКС (INDEX) из той же категории Ссылки и массивы (Lookup and Reference) . Первый аргумент этой функции — диапазон ячеек (в нашем случае это вся таблица, т.е. B2:F10), второй — номер строки, третий — номер столбца (а их мы определим с помощью функций ПОИСКПОЗ).

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

=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0) ; ПОИСКПОЗ(J3;B1:F1;0) )

или в английском варианте

Пример 2. Приблизительный двумерный поиск

Слегка модифицируем предыдущий пример. Предположим, что у нас имеется вот такая ситуация:

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

Решение для серой ячейки будет практически полностью аналогично предыдущему примеру:

=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1) ; ПОИСКПОЗ(G3;C6:K6;1) )

=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))

Разница только в последнем аргументе обеих функций ПОИСКПОЗ (MATCH) — Типу сопоставления (здесь он равен минус 1). Это некий аналог четвертого аргумента функции ВПР (VLOOKUP) — Интервального просмотра (Range Lookup). Вообще говоря, возможных значений для него три:

• 1 — поиск ближайшего наименьшего числа, т.е. введенные пользователем размеры двери округлялись бы до ближайших наименьших подходящих размеров из таблицы. В нашем случае высота 500 округлилась бы до 450, а ширина 480 до 300, и стоимость двери была бы 135.
• -1 — поиск ближайшего наибольшего числа, т.е. нестандартная высота 500 округлялась бы до 700, а ширина 480 — до 600 и стоимость составила бы уже 462. Для бизнеса так гораздо интереснее! 🙂
• 0 — поиск точного соответствия без каких либо округлений. Используется для 100%-го совпадения искомого значения с одним из значений в таблице. Естественно, применяется при поиске текстовых параметров (как в прошлом примере), т.к. для них округление невозможно.

Важно отметить, что при использовании приблизительного поиска с округлением диапазон поиска — а значит и вся таблица — должна быть отсортирована по возрастанию (для Типа сопоставления = 1) или по убыванию (для Типа сопоставления = -1) по строчкам и по столбцам. Иначе приблизительный поиск корректно работать не будет!

Для точного поиска (Тип сопоставления = 0) сортировка не нужна и никакой роли не играет.

P.S. Обратная задача

В комментах неоднократно интересуются — а как сделать обратную операцию, т.е. определить в первом примере город и товар если мы знаем значение из таблицы? Тут потребуются две небольшие формулы массива (не забудьте ввести их с помощью сочетания клавиш Ctrl+Shift+Enter, а не обычного Enter):

Принцип их работы следующий:

1. перебираем все ячейки в диапазоне B2:F10 и ищем совпадение с искомым значением (13) из ячейки J4 с помощью функции ЕСЛИ (IF)
2. когда нашли совпадение, то определяем номер строки (столбца) первого элемента в таблице в этой строке (столбце) с помощью функций СТОЛБЕЦ (COLUMN) и СТРОКА (ROW)
3. выдергиваем значение города или товара из таблицы с помощью функции ИНДЕКС (INDEX)

Ссылки по теме

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

Примеры функций ИНДЕКС и ПОИСКПОЗ по нескольким критериям Excel

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

Примеры использования функций ИНДЕКС и ПОИСКПОЗ по двум критериям в Excel

Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:

1. Сколько очков заработала команда (поиск по названию) на данный момент.
2. Суммарное значение очков, заработанных всеми командами.
3. Сколько игр было сыграно какой-либо командой.

Вид исходной таблицы данных:

Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:

В открывшемся диалоговом окне необходимо выбрать «Тип данных:» — «Список» и указать в поле «Источник» диапазон ячеек:

Переходим в ячейку A13 и выполняем аналогичные действия только лишь указываем другую ссылку на диапазон в поле «Источник:»

Такой же выпадающий список следует создать и для ячейки A15.

Для подсчета общего количества очков в ячейке B11 используем формулу:

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

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

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

Для определения количества очков используем формулу ИНДЕКС, в которой оба аргумента, указывающие номер строки и столбца, будут принимать значения, возвращаемые функцией ПОИСКПОЗ:

В результате мы получили значение по 2-м критериям:

1. – «Челси».
2. – «Очки».

Динамическое суммирование диапазона ячеек по критерию в Excel

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

Вид таблицы данных:

Искомое значение может быть найдено с помощью следующей формулы:

Функция СУММ рассчитывает сумму значений, хранящихся в столбце «Очки», при этом количество ячеек для расчета может быть задано с помощью критерия – выбранного названия команды. Функция ИНДЕКС может возвращать не только значение, хранящееся в искомой ячейке, но и ссылку на эту ячейку. Поэтому можно использовать запись типа E2:ИНДЕКС(…). В результате выполнения функция ИНДЕКС вернет ссылку на ячейку, и приведенная выше запись примет, например, следующий вид: E2:E4 (если выбрана команда «Манчестер Ю.».

Проверим результат выборочного динамического суммирования столбца таблицы с верху вниз.

Сумма чисел в диапазоне E2:E7 и в ячейке B13 совпадает все ОК.

Подсчет количества рабочих дней в Excel по условию начальной даты

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

Вид таблицы данных:

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

Для определения искомого значения даты используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

Первая функция ИНДЕКС выполняет поиск ячейки с датой из диапазона A1:I1. Номер строки указан как 1 для упрощения итоговой формулы. Функция СТОЛБЕЦ возвращает номер столбца с ячейкой, в которой хранится первая запись о часах работы. Выражение «ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>«»» выполняет поиск первой непустой ячейки для выбранной фамилии работника, указанной в ячейке A10 (<>”” – не равно пустой ячейке). Второй аргумент «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер строки с выбранной фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>«»» — номер позиции значения ИСТИНА в массиве (соответствует номеру столбца), полученном в результате операции сравнения с пустым значением.

Примеры определения дат для нескольких сотрудников:

Для автоматического подсчета количества только рабочих дней начиная от даты приема сотрудника на работу, будем использовать функцию ЧИСТРАБДНИ:

Для проверки выберем другую фамилию сотрудника из выпадающего списка в ячейке A9:

Особенности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel

Функция ИНДЕКС может возвращать ссылку или массив значений из одного диапазона или нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек или константу массива. При этом последующие аргументы позволяют указать номера интересующих строки и столбца относительно выбранного диапазона, а также порядковый номер диапазона (если диапазоны ячеек не являются смежными, например, при поиске в различных таблицах). В простейшем случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строки и столбца. Например, =ИНДЕКС(A2:B5;2;2) вернет значение, которое хранится в ячейке B3, поскольку третья строка является второй по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.

При необходимости можно получить целую строку или целый столбец, указав в качестве номера строки и столбца соответственно значение 0 (нуль). Для вывода полученных строки или столбца функцию ИНДЕКС необходимо использовать в качестве формулы массива.

Функция ПОИСКПОЗ используется для поиска указанного в качестве первого аргумента значения в диапазоне ячеек или константе массива. Она возвращает относительную позицию найденного элемента или код ошибки #Н/Д, если искомые данные отсутствуют. При поиске числовых значений можно использовать нежесткие критерии: ближайшее наибольшее или ближайшее наименьшее числа заданному.

Поскольку ПОИСКПОЗ возвращает относительную позицию элемента в диапазоне, то есть, номер строки или столбца, эта функция может быть использована как один или сразу два аргумента функции:

=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))

Такая формула используется чаще всего для поиска сразу по двум критериям.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры