Применить условное форматирование на основе другого значения ячейки в Google Таблицах
Условное форматирование в Google Таблицах работает путем анализа значения в ячейке и последующего форматирования этих ячеек в соответствии с заданным условием. В большинстве случаев вы можете использовать текущее значение ячейки для применения к ней условного форматирования, но вы также можете использовать это для применения условного форматирования на основе другого значения ячейки.
Например, если у вас есть оценки студентов по предмету, вы можете выделить тех студентов, которые набрали меньше 35, или тех, кто набрал больше 80. В этом случае мы применяем условное форматирование к ячейке с имя основано на счете в другой ячейке.
В этом руководстве я покажу вам, как использовать условное форматирование для выделения ячеек на основе другого значения ячейки.
Выделите ячейки с помощью условного форматирования на основе другого значения ячейки в Google Таблицах
Давайте возьмем пример оценки учащегося и посмотрим, как можно выделить имена учащихся на основе их оценок.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите выделить имена, у которых оценка меньше 35.
Ниже приведены шаги для этого:
- Выберите ячейки, которые вы хотите выделить (в этом примере имена учащихся)
- Щелкните параметр Формат
- Щелкните Условное форматирование. Это откроет панель условного форматирования справа.
- Убедитесь, что выбран «Одноцветный».
- В раскрывающемся списке «Правила форматирования» выберите параметр «Пользовательская формула есть».
- В поле «Значение или формула» введите следующую формулу: = B2
- В параметре Стиль форматирования выберите форматирование, которое вы хотите применить. В этом случае я выберу красный цвет.
- Щелкните Готово.
Вышеупомянутые шаги мгновенно выделят те ячейки (красным цветом), в которых есть имена студентов, набравших менее 35 баллов.
Теперь позвольте мне быстро объяснить, как это работает .
Поскольку я выделил ячейки с именами учеников, это будут выделенные ячейки. А для условного форматирования, чтобы решить, применять ли выбранное форматирование к ячейке или нет, достаточно просто проверить указанное условие. Если условие возвращает значение ИСТИНА для ячейки, тогда применяется форматирование, в противном случае — нет.
В нашем случае я использовал формулу = B2
Таким образом, когда условное форматирование проверяет ячейку A2, оно проверяет условие = B2
Выделение ячеек с помощью условного форматирования на основе нескольких других значений ячеек в Google Таблицах
В приведенном выше примере я показал вам, как выделить ячейку на основе значения в другой ячейке. Вы также можете расширить ту же логику и выделить ячейку на основе значений в нескольких других ячейках.
Например, предположим, что у меня есть набор данных, показанный ниже, и я хочу выделить все имена студентов, у которых они набрали менее 35 баллов по любому из трех предметов.
Чтобы это сработало, мне пришлось бы проанализировать три разных значения ячеек и выделить имя студента, если какое-либо значение ячейки (оценка) меньше 35.
Ниже приведены шаги для этого:
- Выберите ячейки, которые вы хотите выделить (в этом примере имена учащихся)
- Щелкните параметр Формат
- Нажмите на опцию условного форматирования
- Убедитесь, что выбран «Один цвет» (выберите, если это не так).
- В раскрывающемся списке «Правила формата» выберите вариант «Пользовательский формат».
- В поле «Значение или формула» введите следующую формулу: =OR(B2 <35,C2<35,D2<35)
- В параметре Стиль форматирования выберите форматирование, которое вы хотите применить. В этом случае я выберу красный цвет.
- Щелкните Готово.
Вышеупомянутые шаги будут анализировать каждую ячейку для имени студента, и в случае, если какой-либо из предметов меньше 35, ячейка будет выделена красным.
Вы также можете использовать приведенную ниже формулу вместо формулы, использованной выше:
=OR(ARRAYFORMULA(B2:D2<35))
Итак, есть два примера, в которых я использовал простую формулу для применения условного форматирования на основе другого значения ячейки (или нескольких значений ячеек) в Google Таблицах.
Одним из замечательных преимуществ условного форматирования является то, что оно динамическое и автоматически обновляет и выделяет ячейки с именами учащихся, набравших менее 35 баллов по любому предмету.
Вы также можете использовать те же шаги, что и выше, для применения нескольких правил условного форматирования. Например, если вы также хотите выделить все ячейки, в которых учащиеся набрали более 80 баллов по всем предметам, вы можете просто использовать другую формулу. Затем каждая ячейка будет проанализирована для обоих условий и выделена соответствующим образом.
Надеюсь, вы нашли этот урок полезным!
- Как скопировать условное форматирование в Google Таблицы (2 простых способа)
- Как очистить форматирование в Google Таблицах (шаг за шагом)
- Как применить формулу ко всему столбцу в Google Таблицах (3 простых способа)
- Как копировать значения, а не формулы в Google Таблицах
- Преобразование формул в значения в Google Таблицах (простое руководство)
- Как скрыть нулевые значения в Google Таблицах (2 простых метода)
- Как суммировать значения ячеек между двумя датами в Google Таблицах
- Подсчет ячеек на основе цвета ячеек в Google Таблицах
- Как распечатать выбранные ячейки в Google Таблицах (шаг за шагом)
- Как разделить в Google Таблицах (числа, ячейки или столбцы)
Условное форматирование в Google Таблицах
Условное форматирование - это автоматическое форматирование ячеек в при соблюдении некоторых критериев.
Все ячейки с числами больше 20000 выделяются зеленым
Все ячейки со словом “Книга” выделяются полужирным шрифтом
Все ячейки с прошедшими датами выделяются серым
Условное форматирование можно вызвать в меню “Формат”:
Как создать правило условного форматирования
1.Выделите диапазон, к которому будет применяться правило.
2. Выберите “Условное форматирование” в меню “Формат”.
3. В пункте “Форматирование ячеек” выберите подходящее вам правило
(“Ячейка с данными” означает, что все заполненные ячейки будут форматироваться)
4. Ниже в пункте “Формат” выберите подходящее форматирование ячеек, подходящих под условие:
Как ссылаться на ячейку в правиле условного форматирования?
Чтобы автоматически выделять ячейки, которые больше (или меньше, или может быть и любое другое из доступных правил) не какого-то фиксированного числа, а числа, указанного в ячейке, можно сослаться на эту ячейку в правиле условного форматирования:
В данном примере будут выделены все ячейки в диапазоне B2:B5, числа в которых больше, чем число, записанное в ячейке C1. То есть вы сможете оперативно поменять число в C1, если решите изменить критерий выделения ячеек, и не придется менять само правило условного форматирования.
Как удалить лишние условия условного форматирования из нашего листа?
Для начала их нужно отобразить:
● откройте лист, с которым будете работать
● выделите все ячейки, выберите любую ячейку и нажмите сочетание клавиш CTRL+A (иногда CTRL+A потребуется нажать несколько раз), либо просто нажмите в левый верхний угол листа
● ячейки выделены, теперь откройте правила условного форматирования, либо правой кнопкой мыши на любой ячейке, пункт “условное форматирование” либо “формат” -> “условное форматирование” в панеле меню.
Перед нами все правила условного форматирования на листе, я советую избавляться от тех, которые не используются или дублирующихся, если вы их случайно создали. Без этих правил документ будет работать быстрее.
Для удаления - просто кликните на корзину.
Как скопировать правило условного форматирования?
Допустим, ваше правило условного форматирования только в строке А2:С2. Чтобы скопировать его на ячейки ниже, выделите строчку А2:С2, выберите “копировать”
Далее выделите строки, на которые нужно перенести правило
И вставьте, кликнув на выбранных ячейках правой кнопкой, используя “специальная вставка”-> “вставить только условное форматирование” (либо вставьте через панель меню, эти же пункты). Значения не поменяются, на выделенные ячейки перенесется только правило.
Либо альтернативный вариант: откройте правила условного форматирования и исправьте диапазон (допустимы и несколько разрозненных диапазонов)
Кейс с условным форматированием. Создаем правило, которое будет выделять цветом опоздавших на работу.
Представьте, что вы руководитель и каждый день проверяете табличку, в которой отмечено время прихода на работу ваших сотрудников.
Рабочий день начинается в 8 утра, так что давайте создадим правило условного форматирования и пусть оно выделит всех тех, кто опоздал на 20 минут и более.
Нам поможет вот эта формула:
=($B2-ЦЕЛОЕ($B2))>=ВРЕМЗНАЧ("08:20")
=($B2-INT($B2))>=TIMEVALUE("08:20")
в левой части мы оставляем от даты и времени только время прихода, а далее сравниваем его с критической точкой опоздания, 08:20. Точку опоздания переводим в числовой формат при помощи формулы ВРЕМЗНАЧ.
А дальше мы:
1) Выделяем диапазон данных B2:B12 (и начинать обязательно с B2, у нас на этом построена формула)
2) Клик правой кнопкой на диапазоне и выбираем “Условное форматирование”
3) Форматирование ячеек: “Ваша формула”, а в поле вводим формулу из предыдущего абзаца, начиная со знака равенства
4) Выбираем стиль условного форматирования, то есть то, как изменятся ячейки в тех случаях, когда формула будет выполняться (будет истиной)
Чтобы выделялись и фамилии работников, нужно расширить диапазон правила: вместо “B2:B12” выберем “A2:B12” и нажимаем “Готово”.
Также можно поступить, если увеличилось кол-во строк в табличке. Просто измените диапазон, чтобы он включал в себя все строки. Но не ставьте в диапазон условного форматирования столбцы целиком - ваш документ может начать тормозить.
Условное форматирование в Гугл Таблицах
. С условным форматированием в Excel мы уже немного знакомы, поскольку я касался этой темы в статье про поиск дублей в Эксель. Google Docs – традиционно более сложный и чуждый нам вычислитель, поэтому с условным форматированием в Sheets и у меня возникли проблемы. В этой статье я научу вас выделять ячейки по условию, а также переносить форматирование на другие листы и таблицы.
- 1 Как сделать условное форматирование в «Гугл Таблице»?
- 2 Как перенести форматирование в Google Sheets на другой лист?
- 3 Как теперь отключить?
- 4 Подводим итоги
Как сделать условное форматирование в «Гугл Таблице»?
Поскольку мой сайт специализированный, я приведу пример из SEO. Кейс будет такой: есть таблица запросов с позициями, и нужно выделить запросы с позициями в первой десятке зеленым, во второй десятке – желтым, за ее пределам (ниже) – красным. Вот запросы:
Дальше – по шагам:
Шаг 1. Идем во вкладку «Формат» и выбираем пункт «Условное форматирование».
Справа появится такой блок:
Шаг 2. В меню «Правила форматирования» выбрать «Меньше или равно».
В появившемся окошечке поставить значение «10» и нажать «Готово»
Шаг 3. Кликаем на «Применить к диапазону», выделяем диапазон ячеек с позициями и нажимаем «ОК».
Видим, что все ячейки, соответствующие условию, окрасились зеленым (потому что он стоял по умолчанию).
Шаг 4. В блоке «Правила условного форматирования» жмем «Добавить правило».
Устанавливаем второе правило – «Между» — и проставляем значения «11» и «20». Ниже, где блок «Формат» выбираем жёлтый цвет. Кликаем на «Готово».
Единственная ячейка, соответствующая условию, окрасилась жёлтым.
Шаг 5. Наконец, настроим последнее правило. Устанавливаем правило «Больше» (1), проставляем «20» (2), выбираем красный цвет (3) и жмем «Готово».
Все, наша таблица отформатирована по условиям. Многим из SEO-шников не нравится такой «цветник», а по мне так сильно упрощает анализ. Всегда так делаю.
Как перенести форматирование в Google Sheets на другой лист?
А если таких таблиц много, на каждую придется заново прописывать правила? Нет, это не обязательно, ведь условное форматирование можно скопировать. Нажимаем на отформатированную ячейку и кликаем на инструмент «Копировать форматирование».
Далее переходите на другой лист и просто выделяете диапазон. Клетки раскрасятся автоматически.
Как теперь отключить?
Это проще простого. Выделите диапазон, перейдите во вкладку «Формат» и кликните на «Очистить форматирование»
Также можно воспользоваться сочетанием горячих клавиш CTRL+\, если так сподручнее.
Подводим итоги
Форматировать по условию в Google Sheets ничуть не сложнее, чем в Excel – просто немного непривычно. Рекомендую вам использовать этот инструмент, поскольку он довольно много функциональный. С помощью его опять-таки можно искать дубли или пустые клетки в таблицах Google Docs.
А ты пользовался условным форматированием в Google Sheets? Расскажи нам, для каких целей, в комментариях!
Полезен ли был этот пост?
Кликни на звездочку, чтобы оценить его!
Средний рейтинг 1 / 5. Итог: 8
Еще никто не проголосовал :((
Оформление данных в Google Таблицах
В Google Таблицах вы можете отформатировать ячейки для их более удобного восприятия.
Доступны следующие варианты форматирования:
- изменить шрифт и его размер;
- сделать текст в ячейках полужирным, зачеркнутым, написанным курсивом;
- изменить цвет текста и цвет заливки ячейки;
- изменить расположение текста в ячейке как по горизонтали, так и по вертикали;
- повернуть текст в ячейке.
Копирование форматирования ячейки
Настроенное для ячейки форматирование можно скопировать для применения к другой ячейке.
Для этого выделите ячейку-образец, кликните кнопку Копировать форматирование, затем кликните по ячейке, к которой требуется применить форматирование.
Очистка форматирования ячейки
Удаление данных из ячейке не приводит к очистке форматирования этой ячейки.
Для удаления форматирования выделите ячейку, перейдите в меню Формат и выберите пункт Очистить форматирование.
Шрифты Lexend
В офисном пакете от Google стали доступны новые шрифты, которые компания анонсировала некоторое время назад.
Шрифты из коллекции Lexend были разработаны специально для того, чтобы облегчить удобство чтения пользователями информации из документов.
Для того, чтобы добавить шрифты к себе в коллекцию, необходимо при выборе шрифта нажать Другие шрифты, в появившемся окне указать lexend в строке поиска и выбрать необходимое.
Темы оформления
Долгожданный инструмент появился в Google Таблицах.
Темы оформления позволяют в один клик поменять настройки визуального отображения целого массива данных на предустановленные.
Пользователь может выбрать один из предложенных вариантов, а также внести в любой из них персонализированные изменения.
Расписание ближайших групп: