Использование фильтров для анализа данных в списках

Фильтрация данных – это способ выделения подмножества данных по заданному критерию для последующей обработки. Отфильтровать список – значит скрыть все строки, которые не удовлетворяют заданным условиям отбора. Excel предоставляет две возможности фильтрации данных в списках: Автофильтр (для более простых случаев) и Расширенный фильтр (для более сложных условий отбора).

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

Рис. 1.4. Использование Автофильтра

Автофильтр можно использовать для выбора:

§ определенных значений, встречающихся в данном столбце списка;

§ заданного числа (или заданного процента) наибольших или наименьших элементов в списке;

§ строк, в которых конкретный столбец содержит пустые ячейки;

§ значений, соответствующих одному или двум условиям отбора (больше, меньше, больше или равно и т. п.), связанных операторами сравнения И (And) или ИЛИ (Or).

Для того чтобы удалить Автофильтр для конкретного столбца, необходимо раскрыть соответствующий список автофильтра и выбрать в нем пункт (Все). Чтобы полностью отменить автофильтры, в меню Данные, подменю Фильтр, команда Отобразить все.

Усиленный (Расширенный) фильтр применяется для фильтрации данных по более сложным критериям.

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

§ допускается создавать условия, соединенные логическим оператором ИЛИ (OR) для нескольких столбцов;

§ допускается задание трех и более условий для конкретного столбца с использованием, по крайней мере, одного логического оператора ИЛИ;

§ допускается задание вычисляемых условий;

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

Команда Расширенный фильтр, в отличие от команды Автофильтр, требует задания условий отбора строк (диапазона условий) в отдельном диапазоне рабочего листа.

Диапазон условий лучше поместить выше списка и отделить пустой строкой. Диапазон условий должен содержать, по крайней мере, две строки: одна – это заголовок столбцов документа (метки критериев), а последующие строки – это условия отбора (критерии отбора).

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

§ невычисляемые условия (например, поиск всех сотрудников, старше 25 лет, имеющих оклад больше 500 грн), рис. 1.5;

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

Рис. 1.5. Использование невычисляемых условий в фильтрах

При использовании невычисляемых условий отбора следует соблюдать следующие условия оформления диапазона условий:

1. Заголовки в диапазоне условий должны точно совпадать с заголовками столбцов в списке.

2. Если условия связываются оператором ИЛИ, то они вводятся в одну строку в каждом из столбцов, на поля которых накладываются условия (см. рис. 1.5).

3. Условия, связанные логическим оператором И, вводятся в одну строку.

4. Пустая строка в диапазоне условий обозначает «любое значение».

Рис. 1.6. Использование вычисляемых условий в фильтрах

При использовании вычисляемых условий отбора следует соблюдать следующие условия оформления диапазона условий:

1. Заголовок над вычисляемым условием должен отличаться от заголовка любого из столбцов списка или он может быть пустым (см. рис. 1.6).

2. Ссылки на ячейки, находящиеся вне списка, должны быть абсолютными.

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

4. Вычисляемое условие должно начинаться со знака =, после которого ставится относительная ссылка на первую ячейку столбца с условием. Например, в ячейке В2, приведенного на рисунке 1.6 примера, размещается следующая формула =H5>СРЗНАЧ($H$5:$H$17).

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

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

1) установить курсор в любую ячейку списка, записи которого будут отфильтрованы;

2) с помощью меню Данные, подменю Фильтр, команды Расширенный фильтр вызвать одноименное диалоговое окно (см. рис. 1.7);

3) в открывшемся диалоговом окне указать исходный диапазон и диапазон условий;

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

Рис. 1.7. Окно диалога Расширенный фильтр

Вопросы для самоконтроля.

1. Как в Microsoft Excel определяется список?

2. Для чего при работе со списком используются формы?

3. Закрепление на экране строк и столбцов списка.

4. Стандартный порядок сортировки Microsoft Excel.

5. Создание и применение пользовательского порядка сортировки.

6. Применение автофильтров к нескольким столбцам.

7. Создание и использование расширенных фильтров для анализа данных в списке.

8. Использование вычисляемых и невычисляемых условий в расширенных фильтрах.

9. Копирование отобранных строк и столбцов в другую часть листа.

Вопросы и задания для самостоятельной работы.

1. Назначение имен спискам, использование имен списков в формулах.

2. Текстовые функции.

3. Сортировка столбцов списка.

4. Сортировка списков по четырем и более столбцам.

5. Сортировка части списка.

6. Сортировка списков и диапазонов, содержащих формулы.

7. Использование символов шаблона в пользовательском автофильтре.

8. Использование при работе с расширенным фильтром операторов ИЛИ для одного столбца.

9. Одновременное использование операторов ИЛИ и И при работе с расширенным фильтром.

10. Задание текстовых условий при работе с расширенным фильтром.

Задания лабораторной работы 1.1.

1. Создать в табличном редакторе Microsoft Excel базу данных (список) Список сотрудников организации.

1 шаг. Скопировать из текстового файла ЗАДАНИЕ 1.doc перечень столбцов списка, применяемых при создании базы данных, в рабочую книгу Microsoft Excel.

2 шаг. Полученный столбец преобразовать в заголовок списка.

3 шаг. С помощью функций ДЛСТР(), ЛЕВСИМВ(), ПРАВСИМВ() очистить текст от лишних символов (номера по порядку, пробела и точки с запятой в конце строки).

4 шаг. Поместить имена столбцов в строку (меню – Правка, команда – Специальная вставка, группа – Вставить, переключатель – Значения, включить флаг – Транспонировать).

5 шаг. Используя специальное диалоговое окно Форма, ввести в список две строки данных:

6 шаг. Скопировать данные в список из файла Задание 21.xls.

7 шаг. Для удобства работы со списком закрепить на экране Заголовок списка и первые три столбца таблицы.

2. Отсортировать полученный список по отделу, по фамилии и по имени.

3. Отсортировать список в следующем порядке должностей: Начальник, Секретарь, Менеджер, Инженер, Аудитор, Экспедитор.

1 шаг. Создать соответствующий пользовательский список.

2 шаг. Сортировать список, используя нестандартный порядок сортировки.

4. Присвоить листу со списком имя Сотрудники.

5. Проанализировать данные в списке, используя два вида предоставляемых Microsoft Excel фильтров.

6. Используя автофильтр, выбрать из списка всех сотрудников, принятых на работу позже 1996 года, и скопировать их на новый лист.

1 шаг. Добавить столбец Возраст и рассчитать возраст каждого работника (определить общее количество прожитых дней; разделить полученное число на 365,25; отбросить от полученного значения дробную часть (функция ОТБР())).

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

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

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

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

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

Дата добавления: 2014-11-29