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

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

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

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

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

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

1. Выделяем область на листе, среди данных которую хотим выделить. На вкладке «Главная» нажмите кнопку «Сортировка и фильтр». Он находится в блоке настроек «Редактирование». В открывшемся после этого списке нажмите на кнопку «Фильтр».

    

   

    

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

 



 

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

 



 

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

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

 



 

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

 



 

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

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

 



 

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

 



 

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

 



 

8. Снова откроется окно Пользовательский автофильтр. Выполним выборку результатов в таблице с 4 по 6 мая 2016 года. В селекторе состояния, как мы видим, вариантов даже больше, чем для числового формата. Выберите пункт «После или равно». В поле справа установите значение «04.05.2016». В нижнем блоке установите переключатель в положение «До или равно». В правом поле введите значение «06.05.2016». Оставляем переключатель состояния совместимости в положении по умолчанию — «И». Чтобы использовать фильтрацию в действии, нажмите на кнопку «ОК».

 



 

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

 



 

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

 



 

 



 

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

 



 

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

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

 



 

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

 



 

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

 



 

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

     

    

     

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

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

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

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

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

1. На этом же листе создаем пустую таблицу с теми же именами столбцов в шапке, что и в исходнике.

 



 

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

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

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

 



 

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

 



 

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

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

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

 



 

5. Точно так же введите следующую формулу в столбце доходов:

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

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

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

 



 

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

 



 

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

 



 

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

 



 

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

 



 

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

 



 

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

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

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

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

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

1. Вводим граничные условия для выборки в отдельный столбец.

 



 

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

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

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

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

 



 

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

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

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

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

   =СЛУЧАЙ()

   Эта функция отображает случайное число на экране. Чтобы активировать его, нажмите кнопку ENTER.

 



 

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

 



 

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

 



 

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

 



 

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

 



 

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

 



 

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

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

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

Еще статьи по данной теме: