Применим средства
EXCEL
для поиска и выделения ячеек, содержащих и НЕ содержащих формулы.
Представим, что в части ячеек листа имеются формулы, а в других – значения. Нужно определить, что в каких находится.
Выделить ячейки, которые содержат формулы можно воспользовавшись стандартным инструментом EXCEL
Выделение группы ячеек…
или через меню: на вкладке
Главная
в группе
Редактирование
щелкните стрелку рядом с командой
Найти и выделить
, а затем выберите в списке пункт
Формулы
.
Выделить ячейки, которые содержат НЕ формулы, т.е. содержат константы можно аналогичным образом, только вместо
Формулы
нужно выбрать
Константы
.
Если в ячейке введено
=11
, то это выражение считается формулой, хотя оно и не может быть изменено. Если у ячейки установлен текстовый формат, то введенная в нее формула будет интерпретирована как текст, т.е. константа.
Вышеуказанный подход требует вмешательства пользователя, т.е. необходимо вручную выбирать пункты меню. Можно настроить
Условное форматирование
так, чтобы после ввода формулы происходило автоматическое выделение, содержащей ее ячейки.
Допустим значения вводятся в диапазон
A1:A10
(см.
файл примера
)
.
Для настройки
Условного форматирования
для этого диапазона необходимо сначала создать
Именованную формулу
, для этого:
-
выделите ячейку
A
1
; -
вызовите окно
Создание имени
из меню
;
-
в поле
Имя
введите название формулы, например
Формула_в_ячейке
; -
в поле
Диапазон
введите
=ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1)
- нажмите ОК.
Теперь настроим правило
Условного форматирования
, для этого:
-
выделите диапазон
A
1:
A
10
; -
вызовите инструмент
Условное форматирование
();
-
выберите
Использовать формулу для определения форматируемых ячеек;
-
в поле «
Форматировать значения, для которых следующая формула является истинной
» введите
=Формула_в_ячейке
; - выберите требуемый формат, например, красный цвет фона;
- Нажмите ОК, затем еще раз ОК.
Теперь все ячейки из диапазона
A
1:
A
10
, содержащие формулы, выделены красным.
В этом примере мы использовали макрофункцию
ПОЛУЧИТЬ.ЯЧЕЙКУ()
. Это набор функций к EXCEL 4-й версии, которые нельзя напрямую использовать на листе EXCEL 2007, а можно использовать только в качестве
Именованной формулы
, что мы и сделали.
Чтобы, наоборот, выделить все непустые ячейки, содержащие константы (или НЕ содержащие формулы), нужно изменить формулу на
=И(НЕ(ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1));НЕ(ЕПУСТО(Лист1!A1)))
Совет
:
Чтобы показать все формулы, которые имеются на листе нужно на вкладке
Формулы
в группе
Зависимости формул
щелкните кнопку
Показать формулы
.
Чтобы выделить все ячейки, содержащие формулы, нужно на вкладке
Главная
, в группе
Редактирование
выбрать команду
Формулы
.
Чтобы найти все ячейки на листе, имеющие
Условное форматирование
необходимо:
-
на вкладке
Главная
в группе
Редактирование
щелкните стрелку рядом с командой
Найти и выделить
; -
выберите в списке пункт
Условное форматирование;
-
будут выделены все ячейки, которым применено
Условное форматирование
.
Отображением формул можно управлять следующим образом:
Щелкните Формулы и выберите показать формулы, чтобы переключаться между отображением формул и результатами.
Нажмите клавиши CTRL+` (апостроф).
Примечание: Эта процедура делает невозможным редактирование ячеек, содержащих формулы.
-
Выделите диапазон ячеек, в которых вы хотите скрыть формулы. Вы также можете выбрать несмежные диапазоны ячеек или весь лист.
-
На вкладке Главная нажмите кнопку Формат и выберите команду Формат ячеек.
-
На вкладке Защита снимите флажок Скрытый.
-
Нажмите кнопку ОК.
-
На вкладке Рецензирование нажмите кнопку Защитить лист.
-
Убедитесь в том, что флажок Защитить лист и содержимое защищаемых ячеек установлен, и нажмите кнопку ОК.
На вкладке Рецензирование нажмите кнопку Снять защиту листа. Если кнопка «Снять защиту листа» недоступна, сначала отключите функцию Общая книга.
Если вы больше не хотите скрывать формулы на защищенном листе, щелкните ячейки правой кнопкой мыши и выберите Формат ячеек. На вкладке Защита снимите флажок Скрытый.
Щелкните Формулы и выберите показать формулы, чтобы переключаться между отображением формул и результатами.
В Excel не предусмотрено встроенной функции для поиска формул. Когда формула введена в ячейку, узнать, является ячейка постоянным значением или значением, полученным из формулы, можно, только щелкнув ячейку и взглянув на строку формул или же нажав Ctrl+~ (тильда; чтобы отменить режим показа формул, нажмите Ctrl+~ еще раз). Предлагаемый трюк позволит вам выделить ячейки с формулами при помощи трех строчек кода VBA и условного форматирования. [1]
Если вы никогда не создавали пользовательской фунции при помощи VBA, рекомендую начать с заметки Сумма по цвету ячеек в Excel, в которой поясняются первые шаги.
Если вы представляете, о чем речь, пройдите по меню Разработчик –> Visual Basic и в открывшемся окне Microsoft Visual Basic for Applications пройдите по меню Insert –> Module (рис. 1). В окне нового модуля наберите следующий код:
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function
Рис. 1. Код пользовательской функции IsFormula в окне Microsoft Visual Basic for Applications
Скачать заметку в формате Word или pdf, скачать пример в формате Excel (с встроенным кодом VBA)
Закройте окно Visual Basic, нажав Alt+Q или пройдя по меню File –> Close and Return to Microsoft Excel. Вы создали функцию IsFormula. Если вы создали функцию в модуле, относящемся к этой рабочей книге (как на рис. 1), а не в модуле Личной книги макросов – Personal.xlsb, то функция будет доступна только в этой конкретной книге Excel. Чтобы ваш труд не пропал даром, сохраните Excel-файл командой Сохранить как, и выбрав тип файла Книга Excel с поддержкой макросов (*.xlsm). Ваша пользовательская функция, как и любая встроенная функция, доступна в окне Вставка функции в категории Определенные пользователем (рис. 2).
Рис. 2. Пользовательская функция доступна в окне Вставка функции
Также вы можете вызвать функцию, просто, начав набирать в ячейке =i (рис. 3).
Рис. 3. Функцию можно выбрать через подсказку, начав набирать в ячейке =i
Функция возвращает два значения: ИСТИНА, если в соответствующей ячейке содержится формула, и ЛОЖЬ, если это не так. Этот булевский результат можно использовать совместно с условным форматированием, чтобы автоматически выделить все формулы с применением нужного форматирования.
Одно из основных преимуществ этого метода заключается в том, что возможности идентификации формул на листе динамические. Это означает, что, если вы добавите или удалите формулу, то форматирование изменится соответствующим образом. Вот как это сделать.
Выделите диапазон ячеек, заполненный данными, плюс сделайте запас на случай, если данные будут добавляться. Не выделяйте весь лист, так как это может увеличить размер файла (иногда катастрофически). В нашем примере (рис. 4) я выделил область А1:К28. Причем ячейка А1 должна быть активной.
В качестве примера в диапазон А1:F20 я ввел формулу =СЛЧИС(). Затем выделил весь диапазон, скопировал его в буфер, и вставил как значение. После этого в нескольких ячейках снова вставил формулу =СЛЧИС().
Рис. 4. Создание правила форматирования для выделеной области
Пройдите по меню Главная –> Условное форматирование –> Создать правило (я работаю в Excel2013). В открывшемся окне Создание правила форматирования выберите опцию Использовать формулу для определения форматируемых ячеек. И в поле Форматировать значения, для которых следующая формула является истинной введите =IsFormula(A1). Щелкните кнопку Формат рядом с полем Образец и выберите желтую заливку для идентификации ячеек с формулами. Щелкните ОК (рис. 5).
Рис. 5. С помощью кода VBA и условного форматирования удалось выделить ячейки, содержащие формулы
Получился ножиданный эффект, который можно наблюдать в прикрепленном Excel-файле. Я считал, что функция =СЛЧИС() пересчитывается только при изменении хотя бы одного значения на листе. Так вот, на листе формально не происходит изменения значений. Но, видимо, функция IsFormula, участвующая в условном форматировании, как-то влияет на этот процесс, так что экран оживает, и значения постоянно изменяются. 🙂
Иногда при вводе формул в окне условного форматирования Excel пытается добавить кавычки вокруг формул после того, как вы щелкаете на кнопке ОК. Это означает, что Excel распознал то, что вы ввели, как текст, а не как формулу. Если это произошло, вернитесь в окно Условное форматирование, удалите кавычки и щелкните ОК.
Теперь указанная формула применяется ко всем ячейкам выделенного диапазона, содержащим формулы. Если вы удалите или исправите содержимое ячейки с формулой, условное форматирование исчезнет. Схожим образом, если вы введете новую формулу в любую ячейку диапазона, она также будет выделена форматированием.
[1] По материалам книги Р.Холи, Д.Холи. Excel 2007. Трюки, стр. 70–72
Достаточно часто в таблицах бывает такая ошибка, когда формула случайно заменяется значением. Этот тип ошибок обычно трудно определяется, особенно если лист содержит большое количество формул. В этом приеме описывается два способа быстро идентифицировать формулы в листе, выделяя их. Затем при уменьшении масштаба вы могли бы определить пробелы в группах формул.
Использование окна Выделение группы ячеек
Этот метод выявления ячеек с формулами легкий, но не динамический. Другими словами, он хорош для единичной проверки.
- Выберите одну ячейку в листе.
- Выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек для открытия диалогового окна Выделение группы ячеек.
- В окне Выделение группы ячеек установите переключатель в положение формулы и убедитесь, что все флажки ниже установлены.
- Нажмите кнопку ОК. Excel выберет все ячейки с формулами.
- Нажмите кнопку Цвет заливки в группе Шрифт вкладки Главная. Выберите любой цвет, который еще не используется.
- Используйте элемент управления Масштаб и задайте для своего листа небольшой масштаб (например, 25%).
- Внимательно проверьте лист и посмотрите, какие ячейки остались невыделенными. Возможно, это формула, которая была перезаписана значением.
Если вы не делали никаких изменений, то можете нажать кнопку Отменить (или нажать Ctrl+Z) для отмены цветовой заливки, которую применили в шаге 6.
Использование условного форматирования
Этот метод определения ячеек с формулами потребует небольшой настройки, но он имеет явное преимущество по сравнению с предыдущим, так как является динамическим. Ячейки с формулами определяются сразу, как только заполняются.
Чтобы настроить условное форматирование, выполните следующие действия.
- Выберите Формулы ► Определенные имена ► Присвоить имя для открытия диалогового окна Создание имени.
- В окне Создание имени введите следующую строку в поле Имя:
CellHasFormula. - Введите такую формулу в поле Диапазон:
=ПОЛУЧИТЬ.ЯЧЕЙКУ(48;ДВССЫЛ("rc";ЛОЖЬ)). - Нажмите кнопку ОК, чтобы закрыть диалоговое окно Создание имени.
- Выделите все ячейки, к которым хотите применить условное форматирование. Как правило, они составляют диапазон от А1 до правого нижнего угла используемой области листа.
- Выберите Главная ► Стили ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
- В верхней части окна выберите пункт Использовать формулу для определения форматируемых ячеек.
- Введите следующую формулу в поле диалогового окна (рис. 196.1):
=CellHasFormula. - Нажмите кнопку Формат для открытия диалогового окна Формат ячеек и выберите тип форматирования для ячеек, содержащих формулу.
- Нажмите кнопку OК, чтобы закрыть окно Формат ячеек, и снова нажмите OК, чтобы закрыть окно Создание правила форматирования.
Рис. 196.1. Окно для установки условного форматирования для выделения ячеек с формулами
После выполнения этих шагов каждая ячейка, которая содержит формулу и находится в пределах выбранного в шаге 5 диапазона, будет отформатирована по вашему выбору. Кроме того, при вводе формулы в диапазон, для которого задано условное форматирование, ячейка немедленно поменяет вид. Это форматирование позволяет вам легко определить ячейку, которая должна содержать формулу, но не содержит.
Формула, которую вы вводили в шаге 3, — макрос XLM. Следовательно, вам необходимо сохранить книгу с расширением с поддержкой макросов (используя расширение XLSM). Если вы сохраните книгу в виде XLSX-файла, Excel удалит имя CellHasFormula.
Содержание
- Способы спрятать формулу
- Способ 1: скрытие содержимого
- Способ 2: запрет выделения ячеек
- Вопросы и ответы
Иногда при создании документа с расчетами пользователю нужно спрятать формулы от чужих глаз. Прежде всего, такая необходимость вызвана нежеланием юзера, чтобы посторонний человек понял структуру документа. В программе Эксель имеется возможность скрыть формулы. Разберемся, как это можно сделать различными способами.
Способы спрятать формулу
Ни для кого не секрет, что если в ячейке таблицы Excel имеется формула, то её можно увидеть в строке формул просто выделив эту ячейку. В определенных случаях это является нежелательным. Например, если пользователь хочет скрыть информацию о структуре вычислений или просто не желает, чтобы эти расчеты изменяли. В этом случае, логичным действием будет скрыть функцию.
Существует два основных способа сделать это. Первый из них представляет собой скрытие содержимого ячейки, второй способ более радикальный. При его использовании налагается запрет на выделение ячеек.
Способ 1: скрытие содержимого
Данный способ наиболее точно соответствует задачам, которые поставлены в этой теме. При его использовании только скрывается содержимое ячеек, но не налагаются дополнительные ограничения.
- Выделяем диапазон, содержимое которого нужно скрыть. Кликаем правой кнопкой мыши по выделенной области. Открывается контекстное меню. Выбираем пункт «Формат ячеек». Можно поступить несколько по-другому. После выделения диапазона просто набрать на клавиатуре сочетание клавиш Ctrl+1. Результат будет тот же.
- Открывается окно «Формат ячеек». Переходим во вкладку «Защита». Устанавливаем галочку около пункта «Скрыть формулы». Галочку с параметра «Защищаемая ячейка» можно снять, если вы не планируете блокировать диапазон от изменений. Но, чаще всего, защита от изменений является как раз основной задачей, а скрытие формул – дополнительной. Поэтому в большинстве случаев обе галочки оставляют активными. Жмем на кнопку «OK».
- После того, как окно закрыто, переходим во вкладку «Рецензирование». Жмем на кнопку «Защитить лист», расположенную в блоке инструментов «Изменения» на ленте.
- Открывается окно, в поле которого нужно ввести произвольный пароль. Он потребуется, если вы захотите в будущем снять защиту. Все остальные настройки рекомендуется оставить по умолчанию. Затем следует нажать на кнопку «OK».
- Открывается ещё одно окно, в котором следует повторно набрать ранее введенный пароль. Это сделано для того, чтобы пользователь, вследствии введения неверного пароля (например, в измененной раскладке), не утратил доступ к изменению листа. Тут также после введения ключевого выражения следует нажать на кнопку «OK».
После этих действий формулы будут скрыты. В строке формул защищенного диапазона при их выделении ничего отображаться не будет.
Способ 2: запрет выделения ячеек
Это более радикальный способ. Его применение налагает запрет не только на просмотр формул или редактирование ячеек, но даже на их выделение.
- Прежде всего, нужно проверить установлена ли галочка около параметра «Защищаемая ячейка» во вкладке «Защита» уже знакомого по предыдущему способу нам окна форматирования выделенного диапазона. По умолчанию этот компонент должен был включен, но проверить его состояние не помешает. Если все-таки в данном пункте галочки нет, то её следует поставить. Если же все нормально, и она установлена, тогда просто жмем на кнопку «OK», расположенную в нижней части окна.
- Далее, как и в предыдущем случае, жмем на кнопку «Защитить лист», расположенную на вкладке «Рецензирование».
- Аналогично с предыдущим способом открывается окно введения пароля. Но на этот раз нам нужно снять галочку с параметра «Выделение заблокированных ячеек». Тем самым мы запретим выполнение данной процедуры на выделенном диапазоне. После этого вводим пароль и жмем на кнопку «OK».
- В следующем окошке, как и в прошлый раз, повторяем пароль и кликаем по кнопке «OK».
Теперь на выделенном ранее участке листа мы не просто не сможем просмотреть содержимое функций, находящихся в ячейках, но даже просто выделить их. При попытке произвести выделение будет появляться сообщение о том, что диапазон защищен от изменений.
Итак, мы выяснили, что отключить показ функций в строке формул и непосредственно в ячейке можно двумя способами. При обычном скрытии содержимого скрываются только формулы, как дополнительную возможность можно задать запрет их редактирования. Второй способ подразумевает наличие более жестких запретов. При его использовании блокируется не только возможность просмотра содержимого или его редактирования, но даже выделения ячейки. Какой из этих двух вариантов выбрать зависит, прежде всего, от поставленных задач. Впрочем, в большинстве случаев, первый вариант гарантирует достаточно надежную степень защиты, а блокировка выделения зачастую бывает излишней мерой предосторожности.
Еще статьи по данной теме: