Отчеты в MS EXCEL. Управленческая отчетность образец в excel Отчет xls
Работать со сводными таблицами Excel приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д.
Сводные таблицы позволяют быстро сформировать различные отчеты по одним и тем же данным. Кроме того, эти отчеты можно гибко настраивать, изменять, обновлять и детализировать.
Создание отчета с помощью мастера сводных таблиц
У нас есть тренировочная таблица с данными:
Каждая строка дает нам исчерпывающую информацию об одной сделке:
- в каком магазине были продажи;
- какого товара и на какую сумму;
- кто из продавцов постарался;
- когда (число, месяц).
Если это огромная сеть магазинов и продажи идут, то в течение одного квартала размер таблицы станет ужасающим. Проанализировать данные в сотне строк будет очень сложно. А на составление отчета уйдет не один день. В такой ситуации сводная таблица просто необходима.
Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках:
- Выберите «Файл»-«Параметры»-«Панель быстрого доступа».
- В выпадающем списке левой колонки: «Выбрать команду из» укажите «Все команды».
- В левой колонке найдите по алфавитному порядку и выделите: «Мастер сводных таблиц и диаграмм». Нажмите на кнопку между колонками: «Добавить» чтобы инструмент переместился в правую колонку и нажмите ОК.
Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой.
Готовый отчет можно форматировать, изменять.
Как обновить данные в сводной таблице Excel?
Это можно сделать вручную и автоматически.
Настройка автоматического обновления при изменении данных:
- На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
- Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
- В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».
Теперь каждый раз при открытии файла с измененными данными будет происходить автоматическое обновление сводной таблицы.
Некоторые секреты форматирования
Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.
Группировка по дате в сводной таблице Excel:
Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:
Если фамилия продавцов для анализа деятельности сети магазинов не важна, можем сформировать отчет с поквартальной прибылью.
Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».
Работа с итогами
У нас есть сводный отчет такого вида:
Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.
Как в сводной таблице сделать итоги сверху:
Уже нет той перегруженности, которая затрудняла восприятие информации.
Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:
Получим отчет без дополнительных сумм:
Детализация информации
Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.
Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».
По умолчанию в сводную таблицу помещается абсолютно вся информация из того столбца, который мы добавляем в отчет.
В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.
Жмем ОК – сводная таблица меняется.
Данная статья немного устарела. Предлагаю Вам ознакомиться с ее новой версией, заключающей в себе новый опыт, полученный за последний год работы. Новая статья .
В работе каждый день мы видим множество отчетов, которые выглядят по-детски или некачественно. Чтобы ваши отчеты выглядели профессионально, я предлагаю вам один из вариантов подготовки таких отчетов. Изначально он был разработан для анализа финансовой отчетности крупного предприятия - получилось около 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 у них самые современные и профессионально выглядящие отчеты.
Для анализа больших и сложных таблиц обычно используют . С помощью формул также можно осуществить группировку и анализ имеющихся данных. Создадим несложные отчеты с помощью формул.
В качестве исходной будем использовать (Вставка/ Таблицы/ Таблица ), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье .
В таблице имеются столбцы:
- Товар – наименование партии товара, например, «Апельсины »;
- Группа – группа товара, например, «Апельсины » входят в группу «Фрукты »;
- Дата поставки – Дата поставки Товара Поставщиком;
- Регион продажи – Регион, в котором была реализована партия Товара;
- Продажи – Стоимость, по которой удалось реализовать партию Товара;
- Сбыт – срок фактической реализации Товара в Регионе (в днях);
- Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
Через Диспетчер имен откорректируем таблицы на «Исходная_таблица » (см. файл примера ).
С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.
Отчет №1 Суммарные продажи Товаров
Найдем суммарные продажи каждого Товара.
Задача решается достаточно просто с помощью функции СУММЕСЛИ()
, однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.
Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только значения. Это можно сделать несколькими способами: формулами (см. статью ), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью . Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:
- Перейдите на лист с исходной таблицей;
- Вызовите (Данные/ Сортировка и фильтр/ Дополнительно );
- Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место ; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи .
- Скопируйте полученный список на лист, в котором будет размещен отчет;
- Отсортируйте перечень товаров (Данные/ Сортировка и фильтр/ Сортировка от А до Я ).
Должен получиться следующий список.
В ячейке B6 введем нижеследующую формулу, затем скопируем ее вниз до конца списка:
СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])
СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)
Отчет №2 Продажи Товаров по Регионам
Найдем суммарные продажи каждого Товара в Регионах.
Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон введите $D$4:$D$530).
Скопируйте полученный вертикальный диапазон в Буфер обмена
и его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.
В ячейке B 8 введем нижеследующую формулу:
СУММЕСЛИМН(Исходная_Таблица[Продажи];
Исходная_Таблица[Товар];$A8;
Исходная_Таблица[Регион продажи];B$7)
Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8 , в Регионе из ячейки В7 . Обратите внимание на использование (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.
Скопировать вышеуказанную формулу в ячейки справа с помощью не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:
СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней];
Исходная_Таблица[Группа];$A8;
Исходная_Таблица[Продажи];C$7)
Отчет №3 Фильтрация Товаров по прибыльности
Вернемся к исходной таблице. Каждая партия Товара либо принесла прибыль, либо не принесла (см. столбец Прибыль в исходной таблице). Подсчитаем продажи по Группам Товаров в зависимости от прибыльности. Для этого будем фильтровать с помощью формул записи исходной таблицы по полю Прибыль.
ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)
Для ввода формулы выделите диапазон С6:С12 , затем в Строке формул введите вышеуказанную формулу и нажмите CTRL + SHIFT + ENTER .
Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ()
:
=СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)*
(Исходная_Таблица[Сбыт, дней]<=A7))
Отчет №5 Статистика поставок Товаров
Теперь подготовим отчет о поставках Товаров за месяц.
Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы:
=МИН(Исходная_Таблица[Дата поставки])
Создадим перечень дат - , начиная с самой ранней даты поставки. Для этого воспользуемся формулой:
=КОНМЕСЯЦА($C$5;-1)+1
В результате получим перечень дат - первых дней месяцев:
Применив соответствующий формат ячеек, изменим отображение дат:
Формула для подсчета количества поставленных партий Товаров за месяц:
СУММПРОИЗВ((Исходная_Таблица[Дата поставки]>=B9)* Теперь добавим строки для подсчета суммарного количества партий по каждому году. Для этого немного изменим таблицу, выделив в отдельный столбец год, в который осуществлялась поставка, с помощью функции ГОД()
. Теперь для вывода по годам создадим структуру через пункт меню : После нажатия ОК, таблица будет изменена следующим образом: Будут созданы промежуточные итоги по годам. Резюме
:
Отчеты, аналогичные созданным, можно сделать, естественно, с помощью или с применением Фильтра
к исходной таблице или с помощью других функций БДСУММ()
, БИЗВЛЕЧЬ()
, БСЧЁТ()
и др. Выбор подхода зависит конкретной ситуации. Ренат уже не в первый раз выступает гостевым автором на Лайфхакере. Ранее мы публиковали отличный материал от него о том, как составить план тренировок: и онлайн-ресурсы, а также создания тренировочного плана.
В этой статье собраны несложные приёмы, позволяющие . Особенно они пригодятся тем, кто занимается управленческой отчётностью, готовит разнообразные аналитические отчёты, основанные на выгрузках из 1С и других отчётах, формирует из них презентации и диаграммы для руководства. Не претендую на абсолютную новизну - в том или ином виде эти приёмы наверняка обсуждались на форумах или . Результат: В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже: ТЕКСТ (значение; формат
) Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек». Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать - стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы. При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки: Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера): Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить: Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция. Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»: После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера: При щелчке на неё появляется диалоговое окно: В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных - выберите второй пункт: В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования: После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы - на существующем или новом листе: Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо: В данном примере в столбце A есть несколько текстовых строк, и наша задача - выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1: Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций: Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной: ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6 А затем разделить эту разницу на длину той строки, которую мы искали: 6 / ДЛСТР (“МТС”) = 2 Именно два раза строка «МТС» входит в исходную. Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» - тот, число вхождений которого нас интересует): =(ДЛСТР(текст
)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст
);ПРОПИСН(искомый
);“”)))/ДЛСТР(искомый
) В нашем примере формула выглядит следующим образом: =(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1) Смотрите видео к статье:
Но при желании, аналитики или любые другие пользователи могут делать свои отчеты в Excel на OLAP-кубах. Благодаря тому, что в основе каждого нашего модуля Бизнес – аналитики (BI) лежит OLAP-куб
, подключившись к которому пользователи легко могут построить любой отчет в Excel с помощью Сводных таблиц. Также построенные в Excel отчеты на OLAP-кубах можно сохранить, и при повторном открытии они автоматически будут обновляться актуальными данными из корпоративной системы Бизнес – аналитики (BI)
. (Процесс построения отчетов смотрите в видео к этой статье)
(Исходная_Таблица[Дата поставки]
Нажатием маленьких кнопочек в левом верхнем углу листа можно управлять отображением данных в таблице.
Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ
Как сохранить данные в каждой ячейке после объединения
Как построить сводную из нескольких источников данных
Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» - два вхождения аббревиатуры МТС)
Типовые отчеты, которые входят в наши модули готовых решений Business Intelligence (BI)
позволяют ответить на 99% самых популярных запросов пользователей.
Примеры отчетов, построенных на OLAP–кубах:
Описание отчетов:
1. Отчет по продажам в рублях и единицах:
2. Отчет по структуре продаж в рублях и единицах (фильтр):
3. Отчет по выполнению плана продаж в рублях и единицах (фильтр):
4. Отчет по приросту продаж в рублях и единицах (фильтр):
5. Отчет по приросту продаж по времени в рублях и единицах (фильтр):
6. Отчет по дебиторской задолженности:
7. Отчет динамика дебиторской задолженности:
8. Отчет платежи приход по дате документа:
9. Отчет платежи приход по дате платежа:
10. Отчет по товарным запасам:
(1 оценок, в среднем: 5,00 из 5)
Loading...
