Практическая работа 6. Создание сводных таблиц и консолидация данных
Упражнение 1. Создание сводных таблиц через объединение листов. Требуется подсчитать общий заработок всех рабочих отдела за два дня.
1. Заполните ячейки таблицы по образцу (данные о рабочих за первый день):
2. Установите следующие параметры таблицы:
- Ширина столбцов по порядку: 15; 13; 11,57; 10. Для этого ЩПКМ по заголовку столбца А, в контекстном меню выберите пункт «Ширина столбца…», в появившемся диалоговом окне поставьте цифру 15. Аналогично определите ширину столбцов В, С и D;
•Шапка таблицы: «Выравнивание» – переносить по словам, выравнивание по центру;«Шрифт» – полужирный;
•В ячейках с цифрами и «–» установите «Выравнивание» по центру;
•Графу «Заработок» посчитайте, используя расчётные формулы. В данном примере это =В2*С2;
•Строку «Итого» заполните, используя значок на панели инструментов.
3. Предложенную таблицу скопируйте на второй лист:
•Выделите ячейку А1 ЩЛКМ по ней;
•Не убирая курсор с ячейки, зажмите ЛКМ и протяните курсор от ячейкиА1 до D8;
•На панели инструментов ЩЛКМ по значку «Копировать»;
•Щёлкните по ярлычку «Лист2»;
•Установите курсор в ячейку А1 и ЩЛКМ по значку «Вставить».
4. Измените числовые данные в столбце «Количество отработанный часов» (кроме строки «Итого») – данные о рабочих за второй день.
5. Создайте итоговую таблицу №3 от таблиц №1 и №2.
•Скопируйте таблицу №1 на Лист3 (см. п2) и удалите из полученной третьей таблицы все числовые данные (при удалении используйте клавишу Delete);
•Сосчитайте числовое значение ячейки В2 (Лист3);
•Выделите ЩЛКМ ячейку В2 (Лист3);
•При помощи клавиатуры напишите значок «=»;
•ЩЛКМ по ярлыку Лист1;
•В открывшемся листе ЩЛКМ по ячейке В2 (Лист1);
•Не уходя с Листа1, наберите значок «+» с клавиатуры;
•ЩЛКМ по ярлыку Лист2;
•ЩЛКМ по ячейке В2 (Лист2);
•Не переключаясь с Листа2, нажмите Enter.
6. Скопируйте формулу во все ячейки диапазона, для этого за маркер заполнения протяните до ячейки D2 и вниз до D7. Сводная таблица должна принять следующий вид:
7. Выполненную работу покажите преподавателю.Упражнение 2. Создание сводных таблиц через объединение книг. Условие задания, п.1,2,3 см. в №1.
1. Создайте новую книгу: либо ЩЛКМ по кнопке «Создать» на панели инструментов, либо выполните команду меню «Файл» - «Создать».
2. Расположите книги рядом:
•Выполните команду меню «Вид» - нажмите значок 
;
•В результате окно Excel разделится на 2 части: Книга1 и Книга2.
3. Создайте итоговую таблицу №3 от таблиц №1 и №2. Скопируйте таблицу №1 на Лист1 Книги2 (см. п2) и удалите из полученной третьей таблицы все числовые данные (при удалении выделите диапазон ячеек B2:D7 и нажмите клавишу Delete).
4. Сосчитайте числовое значение ячейки В2 – Книга2, Лист1:
•Выделите ЩЛКМ ячейку В2 – Книга2, Лист1;
•При помощи клавиатуры напишите значок «=»;
•ЩЛКМ по ячейке В2 - Книга1, Лист1;
•Наберите значок «+» с клавиатуры;
•ЩЛКМ по ячейке В2 - Книга1, Лист2;
•Нажмите Enter.
5. В результате в ячейке В2 – Книга2, Лист1 – появится суммарное значение количества отработанных часов Кузнецова А.Л. за два дня работы.Проделайте то же самое для всех оставшихся ячеек таблицы №3 с числовыми значениями (кроме столбика С).
6. Цифровые данные столбика С (включая ячейку С8) скопируйте в таблицу №3 из таблицы №2.
7. Выполненную работу покажите преподавателю.Упражнение 3. Создание сводных таблиц через мастер сводных таблиц. Для трёх первых рабочих из №1 подсчитать суммарное количество отработанных часов и общую сумму заработка за два дня работы.
1. Заполните ячейки таблицы по образцу:
•Ширину столбцов, шапку таблицы и выравнивание оформите так же, как в №2; диапазон ячеек A1:D7. Последний столбец посчитайте, используя расчётные формулы.
2. Создание сводной таблицы.
•Выделите полученную таблицу;
•Выполните команду меню «Вставка» - «Сводная таблица»;
•Нажмите кнопку «ОК»;
3. В результате на новом листе появится заготовка будущей сводной таблицы и панель инструментов «Сводные таблицы», на которой расположены названия из шапки вашей таблицы.
•Перетащите Ф.И.О. в «Название столбцов», стоимость – в «Название строк», заработок и количество – в «Значения», (причём, когда будете переносить заработок, тащите его на надпись «Сумма по полю Количество отработанных часов»или в поле с его значениями);
•Закройте панель «Сводные таблицы».
•Вы получили сводную таблицу:
4. Результат покажите преподавателю.Упражнение 4. Создание сводных таблиц через консолидацию данных.
При консолидации данных над значениями, расположенными в различных областях, могут быть выполнены те же операции, что и при автоматическом подведением итогов, например вычисление суммы, произведения, нахождение максимального, минимального, среднего значения.
Источники консолидируемых данных могут находиться на одном листе, на нескольких листах, в разных книгах и или в разных местах диска.
Консолидация данных по расположению – используется для однотипных данных, упорядоченных одинаковым образом. Консолидация данных по категориям – используется для однотипных данных, организованных по разному.
Консолидация по расположению.
Для всех рабочих отдела сосчитать суммарное количество всех изготовленных ими деталей, бракованных деталей и деталей стандартного качества.
1. Заполните ячейки таблицы по образцу:
- Ширину столбцов, шапку таблицы и выравнивание оформите так же, как в №2. В данной таблице рассматривается диапазон ячеек A1:D7. Последний столбец посчитайте, используя расчётные формулы.
2. Созданную таблицу скопируйте на второй лист и измените числовые данные в графе «Количество изготовленных деталей» и«Брак».
3. Создайте новую книгу: либо ЩЛКМ по кнопке «Создать» на панели инструментов, либо выполните команду меню «Файл» – «Создать».
4. Расположите книги рядом:
- Выполните команду меню «Вид» – нажмите значок ;
- В результате окно Excel разделится на 2 части: Книга1 и Книга2.
5. Скопируйте таблицу №1 на Лист1 Книги2 (см. п2) и удалите из полученной третьей таблицы все числовые данные (при удалении выделите диапазон ячеек B2:D7 и нажмите клавишу Delete).
6. Создайте сводную таблицу при помощи консолидации:
•Сделайте активной ячейку В2 – Книга2, Лист1 – ЩЛКМ по ней;
•Выполните команду меню «Данные» - «Консолидация…»;
•На экране появится диалоговое окно «Консолидация», в котором курсор мигает в строке под названием «Ссылка».
•Отодвиньте диалоговое окно за заголовок так, чтобы оно не закрывало данные Книги1;
•Не закрывая диалогового окна, выделите в Книге1, Лист1 диапазон ячеек В2:D7 и нажмите кнопку «Добавить»;
•ЩЛКМ по ярлыку «Лист2» и нажмите кнопку «Добавить»;
•Нажмите «OK».
7. Результат покажите преподавателю.Упражнение 5. Консолидация данных по категориям
В данном случае не обязательным является одинаковое расположение данных в сводимых областях, но обязательно должны иметь одинаковые имена – категории сводимых диапазонов.
1. Заполните таблицу по предложенному образцу в первой книге, выполните вычисления графы «к выдаче».
2. Создайте вторую книгу, выполнив команду «Файл – Создать» и в ней заполните следующую таблицу, выполнив вычисления столбца «к выдаче».
3. Создайте 3 книгу и выполните команду меню «Вид» - нажмите значок , скопируйте в эту книгу одну из таблиц, удалите все фамилии и цифровые данные в этой таблице.
4. Установите курсор Excel в А2 (в первую фамилию) и выполните команду меню «Данные – Консолидация».
5. В окне «Консолидации» установите флажок «Значения левого столбца»
6. Выделите все фамилии все цифровые данные таблицы 1 книги 1 и нажмите кнопку «Добавить.
7. Выделите все фамилии все цифровые данные таблицы 2 книги 2 и нажмите кнопку «Добавить.
8. Нажмите кнопку «ОК»,получаем следующее:
9. Все работы сохранить в собственной папке ПР6+Ф.И.О. Например, ПР6+Петрова О.В.
