Запрос на выборку данных (формулы) в MS EXCEL

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра). Произведем отбор значений из исходной таблицы с помощью формул массива. В отличие от применения Фильтра (CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр) отобранные строки будут помещены в отдельную таблицу.

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

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий - число).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра. Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Цены выберите Числовые фильтры..., затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6, таблицу для отфильтрованных данных - в диапазоне D10:E19

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива:

=ИНДЕКС(A11:A19;
НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-СТРОКА($B$10))

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER (формула массива будет возвращать несколько значений).

Те же манипуляции произведем с диапазоном E11:E19 (столбец Цена) куда и введем аналогичную формулу массива:

=ИНДЕКС(B11:B19;
НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-СТРОКА($B$10))

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

Чтобы показать динамизм полученного Запроса на выборку, введем в Е6 значение 55. В новую таблицу попадет только 2 записи.

Если в Исходную таблицу добавить новый товар с Ценой 80, то в новую таблицу автоматически будет добавлена новая запись.

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

2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Диапазон Чисел).

Критерии (нижнюю и верхнюю границы цены) разместим в диапазоне Е5:Е6.

Т.е. если Цена Товара попадает в указанный интервал, то такая запись появится в новой таблице Отфильтрованные данные.

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

Теперь выделим диапазон D11:D19 и в Строке формул введем формулу массива:

=ИНДЕКС(Товары;
НАИМЕНЬШИЙ(
ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);"");
СТРОКА(Цены)-СТРОКА($B$10))-СТРОКА($B$10))

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER.

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива:

=ИНДЕКС(Цены;
НАИМЕНЬШИЙ(
ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);"");
СТРОКА(Цены)-СТРОКА($B$10))-СТРОКА($B$10))

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок).

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий - Дата).

Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия <= используется =):

=ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

=ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

4. Два критерия Дата (Выбрать те Товары, у которых Дата поставки попадает в диапазон)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Диапазон Дат).

Для отбора строк используются формулы массива, аналогичные Задаче2:

=ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

=ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий - Дата (не позже)).

Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:

=ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

Также в файле примера приведены формулы для условий: Не раньше (не включая); Не позже (включая); Не позже (не включая).

Эта формула введена как формула массива, возвращающая множество значений (см. здесь Формулы массива в MS EXCEL, возвращающие несколько значений). Эту формулу можно переделать, чтобы возвращалось только 1 значение, см. следующую задачу 5а.

5а. Один критерий Дата (Выбрать События, которые Закончились/ не начались/ происходят на заданную дату)

Пусть имеется перечень событий и даты их начала и завершения.

Пользователю требуется найти и вывести в отдельную таблицу события, которые либо уже закончились на заданную дату, либо еще длятся, либо еще не начались.

Т.е. нам потребуется формула, обрабатывающая 3 вышеуказанные ситуации. Можно использовать нижеуказанную формулу, которую нужно ввести в строке для каждого события

=ВЫБОР($C$6;$B$7>C15;И($B$7>=B15;$B$7<=C15);$B$7<B15)

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

Для вывода Событий в соседний диапазон используйте формулу:

=ИНДЕКС(A$15:A$21;НАИМЕНЬШИЙ(ЕСЛИ($D$15:$D$21;СТРОКА(A$15:A$21);"");СТРОКА()-СТРОКА($B$14))-СТРОКА($B$14))

Формула делает следующее:

- если Событие удовлетворяет условию, то формула запоминает номер строки этого События: ЕСЛИ($D$15:$D$21;СТРОКА(A$15:A$21);"")

- Функция НАИМЕНЬШИЙ() сортирует полученный массив номеров строк: первыми идут номера строк Событий, которые удовлетворяют критерию;

- Функция ИНДЕКС() выводит названия Событий из указанный строк.

6. Два критерия: Дата и Текст (Выбрать Товары определенного вида, у которых Дата поставки не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист 2 критерия - Дата-Текст).

В отличие от Задачи 5 будем отбирать строки только того Товара, который указан в критерии. Список дат должен быть отсортирован (для каждого из товаров), по товару сортировка не требуется.

Для отбора строк, дата которых не позже (включая саму дату), используется формула массива:

=ИНДЕКС(A13:A21;
НАИМЕНЬШИЙ(ЕСЛИ(($E$7=$A$13:$A$21)*($E$8>=$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);"");СТРОКА($B$13:$B$21)-СТРОКА($B$12))
-СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

Примечание. Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список.

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий - Текст).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск.

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия - текст (И)).

Для отбора строк используется формула массива:

=ИНДЕКС($A$11:$A$19;
НАИМЕНЬШИЙ(ЕСЛИ(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19)))))

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел {1:2:3:4:5:6:7:8:9}, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия - текст (ИЛИ)).

В отличие от Задачи 7 отберем строки с товарами 2-х видов (Условие ИЛИ).

Для отбора строк используется формула массива:

=ИНДЕКС($A$11:$A$19;НАИМЕНЬШИЙ(ЕСЛИ(($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19)))))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только определенных видов. Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

10. Отбор значений с учетом повторов

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

Наиболее популярные статьи из этого раздела:

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL

Отбор повторяющихся значений в MS EXCEL

Отбор уникальных значений из двух диапазонов в MS EXCEL

Отбор уникальных СТРОК с помощью Расширенного фильтра в MS EXCEL

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (8 оценок)

Комментарии

fr.holle

А где файл с примером?

Creator

Внизу статьи.

Константин (не проверено)

Спасибо огромное! Один вопрос - а как вы #ЧИСЛО! в пустых ячейках белым сделали?

Creator
ritaburina

Спасибо за статью! Подскажите, а как упростится формула при наличии одного критерия? И как нужно модифицировать формулу CУММ, что бы она считала результат таких динамических столбцов, игнорируя строки с ошибками?

Creator

В файл примера добавил 2 листа для решения Вашей задачи.

Также посмотрите статьи в разделе Вывод отобранных значений в отдельный диапазон, в том числе http://excel2.ru/articles/poisk-chislovyh-znacheniy-i-vyvod-sootvetstvuy...

Для обработки ошибок используйте функцию ЕСЛИОШИБКА() (решение есть в файле примера), если у Вас EXCEL 2003, то прочитайте эту статью Функция ЕСЛИОШИБКА() в MS EXCEL.

Я упростил формулы массива в файле примера, так что использовать лучше новые (в статье также заменил).

Vitaliymedvid

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

Creator

Решение может быть таким. У каждой квартиры есть набор параметров (этаж, кол-во комнат, площадь, район и пр.), каждый клиент имеет свои предпочтения по всем этим параметрам (задается либо одним значением для каждого параметра, либо диапазоном: этаж выше 3, площадь от 50 до 70 и пр.) Таким образом, можно для 1 выбранного клиента для всех квартир в базе рассчитать некий интегральный показатель (как это сделать решать Вам). Далее выбирая нужного клиента выводится перечень квартир наиболее с подходящими параметрами (т.е. у которых интегральный показатель не меньше чем заданный). Также можно сделать аналогичный запрос со стороны квартиры: какие клиенты запрашивают квартиры с такими или похожими параметрами?

Можете посмотреть статьи из этого раздела http://excel2.ru/gruppy-statey/vyvod-otobrannyh-znacheniy-v-otdelnyy-dia...

Vitaliymedvid

Большое спасибо, это кажется, то, что надо, но придется над ним немножко поколдовать..
Так, как это нужно попробовать запустить в Google таблицах, интересно на сколько оно там работоспособно.

Дмитрий24

Спасибо за статью! Понравился пример 6. Два критерия - текст и дата, "не позже" заданной. Подскажите, пожалуйста, как можно задать условие текст и дата "не раньше" заданной?

Creator

Первый знак > замените на <. Не забудьте ввести формулу как формулу массива нажав ctrl+ shift + enter

alexs37

Доброе время суток, для меня просто открытие. аналогичное решал при помощи VBA, который управлял автофильтром, медленно но автоматически. а вот понять формулы массива не как не получается.
Подскажите, можно как то результаты (по части статьи №8, если конкретнее колонка Е) вывести в одну ячейку в виде строки через разделитель, или на крайней случай, в строку, а не колонку потом использую сцепить, структура такая, что построчно идет обработка по выборке и надо вывести последовательно значения по условием.

Alex517

Всем привет. В примере 1 не понял зачем отнимаем вот это в конце от наименьшего: СТРОКА($B$10) ?

Creator

Это для функции ИНДЕКС(). Функция НАИМЕНЬШИЙ() возвращает номер нужной нам строки (т.е. смещение относительно первой строки листа), а для функции ИНДЕКС() требуется указать смещение в диапазоне  (т.е. смещение относительно первой строки диапазона Цены или Товары). Короче, все сложно )))

Владислав

Добрый день.
В примере 7.
Никак не могу разораться с концовкой формулы.=ИНДЕКС($A$11:$A$19;НАИМЕНЬШИЙ(ЕСЛИ($E$6=$A$11:$A$19;СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19)))))}

вот этот кусок - ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19))

при изменении входных данных, выдает правильные значение, но там, где должы остаться пустые строки выдает ошибки: #ССЫЛКА!

Creator

В файле примера также выдается ошибка ССЫЛКА, но она скрыта Условным форматированием (выделите пустые ячейки и ошибка станет видна). Можно обработать эту ошибку с помощью функции ЕСЛИОШИБКА().

Владислав

Спасибо! Пока заработало =)

Владислав

добрый день еще раз. оказывается, в 2003 excel не работает. какие есть пути исправления?

Creator

Формулы должны работать в MS EXCEL 2003

Яндекс.Метрика