Затраты на производство продукции

Элемент затрат	Сумма по годам, млн. руб.
Материальные затраты	29 000	35 000	40 000
Заработная плата	19 000	19 000	19 000
Отчисления с заработной платы	6 000	6 000	6 000
Амортизация основных средств	4 500	6 000	10 000
Прочие расходы	15 000	20 000	30 000
Полная себестоимость	73 500	86 000	105 000

 

Построить диаграммы:

1 Три круговых диаграммы, отражающих фактическую структуру затрат на производство продукции за каждый год.

2 Столбчатую диаграмму для сопоставления элементов затрат по годам.

Пример выполнения работы показан на рисунке.

 

Вариант 3

30 - 40 мин.

 

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

 

№ пп	Наименование функции	Значения х
	F1 = 2x^2+4x+5
	F2 = x+4x
	F3 = x+6
	F4 = x-4x+2
	F5 = 5x^3-4x

 

Вариант 4

30 - 60 мин.

 

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

 

№ пп.	Наименование показателя	Г О Д Ы
	Показатель 1	27,76	103,70	66,09	59,61	11,62	76,56
	Показатель 2	88,63	20,52	78,24	19,63	53,92	74,46
	Показатель 3	32,33	62,95	22,79	19,03	68,97	35,33
	Показатель 4	55,34	45,13	39,35	73,36	44,96	44,95
	Показатель 5	46,45	90,08	43,10	15,51	49,66	91,58

 

Требуется:

1. Рассчитать среднее значение по каждому показателю за период 1995-2000 г.

2. Рассчитать сумму значений показателей по каждому году и составить круговые диаграммы

3. Составить отдельные столбчатые диаграммы по каждому показателю.

4. Составить единую столбчатую диаграмму для визуального сопоставления изменения значения показателя по годам.

5. Составить графики изменения значений показателей по годам.

 

Занятие №5

Работа со сводными таблицами

 

Цель работы

 

Освоить технологию работы со сводными таблицами.

 

&	Теоретический материал

 

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

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

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

Например, имеется таблица с исходной информацией:

 

Дата операции	Наименование товара	Единица измерения	Приход, кол-во
01.04.2001	Ручки шариковые	шт.
01.04.2001	Блокноты	шт.
01.04.2001	Наборы канцелярские	шт.
05.04.2001	Ручки шариковые	шт.
05.04.2001	Блокноты	шт.
05.04.2001	Наборы канцелярские	шт.

 

После агрегирования по графе «Наименование товара» и применением операции суммирования для графы «Приход, кол-во» в рамках одной группы получим следующую таблицу:

 

Наименование товара	Единица измерения	Приход, кол-во
Ручки шариковые	шт.
Блокноты	шт.
Наборы канцелярские	шт.

 

Полученную в результате агрегирования таблицу значений называют сводной таблицей.

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

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

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

Для решения задач такого класса в Excel предусмотрена технология создания сводных таблиц и работы с ними.

Предположим, имеется следующая таблица:

 

 

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

1. Выделить таблицу исходных данных вместе с шапкой.

2. Д анные ® Свод н ая таблица ® … Далее ® Далее ® Готово…

На отдельном листе будет создан шаблон для построения сводных таблиц (см. рис. ниже).

3. Перетащить кнопки с наименованиями граф в соответствующий области шаблона:

- кнопку «Покупат…» необходимо перетащить (нажать, и удерживая кнопку мыши переместить курсор мыши в область строк шаблона таблицы).

- кнопку «Сумма з…» необходимо переместить в область данных.

 

 

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

 

Предположим, что некоторый магазин "ЛЮКС" реализует товары в розницу. Ежедневно по проданным товарам продавцы предоставляют данные о количестве проданных товаров в формате:

 

Дата	Группа	Наименование товара	Ед. изм.	Кол-во	Цена	Сумма
…	…	…	…	…	…	…
…	…	…	…	…	…	…

 

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

 

?	Пример выполнения работы

 

Исходные данные представлены в следующей таблице.

 

РЕАЛИЗАЦИЯ ТОВАРОВ

 

Дата	Группа	Наименование товара	Ед. изм.	Кол-во	Цена	Сумма
05.04.01	Стройматериалы	Лист 3 мм	тн		9 100	?
05.04.01	Стройматериалы	Труба Д50, толщ. 3 мм	пм			?
05.04.01	Стройматериалы	Кафель белый 20 х 20 М500	тн			?
05.04.01	Стройматериалы	Краска масляная белая	бан.			?
05.04.01	Стройматериалы	Эмаль белая	бан.			?
05.04.01	Стройматериалы	Кафель для пола 20х20	м2			?
05.04.01	Бытовая химия	Порошок стиральный Tide	пач			?
06.05.01	Бытовая химия	Мыло туалетное	шт			?
06.05.01	Бытовая химия	Комет-гель	шт			?
06.05.01	Бытовая химия	Шампунь	шт			?
06.05.01	Продукты питания	Сахар-песок	кг			?
06.05.01	Продукты питания	Масло сливочное	кг			?

 

Графа «Сумма» должна быть рассчитана по формуле: Сумма = Кол-во * Цена.

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

 

Графа "Месяц" = МЕСЯЦ(Графа "Дата")

Графа "Год" = ГОД(Графа "Год")

 

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

 

Теперь необходимо выделить таблицу и активировать опцию Д анные ® Свод н ая таблица … ® Далее ® Далее ® Готово. Получаем на отдельном листе шаблон создания сводной таблицы.

 

 

Далее необходимо построить сводную таблицу, переместив наименование граф таблицы в соответствующие области шаблона сводной таблицы:

1. Переместить " Наимено …" в секцию " Перетащите сюда поля строк ".

 

 

2. Переместить " Ед. изм. " в область строк таблицы.

3. Переместить последовательно " Кол-во " и " Сумма " в область данных таблицы.

 

 

4. Переместить образовавшуюся графу «Данные» в область столбцов таблицы как это показано на рисунке:

 

 

В результате получим таблицу:

 

 

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

1. Перейти на любую ячейку графы " Наименование товара ".

2. Вызвать контекстное меню нажатием правой кнопки мыши и выбрать опцию параметры поля как это показано на рисунке:

 

 

3. В появившемся диалоге необходимо выбрать переключатель «Нет» в информационной группе «Итоги».

 

 

® Нажать Ok

 

В результате убираются строковые итоги после каждого наименование. Получаем следующий вид экрана:

 

 

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

 

 

Далее необходимо переместить кнопки «Мес», «Год», «Группа» в
область полей страниц. Получим:

 

 

Таким образом, получается сводная таблица, позволяющая получать сводные данные по определенным показателям.

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

 

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

 

 

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

 

Присвоим области исходных данных имя «ИсходныеДанные»:

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

2. Вст а вка ® И мя …® П рисвоить … ® Ввести название диапазона: «ИсходныеДанные». …® Ok.

 

 

Рис. Выделение диапазона ячеек – исходных данных для сводной таблицы

 

Рис. Присвоение имени диапазону ячеек.

 

 

Теперь необходимо изменить параметры сводной таблицы: диапазон ячеек исходных данных, для чего необходимо:

1. Переместить курсор на одну из ячеек сводной таблицы.

2. Вызвать контекстное меню и активировать опцию «Мастер…».

 

 

3. В открывшемся диалоге необходимо переместиться на шаг 2 (один раз нажать кнопку «Назад») до места выбора диапазона.

4. Ввести имя диапазона ячеек с исходными данными как показано на рисунке:

 

 

5. Нажать «Готово».

 

Теперь сводная таблица будет брать исходные данные из диапазона ИсходныеДанные. При увеличении числа строк в таблице исходных данных необходимо будет увеличить размерность определенного именованного диапазона ИсходныеДанные: Вст а вка ® И мя ® П рисвоить… ® Выбрать диапазон ИсходныеДанные в списке ® После чего увеличить размер диапазона как показано на рисунке изменив номер последней строки диапазона на больший. Например, «15» заменить на «20».

 

 

 

В повседневной практике исходная таблица дополняется ежедневно, поэтому ежедневное изменение параметров диапазона представляется неудобным и нежелательным, особенно, если это будет производить недостаточно квалифицированный специалист. Поэтому при определении диапазона исходных данных необходимо указать значение последней строки диапазона, недостижимое в ближайшем будущем. Например, вместо того, чтобы увеличить диапазон на 5 строк (в результате замены 15 на 20) увеличить его на 2000 строк, введя вместо 15 значение 2000. Такая мера полностью себя оправдывает тем, что она позволяет избежать возможных ошибок, связанных с тем, что диапазон исходных данных не был своевременно расширен (а данные уже ушли к руководству и на их основе принято ошибочное решение, что в свою очередь привело к прямым убыткам для фирмы).

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

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

Результат предстает в виде автоматически сформированной новой сводной таблицы как это показано на рисунке:

 

 

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

 

Рис. Полученная в результате установки критериев отбора
сводная таблица.

è	Порядок выполнения работы

 

1. Изучение теоретического материала.

2. Выполнение вариантов заданий с помощью рассмотренных инструментов, средств, приемов и технологий

3. Составление отчета о проделанной работе. Отчет должен содержать следующие разделы:

- наименование работы;

- цель работы;

- пошаговое последовательное описание процесса выполнения варианта задания по видам выполняемых действий.

4. Результат выполнения варианта задания должен быть сохранен под именем ФИО_Работа№_Вариант№ (например, «ИвановНН_Работа4 _Вариант1.xls») на жесткий диск в папку «Мои документы\ИТ в экономике» и на дискету – в двух копиях (две копии одной и той же информации в разных папках на дискете).

5. Представление результатов выполнения работы (отчета и файлов на дискете) для проверки преподавателю.

6. Защита выполненной работы: ответ на контрольные вопросы к теоретическому материалу занятия и ответ на замечания преподавателя по выполненной работе.

7. Оценка преподавателем выполненной работы.

 

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

 

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

2. Опишите порядок создания сводной таблицы.

3. Каким образом можно изменить формат полей сводной таблицы? Как возможно изменить наименование графы сводной таблицы?

4. Что необходимо сделать, чтобы обеспечить достоверность данных сводной таблицы?

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

6. Почему при определении диапазона исходных данных сводной таблицы для количества строк необходимо задавать значение, намного превышающее текущую размерность данных? Как производится увеличение размерности диапазона по числу строк?

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

8. Опишите порядок выполнения работы. Как должна быть оформлена работа? Как необходимо представлять результаты проделанной работы?

 

Ä	Варианты заданий

 

 

Вариант 1

30 - 50 мин.

 

В представленной ниже таблице приведена информация о реализации товаров.

 

РЕАЛИЗАЦИЯ ТОВАРОВ

 

Дата	Группа	Наименование товара	Ед. изм.	Кол-во	Цена, руб	Сумма
05.04.2001	Стройматериалы	Лист 3 мм	тн		9 100	?
09.04.2001	Стройматериалы	Труба Д50, толщ. 3 мм	пм			?
10.04.2001	Стройматериалы	Кафель белый 20 х 20 М500	тн			?
15.04.2001	Стройматериалы	Краска масляная белая	бан.			?
25.06.2001	Стройматериалы	Эмаль белая	бан.			?
28.04.2001	Стройматериалы	Кафель для пола 20х20	м2			?
30.04.2001	Бытовая химия	Порошок стиральный Tide	пач			?
06.05.2001	Бытовая химия	Мыло туалетное	шт.			?
08.06.2001	Бытовая химия	Комет-гель	шт.			?
10.05.2001	Бытовая химия	Шампунь	шт.			?
15.05.2001	Продукты питания	Сахар-песок	кг			?
16.05.2001	Продукты питания	Масло сливочное	кг			?
02.06.2001	Стройматериалы	Труба Д50, толщ. 3 мм	пм			?
05.06.2001	Стройматериалы	Краска масляная белая	бан.			?
16.05.2001	Продукты питания	Масло сливочное	кг			?
02.06.2001	Стройматериалы	Труба Д50, толщ. 3 мм	пм			?
05.06.2001	Стройматериалы	Краска масляная белая	бан.			?
11.06.2001	Стройматериалы	Кафель белый 20 х 20 М500	тн			?
11.06.2001	Бытовая химия	Шампунь	шт.			?
13.06.2001	Бытовая химия	Порошок стиральный Tide	пач			?
15.06.2001	Стройматериалы	Лист 3 мм	тн		9 100	?
15.06.2001	Бытовая химия	Мыло туалетное	шт.			?
16.06.2001	Продукты питания	Сахар-песок	кг			?
11.07.2001	Стройматериалы	Кафель белый 20 х 20 М500	тн			?

 

Требуется:

- Рассчитать недостающие данные таблицы (?).

- Отсортировать исходные данные по дате.

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

 

Вариант 2

30 - 50 мин.

 

В представленной ниже таблице приведена информация об отгрузке товаров покупателям.

ОТГРУЗКА ТОВАРОВ ПОКУПАТЕЛЯМ

 

Дата	Покупатель	Наименование товара	Ед. изм.	Кол-во	Цена, руб	Сумма
01.04.2001	ООО «Омега»	Кирпич	тыс. шт.			?
05.04.2001	ЗАО «Промстрой»	Кафель белый 20 х 20	тн			?
10.04.2001	ОАО «Полигон»	Кафель белый 20 х 20	тн			?
11.04.2001	ОАО «Лига»	Доска обрезная	м3		2 100	?
11.04.2001	ОАО «Лига»	Кирпич	тыс. шт.			?
15.05.2001	ЗАО «Промстрой»	Доска обрезная	м3		2 120	?
01.06.2001	ООО «Омега»	Кирпич	тыс. шт.			?
05.06.2001	ЗАО «Промстрой»	Кафель белый 20 х 20	тн			?
10.06.2001	ОАО «Полигон»	Кафель белый 20 х 20	тн			?
11.06.2001	ОАО «Лига»	Доска обрезная	м3		2 000	?
15.06.2001	ОАО «Лига»	Кирпич	тыс. шт.			?
15.06.2001	ЗАО «Промстрой»	Доска обрезная	м3		2 000	?