Лабораторная работа № 8 обработка данных

В программу Excel встроен аппарат быстрого и удобного извлечения и форматирования различных обобщенных сводок. Эти операции производятся с помощью команд Итоги, Консолидация, Сводная таблица.

8.1. Работа с листами в электронных таблицах Задание №1. Работа с листами

Основными командами работы с листами являются:

• Удалить лист;

• Добавить лист;

• Переименовать лист;

• Переместить лист.

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

1 шаг Курсор подвести на ярлычок листа → нажать правую кнопку мыши.

2 шаг Выбрать необходимую команду.

Рассмотрим основные команды работы с листами на примере задачи «Расчет с клиентами в отеле» (см. лабораторную работу № 6, стр. 60).

 Загрузите файл электронной таблицы «Расчет с клиентами отеля».

Рассмотренная задача учитывала расчет клиентов, проживающих на первом этаже.

Рассмотрим, как произвести расчет клиентов, проживающих на трех этажах.

 Переименуйте «Лист1» на «Этаж1»:

 Курсор подведите к ярлыку «Лист1» и нажмите правую кнопку мыши:

 Выберите параметр «Переименовать».

 Введите «Этаж1».

 Переименуйте «Лист2» на «Этаж2»

 Переименуйте «Лист3» на «Этаж3»

 Переименуйте «Лист3» на «Итог»

Исходные и расчетные данные для расчета клиентов на втором этаже аналогичны данным клиентов на первом этаже.

 Произведите

 Перейдите на лист «Этаж1».

 Выделите содержание всего листа.

 «Правка» → «Копировать».

 Перейдите на лист «Этаж2».

 Курсор установите на ячейку A1

 «Правка» → «Вставить»

 Измените значения данных: ФИО клиентов, дни проживания, наличие брони на листе «Этаж2».

 Скопируйте значения листа «Этаж2» на лист «Этаж3».

 Измените значения данных: ФИО клиентов, дни проживания, наличие брони на листе «Этаж3».

8.2. Консолидация данных Задание №2. Консолидация данных

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

Для выполнения команды консолидации необходимы следующие шаги:

1 шаг Курсор установить на свободное место таблицы (справа и снизу должно быть свободно) или на новый лист.

2 шаг «Данные» → «Консолидация»:

3 шаг В поле «Функция» установить вид операции (чаще всего Сумма).

4 шаг В поле «Ссылка» ввести диапазон ячеек, для которых будет производиться анализ.

5 шаг Нажать кнопку «Добавить».

6 шаг По необходимости вернуться к 4 и 5 шагам.

7 шаг Установить или сбросить признаки в поле «Использовать в качестве имен» - если установлены, то в консолидирующей таблице подписи верхней строки и левого столбца будут применены в качестве имен.

8 шаг Установить или сбросить признак «Создавать связи с исходными данными» - установка признака предназначена для обновления данных в консолидирующей таблице при изменении исходных данных в составляющих таблицах.

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

 Создайте консолидирующую (объединенную) таблицу краткого анализа о клиентах, проживающих на трех этажах:

 Скопируйте строку 23 листа «Этаж1» на строку 1 листа «Итог».

 Скопируйте блок ячеек В25:Е31 листа «Этаж1» в блок ячеек В5:Е11 листа «Итог».

 Скопируйте блок ячеек Н25:J35 листа «Этаж1» в блок ячеек J5:L15 листа «Итог».

 Курсор установите на ячейку Н5

 «Данные» → «Консолидация».

 В поле «Функция» установите «Сумма».

В поле «Ссылка» нажмите на пиктограмму установки диапазона ячеек:

 Перейдите на лист «Этаж1».

 Курсором установите диапазон ячеек J25:J31.

 Нажмите пиктограмму конца определения диапазона:

 Нажмите кнопку «Добавить» (диапазон установится в поле «Список диапазонов»).

 Перейдите на лист «Этаж2».

 Курсором установите диапазон ячеек J25:J31.

 Нажмите пиктограмму конца определения диапазона

 Нажмите кнопку «Добавить» (диапазон установится в поле «Список диапазонов»)

 Перейдите на лист «Этаж3».

 Курсором установите диапазон ячеек J25:J31.

 Нажмите пиктограмму конца определения диапазона

 Нажмите кнопку «Добавить» (диапазон установится в поле «Список диапазонов»)

 Перейдите на лист «Итог».

 В поле «Использовать в качестве имен» сбросьте признаки «Подписи верхней строки» и «Значение левого столбца».

 Нажмите кнопку «ОК».

После выполненных действий будет создана объединенная таблица вида:

 Произведите консолидацию по итоговым суммам.