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

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

Здесь я представляю вам лучшее решение — Условное форматирование. С помощью Условного форматирования вы можете выделить все ячейки с формулами, и, что еще лучше, ваше выделенное форматирование будет корректироваться вместе с изменением ваших данных.

Выделение ячеек с формулами с помощью определенного имени формулы и Условного форматирования

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

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

Выделение ячеек с формулами с помощью определенного имени формулы и Условного форматирования

Прежде чем использовать Условное форматирование, вы должны создать Определенное Имя, пожалуйста, следуйте этим шагам:

1. Откройте вашу таблицу и нажмите Формулы > Диспетчер имен, см. скриншот:

2. В появившемся диалоговом окне Диспетчер имен нажмите кнопку Создать.

3. В диалоговом окне Новое имя выполните следующие действия:

• (1.) Введите имя для вашей определенной формулы имени, в этом примере я введу Formatformulas;
• (2.) Выберите Книга из выпадающего списка Область;
• (3.) Введите эту формулу =GET.CELL(48,INDIRECT("rc",FALSE)) в поле Ссылается на.

4. Затем нажмите OK, чтобы вернуться к предыдущему диалогу и закрыть его.

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

6. На вкладке Главная нажмите Условное форматирование > Новое правило, см. скриншот:

7. В диалоговом окне Новое правило форматирования нажмите Использовать формулу для определения ячеек для форматирования и введите эту формулу =Formatformulas (Formatformulas — это ваше определенное имя на шаге 3), вы можете изменить его на свое собственное определенное имя. См. скриншот:

8. Затем нажмите кнопку Формат, установите ваше форматирование на вкладке Заливка, см. скриншот:

9. Затем нажмите OK > OK, чтобы завершить операции, и все ячейки с формулами будут выделены сразу, как показано ниже:

Примечания:

1. Когда вы вводите формулы в любую из ячеек, охваченных Условным форматированием, они будут автоматически выделены.

2. После завершения операций вам нужно сохранить вашу книгу как Книгу Excel с поддержкой макросов, чтобы сохранить ячейки с условным форматированием. А когда вы в следующий раз откроете вашу книгу, нажмите Включить содержимое.

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

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

1. Сначала удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

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

Function HasFormula(Rng As Range) As Boolean
'Updateby Extendoffice
Application.Volatile
HasFormula = Rng.HasFormula
End Function

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

4. Перейдите на вкладку Главная и нажмите Условное форматирование > Новое правило.

5. В диалоговом окне Новое правило форматирования выберите Использовать формулу для определения ячеек для форматирования и введите эту формулу =Hasformula(A1), см. скриншот:

6. Затем нажмите кнопку Формат и выберите понравившийся цвет на вкладке Заливка в диалоговом окне Формат ячеек.

7. Затем нажмите OK > OK, чтобы закрыть диалоговые окна. И все ячейки, содержащие формулы, будут немедленно выделены.

Примечания:

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

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

3. После завершения операций вам нужно сохранить вашу книгу как Книгу Excel с поддержкой макросов, чтобы сохранить ячейки с условным форматированием. А когда вы в следующий раз откроете вашу книгу, нажмите Включить содержимое.

Выделение ячеек с формулами с помощью Kutools для Excel

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

После установки Kutools для Excel, пожалуйста, сделайте следующее:

1. Откройте книгу, в которой вы хотите выделить все формулы.

2. Нажмите Kutools Plus > Проектирование рабочих листов, и новая вкладка Проектирование появится на ленте, затем нажмите Выделить формулы в группе Вид, см. скриншоты:

3. И все формулы во всей книге будут немедленно выделены. См. скриншот:

Примечания:

1. Нажмите Выделить формулы снова, и выделение будет отключено.

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

3. Когда вы вводите новые формулы в любые ячейки текущей книги, они будут автоматически выделены.

Нажмите, чтобы узнать больше об этой функции Выделить формулы.

Скачайте и попробуйте Kutools для Excel прямо сейчас!

Демонстрация: Выделение ячеек с формулами с помощью Kutools для Excel

Связанные статьи:

Как заблокировать и защитить формулы в Excel?

Как скрыть формулы, которые не отображаются в строке формул в Excel?