Данная статья немного устарела. Предлагаю Вам ознакомиться с ее новой версией, заключающей в себе новый опыт, полученный за последний год работы. Новая статья .
В работе каждый день мы видим множество отчетов, которые выглядят по-детски или некачественно. Чтобы ваши отчеты выглядели профессионально, я предлагаю вам один из вариантов подготовки таких отчетов. Изначально он был разработан для анализа финансовой отчетности крупного предприятия - получилось около 30 страниц текста, схем и диаграмм. Отчет очень понравился менеджменту.
Я предлагаю вам посмотреть пример такого отчета в приложенном файле по этой ссылке . О том, как его подготовить в Microsoft Excel, не прибегая к дорогостоящему программному обеспечению, такому как Adobe InDesign.
Для этого нам надо взять на вооружение несколько вещей: (1) вид в режиме "Разметка страницы" (включить его можно, нажав на ленте View на кнопку Page layout из секции Workbook views , об этом виде и о том, как оптимизировать отчет для печати смотрите в этой статье), (2) научный подход.
Если с первым пунктом все понятно, то второй требует определенного пояснения.
Общие советы
Подготовку отчета следует начинать не с обработки информации и не с набросков, а совсем с другой стороны - а именно с разработки шаблона представления отчета. Отчет будет выглядеть профессионально только, если он будет однотипным от раздела к разделу. Именно поэтому и нужен шаблон, который вы будете копировать, как новый лист для нового раздела и заполнять.
Шаблон не должен быть аляпистым. Я предлагаю вам использовать цвета стандартной темы Microsoft Excel 2007-2010. Они очень комфортные для восприятие пользователем вашего отчета: будь то ваш менеджер, финансовый директор, исполнительный директор, акционеры или клиенты компании. Для примера я использовал синий цвет. Зеленый также выглядит достаточно хорошо. Если есть какой-то раздел, на который вы хотели бы обратить особое внимание, вы можете сделать его в красных тонах, главное выбирайте не электрический красный, а комфортный красный.
Градиенты на графиках очень позитивно воспринимаются менеджментом. Обычно для менеджмента это высший пилотаж, в то время как он не требует от вас почти никаких усилий.
Такой отчет (особенно когда он состоит из полусотни страниц) будет очень хорошо смотреться распечатанным на цветном принтере с двух сторон и сброшюрованным. У нашего контроллера лежало с десяток таких отчетов, которые он раздавал заинтересованным сторонам на встречах.
Если вы собираетесь распространять его в электронном виде, ОБЯЗАТЕЛЬНО переведите его в формат pdf. Он гораздо удобнее для чтения, хоть и теряет некоторые функции. На мой взгляд, самый удобный способ перевода его в этот формат - это не использование pdf-ных принтеров, а встроенная функциональность Microsoft Excel 2007-2010 (File => Save as => выбрать формат pdf).
Есть несколько моментов, на которые я хотел бы обратить ваше внимание.
(1) На листе шаблона самая правая колонка - очень узкая (буквально пара пикселей). Она там не просто так. Если ее не добавить и вставлять графики и текстовые поля "притягивая" их к краю ячеек, то те из них, что находятся справа, своей границей (даже если она прозрачная) будут "залазить" на соседнюю страницу, заставляя Excel печатать пустую страницу.
(2) "Притягивайте" текстовые поля и графики к краям ячеек. Тогда они все будут находиться строго вертикально друг под другом. Размер графика и параллельного с ним поля можно будет менять одновременно, растягивая строку, к которой они привязаны. Для того чтобы "привязать" размер объекта к краю ячейки, в момент изменения размера этого объекта нажмите на Alt на клавиатуре. Кстати, если нажать Shift , то его размеры будут меняться строго в одном направлении - вертикально или горизонтально.
Послесловие
Разработку данного отчета во многом вдохновил формат публикаций, подготавливаемых компанией KPMG. Вы можете посмотреть их на их официальном сайте. Уж не знаю в чем они их делают, но из всей G4 у них самые современные и профессионально выглядящие отчеты.
Данные листа Excel должны быть в формате списка с заголовком столбца в первой строке. Остальные строки должны содержать аналогичные объекты в одном столбце, без пустых строк и столбцов внутри диапазона данных.
- В окне открытого листа выделите любую ячейку в диапазоне данных.
- Перейдите к вкладке «Вставка» и в группе «Таблицы» раскройте меню кнопки «Сводная таблица».
- В списке команд выберите пункт «Сводная таблица» (рис. 5.59).
- В окне «Создание сводной таблицы» (рис. 5.60) в графе «Таблица или диапазон» отобразится адрес выбранного диапазона анализируемых данных.
- В группе «Укажите, куда следует поместить отчет сводной таблицы» выберите пункт:
- «На новый лист» – для размещения отчета сводной таблицы на дополнительном листе в книге.
- «На существующий лист» – для размещения отчета сводной таблицы в том же листе, где находятся и исходные данные.
- Закройте окно кнопкой «ОК».
- На листе (новом или существующем) появится пустая сводная таблица в виде контура макета, а справа листа откроется область «Список полей сводной таблицы» для создания отчета (рис. 5.61). В нем можно вставлять в сводную таблицу новые поля, создавать макет и настраивать отчет.
- Отметьте в окне «Список полей» имена столбцов для создания сводной таблицы.
Если необходимо изменить диапазон, то выделите на листе новый диапазон, не закрывая при этом окно «Создание сводной таблицы». Диапазон ячеек, который находится на другом листе книги или же в другой книге, вводится следующим образом: ([имякниги]имялиста!диапазон) .
При выборе данного места размещения необходимо в пункте «Диапазон» самостоятельно указать на листе ячейку для отчета.
Чтобы скрыть окно «Список полей», щелкните по любой свободной ячейке листа. Или же на вкладке «Параметры» в группе «Показать или скрыть» отключите пункт «Список полей».
Отчетность перед руководством и собственниками компании — важная составляющая в работе планово-экономического отдела. Отчеты должны быть понятными и лаконичными, а контрольные показатели необходимо представлять в подробных аналитиках. Если вам нужно подготовить полугодовую отчетность с учетом таких требований, рекомендуем ознакомиться с материалом данной статьи. Вы узнаете, как в Excel сделать отчеты за полугодие в графической форме, выделить в них ключевые показатели.
ПОДГОТОВКА ПОЛУГОДОВОЙ ОТЧЕТНОСТИ В EXCEL
При подготовке полугодовой отчетности специалисты сталкиваются со следующей проблематикой:
- большое количество позиций в отчете;
- объемные отчеты — каждый показатель нужно представить в помесячной динамике;
- разброс значений показателей, разный порядок цифр.
Чтобы успешно справиться с указанными проблемами, подготовить для руководства четкие и понятные отчеты за полугодие, предлагаем использовать графическую форму. Для объемных таблиц, подробных аналитик информативно применить спарклайны и условное форматирование, для компактных таблиц и выбранных ключевых показателей оптимально использовать диаграммы.
Спарклайны в Excel
В полугодовом отчете принято демонстрировать помесячную динамику показателей. Речь идет о таких показателях, как выручка, объем реализации, себестоимость, объем закупок и прибыли, рентабельность. Перечень номенклатуры на практике всегда широкий. В таком случае удобно непосредственно в таблицу с показателями вставить графики, что позволит оценить не только отдельные показатели, но и их динамику, сопоставить результаты. Для указанной группы показателей целесообразно применить спарклайны (рис. 1).
Визуализируем данные табл. 1 «Анализ продаж с помощью спарклайнов», выполнив следующие действия:
- В таблице 1 создаем столбец или строку для размещения следующей графической информации:
- «Динамика за I полугодие»;
- «Анализ выручки по продуктовым группам»;
- «Анализ рентабельности по продуктовым группам».
2. Выполняем команду: Вставка → Спарклайны → График (см. рис. 1). В окне «Создание спарклайнов » задаем диапазон данных и диапазон расположения (рис. 2).
3. Копируем графический элемент в нужное количество ячеек. При необходимости редактируем график с помощью Конструктора (рис. 3). Например, отмечаем флажком отметку максимальных точек или маркеров, изменяем цвет графика.
4. Для «Анализа выручки по продуктовым группам» и «Анализа рентабельности по продуктовым группам» используем тип спарклайна «Столбец».
Дополненная спарклайнами табл. 1 представлена в «Сервисе форм» (код доступа — на с. 119). Из таблицы видно, что только выручка по красителям снижается, имеет нисходящую динамику графика-спарклайна. По остальной продукции и в целом по компании выручка растет (графа «Динамика за первое полугодие»).
Строка «Анализ выручки по продуктовым группам» показывает, что наибольший объем выручки компания получает от реализации кирпича стандартного гладкого — 4003,9 из 42 068,82 тыс. руб.
Столбцовый спраклайн в строке «Анализ рентабельности по продуктовым группам» показывает, что максимальную рентабельность имеет кирпич фактурный — от 40 до 42,9 %. Скачек рентабельности произошел в апреле (видно на графике в столбце «Динамика за I полугодие»). В апреле зафиксирована отрицательная рентабельность по кирпичу стандартному и сниженная (11,1 %) по кирпичу гладкому с повышенным содержанием пигментов.
Важный момент: в отчете нужно указать причину визуализированных изменений. Возможная причина изменений в рассматриваемом случае: в начале сезона продаж (весна) предоставили акционные скидки для стимулирования объемов продаж, привлечения новых клиентов.
ОБРАТИТЕ ВНИМАНИЕ
При копировании таблицы со спарклайнами из Excel в документ Word графики могут быть не видны. В таком случае используют вставку в формате рисунка.
Условное форматирование
В отношении расходов руководство часто требует подробную аналитику. Это могут быть бюджеты расходов по разным направлениям, подробный отчет по фонду оплаты труда, платежи поставщикам. В отчетах нужно выделять важную информацию и нестандартные значения. Восприятие данных улучшится, если использовать гистограммы, цветовые шкалы, наборы значков.
Подготовим табл. 2 «Бюджет общезаводских расходов за I полугодие (факт)» к сдаче руководству с помощью функций условного форматирования:
- Выделяем в таблице диапазон, информацию которого нужно представить более наглядно (в примере — столбцы январь-март). Задаем: Главная → Условное форматирование → Цветовые шкалы . Выберем информативную цветовую шкалу «зеленый-белый» (рис. 4). Пользователи могут выбрать шкалу цвета на свое усмотрение.
Высокие расходы окрасились в привлекающий внимание зеленый цвет:
- расходы на фонд оплаты труда с отчислениями — 250, 240 и 236 тыс. руб.;
- представительские расходы в марте — 300 тыс. руб.
Чем выше сумма расходов, тем насыщеннее цвет в ячейке.
- Дополняем табл. 2 визуализацией итоговых показателей по статьям расходов. Выделим цифры в столбце факта за полугодие и выполним действия: Главная → Условное форматирование → Наборы значков → Другие правила . Сделаем индивидуальные настройки (рис. 5).
Выбираем «Форматировать все ячейки на основании их значений», а затем подходящий «Значок». В нашем случае это гистограммы. Определяем «Значение» и «Тип»:
- заполненная гистограмма (четыре деления) — для значений более 500 тыс. руб.;
- гистограммы с тремя делениями — от 100 до 500 тыс. руб.;
- гистограмма с одним делением — все значения менее 100 тыс. руб.
Параметры экономист задает исходя из их разброса, средних значений и правил, принятых в компании.
Бюджет общезаводских расходов для отправки руководству выглядит следующим образом (табл. 2).
На примере таблицы «Кредиторская задолженность на конец периода» рассмотрим еще один способ удобно представить показатели.
Е. С. Панченко, бизнес-консультант
Материал публикуется частично. Полностью его можно прочитать в журнале
Смотрите видео к статье:
Типовые отчеты, которые входят в наши модули готовых решений Business Intelligence (BI) позволяют ответить на 99% самых популярных запросов пользователей.
Но при желании, аналитики или любые другие пользователи могут делать свои отчеты в Excel на OLAP-кубах.
Благодаря тому, что в основе каждого нашего модуля Бизнес – аналитики (BI) лежит OLAP-куб , подключившись к которому пользователи легко могут построить любой отчет в Excel с помощью Сводных таблиц.
Также построенные в Excel отчеты на OLAP-кубах можно сохранить, и при повторном открытии они автоматически будут обновляться актуальными данными из корпоративной системы Бизнес – аналитики (BI) .
Примеры отчетов, построенных на OLAP–кубах:
Описание отчетов:
(Процесс построения отчетов смотрите в видео к этой статье)
1. Отчет по продажам в рублях и единицах:
- период : за текущий год
- показатели : факт продаж
- детализация : столбцы/квартал, месяц
- детализация : строки/каналы продаж, клиенты, бренды, товары
2. Отчет по структуре продаж в рублях и единицах (фильтр):
- период : за текущий год
- показатели : факт продаж, факт продаж в процентах
- детализация : столбцы/квартал, месяц
- детализация : строки/каналы продаж, бренды
3. Отчет по выполнению плана продаж в рублях и единицах (фильтр):
- период : за текущий год
- показатели : план продаж, факт продаж, выполнение плана
- детализация : столбцы/квартал, месяц
- детализация : строки/каналы продаж, клиенты, категории, товары
4. Отчет по приросту продаж в рублях и единицах (фильтр):
- период
- показатели
- детализация : строки/федеральные округа, регионы, категории, товары
5. Отчет по приросту продаж по времени в рублях и единицах (фильтр):
- период : за текущий год в сравнении с предыдущим
- показатели : продажи предыдущего года, план продаж, факт продаж, выполнение плана, прирост продаж в абсолютном и относительном выражении к предыдущему году
- детализация : строки/квартал, месяц, категории, товары
6. Отчет по дебиторской задолженности:
- период : на текущую дату
- показатели : сумма документа, общая дебиторская задолженность, текущая дебиторская задолженность, просроченная дебиторская задолженность по периодам – до 15 дней, до 30 дней, до 60 дней, до 90 дней, до 180 дней, до 365 дней и более, структура дебиторской задолженности
- детализация : строки/каналы продаж, контрагент, дата оплаты документа, дата документа, документ
7. Отчет динамика дебиторской задолженности:
- период : за текущий год
- показатели : общая дебиторская задолженность, текущая дебиторская задолженность, просроченная дебиторская задолженность, структура дебиторской задолженности
- детализация : строки/год, неделя, контрагент
8. Отчет платежи приход по дате документа:
- период : за текущий год
- показатели : сумма документа, сумма оплаты документа, отклонения по оплате в абсолютном и относительном выражении
- детализация : строки/год, месяц, контрагент, вид документа, дата документа, номер документа
9. Отчет платежи приход по дате платежа:
- период : за текущий год
- показатели : сумма платежа, сумма распределения платежа, отклонения по распределению в абсолютном и относительном выражении
- детализация : строки/год, месяц, контрагент, вид платежа, дата платежа, номер платежа
10. Отчет по товарным запасам:
- период : на текущую дату
- показатели : остаток товара, остаточный срок годности в процентах (по DSO), остаток товара по срокам годности — Срок 0%, Срок 10-30%, Срок 40-60%, Срок 70-90%, Срок 99%
- детализация : строки/категория, товар, статус, склад, дата документа прихода
Привет всем! Сегодняшний материал для тех, кто продолжает осваивать работу с программами-приложениями, и не знает, как сделать сводную таблицу в excel.
Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.
Создание сводной Эксель таблицы требует соблюдения определенных условий:
- Данные вписываются в таблицу, где есть столбцы и списки с названиями.
- Отсутствие незаполненных форм.
- Отсутствие скрытых объектов.
Как сделать сводную таблицу в excel: пошаговая инструкция
Для создания сводной таблицы необходимо:
Создался пустой лист, где видно списки областей и полей. Заголовки стали полями в нашей новой таблице. Сводная таблица будет формироваться путем перетаскивания полей.
Помечаться они будут галочкой, и для удобства анализа вы будете их менять местами в табличных областях.
Я решил, что анализ данных буду делать через фильтр по продавцам, чтобы было видно кем и на какую сумму каждый месяц было продано, и какой именно товар.
Выбираем конкретного продавца. Зажимаем мышку и переносим поле «Продавец» в «Фильтр отчета». Новое поле отмечается галочкой, и вид таблицы немного изменяется.
Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.
Для отображения выпадающего списка имеет значение, в какой последовательности мы указываем наименование. Если изначально в строках делаем выбор в пользу товара, а потом указываем цену, то товары как раз и будут выпадающими списками, и наоборот.
Столбец «Единицы», будучи в главной таблице, отображал количество товара проданного определенным продавцом по конкретной цене.
Для отображения продаж, например, по каждому месяцу, нужно поле «Дата» поставить на место «Названия столбцов». Выберите команду «Группировать», нажав на дату.
Указываем периоды даты и шаг. Подтверждаем выбор.
Видим такую таблицу.
Сделаем перенос поля «Сумма» к области «Значения».
Стало видно отображение чисел, а нам необходим именно числовой формат
Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».
Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».
Оформление сводной таблицы
Если мы поставим галочку, которая подтверждает выделение сразу нескольких объектов, то сможем обрабатывать данные сразу по нескольким продавцам.
Применение фильтра возможно для столбцов и строк. Поставив галочку на одной из разновидностей товара, можно узнать, сколько его реализовано одним или несколькими продавцами.
Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений».
Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.
Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.
Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица отображает весь период продаж или за конкретный месяц.
Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор».
На самом деле рассказывать о настройках сводных таблиц можно еще очень долго. Проводите изменения под свой вкус, добиваясь удобного для вас пользования. Не бойтесь нажимать и экспериментировать. Любое действие вы всегда сможете изменить нажатием сочетания клавиш Ctrl+Z.
Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в excel.
Загрузка...