При работе с таблицами Excel очень часто приходится выбирать их по определенным критериям или нескольким условиям. В программе это можно сделать несколькими способами с помощью ряда инструментов. Давайте узнаем, как сделать выборку в Excel, используя различные параметры.
Выполнение выборки
Выборка данных заключается в процедуре выбора из общей матрицы тех результатов, которые удовлетворяют заданным условиям, с последующим их отображением на листе в отдельном списке или в исходном диапазоне.
Способ 1: применение расширенного автофильтра
Самый простой способ сделать выборку — воспользоваться расширенным автофильтром. Давайте посмотрим, как это сделать на конкретном примере.
- После этого действия в шапке таблицы появляются значки начала фильтрации в виде маленьких перевернутых треугольников на правой границе ячеек. Мы нажимаем на этот значок в заголовке столбца, для которого мы хотим сделать выбор. В открывшемся меню перейдите в пункт «Текстовые фильтры». Далее выбираем пункт «Пользовательский фильтр…».
- Окно пользовательского фильтра активировано. В нем можно задать ограничение, по которому будет производиться выборка. В раскрывающемся списке для столбца, содержащего ячейки числового формата, которые мы использовали в примере, вы можете выбрать один из пяти типов условий:
- равно;
- это не то же самое;
- плюс;
- больше или равно;
- меньше.
В качестве примера поставим условие выбирать только ценные бумаги, по которым сумма дохода превышает 10 000 руб. Установите переключатель в положение «Еще». В правом поле введите значение «10000». Для выполнения действия нажмите кнопку «ОК».
- Как видите, после фильтрации остались только строки, в которых сумма дохода превышает 10 000 рублей.
- Но в тот же столбец мы можем добавить второе условие. Для этого возвращаемся в окно пользовательского фильтра. Как видите, внизу есть еще один переключатель условий и соответствующее поле ввода. Теперь установим верхнюю границу отбора в 15 000 руб. Для этого установите переключатель в положение «Минус» и введите значение «15000» в поле справа».
Кроме того, есть еще переключатель условий. Имеет два положения «И» и «ИЛИ». По умолчанию он установлен в первую позицию. Это означает, что в выборке останутся только строки, удовлетворяющие обоим ограничениям. Если он установлен в положение «О», то будут значения, соответствующие любому из двух условий. В нашем случае необходимо установить переключатель в положение «Y», то есть оставить эту настройку по умолчанию. После ввода всех значений нажмите кнопку ОК».
- Теперь в таблице есть только строки, в которых сумма дохода не меньше 10 000 рублей, но не превышает 15 000 рублей.
- Точно так же вы можете установить фильтры для других столбцов. При этом можно сохранить и фильтрацию по предыдущим условиям, которые были указаны в столбцах. Итак, давайте посмотрим, как осуществляется выборка с помощью фильтра ячеек в формате даты. Щелкните значок фильтра в соответствующем столбце. Последовательно нажмите на пункты списка «Фильтровать по дате» и «Пользовательский фильтр».
- Снова откроется окно Пользовательский автофильтр. Сделаем выборку результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условия, как мы видим, вариантов даже больше, чем для числового формата. Выберите пункт «После или равно». В поле справа установите значение «05.04.2016». На нижнем блоке установите переключатель в положение «До или равно». В правом поле введите значение «05.06.2016». Оставляем переключатель совместимости условий в положении по умолчанию: «Y». Чтобы применить фильтрацию в действии, нажмите кнопку «ОК».
- Как видите, наш список еще больше сократился. Теперь остались только строки, в которых сумма дохода варьируется от 10 000 до 15 000 рублей за период с 04.05 по 06.05.2016 включительно.
- Мы можем сбросить фильтрацию по одному из столбцов. Сделаем это для значений дохода. Щелкните значок автофильтра в соответствующем столбце. В выпадающем списке нажмите на пункт «Удалить фильтр».
- Как видите, после этих действий отбор по сумме дохода будет отключен, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).
- В этой таблице есть еще один столбец: «Имя». Содержит данные в текстовом формате. Давайте посмотрим, как сформировать выборку, отфильтровав эти значения.
Щелкните значок фильтра рядом с именем столбца. Проходим последовательно названия списка «Текстовые фильтры» и «Пользовательский фильтр…».
- Снова откроется окно Пользовательский автофильтр. Сделаем выборку по названиям «Картошка» и «Мясо». В первом блоке установите переключатель условий в положение «То же самое». В поле справа введите слово «Картошка». Также ставим переключатель нижнего блока в положение «То же самое». В противоположном поле делаем запись: «Мясо». А затем мы делаем то, чего раньше не делали: ставим переключатель совместимости условий в положение «О». Строка, содержащая любое из указанных условий, теперь будет отображаться на экране. Нажимаем на кнопку «Принять».
- Как видите, в новом образце есть ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). Ограничений по сумме дохода нет.
- Вы можете полностью удалить фильтр так же, как он использовался для его установки. Неважно, какой метод был использован. Чтобы сбросить фильтрацию, находясь во вкладке «Данные», нажмите кнопку «Фильтр», которая находится в группе «Сортировка и фильтрация».
Второй вариант — перейти на вкладку «Главная». Там мы нажимаем на ленте кнопку «Сортировка и фильтрация» в блоке «Редактировать». В активированном списке нажмите кнопку «Фильтр».
Использование любого из двух вышеуказанных методов удалит фильтрацию и очистит результаты выбора. То есть таблица будет отображать весь массив имеющихся в ней данных.
Урок: Функция автофильтра в Excel
Способ 2: применение формулы массива
Вы также можете сделать выбор, используя формулу сложного массива. В отличие от предыдущей версии, этот метод позволяет вывести результат в отдельную таблицу.
- Выберите все пустые ячейки в первом столбце новой таблицы. Поместите курсор в строку формул. Здесь будет введена формула, выбранная вами на основе указанных критериев. Мы выберем строки, в которых сумма дохода превышает 15 000 рублей. В нашем конкретном примере введенная формула будет выглядеть так:
=ИНДЕКС(A2:A29,НИЗКИЙ(ЕСЛИ(15000
Естественно, в каждом случае адрес ячеек и диапазоны будут разные. В этом примере вы можете сравнить формулу с координатами на иллюстрации и адаптировать ее к вашим потребностям.
- Так как это формула массива, то для применения ее в действии нужно нажать не кнопку Enter, а комбинацию клавиш Ctrl+Shift+Enter. Давай сделаем это.
- Выделив второй столбец с датами и установив курсор в строку формул, введите следующее выражение:
=ИНДЕКС(B2:B29,НИЗКИЙ(ЕСЛИ(15000
Нажмите комбинацию клавиш Ctrl+Shift+Enter.
- Аналогично в графу с доходом вводим следующую формулу:
=ИНДЕКС(C2:C29;НИЗКИЙ(ЕСЛИ(15000
Снова набираем комбинацию клавиш Ctrl+Shift+Enter.
Во всех трех случаях меняется только первое значение координаты, а в остальном формулы полностью идентичны.
- Как видите, таблица заполнена данными, но ее внешний вид не очень привлекателен, кроме того, плохо заполнены значения даты. Эти недостатки должны быть исправлены. Неверная дата связана с тем, что формат ячеек соответствующего столбца общий и нам нужно настроить формат даты. Выделите весь столбец, включая ячейки с ошибками, и щелкните правой кнопкой мыши выделение. В появившемся списке переходим к пункту «Формат ячейки…».
- В открывшемся окне формата откройте вкладку «Число». В блоке «Числовые форматы» выберите значение «Дата». В правой части окна вы можете выбрать желаемый тип отображения даты. После настройки параметров нажмите кнопку «ОК».
- Теперь дата отображается правильно. Но, как видите, весь низ таблицы заполнен ячейками, содержащими неверное значение «#ЧИСЛО!». По сути, это те ячейки, для которых не хватило выборочных данных. Было бы привлекательнее, если бы они отображались совершенно пустыми. Для этого воспользуемся условным форматированием. Выберите все ячейки в таблице, кроме заголовка. На вкладке «Главная» нажмите кнопку «Условное форматирование» на панели инструментов «Стили». В появившемся списке выберите пункт «Создать правило…».
- В открывшемся окне выберите тип правила «Форматировать только те ячейки, которые содержат». В первом поле под надписью «Форматировать только те ячейки, для которых выполняется следующее условие» выберите позицию «Ошибки». Затем нажмите кнопку «Формат…».
- В открывшемся окне форматирования перейдите на вкладку «Шрифт» и в соответствующем поле выберите белый цвет. После этих действий нажмите кнопку «ОК».
- Нажмите на кнопку с точно таким же названием после возвращения в окно для создания условий.
Теперь у нас есть образец, подготовленный для указанного ограничения в отдельной таблице с правильным форматированием.
Урок: Условное форматирование в Excel
Способ 3: выборка по нескольким условиям с помощью формулы
Точно так же, как с помощью фильтра, с помощью формулы вы можете выбрать несколько условий. Для примера возьмем ту же исходную таблицу, а также пустую таблицу, где будут отображаться результаты, с уже сделанным числовым и условным форматированием. Установим в качестве первого ограничения нижнюю границу выбора дохода в 15 000 рублей, а в качестве второго условия – верхнюю границу в 20 000 рублей.
- Как и в предыдущем способе, выбираем поочередно пустые столбцы новой таблицы и вводим в них соответствующие три формулы. В первом столбце вводим следующее выражение:
=ИНДЕКС(A2:A29,LEW(ЕСЛИ(($D$2=C2:C29),СТРОКА(C2:C29),»»),СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($ 1 доллар))
В последующие столбцы вводим точно такие же формулы, только меняем координаты сразу после имени оператора ИНДЕКС на соответствующие нужные нам столбцы, по аналогии с предыдущим способом.
При каждом входе не забывайте набирать комбинацию клавиш Ctrl+Shift+Enter.
- Преимущество этого метода перед предыдущим в том, что если мы хотим изменить границы выборки, то нам не нужно будет менять формулу массива, что само по себе достаточно проблематично. Просто измените числа лимитов в колонке условий листа на те, которые нужны пользователю. Результаты выбора автоматически изменятся немедленно.
Способ 4: случайная выборка
В Excel с помощью специальной формулы СЛУЧАЙ также можно применить случайный выбор. Это нужно делать в некоторых случаях при работе с большим объемом данных, когда нужно представить общую картину без комплексного анализа всех данных в матрице.
- Чтобы создать целый столбец случайных чисел, поместите курсор в правый нижний угол ячейки, которая уже содержит формулу. Появится маркер заполнения. Растягиваем его вниз с нажатой левой кнопкой мыши параллельно таблице с данными до ее конца.
- Теперь у нас есть ряд ячеек, заполненных случайными числами. Но он содержит формулу RAND. Нам нужно работать с чистыми значениями. Для этого скопируйте в пустой столбец справа. Выберите диапазон ячеек со случайными числами. На вкладке Главная щелкните значок Копировать на ленте.
- Выберите пустой столбец и щелкните правой кнопкой мыши, чтобы открыть контекстное меню. В группе инструментов «Параметры вставки» выберите пункт «Значения», который отображается в виде значка с цифрами.
- После этого, находясь на вкладке «Главная», нажмите на уже знакомую иконку «Сортировка и фильтрация». В выпадающем списке остановите выбор на пункте «Выборочная сортировка».
- Активируется окно конфигурации классификации. Обязательно поставьте галочку напротив параметра «Мои данные содержат заголовки», если заголовок есть, а галочки нет. В поле «Сортировать по» укажите название столбца, содержащего скопированные значения случайных чисел. В поле «Сортировка» оставьте настройки по умолчанию. В поле «Порядок» можно выбрать «По возрастанию» или «По убыванию». Для случайной выборки это не имеет значения. После внесения настроек нажмите кнопку «ОК».
- После этого все значения в таблице располагаются в порядке возрастания или убывания случайных чисел. Вы можете взять любое количество первых строк таблицы (5, 10, 12, 15 и т д.) и считать их результатом случайной выборки.
Урок: Сортировка и фильтрация данных в Excel
Как видите, выборку в таблице Excel можно производить как с помощью автофильтра, так и с помощью специальных формул. В первом случае результат будет отображаться в исходной таблице, а во втором — в отдельной области. Можно сделать выборку, как по одному условию, так и по нескольким. Кроме того, вы можете выполнить случайную выборку с помощью функции RANDOM.