Прил. 2 к занятию 1. Выпадающие списки финмодели

Приложение №2 к занятию 1 курса обучения финансовому моделированию в Excel.

Настройка универсальных выпадающих списков в Excel.

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

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

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

Смотрите видео
об организации системы выпадающих списков в финмодели

Во-первых, напомним, что такое выпадающий список в Excel.

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

Один из наиболее удобных и часто используемых приемов для организации ограничений на вносимые исходные данные – это явное указание набора тех значений, которые и только которые можно вносить в ячейку. Для реализации такого приема в Excel предусмотрена процедура создания, так называемых, выпадающих списков. Чтобы создать выпадающий список сначала необходимо создать этот самый список возможных значений на «просторах» Excel-файла, после чего с помощью настроек из подраздела «Данные/Проверка данных» верхнего горизонтального меню файла Excel все ячейки, предназначенные для ввода исходных данных со значениями из такого списка, «привязать» к созданному списку, путем непосредственного указания ссылки на диапазон ячеек, в котором записаны значения выпадающего списка.

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

Как видно из рисунка, название «минимальный период расчетов» этого выпадающего списка находится в строке 13 столбца I, а его значениями являются названия тех периодов времени, которые допустимы в финансовой модели.

Теперь если у разработчика финмодели возникает задача разместить в какой-либо вкладке Excel-файла будущей финансовой модели ячейку для ввода в качестве исходных данных минимального периода расчетов, то, наведя курсор мышки на эту ячейку, т.е. выделив ее, разработчик должен пройти в раздел «Данные» верхнего меню этого Excel-файла, выбрать там опцию «Проверка данных» и в появившемся окне «Проверка вводимых значений» в качестве «Типа данных» выбрать «Список», а в строке «Источник» задать ссылку на диапазон I16:I22 вкладки «Lists» с ранее подготовленным списком названий допустимых минимальных периодов расчета.

Для нас здесь является важным синтаксис записи ссылки на диапазон допустимых значений выпадающего списка:

=Lists!$I$16:$I$22

До восклицательного знака «!» обычно указывается название листа, после чего идет запись диапазона ячеек этого листа, где собраны все допустимые значения выпадающего списка – адреса начальной и конечной ячеек диапазона связываются посредством двоеточия «:». Значки доллара «$» служат для закрепления соответствующих столбцов или строк при копировании ячейки, для которой производится настройка выпадающего списка значений – чтобы ссылка на список допустимых значений «не съезжала» при копировании ячейки.

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

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

Первое. Если к списку допустимых значений во вкладке «Lists» добавить снизу дополнительное значение, которое, очевидно, будет содержаться в ячейке I23, то поскольку диапазон выпадающего списка нашей ячейки из листа «Periods» заканчивается ровно на ячейке I22, дополнительное значение из ячейки I23 листа «Lists» не попадет в выпадающий список нашей ячейки. В этом случае придется либо перенастраивать выпадающий список в нашей ячейке вкладки «Periods», либо сразу закладывать несколько дополнительных пустых ячеек в списке допустимых значений, но тогда, когда они все будут задействованы, то мы опять вернемся к нашей проблеме, либо помнить, что вставлять новые значения в диапазон допустимых значений всегда нужно до последней ячейки диапазона выпадающего списка.

Второе. Если в ячейке с выпадающим списком пользователь в какой-то момент выбрал некоторое значение из диапазона допустимых значений, а после этого через какое-то время внес изменения внутри диапазона, в нашем случае внутри ячеек из массива I16:I22 вкладки «Lists», такие, что изначально выбранного значения больше нет среди допустимых значений, то во-первых, понятно, что оно само не поменяется (такой возможности в Excel нет – ведь в этом случае необходимо понять какому новому значению соответствует старое), во-вторых, это очевидно приведет к ошибкам внутри расчетов финансовой модели.

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

Четвертое. Представьте себе, как было бы удобно, если, меняя название ячейки с выпадающим списком, сам выпадающий список менялся автоматически на тот, у которого в заголовке (например, в строке 13 вкладки «Lists») стоит это название, т.е. автоматически менялся бы диапазон допустимых значений в зависимости от того, какое название, совпадающее с одним из заголовков диапазонов выпадающих списков финмодели, указано в качестве названия ячейки с выпадающим списком.

Покажем, как легко можно справиться с перечисленными выше сложностями.

Для этого рассмотрим следующую функцию Excel:

ДВССЫЛ(«текстовая строка»),

которая возвращает ссылку, заданную текстовой строкой.

Теперь вспоминаем, как выглядит запись ссылки на «Источник» выпадающего списка – в нашем случае ссылка имеет следующую «текстовую строку» (см. выше):

“Lists!$I$16:$I$22”.

Давайте в «Источнике» сделаем запись:

=ДВССЫЛ(“Lists!$I$16:$I$22”)

Нажимаем «Ок» в окне «Проверка вводимых значений», и убеждаемся, что ничего не изменилось – наш выпадающий список работает также, как и ранее.

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

И смотрим, что у нас с выпадающим списком рассматриваемой ячейки в листе «Periods» – естественно ничего не поменялось, «доп. значение» из ячейки I23 вкладки «Lists» в списке не появилось – читатель с легкостью может это проверить самостоятельно. Это и понятно, ведь последняя ячейка диапазона у нас в «Источнике» явно прописана – I22. А что если вместо номера строки «22» в этой записи мы пропишем формулу, которая будет автоматически менять адрес последней ячейки диапазона допустимых значений выпадающего списка?

Например, поменяем запись в строке «Источник» окна «Проверка вводимых значений» на такую:

=ДВССЫЛ("Lists!$I$16:$I$"&(15+МАКС(Lists!$J:$J)))

Причем предварительно добавим в столбце J листа «Lists», который (столбец) находится справа от диапазона допустимых значений нашего выпадающего списка нумерацию значений из диапазона посредством формулы

=МАКС(J$15:J15)+1

размещенной в ячейке J16, напротив ячейки I15 с первым значением списка, и протянутой до последнего значения диапазона выпадающего списка.

В результате мы получим в нашей ячейке во вкладке «Periods» выпадающий список, у которого автоматически появляются вновь добавленные значения в список на листе «Lists», при условии, что при добавлении каждого нового значения, пользователь будет протягивать на соответствующие ячейки в столбце справа (в рассматриваемом примере – столбец J) от диапазона допустимых значений формулу нумерации значений списка.

А теперь реализуем изложенную идею в несколько более системном виде. Договоримся о том, чтобы все выпадающие списки финансовой модели находились во вкладке «Lists», их диапазон допустимых значений начинался с одной и той же для всех списков строки 16, а также о том, чтобы все названия выпадающих списков были различны и находились в виде записей в одной и той же строке 13.

Ну и самое главное! Каждому выпадающему списку в строке 15 поставим в соответствие ссылку на диапазон допустимых значений путем прописания формулы, которая на выходе отражает текстом эту самую ссылку на диапазон – непосредственно запись формулы читатель с легкостью сможет найти в материалах занятия 1 нашего курса обучения финмоделированию. Смотрим, что в итоге вышло:

Для завершения реализации нашей идеи универсальной настройки выпадающих списков финансовой модели в Excel нам осталось написать «универсальную» формулу для «Источника» данных ячеек с выпадающими списками, после чего добавить ее в строку «Источник:» окна «Проверка вводимых значений» подраздела «Данные/Проверка данных» Excel-файла. Сначала запишем формулу, потом разберем ее:

=ДВССЫЛ(ИНДЕКС(Lists!$15:$15;СУММЕСЛИМН(Lists!$1:$1;Lists!$13:$13;$H16)))

Внутри функции ДВССЫЛ у нас здесь находится функция ИНДЕКС, которая из строки 15 вкладки «Lists» (где у нас как раз-таки согласно принципу, который мы чуть ранее заложили, находятся текстовые записи ссылок на диапазоны всех выпадающих списков финмодели) находит и возвращает ссылку на диапазон значений того выпадающего списка, который находится в столбце с номером записанным в строке 1 листа «Lists», напротив которого в строке 13 записан заголовок выпадающего списка, совпадающий с названием ячейки, для которой мы настраиваем выпадающий список, а именно в рассматриваемом случае ячейки с адресом Q16, и которое опять же в нашем случае находится в ячейке H16 вкладки «Periods».

Теперь достаточно поменять в ячейке H16 название ячейки с заголовка одного выпадающего списка в нашем случае – это «минимальный период расчетов», на заголовок другого выпадающего списка, допустим, на «тип листа» (см. выше изображение с набором выпадающих списков листа «Lists»), и выпадающий список допустимых значений ячейки Q16 вкладки «Periods» автоматически меняется на значения выпадающего списка «типов листа», смотрите рисунок:

Тем самым, мы вполне себе изящно решили как первую, так и четвертую проблему из нашего списка в первой части настоящего приложения к занятию 1 курса обучения финансовому моделированию.

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

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

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

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

Следующие уроки: Прил. 3 к занятию 1. Условное форматирование