Цветовая кодификация

Самое обычное, что вы сможете сделать для значимого увеличения читаемости модели – это кодифицировать цвета. Звучит заумно, но я не отыскал более правильного слова, изберите себе что-то более удобоваримое.

В вашей модели будут употребляться некоторое количество видов данных:

· вводимые в виде цифр

· взятые по ссылкам из расчетов с других закладок этого же файла

· взятые по ссылкам из других файлов

· рассчитываемые по формулам, введенным вами в ячейках таблицы

· ячейки, созданные для выбора вариантов предпосылок либо сценариев

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

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

Единицы измерения

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

Именование областей и внедрение имен в формулах

Сейчас приступаем к очень принципиальной и спорной теме, по которой мне не раз приходилось вступать в дискуссии с сотрудниками.

Стандартные буквенно-цифирные ссылки в формулах по принципу «морского боя» из детской игры делают очень огромные задачи для «чтения» модели. Сумма ряда C51:H51 из закладки с именованием «Лист5» либо даже «Амортизация» не много что дает для анализа. Еще меньше инфы дает формула (С25+S51)/1.18-H253/2. Для вас пригодится минимум полчаса для установления источников этих ссылок, определения того, что они означают, догадок о происхождении цифр 1.18 и 2, и позже еще неведомое количество времени на проверку, а так ли это.

Выходом является именование областей от одной клеточки до определенных рядов либо области. Для начала объясню, как это работает. В русском Excel Две тыщи 10 есть закладка «Формулы», где, в свою очередь, «живет» иконка «Диспетчер имен». Тут можно назначить имя разными методами. Тут принципиально осознавать применение знака $ в формулах, по другому предстоящее изложение будет иметь не достаточно смысла. Совершенно кратко – это закрепление значения ряда и/либо колонки при копировании формулы. К примеру, вы называете отдельную ячейку Лист1!$A$1 именованием «курс_доллара». Сейчас в любом месте файла, используя это имя, вы получите ссылку на значение это ячейки, не запоминая ее определенного месторасположения. Просто пишете, например, формулу «=X3*курс_доллара» и получаете разыскиваемое значение.

Тут, естественно, еще лучше иметь в голове либо кое-где еще «словарь» применяемых определений, но в любом случае формулу проще «читать», чем находить, на что она ссылается. «X3» тоже хорошо бы прописать. Пусть это будет «Цена_сырья1», к примеру.

Внимательный читатель мог направить внимание на то, что в первом случае имя начинается со строчной буковкы, а во 2-м с большей. Это не случаем. Для читателя модели это дает информацию о том, что «курс_доллара» — это отдельная ячейка, а «Цена_сырья1» — ссылка на ряд. Тоже очень полезное правило – называть области разным образом. Так сходу будет понятно, какая из переменных одиночка, а какая стоит в ряду и может изменяться по своим законам.

Сейчас о рядах. Excel принимает по дефлоту цифру в «своей» колонке. Если вы в закладке «По периодам» поименовали ряд $D$6:$R$6 «Объем_производства», а ряд $D$50:$R$50 «Цена_продажи» то в ячейке H25 с формулой «=Объем_производства *Цена_продажи» появится значение «=’По периодам’!H6*H50». Еще увлекательнее, что точно такое же значение, взятое из закладки «По периодам», появится в хоть какой ячейке колонки «H» хоть какой закладки вашего файла. Таким макаром, имеет смысл поддерживать порядок расположения колонок единым во всех закладках, где предполагаются ряды, по мере надобности расширять число закладок за границы минимума.

Итак, согласитесь, что прочитать формулу «=(Выручка_всего-Затраты_всего)*налог_прибыль_ставка» еще приятнее, чем «=(’Лист1’!D10-‘Лист 3’!AA25)*.2». Кроме уже упомянутых перечислю еще несколько нужных средств работы с именами областей в Excel:

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

— Вызвать меню диалога работы с именами можно одновременным нажатием Ctrl+F3

— Очень полезным подспорьем является возможность именования сходу многих областей, как отдельных ячеек, так и рядов либо колонок. Для этого в ячейке слева, справа, сверху либо снизу (я предпочитаю слева либо сверху) от области, которую собираемся поименовать, вводим хотимое имя области, позже выделяем область вкупе с этой ячейкой и нажимаем Ctrl+Shift+F3. Возникает диалоговое окно, где предлагаются варианты того, откуда взять имена для «обзывания» области. Эта функция позволяет сначала прописать все нужные имена, а уже после это разом задать наименования. Еще одним полезным свойством клеточки с именованием является возможность узреть имя области впрямую, без использования функций работы с именами

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

— Если поименовать область $D50:$T50, скажем, «ВсеПериоды», то в хоть какой ячейке слева либо справа от нее формула «=СУММ(ВсеПериоды)» выдаст для вас сумму всех ячеек этой области. Что более полезно, при копировании формулы по вертикали будет автоматом употребляться схожий ряд в той же строке, в какой находится формула, другими словами, например в ячейке W90 вы получите сумму D90:T90.

— Еще одним полезным инвентарем является именование предшествующего и последующего столбца, к примеру «Пред_период» и «След_период». Это упрощает написание формул накопительным итогом, расчет налогов, ряд других действий. Для этого, находясь в хоть какой ячейке, необходимо надавить Ctrl+F3, написать соответственное имя и избрать область колонки слева либо справа без цифр и значка $. Например, в ячейке G30 проделываем эти манипуляции и называем «Пред_период» колонку F:F.

— Раздельно следует упомянуть совместное внедрение пересечений областей. Скажем, если в формуле вы используете имя «Пред_период», то значение будет взято из той же строчки, где формула. А если «Пред_период Затраты_всего», другими словами две пересекающиеся области через пробел – из предшествующего столбца области «Затраты_всего», другими словами из предшествующей колонки обозначенного вами ряда.

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

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