Excel выборка по нескольким условиям

Как выбрать в Экселе все значения по нескольким условиям

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

В этой статье мы решим такую задачу. Описываю пример. Есть ведомость школьных оценок 1-3 классов. Мы хотим выбрать класс, оценку, и увидеть учеников класса, которые получили этот балл:

Задача интересная, и достаточно простая. Однако, нам понадобится сделать дополнительный столбец. Действуем в несколько этапов.

Создание вспомогательной колонки

Сейчас необходимо добавить справа от ведомости еще один столбец. Назовём его «технический», т.к. данные в нем будут неинформативными, вспомогательными.

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

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

Формулу укажу на примере ячейки D3:

Функция СУММ для каждой строки сложит значение из предыдущей ячейки, а так же, результат сравнения класса в ведомости с искомым, оценки в ведомости с искомыми. Два уточнения:

• В ячейке D2 – текст, и функция СУММ его проигнорирует. В остальных строчках будет браться число из ячейки сверху;
• ФОРМУЛА И(A3=$L$2;C3=$L$3) вернёт ИСТИНУ (единицу), когда оба условия внутри неё выполнятся, или ЛОЖЬ (ноль), если хотя бы одно из условий не выполнится. Таким образом, когда функция И найдет совпадение класса и оценки, СУММ увеличит порядковый номер на единицу, что нам и требовалось

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

Вывод отобранных строк в таблицу

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

Алгоритм работы на примере ячейки G3:

1. Функция ПОИСКПОЗ получает числовой индекс строки из ячейки F3 и ищет такое же значение в техническом столбце D. Возвращает либо порядковый номер найденной ячейки в массиве, либо ошибку #Н/Д
2. Функция ИНДЕКС получает из ведомости класс, находящийся в ячейке, порядковый номер которой мы получили в первом пункте
3. Далее функция ЕСЛИОШИБКА выводит на экран класс, если он найден, или пустую строку, если ПОИСКПОЗ вернула ошибку

Такой порядок работает и для остальных столбцов: «Фамилия», «Баллы». Теперь если изменить искомый класс или оценку, формулы сами всё пересчитают и переформируют список. Можно скрыть дополнительный столбец, чтобы не мешал.

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

Мы получили полностью автоматизированный интерактивный проект, способный формировать списки «налету», сразу после того, как вы сделаете свой выбор. На этом всё, и я готов отвечать на ваши вопросы!

ВПР по двум и более критериям

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

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

Tips_All_VlookupMany.xls (62,5 KiB, 5 127 скачиваний)

Например, имеется файл с таблицей такого вида:

И необходимо получить сумму выручки не только на основании салона, но и на основании бренда. При этом делать это автоматически, например для получения данных в такую табличку:

Т.е. в ячейке B2 из выпадающего списка выбирается Салон, а из B3 — Бренд(подробнее про выпадающие списки можно прочесть в статье Выпадающие списки). И в зависимости от выбора должна рассчитаться сумма Выручки и должен проставиться комментарий.

В приложенном к статье файле исходная таблица находится на листе » Отчет сентябрь «, а вторая(с выбором) на листе » Выбор «. На самом деле это не сильно принципиально, просто будет более понятна логика формул, приведенных ниже.
Сумму по двум критериям можно найти при помощи той же СУММЕСЛИМН (SUMIFS) :
=СУММЕСЛИМН(‘Отчет сентябрь’!$C$2:$C$67;’Отчет сентябрь’!$A$2:$A$67; B2 ;’Отчет сентябрь’!$B$2:$B$67; B3 )
=SUMIFS(‘Отчет сентябрь’!$C$2:$C$67,’Отчет сентябрь’!$A$2:$A$67,Выбор!B2,’Отчет сентябрь’!$B$2:$B$67,Выбор!B3)
Подробнее про поиск сумм по двум и более критериям можно прочитать в статье Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям.
А вот с комментарием сложнее — там содержится текст и СУММЕСЛИМН ничего не найдет. Да и в случае, если в исходных данных есть задвоения и суммировать по определению ничего не надо СУММЕСЛИМН будет не лучшим вариантом — ведь она вернет сумму всех ячеек, удовлетворяющих условиям. А это, повторюсь — не всегда нужно.
Здесь на помощь придет родственная с ВПР функция — ПОИСКПОЗ (MATCH) :
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( B2 & B3 ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67,0))
Эта функция является формулой массива. Это означает, что вводить её в ячейки надо нажатием не просто Enter, а сочетанием трех клавиш — Ctrl+Shift+Enter. Теперь рассмотрим подробнее принцип работы этой формулы, чтобы можно было применить на любых данных. На самом деле принцип не так уж сложен. Основной упор сделан на эти две «связки»:

• B2 & B3 — здесь объединяем значение выбранного Салона(Санкт-Петербург) и Бренда(Ниссан) в одну строку, чтобы получилось «Санкт-ПетербургНиссан». За объединение двух значений отвечает амперсанд(&).
• ‘Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67 — а здесь мы последовательно объединяем в одну строку значения двух столбцов исходных данных — Салон и Бренд. Т.е. в результате мы получим массив объединенных значений: МоскваBMW, ВоронежBMW, СамараЛексус и т.д. (именно это объединение требует, чтобы формула вводилась как формула массива) . И уже среди этих значений ищем «Санкт-ПетербургНиссан». При нахождении совпадения ПОИСКПОЗ вернет позицию строки, в которой нашел. И передаст её в ИНДЕКС. А т.к. ИНДЕКС в нашем случае возвращает значение из заданной строки указанного массива, то мы получаем требуемое.

По шагам это будет выглядеть так:

сначала искомые значения будут объединены в один
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( B2 & B3 ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( Санкт-Петербург & Ниссан ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( Санкт-Петербург Ниссан ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
далее так же объединяем построчно значения всех столбцов для поиска значений
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( Санкт-Петербург Ниссан ;<"МоскваBMW":"ВоронежBMW":"СамараЛексус". >;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;4)
=>
только допы

Здесь важно следить за тем, чтобы значения в исходном массиве для поиска объединялись в том же порядке, что и искомые значения. На приложенном примере, если первым аргументом(искомое_значение) в ПОИСКПОЗ мы указали сначала Салон( B2 ) , а потом Бренд( B3 ) , то и вторым аргументом(просматриваемый_массив) мы должны указать два столбца в том же порядке — сначала Салон( ‘Отчет сентябрь’!$A$2:$A$67 ), а уже потом Бренд( ‘Отчет сентябрь’!$B$2:$B$67 ). Если их перепутать ошибки как таковой это не вызовет, но и значения найдены не будут.
Так же стоит учитывать, что такая формула будет рассчитываться дольше обычной, поэтому не стоит указывать столбцы полностью( ‘Отчет сентябрь’!$A:$A ), т.к. это может повлечь за собой расчет всего одной формулы непозволительно долгое время.
При этом очевидно, что просматривать значения можно не только из смежных столбцов, а из любых и уж точно они могут быть и первыми, и последними. И значения возвращаемые могут так же располагаться в любом столбце таблицы. И также хочу заметить, что это в примере используется только два критерия — реально их можно сделать и три, и пять, и десять. Объединяем сколько надо, указываем нужное кол-во столбцов так же с объединением и все. Но тут надо понимать, что в некоторых случаях будет оптимальнее добавить в исходные данные(те, где ищем) еще один столбец, в котором записать все данные, объединив их. Т.е. опять же на примере файла из статьи можно на листе «Отчет сентябрь» добавить формулу в столбец E: = A2 & B2
и затем искать можно будет обычной формулой:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( B2 & B3 ;’Отчет сентябрь’!$E$2:$E$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$E$2:$E$67,0))
Больше нюансов, о которых я бы хотел рассказать, нет. Все стандартно, как для обычной связки ИНДЕКС(ПОИСКПОЗ .

В приложенном к статье примере приведен пример и с функцией СУММЕСЛИМН и с функцией ПОИСКПОЗ.
Скачать пример:

Tips_All_VlookupMany.xls (62,5 KiB, 5 127 скачиваний)

P.S. Если кого-то пугает тот факт, что формулу надо вводить как формулу массива(Ctrl+Shift+Enter), то можно модифицировать формулу так:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;СУММПРОИЗВ(МАКС(( B2 =’Отчет сентябрь’!$A$2:$A$67)*( B3 =’Отчет сентябрь’!$B$2:$B$67)*(СТРОКА( $B$2:$B$67 )-1))))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,SUMPRODUCT(MAX((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(ROW($B$2:$B$67)-1))))
Здесь вместо ПОИСКПОЗ роль поиска номера строки в массиве ‘Отчет сентябрь’!$D$2:$D$67 играет СУММПРОИЗВ. Сам принцип работы СУММПРОИЗВ я описывал в этой статье — Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям. К этому могу добавить лишь некоторые разъяснения(хотя если хотите понять сам принцип — лучше потратить пару минут на статью).
( B2 =’Отчет сентябрь’!$A$2:$A$67) — здесь значение выбранного салона сверяется со списком салонов на листе Отчет сентябрь . Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
( B3 =’Отчет сентябрь’!$B$2:$B$67) — здесь значение выбранного бренда сверяется со списком брендов на листе Отчет сентябрь . Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
В результате перемножения этих двух выражений мы получим массив 1 и 0(1 будет там, где бренд и салон совпали, 0 — где различаются), т.к. ИСТИНА для Excel по сути =1, а ЛОЖЬ =0.
Далее полученный массив единиц и нулей перемножаем на выражение: СТРОКА( $B$2:$B$67 ) . Это выражение дает нам массив номеров строк т.к. в выражении берется номер строки на листе, а нам нужен номер в диапазоне ‘Отчет сентябрь’!$D$2:$D$67 , то мы еще вычитаем 1(т.к. диапазон у нас начинается со второй строки). С таким же успехом можно было не вычитать, а либо все диапазоны взять с первой строки, либо указать так: СТРОКА( $B$1:$B$66 )
Полученный массив строк перемножаются на массив единиц и нулей салона и бренда. В результате получим номер строки, умноженный на 1 там, где салон и бренд равны искомым, и на ноль там, где отличаются. И в итоге массив номеров строки и нулей. И из этого отбирается максимальный номер строки.
По сути, это уже решает проблему поиска, но если значений, подходящих под условия больше одного, то именно такой результат может быть(даже скорее всего будет) неверным. Чтобы этого избежать мы применяем функцию МАКС (MAX) , чтобы в результате из всех строк была отобрана только максимальная. Т.е. в результате мы получим не первое совпадение из всех возможных, а последнее.
Так же рекомендую ознакомиться со статьей Как просмотреть этапы вычисления формул, чтобы можно было самостоятельно поразбираться в принципах работы формул.

В сети можно найти так же решения при помощи макросов(VBA), но далеко не всегда есть возможность использовать макросы в файлах.

Статья помогла? Поделись ссылкой с друзьями!

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

Постановка задачи

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

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

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

Способ 1. Дополнительный столбец с ключом поиска

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

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы : Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

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

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

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Как сделать выборку в Excel с помощью формул массива

С помощью средств Excel можно осуществлять выборку определенных данных из диапазона в случайном порядке, по одному условию или нескольким. Для решения подобных задач используются, как правило, формулы массива или макросы. Рассмотрим на примерах.

Как сделать выборку в Excel по условию

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

Сначала научимся делать выборку по одному числовому критерию. Задача – выбрать из таблицы товары с ценой выше 200 рублей. Один из способов решения – применение фильтрации. В результате в исходной таблице останутся только те товары, которые удовлетворяют запросу.

Другой способ решения – использование формулы массива. Соответствующие запросу строки поместятся в отдельный отчет-таблицу.

Сначала создаем пустую таблицу рядом с исходной: дублируем заголовки, количество строк и столбцов. Новая таблица занимает диапазон Е1:G10.Теперь выделяем Е2:Е10 (столбец «Дата») и вводим следующую формулу: < >.

Чтобы получилась формула массива, нажимаем сочетание клавиш Ctrl + Shift + Enter. В соседний столбец – «Товар» — вводим аналогичную формулу массива: < >. Изменился только первый аргумент функции ИНДЕКС.

В столбец «Цена» введем такую же формулу массива, изменив первый аргумент функции ИНДЕКС.

В результате получаем отчет по товарам с ценой больше 200 рублей.

Такая выборка является динамичной: при изменении запроса или появлении в исходной таблице новых товаров, автоматически поменяется отчет.

Задача №2 – выбрать из исходной таблицы товары, которые поступили в продажу 20.09.2015. То есть критерий отбора – дата. Для удобства искомую дату введем в отдельную ячейку, I2.

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

Подобные формулы вводятся и в другие столбцы (принцип см. выше).

Теперь используем текстовый критерий. Вместо даты в ячейку I2 введем текст «Товар 1». Немного изменим формулу массива: < >.

Такая большая функция выборки в Excel.

Выборка по нескольким условиям в Excel

Сначала возьмем два числовых критерия:

Задача – отобрать товары, которые стоят меньше 400 и больше 200 рублей. Объединим условия знаком «*». Формула массива выглядит следующим образом: < =C2:C10);СТРОКА(C2:C10);"");СТРОКА(C2:C10)-СТРОКА($C$1))-СТРОКА($C$1))' >>.

Это для первого столбца таблицы-отчета. Для второго и третьего – меняем первый аргумент функции ИНДЕКС. Результат:

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

Случайная выборка в Excel

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

Исходный набор данных:

Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:

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

Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:

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