Как упростить сложные формулы Excel для лучшего аудита

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

Используйте именованные диапазоны и таблицы

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

Если вы по умолчанию используете такие координаты, как A1 или C500, это заставит любого, кто читает электронную таблицу, искать источник, просто чтобы понять логику. Используя таблицы и именованные диапазоны, вы заменяете эти абстрактные ссылки на сетки понятным человеку языком.

Учитывайте разницу в ясности. Вот формула, содержащая ссылки на ячейки из различных листов:

=SUM('Sales Data'!C2:C500)*(1-'Rates'!G2)

Когда вы называете свои данные, тот же расчет становится следующим:

=SUM(T_Sales[Amount])*(1-Tax_Rate)

Обратите внимание, насколько легче усваивается вторая формула. Он описывает то, что рассчитывается, а не только то, где живут числа. Этот подход также обеспечивает глобальную область действия ваших данных — поскольку Excel распознает эти имена по всей книге, вы можете избавиться от длинных и беспорядочных ссылок на листы, которые обычно засоряют панель формул.

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

Убить двойную скобку

Как только вы начнете использовать таблицы Excel, вы заметите, что Excel создает структурированные ссылки — особый способ ссылки на данные по имени таблицы и заголовкам столбцов (например, «Продажи»).[Amount]) вместо адреса ячейки (например, C2). Они отлично подходят для чтения, но могут быстро стать бельмом на глазу, если ваши заголовки содержат пробелы или специальные символы.

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

=[@[Units sold]]*[@[Price per unit]]

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

=[@Sold]*[@[Price/Unit]]

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

Связанный

Как использовать круглые, квадратные и фигурные скобки в Microsoft Excel

Кто знал, что простые символы могут иметь такую силу?

Думайте вертикально при написании длинных формул.

Написание формул Excel в виде единой горизонтальной стены текста — стандартная привычка, но она становится серьезным препятствием, когда вы имеете дело со сложной логикой. Итак, чтобы лучше контролировать свою работу, выберите вертикальную компоновку.

К используя Alt+Вводвы можете вставлять разрывы строк непосредственно в строку формул. Это позволяет вам объединять различные части формулы, например, различные условия в Заявление IFS или переменные в формуле LET друг над другом.

=IFS(
[@Revenue]>2000,"High",
[@Revenue]>1000,"Mid",
[@Revenue]>0,"Low",
TRUE,"No Sales"
)

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

Модернизируйте использование функций

Использование устаревших функций — один из самых быстрых способов создания спагетти по формуле. Многие из наиболее распространенных проблем в Excel — например, неверные номера индексов или бесконечные вложенные циклы — были решены с помощью новых, более эффективных функций. Обновление словаря формул необходимо для поддержания чистоты и устойчивости вашей логики.

Связанный

6 функций, которые изменили способ использования Microsoft Excel

Функции динамических массивов изменили правила игры.

XLOOKUP против VLOOKUP

Одной из главных причин нечитаемости формул является номер индекса столбца в формуле ВПР. Если вы когда-нибудь видели формулу, заканчивающуюся:

,4,FALSE)

вы знаете разочарование, когда пытаетесь вспомнить, к какому столбцу на самом деле относится цифра 4. XLOOKUP устраняет это используя прямые ссылки на диапазон. Более того, если вы вставляете столбец в свои данные, XLOOKUP настраивается автоматически, тогда как VLOOKUP просто прерывается.

IFS против вложенных IF

Вложенные операторы IF печально известны своим кошмаром скобок — длинной цепочкой закрывающих скобок в конце формулы, которую приходится считать вручную. Функция IFS позволяет тестировать несколько условий в одном линейном списке.

Связанный

Прекратите писать в Excel вложенные формулы IFS и IFS: вместо этого используйте SWITCH.

Напишите более понятную логику Excel, исключив повторяющиеся и длинные формулы.

ПОЗВОЛЯТЬ

ДАВАЙТЕ функция меняет правила игры при длительных вычислениях. Он позволяет один раз присвоить имя результату вычисления, а затем повторно использовать это имя в формуле. Вместо повторения сложного ВПР три раза в одной строке вы можете дать ему имя и вместо этого ссылаться на это имя.

Не бойтесь вспомогательных столбцов

Часто возникает искушение доказать свое мастерство в Excel, объединив весь рабочий процесс в одну основную формулу. Однако если формула состоит из 500 символов, это не шедевр — это обуза. Разбиение массивных вычислений на два или три вспомогательных столбца — отличительная черта опытного строителя, который ставит прозрачность выше сложности.

Здесь вместо использования этой единственной длинной формулы для расчета просроченной комиссии:

=IF([@Status]="Unpaid",IF(TODAY()>[@Due],[@Amount]*0.05,0),0)

Я использовал три отдельные формулы в столбцах E, F и G.

Это приносит несколько преимуществ:

• Изолирование конкретных шагов значительно упрощает проверку конечного результата.

• Если данные выглядят неправильно, мне не нужно тратить время на отладку длинной формулы — я могу просто просмотреть вспомогательные столбцы, чтобы точно определить, где логика сломалась.

• Вспомогательные столбцы дают мне больше информации, чем один столбец.

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

ОС

Windows, macOS, iPhone, iPad, Android

Бесплатная пробная версия

1 месяц

Microsoft 365 включает доступ к приложениям Office, таким как Word, Excel и PowerPoint, на пяти устройствах, 1 ТБ хранилища OneDrive и многое другое.