Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

Начиная с версий 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, что не совсем удобно.
.

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


Способ второй
создание пользовательской функции.

  • Открываете редактор VBA
  • Вставляете стандартный модуль(про модули см.здесь)
  • В модуль копируете следующий код:

Теперь в ячейку записываете =IsFormula(A2).

Данная функция имеет два аргумента:

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

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

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

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

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

  Tips_All_HasFormula.xls (31,0 KiB, 3 081 скачиваний)


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

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

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

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

  2. Лена:

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

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

  4. Лена:

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

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

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

  5. Skull:

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

  6. Сергей:

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

    PROFIT!

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти