Содержание:
При работе с данными в Google Таблицах вам может потребоваться выделить самое большое значение (максимальное значение) или самое низкое значение (минимальное значение) в наборе данных.
И вы можете легко сделать это с помощью условного форматирования. Это простой способ применить формат к ячейке на основе значения в ней.
В этом руководстве я покажу вам, как использовать условное форматирование, чтобы выделить наивысшее значение (максимальное значение), выделить наименьшее значение (минимальное значение), а также выделить 3 верхних и 3 нижних значения в наборе данных.
Итак, приступим!
Выделите высшую ценность в Google Таблицах
Предположим, у меня есть набор данных с именами учащихся и их оценками по предмету, и я хочу выделить наивысший результат в этом наборе данных.
- Выберите диапазон, в котором есть оценки
- Выберите в меню опцию Формат.
- Нажмите на условное форматирование
- Убедитесь, что на открывшейся панели «Правила условного формата» выбран параметр «Один цвет».
- Убедитесь, что диапазон правильный (если нет, вы можете изменить его здесь)
- В раскрывающемся списке «Правила формата» выберите «Пользовательская формула».
-
Введите в поле следующую формулу: = B2 = MAX ($ B $ 2: $ B $ 21) - Укажите цвет, которым вы хотите выделить ячейку (я выберу зеленый цвет по умолчанию)
Вышеупомянутые шаги выделят ячейку с наибольшим значением.
В условном форматировании вы можете использовать настраиваемую формулу, которая возвращает ИСТИНА или ЛОЖЬ.
Условное форматирование просматривает каждую ячейку в указанном диапазоне и анализирует значение этой ячейки, используя указанную вами формулу.
Если результатом формулы является ИСТИНА, то условное форматирование будет применять указанный формат, а если результатом формулы является ЛОЖЬ, то условное форматирование ничего не даст.
В нашем примере выше он просматривает каждую ячейку и анализирует оценку, а также проверяет, равна ли оценка максимальной оценке в списке или нет. Только ячейка с максимальным количеством очков вернет ИСТИНА, а все остальные ячейки вернут ЛОЖЬ.
Вот почему только ячейка с максимальным значением выделяется зеленым цветом (это был формат, который мы указали при условном форматировании).
Если есть несколько ячеек с максимальным значением, все эти ячейки будут выделены.
Выделите самую низкую ценность в Google Таблицах
Так же, как я выделил самое высокое значение в наборе данных, я также могу выделить самое низкое значение в данных.
Все, что для этого нужно, — это небольшая настройка формулы.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите выделить самый низкий балл в столбце B.
- Выберите диапазон, в котором есть оценки
- Выберите в меню опцию Формат.
- Нажмите на условное форматирование
- Убедитесь, что на открывшейся панели «Правила условного формата» выбран параметр «Один цвет».
- Убедитесь, что диапазон правильный (если нет, вы можете изменить его здесь)
- В раскрывающемся списке «Правила формата» выберите «Пользовательская формула».
- Введите в поле следующую формулу:
= B2 = MIN ($ B $ 2: $ B $ 21) - Укажите цвет, которым вы хотите выделить ячейку. В этом случае я выберу красный оттенок.
Вышеупомянутые шаги выделят ячейку с наименьшим (минимальным) значением.
Только ячейка с минимальным значением вернет ИСТИНА для этой формулы и будет выделена, а все остальные ячейки останутся нетронутыми.
Выделите 3 верхних или 3 последних значения в Google Таблицах
Теперь, скажем, вместо выделения самого высокого или самого низкого значения вы хотите выделить 3 верхних значения или 3 нижних значения.
Процесс и логика остались прежними.
Все, что вам нужно сделать, это настроить формулу таким образом, чтобы она возвращала ИСТИНА для трех верхних значений (или трех нижних значений) и выделяла эти ячейки в указанном формате.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите выделить три лучших результата в этих данных.
- Выберите диапазон, в котором есть оценки
- Выберите в меню опцию Формат.
- Нажмите на условное форматирование
- Убедитесь, что на открывшейся панели «Правила условного формата» выбран параметр «Один цвет».
- Убедитесь, что диапазон правильный (если нет, вы можете изменить его здесь)
- В раскрывающемся списке «Правила формата» выберите «Пользовательская формула».
-
Введите в поле следующую формулу: = B2> = LARGE ($ B $ 2: $ B $ 21,3) - Укажите цвет, которым вы хотите выделить ячейку (я выберу зеленый цвет по умолчанию)
Вышеупомянутые шаги выделят три лучших результата в столбце B.
В нашем примере, поскольку мы хотим выделить три верхних значения, я использовал большую формулу, чтобы дать мне третий по величине результат.
Формула теперь просто проверяет каждую ячейку и проверяет, больше ли значение третьего по величине значения или нет. Если это так, то условное форматирование выделяет ячейки, в противном случае оно остается неизменным.
Если вы хотите выделить 3 наименьших результата, вы можете использовать приведенную ниже формулу на шаге 7:
= B2 <= SMALL($ B $ 2: $ B $ 21,3)
А если вы хотите выделить три верхних и три нижних оценки, вам нужно дважды применить условное форматирование с формулами НАИБОЛЬШИЙ и МАЛЕНЬКИЙ.
И убедитесь, что форматирование другое (например, зеленый цвет для трех верхних значений и оранжевый или желтый цвет для трех нижних значений).
Выделите всю строку с наибольшим / наименьшим значением
В примерах, рассмотренных до сих пор, мы выделили только те ячейки, в которых есть оценки.
Но что, если вы хотите выделить всю запись вместе с именем ученика.
Это легко сделать, немного изменив формулу условного форматирования.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите выделить запись с наивысшим баллом в этих данных.
- Выберите весь набор данных ( A2: B21 в этом примере )
- Выберите в меню опцию Формат.
- Нажмите на условное форматирование
- Убедитесь, что на открывшейся панели «Правила условного формата» выбран параметр «Один цвет».
- Убедитесь, что диапазон правильный (если нет, вы можете изменить его здесь)
- В раскрывающемся списке «Правила формата» выберите «Пользовательская формула».
- Введите в поле следующую формулу: = $ B2 = MAX ($ B $ 2: $ B $ 21)
Вышеупомянутые шаги выделят всю запись для ученика, который набрал наибольшее количество баллов.
Хотя эта формула работает так же, как и остальные, описанные выше, есть одно небольшое отличие, которое вам необходимо понять.
= $ B2 = MAX ($ B $ 2: $ B $ 21)
В приведенной выше формуле вместо B2 я использовал $ B2. Это называется смешанной ссылкой.
Ниже приведены три типа справочников, которые вы можете использовать в Google Таблицах:
- B2 — это относительная ссылка, не имеющая знака $
- B $ 2 / $ B2 — это смешанные ссылки, у которых есть один $ (либо перед буквой столбца, либо перед номером строки)
- $ B $ 2 — это абсолютная ссылка, в которой перед буквой столбца и номером строки стоит знак доллара.
В приведенном выше примере мы использовали $ B2, который является смешанной ссылкой.
Это означает, что, хотя условное форматирование анализирует все ячейки в каждой строке при переходе к ячейке A2, оно будет использовать следующую формулу, чтобы проверить, возвращает ли она истину или ложь:
= $ B2 = MAX ($ B $ 2: $ B $ 21)
И когда он переходит к B2, он снова использует ту же формулу выше.
Теперь, когда он переходит в ячейку A3, он использует следующую формулу:
= $ B3 = MAX ($ B $ 2: $ B $ 21)
и когда он переходит в ячейку B3, он снова использует приведенную выше формулу.
Добавив знак доллара перед буквой столбца, мы гарантируем, что даже когда ячейки в столбце анализируются, формула по-прежнему ссылается на значение в столбце B. Это сделано, потому что даже когда имя нужно выделить, нам все равно нужно чтобы проанализировать оценку этого человека.
Вот как вы можете использовать простые формулы условного форматирования для выделения наивысшего или наименьшего значения в наборе данных (или верхних N или нижних N значений).
Надеюсь, вы нашли этот урок полезным.