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

Задачу отбора для дальнейшего анализа из полного списка только части строк в соответствии с некоторым условием решает инструмент Фильтр. Фильтрация — это выборка подмножества данных из списка в соответствии с заданными критериями. MS Excel предлагает два вида фильтров: автофильтр, который используется для отбора строк по простым условиям, и расширенный фильтр, позволяющий выбирать строки по сложным условиям. Далее рассматриваются типовые экономические задачи, связанные с фильтраций.

Задание 1.9. Компания хранит информацию о выполняемых проектах (табл. 1.17). Для дальнейшего анализа требуется получить следующие выборки:

  • ? список текущих проектов;
  • ? список проектов стоимостью от 1000000 до 1800000 руб.;
  • ? список проектов Заказчика 1, стоимостью больше 1000000 руб., и Заказчика 2, стоимостью более 2000000 руб.;
  • ? несданные проекты для Заказчика 2, срок выполнения которых на сегодняшний день прошел.

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

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

Таблица 1.17

Информация о проектах

Руководитель

проекта

Тема

проекта

Заказчик

Стоимость

проекта,

руб.

Дата

начала

Срок

выполнения

Состояние

ФИО

Тема

Заказчик 1

1 659 300

01.10.2013

15.12.2014

В работе

ФИО

Тема

Заказчик 2

1 002 500

15.06.2013

10.12.2013

Сдача

ФИО

Тема

Заказчик 1

256 000

16.09.2013

01.02.2014

В работе

ФИО

Тема

Заказчик 3

1 200 000

21.12.2013

15.06.2014

Согласование

ФИО

Тема

Заказчик 4

580 000

01.12.2013

14.02.2014

Завершен

ФИО

Тема

Заказчик 5

356 000

01.12.2013

20.10.2014

В работе

ФИО

Тема

Заказчик 2

5 230 000

05.11.2013

07.10.2014

В работе

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

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

Вторая задача также может быть решена с помощью автофильтра. Для этого в меню, появившемся после щелчка на стрелке в ячейке с заголовком Стоимость проекта, руб., можно выбрать команду Числовые фильтры/между и в появившемся диалоговом окне задать условие отбора: больше 1000000 И меньше 1800000 (рис. 1.20).

Условие отбора записей по простому диапазону условий

Рис. 1.20. Условие отбора записей по простому диапазону условий

Следует отметить, что с помощью автофильтра не удастся решить, например, задачу об отборе строк по проектам Заказчика 1, стоимостью больше, например, 1000000 руб., и проектам Заказчика 2, стоимостью больше, например, 2000000 руб. Также он не позволит сформировать условие с использованием ссылок на ячейки.

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

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

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

  • 1) в свободную строку вне таблицы следует скопировать заголовки тех столбцов, на значения которых будут наложены ограничения, — Заказчик и Стоимость проекта, руб.;
  • 2) под каждым заголовком надо задать условия выборки данных, соблюдая правило: если условия связаны логическим оператором И, то они располагаются в одной строке, в противном случае (ИЛИ) — в разных строках (табл. 1.18).

Таблица 1.18

Пример диапазона условий

Заказчик

Стоимость проекта, руб.

Заказчик 1

>1000000

Заказчик 2

>2000000

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

Таблица 1.19

Пример диапазона для размещения результатов фильтрации

Тема проекта

Заказчик

Стоимость проекта, руб.

Расширенный фильтр вызывается кнопкой Дополнительно на вкладке Данные. В появившемся одноименном окне (рис. 1.21) в поле Исходный диапазон необходимо указать всю таблицу, включая заголовки столбцов, в поле Диапазон условий — диапазон ячеек, представленный в табл. 1.18, в поле Поместить результат в диапазон — ячейки из табл. 1.19.

Диалоговое окно Расширенный фильтр

Рис. 1.21. Диалоговое окно Расширенный фильтр

Важно! Начинать фильтрацию необходимо с того листа, где планируется размещение таблицы результата

Рассмотрим, как задать диапазон условий для последнего отчета. Очевидно, что диапазон условий должен содержать заголовок «Заказчик». Условие же, сравнивающее срок окончания проекта с текущей датой, должно содержать ссылку на ячейку с текущей датой (ее надо создать заранее, используя функцию СЕГОДНЯ(). Она введена ее в ячейку Ml). Критерии фильтрации, содержащие адреса или имена ячеек, называются вычисляемыми и формируются совсем по новым правилам:

  • ? заголовок для такого условия должен быть отличным от заголовков таблицы (!);
  • ? формула, используемая для создания условия отбора, должна включать относительную ссылку ячейку первой строки столбца, значения которого фильтруются. Результат — всегда одно из двух значений: ИСТИНА или ЛОЖЬ. В нашем примере условие имеет вид: =F2<=$M$1.

Несданные проекты отбираются с помощью функции НЕ: =НЕ (02=”Сдача”). Диапазон условий в итоге примет вид, представленный в табл. 1.20.

Таблица 1.20

Пример диапазона с вычисляемым условием

Заказчик

Заголовок 1

Заголовок 2

Заказчик 2

=НЕ(02=»сдача»)

=F2<=$M$1

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

Задание для самостоятельной работы

Отобрать из табл. 1.17 информацию о 25% самых «дешевых» проектах. Для решения задачи используйте функцию КВАРТИЛЬ.

 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ     След >