Как сделать отчет о результатах в excel?

Отчеты в MS EXCEL

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 ( Вставка/ Таблицы/ Таблица ), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье Сводные таблицы.

В таблице имеются столбцы:

• Товар – наименование партии товара, например, «Апельсины»;
• Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен откорректируем имя таблицы на «Исходная_таблица» (см. файл примера ).

С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара.
Задача решается достаточно просто с помощью функции СУММЕСЛИ() , однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только уникальные значения. Это можно сделать несколькими способами: формулами (см. статью Отбор уникальных значений), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью Расширенного фильтра. Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

• Перейдите на лист с исходной таблицей;
• Вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
• Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи.

• Скопируйте полученный список на лист, в котором будет размещен отчет;
• Отсортируйте перечень товаров ( Данные/ Сортировка и фильтр/ Сортировка от А до Я ).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel.

Также можно легко подсчитать количество партий каждого Товара:

Отчет №2 Продажи Товаров по Регионам

Найдем суммарные продажи каждого Товара в Регионах.
Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон Расширенного фильтра введите $D$4:$D$530).
Скопируйте полученный вертикальный диапазон в Буфер обмена и транспонируйте его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке B8 введем нижеследующую формулу:

=СУММЕСЛИМН(Исходная_Таблица[Продажи];
Исходная_Таблица[Товар];$A8;
Исходная_Таблица[Регион продажи];B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8, в Регионе из ячейки В7. Обратите внимание на использование смешанной адресации (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью Маркера заполнения не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней];
Исходная_Таблица[Группа];$A8;
Исходная_Таблица[Продажи];C$7)

Ссылки, согласно правил относительной адресации, теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки B8, в Буфер обмена, затем вставить ее в диапазон С8:G8, нажав CTRL+V.
В ячейки ниже формулу можно скопировать Маркером заполнения.

Отчет №3 Фильтрация Товаров по прибыльности

Вернемся к исходной таблице. Каждая партия Товара либо принесла прибыль, либо не принесла (см. столбец Прибыль в исходной таблице). Подсчитаем продажи по Группам Товаров в зависимости от прибыльности. Для этого будем фильтровать с помощью формул записи исходной таблицы по полю Прибыль.

Создадим Выпадающий (раскрывающийся) список на основе Проверки данных со следующими значениями: (Все); Да; Нет. Если будет выбрано значение фильтра (Все), то при расчете продаж будут учтены все записи исходной таблицы. Если будет выбрано значение фильтра «Да», то будут учтены только прибыльные партии Товаров, если будет выбрано «Нет», то только убыточные.

Суммарные продажи подсчитаем следующей формулой массива:
=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)*
ЕСЛИ($B$5=»(Все)»;1;(Исходная_Таблица[Прибыль]=$B$5))*
Исходная_Таблица[Продажи])

После ввода формулы не забудьте вместо простого нажатия клавиши ENTER нажать CTRL+SHIFT+ENTER.

Количество партий по каждой группе Товара, в зависимости от прибыльности, можно подсчитать аналогичной формулой.

Так будет выглядеть отчет о продажах по Группам Товаров, принесших прибыль.

Выбрав в фильтре значение Нет (в ячейке B5), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце B.

Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы ЧАСТОТА() , которую нужно ввести как формулу массива:

Для ввода формулы выделите диапазон С6:С12, затем в Строке формул введите вышеуказанную формулу и нажмите CTRL+SHIFT+ENTER.

Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ() :
=СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)*
(Исходная_Таблица[Сбыт, дней] =B9)*
(Исходная_Таблица[Дата поставки] Похожие задачи

Программа Поиск решений

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

Программа ^ Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года.

Разработчик программы ^ Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).

Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса.

По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel , а затем выберите категорию Надстройки . В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти . В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК .

В Excel 2003 и ниже выберите команду Сервис/Надстройки , в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office).

Процедура поиска решения

1. Создайте таблицу с формулами, которые устанавливают связи между ячейками.

2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду:

— В Excel 2007 Данные/Анализ/Поиск решения ;

— В Excel 2003 и ниже Сервис > Поиск решения. Поле Установить целевую ячейку открывшегося диалогового окна надстройки ^ Поиск решения будет содержать адрес целевой ячейки.

3. Установите переключатели Равной, задающие значение целевой ячейки, – максимальному значению, минимальному значению или значению. В последнем случае введите значение в поле справа.

4. Укажите в поле Изменяя ячейки, в каких ячейках программа должна изменять значения в поисках оптимального результата.

5. Создайте ограничения в списке Ограничения. Для этого щелкните на кнопке Добавить и в диалоговом окне Добавление ограничения определите ограничение.

6. Щелкните на кнопке Параметры, и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)

7. Щелкнув на кнопке Выполнить, запустите процесс поиска решения.

8. Когда появится диалоговое окно Результаты поиска решения, выберите переключатель Сохранить найденное решение или Восстановить исходные значения.

9. Щелкните на кнопке ОК.

^ Параметры средства Поиск решения

Максимальное время –служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.

Предельное число итераций – управляет временем решения задачи путем ограничения числа вычислительных циклов (итераций).

Относительная погрешность – определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений.

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

Сходимость – применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается.

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

Неотрицательные значения – позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение.

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

Показывать результаты итераций – приостанавливает поиск решения для просмотра результатов отдельных итераций.

Загрузить модель – после щелчка на этой кнопке отрывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации.

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

Оценка линейная – выберите этот переключатель для работы с линейной моделью.

Оценка квадратичная – выберите этот переключатель для работы с нелинейной моделью.

Разности прямые – используется в большинстве задач, где скорость изменения ограничений относительно невысока. Увеличивает скорость работы средства Поиск решения.

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

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

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

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

Отчеты бывают трех типов:

• по результатам
• по устойчивости
• по пределам

Тип выбирается по окончании поиска решений в диалоговом окне Результаты поиска решений в списке Отчеты. Можно выбрать сразу два или три типа с помощью мыши при нажатой клавиши . Каждый отчет будет создан на отдельном рабочем листе.
^ Отчет по результатам.

Отчет по результатам содержит информацию о трех компонентах задачи оптимизации: целевой функции (Целевая ячейка), плана (Изменяемые ячейки), и ограничений (Ограничения).

^ 1 – начальное значение целевой функции при начальном опорном плане (3);

2 – максимальное или минимальное значение (в зависимости от задачи) целевой функции. В нашем случае – 168,57 д. ед.;

^ 3 – начальный опорный план;

4 – оптимальный план задачи. В нашем случае, чтобы получить максимальную выручку в размере 168,37 д. ед., нужно производить 57,14 единиц товара А и 71,43 единиц товара Б (понятно, что товар должен быть в целых единицах, но если бы мы задали такой параметр, то не получили отчеты, которые нужны для анализа и улучшение полученных результатов);

^ 5 – показывает количество использованных ресурсов на производство при оптимальном плане;

6 – формулы ограничений;

7 – показывает влияние ограничений на конечный результат. Если статус «связанное», тогда данное ограничение влияет на полученный план, если «не связан» – значит не влияет. В нашем случае ресурс 1 и 4 имеют статус «не связан» – это значит, что эти ресурсы не ограничивают возможности в производстве, что не скажешь про ресурс 2 и 3, которые использованы полностью;

^ 8 – разница между имеющимся в наличие количеством ресурсов и использованных при полученном плане.

Отчет по устойчивости

Отчет по устойчивости (более точным за содержанием был бы перевод с оригинала sensitivity – чувствительность) есть наиболее интересным и полезным – он определяет чувствительность структуры полученного плану до изменений начальных данных и, согласно, дальнейшие действия менеджера с целью улучшения результатов. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел. В случае нелинейных моделей отчет содержит данные для градиентов и множителей Лагранжа.

^ 1 – оптимальный план задачи. В нашем случае, чтобы получить максимальную выручку в размере 168,37 д. ед., нужно производить 57,14 единиц товара А и 71,43 единиц товара Б;

2 – нормированная стоимость касается неизвестных плана. Это неудачный перевод с оригинала reduced cost, которую можно было перевести, как «цена, которая уменьшает (целевую функцию)». Этот показатель, как изменится оптимальное значение ЦФ при выпуске продукции, которой нету в оптимальном плане. Например, если нормированная стоимость товара А была бы -3 (хотя в нашем случае это 0), то принудительный выпуск 2 единиц товара А, которых нету в оптимальном плане привел к уменьшению Дохода на 2•3=6 и составлял бы 168,57-6= 162, 57 д. ед.

^ 3 – коэффициенты ЦФ;

4, 5 – границы изменений значений коэффициентов ЦФ при условии, что количество оптимальной продукции (план) не изменится. Например, если целевой коэффициент товара А (КА) равен 1,15 (цена за 1 единицу товара), то изменяя его в рамках 1,15-0,43 0,72

Формируем отчеты в Excel для руководства

Как разработать формы отчетности для руководства в Excel?

Какие расчетные формулы в них следует прописать?

Как заполнить управленческие отчеты в Excel данными о работе компании?

Как визуализировать данные управленческих отчетов с помощью Excel?

Разработчики программных продуктов для учета хозяйственной деятельности предприятия значительно продвинулись в функционале управленческих отчетов для руководства. Однако по-прежнему есть три основные проблемы:

• во-первых, перечень таких отчетов ограничен типовым функционалом программы, набор показателей в них зафиксирован возможностями аналитики данных в этой программе. Соответственно, чтобы расширить перечень отчетов или их показателей, необходимо привлекать к доработке программистов и нести дополнительные расходы;
• во-вторых, большинство отчетов представлены в виде табличных форм. В них есть колонки «План», «Факт» и «Отклонения», но этого недостаточно, чтобы информативно и наглядно продемонстрировать руководству результаты деятельности компании;
• в-третьих, когда речь заходит о формировании комплексной отчетности, то неизбежно сталкиваемся с тем, что в программе легко можно сформировать несколько отчетов с разноплановыми показателями, но сделать один отчет с общими данными нескольких отчетов пользователю программы просто невозможно — для этого нужно создавать как минимум дополнительные обработки, которые опять же ограничены техническими возможностями используемой программы.

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

Разработка форм отчетности для руководства в Excel с расчетными формулами

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

Для того чтобы отчетность для руководства получилась информативной и наглядной, достаточно сначала сформировать типовые отчеты в программе и сохранить их как файлы формата Excel. Затем данные этих файлов копируем как отдельные листы в новой книге и далее формируем в ней листы с формами управленческой отчетности.

Например, руководителю финансово-экономической службы компании «Альфа» нужно предоставить руководителю и собственнику компании отчеты о результатах работы компании за I полугодие 2019 г. исходя из типовых форм регламентированной отчетности — баланса и формы 2 к нему.

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

Чтобы преобразовать это огромное количество цифр в нужный нам формат, создадим дополнительно в книге Excel несколько листов:

• «Структура баланса» — для расчета динамики структуры агрегированных показателей имущества компании и источников его финансирования;
• «Оборотный капитал» — для определения размера чистого оборотного капитала компании;
• «Безубыточность» — для расчета точки безубыточности и запаса финансовой прочности на начало и конец отчетного периода;
• «ФинПоказатели» — для расчета комплекса финансовых показателей и коэффициентов, характеризующих состояние компании;
• «Сводные показатели» — для визуализации обобщенной динамики финансового положения компании по итогам рассматриваемого периода.

Следующий этап построения управленческой отчетности — заполнение созданных на этих листах отчетных форм данными из представленных выше финансовых отчетов — баланса и формы 2 к нему.

Заполнение управленческих отчетов в Excel данными о работе компании

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

Начнем с первого из отчетов — «Структура баланса»:

• в колонках отчета «01.01.2019» и «01.07.2019» по сумме пропишем ссылки на ячейки с показателями листа «Баланс»;
• в колонках отчета «01.01.2019» и «01.07.2019» по процентам пропишем расчет доли значения каждой из строки актива или пассива в общей их сумме;
• в колонке «Динамика» пропишем разность между значениями колонок «01.07.2019» и «01.01.2019».

Полученный отчет представлен в табл. 3.

На основе данных этого отчета уже можно легко увидеть, что за I полугодие 2019 г. у компании «Альфа» в структуре активов увеличилась доля дебиторской задолженности и уменьшилась доля внеоборотных активов и запасов ТМЦ.

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

А вот анализ структуры пассивов компании показывает, что оборотные активы увеличились за счет роста кредитов и займов, что создает риски снижения финансовой устойчивости.

Далее переходим к отчету «Оборотный капитал»: заполним ячейки по строкам «Внеоборотные активы», «Инвестированный капитал»» «Оборотные активы» и «Краткосрочные пассивы» ссылками на соответствующие разделы листа «Баланс» и пропишем по строке «Чистый оборотный капитал» расчетную формулу «Оборотные активы-Краткосрочные пассивы».

А. А. Гребенников
финансовый директор ООО «Ипатовский пивзавод»

Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 9, 2019.

Создание красивого отчёта

Допустим у нас имеется вот такая база данных по продажам, из которой нужно сделать отчёт:

Вы создали небольшую сводную таблицу (Вставка – Сводная таблица):

Но вашему начальнику не нравится внешний вид отчёта, и он хочет видеть что-то похожее на это:

То есть мы имеем несколько ощутимых трудностей:

1. Исходный внешний вид сводной таблицы не подходит — дизайн отчёта должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). «Дорабатывать напильником» дизайн сводной — долгий и мучительный процесс. И не факт, что красота не слетит после пересчёта и обновления.

2. Из всей сводной для отчёта вам нужны не все данные, а только конкретные модели Ford по Питеру — придётся руками фильтровать.

3. Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зелёных ячейках, но среднее по месяцу в итогах — сводная так не умеет.

4. Полученные в сводной результаты — ещё не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе, или делается весьма мучительно и долго с помощью вычисляемых полей и объектов.

5. Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

Таким образом, перед нами стоит задача вытащить данные из сводной таблицы в другую таблицу — нужной нам конструкции, с дополнительными формулами и корпоративным дизайном. Сделать это можно разными способами.

1 способ. Прямая ссылка на ячейку в сводной

Это, что называется, решение проблемы «в лоб». Сделаем на отдельном листе заготовку отчёта:

Теперь в ячейку D7 можно вручную прописать ссылку:

Где Лист1 – имя листа со сводной таблицей, а B8 – нужная нам ячейка в сводной с данными по продажам Ford Fiesta за январь.

При внешней простоте и очевидности у этого способа есть две проблемы:

1. Если сортировка моделей и дат в нашем красивом «отчёте для шефа» отличается от сводной, то скопировать созданную ссылку не получится и придётся делать их для каждой модели автомобиля персонально. А если нужно извлечь много данных, то придётся делать много ссылок вручную.

2. Завтра, после обновления, структура сводной таблицы может измениться — например, Fiesta может оказаться уже не третьей, а седьмой строкой, Focus переехать во вторую и т.д. И тогда все ссылки придётся переделывать.

2 способ. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.

Чтобы её использовать, убедитесь, что при выделении любой ячейки сводной таблицы на вкладке Анализ (Analysis) или Параметры (Options) в выпадающем списке Параметры (Options) включена галочка Создать GetPivotData:

Теперь выделите первую ячейку зелёного диапазона, введите знак «равно» и щёлкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

(«Выручка»;’Сводная для отчета’!$A$4;

Давайте разберём её подробно:

1. Первый её аргумент («Выручка») – это имя извлекаемого поля.

2. Второй (Лист1!$A$4) — это адрес первой ячейки сводной таблицы, откуда мы берём данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать, из какой именно нужно вытащить число.

3. Все остальные аргументы начиная с третьего – это попарно название поля и его значение, т.е. в нашем случае это имя модели (Наименование=»Fiesta») и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто «январь».

У этой функции есть несколько серьёзных преимуществ перед обычной ссылкой на B8, которая приводила бы, на первый взгляд, к тому же результату. Главный плюс в том, что если завтра после обновления в сводной таблице изменится количество строк/столбцов или Ford Fiesta станет не третьей, а пятой строкой, то нам об этом волноваться уже не придётся – функция корректно извлечёт нужное нам значение. Достаточно только обновить сводную правой кнопкой мыши – и наша красивая форма отчёта «для шефа» пересчитается автоматически.

Далее, замените в формуле «Fiesta» на $С7 (т.е. на ячейку с названием модели), а единичку на D$5 (т.е. ячейку с номером месяца) и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зелёные ячейки.

(«Выручка»;’Сводная для отчета’!$A$4;»Наименование»;

Функция извлечёт из сводной нужные нам данные, заполнив нашу корпоративную форму отчёта.

Теперь с данными в диапазоне D7:F10 можно работать как с обычными формулами, а не как со сводной таблицей с её жёсткими ограничениями. Дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д. Как украсить документ можно посмотреть здесь Минидиаграммы в ячейках

Формула для прогноза: =ПРЕДСКАЗ($G$5;D7:F7;$D$5:$F$5)

Формула для среднего значения: =СРЗНАЧ(D7:D10)

Формула для динамики: =D13/D14-1

Ссылка на файл в комментариях. Желаю всем без проблем сдать отчёты)) Кто знает способы лучше, можете поделиться опытом в комментариях.

Графический отчет о финансовых результатах фирмы скачать в Excel

Благодаря стандартным инструментам программы Excel можно создавать красивые графические отчеты из скучных монотонных таблиц с данными. В таких графических отчетах можно применять элементы управления делая их еще и интерактивными. При этом нет необходимости прибегать к макросам VBA. Достаточно лишь выбрать элементы управления значениями на вкладке «РАЗРАБОТЧИК» в группе инструментов «Элементы управления»-«Вставить». Рассмотрим подробнее графический отчет, а в конце обзора можно скачать готовый пример в файле Excel.

Как сделать графический отчет без использования макросов в Excel

Как видно из анимированного *.gif изображения выше, для интерактивного взаимодействия с данными отчета используются встроенные элементы управления значениями Excel (2 счетчика и 2 флажка). Все эти и другие элементы расположены на вкладке панели инструментов «РАЗРАБОТЧИК»:

Не все, но большинство этих элементов управления в своих свойствах имеют опцию «Связь с ячейкой:», как показано ниже на рисунке:

Если мы указываем ссылку на нужную нам ячейку, то при взаимодействии с элементом мы изменяем значения в этой же ячейке. Условия и параметры изменений настраиваются в других опциях элемента.

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

Для примера смоделируем ситуацию. Допустим на листе «Data» у нас имеется таблица со статистическими значениями финансовых ежемесячных показателей по продажам фирмы:

Даже на основе этих примитивных данных можно уже построить множество отчетов. В данном примере нам следует визуально презентовать данные так:

1. Соотношение волатильности при динамических колебаниях средних значений ежемесячных продаж, расходов и прибыли.
2. Зависимость и корреляция наценки и маржи при разных усредненных показателях по продажам, расходам и прибыли.
3. Чем и как отличается наценка и маржа в цифрах и визуально.

Перед тем как выводить на график динамические изменения и придавать им пользовательскую интерактивность необходимо обработать и подготовить исходные данные с помощью формул. Все это реализуется на листе «Processing»:

Обратите внимание на ячейки A1,B1,D1 и E1. На эти ячейки ссылаются соответственные им элементы управления с листа «DASHBOARD». Они изменяют значения в этих ячейках, а дальше формулы лист «Processing» пересчитывают свои значения ссылаясь на эти же ячейки. В результате все графики и диаграммы на листе «DASHBOARD» автоматически перерисовываются относительно изменений данных в результатах вычисления формулами. Поэтому как на эти же результаты ссылаются графики и диаграммы.

Таким образом мы получаем программу в виде интерактивного отчета. Данный шаблон графического отчета в Excel мы можем смело называть программой, не смотря на то что при его создании мы не использовали язык программирования макросов VBA. Ведь суть любой программы заключается в обработке данных, при этом неважно какого рода программы: игры, работа с базами данных, ERP или CRM системы и т.п. Каждая программа по своей природе на входе получает данные, а на выходе выдает результат ее обработки. А пользователям не важно на каких языках программирования и какими технологиями была создана программа и м важен качественный результат, а также чтобы все было быстро, просто и красиво. Ведь в этом и заключается магия.

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

Также можно отказаться от ряда графика показателей средних ежемесячных продаж, чтобы изменить смысловой дизайн областей на вкладке «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Тип»-«Изменить тип диаграммы».

В появившемся окне для рядов средних расходов и средней прибыли применить тип диаграммы «С областями и накоплением»:

Таким образом будут складывается значения этих двух показателей в третий показатель «Средние ежемесячные продажи»:

Прибыль + Расходы = Суммы продаж. И комбинируя формулы и графики данного шаблона можно создавать много других графических отчетов по этим же статистическим данным.

Скачать пример графического отчета в Excel

Проведем краткий обзор исходной версии шаблона графического отчета о финансовых результатах фирмы в Excel. А в конце можно скачать пример шаблона по ссылке на файл Excel для детального и практического ознакомления с принципами действия и формулами. На главном листе «DASHBOARD» находятся 2 основных блока визуализации данных деятельности фирмы за последние 5 лет (с 2020 по 2025 год).

1. В первом блоке находится график с областями и элементами управления графическим отчетом. С помощью интерактивных элементов управления можно переключатся по годам, перемещать курсор по месяцам и включать или отключать кривые показателей маржи и наценки:
2. Во-втором блоке расположены 3 диаграммы со средними показателями продаж, расходов и прибыли по каждому месяцу. При перемещении курсора на верхнем графике по месяцам данные диаграмм в нижнем блоке обновляются автоматически:

• первая диаграмма отображает соотношение суммы прибыли, расходов и продаж;
• вторая диаграмма показывает долю расходов в продажах;
• третья – долю прибыли.

Это достаточно простой пример, который наглядно показывает, как создавать графические интерактивные отчеты с анимацией визуализации данных без использования VBA программирования макросов в Excel. При желании и определенного уровня навыков можно создавать еще боле сложные и функционально красивые отчеты, чем этот:

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