Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Ближайшая вторая или четвертая суббота - Страница 3 - Мир MS Excel

Старая форма входа
  • Страница 3 из 3
  • «
  • 1
  • 2
  • 3
Модератор форума: китин  
Мир MS Excel » Работа и общение » Мозговой штурм » Ближайшая вторая или четвертая суббота (Формулы/Formulas)
Ближайшая вторая или четвертая суббота
AlexM Дата: Среда, 15.11.2023, 19:41 | Сообщение № 41
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
bmv98rus, две последние не работают правильно.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениеbmv98rus, две последние не работают правильно.
Автор - AlexM
Дата добавления - 15.11.2023 в 19:41
bmv98rus Дата: Среда, 15.11.2023, 22:58 | Сообщение № 42
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
AlexM, с FLOOR и правда не проверял, но остальные проверил, правда соглашусь что есть завязка на региональные, что очень не люблю сам.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеAlexM, с FLOOR и правда не проверял, но остальные проверил, правда соглашусь что есть завязка на региональные, что очень не люблю сам.
Автор - bmv98rus
Дата добавления - 15.11.2023 в 22:58
AlexM Дата: Четверг, 16.11.2023, 12:00 | Сообщение № 43
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
bmv98rus, в формуле с ОКРВВЕРХ для даты в А2 массив {#ЧИСЛО!;#ЧИСЛО!;#ЧИСЛО!}, поэтому формула возвращает #Н/Д
Из справки по функции
=ОКРВВЕРХ(-2,5; 2) Возвращает ошибку, т.к. число -2,5 и 2 имеют разные знаки (#ЧИСЛО!)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 16.11.2023, 12:03
 
Ответить
Сообщениеbmv98rus, в формуле с ОКРВВЕРХ для даты в А2 массив {#ЧИСЛО!;#ЧИСЛО!;#ЧИСЛО!}, поэтому формула возвращает #Н/Д
Из справки по функции
=ОКРВВЕРХ(-2,5; 2) Возвращает ошибку, т.к. число -2,5 и 2 имеют разные знаки (#ЧИСЛО!)
Автор - AlexM
Дата добавления - 16.11.2023 в 12:00
Gustav Дата: Четверг, 16.11.2023, 19:02 | Сообщение № 44
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2747
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Поддержу неугомонность в теме. Задумал расширить горизонты формулы с субботы на любой день недели. Второе условие пока остается прежним, т.е. "второй или четвертый...", но теперь не только суббота, а и любой другой день - от понедельника до воскресенья. Вот что получилось и вроде работает:
Код
=-ПРОСМОТР(-A2;-(ОТБР((ДАТА(ГОД(A2);МЕСЯЦ(A2)+{1;0;0};)+6-деньН)/7)+{2;4;2})*7+6-деньН)

где параметр деньН может принимать значения от 1 (понедельник) до 7 (воскресенье).

По поводу возможных дальнейших исследований. Нахождение, скажем, 1-й или 3-й субботы (или другого дня недели), наверное, вопросов уже не вызовет. А вот нахождение 5-й субботы (или другого дня) может быть интересным, поскольку не в каждом месяце имеются те или иные 5-е дни. Причем, вопрос становится еще более интересным при сохранении условия "ближайшая". "Ближайшая 5-я суббота" - это через сколько месяцев или даже лет? ;) (Хоррроший такой получится режим работы для правления СНТ! :D )

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Четверг, 16.11.2023, 19:14
 
Ответить
СообщениеПоддержу неугомонность в теме. Задумал расширить горизонты формулы с субботы на любой день недели. Второе условие пока остается прежним, т.е. "второй или четвертый...", но теперь не только суббота, а и любой другой день - от понедельника до воскресенья. Вот что получилось и вроде работает:
Код
=-ПРОСМОТР(-A2;-(ОТБР((ДАТА(ГОД(A2);МЕСЯЦ(A2)+{1;0;0};)+6-деньН)/7)+{2;4;2})*7+6-деньН)

где параметр деньН может принимать значения от 1 (понедельник) до 7 (воскресенье).

По поводу возможных дальнейших исследований. Нахождение, скажем, 1-й или 3-й субботы (или другого дня недели), наверное, вопросов уже не вызовет. А вот нахождение 5-й субботы (или другого дня) может быть интересным, поскольку не в каждом месяце имеются те или иные 5-е дни. Причем, вопрос становится еще более интересным при сохранении условия "ближайшая". "Ближайшая 5-я суббота" - это через сколько месяцев или даже лет? ;) (Хоррроший такой получится режим работы для правления СНТ! :D )
Автор - Gustav
Дата добавления - 16.11.2023 в 19:02
AlexM Дата: Четверг, 16.11.2023, 19:24 | Сообщение № 45
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
Комментарий к сообщению №1
Предыдущее правления нашего СНТ работали каждую субботу и воскресенье.
Новое правление работает 2 и 4 субботу каждого месяца.
В 5-ю субботу не каждого месяца будет работать следующее правление.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеКомментарий к сообщению №1
Предыдущее правления нашего СНТ работали каждую субботу и воскресенье.
Новое правление работает 2 и 4 субботу каждого месяца.
В 5-ю субботу не каждого месяца будет работать следующее правление.
Автор - AlexM
Дата добавления - 16.11.2023 в 19:24
Gustav Дата: Четверг, 16.11.2023, 19:30 | Сообщение № 46
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2747
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата AlexM, 16.11.2023 в 19:24, в сообщении № 45 ()
В 5-ю субботу не каждого месяца будет работать следующее правление.

hands yes

Для справки (пролистнул календарь): в 2023 году - четыре "5-х субботы": 29.04, 29.07, 30.09, 30.12. Т.е. да, всё-таки месяцы между ними, не годы.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата AlexM, 16.11.2023 в 19:24, в сообщении № 45 ()
В 5-ю субботу не каждого месяца будет работать следующее правление.

hands yes

Для справки (пролистнул календарь): в 2023 году - четыре "5-х субботы": 29.04, 29.07, 30.09, 30.12. Т.е. да, всё-таки месяцы между ними, не годы.
Автор - Gustav
Дата добавления - 16.11.2023 в 19:30
bmv98rus Дата: Четверг, 16.11.2023, 22:01 | Сообщение № 47
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
AlexM, ну у меня считает возможно от версии приложения зависит
К сообщению приложен файл: 0916662.xlsm (98.4 Kb)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеAlexM, ну у меня считает возможно от версии приложения зависит
Автор - bmv98rus
Дата добавления - 16.11.2023 в 22:01
Gustav Дата: Пятница, 17.11.2023, 11:08 | Сообщение № 48
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2747
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Что ж, даю первое приближение "ближайшей 5-й субботы", правда, на "новоязе" версий 365/2021++:
[vba]
Код
=-ПРОСМОТР(-A2; LET(
первые; (ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7;
пятые;  MAP(первые; LAMBDA(c; c+28));

-MAP(первые; пятые; LAMBDA(a; b; ЕСЛИ(МЕСЯЦ(a)=МЕСЯЦ(b); b; НД() )))
))
[/vba]
Смысл алгоритма, думаю, понятен даже при незнакомстве с новоязом, но всё же поясню. Сначала рассчитывается массив первых суббот - от текущего месяца (т.е. от месяца проверяемой даты A2) и на несколько месяцев вперед. Вроде бы, достаточно трёх, т.е. текущий месяц + 3 месяца вперед: {3;2;1;0}. Итого получается массив дат 4-х первых суббот соседних месяцев. Далее прибавляем к этим субботам 28 дней (4 недели) и получаем потенциальные даты пятых суббот (второй массив). Наконец, сравниваем месяцы пятых и первых суббот (в одинаковых позициях массивов). Если месяцы совпадают, то это реальная 5-я суббота внутри месяца, включаем ее как дату в результирующий массив для функции ПРОСМОТР. Если НЕ совпадают, то в результирующий массив на эту позицию помещаем значение #Н/Д.

Итого выходит, что к элементам массива первых суббот после его создания нужно будет обратиться еще по 3 раза: два раза для вычисления функции МЕСЯЦ и один раз для представления выбираемого значения в функции ЕСЛИ. Именно поэтому я и выбрал новояз: в нём, как видно, этот алгоритм получается вполне изящным и компактным. Боюсь, что в версии 2003 массив первых суббот придётся генерить внутри формулы именно 4 раза, ну, или предложить какой-то иной хитрый алгоритм, обходящий это неудобство.

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Пятница, 17.11.2023, 11:15
 
Ответить
СообщениеЧто ж, даю первое приближение "ближайшей 5-й субботы", правда, на "новоязе" версий 365/2021++:
[vba]
Код
=-ПРОСМОТР(-A2; LET(
первые; (ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7;
пятые;  MAP(первые; LAMBDA(c; c+28));

-MAP(первые; пятые; LAMBDA(a; b; ЕСЛИ(МЕСЯЦ(a)=МЕСЯЦ(b); b; НД() )))
))
[/vba]
Смысл алгоритма, думаю, понятен даже при незнакомстве с новоязом, но всё же поясню. Сначала рассчитывается массив первых суббот - от текущего месяца (т.е. от месяца проверяемой даты A2) и на несколько месяцев вперед. Вроде бы, достаточно трёх, т.е. текущий месяц + 3 месяца вперед: {3;2;1;0}. Итого получается массив дат 4-х первых суббот соседних месяцев. Далее прибавляем к этим субботам 28 дней (4 недели) и получаем потенциальные даты пятых суббот (второй массив). Наконец, сравниваем месяцы пятых и первых суббот (в одинаковых позициях массивов). Если месяцы совпадают, то это реальная 5-я суббота внутри месяца, включаем ее как дату в результирующий массив для функции ПРОСМОТР. Если НЕ совпадают, то в результирующий массив на эту позицию помещаем значение #Н/Д.

Итого выходит, что к элементам массива первых суббот после его создания нужно будет обратиться еще по 3 раза: два раза для вычисления функции МЕСЯЦ и один раз для представления выбираемого значения в функции ЕСЛИ. Именно поэтому я и выбрал новояз: в нём, как видно, этот алгоритм получается вполне изящным и компактным. Боюсь, что в версии 2003 массив первых суббот придётся генерить внутри формулы именно 4 раза, ну, или предложить какой-то иной хитрый алгоритм, обходящий это неудобство.
Автор - Gustav
Дата добавления - 17.11.2023 в 11:08
AlexM Дата: Пятница, 17.11.2023, 11:49 | Сообщение № 49
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
Код
=ПРОСМОТР(2;1/(МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7+28)=МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7));(ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7+28)

или
Код
=ПРОСМОТР(2;1/(МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)=МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7));(ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)

Есть несколько ошибок
К сообщению приложен файл: pjataja_subbota.xls (349.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Пятница, 17.11.2023, 11:55
 
Ответить
Сообщение
Код
=ПРОСМОТР(2;1/(МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7+28)=МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7));(ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7+28)

или
Код
=ПРОСМОТР(2;1/(МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)=МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7));(ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)

Есть несколько ошибок
Автор - AlexM
Дата добавления - 17.11.2023 в 11:49
AlexM Дата: Пятница, 17.11.2023, 12:13 | Сообщение № 50
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
Код
=ПРОСМОТР(2;1/(МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)=МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7))/((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7>=A2);(ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)

Работает, но очень длинная
К сообщению приложен файл: pjataja_subbota1.xls (480.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Код
=ПРОСМОТР(2;1/(МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)=МЕСЯЦ((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+1)*7))/((ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7>=A2);(ОТБР(ДАТА(ГОД(A2);МЕСЯЦ(A2)+{3;2;1;0};)/7)+5)*7)

Работает, но очень длинная
Автор - AlexM
Дата добавления - 17.11.2023 в 12:13
AlexM Дата: Пятница, 17.11.2023, 16:23 | Сообщение № 51
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
Еще вариант 5-ой субботы
Код
=1/ПРОСМОТР(2;1/(ДЕНЬНЕД(A2+123-СТРОКА($1:$123);2)=6)/(ДЕНЬ(A2+123-СТРОКА($1:$123))>28)/(A2+123-СТРОКА($1:$123)))
К сообщению приложен файл: pjataja_subbota2.xlsx (45.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЕще вариант 5-ой субботы
Код
=1/ПРОСМОТР(2;1/(ДЕНЬНЕД(A2+123-СТРОКА($1:$123);2)=6)/(ДЕНЬ(A2+123-СТРОКА($1:$123))>28)/(A2+123-СТРОКА($1:$123)))
Автор - AlexM
Дата добавления - 17.11.2023 в 16:23
bmv98rus Дата: Пятница, 17.11.2023, 18:48 | Сообщение № 52
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
AlexM, хозяин барин, но
Цитата AlexM, 15.11.2023 в 19:41, в сообщении № 41 ()
Ближайшая вторая или четвертая суббота
и
Цитата AlexM, 17.11.2023 в 16:23, в сообщении № 51 ()
Еще вариант 5-ой субботы
что-то тут меня настораживает. :D

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеAlexM, хозяин барин, но
Цитата AlexM, 15.11.2023 в 19:41, в сообщении № 41 ()
Ближайшая вторая или четвертая суббота
и
Цитата AlexM, 17.11.2023 в 16:23, в сообщении № 51 ()
Еще вариант 5-ой субботы
что-то тут меня настораживает. :D
Автор - bmv98rus
Дата добавления - 17.11.2023 в 18:48
AlexM Дата: Пятница, 17.11.2023, 19:23 | Сообщение № 53
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
bmv98rus, смотри сообщение №44


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениеbmv98rus, смотри сообщение №44
Автор - AlexM
Дата добавления - 17.11.2023 в 19:23
bmv98rus Дата: Пятница, 17.11.2023, 19:47 | Сообщение № 54
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
AlexM, да видел я его, зачинщик то ясен :-) .

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеAlexM, да видел я его, зачинщик то ясен :-) .
Автор - bmv98rus
Дата добавления - 17.11.2023 в 19:47
AlexM Дата: Понедельник, 20.11.2023, 17:33 | Сообщение № 55
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4493
Репутация: 1115 ±
Замечаний: 0% ±

Excel 2003
Код
=-ПРОСМОТР(;-(A2+123-СТРОКА($1:$123))/(ОСТАТ(A2+123-СТРОКА($1:$123);7)=0)/(ДЕНЬ(A2+123-СТРОКА($1:$123))>28))

на 5 символов короче предыдущего варианта


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Код
=-ПРОСМОТР(;-(A2+123-СТРОКА($1:$123))/(ОСТАТ(A2+123-СТРОКА($1:$123);7)=0)/(ДЕНЬ(A2+123-СТРОКА($1:$123))>28))

на 5 символов короче предыдущего варианта
Автор - AlexM
Дата добавления - 20.11.2023 в 17:33
Gustav Дата: Понедельник, 20.11.2023, 20:59 | Сообщение № 56
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2747
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Конкретно для "ближайшей 5-й субботы", может быть, даже чуть покороче вариант:
Код
=-ПРОСМОТР(-A2;-(ОТБР(A2/7)+20-СТРОКА($1:$22))*7/(ДЕНЬ((ОТБР(A2/7)+20-СТРОКА($1:$22))*7)>28))

Ну, а универсалка для любого "ближайшего 5-го в месяце дня недели", конечно, подлиннее (за счёт сумм в четырех местах и дополнительных скобок):
Код
=-ПРОСМОТР(-A2;-((ОТБР((A2+деньН-6)/7)+20-СТРОКА($1:$22))*7+деньН-6)/(ДЕНЬ((ОТБР((A2+деньН-6)/7)+20-СТРОКА($1:$22))*7+деньН-6)>28))

где параметр деньН может принимать значения от 1 (понедельник) до 7 (воскресенье).

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеКонкретно для "ближайшей 5-й субботы", может быть, даже чуть покороче вариант:
Код
=-ПРОСМОТР(-A2;-(ОТБР(A2/7)+20-СТРОКА($1:$22))*7/(ДЕНЬ((ОТБР(A2/7)+20-СТРОКА($1:$22))*7)>28))

Ну, а универсалка для любого "ближайшего 5-го в месяце дня недели", конечно, подлиннее (за счёт сумм в четырех местах и дополнительных скобок):
Код
=-ПРОСМОТР(-A2;-((ОТБР((A2+деньН-6)/7)+20-СТРОКА($1:$22))*7+деньН-6)/(ДЕНЬ((ОТБР((A2+деньН-6)/7)+20-СТРОКА($1:$22))*7+деньН-6)>28))

где параметр деньН может принимать значения от 1 (понедельник) до 7 (воскресенье).
Автор - Gustav
Дата добавления - 20.11.2023 в 20:59
Мир MS Excel » Работа и общение » Мозговой штурм » Ближайшая вторая или четвертая суббота (Формулы/Formulas)
  • Страница 3 из 3
  • «
  • 1
  • 2
  • 3
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!