Выборка данных в Microsoft Excel

Выборка данных в Microsoft Excel
На чтение
36 мин.
Просмотров
48
Дата обновления
25.10.2024

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Самый простой способ сделать выборку — воспользоваться расширенным автофильтром. Давайте посмотрим, как это сделать на конкретном примере.

 

Включение фильтра через вкладку Данные в Microsoft Excel

 

  • После этого действия в шапке таблицы появляются значки начала фильтрации в виде маленьких перевернутых треугольников на правой границе ячеек. Мы нажимаем на этот значок в заголовке столбца, для которого мы хотим сделать выбор. В открывшемся меню перейдите в пункт «Текстовые фильтры». Далее выбираем пункт «Пользовательский фильтр…».

 

Переключиться на пользовательский фильтр в Microsoft Excel

 

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

    В качестве примера поставим условие выбирать только ценные бумаги, по которым сумма дохода превышает 10 000 руб. Установите переключатель в положение «Еще». В правом поле введите значение «10000». Для выполнения действия нажмите кнопку «ОК».

 

Пользовательский фильтр в Microsoft Excel

 

  • Как видите, после фильтрации остались только строки, в которых сумма дохода превышает 10 000 рублей.

 

Фильтрация результатов в Microsoft Excel

 

  • Но в тот же столбец мы можем добавить второе условие. Для этого возвращаемся в окно пользовательского фильтра. Как видите, внизу есть еще один переключатель условий и соответствующее поле ввода. Теперь установим верхнюю границу отбора в 15 000 руб. Для этого установите переключатель в положение «Минус» и введите значение «15000» в поле справа».

    Кроме того, есть еще переключатель условий. Имеет два положения «И» и «ИЛИ». По умолчанию он установлен в первую позицию. Это означает, что в выборке останутся только строки, удовлетворяющие обоим ограничениям. Если он установлен в положение «О», то будут значения, соответствующие любому из двух условий. В нашем случае необходимо установить переключатель в положение «Y», то есть оставить эту настройку по умолчанию. После ввода всех значений нажмите кнопку ОК».

 

Параметр верхней границы в пользовательском фильтре в Microsoft Excel

 

  • Теперь в таблице есть только строки, в которых сумма дохода не меньше 10 000 рублей, но не превышает 15 000 рублей.

 

Результаты фильтрации по нижней и верхней границам в Microsoft Excel

 

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

 

Переключиться на фильтрацию даты в Microsoft Excel

 

  • Снова откроется окно Пользовательский автофильтр. Сделаем выборку результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условия, как мы видим, вариантов даже больше, чем для числового формата. Выберите пункт «После или равно». В поле справа установите значение «05.04.2016». На нижнем блоке установите переключатель в положение «До или равно». В правом поле введите значение «05.06.2016». Оставляем переключатель совместимости условий в положении по умолчанию: «Y». Чтобы применить фильтрацию в действии, нажмите кнопку «ОК».

 

Пользовательский фильтр для формата даты в Microsoft Excel

 

  • Как видите, наш список еще больше сократился. Теперь остались только строки, в которых сумма дохода варьируется от 10 000 до 15 000 рублей за период с 04.05 по 06.05.2016 включительно.

 

Фильтрация результатов по сумме и дате в Microsoft Excel

 

  • Мы можем сбросить фильтрацию по одному из столбцов. Сделаем это для значений дохода. Щелкните значок автофильтра в соответствующем столбце. В выпадающем списке нажмите на пункт «Удалить фильтр».

 

Удалить фильтр из одного из столбцов в Microsoft Excel

 

  • Как видите, после этих действий отбор по сумме дохода будет отключен, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).

 

Ограничения только по дате в Microsoft Excel

 

  • В этой таблице есть еще один столбец: «Имя». Содержит данные в текстовом формате. Давайте посмотрим, как сформировать выборку, отфильтровав эти значения.

    Щелкните значок фильтра рядом с именем столбца. Проходим последовательно названия списка «Текстовые фильтры» и «Пользовательский фильтр…».

 

Переключиться на фильтрацию текста в Microsoft Excel

 

  • Снова откроется окно Пользовательский автофильтр. Сделаем выборку по названиям «Картошка» и «Мясо». В первом блоке установите переключатель условий в положение «То же самое». В поле справа введите слово «Картошка». Также ставим переключатель нижнего блока в положение «То же самое». В противоположном поле делаем запись: «Мясо». А затем мы делаем то, чего раньше не делали: ставим переключатель совместимости условий в положение «О». Строка, содержащая любое из указанных условий, теперь будет отображаться на экране. Нажимаем на кнопку «Принять».

 

Пользовательский фильтр для форматирования текста в Microsoft Excel

 

  • Как видите, в новом образце есть ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). Ограничений по сумме дохода нет.

 

Ограничения даты и имени в Microsoft Excel

 

  • Вы можете полностью удалить фильтр так же, как он использовался для его установки. Неважно, какой метод был использован. Чтобы сбросить фильтрацию, находясь во вкладке «Данные», нажмите кнопку «Фильтр», которая находится в группе «Сортировка и фильтрация».

     

    Очистить фильтр в Microsoft Excel

     

    Второй вариант — перейти на вкладку «Главная». Там мы нажимаем на ленте кнопку «Сортировка и фильтрация» в блоке «Редактировать». В активированном списке нажмите кнопку «Фильтр».

 

Очистите фильтр на вкладке «Главная» в Microsoft Excel

 

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

 

Сброс фильтра в Microsoft Excel

 

Урок: Функция автофильтра в Excel

Способ 2: применение формулы массива

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

 

Создайте пустую таблицу в Microsoft Excel

 

 

  • Выберите все пустые ячейки в первом столбце новой таблицы. Поместите курсор в строку формул. Здесь будет введена формула, выбранная вами на основе указанных критериев. Мы выберем строки, в которых сумма дохода превышает 15 000 рублей. В нашем конкретном примере введенная формула будет выглядеть так:

    =ИНДЕКС(A2:A29,НИЗКИЙ(ЕСЛИ(15000

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

 

Ввод формулы в Microsoft Excel

 

  • Так как это формула массива, то для применения ее в действии нужно нажать не кнопку Enter, а комбинацию клавиш Ctrl+Shift+Enter. Давай сделаем это.

 

Формула массива, введенная в столбец имени в Microsoft Excel

 

  • Выделив второй столбец с датами и установив курсор в строку формул, введите следующее выражение:

    =ИНДЕКС(B2:B29,НИЗКИЙ(ЕСЛИ(15000

    Нажмите комбинацию клавиш Ctrl+Shift+Enter.

 

Формула массива, введенная в столбец даты в Microsoft Excel

 

  • Аналогично в графу с доходом вводим следующую формулу:

    =ИНДЕКС(C2:C29;НИЗКИЙ(ЕСЛИ(15000

    Снова набираем комбинацию клавиш Ctrl+Shift+Enter.

    Во всех трех случаях меняется только первое значение координаты, а в остальном формулы полностью идентичны.

 

Формула массива, введенная в столбец доходов в Microsoft Excel

 

  • Как видите, таблица заполнена данными, но ее внешний вид не очень привлекателен, кроме того, плохо заполнены значения даты. Эти недостатки должны быть исправлены. Неверная дата связана с тем, что формат ячеек соответствующего столбца общий и нам нужно настроить формат даты. Выделите весь столбец, включая ячейки с ошибками, и щелкните правой кнопкой мыши выделение. В появившемся списке переходим к пункту «Формат ячейки…».

 

Изменение форматирования ячеек в Microsoft Excel

 

  • В открывшемся окне формата откройте вкладку «Число». В блоке «Числовые форматы» выберите значение «Дата». В правой части окна вы можете выбрать желаемый тип отображения даты. После настройки параметров нажмите кнопку «ОК».

 

Установка формата даты в Microsoft Excel

 

  • Теперь дата отображается правильно. Но, как видите, весь низ таблицы заполнен ячейками, содержащими неверное значение «#ЧИСЛО!». По сути, это те ячейки, для которых не хватило выборочных данных. Было бы привлекательнее, если бы они отображались совершенно пустыми. Для этого воспользуемся условным форматированием. Выберите все ячейки в таблице, кроме заголовка. На вкладке «Главная» нажмите кнопку «Условное форматирование» на панели инструментов «Стили». В появившемся списке выберите пункт «Создать правило…».

 

Я собираюсь создать правило в Microsoft Excel

 

  • В открывшемся окне выберите тип правила «Форматировать только те ячейки, которые содержат». В первом поле под надписью «Форматировать только те ячейки, для которых выполняется следующее условие» выберите позицию «Ошибки». Затем нажмите кнопку «Формат…».

 

Переключиться на выбор формата в Microsoft Excel

 

  • В открывшемся окне форматирования перейдите на вкладку «Шрифт» и в соответствующем поле выберите белый цвет. После этих действий нажмите кнопку «ОК».

 

Форматирование ячеек в Microsoft Excel

 

  • Нажмите на кнопку с точно таким же названием после возвращения в окно для создания условий.

 

Создайте условие формата в Microsoft Excel

 

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

 

Выбор сделан в Microsoft Excel

 

Урок: Условное форматирование в Excel

Способ 3: выборка по нескольким условиям с помощью формулы

Точно так же, как с помощью фильтра, с помощью формулы вы можете выбрать несколько условий. Для примера возьмем ту же исходную таблицу, а также пустую таблицу, где будут отображаться результаты, с уже сделанным числовым и условным форматированием. Установим в качестве первого ограничения нижнюю границу выбора дохода в 15 000 рублей, а в качестве второго условия – верхнюю границу в 20 000 рублей.

 

Условия в Microsoft Excel

 

  • Как и в предыдущем способе, выбираем поочередно пустые столбцы новой таблицы и вводим в них соответствующие три формулы. В первом столбце вводим следующее выражение:

    =ИНДЕКС(A2:A29,LEW(ЕСЛИ(($D$2=C2:C29),СТРОКА(C2:C29),»»),СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($ 1 доллар))

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

    При каждом входе не забывайте набирать комбинацию клавиш Ctrl+Shift+Enter.

 

Результат выбора по различным условиям в Microsoft Excel

 

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

 

Изменить результаты выбора в Microsoft Excel

 

Способ 4: случайная выборка

В Excel с помощью специальной формулы СЛУЧАЙ также можно применить случайный выбор. Это нужно делать в некоторых случаях при работе с большим объемом данных, когда нужно представить общую картину без комплексного анализа всех данных в матрице.

 

Случайное число в Microsoft Excel

 

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

 

Ручка заполнения в Microsoft Excel

 

  • Теперь у нас есть ряд ячеек, заполненных случайными числами. Но он содержит формулу RAND. Нам нужно работать с чистыми значениями. Для этого скопируйте в пустой столбец справа. Выберите диапазон ячеек со случайными числами. На вкладке Главная щелкните значок Копировать на ленте.

 

Скопировать в MicrosoftExcel

 

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

 

Вставить в Microsoft Excel

 

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

 

Переключиться на пользовательскую сортировку в Microsoft Excel

 

  • Активируется окно конфигурации классификации. Обязательно поставьте галочку напротив параметра «Мои данные содержат заголовки», если заголовок есть, а галочки нет. В поле «Сортировать по» укажите название столбца, содержащего скопированные значения случайных чисел. В поле «Сортировка» оставьте настройки по умолчанию. В поле «Порядок» можно выбрать «По возрастанию» или «По убыванию». Для случайной выборки это не имеет значения. После внесения настроек нажмите кнопку «ОК».

 

Настроить сортировку в Microsoft Excel

 

  • После этого все значения в таблице располагаются в порядке возрастания или убывания случайных чисел. Вы можете взять любое количество первых строк таблицы (5, 10, 12, 15 и т д.) и считать их результатом случайной выборки.

 

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

 

Урок: Сортировка и фильтрация данных в Excel

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

 

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий