Тема 15. Работа с электронной таблицей как с базой данных Лабораторная работа №16

Вопросы к лабораторному занятию

1. Фильтрование списка

2. Промежуточные итоги

3. Создание структуры таблицы

4. Анализ и обобщение данных с помощью сводных таблиц

1. Методические рекомендации

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

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

1.1 Фильтрация данных списка

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

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

Метки (заголовки) столбцов преобразуются в раскрывающиеся списки (кнопки – стрелки) , в которых можно задавать нужные критерии для поиска данных.

В раскрывающемся списке выводятся все значения, встречающиеся в столбце, и дополнительные опции: Все, Первые 10, Условие, Пустые или Непустые.

При выборе конкретного значения или опций Все, Пустые (Непустые) на экран выводятся соответствующие записи списка.

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

Опция Условие позволяет в диалоговом окне Пользовательский автофильтр указать для одного столбца один или два критерия отбора, объединив их (И – для случая, когда оба условия должны выполняться одновременно, ИЛИ – для случая, когда достаточно выполнение хотя бы одного из условий).

Сброс одного из фильтров осуществляется выбором опции Все раскрывающегося списка этого фильтра. Отмена режима фильтрации (без уничтожения фильтров) реализуется командой Данные/Фильтр/Показать все. Удаление фильтров (т.е. отключение Автофильтра) производится командой Данные/Фильтр/Автофильтр.

1.2. Формирование общих и промежуточных итогов.

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

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

Команда Данные/Итоги вызывает диалоговое окно Промежуточные итоги, содержащее три поля ввода.

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

В списке поля ввода Операция выбирается функция, вычисляющая итоги. Для подведения итогов по числовым полям используются следующие функции: Сумма (по умолчанию), Среднее, Максимум, Минимум, Произведение, Кол-во чисел и функции, вычисляющие отклонение от среднего.

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

В поле ввода Добавить итоги по выделяются (левой кнопкой мыши) столбцы, по которым подводятся итоги.

Диалоговое окно Промежуточные итоги содержит три флажка. Флажок Заменить текущие итоги разрешает обновление данных. Для сохранения текущих итогов и добавления новых следует снять этот флажок.

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

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

Кнопка Убрать все диалогового окна позволяет удалить все итоги из текущего списка.