Формулы Excel являются основой любой мощной электронной таблицы. Но давайте будем реальными. Они также могут стать источником разочарования, когда дела идут плохо. Небольшая опечатка или неправильно поставленная скобка могут мгновенно испортить ваши расчеты. Excel может отображать загадочные ошибки, такие как #REF или #N/A, от которых хочется кричать. Вместо того, чтобы заканчивать работу, овладейте этими навыками отладки Excel, чтобы с лазерной точностью определять источник ошибок и наводить порядок в своих электронных таблицах.
Независимо от того, являетесь ли вы профессионалом в области электронных таблиц или только начинаете свое знакомство с Excel, вы можете овладеть искусством отладки формул, чтобы сэкономить время, головную боль и, возможно, даже несколько сломанных клавиатур.
Связанный
10 творческих способов использования Excel помимо обработки чисел
Неожиданные варианты использования Excel, в которые вы не поверите
4
Основные техники
Начни с очевидного
Даже опытные пользователи Excel могут столкнуться с простыми ошибками. Прежде чем погрузиться в сложные методы отладки, обязательно сначала изучите некоторые основы. В конце концов, Excel не прощает ошибок в правописании. Одна-единственная опечатка в имени функции (SUM или SMU), ссылке на ячейку (A1 или A2) или операторе (+ или -) может испортить весь расчет.
Например, =VLOOKUP(A1, B1:C10, 2, FALSE) не будет работать, поскольку VLOOKUP написан с ошибкой. Чтобы избежать таких опечаток, вам следует использовать функцию автозаполнения Excel. Вам также необходимо учитывать круглые скобки, поскольку они будут определять порядок операций в ваших формулах. Позвольте мне остановиться на простом примере. Когда вы вводите =10 + 5 * 2, получается 20, а =(10 + 5) * 2 — 30.
Вам также необходимо использовать специальный синтаксис, который определяет порядок и тип требуемого аргумента. Например, =SUM(A1:A10) является правильным, но =SUM(A1, A10) суммирует только эти две ячейки, а не диапазон. Если вы не уверены в синтаксисе функции, используйте кнопку fx рядом со строкой формул.
3
Разобрать сложные формулы
Тестирование с простыми значениями
Когда вы имеете дело с длинными и сложными формулами, важно разбить их на части, чтобы определить источник ошибки. Вы можете разделить формулу на более мелкие части и протестировать каждую часть по отдельности. Это поможет вам определить раздел, вызывающий проблему.
Вместо того, чтобы просто смотреть на =ЕСЛИ(СУММА(A1:A10)>100, СРЗНАЧ(B1:B10), СУММА(C1:C10)), вы можете проверить СУММ(A1:A10), СРЗНАЧ(B1:B10) и СУММ(C1:C10) отдельно. Вы также можете использовать временные ячейки для отображения результатов каждой части для упрощения анализа.
Вам также следует заменить сложные части формулы простыми числами, чтобы проверить, правильно ли работает основная логика. Это поможет вам исключить проблемы с типами данных или неожиданными значениями в ваших диапазонах.
Связанный
Как я очищаю свои беспорядочные данные в Excel
Рабочий процесс очистки данных Excel, которому я доверяю
Используйте помощь Excel для устранения ошибок
Excel поставляется с мощными встроенными инструментами, которые помогут вам отслеживать и устранять ошибки в формулах. Вы найдете эти инструменты набора на вкладке «Формула» в Excel. Давайте рассмотрим несколько наиболее популярных вариантов.
Отслеживание прецедентов
Этот параметр помогает визуализировать зависимости внутри таблицы и понять, откуда берутся данные. Например, если вы проследите прецеденты для ячейки с формулой =СУММ(A1:A5), стрелки будут указывать от ячеек от A1 до A5 к выбранной ячейке.
Зависимые от трассировки
Это действие, противоположное Трассировке прецедентов, и показывает, какие другие ячейки полагаются на выходные данные выбранной ячейки. Вы можете предвидеть последствия своих изменений и избежать непреднамеренных ошибок в электронной таблице.
Оценить формулу
Это важный трюк Excel, о котором я хотел бы знать раньше. Откроется диалоговое окно, которое позволяет пошагово выполнить процесс расчета формулы. Вы можете просмотреть результаты каждой части формулы. Это очень удобно при работе со сложными вложенными формулами или формулами с несколькими функциями.
Проверка ошибок
Excel автоматически выполняет некоторые проверки ошибок в фоновом режиме. Если ни один из обычных приемов не работает, используйте инструмент «Проверка ошибок», чтобы запустить проверку на наличие распространенных ошибок, таких как противоречивые формулы или ошибки внутри формул.
1
Показать формулы, чтобы просмотреть все формулы одновременно.
Просмотрите все формулы
Проверка формулы для каждой ячейки может занять много времени. Вместо того, чтобы нажимать на каждую ячейку по отдельности, вы можете использовать параметры «Показать формулы», чтобы отобразить все формулы одновременно. Когда вы его активируете, Excel временно скрывает результаты расчетов во всех ячейках и вместо этого отображает фактические формулы.
Таким образом, у вас будет полное представление о том, как структурирован ваш рабочий лист и как взаимодействуют формулы. Он быстро сканирует ваш лист на наличие потенциальных ошибок в формулах, таких как опечатки, неправильные ссылки на ячейки или неправильно поставленные круглые скобки.
Однако в режиме «Показать формулу» вы не можете редактировать формулу напрямую. Вам необходимо записать ошибки и вернуться в обычный режим, чтобы внести необходимые изменения.
Укротите свои формулы Excel
Отладка формул Excel — это навык, которым может овладеть каждый, и он гораздо менее устрашающий, чем кажется. Добавьте этот пост в закладки и возвращайтесь к нему каждый раз, когда столкнетесь с ошибками в формулах. Вам больше не нужно бесконечно смотреть на экран, сомневаться в своей логике или обращаться к Google чтобы найти правильные ответы. Помимо стандартных формул Excel, вы также можете изучить DAX, чтобы улучшить свои расчеты.
