Начиная с версий Excel 2013 среди стандартных функция появилась функция ЕФОРМУЛА(ISFORMULA), которая позволяет определить наличие формулы в ячейке. Единственный аргумент функции - ссылка на ячейку: =ЕФОРМУЛА(A1).
Функция возвращает ИСТИНА(TRUE), если в ячейке есть формула и ЛОЖЬ(FALSE) если в ячейке константа.
Статья была написана ранее и описанные в ней способы применимы к любой версии Excel

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

Способ первый
Создание именованной формулы(без применения VBA - Visual Basic for Application)

  • Выделяете ячейку B1
  • жметe Ctrl+F3(вызов диспетчера имен) Если по каким-то причинам Ctrl+F3 не работает, то Диспетчер имен можно вызвать через меню:
    Excel 2003:Вставка-Имя-Присвоить.
    Excel 2007-2010:вкладка Формулы-Диспетчер имен.
  • в поле имя пишем - HasFormula(или любое понравившееся название, но не противоречащее правилам создания имен в Excel. Подробнее можно прочитать в статье: Именованные диапазоны)
  • в поле Диапазон пишем формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1)

    если изменить первый параметр на 6 =ПОЛУЧИТЬ.ЯЧЕЙКУ(6;Лист1!A1), то результатом будет текст формулы из ячейки, которая указана в функции ПОЛУЧИТЬ.ЯЧЕЙКУ

Теперь, записав в ячейку В1: =HasFormula Вы узнаете, есть ли в ячейке А1 формула или же там константа.

Так же при помощи функций листа макросов можно получить и сам текст формулы(если в ячейке записана формула СЕГОДНЯ(), то ПОЛУЧИТЬ.ЯЧЕЙКУ вернет именно текст СЕГОДНЯ()). Для этого достаточно поменять формулу в поле Диапазон на такую:
=ПОЛУЧИТЬ.ФОРМУЛУ(Лист1!A1)

Недостатки данного метода - Вы привязаны к "левостороннему" определению формулы. Т.е. таким методом Вы можете узнать есть ли формула(или какая формула) в ячейке, расположенной слева от ячейки с именованной формулой. Для того, чтобы "узнать формулу справа" именованная формула должна выглядеть так: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!C1);для работы формулы обязательно должны быть разрешены макросы. Чтобы не зависеть от левостороннего или правостороннего определения можно закрепить столбцы(выделяете ссылку на ячейку - А1 и нажимаете F4. Ссылка будет меняться, появятся знаки доллара $. Если знак стоит перед именем столбца - $А1 - то смещение по столбцу происходить не будет. Так же со строками)
Для ПОЛУЧИТЬ.ФОРМУЛУ есть еще недостаток: формула будет отображаться в стиле ссылок R1C1, что не совсем удобно.
.

Для работы такой функции должны быть разрешены макросы.


Способ второй
Еще один метод определения(на мой взгляд более удобный в использовании) - создание пользовательской функции:

Function IsFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False)
    'Application.Volatile True
    If ShowFormula Then
        If Cell.HasFormula Then
            IsFormula = "Формула: " & IIf(Cell.HasArray, "{" & Cell.FormulaLocal & "}", Cell.FormulaLocal)
        Else
            IsFormula = "Значение: " & Cell.Value
        End If
    Else
        IsFormula = Cell.HasFormula
    End If
End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(Alt+F11) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Shift+F3), отыскав её в категории Определенные пользователем (User Defined Functions).

Теперь в ячейку записываем созданную формулу
=IsFormula(A2)
Данная функция имеет два аргумента:

  • Cell - ссылка на проверяемую ячейку
  • ShowFormula - необязательный аргумент. Если присвоено значение ЛОЖЬ или 0 или опущен(не указан) - то функция вернет значение ИСТИНА или ЛОЖЬ(в зависимости от наличия или отсутствия формулы в ячейке). Если присвоено значение ИСТИНА или 1, то в функция вернет не просто ИСТИНА или ЛОЖЬ, а еще и текст самой формулы.

Формулу можно так же найти в диспетчере функций в категории Определенные пользователем - IsFormula и записывать её в ячейку через мастер функций.
Чтобы при изменении формулы в целевой ячейке сразу же изменялось её отображение в ячейке с данной функцией необходимо убрать апостроф(') перед Application.Volatile True (в файле-примере это уже сделано).

Недостатки данного метода - для работы функции обязательно должны быть разрешены макросы

Созданные функции можно так же использовать в условном форматировании для подсветки ячеек, содержащих формулы.

Скачать пример

  Tips_All_HasFormula.xls (31,0 КиБ, 4 472 скачиваний)

7 комментариев

  1. Дмитрий,объясните пож-ста,может я чего-то не понимаю,но разве формулы и функции выделенной ячейки не отображаются в поле функции под панелью инструментов?

  2. Лена, конечно, отображается! Но статья писалась для случаев, когда необходимо быстро узнать в массиве ячеек наличие формулы.
    Представьте столбец из 12000 ячеек. Формулы в 3-5 ячейках из всех. В каких - Вы не знаете. Что будет проще: воспользоваться моим спобосом или проглядеть глазками каждую ячейку?

  3. Дмитрий! Спасибо Вам огромное за публикацию этих методов!

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

    Спасибо! Вы очень помогли!

  4. Спасибо за метод.
    Если
    "необходимо быстро узнать в массиве ячеек наличие формулы"
    рекомендую нажать Ctrl~
    будут отображаться формулы, а не вычисленные значения.

  5. Excel 2013
    1. В Параметрах ставим стиль ссылок R1C1
    2. Выделяем все ячейки
    3. Условное форматирование / Создать правило
    4. В поле "Тип правила" выбираем "Использовать формулу для определения форматируемых ячеек"
    5. В формуле пишем =ЕФОРМУЛА(RC)
    6. Задаем формат, например цвет фона

    PROFIT!

    1. Спасибо большое, вы гений, в режиме ссылок R1C1 создаем правило, оно работает, затем меняем стиль на обычный, (кому как удобно) и "вуа-ля" правило становится универсальным.
      ! вот что значит нестрандартный подход

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.