Кол-во просмотров с 04.01.24г. :: 132
Доброго времени суток всем. Мы сегодня записываем второе приложение к занятию первому по финансовому моделированию. И наше приложение сегодня будет касаться выпадающих списков в Excel. Называется оно «Организация системы выпадающих списков в финансовой модели». Прежде чем мы перейдём непосредственно к изложению, хочу сказать следующее. Завтра, скорее всего завтра, выйдет анонс ко второму занятию по финансовому моделированию, и в рамках этого анонса будут даны некоторые пояснения, почему прошёл почти год с того момента, как было записано первое занятие, и с сегодняшним моментом, когда мы решили всё это дело продолжать. Поэтому ожидайте, завтра-послезавтра выйдет второе занятие, ну а перед ним или там почти, может быть, с разницей в несколько часов будет представлен анонс. В нем просто будет немножко рассказано про общую концепцию, которую мы поменяли за год, общую концепцию вот этому курсу, который сейчас перед вами. Надеюсь, что будет интересно. Ну, а сейчас мы переходим к выпадающим спискам. Значит, выпадающие списки в Excel. Ну, мы видим, что перед нами файл. которые мы создавали на первом занятии. По крайней мере, тем, кто просмотрел первое занятие, пожалуй, он знаком. А тем, кто уже скачивал у нас на сайте финансовые модели четвертого поколения, совсем, наверное, знакома эта картинка с этими кнопочками сверху. Но сегодня мы поговорим о том, как создать достаточно гибко настраиваемые универсальные выпадающие списки. Ну, во-первых, как всегда, начинаем с простых вещей. Опять же, поскольку речь идет о финансовых моделях и о выпадающих списках, применительных финансовым моделям, то, как всегда, с очевидных вещей. Если у нас есть, например, какие-то парочка ячеек, парочка выделенных ячеек в рамках финансовой модели. Давайте сделаем сразу же побольше shift. Как-то это получше все выглядело. Вот таким вот образом, например, вот так. Итак, если... Давайте вот здесь начнем создавать. Если у нас есть Две ячейки, в которых мы говорим, что в эти ячейки вносятся те или иные исходные значения финансовой модели. Допустим, 2 и 2. А наша финансовая модель выдает результат. Сумма. Мы пишем сумма. Сумма равна. И мы желаем сложить. вот эти вот два заданных значения напишем равняется ячейка b 9 b 8 b 8 плюс ячейка b 10 соответственно получаем 4 вот она простейшая финансовая модель мы сюда ставим 5 получаем здесь 5 плюс 2 7 ставим сюда 23 получаем 28 и так Как мы уже, в общем-то, говорили на первом занятии, финансовая модель состоит из трех частей. Это вводные, исходные данные, которые мы меняем вручную. Это функционал, который производит те или иные операции над нашими вводными данными. В данном случае наш функционал – это вот этот плюсик. То есть суть финансовой модели состоит в сложении двух каких-то вот этих вот чисел наперед-задом. И, соответственно, третья часть нашей финансовой модели – это этот отчет. Сумма равна, и мы видим результат финансовой модели. Вся штука в том, что если мы, допустим, скажем, что… А что, если мы не позволим какие-то значения, желаем не позволить пользователю задавать те или иные значения, например, вот в эту вот ячейку B8. И, ну, допустим, мы скажем, что давайте сделаем так, что вот в эту ячейку мы задаем только числа 5, 10, 15, 20, 25. Пять чисел. То есть у нас есть, ну, давайте здесь вот напишем 5, 10, 15, 20. и 25. И суть нашей модели состоит в том, вот по каким-то причинам, да, вот у кого-то возникла такая задача, что он говорит, что мы можем вот в этой ячейке выбирать только вот эти вот пять чисел, а вот в эту ячейку ставить любое в качестве исходного значения нашей финансовой модели, ну вот такой вот элементарной. Как это сделать? Ну, конечно же, можно вот здесь вот написать. Написать, что в ячейку B8 ставим только значения 5, 10, 15, 20 и 25. Согласитесь, как-то это будет странно. Мы даже можем написать вот следующую вещь, например, равно. Если в этой ячейке ячейка B8, допустим, она не равна, не равна, ну, допустим, вот 5, для всех остальных будет все то же самое, то тогда мы пишем ошибка. Тогда мы выдаем результат ошибка, а иначе, а иначе, Мы B8, прибавляем B10 и выдаем результат. Соответственно, допустим, если мы вместо 5 здесь единичку поставили, пишем ошибка. Вот появляется слово ошибка. Также, естественно, хотелось бы сделать совсем просто. Сделать так, чтобы пользователь просто не мог сюда поставить значение, которое выходит за рамки дозволенных, так сказать, значений. Ну, понятно, что такая задачка, она решается с помощью выпадающих списков. Естественно, любой из вас, наверное, скажет, что в чем тут проблема. Я удалю. Здесь, соответственно, вот это мы уберем. Обратно сделаем нашу функцию сложения. Отметим. В чем сложность? Заходим в данные. Проверка данных. У нас появляется табличка, в которой мы выбираем тип данных. В данном случае любое значение. Мы говорим, не, не любое значение, то, что в списке. Пишем список и источник. Курсор ставим в ячейку с источником и просто-напросто выделяем тот самый источник. Что такое источник? Это просто диапазон ячеек, внутри которых записаны те значения, которые допустимы для данной ячейки исходных данных нашей модели. Обратим внимание на вот этот вот синтаксис. Написано равно $F$1, $F$1. Ну, соответственно, это вот где у нас пятерочка находится, то есть это начало нашего диапазона. И $F$5, ячейка F5, она завершает наш диапазон допустимых значений. Нажимаем на OK. И понятно, что когда мы наводим мышку на вот такую ячейку, справа у нас появляется кнопочка, и в этой кнопочке появляется выпадающий список допустимых значений. То есть мы выбираем 15, получается 15 плюс 20 – 38. Ну и слава богу, вот, соответственно, вроде казалось бы, все хорошо. Но теперь давайте посмотрим, а что будет, если мы скопируем эту ячейку, допустим, вот сюда куда-нибудь ставим. И что мы видим? Мы видим, что у нас выпадающий список пуст, во-первых. То есть при переносе выпадающего списка у нас, соответственно, он пропадает. То есть здесь у нас ничего нет. Если мы здесь удалим значение, то тогда выпадающий список пустой. Кстати, чтобы посмотреть, куда ссылается данный выпадающий список, мы идем в данные, идем проверка данных, и вот видим, равняется. F1 и F15 все те же самые, но только уже на этом листе. А в этом листе у нас F1 и F15, вот оно здесь загорается, это просто пустые ячейки, здесь ничего нет. Соответственно, если мы какой-то выпадающий список придумали, но понятно, что финансовая модель обычно состоит из множества листов, и крайне неудобно, если на каждом листе придется делать свои какие-то выпадающие списки, тогда, в общем-то, зачем они нужны? Конечно же, имеет смысл один раз забить некоторый список, да, вот выпадающие списки у нас здесь написано, да, там объявить какой-нибудь список, да, Напишем список номер один и, соответственно, взять и внутри этого списка какие-то значения написать. Значение 1.1, значение 1.2 и так далее. Значение 1.3. Один раз задав этот список, мы могли его переносить в разные листочки нашей модели. Теперь давайте еще одну ситуацию посмотрим. Если мы, например, сюда зайдем, проверка данных, список, и давайте мы сделаем следующую вещь. Прямо внутри этого выражения доллары уберем. Доллары уберем. Доллары что? Это значит, при копировании они у нас держат либо строку, либо столбец, вот эти доллары. А так они ничего не держат. Получается, когда мы пишем окей, мы, например, копируем в соседнюю ячейку. И что у нас получается? У нас выпадающий список, здесь в нем ничего нет. Давайте посмотрим. На что настроен данный выпадающий список? Мы видим, что данный выпадающий список настроен на ячейки G1 и G5. Мы скопировали соответствующую ячейку целиком, и диапазон выпадающего списка, который здесь находился, он также скопировался в соседнюю ячейку. Если мы, например, здесь напишем 1, 2, 3, 4, 5, то тогда у нас внутри данного выпадающего списка появится 1, 2, 3, 4, 5. То есть что это означает? Вот это очень важный момент. Это означает, что формула, которая у нас задает диапазон, внутри которого находятся допустимые значения выпадающего списка, Эта формула действует как любая обычная формула Excel. При этом сама формула, давайте посмотрим, как она устроена, написано равно, а потом записывается просто-напросто сам диапазон. Что это означает? Это означает, что если, представьте, мы сюда сейчас напишем равняется, вот у нас есть вкладочка с названием lists. Давайте напишем диапазон лист с восклицательный знак. Вспоминаем синтекс, что такое лист с восклицательный знак. Это значит, что мы с помощью этой формулы уходим во вкладку лист. А потом дальше внутри вкладки лист мы выбираем вот такой диапазон. Мы помним там F, G и так далее, столбцы, они у нас близко к левому краю. Их можно там не увидеть. Давайте прямо напишем вот так вот АА1.