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

Question

Есть ли у кого-нибудь особый совет (настоятельно рекомендуется не VBA), чтобы систематически сообщать, имеет ли ячейка жестко закодированное значение, или это производная формула?

Я имею дело с некоторыми данными, в которых есть грязные промежуточные итоги, и пытаюсь понять, смогу ли я выделить необработанные строки.

Answer 1

Выберите нужные ячейки, нажмите Ctrl+G, а затем выберите Special чтобы открыть следующее диалоговое окно:

Затем укажите требуемый тип ячеек и нажмите « OK и останутся выбранными только те типы ячеек.

Answer 2

Начиная с Excel 2013, вы можете делать:

=ISFORMULA(reference)

Здесь полная документация

Answer 3

Используйте условное форматирование. Выберите опцию «Использовать формулу, чтобы определить, какие ячейки форматировать» и введите «= NOT(Cellhasformula)». Это отформатирует все, что жестко закодировано.

Answer 4

=iferror(Formulatext(cell),"Not Formula")

или показать только формулы:

=iferror(Formulatext(cell),"")

Answer 5

Два других метода

1. Используйте картографический инструмент, такой как мой Mappit!надстройка (которая может быть запущена как доверенная надстройка)
2. Вы можете использовать очень полезный обходной путь XLM / Range Names, который предлагает в реальном времени раскрашивать жестко запрограммированные ячейки, формулы, связанные ячейки и т.д. Для развертывания в отличие от VBA не требуются знания программирования.

Answer 6

Прошло далеко от первоначальной даты, но, если она кому-то пригодится, мне удалось обойти это, выделив диапазон ячеек, затем с помощью Replace (Ctrl+H), установив для него значение "Искать в" "Формулы" и заменить на равно = с апострофом-равно '=

Это вывело все формулы, но, очевидно, также преобразовало бы такие вещи, как если бы $ C $ 1 содержал =if($A1=$B1,"Match","Different") в '=if($A1'=$B1,"Match","Different")

Обратите внимание на A1'=B1 в середине формулы, которая может быть проблематичной, но все равно означает, что вы можете видеть формулы, хотя и непрагматично. Можно использовать функцию SUBSTITUTE, чтобы вернуть ее обратно, поэтому $ D $ 1 содержит =SUBSTITUTE(C1,"'=","=") , затем просто скопируйте ее в Блокнот и вставьте обратно в $ E $ 1.

Другим способом было бы «показывать формулы», но при этом показывались бы формулы для каждой ячейки, а не только для выбранного диапазона.

Answer 7

Function is_formula(c As Range) As Boolean
  is_formula = Left(c.Formula, 1) = "="
End Function

Answer 8

Я знаю, что вы сказали, что предпочитаете не VBA, но если у вас нет других решений, у объекта Range есть свойство HasFormula.

Возвращает: True, если все ячейки в диапазоне содержат формулы; False, если ни одна из ячеек в диапазоне не содержит формулы; в противном случае

Источник: http://msdn.microsoft.com/en-us/library/bb208626%28v=office.12%29.aspx

Answer 9

Если вы хотите сохранить файл Excel в формате книги без макросов, вам следует избегать как VBA, так и макросов (т. Е. Подходы XL4/XLM, предложенные в других ответах). В этом ответе я предположил, что файл Excel не содержит макросов.

Если вы используете MS-Excel 2013, вы можете использовать:

=ISFORMULA(reference)

Если вы используете более старые версии MS-Excel (например, 2010, 2007), нет истинной функции, чтобы определить, содержит ли ячейка формулу. Однако вы можете приблизить его, используя:

=IF(OR(ISBLANK(reference);CELL("prefix";reference)<>"");FALSE;IF(CELL("type";reference)="l";TRUE;"MAYBE"))

Функция выше возвращает:

• TRUE для ячеек, содержащих формулу, которая приводит к строковым данным.
• ЛОЖЬ для ячеек, содержащих строковый литерал или пустой.
• "МОЖЕТ БЫТЬ" для ячеек, содержащих логическое значение, число или дату, независимо от того, является ли это значение буквальным или результатом формулы.