Перейти к содержимому

Как выделить ячейки с формулами в Excel

В Excel не предусмотрено встроенной функции для поиска формул. Когда формула введена в ячейку, узнать, является ячейка постоянным значением или значением, полученным из формулы, можно, только щелкнув ячейку и взглянув на строку формул или же нажав Ctrl+~ (тильда; чтобы отменить режим показа формул, нажмите Ctrl+~ еще раз). Предлагаемый трюк позволит вам выделить ячейки с формулами при помощи трех строчек кода VBA и условного форматирования. [1]

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

Если вы представляете, о чем речь, пройдите по меню Разработчик –> Visual Basic и в открывшемся окне Microsoft Visual Basic for Applications пройдите по меню Insert –> Module (рис. 1). В окне нового модуля наберите следующий код:

Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function

Рис. 1. Код пользовательской функции IsFormula в окне Microsoft Visual Basic for Applications

Рис. 1. Код пользовательской функции IsFormula в окне Microsoft Visual Basic for Applications

Скачать заметку в формате Word или pdf, скачать пример в формате Excel (с встроенным кодом VBA)

Закройте окно Visual Basic, нажав Alt+Q или пройдя по меню File –> Close and Return to Microsoft Excel. Вы создали функцию IsFormula. Если вы создали функцию в модуле, относящемся к этой рабочей книге (как на рис. 1), а не в модуле Личной книги макросов – Personal.xlsb, то функция будет доступна только в этой конкретной книге Excel. Чтобы ваш труд не пропал даром, сохраните Excel-файл командой Сохранить как, и выбрав тип файла Книга Excel с поддержкой макросов (*.xlsm). Ваша пользовательская функция, как и любая встроенная функция, доступна в окне Вставка функции в категории Определенные пользователем (рис. 2).

Рис. 2. Пользовательская функция доступна в окне Вставка функции

Рис. 2. Пользовательская функция доступна в окне Вставка функции

Также вы можете вызвать функцию, просто, начав набирать в ячейке =i (рис. 3).

Рис. 3. Функцию можно выбрать через подсказку, начав набирать в ячейке =i

Рис. 3. Функцию можно выбрать через подсказку, начав набирать в ячейке =i

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

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

Выделите диапазон ячеек, заполненный данными, плюс сделайте запас на случай, если данные будут добавляться. Не выделяйте весь лист, так как это может увеличить размер файла (иногда катастрофически). В нашем примере (рис. 4) я выделил область А1:К28. Причем ячейка А1 должна быть активной.

В качестве примера в диапазон А1:F20 я ввел формулу =СЛЧИС(). Затем выделил весь диапазон, скопировал его в буфер, и вставил как значение. После этого в нескольких ячейках снова вставил формулу =СЛЧИС().

Рис. 4. Создание правила форматирования для выделеной области

Рис. 4. Создание правила форматирования для выделеной области

Пройдите по меню Главная  –> Условное форматирование –> Создать правило (я работаю в Excel2013). В открывшемся окне Создание правила форматирования выберите опцию Использовать формулу для определения форматируемых ячеек. И в поле Форматировать значения, для которых следующая формула является истинной введите =IsFormula(A1). Щелкните кнопку Формат рядом с полем Образец и выберите желтую заливку для идентификации ячеек с формулами. Щелкните ОК (рис. 5).

Рис. 5. С помощью кода VBA и условного форматирования удалось выделить ячейки, содержащие формулы

Рис. 5. С помощью кода VBA и условного форматирования удалось выделить ячейки, содержащие формулы

Получился ножиданный эффект, который можно наблюдать в прикрепленном Excel-файле. Я считал, что функция =СЛЧИС() пересчитывается только при изменении хотя бы одного значения на листе. Так вот, на листе формально не происходит изменения значений. Но, видимо, функция IsFormula, участвующая в условном форматировании, как-то влияет на этот процесс, так что экран оживает, и значения постоянно изменяются. 🙂

Иногда при вводе формул в окне условного форматирования Excel пытается добавить кавычки вокруг формул после того, как вы щелкаете на кнопке ОК. Это означает, что Excel распознал то, что вы ввели, как текст, а не как формулу. Если это произошло, вернитесь в окно Условное форматирование, удалите кавычки и щелкните ОК.

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

 

[1] По материалам книги Р.Холи, Д.Холи. Excel 2007. Трюки, стр. 70–72

10 комментариев для “Как выделить ячейки с формулами в Excel”

  1. Евгений Терехов

    Если нахождение ячеек с формулами — это разовая или не очень частая операция, то вполне можно воспользоваться уже имеющимся функционалом, без использования пользовательских функций.
    Вкладка ленты «Главная», секция «Редактирование», кнопка «Найти и выделить», в выпадающем меню выбрать пункт «Формулы».
    Ну или CTRL+G -> «Выделить» -> «Формулы» — результат аналогичный.
    Чуть подробнее о выделении специальных диапазонов можно прочитать тут: http://allexcel.info/2013/04/04/выделение-хитрых-диапазонов/

  2. Николай Павлов

    Добрый день, Сергей! Спасибо за интересные материалы по Excel и бизнесу — очень здорово пишете!

    По поводу статьи хотел добавить, что в Excel 2013 можно воспользоваться новой функцией ЕФОРМУЛА, которая как раз определяет, есть ли в ячейке формула или нет.

  3. А смысл? Как с этим работать? Если тебя интересует в какие ты ячейки занес формулы, а в какие забыл или наоборот перестарался, то первый вариант гораздо выгоднее, ибо не надо каждый раз находить и выделять их, чтобы править по одной.

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

    Но возникла обратная задача: выделить через УФ ячейки, НЕ содержащие формулы.
    Текущее решение: сначала создать правило с использованием ЕЧИСЛО и нужной раскраской, а затем еще одно правило, использующее функцию ЕФОРМУЛА без изменения формата.

    Корявенько, но работает. Однако, может есть путь в 1 шаг?

  5. Сергей Багузин

    Сергей, подойдет такое правило условного форматирования
    =ЕФОРМУЛА(A1)=ЛОЖЬ

  6. Сергей Викторович, благодарю! Всё гениальное — просто. Надо просто включать голову и выходить за рамки привычного квадрата ограничения мышления…

  7. Здравствуйте
    Перенес код в свой файл с подобніми функциями.
    Но, к сожалению, получаю ошибку (скриншот во вложении)
    Эту ошибку получаю при создании нового правила ли редактировании созданного Вами в Вашем примере.
    В данном случае использую 2010 ексель
    В чем причина, ошибки?

    С уважением

    Вложение

  8. Сергей Багузин

    Виктор, я спросил у Chat GPT, в какой версии Excel появилась функция IsFormula. Ответ в Excel 2013. 🙂

  9. Сергей Багузин, спасибо за ответ.
    Но в моем случае я использую пользовательскую функцию, предложенную автором статьи. Даная функция работает как обычная формула столбец H16 как видно на скриншоте, но почему-то не работает вместе с условным форматированием. И это для меня загадка.

    С уважением

  10. Сергей Багузин

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *