ЗАТРАТЫ НА ПРОИЗВОДСТВО ПРОДУКЦИИ

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

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

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

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

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

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

Требуется:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Графа "Год" = МЕСЯЦ(Графа "Год")

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

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

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

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. Оценка преподавателем выполненной работы.

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

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

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

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

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

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

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

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

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

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

Требуется:

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

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

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

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

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