Диаграммы в Excel для факторного анализа
На днях приезжала моя теща и попросила помочь ей с построением достаточно замороченных диаграмм в Excel’е (для презентации). Опыт оказался интересным и которым я, собственно, хочу поделиться.
Итак, имеем два значения – одно плановое, второе проектное (или базовое и отчетное) и имеем значения отклонения факторов. Задача: построить в Excel красивую диаграмму отображения этих факторов.
Создаем в Excel таблицу, в которой у нас находятся необходимые данные (см.рис.1).
Теперь подпишем столбцы – столбец I – Значение, далее – Основа, далее Влияние фактора (рис.3).
В качестве базовой диаграммы мы будем использовать либо гистограмму либо линейчатую с наполнением.
Теперь поясню на рис.5 что я имею в виду под основой – это такое значение некоторого ряда которое позволит построить нам диаграмму максимально точно.
В вычислении значений этого ряда поступаем следующим образом:
1. Значение первой основы (сразу после базового значения) принимаем равным либо базовому значению (если первый фактор имеет позитивное влияние) либо (базовое значение – величина влияния) – если фактор имеет негативное влияние.
2. Для последующих основ применяется та же схема. Если значение фактора положительное, то за основу берем результирующее значение, полученное на предыдущем факторе. Если же отрицательное, то берем (результирующее – абсолютное значение негативного фактора).
Ту величину, которую я назвал “Влияние фактора” вычисляем как значение изменения фактора по модулю (абсолютное значение) с помощью функции ABS() – рис.6.
Далее по описанному выше алгоритму рассчитываем значения основы для каждого фактора.
=ЕСЛИ(L6>0;I5;I5+L6) — т.е. если первый фактор больше нуля, то берем базовое значение, в противном случае берем базовое + значение изменения фактора (в нашем примере получается просто 100).
=ЕСЛИ(L7>0;M6;M6+L7) — т.е. если фактор больше нуля, то берем полученное на предыдущем факторе результирующее значение, в противном случае берем базовое + значение изменения фактора.
Ахтунг! Не забывайте про правила сложения – если я говорю “плюс значение”, это значит, что подразумевается не абсолютное значение, а позитивное или негативное. Т.е. для третьего фактора получим следующую логику:
Значение изменения фактора меньше нуля, следовательно берем сумму предыдущего результирующего значения и значения изменения фактора, т.е. основа будет равна 170+(-30)=170-30=140.
=ЕСЛИ(L6>0;J6+L6;J6) – т.е. если изменения фактора позитивное, то результирующим значением будет сумма предыдущего результирующего значения и величины изменения фактора, а в противном случае – просто значение основы. Далее переходим уже непосредственно к построению диаграммы. Выделяем ячейки от названия категорий до столбца “Влияние фактора” включительно.
И вставляем необходимый тип диаграммы (в данном случае – гистограмму).
Дальше наводим красоту – переносим на новый лист диаграмму и заодно поправляем мою ошибку в выборе исходных данных (Отчетное значение принимаем 160, а не 150).
Удаляем вертикальную ось, удаляем основные вертикальные и горизонтальные линии осей и у нас получается нечто вроде рис.9.
Дальше в свойствах ряда изменяем боковой зазор до 10% и ряду “Основа” выставляем отсутствие заливки и линий – т.е. делаем его невидимым.
В свойствах горизонтальной оси также поставим “Нет линий” (рис.10).
Далее добавляем рядам “Влияние фактора” и “Значение” подписи данных. Но получается маленькая нестыковка – даже в тех случаях, когда изменение фактора было отрицательным у нас выводятся положительные значения. Для этого дальше переходим обратно на лист 1 и выставляем соответственные форматы для позитивных и негативных значений.
Рис.11. Изменение формата чисел в столбце “Влияние фактора”.
Как видим, уже все изменения отображаются логически верно. Остался маленький штришок – находим точки ряда с негативным изменением и изменяем им цвет заливки на красный, а также меняем цвета подписей данных для этого ряда для большей наглядности (рис.13).
Мы получили симпатичную диаграммку, которую не стыдно вставить в презентацию или в документ.
[expert_bq id=»1570″]Если в процессе создания диаграммы у вас возникнут проблемы с составлением правильной формулы, то вы можете воспользоваться готовыми шаблонами. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Как видите, при помощи excel можно строить графики для разных целей. При этом программа способна работать с гистограммами, круговыми и линейными диаграммами, а также областями данных, которые можно отредактировать под любые пользовательские запросы с помощью широкого спектра настроек.Как построить график в excel
Возможно, на Х оси не будет значений, а только номера на точках. Чтобы исправить эту недоработку, потребуется сделать подписи оси вашего графика. Просто кликните правой кнопкой, и нажмите на меню «Выбрать данные», после чего изменить для горизонтальной оси подписи. Тут выделите нужный диапазон. Теперь график обретает полноценный завершённый внешний вид.
[expert_bq id=»1570″]Чтобы сделать кривую по уравнению, необходимо также предварительно посчитать значения всех составляющих выражения, используя основные математические приемы, а затем с помощью точечной диаграммы построить график уже известным способом. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Диаграмма полностью редактируется, что позволяет придать ей тот вид, который нужен пользователю. При желании можно сделать круговую диаграмму, точечную, линейчатую и пр. В этом плане программа Excel довольно мощная и функциональная.
Отрицательные числа в Excel.
- Лучшие практики используются финансовыми аналитиками при финансовом моделировании, когда отрицательные значения отображаются красным цветом.
- Отрицательные числа возводятся в квадрат при определении дисперсии в анализе данных для устранения проблемы, представленной стандартным отклонением.
- Когда значение дисперсии отрицательное, оно умножается на -1, чтобы сделать его положительным.
- В VBA отрицательные числа используются для обозначения дат до 1900 года.
- Когда нам нужно единственное абсолютное значение отрицательного числа, для получения положительного значения применяется абсолютная функция.
К примеру, нужно показать не только число поступивших в вуз, но ещё и количество выпускников за тот же период. То есть появляется уже больше исходной информации в таблице. Хотя принцип создания графика не меняется. Зато есть смысл не удалять легенду.
Как построить диаграмму или график в Excel — инструкция по работе
Чтобы построить графики в Excel необходимо иметь данные, представленные в таблице. Для этого либо открываем уже готовый документ, либо создаем новый и вводим необходимые значения. Следует обратить внимание, что при создании таблицы в первый столбец, как правило, вводятся текстовые данные (названия, имена, даты и т.п.), а в следующие столбцы – числовые данные.
