Советы по Google таблицам

Полное руководство по условному форматированию в Google Таблицах

Условное форматирование в Google Таблицах можно использовать для выделения / форматирования ячеек на основе значений в них.

Например, если у вас есть баллы 50 студентов по предмету, вы можете быстро выделить студентов, которые набрали меньше 35 баллов на экзамене.

Точно так же вы можете использовать множество таких условий (как мы увидим позже в этом руководстве) для выделения ячеек на основе их значения.

Как получить доступ к условному форматированию в Google Таблицах

Доступ к условному форматированию можно получить, перейдя в Формат → Условное форматирование.

При этом справа откроется панель «Правила условного формата», которую можно использовать для установки правил.

Теперь при использовании условного форматирования в Google Таблицах вам доступны два варианта:

  • Один цвет
  • Цветовая шкала

«Один цвет» можно использовать, если вы хотите выделить все ячейки в зависимости от значения. Например, я могу использовать это, чтобы выделить все ячейки с оценками меньше 35. В этом случае, независимо от того, будет ли оценка 34 или 10, обе будут выделены одним цветом.

«Цветовая шкала» может использоваться, когда вы также хотите визуально представить разницу между значениями в ячейках. Например, оценка 34 означает более светлый оттенок красного, а 10 — более темный оттенок красного.

Условное форматирование в Google Таблицах — Примеры

Вот несколько практических примеров использования условного форматирования в Google Таблицах.

Пример 1. Выделите баллы менее 35

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

Сделать это:

  • Выделите ячейки (в данном случае B2: B16).
  • Перейдите в Формат → Условное форматирование.
  • На панели «Условное форматирование» убедитесь, что выбран «Один цвет».
  • Убедитесь, что «Применить к диапазону» относится к правильному диапазону. Если это не так, вы можете изменить это здесь.
  • В раскрывающемся списке «Форматировать ячейки, если» выберите «Меньше, чем».
  • Новое поле появится прямо под раскрывающимся списком. Введите в него 35.
  • Выберите стиль формата. Вы можете выбрать один из стандартных или создать свой собственный. В этом случае позвольте мне выбрать красный цвет.
  • Щелкните Готово.

Это мгновенно выделит все ячейки с показателем менее 35 красным цветом (как показано ниже).

Пример 2: Создание тепловой карты с использованием оценок

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

Тепловая карта будет показывать градиент цветов на основе значений в ячейках (что-то, как показано ниже).

.

Вот шаги, чтобы создать тепловую карту с использованием результатов экзаменов:

  • Выберите набор данных, в котором вы хотите создать тепловую карту.
  • Перейдите в Формат → Условное форматирование.
  • На панели «Правила условного форматирования» выберите «Цветовая шкала».
  • Убедитесь, что «Применить к диапазону» относится к правильному диапазону. Если это не так, вы можете изменить это здесь.
  • В раскрывающемся списке Предварительный просмотр выберите нужный градиент. Обратите внимание, что цвет слева от градиента применяется к номерам с меньшими значениями, а одна из сторон с более высокими значениями применяется к номерам с более высокими значениями. Выбирая здесь градиент, вы также можете увидеть предварительный просмотр в наборе данных в реальном времени. В этом случае я выбираю градиент от красного к зеленому.
  • Щелкните Готово.

Это создаст тепловую карту с градиентом, показанным на основе значения в ячейке.

Пример 3. Выделите все экземпляры повторяющихся точек данных

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

Предположим, у вас есть набор данных, как показано ниже.

Как видите, в этом списке есть дубликаты.

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

  • Выберите набор данных.
  • Перейдите в Формат → Условное форматирование.
  • В правилах условного формата выберите «Одноцветный».
  • Убедитесь, что «Применить к диапазону» относится к правильному диапазону. Если это не так, вы можете изменить это здесь.
  • В раскрывающемся списке «Форматировать ячейки, если» выберите вариант «Пользовательская формула есть».
  • Введите в него следующую формулу: =COUNTIF($A$2:$A$11,A1)>1
  • Выберите формат.
  • Щелкните ОК.

Это выделит все случаи, когда есть повторяющиеся значения.

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

Пример 4: Выделите альтернативные строки (создание линий «зебра»)

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

Это повышает удобочитаемость отчета и придает ему более профессиональный вид (как показано ниже).

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

Вот шаги, чтобы выделить альтернативные ячейки с помощью условного форматирования в Google Таблицах:

  • Выберите набор данных.
  • Перейдите в Формат → Условное форматирование.
  • В правилах условного формата выберите «Одноцветный».
  • Убедитесь, что «Применить к диапазону» относится к правильному диапазону. Если это не так, вы можете изменить это здесь.
  • В раскрывающемся списке «Форматировать ячейки, если» выберите вариант «Пользовательская формула есть».
  • Введите в него следующую формулу: = MOD (ROW (), 2) = 1.
  • Выберите формат.
  • Щелкните ОК.

Это выделит альтернативные строки в выбранном диапазоне.

Как это работает: функция ROW () возвращает номер строки данной ячейки. Затем функция MOD () делит на 2 и возвращает остаток. Когда остаток равен 1 (в случае нечетных номеров строк), условие — TRUE (ИСТИНА) и применяется условный формат.

Если вы хотите выделить строки с четными числами, используйте следующую формулу: = MOD (ROW (), 2) = 0

Пример 5: выделение пустых ячеек

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

Предположим, у вас есть набор данных, как показано ниже:

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

  • Выберите набор данных.
  • Перейдите в Формат → Условное форматирование.
  • В правилах условного формата выберите «Одноцветный».
  • Убедитесь, что «Применить к диапазону» относится к правильному диапазону. Если это не так, вы можете изменить это здесь.
  • В раскрывающемся списке «Форматировать ячейки, если» выберите вариант «Пользовательская формула есть».
  • Введите в него следующую формулу: = ISBLANK (A2)
  • Выберите формат.
  • Щелкните ОК.

Это мгновенно выделит все пустые ячейки в наборе данных (как показано ниже).

Пример 6: выделение ошибок

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

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

Предположим, у вас есть набор данных, как показано ниже:

Вот шаги, чтобы выделить все ячейки, содержащие ошибку.

  • Выберите набор данных.
  • Перейдите в Формат → Условное форматирование.
  • В правилах условного формата выберите «Одноцветный».
  • Убедитесь, что «Применить к диапазону» относится к правильному диапазону. Если это не так, вы можете изменить это здесь.
  • В раскрывающемся списке «Форматировать ячейки, если» выберите вариант «Пользовательская формула есть».
  • Введите в него следующую формулу: = ISERROR (A2)
  • Выберите формат.
  • Щелкните ОК.

Это мгновенно выделит все ячейки, в которых есть какие-либо ошибки (как показано ниже).

Пример 7: выделение ячеек, содержащих искомую строку

Вы можете использовать условное форматирование в Google Таблицах для создания базы данных с возможностью поиска.

Что-то вроде показанного ниже:

Обратите внимание, что как только я ввожу имя в ячейку C2 и нажимаю клавишу ENTER, он выделяет это имя в наборе данных. Если вы вводите частичное имя, будут выделены все ячейки, содержащие эту часть строки. Например, если вы введете «P» в ячейку C2 и нажмете клавишу ввода, будут выделены имена Пэт и Пол.

Вот шаги, чтобы сделать это:

  • Выберите набор данных.
  • Перейдите в Формат → Условное форматирование.
  • В правилах условного формата выберите «Одноцветный».
  • Убедитесь, что «Применить к диапазону» относится к правильному диапазону. Если это не так, вы можете изменить это здесь.
  • В раскрывающемся списке «Форматировать ячейки, если» выберите вариант «Пользовательская формула есть».
  • Введите в него следующую формулу: = AND (NOT (ISBLANK ($ C $ 2)), ISNUMBER (SEARCH ($ C $ 2, A2)))
  • Выберите формат.
  • Щелкните ОК.

Теперь всякий раз, когда вы вводите строку в ячейку C2 и нажимаете клавишу ENTER, она автоматически выделяет все ячейки, содержащие эту строку.

Как это работает:

Формула, используемая в этом случае, состоит из двух частей:

  • НЕ (ISBLANK ($ C $ 2)) — эта часть используется для проверки, пуста ли ячейка C2 или нет. Если он пуст, то это условие ложно и ничего не выделяется. Если вы не используете эту часть, тогда все ячейки в наборе данных будут выделены, когда C2 пуст.
  • ISNUMBER (SEARCH ($ C $ 2, A2)) — эта часть проверяет, совпадает ли строка в ячейке C2 со строкой в ​​ячейках в диапазоне данных. Если строка найдена, функция SEARCH вернет число. В этом случае ISNUMBER вернет TRUE.