Бизнес-аналитика в Excel: формируем и визуализируем данные

Сводные таблицы, лист прогнозов и другие техники анализа данных в Microsoft Excel, которые пригодятся вам в работе или учёбе.

Инструменты анализа Excel

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Анализ что-если.

Средства анализа «Что-если»:

  1. «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
  2. «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
  3. «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
  4. «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.

Другие инструменты для анализа данных:

Графики и диаграммы.

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

Вызовы 2020 для руководителей

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

  • У кого делать закупку и по какой цене?

  • Где и кому продавать?

  • Давать ли скидки клиентам, работают ли они вообще?

  • Где и как рекламироваться?

В “коронавирусном” 2020 этот ряд пополнили новые вызовы:

  • Выгоднее открываться или отложить запуск?

  • Если подождать, то сколько, и как проводить перезапуск?

  • Как переместить каналы продвижения и сбыта в онлайн?

  • Как оптимизировать доставку?

  • Что делать с людьми, сокращать штат или платить за простой?

В России для многих отраслей ещё не наступила эпоха, когда решения принимаются на основе анализа больших данных. Иногда малый бизнес даже не использует компьютер, все пишется на бумаге, а в расчетах помогает калькулятор. Интуитивный выбор – это обычная практика, и для ряда задач это даже нормально. Однако, вся ответственность за результат при этом ложится на руководителя, а его рабочий день удлиняется до 10+ часов.

Опереться на один лишь опыт и здравый смысл становится сложно, когда:

  • Информация, важная для принятия решения, уже не умещается в голове.

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

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

  • Когда интуитивное решение не очевидно.

Показательной здесь будет история из Петровских времен о металлургах Демидовых. Ее разбирает в своем телеграм-канале Эдуард Бабушкин, эксперт по кадровой аналитике. Акинфий Демидов пригласил на завод немецких мастеров, чтобы обучить своих. Спустя какое-то время он решил проверить эффективность обучения. Немцы были в одной группе, русские мастера в другой. Обеим группам дали одинаковое количество руды (условно 100 тонн). Русские из этой руды наплавили 12 тонн меди, а немцы 10 тонн.

Вопрос: можно ли сказать, что русские выплавили статистически значимо больше меди? Иными словами: это была случайность или русские – действительно молодцы?

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

Анализ бюджета доходов и расходов (БДР) по месяцам

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

Подробнее >>

Переключение рисунков с помощью срезов и сводных таблиц

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

Подробнее >>

Если вы хотите научиться консолидировать бюджеты с использованием Power Query и выполнять сложные расчеты на основе больших массивов данных с помощью DAX-формул, приходите к нам на курсы «Бюджетирование с Business Intelligence»

Пример dashboard в Excel

Сводные таблицы в анализе данных

Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.

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

  1. Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
  2. Откроется диалоговое окно «Создание таблицы».Создание таблицы.
  3. Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.

Таблица данных.

К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).

Конструктор.

Составить отчет можно с помощью «Сводной таблицы».

  1. Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» – «Таблицы» – «Сводная таблица»).
  2. В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
  3. Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.Мастер сводных таблиц.
  4. Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.

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

Диаграммы с переключением для БДР

Предлагаю вам с одной стороны компактный, а с другой – универсальный вариант визуализации данных БДР (бюджета доходов и расходов).

Подробнее >>

Анализ ключевых показателей и EVA в Excel

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

Василий18:50:00 Add Comment

При работе с большими книгами в Excel, часто возникает необходимость скрыть лишние листы, что бы они не мешались в работе. Но что делать, ес…

Далее…

3. Лист прогнозов

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

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

Как работать

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Панель мониторинга 13 месяцев в году

Как показывать данные, чтобы было понятно, запоминалось и чтобы был план факт, прошлый месяц и тот же месяц прошлого года? Для этого нужно построить диаграмму за 13 месяцев.

Подробнее >>

Бюджет доходов и расходов в Excel

Анализ предприятия в Excel: примеры

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

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

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

Конструктор

При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.

Можно настроить:

  • промежуточные итоги:
    • не показывать;
    • показывать все итоги в нижней части;
    • показывать все итоги в заголовке.
  • общие итоги:
    • отключить для строк и столбцов;
    • включить для строк и столбцов;
    • включить только для строк;
    • включить только для столбцов.
  • макет отчета:
    • показать в сжатой форме;
    • показать в форме структуры;
    • показать в табличной форме;
    • повторять все подписи элементов;
    • не повторять подписи элементов.
  • пустые строки:
    • вставить пустую строку после каждого элемента;
    • удалить пустую строку после каждого элемента.
  • параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
    • заголовки строк;
    • заголовки столбцов;
    • чередующиеся строки;
    • чередующиеся столбцы.
  • настроить стиль оформления элементов.

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

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

Помимо этого, при желании, вы можете создать свой собственный стиль оформления.

Анализ прибыли «Что-если»

Решение для оценки влияния факторов на прибыль поможет в принятии решений по увеличению прибыли.

Подробнее >>

KPI-панель эффективности продаж в Excel

Видеоинструкция

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

Если вам по работе или учёбе приходится погружаться в океан цифр и искать в них подтверждение своих гипотез, вам определённо пригодятся эти техники работы в Microsoft Excel. Как их применять — показываем с помощью гифок.

Юлия Перминова

Тренер Учебного центра Softline с 2008 года.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...