Файл: Методичка Excel.docx

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

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

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

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

Рис. 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 строк. Это позволяет избежать возможных ошибок при работе со сводными таблицами, так как несвоевременное увеличение диапазона исходных данных может привести к принятию ошибочного решения, и привести к прямым убыткам.

2.2 Задания

Вариант 1

Таблица 2.3

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

Необходимо:

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

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

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

Вариант 2

Таблица 2.4

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

Необходимо:

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

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

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

Вариант 3

Таблица 2.5