Условное форматирование обычно проверяет значение в одной ячейке и применяет форматирование к другим ячейкам. Отличным применением условного форматирования является выделение всей строки или нескольких строк на основе значения ячейки и условия, приведенного в формуле.
Это очень полезно, потому что для набора данных с большим количеством значений в нем становится громоздким анализировать только путем чтения данных. Итак, если мы выделим несколько строк на основе некоторых условий, пользователю станет легче делать выводы из набора данных. Например, в колледже некоторые студенты были внесены в черный список из-за некоторых незаконных действий. Таким образом, в записи Excel администратор может выделить строки, в которых присутствуют записи об этих студентах.
В этой статье мы увидим, как выделить строки на основе значения ячейки, используя подходящий пример, показанный ниже :
Пример: Рассмотрим данные о сотрудниках компании. Следующая таблица состоит из данных о проекте (проектах), которые были назначены сотрудникам, их возрасте и идентификаторе. Сотрудник может работать над несколькими проектами. Пустая ячейка в project означает, что этому сотруднику не был назначен ни один проект.
Выделение строк
1. На основе совпадения текста :
Цель : выделить все строки, где имя сотрудника “Сришти”.
Шаги :
1. Выделите весь набор данных от A3 до D14 в нашем случае.
2. На вкладке Главная выберите Условное форматирование. Откроется выпадающее меню.
3. Выберите Новое правило из выпадающего списка. Откроется диалоговое окно.
4. В диалоговом окне “Новое правило форматирования” выберите «Использовать формулу, чтобы определить, какие ячейки форматировать» в опции «Выбрать тип правила».
5. В поле формулы введите формулу :
=$B2=»Srishti»
$ is used to lock the column B, so that only the cell B is looked starting from Row 2
Сначала формула проверит, присутствует ли имя “Сришти” в ячейке B2. Теперь, поскольку ячейка B заблокирована, в следующий раз проверка будет выполнена из ячейки B3 и так далее, пока условие не будет выполнено.
6. В поле предварительного просмотра выберите «Формат», затем перейдите к «Заливке «, затем выберите подходящий цвет для выделения и нажмите «ОК».
7. Теперь нажмите OK, и строки будут выделены.
Выделенные строки
2. Нетекстовое сопоставление на основе числа :
Цель : выделить все строки, возраст которых меньше 25.
Подход :
Повторите описанные выше шаги, как описано, и в формуле напишите :
=$C3<25
Выделенные строки
3. На основе условия ИЛИ / И :
ИЛИ, И используются, когда у нас есть несколько условий. Это логические операторы, которые работают со значением True .
И : Если все условия верны, И возвращает TRUE.
ИЛИ: по крайней мере, одно из условий должно быть истинным, чтобы возвращать ИСТИННОЕ значение.
Цель : выделить все строки сотрудников, которые работают либо над проектом 1, либо над проектом 4.
Сведения о проекте находятся в столбце D. Таким образом, формула будет :
=OR($D3=»P-1″,$D3=»P-4″)
Цель : Предположим, что сотрудник Rishabh завершил проект P-3. Администратора просят выделить строку и вести учет завершенных проектов.
Название находится в столбце B, а сведения о проекте — в столбце D. Формула будет иметь вид :
=AND($B3=»Rishabh»,$D3=»P-3″)
Выделенная строка
4. На основе любой пустой строки :
Цель : Проверить, есть ли какие-либо пустые строки. Если они существуют, выделите их.
Сведения о проекте находятся в столбце D. Мы будем использовать функцию COUNTIF( ) для проверки количества пустых записей. Формула будет иметь вид :
=COUNTIF($A3:$D3,»»)>0
«» : Denotes blank
Приведенная выше формула проверяет все столбцы один за другим и проверяет, есть ли хотя бы одна пустая строка. Если значение формулы больше нуля, то будет выполнено выделение, иначе COUNTIF вернет значение FALSE, и выделение не будет выполнено.
5. На основе нескольких условий, и каждое условие имеет разный цвет :
Цель : Предположим, компания хочет различать сотрудников по их возрасту. Сотрудники, возраст которых превышает 25 лет, являются старшими сотрудниками, а возраст которых превышает 20, но меньше или равен 25, являются младшими разработчиками или стажерами. Поэтому администратору предлагается выделить эти две категории разными цветами.
Реализация :
В поле Формула напишите формулу :
=$C3>25
При этом будут выделены все строки, возраст которых превышает 25.
Снова в поле Формула напишите формулу :
=$C3>20
Это выделит все строки, размер которых превышает 20. Фактически это изменит цвет строк, возраст которых превышает 25. Поскольку, если число больше 20, то оно определенно больше 25. Таким образом, все ячейки, возраст которых превышает 20, будут выделены одним и тем же цветом.
Это создает проблему, поскольку наша цель — создать две отдельные группы. Чтобы решить эту проблему, нам нужно изменить приоритет выделения строк. Следующие шаги :
- Отмените предыдущие шаги с помощью CTRL + Z.
- Выделите весь набор данных.
- Перейдите к условному форматированию, за которым следует Управление правилами.
- В диалоговом окне «Управление правилом» :
Порядок выполнения условия необходимо изменить. Условие в верхней части будет иметь больший приоритет, чем в нижней. Итак, нам нужно переместить второе условие в начало первого, используя значок вверх после выбора условия.
- Теперь нажмите «Применить», а затем «ОК».
Можно заметить, что строки теперь разделены на две категории: желтый цвет предназначен для сотрудников старшего уровня, а зеленый — для младших сотрудников и стажеров в компании в зависимости от их возраста.
