Практическая работа 6. Создание сводных таблиц и консолидация данных


Задание 1. Создание сводных таблиц через объединение листов. Требуется подсчитать общий заработок всех рабочих отдела за два дня.
1. Заполните ячейки таблицы по образцу (данные о рабочих за первый день):

Рисунок 1.1 – Заработок рабочих за 1 день
2. Установите следующие параметры таблицы:
  • Ширина столбцов по порядку: 15; 13; 11,57; 10. Для этого ЩПКМ по заголовку столбца А, в контекстном меню выберите пункт «Ширина столбца…», в появившемся диалоговом окне поставьте цифру 15. Аналогично определите ширину столбцов В, С и D;
  • Шапка таблицы: «Выравнивание» – переносить по словам, выравнивание по центру;«Шрифт» – полужирный;
  • В ячейках с цифрами и «–» установите «Выравнивание» по центру;
  • Графу «Заработок» посчитайте, используя расчётные формулы. В данном примере это =В2*С2;
  • Строку «Итого» заполните, используя значок на панели инструментов.
3. Предложенную таблицу скопируйте на второй лист:
  • Выделите ячейку А1 ЩЛКМ по ней;
  • Не убирая курсор с ячейки, зажмите ЛКМ и протяните курсор от ячейкиА1 до D8;
  • На панели инструментов ЩЛКМ по значку «Копировать»;
  • Щёлкните по ярлычку «Лист2»;
  • Установите курсор в ячейку А1 и ЩЛКМ по значку «Вставить».
4. Измените числовые данные в столбце «Количество отработанный часов» (кроме строки «Итого») – данные о рабочих за второй день.

Рисунок 1.2 – Заработок рабочих за 2 день
5. Создайте итоговую таблицу №3 от таблиц №1 и №2.
  • Скопируйте таблицу №1 на Лист3 (см. п2) и удалите из полученной третьей таблицы все числовые данные (при удалении используйте клавишу Delete);
  • Сосчитайте числовое значение ячейки В2 (Лист3);
  • Выделите ЩЛКМ ячейку В2 (Лист3);
  • При помощи клавиатуры напишите значок «=»;
  • ЩЛКМ по ярлыку Лист1;
  • В открывшемся листе ЩЛКМ по ячейке В2 (Лист1);
  • Не уходя с Листа1, наберите значок «+» с клавиатуры;
  • ЩЛКМ по ярлыку Лист2;
  • ЩЛКМ по ячейке В2 (Лист2);
  • Не переключаясь с Листа2, нажмите Enter.
      В результате в ячейке В2 (Лист3) – появится суммарное значение количества отработанных часов Кузнецова А.Л. за два дня работы


Рисунок 1.3 – Итоговый заработок Кузнецова
6. Скопируйте формулу во все ячейки диапазона, для этого за маркер заполнения протяните до ячейки D2 и вниз до D7. Сводная таблица должна принять следующий вид:

Рисунок 1.4 – Итоговая таблица
7. Выполненную работу покажите преподавателю.
Задание 2. Создание сводных таблиц через объединение книг. Условие задания, п.1,2,3 см. в №1.
1. Создайте новую книгу: либо ЩЛКМ по кнопке «Создать» на панели инструментов, либо выполните команду меню «Файл» - «Создать».
2. Расположите книги рядом:
  • Выполните команду меню «Вид» - нажмите значок ;
  • В результате окно Excel разделится на 2 части: Книга1 и Книга2.

Рисунок 2.1 – Расположенные рядом книги
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.

Рисунок 2.2 – Суммарное количество отработанных часов Кузнецова
5. В результате в ячейке В2 – Книга2, Лист1 – появится суммарное значение количества отработанных часов Кузнецова А.Л. за два дня работы.Проделайте то же самое для всех оставшихся ячеек таблицы №3 с числовыми значениями (кроме столбика С).
6. Цифровые данные столбика С (включая ячейку С8) скопируйте в таблицу №3 из таблицы №2.

Рисунок 2.3 – Итоговая сводная таблица через объединение
7. Выполненную работу покажите преподавателю.
Задание 3. Создание сводных таблиц через мастер сводных таблиц. Для трёх первых рабочих из №1 подсчитать суммарное количество отработанных часов и общую сумму заработка за два дня работы.
1. Заполните ячейки таблицы по образцу:

Рисунок 3.1 – Заработок рабочих
  • Ширину столбцов, шапку таблицы и выравнивание оформите так же, как в №2; диапазон ячеек A1:D7. Последний столбец посчитайте, используя расчётные формулы.
2. Создание сводной таблицы.
  • Выделите полученную таблицу;
  • Выполните команду меню «Вставка» - «Сводная таблица»;

Рисунок 3.2 – Создание сводной таблицы
  • Нажмите кнопку «ОК»;
3. В результате на новом листе появится заготовка будущей сводной таблицы и панель инструментов «Сводные таблицы», на которой расположены названия из шапки вашей таблицы.

Рисунок 3.3 – Панель Сводная таблица
  • Перетащите Ф.И.О. в «Название столбцов», стоимость – в «Название строк», заработок и количество – в «Значения», (причём, когда будете переносить заработок, тащите его на надпись «Сумма по полю Количество отработанных часов»или в поле с его значениями);
  • Закройте панель «Сводные таблицы».
  • Вы получили сводную таблицу:

Рисунок 3.4 – Сводная таблица
4. Результат покажите преподавателю.
Задание 4. Создание сводных таблиц через консолидацию данных.
      При консолидации данных над значениями, расположенными в различных областях, могут быть выполнены те же операции, что и при автоматическом подведением итогов, например вычисление суммы, произведения, нахождение максимального, минимального, среднего значения.
      Источники консолидируемых данных могут находиться на одном листе, на нескольких листах, в разных книгах и или в разных местах диска.
Консолидация данных по расположению – используется для однотипных данных, упорядоченных одинаковым образом. Консолидация данных по категориям – используется для однотипных данных, организованных по разному.
      Консолидация по расположению.
Для всех рабочих отдела сосчитать суммарное количество всех изготовленных ими деталей, бракованных деталей и деталей стандартного качества.
1. Заполните ячейки таблицы по образцу:
  • Ширину столбцов, шапку таблицы и выравнивание оформите так же, как в №2. В данной таблице рассматривается диапазон ячеек A1:D7. Последний столбец посчитайте, используя расчётные формулы.

Рисунок 4.1 – Таблица изготовленных деталей 1
2. Созданную таблицу скопируйте на второй лист и измените числовые данные в графе «Количество изготовленных деталей» и«Брак».

Рисунок 4.2 – Таблица изготовленных деталей 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» и нажмите кнопку «Добавить»;

Рисунок 4.3 – Консолидация
  • Нажмите «OK».

Рисунок 4.5 – Итоговая таблица
7. Результат покажите преподавателю.
Задание 5. Консолидация данных по категориям
      В данном случае не обязательным является одинаковое расположение данных в сводимых областях, но обязательно должны иметь одинаковые имена – категории сводимых диапазонов.
1. Заполните таблицу по предложенному образцу в первой книге, выполните вычисления графы «к выдаче».

Рисунок 5.1 – Таблица Сумма к выдаче 1
2. Создайте вторую книгу, выполнив команду «Файл – Создать» и в ней заполните следующую таблицу, выполнив вычисления столбца «к выдаче».

Рисунок 5.2 – Таблица Сумма к выдаче 2
3. Создайте 3 книгу и выполните команду меню «Вид» - нажмите значок , скопируйте в эту книгу одну из таблиц, удалите все фамилии и цифровые данные в этой таблице.
4. Установите курсор Excel в А2 (в первую фамилию) и выполните команду меню «Данные – Консолидация».
5. В окне «Консолидации» установите флажок «Значения левого столбца»
6. Выделите все фамилии все цифровые данные таблицы 1 книги 1 и нажмите кнопку «Добавить.
7. Выделите все фамилии все цифровые данные таблицы 2 книги 2 и нажмите кнопку «Добавить.

Рисунок 5.3 – Консолидация
8. Нажмите кнопку «ОК»,получаем следующее:

Рисунок 5.4 – Итоговая таблица

9. Все работы сохранить в собственной папке ПР6+Ф.И.О. Например, ПР6+Петрова О.В.