00.2 Уч-мет пос МПиС d4 (1). Лабораторная работа Формирование статистической таблицы с возможностью сортировки, фильтрации и группировки данных 4

• Название: Лабораторная работа Формирование статистической таблицы с возможностью сортировки, фильтрации и группировки данных 4
• Дата: 09.10.2022
• Размер: 2.1 Mb.
• Имя файла: 00.2 Уч-мет пос МПиС d4 (1).docx
• Тип: Лабораторная работа; #722618
• страница: 2 из 9

Лабораторная работа № 2.
Сводные таблицы, консолидация

Цель лабораторной работы – научиться использовать в табличном редакторе MSExcel консолидацию и сводные таблицы. Разработать электронный вариант, предложенный в задании, таблицы с использованием функций системы.
Первая часть: Консолидация данных

Под термином консолидация подразумевается ряд стандартных операций с несколькими рабочими таблицами и рабочими книгами. В некоторых случаях консолидация может включать в себя создание связанных формул. Основной фактор, влияющий на консолидацию данных – это способ размещения информации в рабочих таблицах. Если размещение информации во всех таблицах одинаково, то говорят о консолидации по позиции. В том случае, когда размещение информации не идентично, но достаточно похоже, то можно объединить данные по заголовкам строк и/или столбцов. Такая консолидация называется консолидацией по категориям. Если же рабочие таблицы имеют мало общего друг с другом, то необходимо отредактировать листы, чтобы они стали единообразными.
Задание 1. Пусть на разных листах рабочей таблицы представлены отчеты о продаже товаров за три месяца различными филиалами фирмы. Необходимо построить диаграмму, показывающую изменение объема продаж изделий фирмой по месяцам.

Филиал № 1

Назв. товара	Янв	Фев	Мар
А-995	110	10	20
В-123	10	10	20
А143	20	20	40
В-123	30	30	60
С-070	40	40	80
Д-060	60	60	120
Е-130	50	50	100
Ф-270	70	70	140
T-234	120	20	20
M-235	11	11	24

Филиал № 2

Назв. товара	Янв	Фев	Мар
Т-234	10	10	20
В-123	10	10	20
Р-234	20	20	20
А143	20	40	40
В-123	30	30	60
С-070	40	40	80
Д-060	60	60	120
Е-130	50	20	100
Ф-270	70	70	140
У-111	40	40	45
К-254	30	20	45

Филиал № 3

Назв. товара	Янв	Фев	Мар
А-995	10	10	20
В-123	10	10	20
А143	20	20	40
Р-234	100	100	100
В-123	30	30	60
С-070	40	40	80
Д-060	60	60	120
Е-130	50	50	100
Ф-270	70	70	140
К-254	10	10	10

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

Для выполнения данного задания необходимо:

1. 
   Создать рабочие таблицы на различных листах рабочей книги (например, на листах с первого по третий). Часть записей скопировать из данного документа. Добавить не менее пяти записей в каждую рабочую таблицу так, чтобы в таблицах были записи с одинаковым названием товара.
   
2. 
   Создать новую рабочую книгу (выбрать новый рабочий лист), где должны размещаться результаты консолидации. Выполнить команду Данные  Консолидация.
   
3. 
   Задание параметров для диалогового окна Консолидация:
   
   1. 
      В поле Функция указать функцию Сумма, которая показывает тип объединения данных;
      
   2. 
      В поле Ссылка ввести ссылку на диапазон первой рабочей таблицы, которые должны быть консолидированы. Если нужная книга закрыта, щелкнуть по кнопке Обзор, чтобы найти нужный файл на диске. Ссылка может задавать диапазон больший, по числу строк, чем нужно консолидировать, но в случае добавления новых строк, параметры консолидации не нужно будет изменять. Когда в поле Ссылка будет введена нужная ссылка, щелкните по кнопке Добавить, чтобы добавить к списку диапазонов;
      
   3. 
      Вести ссылку на диапазон второй рабочей таблицы и добавьте ее к списку диапазонов. Выполнить указанное действие для остальных диапазонов консолидации;
      
   4. 
      Так как способы размещения информации в рабочих таблицах различны, установить опции Подписи верхней строки и Значения левого столбца. В результате MSExcel будет подбирать данные по заголовкам;
      
   5. 
      Для того, чтобы консолидация была динамической, необходимо установить опцию Создавать связи с исходными данными и нажмем кнопку ОК. В результате MSExcel создаст структуру, содержащую внешние ссылки.
      
4. 
   Построить требуемую диаграмму.
   

Задание 2. На одном из листов рядом с данной таблицей (например, на первом) создать копию. Изменить данные на копии таблицы. Получились две одинаковые таблицы с разными данными. Провести консолидацию по расположению. Недостатком этого метода является то, что консолидация получается нединамической (статическая консолидация).

Поставить курсор в ячейку, где будет начинаться консолидированная таблица (например, А20). Для этого воспользоваться командой Данные  Консолидация. В окне Функция выбрать Среднее, в окне Ссылка выбрать диапазон первой таблицы, нажать на кнопку Добавить, повторить тоже со второй таблицей. Установить опции Подписи верхней строки и Значения левого столбца. Нажать кнопку ОК. Полученная таблица отображает среднее количество проданных изделий на фирме по месяцам.
Задание 3. Провести консолидацию
1. Вариант. Средняя зарплата

Заработная плата за Январь							Фамилия__Зарплата'>Заработная плата за Февраль
Фамилия	Зарплата	Подоходный налог	Сумма к выдаче			Фамилия		Зарплата	Подоходный налог	Сумма к выдаче
Скворцов	2000	260	1740			Скворцов		3000	390	2610
Петухов	1500	195	1305			Петухов		2500	325	2175
Воробьев	3000	390	2610			Воробьев		4200	546	3654
Синица	1800	234	1566			Синица		2500	325	2175
Итого	8300	1079	7221			Итого		12200	1586	10614

2. Вариант. Максимальная стипендия

Стипендия за Январь					Стипендия за Февраль
Фамилия	Стипен­дия	Повыше­ние	Сумма к выдаче		Фамилия	Стипен­дия	Повыше­ние	Сумма к выдаче
Скворцов	2000	15%	2300		Скворцов	3000	10%	3300
Петухов	1500	15%	1725		Петухов	2500	0%	2500
Воробьев	3000	0%	3000		Воробьев	4200	15%	4830
Синица	1800	10%	1980		Синица	2500	10%	2750
Итого					Итого

3. Вариант. Общая зарплата

Заработная плата за Январь					Заработная плат аза Февраль
Фамилия	Зарплата	Подоход­ныйналог	Суммаквыдаче		Фамилия	Зарплата	Подоход­ныйналог	Премия	Суммаквыдаче
Скворец	2000	260	1740		Скворец	3000	390	1000	2610
Петухов	1500	195	1305		Петухов	2500	325	1500	2175
Воробьев	3000	390	2610		Воробьев	4200	546	2000	3654
Орел	1800	234	1566		Орел	2500	325	1000	2175
Итого	8300	1079	7221		Итого	14200	1846	7000	12354

Пиктограмма, отвечающая за вызов панели формул в MSWord 2007 и старше расположена на закладке Вставка. Пример представлен на рис. 4.


Рис. 4. Панель выбора пиктограммы вызова консолидации в MSOffice 2007

Вторая часть: Сводные таблицы

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

Сводная таблица может быть создана на основании данных находящихся:

• 
  в списке или базе данных MSExcel;
  
• 
  во внешнем источнике данных;
  
• 
  в нескольких диапазонах консолидации;
  
• 
  в другой сводной таблице.
  

Каждая сводная таблица состоит из 4 областей: страница, строка, столбец, данные (рис. 5).

Рис. 5. Мастер сводных таблиц

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

• 
  Cтрока. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков строк в сводной таблице. Если в эту область помещено одно поле, то количество строк в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.
  
• 
  Столбец. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков столбцов в сводной таблице. Если в эту область помещено одно поле, то количество столбцов в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.
  
• 
  Данные. Значения полей, помещенных в эту область, используются для заполнения ячеек сводной таблицы итоговыми данными (суммирование, подсчет количества, вычисление среднего значения и т. д.).
  
• 
  Страница. Уникальные значения полей, помещенных в эту область, и элемент «все» используются для построения раскрывающихся списков. В поле страницы можно выбрать только одно значение в каждом из списков. В области данных будут отображены итоговые данные, для выбранного значения. Использование этого элемента сводной таблицы позволяет, в некоторой мере, реализовать отображение трехмерной таблицы.
  

Задание 1. На основании следующей таблицы

Менеджер	МЕСЯЦ	Продукты	Доход	Расход	Прибыль	Регион
Иванов	янв.	мясо	100,00	50,00		Страны СНГ
Иванов	фев.	мясо	100,00	50,00		Россия
Иванов	фев.	мясо	100,00	50,00		Россия
Иванов	апр.	мясо	100,00	50,00		Россия
Иванов	апр.	мясо	100,00	50,00		Россия
Петров	янв.	мясо	100,00	50,00		Страны СНГ
Петров	фев.	мясо	100,00	50,00		Страны СНГ
Петров	фев.	мясо	100,00	50,00		Страны СНГ
Петров	апр.	мясо	100,00	50,00		Страны СНГ
Петров	апр.	мясо	100,00	50,00		Страны СНГ
Сидоров	май	рыба	100,00	50,00		Страны СНГ
Сидоров	янв.	рыба	100,00	50,00		Россия
Иванов	фев.	рыба	100,00	50,00		Россия
Иванов	март	молоко	200,00	20,00		Россия
Петров	март	молоко	300,00	30,00		Страны СНГ
Сидоров	март	молоко	150,00	100,00		Страны СНГ

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

1. 
   Скопировать в буфер обмена таблицу в редакторе MSWord.
   
2. 
   Вставить таблицу на рабочий лист MSExcel лист и оформить данные в виде списка.
   
3. 
   Рассчитать значение поля Прибыль, записав соответствующую формулу.
   
4. 
   Сделать текущей любую ячейку построенного списка.
   
5. 
   Выполнить команды Данные и Сводная таблица.
   
6. 
   Установить флажок ‑ В списке или базе данных Microsoft Excel;
   
7. 
   Указать диапазон, содержащий построенный список. Если список был построен правильно, нужный диапазон будет выбран автоматически.
   
8. 
   Перетащить кнопки Продукция и Менеджер в область Строка. При этом важен порядок перетаскивания – поле Менеджер будет вложенным по отношению к полю Продукция. Затем в область Столбец перетащить кнопку Месяц и в область страниц – кнопку Регион. В область данных перетащить кнопку Прибыль.
   
9. 
   Указать место размещения сводной таблицы.
   

Построенная сводная таблица будет иметь следующий вид (рис. 6).


Рис. 6. Пример готовой сводной таблицы

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

1. 
   Скопировать сводную таблицу Задания 1 на другой лист или повторить процесс ее построения. Можно также создать копию листа со сводной таблицей.
   
2. 
   Отметить диапазон С4:E15. Для этого достаточно сделать текущей ячейку С4 (выделится столбец сводной таблицы за январь месяц) и нажав клавишу Shift щелкнуть по ячейке E15.
   
3. 
   Выполнить команды Данные  Группа и структура  Группировать. В поле столбца появиться новое поле Месяц 2 и в сводную таблицу добавится строка, в которой для выделенных трех столбцов присвоится название Группа 1.
   
4. 
   Выполнить аналогичные действия для столбцов сводной таблицы за апрель и май месяцы. Для этих столбцов должно появиться название Группа 2.
   
5. 
   Удалить поле месяц. Для этого необходимо вызвать контекстное меню или перетащить его из области сводной таблицы.
   
6. 
   Исправить название Месяц 2 на Квартал, Группа 1 - на Первый, Группа 2 - на Второй.
   

Полученная таблица должна иметь следующий вид (рис. 7).


Рис. 7. Пример переработанной сводной таблицы

Задание 1С. На основании построенного списка в задании 1 выполнить один из предложенных вариантов:

1. 
   Построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции в разрезе регионов.
   
2. 
   Построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по регионам.
   
3. 
   Построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции по регионам.
   
4. 
   Построить таблицу, показывающую объем прибыли по регионам.
   

Пиктограмма, отвечающая за вызов мастера консолидации в MSWord 2007 и более поздние версии расположена на закладке Данные.