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

Обновлено: 21.11.2024

Вероятно, вы уже знаете, что можно выделить все ячейки, содержащие формулы на листе, нажав F5 и выбрав Special | Формулы. Если вам нужно постоянно следить за тем, где находятся формулы, повторный выбор может стать утомительным. Лучшим решением является использование возможностей условного форматирования Excel для выделения ячеек с формулами.

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

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

  1. Открыв вкладку «Главная» на ленте, выберите параметр «Условное форматирование» в группе «Стили». Excel отображает палитру параметров, связанных с условным форматированием.
  2. Выберите «Новое правило». Excel отобразит диалоговое окно «Новое правило форматирования».
  3. В области "Выберите тип правила" в верхней части диалогового окна выберите "Использовать формулу для определения форматируемых ячеек". (См. рис. 1.)

Рисунок 1. Диалоговое окно «Новое правило форматирования».

Microsoft представила функцию ISFORMULA в Excel 2013. Функция ISFORMULA позволяет выделять ячейки, содержащие формулы, без использования макроса. Чтобы использовать эту функцию с условным форматированием, выберите ячейки, которые нужно проверить, и выполните следующие действия:

  1. Открыв вкладку «Главная» на ленте, выберите параметр «Условное форматирование» в группе «Стили». Excel отображает палитру параметров, связанных с условным форматированием.
  2. Выберите «Новое правило». Excel отобразит диалоговое окно «Новое правило форматирования».
  3. В области "Выберите тип правила" в верхней части диалогового окна выберите "Использовать формулу для определения форматируемых ячеек".
  4. В поле «Формат значений, где эта формула верна» введите «=ISFORMULA(A1)» (без кавычек). Если активная ячейка в выбранном вами диапазоне не является A1, вам потребуется немного изменить формулу, чтобы отразить любую активную ячейку.
  5. Нажмите «Формат», чтобы открыть диалоговое окно «Формат ячеек».
  6. Используйте элементы управления в диалоговом окне «Формат ячеек», чтобы указать, как вы хотите отформатировать ячейки. Например, вы можете выбрать цвет заливки для ячеек с формулами.
  7. Нажмите "ОК", чтобы закрыть диалоговое окно "Формат ячеек".
  8. Нажмите "ОК".

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию. Нажмите здесь, чтобы открыть эту специальную страницу в новой вкладке браузера.

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

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

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

Во-первых, вы можете включить или выключить видимость формул с помощью сочетания клавиш Control + Grave Accent. Эта клавиша находится чуть ниже клавиши Escape на клавиатурах для США. Этот ярлык заставит Excel отображать сами формулы вместо их результатов. Используя этот ярлык, вы можете быстро и легко переключаться между собой.

Следующий способ найти все формулы – воспользоваться функцией "Перейти к специальному". Go To Special основан на диалоговом окне Go To. Самый быстрый способ открыть это диалоговое окно — использовать сочетание клавиш Control-G. Это работает как на платформах Windows, так и на Mac.

В диалоговом окне «Переход» нажмите «Специальные», выберите «Формулы» и нажмите «ОК». Excel выберет все ячейки, содержащие формулы.

В строке состояния мы можем видеть количество выбранных ячеек.

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

Чтобы очистить это форматирование. Мы можем просто обратить процесс вспять.

[перейти к специальному, чистому форматированию]

Третий способ визуально выделить формулы — использовать условное форматирование. Excel 2013 включает новую формулу под названием ISFORMULA(), которая упрощает правило условного форматирования, но не работает в старых версиях Excel.

Вместо этого мы будем использовать функцию GET.CELL(), который является частью языка макросов XLM, предшествовавшего VBA. К сожалению, GET.CELL нельзя использовать непосредственно на листе. Однако, используя именованную формулу, мы можем обойти эту проблему.

Сначала мы создаем новое имя CellHasFormula. Для справки мы используем функцию GET.CELL следующим образом:

Первый аргумент — 48 — сообщает GET.CELL о возврате TRUE, если ячейка содержит формулу. Второй параметр — это функция ДВССЫЛ. В этом случае "rc" означает текущую строку и столбец, а FALSE указывает ДВСЕСЬ, что мы используем ссылки на стиль R1C1 вместо ссылок на стиль A1.

Теперь мы можем выбрать диапазон, с которым мы хотим работать, и создать новое правило условного форматирования. Мы хотим использовать формулу для управления форматированием, и формула, которую мы используем, — это просто формула с именем CellHasFormula.

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

Одним из преимуществ условного форматирования является то, что оно не влияет на фактическое форматирование ячеек. Чтобы перестать выделять формулы, просто удалите правило.

В следующий раз, когда вы наследуете новую книгу, попробуйте один из этих трех способов, чтобы быстро и легко найти все формулы.

В этом посте рассказывается, как выделить ячейки, содержащие формулы, с помощью функции условного форматирования в Excel. Как условно отформатировать ячейку, если она содержит формулу с использованием определяемой пользователем функции в сочетании с функцией условного форматирования в Excel 2013/2016.

Предполагается, что у вас есть список данных в диапазоне A1:C5, в котором содержатся названия продуктов, значения продаж и формулы. И вы хотите выделить ячейки только в том случае, если они содержат формулу. Как это сделать. Этот пост покажет вам два метода условного форматирования ячеек, если ячейки содержат формулу.

Метод 1: выделить ячейки, содержащие формулы, используя заданное имя

Чтобы выделить ячейки, содержащие формулы на вашем листе, вам нужно создать новое имя диапазона IsFormula в вашей книге. Просто выполните следующие действия:

Шаг 1: перейдите на вкладку «Формулы» и нажмите кнопку «Диспетчер имен» в группе «Определенные имена». и откроется диалоговое окно «Диспетчер имен».

Шаг 2. Нажмите кнопку «Создать» в диалоговом окне «Диспетчер имен». Откроется диалоговое окно «Новое имя».

Шаг 3: введите имя IsFormula в текстовое поле «Имя» и выберите «Книга» в раскрывающемся списке «Область». затем вам нужно ввести следующую формулу в текстовое поле «Относится к». нажмите кнопку "ОК".

=GET.CELL(48,ДВССЫЛ("rc",FALSE))

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

Шаг 5: перейдите на вкладку «Главная», нажмите команду «Условное форматирование» в группе «Стили» и выберите «Новое правило» в раскрывающемся списке меню. Откроется диалоговое окно «Новое правило форматирования».

Шаг 6: нажмите «Использовать формулу, чтобы определить, какие ячейки форматировать» в разделе «Выберите тип правила» в диалоговом окне «Новое правило форматирования» и введите следующую формулу в текстовое поле «Формат значений», где эта формула является истинной.

=Формула

Шаг 7: нажмите кнопку «Формат». Откроется диалоговое окно «Формат ячеек». Перейдите на вкладку «Заливка» в диалоговом окне «Формат ячеек» и выберите один цвет фона, чтобы выделить ячейки с формулами. нажмите кнопку «ОК», чтобы вернуться в диалоговое окно «Новое правило форматирования».

Шаг 8: нажмите кнопку ОК, чтобы увидеть последний результат:

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

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

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

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

В этом посте рассказывается, как объединять ячейки, но игнорировать все пустые ячейки на листе в Excel. Как объединить ячейки, но игнорировать пустые ячейки с формулой в Excel. Как создать формулу конкатенации для .

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

В пакете данных Excel легко найти максимальное или минимальное значение, но если этот пакет чисел содержит как положительные, так и отрицательные числа, максимальное или минимальное абсолютное значение не может быть найдено с помощью .

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

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

В этом посте рассказывается, как фильтровать данные с помощью формулы в Excel. Как фильтровать ячейки с формулами с помощью определяемой пользователем функции в Excel 2013/2016. Формула фильтра Предположим, что у вас есть список данных.

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

В этом посте рассказывается, как извлечь число из заданной тестовой строки в Excel. Как извлечь все числа из строки с помощью формулы в Excel. Как получить все числа из заданной тестовой строки, используя .

В этом посте рассказывается, как отфильтровать или удалить строки с зачеркнутым форматом в выбранном диапазоне ячеек в Excel. Как фильтровать зачеркнутый форматированный текст с помощью функции «Найти и заменить» в Excel 2013/2016. Или как .

Используйте условное форматирование в Excel с формулами для выделения ошибок, пробелов и дубликатов

От группы обучения Avantix | Обновлено 7 апреля 2021 г.

Применимо к: Microsoft® Excel® 2010, 2013, 2016, 2019 и 365 (Windows)

Условное форматирование — отличный инструмент в Excel, и если вы объедините его с формулами, вы сможете делать удивительные вещи. В этой статье мы воспользуемся несколькими простыми примерами, чтобы продемонстрировать возможности условного форматирования.

Условное форматирование отображается на вкладке "Главная" на ленте в группе "Стили".

Вы можете применить условное форматирование с помощью формулы и нового правила. Ниже приведен пример правила с формулой:

Хотите узнать больше об Excel? Посетите наш виртуальный класс или онлайн-курсы Excel >

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

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

В приведенном ниже примере в столбце C есть ошибки:

Чтобы выделить ошибки с помощью условного форматирования:

  1. Выберите диапазон ячеек в столбце, некоторые из которых содержат ошибки. В этом примере выберите от C2 до C10 .
  2. Перейдите на вкладку «Главная» на ленте и выберите «Условное форматирование» в группе «Стили». Появится раскрывающееся меню.
  3. Выберите Новое правило в раскрывающемся меню. Появится диалоговое окно.
  4. Нажмите Использовать формулу, чтобы определить ячейки для форматирования. В поле формулы введите =ЕОШИБКА(ссылка на активную ячейку). В этом примере введите =ЕОШИБКА(C2). Введите формулу для первой ячейки диапазона. В Excel по умолчанию используются относительные ссылки, поэтому формула будет скопирована и скорректирована для остальных ячеек.
  5. Нажмите «Формат». Появится диалоговое окно.
  6. Перейдите на вкладку "Заливка" и выберите светлую заливку.
  7. Дважды нажмите "ОК".

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

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

В приведенном ниже примере в столбце C есть пробелы:

Чтобы выделить пробелы с помощью условного форматирования:

  1. Выберите диапазон ячеек в столбце, некоторые из которых содержат пробелы. В этом примере выберите от C2 до C10.
  2. Перейдите на вкладку «Главная» на ленте и выберите «Условное форматирование» в группе «Стили». Появится раскрывающееся меню.
  3. Выберите в меню Новое правило. Появится диалоговое окно.
  4. Нажмите Использовать формулу, чтобы определить ячейки для форматирования. В поле формулы введите =ПУСТО(ссылка на активную ячейку). В этом примере введите =ПУСТО(C2). Введите формулу для первой ячейки диапазона. В Excel по умолчанию используются относительные ссылки, поэтому формула будет скопирована и скорректирована для остальных ячеек.
  5. Нажмите «Формат». Появится диалоговое окно.
  6. Перейдите на вкладку "Заливка" и выберите светлую заливку.
  7. Дважды нажмите "ОК".

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

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

В приведенном ниже примере в столбце A есть дубликаты:

Чтобы применить условное форматирование для выделения дубликатов:

  1. Выберите диапазон ячеек в столбце, в котором некоторые ячейки содержат дубликаты. В этом примере выберите от A2 до A10.
  2. Перейдите на вкладку «Главная» на ленте и выберите «Условное форматирование» в группе «Стили». Появится раскрывающееся меню.
  3. Выберите в меню Новое правило. Появится диалоговое окно.
  4. Нажмите Использовать формулу, чтобы определить ячейки для форматирования. В поле формулы введите =СЧЁТЕСЛИ(абсолютный диапазон,ячейка, содержащая критерии)>1. В этом примере введите =СЧЁТЕСЛИ($A$2:$A$10,A2)>1. Введите формулу для диапазона ячеек с использованием абсолютной ссылки (поскольку этот диапазон не должен изменяться) и для ячейки, содержащей критерии, с использованием относительной ссылки и ссылки на активную ячейку. Затем Excel скопирует формулу и откорректирует оставшиеся ячейки.
  5. Нажмите «Формат». Появится диалоговое окно.
  6. Перейдите на вкладку "Заливка" и выберите светлую заливку.
  7. Дважды нажмите "ОК".

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

Подпишитесь, чтобы получать больше статей, подобных этой

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

Читайте также: