Условное форматирование Excel на основе другой ячейки: видео
Узнайте, как можно создавать собственные правила для окрашивания данных в соответствии с любыми условиями.
Условное форматирование на основе другой ячейки: стенограмма видео
Нет никаких сомнений в том, что условное форматирование — одна из самых полезных функций Excel. Стандартные правила позволяют быстро раскрасить нужные значения, но что, если вы хотите отформатировать целые строки на основе значения в определенной ячейке? Позвольте мне показать вам, как вы можете использовать формулы для создания любого правила условного форматирования, которое вам нравится.
Применить условное форматирование, если другая ячейка пуста
Вот одна распространенная задача: я хочу выделить строки с пустым идентификатором. Начнем с шагов по созданию пользовательского правила:
- Прежде всего, выберите диапазон, который вы хотите выделить, это сэкономит вам несколько шагов позже. Убедитесь, что вы начинаете с верхней левой записи и опускаете строку заголовка. Преобразование диапазона в таблицу — лучший вариант, если вы планируете применять правило к новым записям в будущем.
- Нажмите «Условное форматирование» вверху и выберите «Новое правило». Вам нужен последний пункт: «Используйте формулу, чтобы определить, какие ячейки форматировать».
- Теперь вы можете ввести свое пользовательское условие и установить желаемый формат.
- Цвет заливки предлагает самый быстрый способ увидеть наши данные, поэтому давайте выберем один из них и нажмем «ОК».
- Формула для поиска строк с пробелами в столбце A: =A2=””. Но это не все. Чтобы убедиться, что правило применяется построчно, вам нужно сделать ссылку на столбец абсолютной, поэтому введите знак доллара перед столбцом A:
=$A2=””
Если вы хотите всегда смотреть на эту конкретную ячейку, вы также исправите строку, чтобы она выглядела следующим образом: $A$2=””
- Нажмите «ОК», и все готово.
Условное форматирование Excel на основе другого значения ячейки
Теперь давайте продолжим и посмотрим, как мы можем найти те названия книг, которые имеют 10 или более в столбце E. Я выберу названия книг, потому что это то, что мы хотим отформатировать, и создам новое правило условного форматирования, которое использует формула. Наше состояние будет аналогичным:
=$E2>=10
Выберите формат и сохраните правило, чтобы увидеть, как оно работает.
Как видите, это простые правила, в которые вы можете ввести любое значение, которое вас интересует. Где значение, не важно. Если он находится на другом листе, просто убедитесь, что вы включили его имя в свою ссылку.
Формула условного форматирования для нескольких условий
Перейдем к случаям, когда ваше условие касается двух разных значений. Например, вы можете захотеть увидеть заказы с высоким приоритетом и более 8 в поле количества.
Чтобы изменить существующее правило, выберите «Управление правилами» в разделе «Условное форматирование», найдите правило и нажмите «Изменить». Чтобы убедиться, что выполняются несколько условий, используйте функцию «И», затем укажите ваши критерии в скобках и не забудьте использовать кавычки для текстовых значений:
=И($D2=”Высокий”,$E2>8)
Если вы хотите убедиться, что хотя бы одно условие выполнено, используйте вместо этого функцию ИЛИ. Замените функцию, теперь она будет выглядеть так: выделить строку, если приоритет высокий или если количество больше 8.
Форматирование на основе другого текста ячейки
Вот еще одна функция, которую вы оцените, если будете работать с текстовыми значениями. Задача покажется сложной, если вы захотите просмотреть ячейки, содержащие ключевое слово, наряду с чем-либо еще. Если это ваш случай, вам нужно будет использовать функцию поиска, и вот как это будет выглядеть. Выберите записи для окрашивания, создайте правило и введите:
=ПОИСК(“Срочно”,$F2)>0
Обратите внимание: если вы введете более 1, вместо этого вы получите ячейки, начинающиеся с этого текста.
Что нужно помнить для ваших пользовательских правил
Вы можете использовать практически любую формулу в качестве условия выделения ваших данных. В одном из наших предыдущих видео мы рассмотрели как определить дубликаты с помощью условного форматирования, и вы можете найти еще несколько отличных примеров формул в нашем блоге по этой теме.
Прежде чем вы приступите к форматированию таблицы, позвольте мне быстро рассмотреть некоторые типичные ошибки, которые могут не позволить вам получить ожидаемые результаты.
Прежде всего, помните о разнице между абсолютными и относительными ссылками на ячейки. Если вы хотите проверить каждую ячейку в столбце, введите знак доллара перед названием столбца. Чтобы продолжать проверять одну и ту же строку, добавьте знак доллара перед номером строки. И чтобы исправить ссылку на ячейку, другими словами, чтобы продолжать проверять одну и ту же ячейку, убедитесь, что у вас есть знак доллара перед обоими: столбцом и строкой.
Затем, если вы видите, что ваше правило применяется только к одной строке или ячейке, вернитесь к управлению правилами и убедитесь, что оно применяется к правильному диапазону.
При создании правила всегда используйте верхнюю левую ячейку диапазона с вашими данными для формулы и опускайте строку заголовка, чтобы избежать смещения результатов.
Пока вы помните об этих моментах, формулы условного форматирования будут творить чудеса с вашими данными. Если у вас все еще есть проблемы с его работой, поделитесь своей задачей в комментариях, мы сделаем все возможное, чтобы помочь вам.
