Фильтрация данных в Excel

image Как сделать формулу в Excel Программа Microsoft Excel Office имеет в себе множество удобных функций, которые помогают не заниматься долгими расчётами перед внесением данных в таблицы или диаграммы. Для подсчёта некоторых значений, например сложения, вычитания, отношения и других, можно использовать изначально заданные формулы. Их вы можете внести самостоятельно либо выбрать из списка уже существующих. Научитесь вставлять формулу поэтапно в этой […] image Как сделать таблицу в excel На самом деле весь программный пакет Excel – одна большая таблица, с бесчисленным количеством столбцов и ячеек. Однако это не значит, что пользователь не может создать свои собственные. О них, а также о полезных хитростях при работе с Microsoft Excel мы и поговорим ниже. 1 Как пользоваться таблицами Excel? Итак, чтобы превратить открытый лист программы […] Как сделать диаграмму в Excel Табличный процессор Майкрософт Эксель является универсальным средством для работы с таблицами. Сводные отчеты, прайсы, каталоги и многое другое с использованием функций для решения повседневных задач – все это Excel. Сухие цифры на экране – только значения и показатели, для наглядного отображения используются диаграммы. 1 Запустите нужный файл, книгу или лист Excel. Или создайте новый документ […] Как удалить строку в Excel MS Excel используется для работы с электронными таблицами. Этот многофункциональный инструмент подходит не только для ввода данных, но и для решения сложных задач с применением формул – математических, статистических и логических. В некоторых из них наличие пустых строк недопустимо, они могут влиять на результат расчетов. Удалить строку, пустую и не только, можно несколькими способами. 1 […] Как сделать график в Ворде Всевозможные иллюстрации, графики, диаграммы, во много раз увеличивают ценность предоставляемой информации, улучшают ее восприятие и запоминание. Поэтому крайне важно уметь создавать их в столь востребованном и распространенном программном пакете, как Microsoft Word. Ниже мы подробно расскажем, как создать график различными способами и гибко настраивать его при необходимости. 1 Как сделать график в Ворде “внедрением” Чтобы […] Как в Excel закрепить строку При работе с большими таблицами в Microsoft Excel возникает необходимость закрепления на экране той или иной строки таким образом, чтобы она не терялась из виду при просмотре содержимого любого участка таблицы. Эта функция особо удобна, например, при работе с различными каталогами, когда необходимо закрепить строку с наименованиями столбцов. Рассмотрим, как это сделать в различных версиях Microsoft Excel. 1 […] Как в Ворде сделать диаграмму Данная статья, при рассмотрении особенностей построения диаграммы, будет опираться на интерфейс наиболее популярной и надежной версии офисного пакета, а именно Microsoft Office Word 2007. В более новых выпусках “офиса” принцип остался тем же, возможны некоторые изменения в пунктах меню и появление новых возможностей для пользователя. 1 Как сделать диаграмму в Ворде на основе полученных данных? […] Как включить макросы в Excel Макросы в документах Microsoft Office являются довольно удобным средством редактирования и просмотра страницы, однако некоторые из них могут серьезно повредить вашей системе. Подумайте хорошо, прежде чем включать макросы в любой из программ среды Microsoft Office. Некоторые книги Microsoft Office Excel содержат ряд макросов, чтобы быстрее упорядочить и систематизировать информацию внутри них. Сами макросы служат для […]

Охотничий и рыболовный магазин BALLISTICA.SU

Рабочий лист Microsoft Office Excel может вмещать большой объем данных. Иногда нужно сделать выборку необходимой информации. Можно воспользоваться сортировкой, если известны условия, или встроенным фильтром. Однако оба метода имеют ограниченный функционал и могут не удовлетворить всем условиям пользователя. Сегодня рассмотрим, как работает расширенный фильтр в excel.

Использование

Сразу отметим, что процесс работы с этим инструментом для версий редактора 2007, 2010 и 2016 годов идентичен. Расширенный фильтр – это улучшенная версия стандартной функции, благодаря которой можно отбирать информацию по нескольким пользовательским условиям.

Рассмотрим пример: есть отчет о работе сети магазинов по продаже мебели, необходимо отобрать данные о продаже Магазина №1.

Последовательность действий следующая:

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

Важно! Расстояние между основной и дополнительной таблицей должно быть хотя бы одна пустая строка.

  1. Теперь необходимо разобраться, как задать условие. По задаче нас интересует Магазин №1. После скопированного заголовка, в графе Место продажи ставите нужное значение.
  1. Ставите курсор на любую ячейку, переходите во вкладку Данные на Панели управления и ищете кнопку Дополнительно в блоке Сортировка и фильтр.
  1. Заполняете диалоговое окно. Здесь исходный диапазон это первоначальная таблица, а диапазон условий – новая таблицы с параметрами фильтрации.

Важно! Блоки выделяете совместно с заголовками таблиц.

  1. Подтверждаете действие нажатием клавиши ОК и видите результат.

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

Добавим к исходным условиям выборку по выручке и продажи Магазина №2. Результат будет следующим:

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

Дополнительные возможности

Иногда результаты отбора нужно сопоставить с первоначальными данными. Инструмент позволяет выводить данные в отдельном месте. Для этого в диалоговом окне ставите галочку напротив Скопировать результат в другое место и обозначаете зону в дополнительной строке.

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

Более опытные пользователи Microsoft Office Excel могут создать свой собственный макрос на языке программирования Visual Basic. Однако стоит помнить о том, что сначала нужно продумать логику работы программы и потом реализовать ее в виде программного кода VBA. Поэтому такой метод рекомендуем только для более продвинутых пользователей.

Чтобы удалить результаты фильтрации, достаточно нажать отдельную кнопку Очистить в блоке Фильтр во вкладке Данные.

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

Вы здесь: Главная > Табличный процессор MS EXCEL > Практическое задание №5. Работа с данными в Microsoft Excel: установка фильтров и настройка параметров отбора по значению, по условию, по формату

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

Задание: Используя табличный процессор Ms Excel выполните ниже приведённую последовательность действий (сценарий) и выполните Контрольное задание к практической работе №5.

Отбор данных

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

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

Строки, отобранные при фильтрации, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать.

Установка фильтра

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

  1. Создайте на Рабочем столе компьютера файл отчёта по практической работе: ОтчётФильтрФамилия_студента.docx
  2. Откройте файл DataFiltr.xlsx   DataFiltr.xlsx (41,5 KiB, 2 052 hits)
  3. Сохраните файл DataFiltr.xlsx на Рабочем столе компьютера под именем ФильтрФамилия_студента.xlsx
  4. Откройте файл ФильтрФамилия_студента.xlsx
  5. Выделите одну любую ячейку в диапазоне, для которого устанавливаются фильтры.
  6. Установите фильтр: нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите команду Фильтр или нажмите кнопку Фильтр группы Сортировка и фильтр вкладки Данные.
  7. Сделайте первый скриншот и отправьте его в файл ОтчётФильтрФамилия_студента.docx

После установки фильтров в названиях столбцов таблицы появятся значки раскрывающих списков (стрелки в ячейках A1:G1 ).

Работа с фильтром

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

  1. Произведите отбор данных по столбцу А (Товар), выберите Апельсины и столбцу D (Регион продаж), выберите регион Москва. Для этого:
  2. Щёлкаем по списку (стрелке ) Товар, снимаем (убираем) флаг с пункта Выделить всё (щелчком по квадратику или пункту, объекту) и устанавливаем его на пункте Апельсины, нажимаем ОК.
  3. Повторяем действия пункта 2 для столбца D (Регион продаж), выбираем регион Москва.

Результат будет выглядеть так:

Отбор по формату

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

  1. Приведите таблицу в исходное состояние для большей наглядности последующих действий.
  2. Для этого: щёлкните по списку Товар, поставьте флаг на пункт Выделить всё, ОК, щёлкните по списку Регион продаж, поставьте флаг на пункт Выделить всё, ОК.
  3. Щелкните по значку раскрывающегося списка столбца Группа.
  4. Выберите команду Фильтр по цвету, а затем в подчиненном меню выберите цвет ячейки (зелёный). Нажмите кнопку ОК.

Отбор по условию

Можно производить выборку не только по конкретному значению, но и по условию.

Условие можно применять для числовых значений.

  1. Щелкните по значку раскрывающегося списка столбца Продажи .
  2. Выберите команду Числовые фильтры, а затем в подчиненном меню выберите применяемое условие (ниже по тексту).

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

Результат выглядит следующим образом:

  1. Щелкните по значку раскрывающегося списка столбца Дата поставки
  2. Выберите команду Фильтры по дате, а затем в подчиненном меню выберите применяемое условие (ниже по тексту).

3. После выбора условий: До, После или Между появляется окно  Пользовательский автофильтр, где можно  установить значение условия. Значение условия можно выбрать из списка или ввести с клавиатуры. Можно также щелкнуть по кнопке Выбор даты и выбрать значение даты из календаря.

4. Выберем После. Появится окно Пользовательский автофильтр, введём условие отбора: После 30.09.2011.

Условия можно использовать при отборе и для текстовых значений

  1. Изменим таблицу для большей наглядности последующих действий. Для этого:
  2. Снимите фильтр по цвету: щёлкните по списку Группа, выберите пункт Снять фильтр с «Группа».
  3. Отсортируйте столбец Группа по цвету в порядке: зелёный, красный, жёлтый, коричневый. Если забыли последовательность действий при сортировке обратитесь к Практическому заданию №4
  4. Произведите отбор по столбцу Регион продаж.
  5. Для этого : щелкните по значку раскрывающегося списка столбца Регион продаж.
  6. Выберите команду Текстовые фильтры, а затем в подчиненном меню выберите применяемое условие (ниже по тексту).

3. При использовании условий: равно, не равно, содержит, не содержит, начинается с, не начинается с, заканчивается на, не заканчивается на появляется окно Пользовательский автофильтр, где устанавливается значение. Значение условия обычно вводят с клавиатуры.

4. Выберем Текстовый фильтр, начинается с, Пользовательский автофильтр, где вводим Ба для отбора имени региона, начинающегося с Ба.

Результат установки текстового фильтра:

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

Отбор наибольших и наименьших значений

Для числовых значений можно отобрать строки по наибольшим или наименьшим значениям в каком-либо столбце.

  1. Для более наглядной иллюстрации отбора снимите фильтры со столбцов Дата поставки, Регион продажи, Продажи, в столбце Группа щёлкните по списку, выберите Сортировка по цвету, Пользовательская сортировка и нажмите ОК.
  2. Щелкните по значку раскрывающегося списка столбца Продажи.
  3. Выберите команду Числовые фильтры, а затем в подчиненном меню выберите команду Первые 10.

В окне Наложение условия по списку установите количество (или процент от общего числа) отбираемых элементов. Выберите принцип отбора (наибольшие или наименьшие значения) и режим отбора: отображение строк с наибольшими или наименьшими значениями (элементов списка) или отображение строк с набольшими или наименьшими значениями в процентном отношении (% от количества элементов).

Результат отбора следующий:

Кроме того, можно выбрать значения выше или ниже среднего.

Отбор по ячейке

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

  1. Преобразуйте таблицу к наглядному виду. Для этого снимите фильтр со столбца Продажи, в столбце Группа щёлкните по списку, выберите Сортировка по цвету, Пользовательская сортировка и нажмите ОК.
  2.  Щелкните правой кнопкой мыши по любой ячейке.
  3. В контекстном меню выберите команду Фильтр, а затем в подчиненном меню принцип отбора: по значению выделенной ячейки (в нашем случае), цвету ячейки, цвету текста или по значку.

Для удаления фильтров выделите одну любую ячейку в диапазоне, для которого устанавливаются фильтры, нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите команду Фильтр или нажмите кнопку Фильтр группы Сортировка и фильтр вкладки Данные.

  1. Закройте файлФильтрФамилия_студента.xlsx без сохранения изменений.
  2. Сохраните файл отчёта ОтчётФильтрФамилия_студента.docx в папке Яндекс.Диск→Excel
  3. Закройте табличный процессор Ms Excel.
  4. Выполните Контрольное задание к практической работе №5.

Приглашайте друзей на мой сайт

Поддержите проект! Выберите один из вариантов платежа:

С карты, с баланса сотового, из Кошелька

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

Уровень мастерства: средний

Содержание

Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы можете добавить его в свою личную книгу макросов и использовать макросы в своих проектах.

Почему метод автофильтрации такой сложный?

Этот пост был вдохновлен вопросом от Криса, участника The VBA Pro Course. Комбинации Критерии и Операторы могут быть запутанными и сложными. Почему это?

Ну, фильтры развивались на протяжении многих лет. Мы увидели много новых типов фильтров, представленных в Excel 2010, и эта функция продолжает улучшаться. Однако параметры метода автофильтра не изменились. Они отлично подходят для совместимости со старыми версиями, но также означает, что новые типы фильтров работают с существующими параметрами.

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

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

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

Алгоритм действий во время сортировки

Фильтрация. Автофильтр.

Слишком сложно? Тогда закажите нам выполнение курсовой работы по информационным технологиям!

Алгоритм использования автофильтра:

  • скопируем исходные данные на новый лист и дадим ему имя «Фильтрация»;
  • установим курсор внутри таблицы;
  • в вкладках «Главная», группа «Редактирование», кнопка «Сортировка и фильтр», команда «Фильтр»;
  • около имени каждого поля таблицы появляются кнопки ;
  • при нажатии на кнопку, появляется меню, состоящее из 3х разделов: 1.сортировка (еще один способ сортировки); 2.фильтрация (где есть ряд команд, позволяющие фильтровать по дате, по тексту, по числовым данным);

Позволяет сразу удалить данные из списка, который фигурирует:

Найти среднее значение цены.

Вариант 2 (при помощи функции «Среднее значение»):

Цена ниже средней.

• для фильтрации продукции, цена которой ниже средней, используем «числовой фильтр», «цена ниже средней»:

Далее покажем самую дорогую продукцию. Для этого:

Результат применения расширенного фильтра:

Сделаем выборку продукции, цена которых больше или равна 550 рублей и класс больше или равен 32.

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий