Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Условное форматирование несколких ячеек в зависимости от значения в двух из них, Необходимо распространить это правило на "почти" бесконечное количество ячеек
 
#1
17.07.2018 12:28:42
Добрый день, уважаемые знатоки.
Заранее извиняюсь, если вопрос/задачка примитивная, или неинтересная, или сто раз избитая (я не смог насёрфить решение простым поиском)


Составляю шаблон для графика работы. В нём каждому сотруднику на каждый день посвящено 6 ячеек. В двух из них проставляется время прихода на работу и ухода с неё. Необходимо, чтобы при заполнении этих двух ячеек все 6 отмечались некоторым цветом заливки.
Такую задачу по одному человеку на один день я, вроде, решил.
Но вторая задача - сделать так, чтобы это условное форматирование распространилось на весь график, и могло распространятся несложными движениями при увеличении, например, строчек (из-за увеличения численности), или - при продлении графика на следующие недели.


Полагаю, описание не самое понятное =) Поэтому пример прикрепил...
Изменено: Эжен Несознанский - 17.07.2018 12:38:27
1.
 
 
 
#2
17.07.2018 12:53:10
Для двух УФ
=OR(AND(F3<>0;G3<>0);AND(F2<>0;G2<>0);AND(F1<>0;G1<>0))
=AND(G3<>0;F3<>0)
Изменено: БМВ - 17.07.2018 13:09:46
По вопросам из тем форума, личку не читаю.
 
 
 
#3
17.07.2018 12:55:51
Можно попробовать так:
=ИНДЕКС($F$3:$GG$270;ЦЕЛОЕ(ОСТАТ(СТРОКА(F3);3)/3)+ЦЕЛОЕ(СТРОКА(F3)/3);СТОЛБЕЦ(F3)-4)<>0
Но у Вас проблема будет с окрашиванием тех ячеек, в которых указано начальное время и не указано конечное, т.к. объединенные ячейки будут мешать.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#4
17.07.2018 13:09:09
Или для единого
=OR(AND(F$2<>"";OR(AND(F3<>0;G3<>0);AND(F2<>0;G2<>0);AND(F1<>0;G1<>0)));AND(F$2="";E3<>"";F3<>""))

UPD, так будет правильнее
=IF(F$2<>"";OR(AND(F3<>0;G3<>0);AND(F2<>0;G2<>0);AND(F1<>0;G1<>0));AND(E3<>"";F3<>""))
Изменено: БМВ - 17.07.2018 13:36:22
По вопросам из тем форума, личку не читаю.
 
 
 
#5
17.07.2018 13:22:51
Вот еще формула - будет окрашивать весь блок только если обе ячейки со временем заполнены. В противном - не красит.
=И(ИНДЕКС($F$3:$GG$229;ЦЕЛОЕ(ОСТАТ(СТРОКА(F3);3)/3)+ЦЕЛОЕ(СТРОКА(F3)/3);СТОЛБЕЦ(F3)-ЕСЛИ(F$2="";6;5))<>0;ИНДЕКС($F$3:$GG$229;ЦЕЛОЕ(ОСТАТ(СТРОКА(F3);3)/3)+ЦЕЛОЕ(СТРОКА(F3)/3);СТОЛБЕЦ(F3)-ЕСЛИ(F$2="";5;4))<>0)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#6
17.07.2018 13:52:45
Как истовый Хондовод, сегодня должен всё-таки признавать - БМВ рулит! =))))))

В Вашем первом примере (Copy of Пример148) хот по факту и работало, как надо, но оказалось, что на листе ооооочень много правил. И когда я удалил все правила, кроме указанных здесь в сообщении (оставил только =OR(AND(F3<>0;G3<>0);AND(F2<>0;G2<>0);AND(F1<>0;G1<>0)) и =AND(G3<>0;F3<>0), полез баг: правая верхняя ячейка "6-ячеечного блока" не окрашивалась, если справа от неё была пустая ячейка.
Видимо не хватало какого-то доп.правила.


Но со второй формулой (в "Copy of Пример148_1") всё заработало как надо!


"Премного Вами благодарен"!   © =)))
1.
 
 
 
#7
17.07.2018 14:03:32
Дмитрий(The_Prist) Щербаков, "что-то пошло не так": этот способ работает в пределах первых трёх 6-ячеечных блоков. А в остальном пространстве получается не так красиво (см. прикреплённый пример)
1.
 
 
 
#8
17.07.2018 14:31:12
Цитата
Эжен Несознанский написал:
И когда я удалил все правила, кроме указанных здесь
там были два правила для типа F и G столбцов, потом простым копированием форматов размножены.
Но лучше 148_1 использовать и именно с =IF(F$2<>""; .

Цитата
Эжен Несознанский написал:
"что-то пошло не так":
Возможно надо допилить чуток , чтоб попадать в нужные строки. Будет ли это эффективнее, чем многочисленные сравнения - может да, может нет. Понятнее точно не будет + любой сдвиг строк или столбцов может все сбить, но вариант Дмитрия подтолкнул к варианту #4.

Дмитрий чуть переусердствовал
=AND(INDEX($F$3:$GG$229;INT(ROW()/3)*3-2;COLUMN()-IF(F$2="";6;5))<>0;INDEX($F$3:$GG$229;INT(ROW()/3)*3-2;COLUMN()-IF(F$2="";5;4))<>0)
Изменено: БМВ - 17.07.2018 15:14:12
По вопросам из тем форума, личку не читаю.
 
 
 
#9
17.07.2018 20:06:59
Просто чуть напутал с диапазоном в ИНДЕКС и забыл умножить на кол-во строк в блоке. Надо бы так:
=И(ИНДЕКС($F$1:$GG$229;ЦЕЛОЕ(ОСТАТ(СТРОКА(F3);3)/3)+ЦЕЛОЕ(СТРОКА(F3)/3)*3;СТОЛБЕЦ(F3)-ЕСЛИ(F$2="";6;5))<>0;ИНДЕКС($F$1:$GG$229;ЦЕЛОЕ(ОСТАТ(СТРОКА(F3);3)/3)+ЦЕЛОЕ(СТРОКА(F3)/3)*3;СТОЛБЕЦ(F3)-ЕСЛИ(F$2="";5;4))<>0)
Впрочем, БМВ уже поправил. Спасибо.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#10
17.07.2018 20:48:02
Дмитрий(The_Prist) Щербаков, Дмитрий , что-то я не пойму цель усложнения?
ЦЕЛОЕ(ОСТАТ(СТРОКА(F3);3)/3)+ЦЕЛОЕ(СТРОКА(F3)/3)*3
vs
INT(ROW()/3)*3-2
Или все дело в
$F$1:$GG$229 и $F$3:$GG$229;
Изменено: БМВ - 17.07.2018 20:49:26
По вопросам из тем форума, личку не читаю.
 
 
 
#11
17.07.2018 21:24:15
Цитата
БМВ написал:
Или все дело в
Да в общем-то нет. Просто перемудрил на стадии выдумывания формулы и все.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#12
17.07.2018 21:29:16
Цитата
Дмитрий(The_Prist) Щербаков написал:
Просто перемудрил
Бывает.
Однако интересно что легче из #4 Или Из #8.  Естественно не про длину, а про расчетную нагрузку.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
18.07.2018 00:08:49
БМВ, <я уже малость "ковыряюсь"...> у Вас и в примере из #4, и в примере из #8 есть зависимость заливки блока от наличия даты: если даты вверху столбца нет, то блок окрашивается не полностью. Так и задумывалось?
1.
 
 
 
#14
18.07.2018 00:24:02
Эжен Несознанский, №4 да, надо было привязаться к чему то, что определяет столбец.  а вот в №8 такого не должно быть,
По вопросам из тем форума, личку не читаю.
 
 
 
#15
18.07.2018 08:32:10
В моей формуле(как и в последней формуле БМВ) не столько завязка на дату, сколько на некий признак, что это первый столбец из двух:
ЕСЛИ(F$2="";6;5)
Можно взять и не дату, а название дня недели или любой другой признак. Но он должен быть по факту. Изначально я и от этого хотел уйти, отсюда и полезли ноги в виде ОСТАТ, т.к. они и для столбцов изначально были.
Изменено: Дмитрий(The_Prist) Щербаков - 18.07.2018 08:32:58
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
Страницы: 1
Наверх