Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Интервалы дат в динамический массив дат. Одной формулой. На лету.
 
#1
18.12.2021 18:04:07
Здравствуйте, господа эксперты.

Админу: по правилам я, вроде как, должен в этой теме (двухлетней, без малого, давности) написать. Но у меня иная, чуть более узкая задача. Посему и создал тему новую. Ежели нарушил - каюсь заранее, на берегу. Скажите - перенесу.
Итак,

Дано: интервалы дат (создаются руками, произвольно).
Найти (создать): динамический массив из дат входящих в интервалы. Одной формулой. Последовательность дат - не важна, т.е. любая.

Пытаюсь, не могу. Взываю к помощи. Дайте хотя бы на_водку, как сконструировать массивную формулу ?
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#2
18.12.2021 18:35:30
Цитата
Сергей Евдокимов написал:
Найти (создать): динамический массив из дат входящих в интервалы. Одной формулой. Последовательность дат - не важна, т.е. любая.
Без повторений? Всех диапазонов?
По вопросам из тем форума, личку не читаю.
 
 
 
#3
18.12.2021 18:50:29
Цитата
написал: Без повторений? Всех диапазонов?
Если дата повторяется в интервалах, то повторяется и в массиве. Хоть дважды, хоть трижды и т.д.
Да, всех указанных диапазонов. Сия задача сродни предшествующей, где тоже не мог сконструировать массивную формулу.

P/S/ даже можно по интервалам не группировать (как в файле показано).
Достаточно  создать на выходе массив всех дат входящих во все интервалы. Хоть одномерный, хоть двумерный. Пофиг.
Изменено: Сергей Евдокимов - 18.12.2021 19:03:18
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#4
18.12.2021 19:03:03
Так нужно?
=SMALL(IF((ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)))>=INDEX(2:2;N(INDEX(COLUMN(A:J)*2-1;))))*(ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)))<=INDEX(2:2;N(INDEX(COLUMN(A:J)*2;))));ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2))));ROW(A1:INDEX(A:A;COUNT(IF((ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)))>=INDEX(2:2;N(INDEX(COLUMN(A:J)*2-1;))))*(ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)))<=INDEX(2:2;N(INDEX(COLUMN(A:J)*2;))));ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2))))))))
По вопросам из тем форума, личку не читаю.
 
 
 
#5
18.12.2021 19:07:36
Цитата
Так нужно?
Да. Результат верный. Только опупеть какая она огромная ))
Думал, будет полегче. Такую даже вряд ли когда раскрутить (для понимаю) смогу.

P/S/ разве что в какую-нибудь лямбду ее засунуть 😄
Изменено: Сергей Евдокимов - 18.12.2021 19:10:55
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#6
18.12.2021 19:13:53
Если не не беспокоит концовка с ошибками то короче

=SMALL(IF((ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)))>=INDEX(2:2;N(INDEX(COLUMN(A:J)*2-1;))))*(ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)))<=INDEX(2:2;N(INDEX(COLUMN(A:J)*2;))));ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2))));ROW(A1:INDEX(A:A;10*(MAX(2:2)-MIN(2:2)+1))))
Изменено: БМВ - 18.12.2021 19:16:15
По вопросам из тем форума, личку не читаю.
 
 
 
#7
18.12.2021 19:17:48
чуть покороче
Код
=НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ОСТАТ(СТОЛБЕЦ(A2:T2);2);--ТЕКСТ(ТЕКСТ(СТРОКА(A1:ИНДЕКС(A:A;МАКС(A2:T2)-МИН(A2:T2)+1))+МИН(A2:T2)-1;"[>="&A2:S2&"]0;");"[<="&B2:T2&"]0;");"");"");СТРОКА(A1:ИНДЕКС(A:A;СУММ((B2:T2-A2:S2+1)*ОСТАТ(СТОЛБЕЦ(A2:S2);2)))))
 
 
 
#8
18.12.2021 19:26:40
Андрей Лящук, + я 2 момента упустил начисто. Так задумался о неповторяющихся что количество равное сумму количеств в периодах упустил .
Чуть сократит можно паразитируя на --TEXT(TEXT
=SMALL(--TEXT(TEXT(MOD(COLUMN(A:S);2)*ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)));"[>="&A2:S2&"]0;1\E9");"[<="&B2:T2&"]0;1\E9");ROW(A1:INDEX(A:A;SUM((B2:T2-A2:S2+1)*MOD(COLUMN(A:S);2)))))
Изменено: БМВ - 18.12.2021 20:25:45
По вопросам из тем форума, личку не читаю.
 
 
 
#9
18.12.2021 20:22:58
Спасибо, господа. Ваши решения рабочие и их можно использовать.
Но мои попытки их понять не увенчались успехом. Как-то жутко для меня, многоэтажно.

_______________________
Боевая задача такая: каждый сотрудник имеет разное количество дней отпуска. И может потреблять его частями. Фиксируются интервалы, когда сотрудник не на работе. И нужно получить сколько дней своего отпуска он израсходовал (осталось).
  • Если в интервал отсутствия попадает нерабочий день, то, соответственно, этот день отниматься от отпуска НЕ должен.
  • Или наоборот: если какая-нибудь суббота/вскр. по указанию Путина перенесена и является рабочей, то ее НАДО отнять.
Поэтому типовые Excel-решения с датами не подходят.

В итоге даю кадровику таблицу, она просто вносит интервалы - и остаток отпуска подсчитывается автоматом.
Упрощенный шаблон

Пользуемся сей системой давно. Все работает. Каждый год невольно ее совершенствую. Но всё-равно мне не нравится то, что данные из интервалов я получаю посредством "+" в формуле. Не красиво. Не мастерски. Совсем не камильфо. При наличии-то уже динамических массивов )

Красивым мне виделось решение с использованием ЧИСТРАБДНИ.МЕЖД
В котором массивно укажу все интервалы (спасибо БМВ за помощь) и вычту массив всех нерабочих дней (он есть). Но побочный эффект сей функции в том, что она обязательно должна всё-таки отнять "стандартные" выходные.  Вот я и хотел эти выходные назад прибавить, посчитав их количество в перечне дат. А получение этих дат из интервалов, в свою очередь, и есть старт этой темы 👌
Изменено: Сергей Евдокимов - 18.12.2021 20:36:10
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#10
18.12.2021 20:39:33
Сергей Евдокимов, это уже другая тема, но функции не учитывают рабочие дни, которые перенесены, то есть есть выходные сб-ВС. Есть праздничные, а есть черный сб, вс.  не совсем понятно, почему периоды могут накладываться или это  только пример.
По вопросам из тем форума, личку не читаю.
 
 
 
#11
18.12.2021 20:56:23
Цитата
БМВ написал:
не совсем понятно, почему периоды могут накладываться или это  только пример.
Пример. По факту конечно пересечений нет.
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#12
18.12.2021 21:18:04
Вы б сформулировали иначе, вам ведь даже не массив нужен, а колличество рабочих дней с учетом праздников и переносов в указанных периодах. Так?
Тогда от первого до последнего дня нужно проверить попадает ли в период, попадает ли в выходной или перенесенный или в праздник, но это другая тема.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
18.12.2021 22:54:39
Список уникальных, вдруг кому пригодится
Код
=НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЧАСТОТА(--ТЕКСТ(ТЕКСТ(ОСТАТ(СТОЛБЕЦ(A:S);2)*СТРОКА(ИНДЕКС(A:A;МИН(2:2)):ИНДЕКС(A:A;МАКС(2:2)));"[>="&A2:S2&"]0;1\E9");"[<="&B2:T2&"]0;1\E9");СТРОКА(ИНДЕКС(A:A;МИН(2:2)):ИНДЕКС(A:A;МАКС(2:2))))^0*СТРОКА(ИНДЕКС(A:A;МИН(2:2)):ИНДЕКС(A:A;МАКС(2:2)));"");СТРОКА(A1:ИНДЕКС(A:A;МАКС(2:2)-МИН(2:2)+1)))
 
 
 
#14
19.12.2021 06:30:15
Цитата
БМВ: Вы б сформулировали иначе...
Формулировал.

Цитата
БМВ: вам ведь даже не массив нужен...
Массив дат - это промежуточный итог. Для расчета количества "стандартных" выходных во всех интервалах.

P/S/
Думаю, не открою Америку, если сообщу, что цель у меня главенствующая - не просто решить задачу, а обрести знания для ее решения.
Выложи вам задачу полностью, вы, без сомнения, ее решите, смастерив без пояснений многоэтажную конструкцию. А я не смогу ее даже раскрутить и понять. Стало быть, цели своей не достигну. Поэтому и провожу декомпозицию проблемных моментов, постепенно их решая.

P/S/S/
Конкретно в этой теме хотел понять как такую формулу =ПОСЛЕД(B2-A2+1;;0)+A2 (она вложением в файле стартовой темы) превратить в массивную. Чтобы перебирала не только один интервал, а сразу все ей указанные. Как здесь.
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#15
19.12.2021 06:37:28
Цитата
Андрей Лящук: Список уникальных, вдруг кому пригодится
Андрей, а вас не сильно затруднит хотя-бы крупными мазками, по пунктам, поведать, как ваша формула работает творит чудеса?
Ну или с отступами ее как-то выложить (типо код VBA), чтобы можно было поразбираться, понимая где начало и конец расчетных блоков.
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#16
19.12.2021 09:48:32
Цитата
Сергей Евдокимов написал:
поэтому и провожу декомпозицию проблемных моментов, постепенно их решая.
это не всегда правильно, так как подход может отличатся и целиком задачу решить проще чем соединить.

Так как периоды не пересекаются, то уникальность нас не интересует
берем укороченный мной вариант
=SMALL(--TEXT(TEXT(MOD(COLUMN(A:S);2)*ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)));"[>="&A2:S2&"]0;1\E9");"[<="&B2:T2&"]0;1\E9");ROW(A1:INDEX(A:A;SUM((B2:T2-A2:S2+1)*MOD(COLUMN(A:S);2)))))

1.ну то что период дней для проверки это от MIN(2:2) до MAX(2:2) - ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2))) формирует строчную последовательность целых чисел равных датам.
2 MOD(COLUMN(A:S);2)* нужно для того чтоб отсечь проверку с четными парами диапазонов как , так как это столбцы то получаем при перемножении двумерный массив.
4 далее Андрей напомнил мне использование фомата условного
сперва число, а это целое  число в нечетном столбце или ноль в четном форматируется по правилу [>="&A2:S2&"]0;1\E9" в результате будет или само число если оно больше значени A2:S2 , правда записанное текстом или 1E9, то есть 10^9 что больше дат на многие года. напоминаю на входе двумерный массив образно
1 0 1 0...
2 0 2 0 ...
3 0 3 0 ...
4 0 4 0 ...
первый столбец сравнивается с A второй с B..... и на выходе если A2 = 2 например
1E9 1E9 1E9 1E9
2 1E9 1E9 1E9
3 1E9 1E9 1E9
4 1E9 1E9 1E9
это снова форматируется [<="&B2:T2&"]0;1\E9" и если b2 например 3 то
получаем
1E9 1E9 1E9 1E9
2 1E9 1E9 1E9
3 1E9 1E9 1E9
1E9 1E9 1E9 1E9
5. далее все просто берутся по порядку от меньшего к большему  SMALL( ......  .  а  ROW(A1:INDEX(A:A;SUM((B2:T2-A2:S2+1)*MOD(COLUMN(A:S);2)))) формирует последовательность от 1 до числа дней которое
SUM((B2:T2-A2:S2+1)*MOD(COLUMN(A:S);2)
количество дней всего будет равно сумме разности окончания периодов и их начала (B2:T2-A2:S2+1) , но так как массив выстроен строкой то берутся только разности нечетные по порядку MOD(COLUMN(A:S);2)

на выходе последовательность ваших дат. в примере это 2 и 3
Изменено: БМВ - 19.12.2021 10:51:01
По вопросам из тем форума, личку не читаю.
 
 
 
#17
19.12.2021 15:15:01
Как же все непросто.

Над разбором и пониманием только этой конструкции:
=СТРОКА(ИНДЕКС(A:A;МИН(2:2)):ИНДЕКС(A:A;МАКС(2:2)))
= ROW(INDEX(A:A;MIN(2:2)):INDEX(A:A;MAX(2:2)))
кучу времени просидел. Удивляюсь, как можно было такое придумать?!

4-ый пункт - всё. Тупик для меня. Набор каракуль.
Но всё-равно, спасибо вам, БМВ, за попытку объяснить. Знаний прикладных у меня не добавилось, но теперь я еще больше знаю, что нифига в Exсel'е не знаю. Вот такой вот каламбур ))
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
Страницы: 1
Наверх