Страницы: 1
RSS
Выделение диапазона, если в нем несколько подряд одинаковых ячеек
 
Добрый день. Занимаюсь заполнением табеля. Необходимо, чтобы при выставлении графика, выделялись ячейки, если выставляется больше 5 рабочих дней.
К примеру, поставил я какому нибудь сотруднику подряд 6 рабочих дней, в этот момент все эти дни должны выделиться цветом.
 
см.вложение
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Немного не то, что я хотел. Посмотрите на этот диапазон.
Картинка 1

Если я добавлю рабочий день на 6 число, то красным выделится только 1 число, если я укажу раб день еще и 7 число, выделиться 2ой день, и т.д.

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

Картинка 3

Подскажите пожалуйста, как это сделать.
Изменено: RamRiz - 29.10.2020 21:19:06
 
а без картинок можно?
называя ячейки их именами ві даете абсолютно точную информацию собеседнику о чем речь
и на примере своего файла, в который я добавил условные форматы (жирным белым по красному)
вот здесь формат зарисовал, а не нужно было
вот здесь не зарисовал, а нужно было
вот здесь я пишу это должно бы все зарисовать, а не зарисовывает
вот здесь очистил ячейку - должен бы снятся формат, а он остался
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ничего сложного
непрерывный диапазон одинаковых значений из X элементов может быть относительно какой либо ячейки а в диапазоне от левее на X-1 и правее на X-1
то есть для J6 это E6:O6 .Применяем для этого https://www.planetaexcel.ru/techniques/2/4033/ и находим самую длинную последовательность и сравниваем её с нужной величиной
=MAX(FREQUENCY(IF(E6:O6=8;COLUMN(E:O));IF(E6:O6<>8;COLUMN(E:O))))=6  . 8 - это признак рабочего дня. Если есть иное, то нужно знать какое оно. Может оказаться что нужно просто проверить ISNUMBER(E6:O6), тогда и 8 и 7 и 12 будет рабочим днем.
но
Если для если для F6 диапазон будет A6:K6 то для E6 Будет ошибка. В этом случае или нужно добавить пустой столбец, или мудрить с диапазоном
=MAX(FREQUENCY(IF(INDEX(6:6;MAX(1;COLUMN()-5)):J6=8;COLUMN(INDEX(6:6;MAX(1;COLUMN()-5)):J6));IF(INDEX(6:6;MAX(1;COLUMN()-5)):J6<>8;COLUMN(INDEX(6:6;MAX(1;COLUMN()-5)):J6))))=6
но УФ не умеет работать с UNION и тогда просто убираем формулу в имя, в области листа, если будут табеля по месяцам в разных листах
Изменено: БМВ - 30.10.2020 08:57:26
По вопросам из тем форума, личку не читаю.
 
Ігор Гончаренко, Извиняюсь, хотел как лучше, думал так понятней будет.
БМВ, Спасибо большое, то что нужно.
Но вы хорошо подметили момент с количеством часов, он ведь может отличаться.
В основном у меня в табеле по 8 часов ставится, но бывает и 7 часов ставлю, если короткий рабочий день.
Как можно сделать, чтобы учитывались любые цифры, указанные в ячейке? Это будет означать рабочий день, а они не должены превышать больше 5.

В общем, создать правило форматирования, в котором учитываются любые цифры.
 
БМВ, Разобрался с вашей формулой, вы всю формулу вставили в диспетчер имен, и назвали ее _Check6.
Теперь у меня тоже работает.
Но есть один ньюанс.
В табеле из моего примера месяц делится на 2 части. с 1 по 15 и с 16 по 31 число. Табель заполняется по очереди, сначала 1 часть, потом 2ая. Поэтому между ними стоит стобец, который показывает отработанное количество часов за первую половину месяца. И из-за этого ваша формула подсчета ячеек перестает работать именно в этом моменте. Обратите внимание на диапазон R6:Z6, там 8 рабочих дней подряд, но выделилилсь не все.

Я вижу один выход из этой ситуации, перенести этот стобец для подсчета количества часов в конец таблицы, но если вы сможете подобрать соответствующую формулу, не прибегая к перестановке столбца, было бы идеально.
Изменено: RamRiz - 30.10.2020 10:00:50
 
=MAX(FREQUENCY(IF(ISNUMBER(INDEX(Октябрь!6:6;MAX(1;COLUMN()-5)):Октябрь!O6);COLUMN(INDEX(Октябрь!6:6;MAX(1;COLUMN()-5)):Октябрь!O6));IF(ISNUMBER(INDEX(Октябрь!6:6;MAX(1;COLUMN()-5)):Октябрь!O6);1=0;COLUMN(INDEX(Октябрь!6:6;MAX(1;COLUMN()-5)):Октябрь!O6))))>5
Это занесено в Имена под именем _Check6
Сейчас реаггирует на любые числа, но и на 0  тоже.
По вопросам из тем форума, личку не читаю.
 
БМВ,Посмотрите пожалуйста сообщение выше, я отредактировал.
 
RamRiz, Возможно , но сложность не пропорционально вырастает. В одну формулу не стал объединять.
Для устрашения если не менять методологию.
IF(ISNUMBER(INDEX(6:6;N(INDEX(SMALL(IF(ISNUMBER($E$4:$AJ$4);COLUMN($E:$AJ));MIN(MAX($E$4:$AJ$4);MAX(0;DAY(E$4)-6))+ROW($A$1:INDEX($A:$A;11+MIN(0;DAY(E$4)-6)+MIN(0;DAY(MAX($E$4:$AJ$4))-DAY(E$4)-5))));))));
    ROW($A$1:INDEX($A:$A;11+MIN(0;DAY(E$4)-6)+MIN(0;DAY(MAX($E$4:$AJ$4))-DAY(E$4)-5))));
IF(ISNUMBER(INDEX(6:6;N(INDEX(SMALL(IF(ISNUMBER($E$4:$AJ$4);COLUMN($E:$AJ));MIN(MAX($E$4:$AJ$4);MAX(0;DAY(E$4)-6))+ROW($A$1:INDEX($A:$A;11+MIN(0;DAY(E$4)-6)+MIN(0;DAY(MAX($E$4:$AJ$4))-DAY(E$4)-5))));))));1=0;
    ROW($A$1:INDEX($A:$A;11+MIN(0;DAY(E$4)-6)+MIN(0;DAY(MAX($E$4:$AJ$4))-DAY(E$4)-5))))
))>5
Изменено: БМВ - 30.10.2020 13:13:01
По вопросам из тем форума, личку не читаю.
 
БМВ,Спасибо большое. Все просто идеально.
 
БМВ,Расскажите пожалуйста что именно вы сделали. Я не смог перенести все это в свою таблицу.

Я скопировал ваши формулы, которые хранятся в диспетчере имен. Их там 3 штуки.
Но ничего не заработало.
 
Нужно или использовать R1C1 стиль или при занесении в имена находится в тех ячейках которые обрабатываются, для Вашего примера это E6.
По вопросам из тем форума, личку не читаю.
 
Вот ещё черновой вариант формулы для УФ. Учитывает столбец T между 15 и 16 числом. Но только для 6 рабочих смен подряд.
=ИЛИ(ПОИСКПОЗ(7;ЧАСТОТА(СТОЛБЕЦ($A:$AE);ЕСЛИ(Ч(СМЕЩ($D6;;СТОЛБЕЦ($A:$AE)+(СТОЛБЕЦ($A:$AE)>15)))>0;;СТОЛБЕЦ($A:$AE)));)=СТОЛБЕЦ(B:G)-(СТОЛБЕЦ(B:G)>15))
Изменено: Светлый - 30.10.2020 21:50:02
 
Цитата
Светлый написал:
СМЕЩ($D6
полетели :-)
По вопросам из тем форума, личку не читаю.
 
БМВ,Не понимаю я R1C1, а вот при копировании формулы с вашего примера и добавлении в диспетчер имен, как раз использую ячейку E6.  
 
Не могу сказать, что вы не так делаете.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
полетели :-)
Тогда так:
=ИЛИ(ПОИСКПОЗ(7;ЧАСТОТА(СТОЛБЕЦ($A:$AE);ЕСЛИ(Ч(ИНДЕКС($E6:$AJ6;Ч(ИНДЕКС(СТОЛБЕЦ($A:$AE)+(СТОЛБЕЦ($A:$AE)>15);))));;СТОЛБЕЦ($A:$AE)));)=СТОЛБЕЦ(B:G)-(СТОЛБЕЦ(B:G)>15))
*Перед разрывом неправильно форматирует.
Изменено: Светлый - 30.10.2020 21:53:25
 
Светлый, в файле бы посмотреть. Ну совершенно нет желания переводить.
По вопросам из тем форума, личку не читаю.
 
Светлый, Ваша формула работает, но если повторяющихся ячеек больше 6, то форматирование перестает работать. У БМВ в
этом плане нет ограничений на повторяющиеся ячейки, хоть до конца месяца могут выделяться.
 
Цитата
Светлый написал:
Но только для 6 рабочих смен подряд.
Цитата
RamRiz написал:
но если повторяющихся ячеек больше 6,
Не задалось...
Вот эта формула работает:
=СЧЁТ(1/(СТОЛБЕЦ(A6)<ПОИСКПОЗ(СТРОКА($7:$15);ЧАСТОТА(СТОЛБЕЦ($A:$AF);ЕСЛИ(Ч(ИНДЕКС($E6:$AI6;Ч(ИНДЕКС(СТОЛБЕЦ($A:$AE);))));;СТОЛБЕЦ($A:$AE)));))/(СТОЛБЕЦ(A6)+СТРОКА($7:$15)>ПОИСКПОЗ(СТРОКА($7:$15);ЧАСТОТА(СТОЛБЕЦ($A:$AF);ЕСЛИ(Ч(ИНДЕКС($E6:$AI6;Ч(ИНДЕКС(СТОЛБЕЦ($A:$AE);))));;СТОЛБЕЦ($A:$AE)));)))
Без столбца между 15 и 16 числами. Форматирует чёрным цветом до 15 ячеек подряд.
 
Светлый,Спасибо. Буду тогда так использовать, перенесу этот столбец в конец.
Ваша формула удобна для меня тем, что кроме нее больше ничего нету. Создал правило в условном форматировании, просто скопировав вашу формулу.
У БМВ нужно создавать дополнительные имена в диспетчере имен. Тоже рабочий вариант, но для меня слишком сложный, я так и не понял как перенести в свою таблицу.

Спасибо вам БМВ, Светлый, огромное за помощь.
 
Светлый, Чуть изменил таблицу. Добавил доп столбцы для подсчета количества строк, визуально более удобно стало. Но теперь формула считает не правильно, помогите пожалуйста исправить.
 
Добрый вечер!
Формула УФ конкретно для файла из сообщения #23:
=СЧЁТ(1/(СТОЛБЕЦ(A6)<ПОИСКПОЗ(СТРОКА($7:$15);ЧАСТОТА(СТОЛБЕЦ($A:$AF);ЕСЛИ(Ч(ИНДЕКС($G12:$AK12;Ч(ИНДЕКС(СТОЛБЕЦ($A:$AE);))));;СТОЛБЕЦ($A:$AE)));))/(СТОЛБЕЦ(A6)+СТРОКА($7:$15)>ПОИСКПОЗ(СТРОКА($7:$15);ЧАСТОТА(СТОЛБЕЦ($A:$AF);ЕСЛИ(Ч(ИНДЕКС($G12:$AK12;Ч(ИНДЕКС(СТОЛБЕЦ($A:$AE);))));;СТОЛБЕЦ($A:$AE)));)))
Страницы: 1
Наверх