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

Хитрости »

1 Май 2011              82087 просмотров

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

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

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

    Видеоуроки

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



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

Всем привет.

При обработке некоторой таблички в MS Excel возникла простенькая задача — надо было найти все формулы в которых участовала определенная ячейка. Ммм, попробуйте далее не читать и решить ее самостоятельно.

Ну а кому не втерпеж, слушаем далее. Итак для примера мне надо найти кто использует ячейку B4.

Первое решение — используем поиск. Указываем ячейку в поиске и нажимаем «Найти все». В результате имеем список всех ячеек где наша B4 участвует в расчетах. Для чего не подходит такой способ? Это не подходит в случае, если наша ячейка участвует  в диапазоне типа «=СУММ(B3:B7)» или ссылка абсолютная «=B$4».

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

Использование окна Выделение группы ячеек

Этот метод выявления ячеек с формулами легкий, но не динамический. Другими словами, он хорош для единичной проверки.

1. Выберите одну ячейку в листе.
2. Выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек для открытия диалогового окна Выделение группы ячеек.
3. В окне Выделение группы ячеек установите переключатель в положение формулы и убедитесь, что все флажки ниже установлены.
4. Нажмите кнопку ОК. Excel выберет все ячейки с формулами.
5. Нажмите кнопку Цвет заливки в группе Шрифт вкладки Главная. Выберите любой цвет, который еще не используется.
6. Используйте элемент управления Масштаб и задайте для своего листа небольшой масштаб (например, 25%).
7. Внимательно проверьте лист и посмотрите, какие ячейки остались невыделенными. Возможно, это формула, которая была перезаписана значением.

Если вы не делали никаких изменений, то можете нажать кнопку Отменить (или нажать Ctrl+Z) для отмены цветовой заливки, которую применили в шаге 6.

Использование условного форматирования

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

Чтобы настроить условное форматирование, выполните следующие действия.

1. Выберите Формулы ► Определенные имена ► Присвоить имя для открытия диалогового окна Создание имени.
2. В окне Создание имени введите следующую строку в поле Имя: CellHasFormula.
3. Введите такую формулу в поле Диапазон: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;ДВССЫЛ("rc";ЛОЖЬ)).
4. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Создание имени.
5. Выделите все ячейки, к которым хотите применить условное форматирование. Как правило, они составляют диапазон от А1 до правого нижнего угла используемой области листа.
6. Выберите Главная ► Стили ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
7. В верхней части окна выберите пункт Использовать формулу для определения форматируемых ячеек.
8. Введите следующую формулу в поле диалогового окна (рис. 196.1): =CellHasFormula.
9. Нажмите кнопку Формат для открытия диалогового окна Формат ячеек и выберите тип форматирования для ячеек, содержащих формулу.
10. Нажмите кнопку OК, чтобы закрыть окно Формат ячеек, и снова нажмите OК, чтобы закрыть окно Создание правила форматирования.

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

Формула, которую вы вводили в шаге 3, — макрос XLM. Следовательно, вам необходимо сохранить книгу с расширением с поддержкой макросов (используя расширение XLSM). Если вы сохраните книгу в виде XLSX-файла, Excel удалит имя CellHasFormula.