Выборка данных в 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 года включительно. Переключатель выбора условий, как видите, имеет даже больше возможностей, чем числовой формат. Выбираем позицию «После или равно». В поле справа установите значение «05.04.2016». В нижнем блоке установите переключатель в положение «До или Равно». В поле справа введите значение «06.05.2016». Переключатель совместимости условий оставьте в положении по умолчанию — «И». Чтобы применить фильтр в действии, нажмите кнопку «ОК».
9. Как видите, наш список сузился еще больше. Теперь он содержит только строки, в которых сумма выручки варьируется от 10 000 до 15 000 рублей за период с 04.05.2016 по 06.05.2016 включительно.
10. Мы можем сбросить фильтрацию по одному из столбцов. Мы делаем это для значений дохода. Щелкните значок автоматического фильтра в соответствующем столбце. В раскрывающемся списке щелкните запись «Удалить фильтр».
11. Как видите, после этих действий выборка по размеру выручки будет отключена и останется только выборка по датам (с 05.04.2016 по 06.05.2016).
12. В этой таблице есть еще один столбец: «Имя». Содержит данные в текстовом формате. Давайте посмотрим, как сформировать выборку путем фильтрации на основе этих значений.

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

13. Снова откроется окно настраиваемого автофильтра. Сделаем подборку с названиями «Картофель» и «Мясо». В первом блоке установите переключатель условий в положение «Равно». В поле справа введите слово «Картофель». Так же переводим переключатель нижнего блока в положение «Равно». В поле перед ним вставляем запись: «Мясо». А затем мы делаем то, чего никогда раньше не делали: устанавливаем переключатель совместимости условий в положение «ИЛИ». Теперь на экране появится строка, содержащая любое из указанных условий. Щелкните кнопку «ОК».
14. Как видите, в новом образце есть ограничения по дате (с 04.05.2016 по 06.05.2016) и по названию (картошка и мясо). Нет ограничений по размеру дохода.
15. Вы можете полностью удалить фильтр, используя те же методы, что и при его установке. И неважно, какой метод был использован. Чтобы сбросить фильтр, на вкладке «Данные» нажмите кнопку «Фильтр», которая находится в группе «Сортировка и фильтр».

    

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

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

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

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

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

   = ИНДЕКС (LA2: 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. После возврата в окно создания условий нажмите кнопку с таким же названием.

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

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

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

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

   = ИНДЕКС (A2: A29; МАЛЫЙ (ЕСЛИ (($ D $ 2 = C2: C29); СТРОКА (C2: C29); «»), СТРОКА (C2: C29) -ЛИНИЯ ($ C $ 1)) — СТРОКА ($ C $ 1))

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

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

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

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

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

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

   = СЛУЧАЙНЫЙ()

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

2. Чтобы создать целый столбец случайных чисел, поместите курсор в нижний правый угол ячейки, которая уже содержит формулу. Появится индикатор заполнения. Растяните его, удерживая левую кнопку мыши параллельно таблице данных до конца.
3. Теперь у нас есть ряд ячеек, заполненных случайными числами. Но он содержит формулу RAND. Мы должны работать с чистыми ценностями. Для этого скопируйте в пустой столбец справа. Выделите диапазон ячеек со случайными числами. На вкладке «Главная» щелкните значок «Копировать» на ленте.
4. Выберите пустой столбец и щелкните правой кнопкой мыши, чтобы открыть контекстное меню. В группе инструментов «Параметры вставки» выберите пункт «Значения», показанный в виде пиктограммы с цифрами.
5. Далее, находясь на вкладке «Главная», щелкните уже знакомый значок «Сортировка и фильтр». В раскрывающемся списке остановите выбор на пункте «Пользовательская сортировка».
6. Окно настроек сортировки активировано. Обязательно установите флажок рядом с параметром «Мои данные содержат заголовки», если заголовок есть, но галочки нет. В поле «Сортировать по» укажите имя столбца, который содержит скопированные значения случайных чисел. В поле Сортировка оставьте настройки по умолчанию. В поле «Порядок» вы можете выбрать По возрастанию или По убыванию. Для случайной выборки это не имеет значения. После выполнения настроек нажмите кнопку «ОК».
7. Затем все значения в таблице расположены в порядке возрастания или убывания случайных чисел. Вы можете взять любое количество первых строк таблицы (5, 10, 12, 15 и т.д.), И их можно будет рассматривать как результат случайной выборки.

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