Здавалка
Главная | Обратная связь

Создание отчета сводной таблицы



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

1. В окне открытого листа выделите любую ячейку в диапазоне данных;

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

3. В поле «Укажите, куда следует поместить отчет сводной таблицы» выберите пункт:

- «на новый лист» – для размещения отчета сводной таблицы на дополнительном листе в книге;

- «на существующий лист» – для размещения отчета сводной таблицы в том же листе, где находятся и исходные данные.

 

 

Рис. 2.1. Меню «Вставка – «Сводная таблица».

 

 

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

5. Закройте окно кнопкой «ОК»;

 

 

 

Рис. 2.2. Создание сводной таблицы.

6. На листе (новом или существующем) появится пустая сводная таблица в виде контура макета, а справа листа откроется область «Список полей сводной таблицы» для создания отчета. В нем можно вставлять в сводную таблицу новые поля, создавать макет и настраивать отчет.

 

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

Таблица 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.6. Реализация товаров.

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

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

 

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

 

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

 

 

Рис. 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







©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.