Кол-во просмотров с 05.01.14г. :: 872
Доброго времени суток всем, кто слушает нас, смотрит. Мы записываем сейчас третье приложение к первому занятию нашему по финансовому моделированию. Соответственно, название данного приложения — это «Организация системы условного форматирования финансовой модели». Ну и сразу же ещё раз скажу, что занятие номер два выйдет, ну, скорее всего, завтра. То есть идёт процесс монтажа и обработки на Ютубе. Ну и, соответственно, вот у нас был «должок» по первому занятию, так сказать. Но этот «должок» не перед вами, а перед нами, перед самими, перед нашим планом. Мы очень хотели подробно рассказать, про систему контроля, которая еще была год назад сделана. Мы рассказали сегодня, ну, практически сегодня выложили, а вчера и сегодня записывали видео по системе выпадающих списков. Вот сейчас про условное форматирование. Также в планах было приложение четвертое, это рассказать немножко подробнее о формулах, которые мы используем. Но формулы мы решили перенести в качестве приложения к занятию 2, ну то есть после занятия 2, потому что в занятии 2 там уже будут представлены все основные формулы Excel, которые будут так или иначе использованы при построении фактически всех финансовых моделей данного курса. Ну и поэтому лучше мы комплексно про формулы про функции расскажем там. Поэтому еще наберитесь терпения. Вот сейчас вот это приложение три к занятию один условное форматирование и завтра выйдет занятие два. После занятия два также выйдет анонс вообще по всему курсу, который вам будет представлен. Там будет чуть-чуть рассказано про то, как будут проводиться занятия далее. И примерно будет набросан план этих занятий. Это будет интересно. Это будет не только Excel, это будет несколько шире. Ну а сейчас мы, как обычно, переходим к нашему Excel-файлю. Сначала мы перейдем вот к чему. Я так, навскидочку, взял здесь четыре файла. Это файлы с финансовыми моделями, которые мы делали там 5 лет назад, 6, 10 лет назад. Я уж не знаю, там уже со счета сбился, потому что финансовым моделированием мы уже 20 лет занимаемся. И, соответственно, просто немножко хотел объяснить проблему, почему условное форматирование мы вот так вот выделяем в отдельное занятие, в отдельное приложение. Давайте просто глянем на то, как выглядели наши же финансовые модели. Их можно скачать на нашем сайте в разделе «Примеры финансовых моделей». Вот как условия выглядят, как отчетность выглядит. Это просто внешний вид. Мы сейчас даже не вдаемся в детали, что и как рассчитывается, формулы. Сам внешний вид. То есть вот таким вот образом это еще один файл, например, второй файл. Вот так, например, это выглядело меленько, вот с какими-то выделениями здесь. То есть везде, естественно, какие-то формулы. Звездочки, так сказать, использовались для того, чтобы вносить значения. Опять же, отчетность выглядела в каком-то вот таком виде. Есть, например, вот такой вариант. То есть здесь вот тоже как-то все это выделялось. Здесь вот более так вот пестро, ярко мы выделяли те или иные расчеты, те или иные показатели и так далее. Вот здесь NPV, опять же PL. В основном такой монохромный вид. Кэш-фло, баланс. Вот это вот модели, кажется, модели, которые мы называем третьего поколения. Ну вот здесь вот, например, в отчетность, если зайти, то вот она какая вся разукрашенная. Кому-то это нравится, кому-то не нравится. Очень много вкусовщины в этом деле. Ну вот как-то исторически складывалось каждый раз, что какой-то вид Да, вот эти вот разукрашки мы выбирали, какой-то стиль, если это можно назвать стилем, и вот в каком-то вот таком вот виде это все представлялось вниманию наших клиентов, заказчиков, ну и сами, находясь в тех или иных компаниях, занимая там должности различные, мы вот создавали вот такого плана финансовые модели. Вот так вот выглядяще. Очень разношерстно. Каждый раз на форматирование уходило очень много времени. Все вот эти вот ячеечки, все эти подчеркивания, выделение цветом, какие-то красные звездочки, там еще что-то, ну и так далее, и так далее. То есть здесь вот такого, там такой ширины ячейки, другой ширины, ну и так далее, и так далее. То есть можно посмотреть, действительно, самые разные вот эти вот разукрашки мы использовали. для визуализации финансовых моделей. В связи с этим как раз возник вопрос, который мы уже ребром в прошлом году поставили. Мы решили просто создать один раз систему условного форматирования, какую-то простую, понятную, и которую мы будем применять уже во всех наших финансовых моделях. Мы их назвали моделями четвертого поколения, они у нас есть в соответствующем разделе на нашем сайте, там можно посмотреть. Наш курс обучения финансовому моделированию, в результате мы построим около сотни финансовых моделей в разных отраслях. Все эти модели будут иметь одно и то же условное форматирование, то есть одну и ту же систему форматирования. Для чего это нужно? Для того, чтобы просто не тратить на это время и для того, чтобы выработать какой-то принцип, какой-то подход к этому делу, чтобы уже не было всех вот этих вот разукрашенных отчетностей. С нашей стороны уже финансовые модели, например, в этом году в 99% случаях финансовые модели выходили именно вот в таком виде. Причем очень часто нам как раз-таки из-за этого клиенты говорили, это, ну, наверное, тоже там в 99% случаев они говорили следующее, но они же выглядят точно так же, как шаблон на сайте. Ну, естественно, они выглядят точно так же, именно внешний вид. Наполнение формулами почти всегда, почти для каждого клиента отличается от клиента к клиенту, от заказа к заказу, потому что везде всегда возникает та или иная специфика. Ну, надеюсь, я как-то пояснил, почему мы так серьезно касаемся этого момента. И, соответственно, Переходим к этим самым принципам условного форматирования. Опять же, заходим сразу же на листочек, на котором ничего нет, так чтобы на чистом эксэлевском листе показать то, как создается эта система условного форматирования, ну а потом на нашем уже типовом листочке с кнопочками, с разметкой, страницы финансовой модели, мы это покажем, как оно здесь реализуется. Опять же, сделаем шрифт побольше, так чтобы было все максимально хорошо видно. Соответственно, ячейки пошире сделаем. Значит, о каких принципах идет речь? Что мы о чём мы, так сказать, в нашем узком кругу договорились. Правильно это, неправильно, не знаю, но из опыта нам как-то показалось, что это будет правильно. Суть следующая. Мы договорились о том, что вся система показателей, любых показателей, которые у нас присутствуют, она будет трёхуровневая, не больше, то есть три уровня. самое простое выручка допустим мы как говорим выручка потом мы говорим в том числе то есть обычно мы говорим в том числе и потом говорим допустим доход от продаж продукта 1, доход от продаж продукта 2, доход от продаж продукта 3, 4, 5, 10. И там прочие доходы. Прочие доходы. Или, например, так, доход от продаж продукта 1. Копируем ниже. Здесь, допустим, продукт 1 делится, например, там, допустим, это размер 35, размер 36, ну и так далее. И вот мы договорились о том, что у нас уровень первый – это просто выручка, то есть есть показатель выручки, и дальше выручка детализируется на один уровень ниже, второй уровень – это доход, это продукт 1, продукт 2, продукт 3, допустим, SKU, ботинки, кроссовки, туфли, еще сапоги, еще что-нибудь. а соответственно вот эти вот обувь в виде ботинок, туфель и так далее разделяем ее на размер 35, размер 36. Если, допустим, кто-то захотел применять там цвет еще, включать белый, черный, красный, синий, то мы вот этого уже не делаем. То есть мы говорим о том, что давайте максимально будем использовать детализацию таким образом, чтобы было каждый раз использовалось три уровня. или, так скажем, в 99% случаев. В каких-то отдельных частных случаях можно, допустим, добавить еще какие-то измерения, какие-то направления детализации. Это первое. Это когда мы показатель детализируем. Также второй принцип – это то, что, хоть скоро мы занимаемся моделированием, то значит, когда мы моделируем, то мы по данному показателю получаем ряд каких-то чисел. 1000, 2000, 3000. Понятно, что сама суть финансовой модели состоит в том, что мы не желаем руками вбивать все цифры финансовой модели. То есть мы моделируем ради того, чтобы, грубо говоря, нажатием две-три кнопки, утрирую я, в кавычках нажатием на две-три кнопки или кликая два-три раза мышкой, мы поимели информацию о том, что будет, если мы будем двигаться в рамках нашего бизнеса по тем или иным трендам. Значит, мы в принципе в рамках финансовой модели хотим задавать тренды. А значит, мы не хотим изначально вбивать руками какие-то показатели, как вот здесь, 4000. Желание это такое, что с помощью двух-трех параметров задать основной тренд по тому или иному какому-то существенному показателю. И, например, если мы выручку хотим в виде тренда задавать, то допустим, что в данном случае мы можем сделать? Мы, например, можем задать такую характеристику, как начальный уровень. И пусть у нас этот начальный уровень, здесь у нас там обозначим эту ячейку как вводное значение. Допустим, начальный уровень 1000. А здесь напишем прирост. Еще, допустим, там 1000, к примеру. Давайте скопируем. И мы говорим, что вот мы хотим начальный уровень задавать и приростом, допустим, от месяца к месяцу, от года к году и так далее. И получать сразу же последовательность значений. То есть тогда мы напишем, что равно, например, если это у нас первый период, если это равняется единичке, то тогда мы пишем 1000. А иначе мы берем предыдущее значение и прибавляем прирост. Понятно, что тысячу и прирост мы просто удерживаем долларами. И прирост – это вот это вот E4, E5 – это наши вводные значения. Здесь мы сделаем шкалу времени 1. Здесь напишем равняется предыдущий период плюс 1. И вот мы уже получаем финансовую модель. то есть мы протянули данную формулу, и теперь что у нас получается? здесь, например, у нас мы пишем равняется сумма, это у нас итого. здесь напишем итого. чтобы красивее выглядело. ну и давайте здесь делаем формат ячеек числовой. и разделитель разряда. Знаки после запятой нам не нужны. Допустим, начальный уровень, например, один миллион рублей. Ну, соответственно, вот у нас получается миллион. Выделим здесь еще вот так вот жирным. Вот у нас простейшая финансовая модель. Сколько у нас будет выручки там за четыре вот этих периода, если мы вот вводим вот эти, какой-то есть начальный уровень и какой-то есть прирост. 200 тысяч, допустим, при росте 200 тысяч, получаем 5 миллионов 200. Но мы-то говорим про условное форматирование, это все понятно. Мы говорим про второй принцип. Второй принцип, который мы решили ввести, это следующее. Мы договариваемся о том, что если есть некоторые показатели, которые у нас на каком-то верхнем уровне, допустим, там 1-2 уровень, И мы, и для расчета данного показателя используются те или иные характеристики, какие-то атрибуты. То есть если данный показатель, его тренд развития этого показателя зависит, непосредственно зависит напрямую от тех или иных исходных данных, то эти исходные данные всегда идут ниже его. То есть у нас, соответственно, условным форматированием мы настроим показатель первого уровня, а все его характеристики идут как показатели второго и третьего уровней. Вот такая система. И все. Больше никаких правил мы вводить не желаем. Два принципа. Либо мы детализируем расчетный показатель, как мы перед этим писали, детализацию мы делаем двух уровней. То есть первый уровень – сам показатель и его детализацию вводим. Либо мы формируем тот или иной показатель в рамках финансовой модели, то есть мы его моделируем с помощью тех или иных характеристик. Тогда эти характеристики идут непосредственно снизу ну или так скажем в следующих строках от этого показателя. И данные характеристики, их вид будет выглядеть точно так же, абсолютно точно так же, как детализация. Единственное, что если данные ячейки используются как исходные значения, то естественно их мы у них свой собственный вид. У них должна быть слева звездочка, справа может стоять, соответственно, галочка вверх, если это из выпадающего списка, ну и так далее. Но важно то, что мы договорились, что у нас есть три уровня, и мы задаем три типа условного форматирования. И все. Вот именно когда мы это для себя приняли, такое решение, то после этого, если посмотреть все наши финансовые модели, а их было сделано несколько сотен за этот год, они выглядят все, ну если вот человек просто их просматривает, то такое ощущение, как будто бы это одна и та же модель. Да, они все выглядят абсолютно одинаково. Но это решение позволило сократить просто огромное количество времени при работе с финансовыми моделями. С лирикой со всей этой мы закончили. Теперь давайте все это удалим. И теперь момент следующий. Мы для себя договорились также о том, что вот это условное форматирование будет иметь некоторый внешний вид, который мы сейчас сделаем. Естественно, когда вы для себя будете создавать финансовую модель, свою финансовую модель, настройте так, как это вам будет по душе. Используйте цвета, используйте выделения, используйте что-то еще. Естественно, наш совет только одно – не перебарщивайте. Когда модель очень сильно пестрая, мы, например, очень часто сталкивались с тем, что серьезные люди начинают смотреть, у них возникает почему-то какое-то недоверие к финансовой модели, которая слишком пестрит большим количеством расцветок, подчеркиваний, выделений и так далее. В общем, финансы не любят выпендрежи, поэтому как-то, наверное, нужно стараться держаться в каком-то тонусе, в таком консервативном. Но с другой стороны, когда жизнь совсем серая, тоже, наверное, не очень интересно. Смотрите, кому как нравится. Переходим к условному форматированию. Итак, мы применяем, мы создаем условное форматирование, вид которого или, так скажем, конфигурация которого всегда определяться в рамках данной строки, будет всегда определяться тремя выделенными ячейками, в которых у нас находится показатель. То есть если у нас вот эти три столбца, данные три столбца, ну или, например, вот если мы в период идем, вот у нас вот здесь вот есть три столбца. которые будут соответствовать нашим показателям. Если мы посмотрим занятие 1, если вы посмотрите, там это все есть. Вот здесь мы подробно об этом говорили. Здесь мы уже вам напоминаем, что данные три столбца H, I и J, причем это во всех листах, эти три столбца отвечают за то, что здесь будут написаны написаны слова, соответствующие тому или иному показателю. Слова, словосочетания и так далее. То есть название показателей. И мы говорим, что данная строка, формат каждой строки внутри финансовой модели, то есть все, что у нас идет от столбца H и туда далее вправо, Вот все эти ячейки будут подчиняться закону форматирования, который определяется по первым трём столбцам, по первым трём столбцам, то есть определяется содержанием того, что находится в этих трёх ячейках. Давайте так и сделаем. Давайте теперь постараемся словами. сформулировать принципы, по которым мы будем выделять показатели верхнего уровня. Ну, показатель верхнего уровня – это показатель, который общий, да, имеет, так сказать, некоторое итоговое значение. Выручка – выручка вся, да, то есть она там дальше может разделяться на что-то, но здесь она вся. Прибыль – здесь она вся, да, там, или какое-нибудь там капитальные вложения – здесь в этом показателе они все сводятся. Дальше они могут детализироваться или там какие-то могут быть характеристики, которые характеризуют, задают, однозначно определяют тренд этого показателя. Но коль скоро он такой важный, то мы говорим так, что пусть показатель верхнего уровня всегда выделяется жирным цветом, В данном случае я здесь сделал 14 шрифт, но пусть он, допустим, здесь будет 14 шрифтом и выделен каким-нибудь здесь сереньким цветом, например, вот таким. Допустим, вот таким цветом, чтобы не сильно затемнять. Вот таким вот цветом. Сейчас это уберу. Первое, то есть определились, что показатель верхнего уровня будет закрашиваться, будет иметь две ключевые характеристики и третью, ну такую, это про размер шрифта, то есть иметь нормальный размер шрифта, основной регулярный размер шрифта, который используется в целом для модели, который там выбран. А вот две первые характеристики – это то, что он должен быть жирным и немножко сереньким, подсвечен. А, ну, кстати, давайте мы еще его и подчеркнем. Допустим, сделаем… ну, здесь можно еще такое подчеркивание сделать. То есть, если мы, допустим, здесь уберем, зайдем в параметры дополнительные, и уберем сетку. То есть если мы сеточку нашу уберем, то тогда нам желательно бы еще его подчеркнуть. То есть какое-то подчеркивание, отделение от всех остальных сделать. Итак, значит жирный цвет, подчеркивание и заливка серенькая. С тем, как это должно быть, должно выглядеть, мы определились. Что должно произойти для того, чтобы выделить эти ячейки в такой формат? Что значит показатель верхнего уровня? Это значит, что у нас, если мы используем столбцы B, C, D, это значит, что у нас в столбце B он должен быть не пустой. Ячейка в столбце B, в данном случае, например, B5, эта ячейка должна быть не пустой. а ячейки C и D должны быть пустыми. То есть если здесь у нас какой-то текст, выручка, и если в ячейке C, в столбце C, в столбце D, то есть в соответствующих справа рядом лежащих ячейках пусто, тогда мы говорим, что здесь находится показатель верхнего уровня. уже непосредственно к условному форматированию. Давайте для начала выделим, соответственно, три пока что вот эти вот ячейки. Для того, чтобы произвести условное форматирование, мы заходим в главную. Кстати, вот эта вот панелька, вот здесь вот то, что вытаскивается, она может быть скрыта, но можно ее и показать. Там, кажется, дважды, если мы на нее дважды я сейчас щелкну, она убралась, дважды если я щелкну, она здесь появится. У многих вид Excel вот такой вот представлен, но вот эта панелька сверху, она достаточно широкая и если вы занимаетесь финансовой моделью какой-то такой громоздкой, большой, то тогда вот это вот Пространство сверху, оно просто так фактически вам уменьшает общее рабочее пространство. Поэтому щелкните дважды, оно здесь вскроется. Итак, выделяем три нашей ячейки, заходим во вкладку главная, нажимаем на кнопку главная. У кого как, ну вот здесь нужно найти кнопочку условное форматирование. Нажимаем на него, условное форматирование. И потом вот здесь вот есть такое создать правила. создать правило. Смотрим, какие здесь есть возможности. Форматировать все ячейки на основании их значений. Вот здесь есть такое. Использовать формулу для определения форматируемых ячеек. Что значит использовать формулу? Здесь есть пространство для того, чтобы прописать сюда формулу. при выполнении которой, то есть если эта формула будет давать значение истина, то есть если эта формула будет выполняться, то тогда эти выделенные ячейки будут отформатированы в тот вид, который вы зададите. Здесь видите написано формат не задан, его нужно будет задать. Сейчас секундочку, телефон выключу. Значит, формулу задаем. Какая у нас формула? Что мы сказали? Что формат мы применяем только лишь тогда, когда у нас первая ячейка не пустая, то есть в ней что-то записано, там, допустим, написан какой-то текст, написано название показателя верхнего уровня, а вторые две ячейки пустые. Так и пишем, пишем равно, то есть у нас любая формула записывается равно. И у нас должно быть одновременно выполнено три условия. Одновременно, чтобы были выполнены три условия, это должно быть так. Верно A, и верно B, и верно C. Три условия, A, B, C. Соответственно, равняется, мы пишем союз И. Союз И означает, то есть скобка открывается, скобка закрывается. И это означает, что внутри все утверждения, которые внутри вот этих скобочек, Сейчас будут прописаны, они должны быть выполнены, они должны быть истинными. А сами утверждения должны разделяться точками с запятой. Значит, мы пишем И, что И? B5, первая ячейка, ячейка B5. Так и записываем, пишем B5. Единственное, что если мы хотим копировать потом вправо, влево, вверх, вниз, то, что нам нужно, поскольку мы вот эти столбцы фиксируем, что эти B, C, D, именно по ним, по этим столбцам будет все определяться, нам нужно, чтобы столбцы у нас не гуляли, а строки пусть гуляют. Соответственно, мы перед столбцом B, перед названием столбца, перед B, ну, как обычно, ставим доллар, доллар B. Перед пятерочкой мы не ставим, потому что при копировании нужно, чтобы строчки у нас могли меняться. B5 должно быть не пусто, то есть не должно совпадать с пустотой. Не пусто, то есть не записывается вот так, то есть меньше, больше. Если пишется меньше, больше, значит это означает запись союза не. А пусто, ну просто две кавычки, вот она пустота. То есть в ячеечке B5 не должно быть пустоты. Все, мы записали первое утверждение, ставим точку запятой. Второе утверждение, оно говорит о том, что С5 должно быть пусто, D5 должно быть пусто. Хорошо, пишем $C5, то есть, понятно, опять столбец C мы держим, должно быть равно пусто, пишем пусто, точка запятой $D5. Ячейка D5 должна быть пустой. Пишем равно пусто. Ну и все. То есть форматировать значения, для которых следующая формула является истинной. Вот что здесь написано. Это формула, если она выполняется, если она истинная, то мы будем эти ячейки форматировать. Как? Теперь мы нажимаем на кнопочку формат, нажимаем формат. Здесь нам предоставляется, что мы хотим. Шрифт. Какой шрифт? Мы пишем полужирный. Вот такой жирный. Кстати, можно цвет его. Кто на что гораст. Вы посмотрите. Но мы договорились, что мы должны жирный. Заливку мы выбираем вот такую слегка серенькую. И еще размер шрифта. размер шрифта размер шрифта мы его не меняем да здесь вот нельзя да условно форматировать нельзя размер шрифта здесь вот и давайте мы еще сделаем для него одинарное подчеркивать не да вот подчеркивание одинарное по значению нет не надо такого нам не надо позначаем просто от границы берем границу и берем вот это вот подчеркивание пусть будет обычного черного цвета внизу. То есть если вот это у нас и ячеечка, то, соответственно, внизу ячейки вот такое сделаем подчеркивание. Окей, ну вот у нас появилось. Если у нас, соответственно, мы, например, в ячейку C5 что-нибудь там в объем какой-нибудь, не знаю, то понятно, что у нас здесь какая-то абракадабра видна, но форматирования нет. Если мы слово выручку даже вначале уберем, тоже ничего нет. Если мы все здесь очистим, и еще раз напишем, например, себе стоимость. Вот она сразу же окрашивается в такой цвет. Теперь. Допустим, если у нас в этих ячейках будут находиться те или иные значения, себестоимости, например, то как нам эти ячейки сделать тоже в таком формате? Просто берем, копируем. Вот эту ячейку D5, например, копируем и ставим, копируем в эти четыре ячейки. Если мы здесь, смотрите, сделаем delete сейчас, то у нас все форматы уйдут. здесь вообще пусто, здесь ничего нет. если мы в ячейку b5 напишем там прибыль, то есть показатели верхнего уровня, вот у нас появляется выделение. прибыль убрали, у нас ничего нет. если мы в этих ячейках напишем 100, 200, то здесь вот так вот они выглядят просто 100, 200. А если нам необходимо показать, что это значение некоторого показателя верхнего уровня, напишем сюда прибыль, выручка. Вот оно выделилось, то есть они жирным стали. Так, хорошо, показатель первого уровня мы сделали. Теперь создаем показатель второго уровня. Ну, допустим, показатель, например, второго уровня, скажем, пусть он у нас будет нежирным обычного шрифта. То есть такая самая простая схема нежирный обычного шрифта. И что там еще? И подчеркивание, но не полностью черный, допустим, вот этой черточки сделаем, а такой серенький подчеркиванием сделаем. Давайте так. Опять заходим в вкладку главное. Условное форматирование. И еще одно правило. Создавать правило можно сколько угодно. Использовать формул для определения форматируемых ячейк. Те же самые три ячейки. И записываем новое правило. Что такое показатель второго уровня? Показатель второго уровня. Это когда у нас первые две ячейки не пустые, а последняя третья ячейка пустая. Ну, соответственно, пишем. Равняется и скобочка открывается, скобка закрывается, и внутри пишем. Внутри пишем. Доллар B. какой английский доллар b 5 не равно пусто точка с запятой доллар c 5 не равно пусто точка с запятой а доллар d 5 до 3 ячейка она у нас должна быть пусто в этом случае наш excel должен распознать соответствующую строчку как показатель второго уровня формат тогда в этом случае мы говорим формат у нас должен быть шрифт ну обычный мы сказали ничем не выделяющийся только будем его подчеркивать сплошной линией но такой вот серенькой ну давайте вот возьмем серенькая подчеркивать невзрачная Пишем окей. Соответственно, если у нас здесь написана выручка, здесь мы пишем равно, протягиваем, это у нас тоже выручка. А, ну давайте, значит, мы условное форматирование создали, мы дополнительное правило добавили. Давайте мы, соответственно, скопируем и сюда. Смотрите, здесь можно посмотреть, какие у нас правила применяются. Условное форматирование, управление правилами. Мы выделили вот эти ячейки. Видим, здесь два правила используются для вот этих ячеек. Мы скопировали сейчас. Первое правило на показатель верхнего уровня, второе правило на показатель второго уровня. Теперь мы можем это все дело взять и скопировать ниже. Например, вот сюда вставим. Выручка, мы пишем, равняется, что это у нас выручка. Причем, если у нас есть показатель второго уровня, то у него на первом уровне мы повторяем название показателя первого уровня. Об этом мы говорили в первом занятии. Выручка, а здесь пишем, например, продукт 1. Хорошо, что у нас форматирование работает. Здесь, например, продукт 2. Копируем еще ниже. Здесь, например, у нас продукт 3. Мы видим, как вот это подчеркивание более темное, данное подчеркивание для показателей второго уровня более серенькое. Вот все работает. Единственное, что нам здесь, наверное, не понравится, это то, что вот здесь, когда у нас идет показатель второго уровня, вообще хотелось бы вот это вот не видеть. То есть мы должны понимать, что здесь внутри написано слово выручка, но видеть это не особо хотелось бы. Поэтому мы можем добавить именно для вот этих вот ячеек, которые здесь находятся, только вот для этих ячеек, следующую Следующее правило. Только для ячеек, которые находятся в столбце B. Напишем условное форматирование. Создать для них дополнительное правило. Использовать формулу для определения форматируемых ячеек. Давайте мы сделаем отмену. Мы все-таки одну ячейку выделим, для нее сделаем, а потом протянем. условное форматирование, создать правило для вот этой вот ячейки. Использовать формулу. Какую мы используем формулу? То есть если у нас здесь находится показатель второго уровня, и даже если и третьего уровня, то в этом случае мы окрашиваем это в белый цвет, например. Если это будет белый цвет и будет сливаться с цветом фона, нужно, чтобы это сливалось, то показатель второго уровня, если здесь у нас, и показатель третьего, например, уровня, если они у нас будут белого цвета, фон белого цвета, ну или вообще без заливки, то тогда мы абсолютно спокойно, у нас шрифт сольется с цветом фона, мы это просто видеть не будем, в глаза не будет. Тогда мы что пишем? Что пишем равно? и скобка открывается. Если у нас есть запись в столб CB, то есть доллар B5, доллар B5 не равняется пусто, точка с запятой, точка с запятой. И доллар C5, C5 у нас тоже не равно пусто. Понятно, что доллар D5 мы можем не писать, потому что если у нас показатель третьего уровня, то для показателя третьего уровня у нас априори и в столбце B, и в столбце C у нас будет не пустое значение. Поэтому мы пишем только лишь два утверждения, это то, что в столбце B у нас не пусто, в столбце c у нас не пусто, и добавляем еще одно правило. Пишем формат ячеек и добавляем цвет шрифта. Цвет шрифта пишем. Здесь вот у нас нет, это без цвета не видно. Напишем, что просто белый цвет. И скопируем это вниз. И вот мы видим, что у нас здесь белый цвет. Хотя у нас здесь написано выручка. Конечно, лучше это написать вот так. Равняется, равняется, равняется изначальному показателю сверху. Ну, если у нас, например, продукт 3 разделяется там на какие-то размеры, например, или на подпродукты какие-то, или это у нас категория, допустим, здесь у нас категория, категория варная, категория 1. Здесь у нас товарная категория 2.3, а допустим товарную категорию 1 мы тоже желаем разделить, например, на продукт 1.2 и дальше написать продукт 1.3. То есть сделать трехуровневую систему, а здесь мы напишем, допустим, равняется товарная категория 1, равняется товарная категория 1, то в этом случае нам необходимо форматирование для показателей третьего уровня. То есть здесь мы видим, что здесь пропадает какое-либо форматирование ни первого, ни второго уровня, поскольку у нас в столбце D не пустое значение. Хорошо, давайте создаем. форматирование для показателя третьего уровня. Что мы для этого? Опять выделяем эти 3 ячейки. Заходим в главное условное форматирование. Создать правило. Использовать еще одну формулу. Пишем равняется и, скобка открывается, доллар B, английский шрифт, доллар B5 не равно пусто, точка с запятой $BCC5 не равно пусто, ну и соответственно $D5 тоже не равно пусто. Если происходит вот такое событие, когда у нас все три ячейки не пустые, значит наша Excel определяет, что это у нас показатель третьего уровня. Что мы делаем для показателя третьего уровня? Давайте мы сделаем цвет шрифта более серенький. Границу сделаем, например, пунктирную и тоже серенькую пунктирную. Заливку никакую не используем. Напишем ОК. И что нам нужно еще? Здесь у нас та же проблема. Во втором столбце у нас та же проблема, как и в первом, когда у нас используется показатель второго уровня. Здесь есть один только момент, да, вот здесь вот можно еще сделать некоторое дополнение. Видите, у нас вот здесь еще возникает граница вот у этой ячейки. Она у нас пустая. Она у нас, в смысле, не пустая, но выглядит так, как будто бы там ничего нет. Хотя там у нас написано выручка, то есть мы об этом знаем. Почему, кстати, мы оставляем здесь слово выручка? Потому что можно будет использовать формулы, то есть SUM, IF, MAN – это мы потом этой формулой будем пользоваться. Например, мы считаем выручку по товарной категории 1, тогда мы будем использовать два столбца. И везде, где написано слово «выручка» и где написано «товарная категория 1». То есть это нам важно. Но выглядит это не очень красиво. Вот именно внутри этой ячейки происходит подчеркивание. Давайте поменяем. Как поменять условное форматирование? Заходим опять в условное форматирование, только не создать правила, а управление правилами. И смотрим, какое здесь правило у нас задействовано. Здесь у нас задействовано вот это вот, ровно вот это правило. То есть, когда мы в белой В белый цвет окрашиваем шрифт, если это у нас показатель второго уровня. Выделили. И, соответственно, здесь вот есть такая кнопочка «изменить правило». Нажимаем. Соответственно, заходим в формат. И в формате мы делаем следующее. Шрифт, понятно, здесь он белый выбран, это все нормально. А еще мы убираем границу. То есть нам нужно, чтобы нижней границы никакой не было. Отжимаем вот эту вот штучку. Видим, что здесь вообще без границы, никакой границы нет. И пишем окей. Окей. Ну и применить. Окей. Соответственно копируем теперь все это дело. Мы это все дело скопировали. Мы видим, что вот здесь у нас появляется... У нас здесь... Так, а почему у нас... Так, здесь у нас все хорошо. А здесь у нас появляется... Применяется форматирование для третьего уровня. Давайте сейчас посмотрим. Это как бы иерархия. Давайте сейчас закончим с третьим уровнем. Что нам нужно сделать? Ячейки, которые у нас находятся во втором столбце, во втором столбце, они у нас тоже должны, должен шрифт закрашиваться в белый и никаких подчеркиваний не должно отображаться. Если у нас здесь возникает показатель третьего уровня. Что мы делаем? Главное условное форматирование. Создать правило. Использовать формулу для этого. Пишем равняется и скобка открывается. Доллар B5 не равно пусто, точка с запятой. $C5 не равно пусто, точка с запятой. И $CDD5 не равно пусто. Пусто. Скобка закрывается. Пишем формат. Формат какой у нас должен быть? Ну, границы нет нижней, да, вот мы ее убрали. И, соответственно, шрифт. Сейчас здесь верхняя уберется. Шрифт. Делаем белым. Окей. Окей. Ну и, соответственно, копируем именно эту ячейку. Получаем. Значит, когда мы, соответственно, сюда скопировали, мы здесь убрали название Товарная категория. Напишем товарная категория. Категория 1. Вот мы получаем, соответственно, правильно работающую показатель первого уровня, показатель второго уровня. А для показателя здесь мы пишем товарная категория 1. Здесь мы пишем товарная категория 1. И у нас, соответственно, проблемка в этой ячейке. Что здесь можно сделать? Давайте посмотрим сейчас условное форматирование управления правилами. Здесь нам необходимо просто вот это правило, мы его должны поднять выше. То есть мы их меняем местами, чтобы вот это правило То есть оно у нас здесь превалировало. Применить? Окей. Соответственно, вставляем. Получаем то, что нам необходимо. Получаем то, что нам необходимо путем копирования. Когда мы создали правило для... третьего уровня, то мы еще это дело не скопировали вниз. Вот мы сюда копируем и еще раз прописываем. Товарная категория 1. А здесь у нас продукт, допустим, 1.1. Нет, не здесь, извините. Здесь у нас тоже товарная категория 1. товарная категория 1, мы ее делим на продукты. Продукт 1.1. Вот так. Здесь у нас тоже пишем равняется товарная категория 1, продукт 1.2. Теперь мы видим, что у нас вот здесь вот нет соответствующего выделения. Понятно, что мы Как и в предыдущих случаях, мы просто-напросто берем вот это вот форматирование, которое было здесь, мы его копируем сюда. Ну и давайте прямо вот отсюда сверху скопировать. Прямо вот это вот все скопируем, вот эти четыре ячейки. И, соответственно, у нас получается вот такая вот ситуация. То есть здесь, например, если у нас здесь 100, 100 разделяется, 50 это товарная категория 1, разделяется на два продукта, 30 и 20. И вот мы видим, как это у нас все отображается. Если у нас далее мы используем, пишем равняется, выручка, и допустим у нас возникает товарная категория 2. Пишем товарная категория 2. Товарная категория 2. Давайте вот так вот скопируем. Понятно, что мы сюда не скопировали форматы. Для того, чтобы во все теперь ячейки скопировать наши форматы, мы просто-напросто берем и вот так вот ниже, например, скопируем, пишем специальная вставка. И вот здесь вот есть такое форматы. Выделяем форматы. Окей. И, соответственно, вот мы получаем товарная категория 2. Если у нас товарная категория 2, ну давайте выручка, да, вот мы пишем равняется выручка, допустим, закрепим прям B5, закрепим и протянем вниз, протянем вниз, делаем специальная вставка, формулы. Да, вот у нас везде выручка, выручка, выручка, выручка. Теперь мы ее начинаем наполнять. Товарная категория 2, пишем равняется товарная категория 2, равняется товарная категория 2, равняется товарная категория 2. Допустим, у нас по категории 2 3 продукта. Пишем продукт 2.1, продукт 2.3, продукт 2.3, а здесь поставим 2. То есть вот мы это. Ну и, к примеру, дальше у нас, например, 3 категории, например, идут, которые допустим, без товарная категория, допустим, 3. И у нас товарные категории 3, 4, 5, например, без разделения на продукты. А допустим, товарная категория 6, к примеру, она у нас разделяется, например, на два продукта. Допустим, она у нас разделяется на, напишем, продукт. 6.1, продукт 6.2. Ну и вот таким вот образом у нас формируются структуры. Если у нас здесь возникают какие-то числа, то они у нас тоже, соответственно, закрашиваются в соответствующие цвета. не соответствующие цвета, а приобретают соответствующие форматы. Вот таким вот образом мы предлагаем решить вопрос с условным форматированием. И теперь работа с финансовой моделью, она строится следующим образом. Как только вы желаете добавить тот или иной показатель в рамках листов финансовой модели, Соответственно, нужно просто-напросто брать, копировать, уже готовые форматы вставлять. И там, допустим, выручка, начальные значения, как у нас было, прирост. Здесь, если мы просто удаляем все содержимое, то у нас форматы пропадают. То есть, если мы удалили, у нас форматы пропадают. Здесь, соответственно, тоже удалили, форматы пропадают. Если у нас в данной ячейке мы, допустим, мы давайте ширину сделаем 15. Если у нас выручка задается по начальному значению и приросту, ну давайте здесь опять, как у нас там было, создадим ячейку, формат ячеек. граница, пунктирную границу поставим, закрасим эту ячейку в какой-нибудь там синий цвет и допустим начальное значение 1000, прирост 2000 и потом, соответственно, вот теперь если у нас вот эти вот характеристики, которые определяют наш показатель верхнего уровня, если вот эти вот характеристики определяют его полностью, и нам вот в этих вот строчках ничего не нужно, то, пожалуйста, выделите туда, просто скопируйте без формата. В принципе, можно просто выделить и удалить форматы, главное условное форматирование, удалить правила из выделенных ячеек. Так, чтобы там никаких линий, ничего не появлялось. То есть, если вы здесь ничего не будете использовать, то, слава богу, делайте так. Если у нас здесь идет периодизация первая, вторая, третья, четвертый период, то вот, пожалуйста, вы здесь пишите. Равняется. Если у нас, соответственно, в данной ячейке... Нет, как у нас было? у нас вот эта вот шкала времени здесь у нас равняется единице, то тогда мы сюда ставим значение, начальное значение, а иначе мы к предыдущему прибавляем прирост. Скобочку закрываем. Теперь желательно, кстати, когда мы вводим систему форматов, то нежелательно делать копирование и вставлять все, желательно делать специальную вставку. То есть обычно делается так, мы копируем и во все вот эти вот ячейки нажимаем на правую, например, кнопку мыши, пишем специальная вставка и, например, форму выставить, чтобы каждый раз форматы не перекопировать. Как только вы перегружаете условное форматирование, постоянно полностью все копируете везде, у вас будет мешанина с разными формулами по условному форматированию. Из-за этого файл перегружается. Поэтому вообще в Excel лучше всего не пользоваться при копировании. Когда вы что-то копируете, не пользоваться вот этим вот значком, вставить все. Лучше всего делать специальную вставку и вставлять ровно то, что вам нужно. Либо формулы, либо значения, либо форматы. Ну и так далее. Вот то, что мы здесь сделали. Вот так вот. Меняем на миллион или сколько. И получаем изменение показателей нашей финансовой модели. Я думаю, что никаких проблем для вас не составит то же самое сделать в рамках уже наших листов. То есть вот мы выбираем три столбика h, i и j, ну и соответственно в них настраиваем условное форматирование. В рамках занятия 1 это все есть, поэтому я здесь это не буду повторять. А, ну и так еще. Просто интересно, покажу, как у нас выглядит, если мы вот это вот все выделим. Если мы это все, допустим, закрасим в какой-нибудь там цвет, то вот мы видим, что у нас здесь... Не забывайте, что вот здесь внутри вот этих вот ячеек необходимо, чтобы внутри были соответствующие название показателей верхнего уровня. Это как раз-таки позволит нам с помощью одной-двух формул потом дальше собирать любую отсчетность финансовой модели. Это как раз-таки очень важный момент. Можно сказать, что это еще один принцип. Третий принцип – то, что когда мы в Excel используем второй столбец для показателей второго уровня, то тогда напротив этих показателей второго уровня в первом столбце всегда должно находиться название соответствующего показателя верхнего уровня. И если так можно сказать, что четвёртый принцип – это когда мы закончили с одним показателем и переходим к другому показателю, то между ними желательно одну строчку оставлять полностью пустой. И, кстати, желательно отслеживать то, чтобы на самом деле вот эти строчки, которые пустые, они были безформатированы. Это сложнее уже, но, в общем, финансовая модель такая скрупулезная, занятия лучше отслеживать. Здесь мы пишем, например, себе стоимость. Соответственно, пишем здесь равняется себестоимость, равняется себестоимость, равняется себестоимость. Ну и сразу же форматы у нас есть. Выглядит скучно, но, по крайней мере, сразу же, как это говорится, лишает нас многих проблем, связанных, на самом деле, с тем, что на эти форматы всегда уходит очень много времени. создание внешнего вида. Ну и когда мы задаем эти условные форматирования, модель выглядит строго, четко и достаточно, так сказать, устойчиво, устойчив ее внешний вид. На этом мы заканчиваем приложение 3 к занятию 1 по условному форматированию. Ну и ожидайте, занятие 2 выйдет уже, что называется, в ближайшие часы. Спасибо за внимание. До свидания.