Как создавать электронные таблицы Excel, которые просты в использовании и радуют глаз.
С помощью таблиц Excel финансовые менеджеры могут коммуницировать со слушателями и пользователями, демонстрируя результаты оценки проектов, прогнозы данных или какой-либо другой вид количественного анализа. Обратите внимание, что мы используем глагол "коммуницировать". Таблицы – это отличный инструмент для коммуникаций и выступлений. Тем не менее эта связь часто омрачается плохими макетами, недостаточным или излишним количеством ярлыков и несоответствиями, которые вызывают проблемы в понимании цели и контента таблиц у конечных пользователей. Эти проблемы могут привести к ненужной дополнительной работе, затруднить принятие решений топ-менеджерами или к неправильным выводам, основанным на ошибках в логике и/или формулах, которые не лежат на поверхности.
Поэтому необходимо подумать о разработке дизайна и структуры электронных таблиц, а не только об используемых формулах, функциях и форматах. Об этом мы и поговорим в статье, где рассмотрим основные принципы построения качественных моделей в Excel. Начнем с того, что хорошая модель имеет четыре ключевых атрибута, называемых CRaFT (от английского Consistency, Robustness, Flexibility, Transparency):
• последовательность;
• надежность;
• гибкость;
• прозрачность.
Создать эффективный коммуникационный инструмент путем составления соответствующей финансовой модели просто: необходимо только правильно спроектировать модель и правильно определить ключевые данные, которые необходимо представить для обсуждения. Проблема в том, что времени всегда мало и все ждут быстрых результатов. Составление правильной структуры макета не решит все проблемы, но положит хорошее начало.
Итак, начнем. И начнем с пустой рабочей таблицы. Первое, что мы делаем, – это сужаем первые несколько столбцов от столбца A до столбца E, так удобнее будет работать со всей таблицей (выделить столбцы, затем щелкнуть правой кнопкой мыши и выбрать Ширину столбца (Column Width) из всплывающего контекстного меню).
Выбираем ширину 3, тогда ячейка будет квадратной.
Можно выделить другое количество столбцов или использовать другую ширину. Есть два ключевых момента:
• используйте столбец А только для названия Заголовка таблицы. Почему? Расскажем далее;
• будьте последовательны и используйте одинаковую ширину для идентичных столбцов на всех рабочих листах.
Даем Заголовок таблицы в ячейке A1. Он должен быть таким же, как в описании на вкладке листа. В этом примере мы назвали его "Заголовок листа" (Sheet Title), чтобы подчеркнуть назначение этих ячеек, но без кавычек:
Вот некоторые рекомендации:
• учитывая, что названия таблиц листов не могут быть бесконечно длинными, старайтесь Заголовок листа делать максимально кратким и понятным для конечного пользователя;
• учитывая, что Заголовок листа размещен на листе, название должно быть написано формально и не должно состоять из непонятной аббревиатуры;
• такой подход способствует согласованности – одной из четырех ключевых концепций построения наилучшей модели.
В ячейке A2 пишем название модели. Это важно, поскольку в отчетах и презентациях PowerPoint мы часто даем только выдержки из книг Excel. Поэтому удобно размещать название модели здесь, а не в верхнем (Header) или нижнем колонтитуле (Footer) каждого рабочего листа (как вариант, можно делать в обоих местах):
Если вы хотите добавить название в верхний или нижний колонтитул, вы можете использовать комбинацию клавиш ALT + P + SP -> "Верхний/Нижний колонтитул" -> "Изменить колонтитул ..." (ALT + P + SP -> ‘Header / Footer’ tab -> ‘Custom Footer…’). Колонтитул сделать проще, но в таком случае название будет отображаться только при распечатке рабочего листа. А что делать, если таблица будет изображением на слайде PowerPoint или, скажем, приложением в документе Word? Вот почему удобно отображать название модели впереди и в центре на рабочих листах.
Следующий ключевой элемент в верхней части таблицы – это навигационная ссылка. Рабочие книги Excel могут быть довольно сложными, со многими рабочими листами. В этом случае центральная навигационная страница "Оглавление" (Table of Contents) будет очень кстати – это позволит конечному пользователю быстро и легко перемещаться по книге.
Кажется, что в ячейку A3 добавили гиперссылку, верно? На самом деле выделили ячейки A3:F3, а затем объединили ячейки с помощью функции Слияние и Центр (Merge Across) (комбинация клавиш ALT + H + M + A):
Гиперссылки могут быть легко созданы с помощью сочетания клавиш CTRL + K (или нажатием кнопки "Ссылка" (Link) на вкладке "Вставка" (Insert) для ссылки на место в вашем документе. Главное – создать основной рабочий лист с содержанием (Table of Contents), где находятся все гиперссылки на все рабочие листы:
Гиперссылка должна ссылаться на ячейку A1 этого рабочего листа, и эта ячейка должна иметь имя диапазона, например HL_TOC. Важно указывать имя диапазона, чтобы избежать неработающей ссылки, если кто-то переименует лист назначения. HL означает, что ячейка используется в качестве адресата для гиперссылки. Ячейки A3:F3 объединяются, так что если пользователь нажимает в любом месте этого диапазона, гиперссылка активируется; иначе пользователю придется нажать именно на ячейку A3 для запуска гиперссылки.
Итак, мы подошли к ячейке A4:
Мы можем добавить здесь формулу, чтобы выводить любые проверки ошибок, присутствующих в модели. Как правило, чтобы ошибки выводились в одном месте на листе, даты и другие заголовки можно также указывать в этой строке:
В нашем примере столбец G является столбцом Units (единица измерения). В этом столбце мы будем указывать единицы измерения, чтобы пользователи могли различать числовые поля. Как часто вам попадалась в руки распечатка и вы не знали, данные указаны в $ или $000, или m, или кг? Предложенный подход решает эту проблему. Следует отметить, что этот столбец не всегда нужен. Как вариант, вы можете просто указать в верхней части листа: "Все данные отображены в $K, если не указано иное".
Ячейки J4:N4 содержат заголовки дат. Даты должны быть периодическими (например, ежемесячно, ежеквартально, ежегодно) и всегда должны начинаться и заканчиваться в тех же столбцах (и строках) на каждом листе рабочей книги. Это не всегда возможно: иногда вам требуется, чтобы некоторые из ваших данных прогнозировались ежегодно, а некоторые – ежемесячно. В таких случаях, когда необходимы периодические отчеты, эти несогласованные рабочие листы должны быть четко выделены из других областей рабочей книги.
Вы, наверное, заметили, что между строками 4 и 5 есть линия:
Это не нарисованная линия. Это закрепленная область (Freeze Pane). Закрепленные панели разбивают рабочий лист на четыре сектора и позволяют двум секторам листа оставаться всегда на виду ("замораживаться"), пока пользователь прокручивает лист вдоль или поперек. На вкладке "Вид" (View) есть три способа создания закрепленных панелей:
• "Закрепить верхнюю строку" (Freeze Top Row): удерживает верхний ряд видимым независимо от того, насколько далеко вниз вы прокручиваете таблицу;
• "Закрепить первый столбец" (Freeze First Column): фиксирует первый столбец независимо от того, насколько далеко вправо вы прокручиваете таблицу;
• "Закрепить область" (Freeze Panes): фиксирует область в заданном месте пересечения верхней строки и первого столбца выбранной ячейки.
В нашем примере ячейка A5 была сделана основой для закрепленной панели, так что строки с 1 по 4 всегда будут видны. Этой ячейке должно быть присвоено название диапазона, например HL_Home, поскольку это ячейка, к которой должны привязываться гиперссылки на этот лист. Добавив "замороженную" панель, мы упрощаем навигацию по модели. Эта ячейка может быть идентифицирована с помощью сочетания клавиш CTRL + HOME.
Сейчас давайте вернемся к тому, почему мы сузили первые несколько столбцов. Итак, мы даем ниже заголовки данных. Заголовки лучше начинать со столбца В, а не А. Таким образом мы создаем видимый отступ. Сначала указываем заголовок, ниже – подзаголовки.
Мы назвали их "заголовками", "подзаголовками" и т. д., чтобы было понятно. Но такой подход быстро станет трудным для восприятия. Поэтому мы рекомендуем называть заголовки и подзаголовки "Заголовок 1", "Заголовок 2" и т. д. Это также делает их совместимыми с предустановленными стилями заголовков в Excel.
Теперь, кажется, понятно, почему лучше сужать столбцы, A, B, C и D (и на всякий случай сохранять столбец E)? Сужение столбцов урезает заголовки и упрощает чтение и перемещение по рабочим листам. В особенности, если сетка отключена.
Обратите внимание на промежуток между названиями заголовков: один пустой ряд между заголовками; две линии между разделами. Можно выбрать свой стиль, если хотите, главное – быть последовательными. Это упрощает копирование разделов и использование ссылок, если промежутки одинаковые и предсказуемые.
Пустые столбцы H и I предусмотрены для случаев, если есть какие-либо вычисления, входные данные или значения, которые не относятся к определенному периоду времени. Если таковых не будет, их можно сузить до ширины 1, а если вдруг они понадобятся – они уже есть.
Добавление меток, данных и формул:
Сейчас наш пример уже больше похож на электронную таблицу. Следующим шагом будет использование стилей (комбинация ALT + H + J), как показано ниже:
Теперь наша таблица очень быстро становится красивой:
Если отключить линии сетки в таблицах, то большинство файлов будет иметь белый фон. Идея белого фона состоит не только в эстетике. Добавление цвета на фон таблицы, скорее всего, сделало бы файл очень "тяжелым".
Хотите перемещаться между основными заголовками в столбце B? Нажмите на ячейку B6, перейдите CTRL + Стрелка вниз, и вы попадете на ячейку B18. Повторите это действие и следующая ячейка, на которую вы попадете, будет ячейкой B1048576, то есть самой нижней частью таблицы, потому что в этом столбце после ячейки B18 нет никаких данных.
Нажмите на ячейку D10 (Заголовок 3) и используйте комбинацию клавиш CTRL + Стрелка вниз, чтобы перейти к ячейке D15, последней ячейке в смежном диапазоне. Комбинации клавиш CTRL + Стрелка вверх, CTRL + Стрелка вправо или CTRL + Стрелка влево будут выполнять аналогичные действия. Нужно выделить диапазон? Нажмите любую ячейку в пределах диапазона и нажмите комбинацию CTRL + A.
И последний вопрос на сегодня: почему колонка А пустая? Это функциональная колонка для учета "незаконченной работы". Часто мы отрываемся от работы, это может быть срочный вопрос от руководства, звонок родных, собрание или конференц-колл. Когда мы отвлекаемся, необходимо запомнить, где мы остановились. Прежде чем отложить работу с таблицей, нажмите клавишу HOME, которая приведет вас к столбцу A той строки, с которой вы работаете в данный момент. Введите что-нибудь в этой ячейке, например, "w" для "work-in-progress" или "с" для "check", любой символ или буква подойдут. И все, место остановки работы зафиксировано.
Независимо от того, какую задачу вы решаете с помощью таблиц Excel, главный совет: используйте принципы CRaFT: последовательность, надежность; гибкость; прозрачность.
По материалам: www.fm-magazine.com
_____________________________________________
© ТОВ "ІАЦ "ЛІГА", ТОВ "ЛІГА ЗАКОН", 2018
У разі цитування або іншого використання матеріалів, розміщених у цьому продукті ЛІГА:ЗАКОН, посилання на ЛІГА:ЗАКОН обов'язкове.
Повне або часткове відтворення чи тиражування будь-яким способом цих матеріалів без письмового дозволу ТОВ "ЛІГА ЗАКОН" заборонено.
