В экселе как сделать выборку


В экселе как сделать выборку

В экселе как сделать выборку

В экселе как сделать выборку



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь выделим диапазон D11:D19 и в введем :

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание. Случай, когда список несортирован, рассмотрен в статье .

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

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

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Источник: http://excel2.ru/articles/zapros-na-vyborku-dannyh-formuly-v-ms-excel


В экселе как сделать выборку

В экселе как сделать выборку

В экселе как сделать выборку

В экселе как сделать выборку

В экселе как сделать выборку

В экселе как сделать выборку

В экселе как сделать выборку

В экселе как сделать выборку