Если вам приходилось создавать сложные формулы со множеством вложений различных функций, то вы наверняка сталкивались с ситуацией, когда во время написания и отладки формулы возникала ошибка, которую было довольно сложно обнаружить и устранить. Да и возвращаясь к формуле через некоторое время, например, чтобы изменить в ней какую-то часть, порой приходится снова разбираться с ее логикой, на что тратится немало времени. Что уж говорить о чужих документах, в которых нужно что-то подредактировать.
Давайте разберем ряд приемов, которые помогут вам при работе с большими формулами. Для примера возьмем простую задачу - есть таблица результатов тестирования. В ней представлен список студентов и количество баллов, которые набрал каждый студент в ходе тестирования. Необходимо на основе набранных баллов проставить оценку.
Данная задача была решена с помощью следующей формулы:
Предположим, вам предстоит разобраться в этой формуле для создания аналогичной, чтобы решить похожую задачу.
Пошаговая отладка
Во-первых, на вкладке Формулы есть замечательный инструмент Вычислить формулу.
С его помощью можно пошагаво выполнить любую сложную формулу, контролируя промежуточные результаты.
В итоге понять логику формулы или найти ошибку на определенном этапе вычислений становится в разы проще.
Вычисление части формулы
Еще один прием позволит вычислить часть формулы прямо в строке формул. Это может быть полезно в том случае, если вы не понимаете, какой результат вернет вложенная в формулу функция, а использовать для этого инструмент Вычислить формулу нерационально, так как в нем нужно будет пройти все шаги до этого момента, на что потратится лишнее время.
Просто выделяем фрагмент формулы, который нужно выполнить и нажимаем клавишу F9. Важно выделять всю функцию или участок формулы со всеми открывающими и закрывающими скобками.
В итоге будет вычислен результат этого участка формулы.
Важно не забыть вернуть формуле первоначальное состояние. Для этого можно либо отменить операцию, например, нажав Ctrl + Z, либо просто нажать клавишу Esc для выхода из режима редактирования ячейки с формулой без сохранения проделанных изменений.
Контроль скобок
Одной из самых частых проблем, возникающих при работе со сложными формулами, является неправильная расстановка скобок. Например, в нашем случае есть несколько вложенных функций ЕСЛИ, в условиях которых есть функции И. Если какая-то из скобок была забыта или поставлена не в той части формулы, то появится ошибка, найти которую будет довольно сложно. Но всегда можно опираться на подсказки самого Excel.
Во-первых, открывающие и закрывающие скобки, относящиеся к одной функции имеют одинаковый цвет.
Кроме этого при перемещении текстового курсора по формуле пара скобок будет подсвечиваться жирным шрифтом, поэтому можно быстро найти в каком месте формулы находится закрывающая скобка, а если ее нет, то и текущая скобка подсвечиваться не будет.
Это позволит быстро найти и устранить ошибку.
Синтаксис функций
Также в формуле вы можете установить текстовый курсор в любую ее часть и ниже появится подсказка с синтаксисом текущей функции.
По этой подсказке можно судить, какой аргумент функции в формуле сейчас выбран и за что он отвечает.
Щелкать мышью можно не только в различных участках формулы, но и в самой подсказке. Тем самым мы подсветим конкретный аргумент функции.
Вложенные функции
Ну и еще один прием, который будет полезен при изучении чужих формул, имеющих «многоуровневую» структуру.
Например в нашем случае функции ЕСЛИ как раз и создают несколько уровней вложенности. При этом у нас довольно простые условия, но в ряде ситуаций сами условия могут содержать вложенные функции, что сильно усложнит восприятие формулы в целом. В такой ситуации можно представив формулу в более наглядном виде.
Расширим строку формул, а затем с помощью сочетания клавиш Alt + Enter вставим разрывы строк в начале каждой функции ЕСЛИ. Затем с помощью пробелов добавим отступы, то есть смоделируем уровни вложенности функций.
В таком виде формула выглядит намного более понятной и найти в ней ошибку или упростить ее будет в разы легче. Например, в нашем случае использование функций И избыточно, так как первое условие исключает нижнюю границу во втором, а второе в третьем. Поэтому можно задать простое сравнение с нижней границей из информационной таблицы. А последнюю функцию ЕСЛИ можно и вовсе убрать, ведь в случае невыполнения всех предыдущих условий останется только один вариант - "Отлично".
Формула будет работать также, как и ее более сложная предшественница, но станет заметно менее громоздкой.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм