Лабораторная работа 6 Excel

ЛАБОРАТОРНАЯ РАБОТА № 6

Тема: Фильтрация данных. Автофильтр. Расширенный фильтр.

Цель работы: Научить студентов с помощью команды Фильтр отыскивать и использовать нужное подмножество данных в списке при решении задач предметной области.

Краткий комментарий

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

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

- Расширенный фильтр для более сложных условий отбора.

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

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

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

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;интервал_вычисл.)

В ней первым аргументом является номер математической или статистической операции – это число от 1 до 11 (9 – это суммирование; 1 – вычисление среднего значения; 2 и 3 – подсчет количества чисел и непустых ячеек; 4 и 5 – вычисление максимума и минимума; 6 - произведение; 7 и 8 – стандартное отклонение; 10 и 11 – дисперсия). Вторым аргументом функции является интервал вычислений.

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

Критерии отбора могут быть и более сложными. Для этого необходимо вызвать диалоговое окно Пользовательский автофильтр.

Чтобы увидеть таблицу целиком, следует выбрать в списке критериев пункт Выделить все.

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

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

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

Практическое задание

1. Загрузить MS Excel 2007.

2. На Листе 1 рабочей книги на основании данных, представленных на рис. 6.1, создать таблицу. Установить при этом достаточную ширину столбцов и высоту строк, в «шапке» таблицы выполнить центрирование заголовков. Оформить таблицу линиями.

Рис. 6.1 Обеспеченность кормами животных

3. Произвести вычисление столбца «% к потребности» по формуле:

«% к потребности» = «Фактически скормлено кормовых единиц» / «Плановая потребность кормовых единиц» * 100

4. Произвести вычисление столбца «Отклонение от плана, кормовых единиц» по формуле:

«Отклонение от плана, кормовых единиц» = «Плановая потребность кормовых единиц» - «Фактически скормлено кормовых единиц».

5. Посчитать Всего по группам кормов по столбцам 3, 5, 7.

6. Сохранить документ в своей фамильной папке под именем лр6зад1.

7. Используя автофильтр, отобразить записи, которые соответствуют более 100 % к потребности.

Для этого необходимо выделить таблицу в диапазоне A4:G11, а затем на вкладке Данные в группе Сортировка и фильтр выбрать кнопку Фильтр, при этом в первой строке выделенного диапазона появятся кнопки. Раскрыть кнопку столбца «% к потребности» и выбрать соответствующие команды, как указано на рис. 6.2.

Рис. 6.2 Команды кнопки Автофильтр

При этом появляется диалоговое окно «Пользовательский автофильтр» (Рис. 6.3), в котором задаем условие отбора по шестому столбцу.

Рис. 6.3 Диалоговое окно Пользовательский автофильтр

Это позволит отфильтровать таблицу по заданному условию.

8. Дополнить отфильтрованную таблицу строками «Итого», «Минимум», «Максимум», «Среднее» и рассчитать эти показатели с помощью функции Промежуточные итоги.

9. Построить по отфильтрованной таблице гистограмму по 3 и 5 столбцам со всеми оформительскими элементами: название диаграммы - Обеспеченность животных кормами; легенда – плановая потребность кормовых единиц и фактически скормлено кормовых единиц каждого вида корма. Гистограмму разместить на этом же листе.

10. Сохранить результат под именем лр6зад2 в своей фамильной папке.

11. Отобразить все записи в таблице.

Для этого в условиях отбора (Рис. 6.2) выбрать команду «Выделить все».

12. Используя автофильтр, отобразить записи, у которых «Отклонение от плана, кормовых единиц» - отрицательные числа.

13. Сохранить результат под именем лр6зад3 в своей фамильной папке.

14. Восстановить все записи, снять автофильтр.

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

Для этого на вкладке Данные в группе Сортировка и фильтр выбрать кнопку Дополнительно (Рис. 6.3)

Рис. 6.3 Кнопка Дополнительно для расширенного фильтра

При этом появляется диалоговое окно, в котором установить параметры, как указано на рис. 6.4.

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

Результат скопировать в другое место.

16. Сохранить документ в фамильной папке под именем лр6зад4.

17. Используя расширенный фильтр, отобрать фактически скормленные виды кормов, у которых содержание кормовых единиц выше 18000, и меньше 12000, результат поместить в новом месте.

18. Сохранить документ в фамильной папке под именем лр6зад5.

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

1. Что такое фильтрация? Чем отличается фильтрация от сортировки?

2. Какими способами можно выполнить фильтрацию?

3. Как отфильтровать список с помощью Автофильтра?

4. Как отфильтровать список с помощью Расширенного фильтра?

5. Как произвести вычисления по отфильтрованному участку?