Гугл таблицы условное форматирование формулы

518583 просмотра

6 ответа

2056 Репутация автора

Я использую Google Sheets для ежедневной панели. Мне нужно изменить цвет фона ячейки B5 на основе значения другой ячейки – C5. Если C5 больше 80%, тогда цвет фона зеленый, но если он ниже, он будет янтарным / красным.

Это доступно с функцией Google Sheets или мне нужно вставить скрипт?

Ответы (6)

439 плюса

16262 Репутация автора

Примечание: когда в пояснении ниже говорится «B5», это фактически означает «B », поэтому для C5 это B5, для C6 это B6 и так далее. Если вы не укажете $ B $ 5 – тогда вы ссылаетесь на одну конкретную ячейку.

В вашем случае вам нужно будет установить условное форматирование на B5.

• Используйте опцию « Пользовательская формула » и установите для нее значение =B5>0.8*C5 .
• установите опцию « Диапазон » на B5 .
• установить желаемый цвет

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

Более того, сделайте единое правило применимым ко всем строкам, используя диапазоны в « Range ». Пример, предполагающий, что первая строка является заголовком:

• В условном форматировании B2 установите для « Пользовательская формула » значение =B2>0.8*C2 .
• установите опцию « Диапазон » на B2:B .
• установить желаемый цвет

Будет аналогично предыдущему примеру, но будет работать со всеми строками, а не только со строкой 5.

Диапазоны также можно использовать в «Пользовательской формуле», поэтому вы можете раскрасить всю строку на основе значений их столбцов.

177 плюса

3610 Репутация автора

Еще один пример:

Если у вас есть столбец от A до D, и вам нужно выделить всю строку (например, от A до D), если B – «Complete», то вы можете сделать это следующим образом:

Конечно, вы можете изменить Range на A: T, если у вас есть больше столбцов.

Если B содержит «Complete», используйте поиск следующим образом:

17 плюса

809 Репутация автора

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

Он должен продемонстрировать, на что способна эта удивительная функция, и особенно, как работает $ thing.

Пример таблицы

Цвет от D до G зависит от значений в столбцах A, B и C. Но формуле необходимо проверить значения, которые фиксированы по горизонтали (пользователь, начало, конец), и значения, которые зафиксированы по вертикали (даты в строке 1). Вот где знак доллара становится полезным.

Решение

В таблице 2 пользователя, каждый с определенным цветом, соответственно foo (синий) и bar (желтый).
Мы должны использовать следующие правила условного форматирования и применить их оба к одному и тому же диапазону ( D2:G3 ):

На английском языке условие означает: «
Пользователь есть name , а дата текущей ячейки – после start и до». end

Обратите внимание, как единственное, что меняется между двумя формулами, – это имя пользователя. Это позволяет легко использовать его многими другими пользователями!

Пояснения

Важно : Переменные строки и столбцы относятся к началу диапазона. Но фиксированные значения не затрагиваются.

Это легко спутать с относительными позициями. В этом примере, если бы мы использовали диапазон D1:G3 вместо D2:G3 , форматирование цвета было бы смещено на 1 строку вверх.
Чтобы избежать этого, помните, что значение переменных строк и столбцов должно соответствовать началу содержащего диапазона .

В этом примере диапазон, который содержит цвета D2:G3 , поэтому начало D2 .

User , start И end изменяются со строками
-> Фиксированные столбцы АВС, переменные строки , начиная с 2: $A2 , $B2 , $C2

Dates варьируется в зависимости от столбцов
-> Переменные столбцы, начиная с D, фиксированная строка 1: D$1

1 плюс

48469 Репутация автора

изменить цвет фона ячейки B5 на основе значения другой ячейки – C5. Если C5 больше 80%, тогда цвет фона зеленый, но если он ниже, он будет янтарным / красным.

Нет упоминания о том, что B5 содержит какое-либо значение, поэтому предполагается, что 80% оно .8 отформатировано в процентах без десятичных знаков и пустых значений как «ниже»:

Выберите B5, цвет «янтарный / красный» со стандартной заливкой, затем «Формат» – «Условное форматирование», пользовательская формула и:

с зеленой заливкой и Done .

Автор: pnuts Размещён: 05.10.2018 03:39

-1 плюса

11741 Репутация автора

Я разочарован тем, как долго это решалось.

Я хочу увидеть, какие значения в моем диапазоне находятся за пределами стандартного отклонения.

1. Добавьте вычисление стандартного отклонения в ячейку где-нибудь =STDEV(L3:L32)*2
2. Выберите диапазон, который нужно выделить, щелкните правой кнопкой мыши, условное форматирование
3. Выберите формат ячеек, если он больше, чем
4. В поле типа Значение или Формула =$L$32 (в какой бы ячейке ни находился ваш stdev)

Я не мог понять, как вставить STDEv. Я перепробовал много вещей с неожиданными результатами.

плюса

152 Репутация автора

В общем, все, что вам нужно сделать, это добавить $ в качестве префикса в букву столбца и номер строки. Пожалуйста, смотрите изображение ниже

Условное форматирование – это автоматическое форматирование ячеек в при соблюдении некоторых критериев.

Все ячейки с числами больше 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” и нажимаем “Готово”.

Также можно поступить, если увеличилось кол-во строк в табличке. Просто измените диапазон, чтобы он включал в себя все строки. Но не ставьте в диапазон условного форматирования столбцы целиком – ваш документ может начать тормозить.

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

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

На согласовании, ПОДТВЕРЖДЕНО, ОТМЕНЕНО, ВАЖНО

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

Для начала следует определиться с данными. Ячейки, в которых будет проверяться значение находятся в колонке E – Статус согласования . Первая строка – заголовок. Общее количество строк до конца документа – 49.