Кол-во просмотров с 04.01.24г. :: 893
Доброго времени суток всем. Мы сегодня записываем второе приложение к занятию первому по финансовому моделированию. И наше приложение сегодня будет касаться выпадающих списков в Excel. Ну, называется оно «Организация системы выпадающих списков в финансовой модели». Прежде чем мы перейдём непосредственно к изложению, хочу сказать следующее. Завтра, скорее всего завтра, выйдет анонс ко второму занятию по финансовому моделированию, и в рамках этого анонса будут даны некоторые пояснения почему прошёл почти год с того момента, как было записано первое занятие, и с сегодняшним моментом, когда мы решили всё это дело продолжать. Поэтому ожидайте, завтра-послезавтра выйдет второе занятие, ну а перед ним или там почти, может быть, с разницей в несколько часов будет представлен анонс. В нем просто будет немножко рассказано про общую концепцию, которую мы поменяли за год, общую концепцию вот этому курсу, который сейчас перед вами. Ну, надеюсь, что будет интересно. Ну, а сейчас мы переходим к выпадающим спискам. Значит, выпадающие списки в Excel. Ну, мы видим, что перед нами файл. которые мы создавали на первом занятии. По крайней мере, тем, кто просмотрел первое занятие, пожалуй, он знаком. А тем, кто уже скачивал у нас на сайте финансовые модели четвертого поколения, совсем, наверное, знакома эта картинка с этими кнопочками сверху. Но сегодня мы поговорим о том, как создать достаточно гибко настраиваемые универсальные выпадающие списки. Ну, во-первых, как всегда, начинаем с простых вещей. Опять же, поскольку речь идет о финансовых моделях и о выпадающих списках, применительных финансовым моделям, то, как всегда, с очевидных вещей. Если у нас есть, например, какие-то парочка ячеек, парочка выделенных ячеек в рамках финансовой модели. Давайте сделаем сразу же побольше shift. Как-то это получше все выглядело. Вот таким вот образом, например, вот так. Итак, если... Давайте вот здесь начнем создавать. Если у нас есть... Две ячейки, в которых мы говорим, что в эти ячейки вносятся те или иные исходные значения финансовой модели. Допустим, 2 и 2. А наша финансовая модель выдает результат. Сумма. Мы пишем сумма. Сумма равна. И мы желаем сложить. Вот эти вот два заданных значения, напишем, равняется ячейка B8 плюс ячейка B10. Соответственно, получаем 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 плюс 28. Ну и слава богу, вот, соответственно, вроде казалось бы, все хорошо. Но теперь давайте посмотрим, а что будет, если мы скопируем эту ячейку, ну, допустим, вот сюда куда-нибудь ставим. И что мы видим? Мы видим, что у нас выпадающий список пуст, во-первых. То есть при переносе выпадающего списка у нас, соответственно, он пропадает. То есть здесь у нас ничего нет. Если мы здесь удалим значение, то тогда выпадающий список пустой. Кстати, чтобы посмотреть, куда ссылается данный выпадающий список, мы идем в данные, идем проверка данных, и вот видим, равняется. F1 и F15 все те же самые, но только уже на этом листе. А в этом листе у нас F1 и F15, вот оно здесь загорается, это просто пустые ячейки, здесь ничего нет. Соответственно, если мы какой-то выпадающий список придумали, но понятно, что финансовая модель обычно состоит из множества листов, и крайне неудобно, если на каждом листе придется делать свои какие-то выпадающие списки. Тогда, в общем-то, зачем они нужны? Конечно же, имеет смысл один раз забить некоторый список, да, вот выпадающие списки у нас здесь написано, да, там объявить какой-нибудь список, да, Напишем список номер 1 и, соответственно, взять и внутри этого списка какие-то значения написать. Значение 1.1, значение 1.2 и так далее. Значение 1.3. Один раз задав этот список, мы могли его переносить в разные листочки нашей модели. Теперь давайте еще одну ситуацию посмотрим. Если мы, например, вот сюда зайдем, проверка данных, вот список, и давайте мы сделаем следующую вещь. Прямо вот внутри вот этого выражения доллары вот эти вот уберем. Доллары уберем. Доллары что? Это значит, при копировании они у нас держат либо строку, либо столбец, вот эти доллары, а так они ничего не держат. Получается, когда мы пишем окей, мы, например, копируем в соседнюю ячейку, и что у нас получается? У нас выпадающий список, здесь в нем ничего нет. Давайте посмотрим. На что настроен данный выпадающий список? Мы видим, что данный выпадающий список настроен на ячейки G1 и G5. Ну и понятно, мы скопировали соответствующую ячейку целиком, и диапазон вот этого выпадающего списка, который здесь находился, он также скопировался в соседнюю ячейку. Если мы, например, здесь напишем 1, 2, 3, 4, 5, то тогда у нас внутри данного выпадающего списка появится 1, 2, 3, 4, 5. То есть что это означает? Вот это очень важный момент. Это означает, что формула, которая у нас задает диапазон, внутри которого находятся допустимые значения выпадающего списка, Эта формула действует как любая обычная формула Excel. При этом сама формула, давайте посмотрим, как она устроена, написано равно, а потом записывается просто-напросто сам диапазон. Что это означает? Это означает, что если, представьте, мы сюда сейчас напишем равняется, вот у нас есть вкладочка с названием lists. Давайте напишем диапазон ЛИС, ТС, восклицательный знак. Вспоминаем синтакси, что такое ЛИС, восклицательный знак. Это значит, что мы с помощью этой формулы уходим во вкладку ЛИСТС. А потом дальше внутри вкладки ЛИСТ мы выбираем вот такой диапазон. Мы помним там F, G и так далее, столбцы, они у нас близко к левому краю. Их можно там не увидеть. Давайте прямо напишем вот так вот. АА1. АА1 двоеточие АА5. То есть мы здесь производим запись диапазона, который состоит из пяти ячеек. Первая ячейка находится по адресу АА1, а последняя АА5. Хорошо, давайте здесь выделить и сделаем. Уберем пустую. Понятно, что здесь пока что ничего нет. Почему? Ну, заходим в лист. Вот у нас, соответственно, диапазон ячеек АА1, АА5. Вот они, они здесь. Ну, и давайте здесь напишем, там, допустим, 5, 6, 7, 8, 9. Цифры сюда вобьем. Переходим сюда, смотрим. 5, 6, 7, 8, 9. То есть мы... Просто-напросто записали диапазон, и если внутри этого диапазона появились какие-то непустые значения, то мы их видим в рамках данного выпадающего списка. Если мы здесь напишем буквы какие-нибудь, A, B, C, D, E, F, G, H, O, P, R, Ну, здесь, наверное, плохо вам видно. Ну, сейчас мы это увидим вот здесь. То есть мы в каждую из пяти вот этих ячеек поставили какие-то там записи. То, соответственно, мы видим ABCDEFFGHOPRSP и так далее. То есть мы видим значения данного списка допустимых значений. Хорошо. Теперь возникает Следующий момент. Вот у нас есть, опять вернемся к изначальному выпадающему списку 5, 10, 15, 20, 25. Что если мы сюда поставим число 30? То есть шестую ячейку задействовали. Задействовали, задействовали, посмотрели, а ее здесь нет в выпадающем списке. Если у нас, допустим, выпадающий список состоит из двух ячеек заполненных, но настроен он на 5 ячеек, Мы здесь видим, соответственно, 5-10. Вот там 10 выбрали. А здесь мы видим пустые значения. Ну, понятно, что если мы пожелаем добавить какое-то еще одно допустимое значение, допустим, 15. Ну, вот в таком случае оно здесь появится. Вот оно, 15. Его можно выбрать. Пожелаем 20 добавить. Ну, соответственно, добавилось 20, мы видим. Также видим, что остается всего лишь одно место. Ну, как было пять кечеек, так и остается. Соответственно, если у нас диапазон допустимых значений предполагает какие-то изменения в количестве значений, то возникает некоторая такая неприятность, которую на первый взгляд, ну и многие как бы сразу же, сходу решают следующим образом. Ну, мы заходим, соответственно, в выпадающий список. Например, здесь не 5 напишем, а 10 запишем. F1, F10 источник диапазона. Ну и действительно, тогда у нас вот такой вот будет выпадающий список, внутри которого мы можем вот сюда, например, 4 поставить, сюда поставить 3. Ну и мы увидим, что вот они появляются 15, 20, 3, 4 и так далее. Но мы видим, что Вот эти пробелы внутри самого выпадающего списка, ну, они просто соответствуют вот этим вот пустым ячейкам, которые у нас в диапазоне вот этих вот уже 10 ячей нашего диапазона выпадающего списка. Теперь еще можно усложнить задачу. Понятно, что когда мы используем, когда мы создаем финансовую модель, то не секрет, что в рамках финансовой модели создается не один выпадающий список, может создаваться с десяток выпадающих списков, которые используются в тех или иных местах финансовой модели. Мы в данном случае для этих самых списков создали целый выделенный листочек, выпадающий в списке финансовые модели для того, чтобы как-то сделать название выпадающего списка, список номер один и его значение. Название выпадающего списка и его значение. И сколько бы их нам здесь не нужно было, мы их все сюда собираем, все выпадающие списки в данный лист. в данную вкладку нашей финансовой модели и потом нам какую мы можем цель поставить. Мы можем поставить, например, такую цель. Вот если у нас вот здесь вот есть написано список номер один, то вообще хотелось бы иметь следующую возможность, что если мы вот сюда, например, пишем равно, в какую-то ячейку пишем равно, и настраиваем на список номер один, говорим, вот равно список номер один, то, например, вот в какой-нибудь здесь вот ячейке в данном случае, допустим, в столбце E, если здесь есть тот или иной выпадающий список, то хотелось бы, чтобы здесь был ровно тот выпадающий список, название которого находится напротив. То есть если мы здесь поменяем название нашего выпадающего списка, Вообще, хотелось бы, чтобы автоматически и здесь сам список тоже поменялся. Ну, конечно же, еще бы хотелось, чтобы… Это уже специфика финансовой модели. Желательно, чтобы наша финансовая модель отслеживала, чтобы допустимые значения нашего списка, выпадающего списка, чтобы они не повторялись. Это тоже очень важно, потому что формулы иногда используют вот эти вот значения из выпадающих списков, и если они повторяются, то возникают неоднозначности. Короче говоря, пожеланий много. Ну, давайте начнем вот с этого, ну, такого наиболее интересного пожелания, что вот давайте сделаем так, что вот если мы в данной ячейке, в ячейке B14. Вот у нас ячейка B14. Если мы в ячейку B14 ставим название какого-либо списка из данной вкладки, где находятся все выпадающие списки, то тогда нам необходимо, чтобы в ячейке E14 появлялся ровно тот выпадающий список, который соответствует вот этому названию. Опять же понятно, что уже сама постановка задачи требует наличия контроля того, чтобы в данной строчке, в 13-й строке, где у нас будут названия наших списков, нам необходимо сразу же будет создавать контроль, за тем, чтобы не было двух списков с одинаковыми названиями. Потому что, опять же, модель должна четко понимать, что если у нас появилось здесь некоторое название списка, то, значит, вот в этой ячейке формулы должны однозначно определить, какой конкретно список отсюда необходимо подтянуть вот в эту ячейку с выпадающим списком. Ну, давайте размышлять логически. Значит, цель поставлена. Цель следующая. Вписали сюда название списка в ячейку B14, и у нас в ячейке E14 должен появиться выпадающий список. Поскольку выпадающий список, он, по сути, является некоторым диапазоном ячеек, то мы должны задаться вопросом, где находится данный диапазон и куда его вписать. Диапазоны у нас все находятся здесь, во вкладке «Lists», во вкладке «Выпадающие списки нашей финансовой модели». Теперь давайте договоримся так, что все диапазоны выпадающих списков начинаются со строки 16, допустим. То есть у нас в тринадцатой строке будут находиться только названия наших выпадающих списков. В строке 16 у нас будут начинаться наши выпадающие списки. И наша задача такая, что сколько бы мы сюда не добавили значений, то нам необходимо, чтобы наш выпадающий список, который появится в ячейке E14, он выдавал ровно те значения нашего выпадающего списка, которые именно на данный момент актуальны. Соответственно, нужно будет ввести понятие актуальности. Ну хорошо, все по порядку. Для того, еще раз повторяю, для того, чтобы у нас здесь появился выпадающий список, нам нужно что? Нам необходимо зайти в данные, выбрать проверка данных и прописать формулу. Правильно? Прописать формулу нашего диапазона. То есть нам необходимо написать равно. Потом необходимо написать, задать сам диапазон. Отмену пока нажмем. Теперь мы знакомимся с одной очень интересной, очень известной функцией. Эта функция называется dvSyll. Эта функция, она интересна ровно тем, что если вы напишете равняется dvssyl и внутри укажете тот или иной диапазон именно в текстовом формате, то есть текст некоторого диапазона. Допустим, вот так вот. Откройте кавычки, откроем кавычки и напишем, допустим, f1. Вот она у нас, ячейка f1, вот здесь. Два иточи. Так, сейчас, два иточи и ячейку f5. Поставим здесь, соответственно, кавычки, закроем. И посмотрим, что, ну, понятно, пишут значение, но важно следующее. Важно, что когда мы вот это вот dvsl и в кавычках f1, f5, мы вот эту формулу скопируем и поставим ее в формулу диапазона, то есть мы идем на вкладку данные, проверка данных. И, соответственно, вот сюда мы ставим Ctrl V, делаю, да, и мы пишем равняется dvSyll f1, f5. Пишем ОК. Но тогда что мы видим? Мы видим 5, 10, 15, 20 и пустую ячейку, да, вот она, 5, 10, 15, 20 и пустая ячейка. Это диапазон f1, f5. То есть понятно, что если мы записываем... То есть вот эта вот запись равняется dvsyl, кавычки открываются f1, двоеточие, f5. Эта запись равняется, она абсолютно идентична записи вот такой f1, двоеточие, f5. То есть если мы вот так напишем, тоже получается значение. Суть в том, что вот это, здесь записаны две одинаковые формулы. То есть вот такая формула dvsl f1, f5 и вот такая запись f1, f5, они абсолютно одинаковы. Но разница между вот этими двумя записями состоит в следующем. Вот здесь вот в кавычках мы указали текст. Но если мы этот текст напишем, например, вот здесь, как текст f, двоеточие F5. То есть вот здесь вот мы его визуально видим. F1, двоеточие F5. Это текст. То есть в ячейке находится текст. То тогда если мы, например, если мы dvsyl вот этот вот в кавычках F1, F5, мы сюда в скобочке поместим ссылку на вот эту ячейку, где записан этот текст, то тогда он конвертируется, да, то есть вот эта ссылка ячейки, она конвертируется в сам текст, который записан вот в этой ячейке. То есть, опять же, здесь выдает ошибку, но если мы вот эту запись dvsl b19 скопируем, зайдем в данные, проверка данных, и вот в этой формуле dvsl f1, f5 Мы здесь CTRL V ставим, поставим dvsyl b19, то есть ссылку на ячейку b19, где текст f1, f5, диапазон написан, нажмем OK, то результат от этого тоже не поменяется. То есть получается следующая интересная вещь, что если мы в данную, если мы Допустим, во вкладке со всеми выпадающими списками для каждого выпадающего списка в нулевых, так сказать, ячейках, в строке 15, допустим, в каких-то строках, в каких-то ячейках, которые однозначно соответствуют нашим выпадающим спискам, мы запишем текстовую запись диапазона ячеек, диапазона допустимых значений. И напишем функцию, которая по названию будет ссылаться на соответствующую ячейку с названием диапазона. Вот на вот эту, например, или на эту. и разместим это в источнике, вот здесь поместим эту функцию, то тогда, копируя вот эту конструкцию с названием выпадающего списка и с самим выпадающим списком, который формируется однозначно по названию, мы будем иметь универсальный выпадающий список нашей финансовой модели. Теперь давайте последовательно постараемся это сделать. Значит, в чём состоит наша задача? Наша задача состоит в следующем – написать универсальную формулу, разместить её в пятнадцатой строчке над нашим выпадающим списком. Таким образом, чтобы здесь выдавался текст, текстовая запись вот этого самого диапазона. Ну, давайте будем записывать. Пишем «равно». Значит, во-первых, диапазон, запись диапазона начинается с записи названия листа. Ещё на занятии 1 к сожалению, год назад, да, это не вчера было, мы говорили, мы решили, что вот в этой вот ячеечке F11 у нас вот здесь вот всегда будет фигурировать, всегда на всех листах будет фигурировать название нашего листа. Ну, соответственно, если название у нас есть, ну, давайте мы напишем равно и ссылочку ровно на эту ячейку, на F11. Поскольку у нас списков здесь может быть много, это значит, что вот эту ячейку с названием диапазона мы потом предполагаем копировать. Это значит, что желательно нам вот эту ссылку на ячейку F11 закрепить, то есть доллар-столбец, доллар-строка поставить. Ну и тогда при копировании уже съезжать вот эта ссылочка на эту ячейку она не будет. Хорошо. Теперь дальше. После названия листа у нас должен идти восклицательный знак, который отделяет название листа от диапазона ячей. Соответственно, синтаксис следующий. Мы пишем объединение, символ объединения. Объединяем с чем? И теперь нам нужно просто записать восклицательный знак. Для того, чтобы записать восклицательный знак, его нужно поместить в кавычки. Открываем кавычку, записываем восклицательный знак. Кавычку закрываем. Ну и давайте на Enter нажмем. Вот мы на Enter нажимаем. Давайте я здесь подсвечу значение. Вот так вот напишем. Я красным сделаю, чтобы было видно. Равно F11. Объединяем с текстом. Каким текст? Берется в кавычки. Это восклицательный знак. Соответственно, у нас lists, вот записано в этой ячейке, вот здесь вот в ячейке F11. И дальше восклицательный знак. Хорошо, первая часть получена. Значит, мы должны записать lists, восклицательный знак. Давайте вот здесь вот тоже я напишу. Мы должны написать ЛИСТС, восклицательный знак. Потом нам необходимо взять вот эту ячейку У16, У18. Ну, давайте так и пишем У16, двоеточие, У18. Ну, понятно, здесь взят серый шрифт изначально, поэтому давайте его красным тоже напишем. Итак, мы пишем ЛИСТС. восклицательный знак U16, U18. Вот что нам нужно, чтобы у нас вот здесь отобразилось. Но нужно сделать так, чтобы потом при копировании вот этого вот сюда, у нас здесь было то же самое. Вот сейчас мы копируем, и у нас, в принципе, текст отображается. А если мы здесь, допустим, напишем «объединить это все с U16», к примеру, да, ну давайте опять тоже в кавычки возьмем, давайте в кавычки возьмем, то понятно, вот лист U16, но если мы это скопируем вот сюда, то здесь U16, но у нас здесь должно быть M16. M16, да, вот так вот. То есть лист восклицательный знак M16 должно быть, у нас U16. Следовательно, нам необходимо произвести запись вот этой вот ячейки, которая сразу же идёт под названием диапазона, то есть название ячейки, которая является первой ячейкой в данном диапазоне. У всех этих ячеек строчка одна и та же, строчка 16. Номер этой строки у нас находится в столбце А. То есть здесь у нас записана функция, равняется строка, скобка открывается, скобка закрывается. Вот эта функция строка, скобка открывается, закрывается, она выдает номер этой строки. Соответственно, номер строки у нас есть. Также, если мы посмотрим столбец, то название столбца у нас тоже есть. Вот эту функцию мы ее разбирали. То есть, в строке 2 нашего вот этого листа, совпадающего с списками, в строке 2 у нас всегда здесь отображается название данного столбца. Ну и отлично. То есть, название столбца у нас есть, номер строки у нас тоже есть. То есть, название столбца у нас находится в ячейке U2, а номер строки у нас находится в столбце А. Давайте так и запишем. Объединяем. С чем мы объединяем? То есть нам нужно объединить, и после воскресательного знака, если мы вот сюда посмотрим, после воскресательного знака у нас должно идти название столбца. Где у нас название столбца? Вот здесь вот сверху. Так и пишем. Пишем в ссылочку. У2. То есть в ячейке U2 у нас идет название текущего столбца. Понятно, что при копировании там лево-вправо у нас вот это вот должно смещаться, следовательно, столбец мы не можем двигать, но если, не дай бог, у нас это куда-то вниз-вверх будет, допустим, мы будем копировать по какой-то причине, то, конечно же, желательно сделать тогда вот такую конструкцию, то есть доллар подставить перед строкой. Итак, ну давайте нажмем на Enter. Видите, lists восклицательный знак U. Давайте скопируем, копируем, вставляем, lists восклицательный знак M. Давайте здесь скопируем, получаем lists восклицательный знак I. Скопируем сюда, мы получаем lists, а здесь у нас нет восклицательного знака. Почему? Потому что в ячейке AD2, вот в этой ячейке, здесь пусто. Следовательно, мы вот эту формулу туда тоже копируем. То есть для того, чтобы нам дальше в будущем создавать новые выпадающие списки, вот лист восклицательного знака ID появилась. То есть мы сюда поставили название столбца, ну и, соответственно, лист восклицательный знак названия столбца у нас это в формуле отобразилось текстом. То есть еще раз, мы понимаем, что если мы в последствии после того, как все мы здесь настроим. Если мы пожелаем создать новый выпадающий список, то мы должны будем копировать сразу же целый столбец. Копировать столбец, например, вот сюда мы его копируем, вставляем, и у нас появляется лист с восклицательным знаком AF. Дальше мы сейчас допишем это, но понятно, что нужно будет копировать желательно сразу же все. целый столбец копировать, тогда он комплексно переносит нам целый, создает новый выпадающий список. Ну, понятно, что нужно будет название списка поменять и соответствующее значение здесь поставить. Ну, это уже другой вопрос. На данный момент мы написали только название столбца. Теперь дальше. Номер строки. То есть у нас лист восклицательный знак У16, потом двоеточие будет идти. У мы поставили, то есть название столбца мы поставили. Далее мы пишем значок объединения, соединение текстов. И далее у нас должен зайти сюда текстовый номер. Чего номер? Номер первого элемента нашего выпадающего списка, первой ячейки. Первая ячейка, она находится, номер строки у нас вот здесь. мы прям, соответственно, сюда, в ячейку А16 и ткнем курсором. То есть вот. То есть объединение с ячейкой А16. Опять же, если мы вправо-влево вот это вот будем копировать, то тогда вот эта вот у нас ссылка на вот эту ячейку А16, она будет тоже вправо-влево съезжать. Не дай бог влево мы ее сместим, здесь вообще будет ошибка. Ну, понятно, что Получается, что столбец нам точно нужно держать, ну а если вверх-вниз мы будем копировать, да, будет меняться строка, ну, конечно, желательно тогда строчку не закреплять. Значит, мы... Вот так это у нас закреплена и строчка, и столбец. Вот так у нас закреплена строка, столбец у нас ходит, а нам нужно закрепить именно столбец А. Вот он, доллар А закреплен. 16 у нас свободно, да, то есть оно у нас будет гулять, строчка у нас гуляет. Соответственно, нажимаем на Enter, получаем лист У16, копировать, вставляем сюда, лист М16, копировать, вставляем сюда, у нас получается лист АФ16. Хорошо. То есть уже половина диапазона у нас задана, то есть начало диапазона у нас задано. Теперь дальше, для того чтобы нам перейти к концу диапазона, мы обязаны по синтепсису поставить двоеточие, то есть мы пишем объединение значок, потом кавычки открываются, кстати, можно вот сюда смотреть, да, вот они, кавычки открываются, ставим здесь не точку запятую, а двоеточие, кавычки закрываются, ну и давайте посмотрим, что получится. Итак, лист с восклицателем знак, U16, двоеточие. И далее у нас должна появиться замыкающая ячейка нашего диапазона допустимых значений, именно конкретного вот этого списка. Понятно, что столбец, в котором находится эта ячейка, это тот же столбец, то есть U16, U, в данном случае, U18. Но если у нас будет вот такая ситуация, то это будет у 19. А если вот такая, это будет у 20. А если вот такая, то это у нас будет у 17. То есть мы понимаем, что после двоеточия, вот после двоеточия, переместимся в формульную строчку вот сюда наверх. То есть после двоеточия мы это все дело объединяем и опять сюда подтягиваем вот эту вот ячейку у2, значение из ячейки у2, где у нас название столбца. Ну, и делаем все то же самое. Столбец у нас меняется при копировании. Строчка доллар-два, она у нас держится. Точно так же, как вот здесь, U2. Здесь вот U доллар-два. Хорошо, есть. Итак, осталась только одна часть, то есть переменная. То есть у нас конец нашего выпадающего списка, он неопределен, получается. А нам необходимо сделать так, чтобы с помощью формулы мы сюда могли подтянуть номер строки, в рамках которой у нас заканчивается диапазон. Давайте сделаем это следующим образом. Понятно, что если мы вот эти вот значения, вот эти вот ячейки, в которых находятся значения выпадающего списка, допустимые значения, если мы их пронумеруем рядышком вот здесь, вот как вот здесь вот единичка у нас стоит, если мы вот так протянем, то у нас будет 1, 2, 3, 4, 5, 6, 7, 8. Вот оно, пошел отсчет. То есть, если мы их переименуем, то, по крайней мере, у нас будет некоторое число, которое в точности соответствует каждому значению нашего диапазона. Понятно, что если мы это число прибавим, например, к номеру этой строки нулевой, нулевой строкой является пятнадцатая строчка. Если мы к пятнадцатой строчке будем прибавлять номер каждой из этих ячеек, допустимых значений, то вполне себе спокойно сможем получить последнюю ячейку как максимальное значение, которое здесь будет фигурировать. Ну, соответственно, функция, видите, вот такая вот используется в данном случае. Максимум n15, n15 плюс 1. То есть давайте мы Сюда запишем, напишем. Равняется максимум. Максимум. Макс. Максимум. И, соответственно, V15. Значит, держим строчку 15, двоеточие. Двоеточие V15. V15. И плюс один. Тогда это у нас получается единичка. Как эта функция работает? Она у нас, если мы вот так вот скопируем вниз, вот сюда, например, вставим, то у нас получится, что эта функция смотрит максимум в диапазоне ячеек от 15 строки до вот этой активной ячейки, берет максимальное значение и прибавляет единичку. Если они у нас протягиваются сквозным методом, то мы просто получаем нумерацию. И понятно, что в таком случае нам необходимо договориться о некоторых правилах игры, так сказать, что если у нас есть какой-либо выпадающий список, допустим, вот у нас есть два значения в выпадающем списке, соответственно, здесь у нас находится формула, то если мы желаем добавить еще одно значение в наш выпадающий список, то мы добавляем это не вот таким образом, когда мы просто двигаем вот эту ячейку и добавляем сюда третье значение. А мы говорим, что правило добавления новых значений состоит в том, что мы объединяем. Мы берем вот эти два столбца и протягиваем их на то количество значений, сколько нам нужно. Допустим, на одну. Так, чтобы у нас нумерация всегда следовала за новыми значениями, или, например, вот так, то есть мы создаем пять значений, или если мы желаем убрать те или иные значения, то мы удаляем сразу же по две ячейки, то есть по столбцу U и по столбцу V в данном случае. То есть вот таким вот образом их удаляем. Так, чтобы удалялась вместе с ячейкой, внутри которой находится ненужное нам значение, удалялась, в том числе удалялся ее номер. Удалялся ее номер. В таком случае при добавлении любого количества дополнительных значений у нас напротив будет всегда в самом конце, в самом низу у нас будет находиться номер, которые соответствуют количеству всех значений. Понятно, как теперь нам в данном случае быть. Соответственно, для того, чтобы нам сюда приписать номер строки, в которой находится наше последнее значение диапазона, нам необходимо сюда прописать следующую формулу. Скобочку откроем, скобочку закроем. и к 15 или к номеру вот этой нулевой строки, которая стоит над нашим диапазоном А15. И, соответственно, мы закрепляем столбец А, а строчку 15 оставляем. А 15 мы прибавляем, что прибавляем? Мы должны прибавить максимальное значение вот в этом столбце, который находится справа от нашего выпадающего списка. Соответственно, плюс макс, плюс макс, и внутри просто-напросто записываем максимум по столбцу V. Максимум по столбцу V. Единственное, что внутри этого столбца нужно договориться также о правилах игры, что внутри любого столбца, который находится справа от выпадающего списка, желательно, чтобы внутри не было никаких других формул или каких-то чисел и так далее. Но чтобы за этим специально вот это не отслеживать, Ну, можно, в принципе, тогда здесь не весь столбец поставить, максимум не по всему столбцу. А, ну, во-первых, давайте Enter нажмем, и вот мы видим U16, U20. Вот он, наш диапазон, U16, U20. Если мы этот диапазон вот сделаем вот таким, то есть, допустим, у нас 17 будет значений в выпадающем списке, заканчивается на 32 строке, то мы видим у нас здесь U16, U32. Вот как это все у нас действует. Если мы желаем удалить часть значений, то удаляем сразу же по двум столбцам. Вот 10 значений заканчивается в 25 строке. U16, U25. Соответственно, если мы, например, скопируем вот этот выпадающий список вот сюда, то мы получаем U16, U23. Ой, M16, M23. Вот действительно, где восьмерочка стоит, это вот 23 строка. Все-таки если, возвращаюсь, если мы, допустим, если мы не желаем задумываться о том, чтобы вот в этой части, вот в этой шапке сверху всегда были пустые ячейки, допустим, они для каких-то расчетов, что-нибудь нам здесь понадобится, то, в принципе, мы вместо максимум по всему столбцу V, мы можем здесь записать, например, V15 диапазон и, например, V100000. То есть максимально туда вниз. То есть мы понимаем, что вряд ли здесь будет использоваться диапазон в 100000 значений. Соответственно, сделаем V15, V100000 и более того, Закрепим... Соответственно, что мы закрепим? Ну, V15 мы оставим, да? Да, и V100000 можно тоже оставить. В принципе, вот так вот будет достаточно. Можно вот так сделать. Вот. Соответственно, вот если мы сюда, например, перекинем... Видите, здесь AF16, AF15. Почему? Потому что у нас нет вот этой вот формулы здесь. Если мы здесь скопируем сюда, вставим соответствующие номера, то у нас получается АФ-16, АФ-18. В восемнадцатой строке у нас конец данного диапазона. Ну, и здесь написано, лист восклицательный знак, АФ-16, АФ-18. Итак, для каждого... Ну, давайте здесь, например, у нас будет написан список номер 2. Здесь, например, у нас список номер 3. номер 3. Здесь у нас, допустим, давайте вот так вот перекинем отсюда. Здесь у нас значение 2.1. К примеру, здесь 8 этих значений. Давайте вот это вот уберем вот так. Здесь у нас 8 значений. Здесь у нас, ну, пусть у нас там будет, допустим, 5 значений. Значение копировать, вставить. Значение 3.1 и их 5. Давайте здесь пока что напишем список номер 4. Список номер 4. Теперь. Возникает, соответственно, следующий вопрос. Лист М16, М23. Все верно. Здесь у нас просто лист. Давайте формулу перекинем, копируем, ставим сюда вот эту формулу. Вот у нас получается лист АИ-16, АИ-20. То есть мы научились в начале каждого выпадающего списка мы научились вначале прописывать диапазон значений данного выпадающего списка. Диапазон значений. Ну, это уже хорошо. То есть теперь наша задача состоит в чём? Чтобы нам в данную ячейку, данной ячейке поставить в соответствии выпадающий список, название которого находится вот в этой ячейке, в ячейке B14, в ячейке B14, то нам необходимо что сделать? Нам необходимо прописать такую формулу, которую мы поместим в источник. И вот здесь у нас должна появиться формула. Должно быть написано равно dvsyl И вот внутри скобочек вот этих вот ДВСЛ у нас здесь внутри должна быть универсальная формула, которая будет нам сюда доставлять текстовое значение вот из этих вот ячеек с названиями выпадающего списка, но ровно из… тех столбцов, где находится соответствующее название нашего списка. То есть, если вот здесь у нас написано «список номер один», то тогда формула у нас должна ссылаться на ячейку U15. Если мы здесь напишем «список номер два»… Давайте так, копировать. специальная вставка значения. Пока что сделаю как значение. Если мы здесь напишем список номер 2, то тогда нам необходимо, чтобы здесь у нас сразу же появился выпадающий список из столбца М. То есть, грубо говоря, чтобы формула указала на ячейку М15, где записан вот этот самый диапазон. Давайте это сделаем. Давайте вот здесь пропишем такую формулу. Ну или, например, вот здесь, перед глазами. Значит, что нам нужно? Ну или давайте вот напротив. Вот список номер 2 и вот прямо напротив постараемся сюда, вот в эту ячейку C14, напротив названия списка, сюда мы сейчас подтянем диапазон. допустимых значений, которые находятся в этом самом списке с этим названием. Что это значит? Это значит, что мы сюда должны подтянуть, в данном случае, если список номер 2, то мы в эту ячейку должны подтянуть значение какой ячейки? Значение ячейки M15. Ячейки, значит, Если мы там поменяем на список номер три, то нам нужно будет подтянуть вот это вот значение. То есть первое, что мы замечаем, что формула должна, формула, которая будет сейчас вот здесь прописана, она должна нам вытащить нужную ячейку из вот этой строки. То есть мы точно понимаем, что вот именно в этой строке у нас прописаны все необходимые названия диапазона. Отлично. Такая формула есть. Она называется индекс. Мы пишем равняется индекс, скобочка открывается. Мы просто сюда смотрим массив. Какой массив? То есть массив в данном случае это тот массив ячейки, из которого мы должны вытащить нужную нам ячейку. В данном случае наш массив ячеек — это есть строка 15. Вот она записана, видите лист с 15-15. Давайте ее сразу же закрепим. Лист с 15-15. Пишем точку с запятой. И дальше необходимо указать номер строки и точка запятой номер столбца. Что за номер строки и номер столбца? Нам необходимо указать номер строки и номер столбца внутри данного массива. Давайте сюда зайдем, посмотрим на него. Наш массив – это есть строка 15. Номер строки внутри этого массива, поскольку он состоит из одной строки, то номер строки у нас определен. Это есть единичка. Строка один. Она всегда будет один, потому что мы выбираем только вот этот номер строки. А точка с запятой, и нам нужно указать номер столбца. Давайте посмотрим, где у нас находится номер столбца. Столбцы у нас идут слева направо со столбца А. А, Б, С, Д. И номера столбцов здесь можно увидеть, что они находятся... Номера столбцов записаны внутри вот этого листочка, листц. Номера указаны в первой строке. Здесь вот можно, если приглядеться, то вот видно, например, здесь вот 21, да, столбец У, он 21. Например, вот столбец АФ, да, вот здесь вот написано 32. То есть номера столбцов у нас записаны в первой строке. Как нам вытащить нужный нам номер из первой строки? А нам какой нужен номер? Нам нужен номер, напротив которого находится название, которое нас волнует. А название находится в тринадцатой строке. Для того, чтобы отсюда выбрать соответствующий номер, мы используем функцию sum, если mn. Скобка открывается. sum, если mn работает по следующему принципу. Мы вначале указываем диапазон, из которого мы желаем вытащить соответствующее число. Давайте его тоже закрепим долларами. Потом мы пишем точка с запятой, ставим. И нам необходимо указать условие. Видите, вот диапазон условия. Диапазон условия у нас тринадцатая строка. Также мы записываем доллары, как бы удерживаем тринадцатую строчку. И само условие. Чем является условие? Условие является тем, что должно быть записано в тринадцатой строке. В тринадцатой строке у нас должно быть записано, возвращаемся в наш листочек, у нас должно быть записано вот это название. Список N2. Ну, поскольку мы находимся в данном листе, мы вот это название листа убираем. И, допустим, столбец B14, столбец B мы держим, ну а, соответственно, четырнадцатая строка, то есть она у нас может при копировании перемещаться. Таким образом, закрываем две скобочки, у нас получается индекс, то есть мы вытаскиваем из 15-й строки ту ячейку, в которой записан диапазон. Диапазон чего? Диапазон того столбца, у которого номер записан в первой строке нашей вкладки с выпадающими списками, напротив которого в 13-й строке записано название нашего выпадающего списка. Нажимаем на Enter. И вот мы видим лист, восклицательный знак М16, М23. Список номер 2, лист М16, М23. Заходим. Лист М16, М23, список номер 2. Давайте поменяем. Список номер 3 поставим. Список 3. Видим лист восклицательных знак Ай-16, Ай-20. Заходим в лист Ай-16, Ай-20. Ну, давайте посмотрим здесь список номер 4. Давайте напишем список номер 4. Лист Ай-16, Ай-18. Заходим сюда. Ай-16, Ай-18. Итак, по названию списка, по названию выгодающего списка мы научились получать диапазон значений нашего выпадающего списка. Причем вот, например, список номер 4, AF16, AF18. Если мы здесь, допустим, добавим еще два значения, то у нас диапазон значений будет AF16, AF20. Заходим сюда, смотрим, AF16, AF20. Итак, у нас есть название списка. По названию списка мы можем однозначно определить диапазон значений данного списка. Осталось только сделать что? Осталось вот эту формулу, которая вытаскивает диапазон, её вставить внутрь функции dvSyll внутри источника. Пишем «копировать» именно вот эту запись. Копируем эту функцию, заходим, наводим на нашу ячейку, где у нас ссылка, пишем «данные», «проверка данных». И вот сюда внутрь, вот сюда внутрь вот этих скобочек, dvsl, скобка открывается, скобка закрывается. Внутрь ctrl-v пишем. И мы сюда вставили нашу вот эту функцию. Пишем ok. Удаляем значения, смотрим. Список номер 4. Давайте посмотрим, что у нас. Значения 1, 1, 1, 2, 1, 3, 1, 4, 1, 5. Заходим в лист. Вот они, значения 1, 1, 1, 2, 1, 3. Ну, они такие же, как отсюда, x, k, r. Если мы здесь их поменяем, Например, значение 4.1, 4.2, 4.3 и так далее, 4.5. Если мы сюда зайдем, то мы увидим значение 4.1, 4.2, 4.3, 4.5. Соответственно, выбираем. Давайте удалим. Давайте напишем список номер 2. Смотрим, значение 2, 3 и так далее, 2.8. Все значения списка номер 2. Ну, так, чтобы там видно было, допустим, давайте вот сделаем вот этот список. Копировать, вставить. Там, например, список номер 5. Сделаем. Ну, давайте мы его не список, а ответы. Ответы. Напишем. У нас такое есть. Ответы. Да. Да, нет. Ответы да, нет. И вот здесь напишем да, здесь запишем нет. Других ответов у нас нет. Значит, мы по двум столбцам проводим, стираем, вот они да, нет. И, соответственно, как нам этот список? Напишем просто равно, равно, листья, ответы да, нет. И получаем здесь «Да», «Нет». Либо «Да», либо «Нет». Вот выбираем. Если мы теперь... Ну, давайте вот это вот удалим. Это все здесь удалим. Теперь смотрите. Допустим, мы вот это вот теперь копируем, вставляем. И, допустим, здесь мы хотим подтянуть сюда какой-нибудь другой список. Список номер 2. Мы прямо на него делаем ссылку, на его название. У нас появляется здесь список номер 2 с названием. Вот оно. Значение 2, 2, 4, 2, 8 и так далее. Ну и так далее. Допустим, у нас там в самом начале будет идти минимальный период расчетов. Минимальный период расчетов. И у нас там идет дни, недели, декады. месяцы, дни недели, декады, месяцы, годы. Например, вот так. Если нам необходимо будет где-то задать минимальный период расчетов. Не помню, здесь есть или нет. Минимальный период расчетов. Давайте мы это просто берем и копируем. Вставляем, делаем ссылку куда. У нас название находится в 13-й строке, то есть i13. Если мы здесь поставим не m13, а, например, i13, вот минимальный период расчетов, то тогда в выпадающем списке мы увидим дни, недели, декады, месяцы. Выбираем месяц. Вот таким вот образом настраиваются выпадающие списки. Понятно, что если мы теперь вот это вот скопируем, да, теперь смотрите, скопируем вот эту вот ячейку, например, в период, да, и давайте мы сделаем вот как. Мы её пока что не будем копировать, а мы подготовимся. Мы вот эту ячейку, да, ячейка, которая у нас означает, что здесь выпадающий список. Слева звездочка, справа у нас галочка вверх. Давайте мы ее скопируем, вот сюда вот так вот вставим саму эту ячейку, чтобы нам потом форматы перенести. Настроенную ячейку уже со списком, то есть вот с этой вот формулы, которая у нас здесь. Или даже, кстати, давайте проще сделаем. Для того, чтобы нам теперь во вкладке Periods использовать вот такой вот универсальный выпадающий список, просто скопируем формулу. Копировать, отмена, уходим во вкладку Periods, заходим сюда, наводим на соответствующую ячейку, идем в данные, проверка данных и просто-напросто сюда вставляем Вставляем формулу. Единственное, что у нас вот здесь вот есть B21. Вот эта вот ячейка, которая на самом деле вот здесь находится, у нас должна быть ячейка H16. То есть мы будем название выпадающего списка вставлять в ячейку H, а строка должна быть ровно та строка, в которой находится сам выпадающий список 16. Соответственно, вместо B21 пишем H. 16, и нажимаем на OK. При вычислении источник возникает ошибка. Ну и действительно, да, то, что ошибка с источником, ну, это понятно у нас, потому что в ячейке H16 ничего не написано. а здесь должно быть название списка. Хорошо, мы впишем сюда список. Ну, можно руками, конечно, вписать. Нам, чтобы не сделать ошибку, мы просто пишем равно, заходим в lists, например, минимальный период расчетов. Желаем, чтобы у нас там отображался выпадающий список с минимальными периодами расчетов. Нажимаем на Enter. Вот у нас уже здесь сразу же настроечка работает, да, условное форматирование выделяет данную строку. И как только у нас здесь появилось название соответствующего выпадающего списка, мы его видим при нажатии на кнопочку. То есть вот у нас, соответственно, появляются кадры, месяцы, годы. Теперь, если вспомним, например, у нас там есть год текущий, год календарный. Здесь у нас просто годы написано. Давайте поменяем. Идем во вкладку lists, в выпадающие списки, и добавляем сюда еще одно значение. Напишем текущий год, календарный год. Хорошо, переходим во вкладку «Период» и смотрим, что у нас с выпадающим списком. Текущий год, календарный год. Таким образом, вопрос создания выпадающих списков мы решили. То есть у нас универсальные выпадающие списки вот таким вот образом создаются. Но есть небольшие нюансы, связанные вот с чем. Значит, во-первых, если у нас, как мы уже сейчас вот видели, если у нас здесь не задан тот или иной выпадающий список, то мы видим, что у нас эта кнопочка не работает, поскольку есть ошибка. Если у нас все-таки здесь есть выпадающий список, то есть поставим здесь заголовок, то какие у нас могут быть проблемы? Если здесь у нас написано слово «месяцы» отображается, а мы, например, решим не во множественном числе, а просто «месяц» писать, то есть если мы меняем выпадающий список, меняем его значение, соответствующее значение здесь у нас в ячейке отображается, но его в выпадающем списке уже нет, что, вообще говоря, может привести к ошибкам для расчетов финансовой модели, то мы этого не замечаем. То есть нам необходимо какие-то контроли поставить. То есть каждый раз проверять вот это вот и опять выбирать месяц вместо месяца. А если мы, например, забудем или не помним уже, соответствие значения, а может быть, то значение, которое у нас здесь стояло, мы вообще перестали им пользоваться, то есть мы, допустим, его убрали из списка lists, то есть из выпадающего списка, то что в этом случае делать? Короче говоря, необходимо использовать нашу систему контроля, то есть в приложении номер один к первому занятию Мы достаточно подробно рассказали о том, как у нас выстроена система контроля, ну и, соответственно, применим её. Но давайте решим этот самый вопрос. Если в данной ячейке находятся значения, которые не соответствуют списку значений выпадающего списка, с данным названием, которое находится в ячейке H16, то тогда нам необходимо сигнализировать этот момент. Давайте подумаем, как нам это сделать. Вот у нас здесь есть столбец CS, который принадлежит нашей системе контроля. Почему? Потому что если мы в этом столбце, допустим, напишем любое число, неравное нулю, допустим, вот единичка у нас здесь появляется, то система контроля загорается красным, она нам сигнализирует об этом. Соответственно, что нужно сделать? Необходимо напротив вот этой ячейки с выпадающим списком в столбце S прописать такую формулу, которая будет давать значение единичка, если значение в данной ячейке не соответствует самому выпадающему списку, который здесь должен выпадать. Ну, давайте будем смотреть. Ну, давайте для начала напишем простую формулу, напишем равно, значит, пишем если Надеюсь, здесь всем видно. Мы можем вот сюда перейти, в строку формулы. Если... пишем «если». Что «если»? Мы желаем написать, что если... Переходим в выпадающие списки. Если у нас вот в этом столбце Вот в этом столбце, где находятся все значения нашего выпадающего списка, если среди этих значений не нашлось того значения, которое находится в ячейке из вкладки период, то, соответственно, мы там должны поставить единичку, чтобы у нас все зажглось красным. Как это сделать? Есть такая функция счет. Давайте ее применим в счет. Если в счет, если lists i, вот он у нас записан, пишем 2 и точе. Если в списке lists i у нас содержится значение из ячейки q, 16, вот период Q16, закрываем скобку. Если счет если, если это равняется единице, то есть если это значение единожды встречается в том столбце из выпадающих списков, то тогда мы пишем 0, то есть оно там есть. А иначе мы пишем единичку, то есть закрываем наше если и Вывели для этой функции аргументы. Слишком мало. Здесь нам нужно поставить точку с запятой. Точка с запятой. Мы указываем столбец и, соответственно, ячейку, в которой берем значение для сравнения. Нажимаем на Enter, получаем нолик. Итак, у нас написано месяц. То, что здесь нолик оказался, означает, что у нас слово «месяц», оно единыжды, вот у нас единичка, единыжды слово «месяц», которое находится в ячейке Q16, единыжды встречается в столбце I, листа lists. Ну и действительно, заходим сюда, вот у нас столбец I, вот у нас слово «месяц», вот оно здесь встречается. тем более один раз. Если мы здесь напишем, поменяем это название, напишем месяцы, то видим сразу же зажигается контроль. То есть вот еще раз пишем месяц, у нас здесь ничего не зажигается. Меняем на месяцы, сразу же замечаем, что зажигается контроль. Идем во вкладку контроль, видим во вкладке период появилась одна ошибка. Заходим во вкладку период, видим, что вот у нас ошибка в столбце S загорелась. Ну и вот эта единичка означает, что слово «месяц» не встречается в списке допустимых значений нашего выпадающего списка. Ну и действительно это так. Вот сейчас только что сделали. Таким образом, если мы заметили такую ошибку, то нам необходимо во всех этих ячейках выбрать соответствующее значение из выпадающего списка, который претерпел те или иные изменения. Только лишь есть одна проблема, что если у нас выпадающий список поменяется, и мы, допустим, будем использовать список номер 2, то и выберем здесь любое значение. То есть значение вот этого 2.4, оно у нас в списке номер 2 есть. Вот оно, значение 2.4. Но поскольку у нас данная формула настроена на другой выпадающий список, ну, соответственно, здесь неверно работающий контроль получается. То есть нам вот эту формулу, которая контролирует соответствие значение ячейки выпадающему списку с данным названием, нам необходимо эту формулу сделать универсальной. То есть, что значит универсально сделать? Вот выбор столбца, выбор вот этого столбца lists i, он должен соответствовать названию данного списка. Как нам такое название получить? Ну, что мы делаем? Опять мы применяем, ну, например, формулу dvssl. Прописываем сюда, внутрь пишем dvssl, используем формулу dvssl. Берём в скобочке вот эту вот ссылку на столбец. Понятно, что вот это «Лисц» — восклицательный знак. «Лисц» — восклицательный знак или название листа, в котором у нас все выпадающие списки. Мы его берём откуда? Мы идём, соответственно, в эту вкладку нажимаем на ячейку F11, то есть если у нас название данного листа поменяется, то тогда, по крайней мере, в ячейке F11 этого же листа это название будет фигурировать. Соответственно, мы его выделяем в доллары, чтобы при копировании ничего никуда не смещалось. И объединяем это с текстом, в рамках которого у нас будет находиться восклицательный знак, который отделяет название листа от записи блока. Теперь дальше. Нам необходимо вот здесь получить два раза название столбца, внутри которого находится выпадающий список вот с этим названием списка, который вот в ячейке H16. А между ними должно быть двоеточие. Ну, с двоеточием у нас все в порядке. То есть мы, соответственно, объединяем это с названием столбца. Название столбца мы сейчас создадим. Объединяем это с записью вот этого самого двоеточия, в кавычках берем, и объединяем еще раз с записью столбца. Запись столбца. Как нам получить запись столбца? Ну, мы уже умеем это делать с помощью функции индекс. Итак, мы пишем индекс. Скобочка открывается, скобочка закрывается. Где у нас находится название столбцов? Название столбцов у нас находится во второй строчке вкладки lists. Вот она, вторая строчка вкладки lists. То есть вот они у нас здесь, название столбцов. То есть мы из второй строки вкладки лист должны выбрать то название столбца, которое соответствует нашему выпадающему списку. Индекс как работает? Соответственно, мы массив, вот он массив, мы внесли лист 2.2, то есть вот мы его выделяем. Теперь нам здесь вот подсказочка выскакивает. Номер строки, номер столбца нужно задать. Номер строки у нас один, то есть мы пишем точку с запятой, пишем единичку, точка с запятой. Теперь нужно указать номер столбца. Номер столбца — это тот столбец, в тринадцатой строчке которого написано вот это вот самое название выпадающего списка. Хорошо? Пишем формулу sum, если mn, функцию sum, если mn, скобка открывается, скобка закрывается. Что мы суммируем? Мы суммируем по первой строке, где у нас номера, где у нас отображены номера наших столбцов, чтобы нам вытащить тот номер столбца, который что? Такой номер столбца, так, чтобы напротив в 13 строке, да, вот у нас лист 13, выделяем 13 строчку. Давайте здесь сразу же это закрепим в доллары. И после 13 строчки мы пишем точку с запятой, точку с запятой, так, чтобы в 13 строчке у нас встретилось или находилось название соответствующего выпадающего списка. соответствующий выпадающий список у нас находится в ячейке h16. В текущей вкладке не обязательно period. Мы слово period, в данном случае название данной вкладки можем убрать, удалить. Ну и закрепим столбец. Будем считать, что у нас в столбце h находится, только в столбце h будут находиться названия выпадающих списков. Ну и, соответственно, здесь тоже мы слово «период» суберем, так чтобы при перемещении там другие вкладки, ну, нам не нужно было там менять постоянно название. Что нам еще не хватает? А, нам еще не хватает вот эту буквочку I тоже заменить на соответствующее на соответствующее название столбца. Соответствующее название столбца — это вот эта вот формула dvsl. Мы ее просто копируем и вставляем сюда. Ctrl-V. Ставим. Нажимаем на Enter. Вы ввели для этой функции аргументов слишком много. Так, значит, где-то мы сделали ошибку. Давайте посмотрим. Значит, для какой функции? Значит, давайте смотрим, у нас идет dvscl, лист восклицательный знак ff, это у нас берется название листа, потом мы ставим восклицательный знак и объединяем это с чем? Объединяем это с функцией индекс. Функция индекс, где она у нас заканчивается? Вот здесь она у нас заканчивается. Потом мы, соответственно, это объединяем с двоеточием, и дальше у нас... А дальше... А, я захватил функцию dvsl. Нам ее не надо, нам нужен индекс. То есть вот так вот нужно стереть. Вот, индекс. Ну и, соответственно, вот эта скобочка здесь тоже... А, нет, эта скобочка у нас не лишняя, это dvsl. Все, нажимаем на Enter, получаем 0. Ну и теперь действительно, если мы меняем название списка, то мы сразу же получаем ошибку. То есть мы меняем название списка, в новом списке, допустим, вот такого значения нет, соответственно, вот эта единичка, Сработавший вот этот контроль говорит о том, что данного названия нет в указанном выпадающем списке. Ну и слава Богу. Единственное, что у нас остаётся важным во всей этой конструкции и что может нарушить всю эту конструкцию, — это если у нас появятся два выпадающих списка с одинаковым названием. То есть вот список номер один, напишем здесь список номер один. Как только у нас возникнет вот такая ситуация, то что мы получим? Мы получим вот список номер один. Видите, у нас здесь возникает сразу же ссылка. То есть у нас здесь возникает ошибка. Вот, возникает ошибка и выпадающий список у нас не работает. Почему? Все связано вот с этой функцией индекс. Функция индекс, она у нас находит выпадающий список, который находится в столбце вот с этим номером, а номер у нас находится с помощью функции sum, если mn. Соответственно, функция sum, если mn у нас работает каким образом? Она у нас суммирует значение в первой строке, значение ячеек, в которых находятся номера данных столбцов, и суммирует все те ячейки, напротив которых находится соответствующее название выпадающего списка. В нашем случае, если у нас два раза повторяется одно и то же название, то что мы получаем? Мы получаем суммирование вот этих вот двух номеров столбцов, 21 и 32, и получаем, ну, ошибку, короче говоря. Мы получаем столбец с номером 53, в котором нет никаких выпадающих списков, поэтому выдается ошибка. Значит, ну, во-первых, если у нас здесь возникла ошибка, то это уже будет отображено. в вкладке «период», то есть тут уже происходит. Но этого недостаточно. Желательно идентифицировать, в каком месте находится ошибка. Для этого мы можем сразу же во вкладке «лист» с нашими выпадающими списками ввести следующую контрольную формулу. Строка 14 Она у нас также принадлежит системе контроля финансовой модели. Давайте напишем следующую универсальную формулу для каждого выпадающего списка. Вот в эту ячеечку, то есть в столбце выпадающего списка в строке 14, вот в этом месте, здесь у нас везде пустые ячейки в строке 14. Мы здесь пропишем формулу, которая производит следующее действие. Она будет смотреть на название нашего выпадающего списка, который находится в данном столбце. И если где-то еще повторяется такое название, то здесь будет вот эта ячейка, в этой ячейке будет появляться значение один. Вот если я единичку здесь впишу, то она у нас будет загораться. Соответственно, система контроля будет выдавать ошибку. А мы будем точно понимать, что если данная ячейка загорелась, то это означает ровно одну ошибку — повторение названия списка. Ну, представьте себе, что если у вас в финансовой модели, например, там уже 30-40 выпадающих списков может находиться, то, ну, действительно, При копировании одного выпадающего списка для того, чтобы создать другой выпадающий список, мы действительно можем забыть, переименовать этот список. То есть, если мы забыли переименовать, то мы, по крайней мере, должны увидеть это сразу же в системе контроля, тут же, и сразу переименовать. Как это сделать? Опять пишем равно. Везде используем в основном для системы контроля используем формулу если. Значит, если у нас более одного такого названия в строке 13, более одного название или более одного списка, выпадающего с таким названием, которое находится в данном столбце, то мы должны написать единичку в этой ячейке. Ну, так и пишем. Если. Счет «если». Опять. Счет «если». Что мы считаем? Мы считаем, сколько у нас в строке 13. Вот «если». Счет «если» в строке 13. можем также ее закрепить. Если счет «если» в строке 13 вот с таким вот названием, как в данном столбце, в столбце I13, у нас вот это вот счет «если» равняется единице, если в строке 13 всего одно такое название, как здесь, ну, это именно оно, то тогда мы пишем нолик, то тогда мы пишем нолик, то есть все в порядке. А если их больше, то пишем единичку. Ну и все, скобочку закрываем. Вот, видите, нолик, то есть минимальный период расчета в такой выпадающий список здесь. У нас пока что один. Давайте скопируем. Теперь копируем сюда, сюда, ну, во все выпадающие списки. Копируем данную формулу. И что мы видим? У нас появляется три ошибки, то есть у нас три. выпадающих списков с одними и тем же названием. Ну, хорошо, давайте вместо списка номер 1 поставим список номер 2. Видите, список номер 2 уже есть. Хорошо. Давайте напишем список номер 3. Смотрим. Да, список номер 3 нет. Здесь у нас список номер 1 повторяется. Хорошо, давайте напишем список номер 4. Как только у нас все ошибки исчезли, то, соответственно, у нас и исчезает ошибка здесь. То есть если мы, правда, список номер один, из списка номер один возьмем значение в данную ячейку, то у нас сразу же пропадают ошибки. И в заключение еще. отметим два момента. Первое. Когда мы формируем выпадающий список, если он у нас достаточно большой, и это не просто последовательность каких-то значений, у которых идет 14, 15, 17, 19, 20 и так далее, а мы здесь прописываем, например, продуктовая линейка, ассортимент продукции прописываем, количество SKU, допустим, 100. И, естественно, мы здесь можем какую-то продукцию, грубо говоря, просто повторить, одно и то же вписать. Ну, например, здесь вот у нас написано значение 2.12. Ну, представьте, что вот здесь вот у нас тоже значение 2.12. Здесь значение 2.12, здесь значение 2.12. Для выпадающего списка это нехорошо. Вообще, списки должны Вообще, их суть состоит в том, что значения в выпадающих списках должны быть все попарно различными. Мы уже знаем, как создать проверку на идентичность. Вот здесь мы как раз-таки создали такую функцию, что если у нас Счет «если». То есть, если у нас количество вот таких записей в данной строке не равно единице, то мы пишем единичку. Если равно единичке, то есть всего один раз встречается такое название, то мы пишем ноль. Ну, давайте ровно такую же функцию и используем в данном случае. Я просто скопирую. Здесь она уже, правда, вот стоит, но я еще раз повторю. Вставляем. Мы сюда вставляем вот эту функцию, просто перекидываем, ну а, соответственно, внутри функции счет если просто меняем. Что мы проверяем? Мы, соответственно, можем проверять сразу же столбец, но здесь возникает проблема пересечения. У нас идет вот эта формула, она использует столбец и пересекается с нашей системой контроля по 14-й строке. Поэтому желательно, чтобы не было конфликтов и циклических ссылок, то на самом деле просто делаем. Берем и диапазон ячеек отсюда, с 15 строки, с нулевой строки нашего выпадающего списка, и здесь возьмем на 100 тысяч строк вниз, то есть интервал. Ну и, соответственно, в данном случае просто закрепим сами строчки и будем искать вот такие вот названия, которые напротив данной ячейки, во всех ячейках как бы ниже. Ну, не ниже, а с 15-й строки и ниже до 100-тысячной строки. Ну а, соответственно, значение, которое мы проверяем, мы его берем из ячейки, которая стоит напротив данной ячейки с контрольной формулой АИ-16. То есть мы говорим, если в данном диапазоне, а внутри этого диапазона как раз сосредоточен выпадающий список, находится Одна ячейка вот с таким вот названием, вот с таким значением, со значением дни в данном случае конкретно, то тогда мы пишем нолик. То есть у нас все в порядке, у нас там серенький нолик, мы его не видим. А во всех остальных случаях мы пишем единичку. Пишем Enter, вот, соответственно, и протягиваем. То есть теперь у нас выпадающий список задается с помощью трех столбцов. Для того, чтобы нам создать очередной выпадающий список, желательно производить копирование сразу же вот этих трёх столбцов. Ну, на самом деле, лучше четырёх. Один столбик вначале лучше оставить пустым. И, соответственно, вот так вот четыре столбика берём, например, там копируем и куда-нибудь вот сюда вставляем, например. Вставили. Сразу же у нас... Вот здесь сотрём название. которое попало в название листа. Сразу же мы видим, что появляется ошибка из-за того, что у нас появляется два выпадающих списка с одинаковыми названиями. Меняем здесь название. Пока что напишем список номер 5. И, соответственно, здесь мы вводим любые значения. Значение 5.1. Значение первое, список. ну и, соответственно, получаем новый выпадающий список. Если мы теперь в каком-то месте в нашей модели пожелаем этот список использовать, то пишем равно в столбце H нашего листа Enter, и у нас здесь появляется список номер 5. Сразу же загорается контроль, который говорит, ну, то есть, если мы скопировали это или решили поменять выпадающий список, то сразу же загорается контроль того, что данное значение не находится в этом списке, ну и, соответственно, сюда нам нужно выбрать значение именно из этого списка. Выбираем, и у нас все контроли перестают нас смущать. Вот таким вот образом можно создать систему универсальных, гибко настроенных, выпадающих списков. соответствие значений которых у нас постоянно контролируется. На этом, пожалуй, мы завершим данное занятие по выпадающим спискам. Спасибо за внимание. До свидания.