Динамический диаграмм в области финансового моделирования - манекены

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

Построение диаграммы данных, управляемых формулой

Загрузить файл 0901. xlsx. Откройте его и выберите вкладку с надписью 9-23, чтобы попробовать ее самостоятельно.

Изменение раскрывающегося списка.

Если вы спрячете данные в своем исходном листе, это не будет отображаться на диаграмме. Протестируйте это, сокрыв один из столбцов на листе Financials и убедитесь, что месяц исчез на диаграмме. Вы можете изменить параметры в разделе «Выбрать источник данных», чтобы отображать скрытые ячейки.

Связывание названий диаграмм с формулами

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

1. В ячейке A1 этой модели измените заголовок на следующее: = «Пятилетние стратегические затраты на прогнозирование для центра обработки вызовов» и «F1».

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

   Вместо амперсанда вы также можете использовать функцию CONCATENATE, которая работает очень сходно, объединяя отдельные ячейки вместе, или функция TEXTJOIN является новым дополнением к Excel 2016, которое объединит большие количества данных.

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

2. Щелкните название диаграммы.

   Эта часть может быть сложной. Убедитесь, что вы выбрали только название диаграммы.

3. Щелкните панель формул.
4. Тип =, а затем щелкните ячейку A1.
5. Нажмите «Ввод».

   Название диаграммы изменяется, чтобы показать, что находится в ячейке A1.

Связывание названий диаграмм с формулами.

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

Создание динамического текста

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

Создание кластерной диаграммы столбцов.

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

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

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

6. Выделите все данные, включая заголовки, и нажмите кнопку «Сортировка» (в разделе «Сортировка и фильтра» на вкладке «Данные» в ленте).

   Появится диалоговое окно Сортировка.

7. Сортировать по Реальные от самых маленьких до самых маленьких.

   

   Сортировка данных диаграммы.

   Очень просто испортить формулы при сортировке, поэтому убедитесь, что вы выделили все столбцы из столбцов A-G перед применением сортировки.

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

8. В ячейке A15 создайте формулу, которая будет автоматически подсчитывать, сколько позиций превышает бюджет.

   Вы можете сделать это с помощью формулы = COUNTA (G3: G12) -COUNT (G3: G12), которая вычисляет количество непустых ячеек в столбце G.

9. Вы можете видеть, что две позиции бюджет, поэтому преобразуйте его в динамический текст с помощью формулы = COUNTA (G3: G12) -COUNT (G3: G12) и «Элементы по бюджету. "

   

   Завершенный график с динамическим текстовым полем.
10. Вставьте текстовое поле в диаграмму, нажав кнопку «Текстовое поле» в группе «Текст» на вкладке «Вставка» на ленте.
11. Щелкните диаграмму один раз.

    Появится текстовое поле.

12. Внимательно выберите внешнюю часть текстового поля с помощью мыши, как и в последнем разделе при связывании заголовков диаграмм.
13. Теперь перейдите в панель формул и введите =.
14. Щелкните ячейку A15 и нажмите «Ввод».
15. При необходимости измените размер и поместите текстовое поле.
16. Протестируйте модель, изменив номера, чтобы больше элементов превышало бюджет, и следите за тем, чтобы комментарий в текстовом поле менялся.