Начиная с версий 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)
Теперь, записав в ячейку
Так же при помощи функций листа макросов можно получить и сам текст формулы(если в ячейке записана формула
Недостатки данного метода - Вы привязаны к "левостороннему" определению формулы. Т.е. таким методом Вы можете узнать есть ли формула(или какая формула) в ячейке, расположенной слева от ячейки с именованной формулой. Для того, чтобы "узнать формулу справа" именованная формула должна выглядеть так:
=ПОЛУЧИТЬ.ЯЧЕЙКУ(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(
Теперь в ячейку записываем созданную формулу
Данная функция имеет два аргумента:
Cell - ссылка на проверяемую ячейкуShowFormula - необязательный аргумент. Если присвоено значение ЛОЖЬ или 0 или опущен(не указан) - то функция вернет значение ИСТИНА или ЛОЖЬ(в зависимости от наличия или отсутствия формулы в ячейке). Если присвоено значение ИСТИНА или 1, то в функция вернет не просто ИСТИНА или ЛОЖЬ, а еще и текст самой формулы.
Формулу можно так же найти в диспетчере функций в категории Определенные пользователем - IsFormula и записывать её в ячейку через мастер функций.
Чтобы при изменении формулы в целевой ячейке сразу же изменялось её отображение в ячейке с данной функцией необходимо убрать апостроф(') перед
Недостатки данного метода - для работы функции обязательно должны быть разрешены макросы
Созданные функции можно так же использовать в условном форматировании для подсветки ячеек, содержащих формулы.
Tips_All_HasFormula.xls (31,0 КиБ, 4 509 скачиваний)
однозначно, Эксель - это не для меня, но всё равно спасибо за полезные советы!
Дмитрий,объясните пож-ста,может я чего-то не понимаю,но разве формулы и функции выделенной ячейки не отображаются в поле функции под панелью инструментов?
Лена, конечно, отображается! Но статья писалась для случаев, когда необходимо быстро узнать в массиве ячеек наличие формулы.
Представьте столбец из 12000 ячеек. Формулы в 3-5 ячейках из всех. В каких - Вы не знаете. Что будет проще: воспользоваться моим спобосом или проглядеть глазками каждую ячейку?
Дмитрий! Спасибо Вам огромное за публикацию этих методов!
Я с ног сбилась в поисках. Куча стандартных функций в Экселе, чтобы узнать, является ли значение в ячейке ошибкой, числом или текстом, а для определения, если ли в ней формула нет...
Спасибо! Вы очень помогли!
Спасибо за метод.
Если
"необходимо быстро узнать в массиве ячеек наличие формулы"
рекомендую нажать Ctrl~
будут отображаться формулы, а не вычисленные значения.
Excel 2013
1. В Параметрах ставим стиль ссылок R1C1
2. Выделяем все ячейки
3. Условное форматирование / Создать правило
4. В поле "Тип правила" выбираем "Использовать формулу для определения форматируемых ячеек"
5. В формуле пишем =ЕФОРМУЛА(RC)
6. Задаем формат, например цвет фона
PROFIT!
Спасибо большое, вы гений, в режиме ссылок R1C1 создаем правило, оно работает, затем меняем стиль на обычный, (кому как удобно) и "вуа-ля" правило становится универсальным.
! вот что значит нестрандартный подход