Скопировать ссылку
Цветовая кодификация
Самое простое, что вы можете сделать для значительного повышения читаемости модели – это кодифицировать цвета. Звучит заумно, но я не нашел более правильного слова, выберите для себя что-то более удобоваримое.
В вашей модели будут использоваться несколько видов данных:
· вводимые в виде цифр
· взятые по ссылкам из расчетов с других закладок этого же файла
· взятые по ссылкам из других файлов
· рассчитываемые по формулам, введенным вами в ячейках таблицы
· ячейки, предназначенные для выбора вариантов предпосылок или сценариев
Чтобы упростить восприятие модели, сделайте несложное действие – обозначьте разными цветами ячейки с этими видами данных. К примеру, я использую зеленый для жестко введенных значений, желтый для ячеек с выбором, оттенки красного для ссылок на другие закладки или файлы (исключение – основные две закладки, описанные выше), белый (без изменений) для формул. Прослеживается некоторая ассоциация со светофором или со степенью зрелости плода – у каждого в данном случае свой принцип кодификации.
Чрезвычайно важно в данном случае, чтобы эти принципы были описаны в явном виде и «бросались в глаза» при первом же открытии закладки. Это будет существенной подсказкой для того, кто займется проверкой правильности построения модели.
Единицы измерения
Обязательно нужно найти в себе силы указать все единицы измерения для всех значений в вашей модели. Это можно сделать в отдельной ячейке справа от ячейки значения (хорошо работает с одиночными переменными). В случае рядов и областей лучше это сделать в подзаголовках, о которых еще поговорим, или в описании самой переменной, если область включает ряды с разными единицами измерений.
Именование областей и использование имен в формулах
Теперь приступаем к очень важной и спорной теме, по которой мне не раз приходилось вступать в дискуссии с коллегами.
Стандартные буквенно-цифирные ссылки в формулах по принципу «морского боя» из детской игры создают очень большие проблемы для «чтения» модели. Сумма ряда C51:H51 из закладки с именем «Лист5» или даже «Амортизация» мало что дает для анализа. Еще меньше информации дает формула (С25+S51)/1.18-H253/2. Вам понадобится минимум полчаса для установления источников этих ссылок, определения того, что они означают, догадок о происхождении цифр 1.18 и 2, и потом еще неизвестное количество времени на проверку, а так ли это.
Выходом является именование областей от одной клетки до конкретных рядов или области. Для начала поясню, как это работает. В русскоязычном Excel 2010 есть закладка «Формулы», где, в свою очередь, «живет» иконка «Диспетчер имен». Здесь можно назначить имя различными способами. Здесь важно понимать применение знака $ в формулах, иначе дальнейшее изложение будет иметь мало смысла. Совсем коротко – это закрепление значения ряда и/или колонки при копировании формулы. Например, вы называете отдельную ячейку Лист1!$A$1 именем «курс_доллара». Теперь в любом месте файла, используя это имя, вы получите ссылку на значение это ячейки, не запоминая ее конкретного месторасположения. Просто пишете, к примеру, формулу «=X3*курс_доллара» и получаете искомое значение.
Здесь, конечно, еще желательно иметь в голове или где-то еще «словарь» используемых терминов, но в любом случае формулу проще «читать», чем искать, на что она ссылается. «X3» тоже неплохо бы прописать. Пусть это будет «Цена_сырья1», например.
Внимательный читатель мог обратить внимание на то, что в первом случае имя начинается со строчной буквы, а во втором с заглавной. Это не случайно. Для читателя модели это дает информацию о том, что «курс_доллара» - это отдельная ячейка, а «Цена_сырья1» - ссылка на ряд. Тоже очень полезное правило – именовать области различным образом. Так сразу будет понятно, какая из переменных одиночка, а какая стоит в ряду и может изменяться по собственным законам.
Теперь о рядах. Excel воспринимает по умолчанию цифру в «своей» колонке. Если вы в закладке «По периодам» поименовали ряд $D$6:$R$6 «Объем_производства», а ряд $D$50:$R$50 «Цена_продажи» то в ячейке H25 с формулой «=Объем_производства *Цена_продажи» появится значение «=’По периодам’!H6*H50». Еще интереснее, что точно такое же значение, взятое из закладки «По периодам», появится в любой ячейке колонки «H» любой закладки вашего файла. Таким образом, имеет смысл поддерживать порядок расположения колонок единым во всех закладках, где предполагаются ряды, при необходимости расширять число закладок за пределы минимума.
Итак, согласитесь, что прочесть формулу «=(Выручка_всего-Затраты_всего)*налог_прибыль_ставка» гораздо приятнее, чем «=(’Лист1’!D10-‘Лист 3’!AA25)*.2». Помимо уже упомянутых перечислю еще несколько полезных средств работы с именами областей в Excel:
- Найти нужную поименованную область проще всего в выпадающем окошке слева под основным меню. Можно скопировать туда имя, другой вариант начать набивать его от руки, третий «щелкнуть» на стрелочку и вам покажут все, имеющиеся в наличии. Последний вариант не очень удобен при наличии большого количества имен, как это обычно бывает в моих моделях.
- Вызвать меню диалога работы с именами можно одновременным нажатием Ctrl+F3
- Очень полезным подспорьем является возможность именования сразу многих областей, как отдельных ячеек, так и рядов или колонок. Для этого в ячейке слева, справа, сверху или снизу (я предпочитаю слева или сверху) от области, которую собираемся поименовать, вводим желаемое имя области, потом выделяем область вместе с этой ячейкой и жмем Ctrl+Shift+F3. Появляется диалоговое окно, где предлагаются варианты того, откуда взять имена для «обзывания» области. Эта функция позволяет вначале прописать все необходимые имена, а уже после это разом задать названия. Еще одним полезным свойством клетки с именем является возможность увидеть имя области напрямую, без использования функций работы с именами
- При написании формулы вы можете нажать F3, чтобы вызвать список имеющихся поименованных областей и вставить необходимую ссылку.
- Если поименовать область $D50:$T50, скажем, «ВсеПериоды», то в любой ячейке слева или справа от нее формула «=СУММ(ВсеПериоды)» выдаст вам сумму всех ячеек этой области. Что более полезно, при копировании формулы по вертикали будет автоматически использоваться подобный ряд в той же строке, в которой находится формула, то есть, к примеру в ячейке W90 вы получите сумму D90:T90.
- Еще одним полезным инструментом является именование предыдущего и следующего столбца, например «Пред_период» и «След_период». Это упрощает написание формул накопительным итогом, расчет налогов, ряд других действий. Для этого, находясь в любой ячейке, нужно нажать Ctrl+F3, написать соответствующее имя и выбрать область колонки слева или справа без цифр и значка $. К примеру, в ячейке G30 проделываем эти манипуляции и называем «Пред_период» колонку F:F.
- Отдельно следует упомянуть совместное использование пересечений областей. Скажем, если в формуле вы используете имя «Пред_период», то значение будет взято из той же строки, где формула. А если «Пред_период Затраты_всего», то есть две пересекающиеся области через пробел – из предыдущего столбца области «Затраты_всего», то есть из предыдущей колонки указанного вами ряда.
Можно еще долго перечислять вариации, но если понять базовые закономерности, то экспериментировать и изобретать можно долго. Важно не забывать обращать внимание на знак закрепления «$», правила названий областей и возможность их совместного использования. И тогда ваша модель «заговорит»!
В последней части мы коснемся логики модели, возможностей выбора вариантов значений переменных и сценариев, оформления модели, включая группирование областей и представление выходных данных, и некоторых сугубо экономических вопросов, которые имеет или не имеет смысл включать в рамки модели.
