Задание.. Заменить текущие итоги

Лабораторная работа № 8.

Тема. Работа со списками в Excel. Сортировка. Выборка данных.

Вычисление промежуточных итогов.

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

· каждая строка списка рассматривается как запись базы данных;

· столбцы списков считаются полями базы данных;

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

· в таблице не должно быть пустых строк и столбцов.

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

– добавлять, изменять и удалять записи в режиме формы данных;

– находить записи с помощью формы данных;

– сортировать записи;

– выполнять фильтрацию и выборку данных с помощью Автофильтра и Расширенного фильтра;

– подводить общие и промежуточные итоги и т.д.

Задание.

1. Открыть файл Лаб_работа_8.xls Сохранить файл на диске R:\.

2. Вставить в книгу дополнительно 5 рабочих листов: 1 лист – для автофильтра, 1 лист – для расширенного фильтра, 3 листа – для итогов (п.меню Вставка►Лист).

3. Скопировать исходную таблицу на все рабочие листы. В дальнейшем каждый пункт задания выполнять на отдельном листе. Листы переименовывать в соответствии с пунктом задания (п.меню Формат►Лист►Переименовать).

4. Провести на первом листе сортировку списка по алфавиту Марки тягача (графа 2), а затем – по количеству рейсов (графа 6) (п.меню Данные►Сортировка).

5. На втором листе для таблицы установить автофильтр (п.меню Данные►Фильтр►Автофильтр) и сформировать выборку записей

Ø о стоимости одного рейса для тагача Volvo (например) в диапазоне менее 500, но больше 300 евро.

Для выполнения выборки с помощью автофильтра надо:

1) Щелчком мыши по кнопке раскрыть список столбца, по которому будет производиться выборка;

2) Указать требуемые значения или выбрать строку (Условие…) и задать критерии выборки в диалоговом окне Пользовательский автофильтр (см.рис.)

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

Для отмены режима фильтрации надо установить курсор внутри таблицы и повторно выполнить команду Данные►Фильтр►Автофильтр.

6. На третьем листе, используя расширенный фильтр, провести выборку ФИО водителя

Ø По расход топлива – Итого(графа 14) меньше 35 л., а расстояние (графа 4) больше 1300 км

Ø Фамилия которых начинается с буквы «Д» или «Т»

Ø Конкретная марка тягача (графа 2) и расходы в евро (графа 15) которого больше среднего значения по полю расходы в евро (графа 15) (с помощью вычисляемого критерия).

Для формирования выборки с помощью расширенного фильтра надо:

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

· в одной строке, если они должны выполняться одновременно (т.е. связаны условием «И»);

· в разных строках, если должен выполниться хотя бы один (т.е. связаны условием «ИЛИ»);

· при записи критерия сравнения могут быть использованы: точные значения; шаблоны с символами подстановки * и ?; значения с операторами сравнения <, >, <=, >= и т.п.

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

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

3) Выполнить команду п.меню Данные►Фильтр►Расширенный фильтр;

4) В диалоговом окне указать:

· режим обработки – скопировать результат в другое место;

· адрес исходного диапазона;

· адрес диапазона условий;

· адрес левой верхней ячейки диапазона для размещения результата.

5) Нажать экранную кнопку ОК.

7. Вычислить на отдельных листах промежуточные итоги:

Ø по направлению (графа 3) с суммой по полям – расстояние (графа 4) и Итого расходы в бел. рублях (графа 16);

Ø по фамилиям (графа 1) с суммой по полю количество рейсов (графа 6) и средним значением по полю Итого расходы в евро (графа 15);

Ø по марке тягача (графа 2) с подсчетом количества по полю направление (графа 3) и суммой по полю расход топлива - Итого (графа 14).

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

1) Отсортировать данные в таблице по столбцу, для которого должны быть вычислены промежуточные итоги;

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

3) Выполнить команду п.меню Данные►Итоги;

4) В диалоговом окне в поле При каждом изменении в:из списка выбрать номер графы, по которой была выполнена сортировка;

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

6) В поле Добавить итоги по: флажками выбрать столбцы, содержащие значения, по которым надо подвести итоги.

7) Флажками установить режимы вывода итогов:

Заменить текущие итоги

12 Следующая ⇒