Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Как узнать есть ли формула в ячейке?

Начиная с версий 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 KiB, 4 446 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 7 комментариев
  1. Юзер:

    однозначно, Эксель - это не для меня, но всё равно спасибо за полезные советы!

  2. Лена:

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

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

  4. Лена:

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

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

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

  5. Skull:

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

  6. Сергей:

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

    PROFIT!

    • Владимир:

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

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти