Как сделать факторный анализ в excel пошагово
Обновлено: 07.07.2024
На днях приезжала моя теща и попросила помочь ей с построением достаточно замороченных диаграмм в Excel’е (для презентации). Опыт оказался интересным и которым я, собственно, хочу поделиться.
Итак, имеем два значения – одно плановое, второе проектное (или базовое и отчетное) и имеем значения отклонения факторов. Задача: построить в Excel красивую диаграмму отображения этих факторов.
Рис.0. Окончательный результат.
Создаем в Excel таблицу, в которой у нас находятся необходимые данные (см.рис.1).
Рис.1. Исходные данные
После этого разносим их следующим образом (рис.2)
Рис.2. Подготовка данных
Теперь подпишем столбцы – столбец I – Значение, далее – Основа, далее Влияние фактора (рис.3).
Рис.3. Названия столбцов.
В качестве базовой диаграммы мы будем использовать либо гистограмму либо линейчатую с наполнением.
Рис.4. Используемые типы диаграмм
Теперь поясню на рис.5 что я имею в виду под основой – это такое значение некоторого ряда которое позволит построить нам диаграмму максимально точно.
В вычислении значений этого ряда поступаем следующим образом:
1. Значение первой основы (сразу после базового значения) принимаем равным либо базовому значению (если первый фактор имеет позитивное влияние) либо (базовое значение – величина влияния) – если фактор имеет негативное влияние.
2. Для последующих основ применяется та же схема. Если значение фактора положительное, то за основу берем результирующее значение, полученное на предыдущем факторе. Если же отрицательное, то берем (результирующее – абсолютное значение негативного фактора).
Что такое основа легко понять по рис.5.
Ту величину, которую я назвал “Влияние фактора” вычисляем как значение изменения фактора по модулю (абсолютное значение) с помощью функции ABS() – рис.6.
Рис.6. Вычисленные значения “Влияния фактора”
Далее по описанному выше алгоритму рассчитываем значения основы для каждого фактора.
Для первой основы используются следующая функция:
Для всех последующих:
Ахтунг! Не забывайте про правила сложения – если я говорю “плюс значение”, это значит, что подразумевается не абсолютное значение, а позитивное или негативное. Т.е. для третьего фактора получим следующую логику:
Значение изменения фактора меньше нуля, следовательно берем сумму предыдущего результирующего значения и значения изменения фактора, т.е. основа будет равна 170+(-30)=170-30=140.
Результирующее значение вычисляется по формуле:
=ЕСЛИ(L6>0;J6+L6;J6) – т.е. если изменения фактора позитивное, то результирующим значением будет сумма предыдущего результирующего значения и величины изменения фактора, а в противном случае – просто значение основы. Далее переходим уже непосредственно к построению диаграммы. Выделяем ячейки от названия категорий до столбца “Влияние фактора” включительно.
Рис.7. Выделяемая область.
И вставляем необходимый тип диаграммы (в данном случае – гистограмму).
Рис.8. Полученный результат
Дальше наводим красоту – переносим на новый лист диаграмму и заодно поправляем мою ошибку в выборе исходных данных (Отчетное значение принимаем 160, а не 150).
Удаляем вертикальную ось, удаляем основные вертикальные и горизонтальные линии осей и у нас получается нечто вроде рис.9.
Дальше в свойствах ряда изменяем боковой зазор до 10% и ряду “Основа” выставляем отсутствие заливки и линий – т.е. делаем его невидимым.
В свойствах горизонтальной оси также поставим “Нет линий” (рис.10).
Рис.10. Делаем ось невидимой
Далее добавляем рядам “Влияние фактора” и “Значение” подписи данных. Но получается маленькая нестыковка – даже в тех случаях, когда изменение фактора было отрицательным у нас выводятся положительные значения. Для этого дальше переходим обратно на лист 1 и выставляем соответственные форматы для позитивных и негативных значений.
Для позитивных: +0,0
Для негативных, соответственно: –0,0 – рис.11
Рис.11. Изменение формата чисел в столбце “Влияние фактора”.
Получившийся результат показан на рис.12
Рис.12. Подписи данных после изменения формата
Как видим, уже все изменения отображаются логически верно. Остался маленький штришок – находим точки ряда с негативным изменением и изменяем им цвет заливки на красный, а также меняем цвета подписей данных для этого ряда для большей наглядности (рис.13).
Рис.13. Окончательный результат.
Мы получили симпатичную диаграммку, которую не стыдно вставить в презентацию или в документ.
Читайте также: