Создание отчета сводной таблицы, используя таблицу данных.

Таблица 2.1.

Исходные данные

 

Дата операции	Наименование товара	Ед.изм.	Приход, кол-во
01.04.2012	Ручки шариковые	шт.
06.04.2012	Блокнот	шт.
15.04.2012	Ручки шариковые	шт.
05.05.2012	Дырокол	шт.
15.05.2012	Ножницы канцелярские	шт.
20.05.2012	Ручки шариковые	шт.

 

1. В окне открытого листа Excel выделите таблицу данных;

2. Перейдите к вкладке «Вставка» и в группе «Таблицы» раскройте меню кнопки «Сводная таблица»;

3. В списке команд выберите пункт «Сводная таблица»;

4. В окне «Создание сводной таблицы» в графе «Таблица или диапазон» отобразится имя используемой таблицы.

 

 

Рис. 2.3. Выбор диапазона

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

 

 

Рис. 2.4 Шаблон сводной таблицы

Примечание: В открывшемся окне вы видите поле отчета сводной таблицы и список полей сводной таблицы (справа).

6. Далее необходимо сформировать сводную таблицу. Для этого в списке полей сводной таблицы нужно выбрать необходимые параметры. В нашем примере нужно выбрать наименование товара и приход. Результат виден на рис. 2.5.

 

Рис. 2.5. Сводная таблица

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

 

 

Таблица 2.2.

Исходные данные

 

Дата	Группа	Наименование	Ед. изм.	Кол-во	Цена	Сумма
01.05.2011	Стройматериалы	Лист 3мм	тн.		25 000,00	?
01.05.2011	Стройматериалы	Труба стальная	пм.		2 000,00	?
05.05.2011	Стройматериалы	Кафель, 30х30	кв.м.		560,00	?
08.05.2012	Стройматериалы	Краска белая	бан.		300,00	?
15.05.2012	Бытовая химия	Порошок стир.	уп.		80,00	?
01.06.2012	Бытовая химия	Шампунь	шт.		105,00	?
20.08.2012	Мебель	Диван угл.	шт.		35 000,00	?
25.06.2012	Мебель	Стол кух.	шт.		15 000,00	?
01.07.2012	Продукты питания	Сахар	кг.		35,00	?
08.07.2012	Продукты питания	Мука	кг.		30,00	?
05.08.2012	Продукты питания	Картофель	кг.		20,00	?
28.08.2012	Продукты питания	Масло слив.	кг.		220,00	?

 

В связи с тем, что итоги должны быть рассчитаны за месяц и за год необходимо ввести дополнительные графы «Месяц» и «Год», рассчитываемые по формулам:

 

- Графа «Месяц» = МЕСЯЦ(Графа “Дата”);

- Графа «Год» = ГОД(графа “Дата”).

В результате в соответствующих графах должен отобразится номер месяца и год. Эти графы в дальнейшем будут использоваться для выборки и формирования итогов. В результате получим таблицу показанную на рис.

Рис. 2.6. Реализация товаров

Далее необходимо выделить исходные данные и сформировать шаблон для создания сводной таблицы на новом листе. Затем необходимо построить сводную таблицу, переместив наименование граф таблицы в соответствующие области шаблона сводной таблицы, как показано на рисунке 2.7.

Графы «Сумма по полю Кол-во» и «Сумма по полю Сумма» необходимо переименовать в «Количество» и «Сумма», соответственно.

Далее необходимо сформировать фильтр отчета сводной таблицы, который позволяет получать сводные данные по определенным показателям. Для этого в списке полей сводной таблицы необходимо переместить кнопки «Месяц», «Год», «Группа» в «Фильтр отчета». Результат отображен на рис. 2.8.

 

Рис. 2.7. Готовая сводная таблица

 

 

 

Рис. 2.8. Фильтр сводной таблицы

Например, необходимо определить выручку за май месяц 2011г. по группе стройматериалы. Для этого в верхней части сводной таблицы из списка необходимо выбрать соответствующие параметры. Результат представлен на рисунке 2.9. Нам видно, что за май месяц 2011 года было реализовано товара по группе «Стройматериалы» на сумму 894 800руб.

Рис. 2.9 Выручка по группе «Стройматериалы» за май 2011г.

 

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

Для этого:

1. На вкладке «Работа со сводными таблицами» необходимо перейти в меню «Параметры»;

2. Нажать на кнопку «Изменить источник данных»;

 

 

Рис. 2.10. Изменение источника данных в сводной таблице

 

3. В появившемся окне в области диапазона изменить номер последней строки диапазона на больший. В нашем примере 250 (рис.2.11).

 

Рис. 2.11. Изменение источника данных сводной таблицы

4. Завершить операцию нажатием клавиши Enter;

5. После ввода новых данных необходимо перейти на вкладку «Работа со сводными таблицами» в меню «Параметры» и нажать кнопку «Обновить».

На практике исходная таблица дополняется ежедневно, поэтому будет нецелесообразным менять диапазон исходных данных так часто. Поэтому, вместо того, чтобы изменять диапазон на 50 или 100 строк, можно увеличить его на 2000 строк. Это позволяет избежать возможных ошибок при работе со сводными таблицами, так как несвоевременное увеличение диапазона исходных данных может привести к принятию ошибочного решения, и привести к прямым убыткам.

 

 

Задания

Вариант 1

Таблица 2.3

Исходные данные

 

Дата	Группа	Наименование	Кол-во	Цена	Сумма
05.04.2010	Стройматериалы	Лист, 3 мм		15 000,00	?
09.04.2010	Стройматериалы	Труба, толщ 3 мм		500,00	?
10.04.2010	Стройматериалы	Кафель, 20х20		350,00	?
15.04.2010	Бытовая химия	Порошок		80,00	?
25.06.2010	Стройматериалы	Эмаль белая		240,00	?
28.06.2010	Стройматериалы	Плитка трот.		680,00	?
30.04.2011	Бытовая химия	Мыло хозяйств.		15,00	?
06.05.2011	Бытовая химия	Шампунь		75,00	?
08.06.2011	Бытовая химия	Отбеливатель		60,00	?
10.06.2011	Бытовая химия	Зубная паста		35,00	?
05.04.2012	Продукты питания	Масло подс.		70,00	?
08.04.2012	Продукты питания	Масло слив.		210,00	?
15.04.2012	Продукты питания	Мука		30,00	?
02.05.2012	Продукты питания	Соль		25,00	?
08.05.2012	Стройматериалы	Доска		800,00	?
25.05.2012	Стройматериалы	Гвозди		30,00	?
21.06.2012	Бытовая химия	Детское мыло	11,00	200,00	?

 

Необходимо:

1. Рассчитать графу «Сумма»;

2. Отсортировать исходные данные по следующим критериям: реализация продуктов питания за май 2012г.;

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

 

Вариант 2

Таблица 2.4

Производство продукции

№	Дата	Наименование	Кол-во	Цена	Сумма
	31.01.2012	Табуретка		450,00	?
	31.01.2012	Стол письменный		1 500,00	?
	31.01.2012	Диван		35 000,00	?
	28.02.2012	Табуретка		450,00	?
	28.02.2012	Стол письменный		1 500,00	?
	28.02.2012	Диван		35 000,00	?
	28.02.2012	Диван		25 000,00	?
	31.03.2012	Табуретка		400,00	?
	31.03.2012	Табуретка		450,00	?
	31.03.2012	Стол письменный		1600,00	?
	31.03.2012	Кресло		10 000,00	?
	31.03.2012	Стол кухонный		18 000,00	?
	31.03.2012	Кровать детская		12 000,00	?
	31.03.2012	Табуретка		450,00	?
	31.03.2012	Диван		35 000,00	?
	30.04.2012	Детская кровать		12 000,00	?
	30.04.2012	Журнальный стол		5 000,00	?
	30.04.2012	Диван		25 000,00	?
	30.04.2012	Табуретка		400,00	?
	31.05.2012	Диван		35 000,00	?
	31.05.2012	Кресло		10 000,00	?
	21.06.2012	Стол письменный		1 500,00	?
	21.06.2012	Стол кухонный		18 000,00	?

 

Необходимо:

1. Рассчитать графу «Сумма»;

2. Рассчитать итоговую строку;

3. Используя технологию сводных таблиц получить информацию об объемах производства продукции за I квартал;

 

Вариант 3

Таблица 2.5

Выдача ТМЦ со склада ООО «Электрика»

 

№	Дата	Ф.И.О. сотрудника	Наименование материала	Ед. изм.	Кол-во
	01.02.2012	Петрова Н.Г.	Гвозди	кг.
	01.02.2012	Петрова Н.Г.	Гвозди	кг.
	05.02.2012	Филипов А.О.	Розетки	шт.
	06.02.2012	Радионова Л.Н.	Патроны электрические	шт.
	06.02.2012	Радионова Л.Н.	Патроны электрические	шт.
	05.03.2012	Фурсенко А.Т.	Розетки настенные	шт.
	07.03.2012	Петрова Н.Г.	Краска белая	л.
	07.03.2012	Макаренко Г.Ш.	Краска белая	л.
	22.03.2012	Радионова Л.Н.	Доска	м3
	22.03.2012	Федоров К.Н.	Доска	м3
	28.03.2012	Федоров К.Н.	Стекло 5 мм	м3
	28.03.2012	Федоров К.Н.	Масло машинное	л.
	29.03.2012	Петрова Н.Г.	Гвозди	кг.
	29.03.2012	Филипов А.О.	Гвозди	кг.
	29.03.2012	Петрова Н.Г.	Розетки настенные	шт.
	29.03.2012	Радионова Л.Н.	Кабель электрический	м.
	01.04.2012	Макаренко Г.Ш.	Кабель электрический	м.
	05.04.2012	Макаренко Г.Ш.	Краска белая	л.
	06.04.2012	Филипов А.О.	Выключатель настенный	шт.
	06.04.2012	Филипов А.О.	Удлинитель 5 м	шт.
	07.04.2012	Радионова Л.Н.	Удлинитель 5 м	шт.
	22.05.2012	Фурсенко А.Т.	Розетки настенные	шт.
	28.05.2012	Фурсенко А.Т.	Гвозди	кг.
	01.06.2012	Петрова Н.Г.	Шурупы	кг.
	05.06.2012	Петрова Н.Г.	Шурупы	кг.
	29.06.2012	Радионова Л.Н.	Розетки настенные	шт.
	30.06.2012	Филипов А.О.	Выключатель настенный	шт.

 

Необходимо, используя технологию сводных таблиц, определить: кто (Ф.И.О.), какие материальные ценности, и в каком объеме получал со склада в течение определенного месяца.

 

Контрольные вопросы

1. Что такое сводная таблица? Опишите область ее применения.

2. Перечислите основные составляющие отчета сводной таблицы;

3. Как при создании сводной таблицы нужно учесть возможность увеличения объема исходных данных?

4. Как производится увеличение объема диапазона?

5. Как производится выбор критериев отбора сводной таблицы?

Лабораторная Работа № 3