Выделение строк на основе значения ячейки в Excel
Условное форматирование обычно проверяет значение в одной ячейке и применяет форматирование к другим ячейкам. Отличным применением условного форматирования является выделение всей строки или нескольких строк на основе значения ячейки и условия, указанного в формуле.
Это очень полезно, потому что для набора данных с огромным количеством ценности становится громоздким анализировать просто чтение данных. Итак, если мы выделим несколько строк на основе некоторых условий, пользователю станет легче делать выводы из набора данных. Например, в колледже некоторые студенты были занесены в черный список из-за какой-то незаконной деятельности. Таким образом, в записи Excel администратор может выделить строки, в которых присутствуют эти записи учащихся.
В этой статье мы увидим, как выделить строки на основе значений ячеек, используя подходящий пример в реальном времени, показанный ниже:
Пример: рассмотрим данные о сотрудниках компании. Следующая таблица содержит данные о проектах, которые были назначены сотрудникам, их возраст и идентификатор. Сотрудник может работать над несколькими проектами. Пустая ячейка в проекте означает, что этому сотруднику не назначен ни один проект.
Выделение строк
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 .
AND : If all conditions are TRUE, AND returns TRUE.
OR : At least one of the conditions needs to be TRUE to return TRUE value.
Цель: выделить все строки сотрудников, которые работают либо над проектом 1, либо над проектом 4.
Детали проекта указаны в столбце D. Таким образом, формула будет выглядеть так:
=OR($D3="P-1",$D3="P-4")
Цель: Предположим, сотрудник Ришаб завершил проект P-3. Администратора просят выделить строку и вести учет завершенных проектов.
Имя находится в столбце B, а сведения о проекте — в столбце D. Формула будет следующей:
=AND($B3="Rishabh",$D3="P-3")
Выделенная строка
4. На основе любой пустой строки:
Цель: проверить, есть ли пустые строки. Если они есть, то выделите их.
Детали проекта находятся в столбце D. Мы будем использовать функцию COUNTIF() для проверки количества пустых записей. Формула будет:
=COUNTIF($A3:$D3,"")>0 "" : Denotes blank
Приведенная выше формула проверяет все столбцы один за другим и проверяет, есть ли хотя бы одна пустая строка. Если значение формулы больше нуля, будет выполнено выделение, иначе СЧЕТЕСЛИ вернет значение ЛОЖЬ, и выделение не будет выполнено.
5. На основе нескольких условий, и каждое условие имеет разный цвет:
Цель: Предположим, компания хочет различать сотрудников по возрасту. Сотрудники, возраст которых превышает 25 лет, являются старшими сотрудниками, а чей возраст превышает 20, но меньше или равен 25 годам, — младшими разработчиками или стажерами. Итак, администратора просят выделить эти две категории разными цветами.
Реализация :
В поле Формула напишите формулу:
=$C3>25
Это выделит все строки, где возраст больше 25 лет.
Снова в поле Формула напишите формулу:
=$C3>20
Это выделит все строки больше 20. На самом деле это изменит цвет строк, где возраст больше 25. Поскольку, если число больше 20, то оно определенно больше 25. Таким образом, все ячейки, имеющие возраст старше 20 лет будет выделен тем же цветом.
Это создает проблему, поскольку наша цель состоит в том, чтобы сделать две отдельные группы. Чтобы решить эту проблему, нам нужно изменить приоритет выделения строк. Шаги:
- Отмените предыдущие шаги с помощью CTRL+Z.
- Выберите весь набор данных.
- Перейдите в « Условное форматирование », а затем « Управление правилами».
- В диалоговом окне « Управление правилом »:
Порядок условия нужно изменить. Верхнее условие будет иметь больший приоритет, чем нижнее. Итак, нам нужно переместить второе условие в начало первого с помощью значка вверх после выбора условия.
- Теперь нажмите «Применить», а затем «ОК».
Можно заметить, что строки теперь разделены на две категории: желтый цвет предназначен для сотрудников старшего уровня, а зеленый цвет — для младших сотрудников и стажеров в компании в зависимости от их возраста.