Как посмотреть в excel есть формула или нет - Word и Excel

Хитрости »

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

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

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

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

Видеоуроки

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

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

Источник

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше

Предположим, вам нужно быстро изменить некоторые формулы, но вы еще не работали с книгой и не знаете, где находятся формулы. Вот как их найти.

Вы можете выбрать ячейку или диапазон ячеек.

Если выбрать одну ячейку, поиск будет происходить на всем. При выборе диапазона поиск будет только в этом диапазоне.
Щелкните Главная > найти & Выберите >Перейти к специальным.
Щелкните Формулыи при необходимости счистите любой из флажков под кнопкой Формулы.

4. Нажмите кнопку ОК.

Подробнее о поиске в Excel

Поиск или замена текста и чисел на листе
Поиск объединенных ячеек
Поиск диапазонов с использованием определенных имен
Удаление или разрешение циклической ссылки
Поиск скрытых ячеек на листе

Нужна дополнительная помощь?

Источник

mazayZR Пользователь Сообщений: 950 Регистрация: 01.01.1970	тот ли это слэн? или поддельный…

	формулой или макросом есс-но, не взглядом

	If Left(ActiveCell.Formula, 1) = «=» Then MsgBox 1

	я почему-то подумал: if .formula<>.text then понятно, что и то и то медленно, а что лучше? а как скомпилировать проект?

{quote}{login=слэн}{date=16.04.2008 02:40}{thema=}{post}я почему-то подумал:

if .formula<>.text then

понятно, что и то и то медленно, а что лучше?

а как скомпилировать проект?{/post}{/quote}

мне тоже всегда было интересно можно ли формулой, сам не знаю…
что быстрее, только опытным путем, заполнить колонку и померить через таймер
последний вопрос не понял)))

	Application.VBE.VBProjects(1).MakeCompiledFile как использовать

	А правка — перейти — выделить — формулы? Или то же на ВБА Selection.SpecialCells(xlCellTypeFormulas, 23).Select все ячейки с формулами выделены, дальше что хотим, то и делаем с ними

	вообще хотелось формулу.. сделал пользовательскую функцию, но замедляет — страшно ну да, тоже один из способов, если использовать intersect(range1,specialceels(xlCellTypeFormulas, 23)), но подозреваю, что не лучший..

mazayZR Пользователь Сообщений: 950 Регистрация: 01.01.1970	гугл вот что показывает http://msoffice.nm.ru/faq/macros/formulas.htm#faq140 If Range(«A1»).HasFormula = True Then MsgBox «Ячейка содержит формулу» Else MsgBox «Ячейка не содержит формулы» End If

{quote}{login=слэн}{date=16.04.2008 02:48}{thema=}{post}Application.VBE.VBProjects(1).MakeCompiledFile

как использовать{/post}{/quote}

Похоже это метод VB и в VBA он не работает, примеров не нашел , кроме вот этой ссылки, но там по англицки

http://www.tutorials-win.com/WordVBA/Error-trying-70803/

попроьывал приведенный код, VBA ругается

ZVI

Пользователь

Сообщений: 4328
Регистрация: 23.12.2012

{quote}{login=слэн}{date=16.04.2008 02:56}{thema=}{post}вообще хотелось формулу..
сделал пользовательскую функцию, но замедляет — страшно

ну да, тоже один из способов, если использовать
intersect(range1,specialceels(xlCellTypeFormulas, 23)),
но подозреваю, что не лучший..{/post}{/quote}
—
Слэн, можно и формулой, но не универсально.
Зависит от применения, которое хотелось бы уточнить.
Например, в формуле [A2] нужно проверить, есть ли формула в [B2], и так до [A100]- это один случай, не универсальный с т.з. фиксации ссылки: строка — та же, 1 колонка справа. Другой случай (универсальный), когда в формуле условного форматирования нужно проверить, есть ли формула в ячейке, которая условно форматируется.
—
ZVI

ZVI Пользователь Сообщений: 4328 Регистрация: 23.12.2012	{quote}{login=слэн}{date=16.04.2008 08:57}{thema=}{post}первый случай..{/post}{/quote} — Прилагается вполне универсальный вариант функции HasFormula(Refernce). — ZVI

	CTRL+’ и все формулы налицо

	{quote}{login=gremlin}{date=17.04.2008 12:00}{thema=}{post}CTRL+’ и все формулы налицо{/post}{/quote}1. Внимательно перечитываем треий пост этой темы: формулой или макросом есс-но, не взглядом 2. При необходимости п. 1 повторить

{quote}{login=ZVI}{date=16.04.2008 10:20}{thema=Re: как определить есть ли в ячейке формула или нет}{post}{quote}{login=слэн}{date=16.04.2008 08:57}{thema=}{post}первый случай..{/post}{/quote}
—
Прилагается вполне универсальный вариант функции HasFormula(Refernce).
—
ZVI{/post}{/quote}

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

	юзаем гуглю и мелкософт.ком и находим справку по этим функциям

ZVI

Пользователь

Сообщений: 4328
Регистрация: 23.12.2012

{quote}{login=Артем}{date=17.04.2008 02:06}{thema=Re: Re: как определить есть ли в ячейке формула или нет}{post}{quote}{login=ZVI}{date=16.04.2008 10:20}{thema=Re: как определить есть ли в ячейке формула или нет}{post}{quote}{login=слэн}{date=16.04.2008 08:57}{thema=}{post}первый случай..{/post}{/quote}
—
Прилагается вполне универсальный вариант функции HasFormula(Refernce).
—
ZVI{/post}{/quote}

а можно поподробнее описать процесс, как это получилось,а то ни в справке, ни в гугле подробного описания использованных функций не нашел{/post}{/quote}
—
Использован Excel-макро-язык XLM, на котором программировали еше наши бабушки до того, как появился VBA.
Функции, написанные таким образом, в рабочем пространстве книг и листов работают на порядок быстрее VBA-ных аналогов, так как не используется промежуточный и достаточно медленый OLE-транслятор между рабочим простанством таблиц и рабочим пространством VBA.
Справку по функциям этого языка можно скачать отсюда:

http://download.microsoft.com/download/excel2000/xlmacro/2000/WIN98/EN-US/xlmacro.exe

Но не без заморочек: нужно переименовать EXE в ZIP и архиватором извлечь CHM, т.к. самораспаковывается только, если установлен Excel 2000.
Описаний правил програмирования на Excel-XLM я не видел, но о них несложно догадаться, если взглянуть на какие-нибудь примеры.

Вопрос Слэна, на самом деле, очень интересный и тонкий, несмотря на напрашивающееся «тыкнуть и позырить»

—
ZVI

я тут поэксперементировал и..

c exel4 макросами — 8сек
left(.formula)=»=» — 17сек
.formula<>.text — 27сек

такой большой разницы между вторым и третьим не ожидал

спасибо zvi обращу внимание на листы макросов, особенно мне понравилось, что его невозможно отключить.. ?

ZVI

Пользователь

Сообщений: 4328
Регистрация: 23.12.2012

—
Просто для истории:
Оказалось, что Exel4-макро-формула, записанная в имени книги, работает примерно в 20 раз быстрее аналогичной фомулы листа макросов.
В приложении приведен вариант проверки формулы в колонке справа. Использована формула в имени [ФормулаСправа]
—
ZVI

{quote}{login=ZVI}{date=30.04.2008 09:48}{thema=Re: как определить есть ли в ячейке формула или нет}{post}—
Просто для истории:
Оказалось, что Exel4-макро-формула, записанная в имени книги, работает примерно в 20 раз быстрее аналогичной фомулы листа макросов.
В приложении приведен вариант проверки формулы в колонке справа. Использована формула в имени [ФормулаСправа]
—
ZVI{/post}{/quote}

ZVI вы ипользовали англоязычный эксель?
А то у меня local rus ругается….

И как получилось задать ссылку на ячейку справа?
Неужто напрямую R[0]C[1]?

	зачем RэCэ.. просто мышкой тыкнуть, но деньги убрать.. интересно! спасибо, ZVI!

ZVI

Пользователь

Сообщений: 4328
Регистрация: 23.12.2012

—
Ответ для DL.

ФормулаСправа из post_16496.rar работает в русскоязычной версии, проверял в Excel 2002, 2003, 2007.
Русскоязычная формула: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!B2)
Англоязычная формула: =GET.CELL(48,Лист1!B2)

Используется функция, определенная именем: Named Defined Function (NDF) – придумалось такое вот название, по аналогии с VBA-ной User Defined Function (UDF).

Принцип простой, его удобнее объяснить на простейшем примере:
Допустим, для каждой ячейки диапазона C1:C10 нужна NDF, которая бы вычисляла сумму двух ячеек справа. Если пользоваться Excele-ем, то нужно было бы просто в C1 написать формулу: =D1+E1 и протянуть формулу до C10.

Чтобы сделать то же самое с помощью NDF, нужно:
1. Активизировать C1
2. Через меню Вставить — Имя — Присвоить ввести имя NDF, например: CуммаСправа
3. В поле формула, ввести формулу, как рекомендовал Слэн: ткнуть мышкой в D1, добавить плюс и ткнуть в E1, затем аккуратно поудалять знак абсолютной ссылки $. Формула станет относительной и будет выглядеть так: =Лист1!D1+Лист1!E1
4. Теперь если где-либо на листе1 ввести формулу (удобнее через F3) =СуммаСправа
то в этой ячейке появится сумма двух ячеек справа.

Когда нужна NDF:
1. NDF позволяют использовать формулы макроязыка Excel 4.0 XML, которые не работают в обычных формулах ячеек. Примером может служить ФормулаСправа
2. NDF экономит память и улучшает читаемость формул.
Пример здесь:

http://www.planetaexcel.ru/forum.php?thread_id=3377

post_14945.rar

—
ZVI

ZVI Пользователь Сообщений: 4328 Регистрация: 23.12.2012	— Добавлю, что формула NDF привязана к конкретному листу, для другого листа можно создать другое имя с аналогичной формулой. Возможно, что проблема DL именно в этом. — ZVI

DL Гость	#28 06.05.2008 07:02:48 ZVI ОГРОМНОЕ ОЧЕРЕДНОЕ СПАСИБО! Просто узнавая такие вещи руки чешутся на эксперементирование и философствованье. Одна беда что что наэксперементировано и нафилосовтвовано и до нас не мало….

Источник

Если у вас есть большой рабочий лист, содержащий несколько формул, и теперь вы хотите найти или проверить, содержат ли формулы формулы. Есть ли у вас какие-нибудь хорошие способы сразу найти или проверить ячейки с формулами в Excel?

Проверьте, содержат ли ячейки формулы с функцией

Найдите и выделите все ячейки с формулами с помощью команды Перейти к специальной

Выделите все ячейки с формулами с кодом VBA

Автоматически выделять все формулы во всей книге с помощью Kutools for Excel

Проверьте, содержат ли ячейки формулы с функцией

По умолчанию есть простая функция, позволяющая проверить, содержит ли ячейка формулу в Excel, сделайте следующее:

1. Введите эту формулу: = ISFORMULA (A1) (A1 это ячейка, которую вы хотите проверить, является ли это формулой) в пустую ячейку, чтобы проверить, содержит ли конкретная ячейка формулу или нет, затем нажмите Enter ключ к результату, НЕПРАВДА указывает, что это не формула, ИСТИНА обозначает формулу. Смотрите скриншот:

2. Затем вы можете перетащить дескриптор заполнения в диапазон ячеек, в которых вы хотите проверить соответствующие ячейки, если это формулы, см. Снимок экрана:

Найдите и выделите все ячейки с формулами с помощью команды Перейти к специальной

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

1. Выберите диапазон, в котором вы хотите выделить ячейки формулы.

2. Нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:

3. В Перейти к специальному диалоговое окно, выберите Формулы и проверьте все параметры под Формулы, см. снимок экрана:

4. Затем нажмите OK, выделены сразу все ячейки с формулами. Смотрите скриншот:

Выделите все ячейки с формулами с кодом VBA

Следующий код VBA может помочь вам выделить все ячейки формулы, чтобы выделить их.

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

Код VBA: выделить все ячейки формулами

Sub FindFormulaCells()
'Updateby Extendoffice
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox("please select a range:", "Kutools for Excel", Application.ActiveSheet.UsedRange.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xRg = xRg.SpecialCells(xlCellTypeFormulas, 23)
    If xRg Is Nothing Then Exit Sub
    xRg.Interior.Color = vbYellow
End Sub

3, Затем нажмите F5 ключ для запуска этого кода, а затем выберите диапазон данных, в котором вы хотите найти формулы из окна подсказки, см. снимок экрана:

4. Нажмите OK кнопку, и все ячейки формулы были выделены желтым цветом, как показано на следующем снимке экрана:

Внимание: В приведенном выше коде xRg.Interior.Color = vbYellow, вы можете изменить цвет выделения — желтый на свой вкус, например красный, зеленый, синий и т. д.

Автоматически выделять все формулы во всей книге с помощью Kutools for Excel

Kutools for ExcelАвтора Выделить формулы Эта функция может помочь вам автоматически выделить формулы во всей книге, а выделенные ячейки будут изменяться по мере динамического изменения ячейки.

После установки Kutools for Excel, пожалуйста, сделайте следующее Бесплатная загрузка Kutools for Excel Теперь )

1. Нажмите Кутулс Плюс > Дизайн рабочего листа, И новый Дизайн вкладка будет отображаться на ленте, щелкните Выделить формулы под Дизайн вкладку см. скриншоты:

2. И тогда все ячейки формулы на всех листах были выделены сразу.

Как выделить / условное форматирование ячеек с формулами в Excel?

Бесплатная загрузка Kutools for Excel Теперь

Статьи по теме:

Как выделить / условное форматирование ячеек с формулами в Excel?

Как выделить всю / целую строку при прокрутке на листе Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Если вы часто работаете с Excel, то кропотливость работы с разбором не работающих формул вам точно знакома. Теперь работать с ними будет проще…

Вместо того, чтобы смотреть в строку формул при наведении на ячейку или делать дабл-клик на ячейке, для просмотра формулы, просто нажмите сочетание Ctrl + ‘. Вам тут же будет показана формула. Для живущих в Excel людей — это крайне нужное сочетание!

Источник