Практическая работа СТРУКТУРИРОВАНИЕ, КОНСОЛИДАЦИЯ ДАННЫХ И ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ
учебно-методическое пособие на тему

Практическая работа

СТРУКТУРИРОВАНИЕ, КОНСОЛИДАЦИЯ ДАННЫХ
И ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ

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

•  манипулирование данными, расположенными на разных листах рабочей книги;
•  списки и операции со списками (фильтрация, сортировка);
•  использование диалоговых окон для изменения информации в списках;
•  структура таблицы (создание и удаление);
•  консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация;
•  построение сводных таблиц.

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

Основные сведения о списках, структуре рабочего
листа, консолидации и сводных таблицах

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

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

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

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

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

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

Выполнение практической работы

Перед вами стоит задача совместной обработки нескольких таблиц – списков, расположенных на разных листах рабочей книги.

1. Загрузить программу Excel.

2. На листе рабочей книги (Лист1) создать табл. 1 с исходными данными о заказе партий запчастей у некоторой фирмы на год:

Таблица 1

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

4. Переименовать "Лист1" в "Заказ". Для этого установить указатель на ярлык "Лист1", нажать правую кнопку мыши, в контекстном меню выбрать команду Переименовать и вместо прежнего имени листа Лист1 ввести новое имя Заказ.

5. Получить итоговую сумму по столбцу "Сумма". Для этого установить курсор в ячейку Е12 и нажать кнопку автосуммирования. Ввести сформированную формулу.

6. Создать структуру построенной таблицы для скрытия детальных числовых данных. Для этого установите курсор внутри таблицы и выполните команду Данные/Группа и Структура/Создание структуры. На экране структуры таблицы щелкните кнопку "-", чтобы скрыть столбцы с числами, а затем кнопку "+" для показа скрытой информации.

7. Удалить структуру, выполнив команду Данные/Группа и Структура/Удалить структуру.
8. Добавить к существующим листам рабочей книги еще один. Для этого установить указатель на один из ярлыков, нажать правую кнопку мыши и выбрать команду Добавить. В диалоговом окне Вставка выделить значок с названием "Лист" и нажать кнопку "Ok".
9. Переименовать "Лист2", "Лист3", "Лист4" в "Январь", "Февраль", "Март" (табл. 2, 3, 4), так как они будут содержать информацию о реализации запчастей за первые три месяца года. Ввод данных осуществлять в соответствии с указаниями следующего пункта.

Январь                                Таблица 2

Февраль                                Таблица 3

Март                                Таблица 4

10. Сгруппировать листы "Январь", "Февраль", "Март" для ввода общей для них информации. Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише . Для ввода индивидуальной для каждой таблицы информации листы разгруппировать путем выбора в контекстном меню команды Разгруппировать листы.

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

12. Использовать консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого:

• добавить новый лист, переименовать его в "Квартал";
• выделить ячейку на новом листе "Квартал", начиная с которой будут размещены итоговые данные (например, А1);
• выполнить команду Данные/ Консолидация;
• в диалоге "Консолидация" выбрать в списке функций функцию Сумма;
• в строку "Ссылка" ввести абсолютную ссылку на консолидируемые данные (например, Январь!$А$2:$С$12) и нажать кнопку "Добавить";
• повторить ввод и добавление данных для ввода всей консолидируемой информации;
• включить флажок "значения левого столбца";
• нажать кнопку "Ok".

13. Изменить некоторые данные в одном из консолидируемых листов, например количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?

14. Установить связанную консолидацию данных. Для этого
вставить новый рабочий лист, переименовать его в "Квартал1", активизировать ячейку начала формирования итоговой таблицы (например, А1) , выполнить все положения пункта 12, добавив флажок "Создавать связи с исходными данными".

15. В полученной структурированной таблице просмотреть скрытые данные, нажав кнопки "2" или "+".
16. Изменить некоторые данные в одном из консолидируемых листов, например, количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?
17. Построить сводную таблицу, информирующую о сумме проданных деталей по каждому наименованию отдельно. Для этого:

• активизировать рабочий лист "Заказ";
• вызвать мастер сводных таблиц, выполнив команду Данные/ Сводная таблица;
• в окне "Мастер сводных таблиц – шаг 1 из 4" выбрать источник, откуда будут поступать данные для построения таблицы (первую из предложенных опций: "В списке или в базе данных Microsoft Excel") и щелкнуть по кнопке "Далее>";
• в окне шага 2 ввести область исходных данных для построения сводной таблицы, например, Заказ!$А$1:$F$11, и щелкнуть по кнопке "Далее>";
• в окне шага 3 для определения внешнего вида сводной таблицы требуется в макете сводной таблицы перетащить поле таблицы "Название" в поле макета "столбец", поле таблицы "Квартал" в поле макета "строка", "Сумма" должна быть расположена в поле "данные", а затем щелкнуть по кнопке "Далее>";
• в окне шага 4 установить переключатель "Новый лист" и нажать кнопку "Готово".

18. Изменить исходные данные (сначала убрать, а затем добавить одну строку в исходную таблицу) и в контекстном меню (щелчком правой клавиши мыши на поле сводной таблицы) выбрать команду Обновить данные.
19. Поменять местами строки и столбцы сводной таблицы. Для этого снова запустить Мастер сводных таблиц и в диалоге шага 3 повернуть макет таблицы на 90 градусов: в строках вывести названия изданий, а в столбцах – квартал, после чего завершить диалог.
20. Открыть макет сводной таблицы (3 шаг) и перетащить поле "Название" в область макета "страница", закончить диалог и обратить внимание на изменения в сводной таблице, затем нажать кнопку "Отобразить страницы" панели инструментов "Сводная таблица".

21.        Отсортировать данные таблицы "Заказ" по возрастанию цены. Для этого:

• сделать текущей ячейку поля "Цена";
• нажать кнопку инструментального меню "Сортировать по возрастанию".

22.        Выполнить многоуровневую сортировку по двум ключам: сначала по цене, потом по названиям в порядке возрастания значений этих ключей. Для этого:

• установить курсор в область данных таблицы "Заказ";
• вызвать команду Данные/Сортировка;
• в диалоговом окне в область "Сортировать по" ввести первый ключ сортировки "Цена"; в область "Затем по" ввести второй ключ сортировки "Название";
• нажать кнопку "Параметры..." и ознакомиться с возможными вариантами задания параметров сортировки;
• щелкнуть кнопку "Ok".

23.        Выполнить подсчет промежуточных итогов по Количеству шт. в партии отдельно по кварталам, предварительно отсортировав данные таблицы "Заказ" по возрастанию номера квартала. Для этого:

• сделать текущей ячейку поля "Квартал";
• нажать кнопку инструментального меню "Сортировать по возрастанию";
• выполнить команду Данные/Итоги;
• в диалоговом окне команды "Промежуточные итога" в области "При каждом изменении в" выбрать "Квартал", в области "Операция" выбрать "Сумма", в области "Добавить итоги по" выбрать " Количество шт. в партии ";
• щелкнуть кнопку "Ok".

24.        Аннулировать промежуточные итоги таблицы "Заказ". Для этого:

• установить указатель мыши на таблицу "Заказ";
• выполнить команду Данные/Итоги;
• в диалоговом окне команды "Промежуточные итоги" щелкнуть по кнопке "Убрать все".

25.        Использовать форму базы данных для просмотра и корректировки записей таблицы "Заказ". Для этого:

• выделить область рабочего листа, где находятся данные вместе с названием столбцов;
• выполнить команду Данные/Форма;
• переместиться вперед и назад по таблице, используя кнопки "Далее" и "Назад";
• удалить последнюю строку таблицы нажатием кнопки "Удалить";
• добавить новую строку, используя кнопку "Добавить";
• закрыть диалог нажатием кнопки "Закрыть".

26. Использовать форму базы данных для поиска в таблице "Заказ" информации о запчастях, Количество шт. в партии которых превышает 10000. Для этого:

• выделить область рабочего листа, где находятся данные вместе с названием столбцов;
• выполнить команду Данные/Форма;
• в диалоговом окне "Заказ" щелкнуть по кнопке "Критерии";
• в поле Количество шт. в партии ввести критерий поиска (>10000);
• переместиться вперед и назад по найденным записям, используя кнопки "Далее" и "Назад";
• закрыть диалог нажатием кнопки "Закрыть".

27. Самостоятельно выполнить:

• сортировку данных таблицы "Заказ" по возрастанию значений поля "Номер";
• поиск в таблице "Заказ" информации о запчастях, заказанных в первом квартале с Количеством шт. в партии менее 20000.

28. Использовать автофильтр для вывода в таблице "Заказ" информации только о запчастях, цена которых более 5000 руб. Для выполнения этого задания необходимо:

• отметить область рабочего листа с данными и с заголовками;
• выполнить команду Данные/Фильтр/Автофильтр;
• раскрыть список на поле "Цена", выбрать пункт "Условие" и ввести выражение "больше 5000";
• щелкнуть кнопку "Ok".

29. Отменить автофильтр, для этого выполнить команду Данные/Фильтр и снять пометку с позиции Автофильтр.

30. Использовать усиленный фильтр для получения данных о запчастях, цена которых менее 5000, заказанных в третьем квартале Количеством шт. в партии более 10000. Для этого:

• скопировать имена столбцов "Цена", "Квартал" и  "Количество шт. в партии" в ту часть рабочего листа, которая не содержит данных для поиска: H1,I1,J1;
• в клетки H2,I2,J2 ввести критерии поиска (<5000, >10000, Кв3);
• выполните команду меню Данные/Фильтр/Расширенный фильтр;
• в диалоге "Расширенный фильтр" задать область, где находятся данные (интервал списка) и область, в которой заданы критерии поиска;
• в группе "Обработка" укажите, что фильтрация будет выполняться на месте;
• щелкните кнопку "Ok".

31. Сохранить рабочую книгу в файле с именем lab3.xls.

32. Для выхода из Excel выберите из меню команду Файл/Выход.

Задания для самостоятельной работы

Проанализируйте данные о произведённых ремонтных работах некоторого автосервиса, в качестве исходных данных используйте данные в файле copir/ 311-315_ИТ_в_ПД /ТО.xls. Результаты по каждому вопросу представьте на отдельном листе.

1. Проанализируйте суммы ремонта по видам ремонта

Для этого существует несколько способов:

Первый:

1. Отсортируйте всю таблицу по столбцу вид ремонта.
2. Выполните команду Данные/ Итоги просуммируйте Сумму ремонта при каждом изменении вида ремонта. Отобразите только итоги по видам ремонта

Второй:

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

Постройте диаграмму.

2. Проанализируйте суммы ремонтов по видам ремонта и маркам автомобилей.
3. Проанализируйте количество ремонтов по маркам автомобилей.
   Получите фамилии владельцев а/м форд, для этого дважды кликните по соответствующей ячейке сводной таблицы или примените фильтр.
4. Проанализируйте количество и суммы ремонтов по мастерам-исполнителям.
5. Найдите информацию о ремонтах на сумму более15000 руб.
   Найдите информацию о ремонтах на сумму более15000 руб. а/м Audi мастера Зайцева (примените фильтр).
6. Подготовьте информацию о клиентах проходивших ТО1 отдельно по каждой марке.
7. Подготовьте информацию о времени работы мастеров отдельно за каждый месяц.
   Для этого постройте сводную таблицу в область строк поместите дату ремонта, в области столбцов ФИО мастера, в область данных сумму по времени работ.
   Сгруппируйте данные по месяцам: установите курсор на любую ячейку с датой и выполните команду Данные/ Группировка
8. Сгруппируйте ремонты на мелкие, средние и крупные (до 3000р.; от3000 до10000р.; свыше 10000р.) и подсчитайте их количество и среднюю сумму по группе.
9. Проанализируйте количество ремонтов по видам ремонтов отдельно по маркам автомобилей Audi, Nissan и Skoda.
   Постройте сводную таблицу, где в области строк будут виды ремонта, в области столбцов – модели а/м, марки а/м поместите в область страниц и выберите только марки Audi, Nissan и Skoda, а в область данных поместите сумму ремонта.