Для иллюстраций использован Excel 2013, в предыдущих версиях другой интерфейс
и некоторые команды отсутствуют. Курсивом выделены пункты меню и отдельные термины.

1. Определите, какую роль играют электронные таблицы в вашей организации, и определите соответствующие стандарты и порядок работы с ними.

Если таблицы в вашей организации – ключевой или критический элемент, убедитесь, что они рационально разработаны, протестированы, управляются и контролируются на должном уровне. Таблицы, являющиеся частью ключевых бизнес-процессов, должны управляться совершенно иначе, чем те, что предназначены для использования непродолжительное время в индивидуальных целях.

2. Утвердите стандарт организации и придерживайтесь его.

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

Товарные знаки: Excel является зарегистрированным товарным знаком Microsoft Inc, скриншоты перепечатаны с разрешения этой корпорации

3. Удостоверьтесь, что все допущенные к созданию или работе с таблицами имеют соответствующий уровень знаний и компетенции.

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

4. Делайте работу совместно, разделяйте «владельцев», проводите экспертную оценку.

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

5. Прежде чем приступить, убедитесь, что электронные таблицы – соответствующий инструмент для выбранной задачи.

Таблицы это не панацея. Можно потратить впустую уйму времени на поиск и исправление ошибок, связанных с использованием электронных таблиц там, где уместнее применить иное ПО. Очень часто более разумно использовать текстовый редактор (если это таблица текста), организовать базу данных (если имеем дело с большим объёмом однотипных данных) или обратиться к готовым программным пакетам (если это касается хорошо налаженных процессов, таких, как бухгалтерский учет, специализированное ПО для которых легко доступно). Даже если электронные таблицы являются верным выбором, стоит поискать готовые шаблоны, прежде чем начинать с нуля.

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

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

7. Включите в документ лист с общей информацией, нечто похожее на пункт «О программе».

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

8. Дизайн на длительный срок.

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

В первой строке примера выше, если организации потребуется добавить новый отдел, достаточно вставить лист между "отд А" и "отд Г" ("отд Б2", например). Во второй строке после каждой вставки нового листа изменение придётся делать вручную.

9. Сосредоточьтесь на требуемых выходных данных.

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

10. Разделяйте и чётко обозначайте входные, рабочие и выходные данные.

Таблицу с правильной структурой (в которой соблюдается данный принцип) легко понимать и поддерживать.

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

11. Будьте последовательны в структуре.

Используйте одинаковые столбцы для одинаковых сущностей, особенно при работе с временными рядами. Следование договорённостям в рабочей книге снижает риск ошибок при ссылках между листами. Например, общепринято, что «время» идёт горизонтально слева направо (и отдельный столбец равен году), а расчёты проводятся сверху вниз. Подобная структура поможет избежать циклических ссылок.

12. Будьте последовательны в использовании формул.

На каждом листе используйте минимально возможное количество различных формул. Группы ячеек с различными формулами должны быть чётко обозначены.

На приведённом выше примере, слева формула из ячейки B15 (=$A15*B$11) была «протянута» на весь диапазон B15:D24 (сначала вниз, потом вбок). Справа же, из-за применения только относительных ссылок в каждую из 30 ячеек формулы вводились вручную. Такой способ значительно увеличивает возможность ошибки и время необходимое на проверку листа.

Продемонстрируем: выделяем данные столбца, жмём F5 > Выделить > отличия по столбцам. Эта операция покажет «неодинаковые» ячейки. В левой части скриншота сообщается, что искомых ячеек не обнаружено. Из чего следует, что все формулы столбца согласующиеся. В правой части выделены ячейки K16:K24, т.к. формулы в них отличаются от формулы в K15 – первой ячейке диапазона.

13. Составляйте как можно более короткие формулы.

Короткие формулы легче составить (что само по себе снижает вероятность ошибок), легче понять и проверить. Последовательные расчёты через промежуточные ячейки предпочтительнее использования длинных сложных формул.

14. Никогда не вставляйте изменяемые данные непосредственно в формулу.

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

На примере слева НДС введён в отдельные ячейки и формулы в диапазоне B15:B24 и E15:E24 используют ссылки на них. Кроме того, ячейки с формулами защищены. При щелчке по любой из них выводится предупреждение, что и продемонстрировано. На правом примере НДС введён в формулы как число. Если ставка НДС изменится, то во втором случае её придётся исправлять в каждой ячейке вручную. А это сразу увеличит риск ввода ошибочных значений. Вдобавок, на правом примере формулы, похоже, были «испорчены» при ручном редактировании.

15. Выполняйте вычисление один раз и затем ссылайтесь на его результат.

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

16. Избегайте использования сложных инструментов, если тот же результат получается простыми средствами.

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

17. Организуйте систему резервного копирования и контроля версий, которая должна постоянно применяться в повседневной практике организации.

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

18. Тщательно тестируйте рабочую книгу.

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

Этот пример иллюстрирует применение инструментов Зависимые ячейки и Влияющие ячейки из группы Зависимости формул. Стрелки показывают соответствующие ячейки относительно активной.

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

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

Используйте главный флаг для вывода общей суммы по отдельным флагам и расположите его на видном месте (на листе итогов, или даже на каждом листе книги в заголовках) так, чтобы пользователи не могли его пропустить.

Во втором примере выше, фактическая процентная ставка была введена в размере 12%, что на 2% превышает максимально допустимый уровень. Отсюда появление «красной точки» и поясняющее сообщение об ошибке.

20. Защищайте части рабочей книги, которые не должны меняться рядовыми пользователями.

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

«Двадцать принципов оптимального создания электронных таблиц» – адаптированный перевод доклада, подготовленного совещательным комитетом сообщества IT Faculty's Excel Community, командой Needfordata.

Бизнес-среда, в которой применяются таблицы

1. Определите, какую роль играют электронные таблицы в вашей организации, и определите соответствующие стандарты и порядок работы с ними.

2. Утвердите стандарт организации и придерживайтесь его.

3. Удостоверьтесь, что все допущенные к созданию или работе с таблицами имеют соответствующий уровень знаний и компетенции.

Проектирование и создание электронной таблицы

4. Делайте работу совместно, разделяйте «владельцев», проводите экспертную оценку

5. Прежде чем приступить, убедитесь, что электронные таблицы – соответствующий инструмент для выбранной задачи.

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

7. Включите в документ лист с общей информацией, нечто похожее на пункт «О программе».

8. Дизайн на длительный срок.

9. Сосредоточьтесь на требуемых выходных данных.

10. Разделяйте и чётко обозначайте входные, рабочие и выходные данные.

11. Будьте последовательны в структуре.

12. Будьте последовательны в использовании формул.

13. Составляйте как можно более короткие формулы.

14. Никогда не вставляйте изменяемые данные непосредственно в формулу.

15. Выполняйте вычисление один раз и затем ссылайтесь на его результат.

16. Избегайте использования сложных инструментов, если тот же результат можно получить простыми.

Риски связанные с таблицами и управление ими

17. Организуйте систему резервного копирования и контроля версий, которая должна постоянно применяться в повседневной практике организации.

18. Тщательно тестируйте рабочую книгу.

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

20. Защищайте части рабочей книги, которые не должны меняться рядовыми пользователями.