Методические указания к выполнению лабораторной работы «Формирование отчета о структуре продаж по данным программы «1с-бухгалтерия» для дисциплин «Организация и методика аудита», «1с-бухгалтерия» (для (стр. 4 из 6)

Выполним двойной щелчок левой кнопкой мыши на поле «Кол-во» в области данных. Так мы раскроем окно операций над этим полем: сумма, количество значений, среднее, максимум, минимум (всего 11 вариантов). Нужно убедиться, что для этого поля выбрана операция суммирования. Кроме того, в области Имя можно изменить заголовок, который Excel поставит в сводном отчете. Озаглавим поле «Кол.,шт.» и нажмем ОК. Окончательный вид сводного отчета показан на рис. 10.

Рис. 9— Формирование структуры отчета сводной таблицы

Рис. 10 — Сводный отчет о структуре продаж

3.7. Использование деловой графики для анализа структуры продаж

Для иллюстрации полученных результатов удобно использовать деловую графику Excel. Рассмотрим этот процесс более подробно. В Excel встроены 14 стандартных и 20 нестандартных типов диаграмм. Каждому типу соответствует несколько способов его визуального представления. Для быстрого построения диаграмм служит Мастер диаграмм. Он предполагает построение диаграммы за четыре шага, но в принципе достаточно пройти всего один. Все, что требуется — определить диапазон данных и выбрать тип графика. На остальных шагах уточняются только параметры диаграммы. Покажем работу с Мастером на примере.

Установив курсор в область данных сводной таблицы, выполним команду «Вставка4Диаграмма». Откроется окно Мастера диаграмм (рис. 11). Из перечня графиков в левой части окна Мастера выберем пункт «Гистограмма». В правой части окна выберем вид гистограммы - объемный вариант гистограммы с накоплением. Щелкнем мышью по выбранному виду, и фон его окна сменится со светлого на темный.

Рис. 11 — Определение типа диаграммы

Для стандартных типов диаграмм доступен режим быстрого просмотра результатов. Если нажать и удерживать левую кнопку мыши на кнопке «Просмотр результата» в нижней части окна Мастера, вместо панели «Вид» откроется панель «Образец» (рис. 12). В этой панели в уменьшенном масштабе будет показан пример диаграммы, построенной на данных таблицы пользователя. Если настройки по умолчанию вас устраивают — щелкните мышью по кнопке «Готово», и вы получите готовый график. Мы пройдем четыре шага Мастера до конца, поэтому щелкнем по кнопке «Далее».

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

Рис. 12 — Окно просмотра образца диаграммы

Колонка диаграммы с общим итогом по продажам нам не нужна, поэтому мы выделим диапазон ячеек A2:D4 (с заголовками строк и столбцов, без итогов). Excel преобразует относительный адрес диапазона в абсолютный и добавит имя листа: =Лист1!$A$2:$D$4, — ведь диаграмму можно размещать как на текущем, так и на новом листе книги. Образец диаграммы показан на верхней панели окна Мастера (рис. 13).

В этом же окне можно уточнить, как располагаются ряды данных: в строках или в столбцах. Информация о покупателях содержится в строках нашей сводной таблицы. В диаграмме на рис. 13 два ряда данных — для покупателей «Дельта» и «Подарок».

Рис. 13 — Определения диапазона данных диаграммы

Можно указать, что ряды данных расположены в столбцах. Тогда в столбцах диаграммы будут отражены данные по товарам, а количество столбцов будет соответствовать количеству покупателей (рис. 14). На закладке «Ряд» этого окна Мастера можно уточнить состав отображаемых рядов, их наименования и диапазоны выводимых значений или нажать «Готово». Щелкнем по кнопке «Далее».

Рис. 14 — Изменение характера расположения рядов данных

На третьем шаге можно видоизменить внешний вид диаграммы. На закладке «Заголовки» этого окна (рис. 15) введем наименование диаграммы и подписи к ее осям. Введенные надписи немедленно появятся на образце нашего графика.

На остальных закладках этого окна можно уточнить другие параметры оформления графика. Например, на закладке «Легенда» можно указать место расположения легенды рядов: справа от диаграммы (рис. 16), вверху, внизу и т.д.

На последнем, четвертом шаге (рис. 16) выбираем место расположения диаграммы: на новом листе книги Excel или на имеющемся (текущем) листе. Наша сводная таблица небольшая, диаграмма вполне поместится рядом с ней[7]. Щелкнем по кнопке «Готово», и диаграмма будет вставлена в текущий лист (рис. 17).

Рис. 15 — Определение заголовков диаграммы

Рис. 16 — Выбор местоположения диаграммы

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

На рис. 17 видно, что диаграмма заключена в рамку, и в окне Excel появилась панель инструментов «Диаграммы». Подведите курсор мыши к диаграмме. Когда курсор примет вид крестика со стрелками, диаграмму можно будет перетащить в другое место листа. Если подвести курсор к границе окна диаграммы, он примет вид двойной стрелки. Перетаскивая границу окна, при нажатой левой кнопке мыши, можно изменить масштаб диаграммы. С помощью панели инструментов «Диаграммы» можно выбрать другой тип графика, изменить ряды данных, шрифты подписей и прочее. Кнопкой Delete можно удалить диаграмму. Не бойтесь экспериментировать: любое изменение, и даже удаление диаграммы не затрагивает исходные данные, на которых она построена. Диаграмму всегда можно будет построить заново.

3.8. Организация вычислений в сводном отчете

3.8.1. Изменение структуры сводного отчета

С помощью построенной таблицы можно проанализировать продажи в самых различных аспектах: по датам, покупателям, партиям, вначале датам, затем по накладным… Главное — не переусердствовать в выборе вариантов анализа. Здравый смысл должен сдерживать буйство фантазии. Для перестройки таблицы необязательно каждый раз строить ее заново[8]. Группировку данных можно менять, перетаскивая мышью заголовки полей. Например, перестроим сводную таблицу, показанную на рис. 10 так, чтобы покупатели располагались по столбцам, а купленные ими товары — по строкам. Нажмем левую кнопку мыши на поле «Покупатель» (рис. 18и перетащим его в верхнюю часть окна Excel, в область строк. Когда курсор мыши примет вид стрелки с фрагментом ячеек таблицы, отпустим кнопку. Перетаскиваемое поле «приклеится» к полю «Товар». Выделим и перетащим мышью поле «Товар» к левому краю окна Excel, столбцы сводной таблицы поменяются местами со строками. Два взмаха мышью – и сводная таблица транспонирована.

Сводная таблица — это внутренний объект Excel, который занимает определенное (часто довольно большое) место в оперативной памяти и на жестком диске. Мы преобразовываем не весь объект «сводная таблица», а только отчет сводной таблицы, своего рода «верхушку айсберга». Перетаскивая мышью поля, группируя и разгруппировывая строки и столбцы, мы всего лишь изменяем способ представления уже рассчитанных данных. При этом сам объект «сводная таблица» не изменяется и заново не пересчитывается. Если сводная таблица большая, а компьютер недостаточно мощный, перестройка сводной таблицы выполняется долго. В этом случае нужно пользоваться Мастером сводных таблиц. Он позволяет разделить процессы проектирования структуры и построения отчета сводной таблицы. Компьютеру не придется перестраивать отчет «на лету» при каждом перетаскивании поля.

Рис. 18 Изменение макета сводного отчета

Если щелкнуть правой кнопкой мыши в области сводной таблицы и выбрать из выпадающего меню пункт «Мастер…», откроется знакомое окно Мастера сводных таблиц. Причем сразу на третьем шаге (подразумевается, что диапазон исходных данных уже определен). Как вы помните, на третьем шаге Мастер предоставляет диаграмму сводной таблицы, и список всех доступных полей. Диаграмма будет заполнена полями, в соответствии со структурой сводной таблицы.

Перестроим диаграмму сводной таблицы: перетащим поле «Товар» из области столбцов в область строк, поле «Покупатель» в область столбцов (рис. 19 Перед полем «Товар» вставим поле «Дата», тогда строки отчета будут группироваться сначала по датам, а в пределах каждой даты — по товарам. Щелкнем по кнопке «Готово», и отчет сводной таблицы будет перестроен в соответствии с изменившимися планами (рис. 20).

Рис. 19 — Перегруппировка сводного отчета по датам и товарам

Рис. 20 — Результат перегруппировки сводного отчета

3.8.2. Внедрение вычисляемых полей в сводный отчет

В нашей таблице есть информация о сумме продаж и о себестоимости отгруженных по каждой накладной товаров. Проанализируем, какую прибыль принесла каждая конкретная сделка. С каким покупателем работать выгодно, а с каким – нет? Для этого в сводной таблице нужно выполнить дополнительные вычисления. Чтобы это сделать, в сводную таблицу нужно добавить вычисляемые поля или вычисляемые элементы полей[9]. Заметим, что вычислительные возможности сводной таблицы с дополнительными полями ограничены. В формулах для вычисляемых полей нельзя ссылаться на ячейки со сводными (итоговыми) данными, как в обычной таблице Excel. Можно ссылаться только на поля и элементы полей. Еще одно ограничение: способ расчета вычисляемого поля только один — суммирование.