Трюк №18. Как искать в Excel 2010 формулы при помощи условного форматирования

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

Код VBA в этой пользовательской функции (также называемой функцией, определенной пользователем) позволяет идентифицировать ячейки, содержащие формулы, не щелкая и не изучая все 10 000 ячеек. Чтобы превратиться в хитрого охотника на формулы, начните с перехода в редактор Visual Basic→Сервис→Макрос→Редактор Visual Basic (Tools→Macro→Visual Basic Editor) — и выберите команду Insert→Module. Введите следующую функцию:

1
2
3
4

// Листинг 1.19
Function IsFormu1a(Check_Cell As Range)
	IsFormula = Check_Cell.HasFormula
End Function

Закройте окно (нажмите сочетание клавиш Alt/Apple+Q или кнопку закрытия в строке заголовка окна). Теперь эта функция доступна из любой ячейки на любом листе в этой книге; для этого нужно ввести формулу =IsFormula($A$l). Добавить эту функцию можно также, выбрав команду Вставка→Функция (Insert→Function), для параметра Категория (Category) выберите вариант Определенные пользователем (UserDefined) и в списке функций выберите IsFormula.

Формула возвращает ИСТИНА (TRUE), если в соответствующей ячейке содержится формула, и ЛОЖЬ (FALSE), если это не так. Этот булевский результат можно использовать совместно с условным форматированием, чтобы автоматически выделить все формулы с применением нужного форматирования.

Одно из основных преимуществ этого метода заключается в том, что возможности идентификации формул на листе динамические. Это означает, что, если вы добавите или удалите формулу, то форматирование изменится соответствующим образом. Вот как это сделать: выделите диапазон ячеек на листе, скажем, A1:J500, и захватите еще несколько ячеек на случай, если формулы будут добавлены позднее.
[stextbox id=»warning»]Не выделяйте весь лист, так как этот метод может увеличить размер и излишне нагрузить электронную таблицу. [/stextbox]
Теперь, когда ячейки выделены, причем ячейка А1 должна быть активной, выберите команду Формат→Условное форматирование (Format→Conditional Formatting). В поле условий выберите Формула (Formula Is) и в поле справа от него введите =IsFormula(A1). Щелкните кнопку Формат (Format) и выберите нужное форматирование, которое будет применяться для идентификации ячеек с формулами. Щелкните на кнопке ОК, затем еще раз щелкните на кнопке ОК.
[stextbox id=»warning»]Иногда при вводе формул в окне условного форматирования Excel пытается добавить кавычки вокруг формул после того, как вы щелкаете на кнопке ОК. Это означает, что Excel распознал то, что вы ввели, как текст, а не как формулу. Если это произошло, вернитесь в окно Условное форматирование (Conditional Formatting), удалите кавычки и щелкните на кнопке ОК.[/stextbox]
Теперь указанная формула должна быть применена ко всем ячейкам на листе, содержащим формулы. Если вы удалите или исправите содержимое ячейки с формулой, условное форматирование исчезнет. Схожим образом, если вы введете новую формулу в любую ячейку диапазона, она также будет выделена форматированием.

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