Консультации Портала - Консультация #200406

Задать вопрос Консультации Пользователи Форум

Задать вопрос экспертам

Консультация № 200406

11.03.2021, 11:31

0.00 руб.

1 31 1

Компьютеры Программное обеспечение

Здравствуйте, уважаемые эксперты! Прошу вас ответить на следующий вопрос по Условному форматированию (УФ) в Excel.
Какие формулы в УФ применить к ячейкам с датами, чтобы даты, относящиеся, например, к текущей неделе, все имели зелёную заливку; к прошлой - красную, следующая неделя - жёлтая и т.п...
Но когда наступит следующая неделя (переход с воскресенья на понедельник), заливка ячеек должна измениться (т.к., к примеру зелёные даты "текущей" недели уже перейдут в "прошлую" неделю и должны стать красными).
Интересуют недели:
- позапрошлая и далее
- прошлая
- текущая
- следующая
- через неделю.
(xlsx приложил)

Спасибо!

Прикрепленные файлы:

7f1b0b6bb2ba245b73105a4d814796c7ae5f7ff4.xlsx

скачать (11.00 кБ)

Обсуждение

давно

youtuxmail

Посетитель
404864
10

11.03.2021, 18:47

общий

Привязки к году/месяцу нет. Важно, чтобы после смены недели менялся цвет заливки. Т.е. пока даты находятся в "рамках" текущей недели - заливка зелёная. И так все остальные заливки - при смене недели меняется заливка.
Т.е. "рамка" - 5 недель (позапрошлая, прошлая, текущая, следующая, через неделю).

Спасибо за быстрый ответ.

ЗЫ: и почему-то УФ помечает конец недели в субботу! А начало в воскресение.

давно

Megaloman

Модератор
137394
1850

11.03.2021, 18:53

общий

это ответ

Здравствуйте, youtuxmail!
[i][size=1]Внимание! Ответ изменён из-за обнаруженной ошибки![/size][/i]
Допустим, для ячейки B2 формула для условного форматирования

Код:


позапрошлая и далее
=И(ЕЧИСЛО(B2);B2<СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3))

прошлая
=И(СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3)<=B2;B2<=СЕГОДНЯ()-1-ДЕНЬНЕД(СЕГОДНЯ()-7;3))

текущая
=И(СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();3)<=B2;B2<=СЕГОДНЯ()+6-ДЕНЬНЕД(СЕГОДНЯ();3))

следующая
=И(СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3)<=B2;B2<=СЕГОДНЯ()+13-ДЕНЬНЕД(СЕГОДНЯ()+7;3))

через неделю
=И(СЕГОДНЯ()+14-ДЕНЬНЕД(СЕГОДНЯ()+14;3)<=B2;B2<=СЕГОДНЯ()+20-ДЕНЬНЕД(СЕГОДНЯ()+14;3))

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

Для Вашего примера

Для более легкого контроля работы форматирования даты подряд:

Пример применения форматирования для каждого условия по отдельности:

Прикрепил Ваш файл с моим форматированием. ya210312.xlsx (16.1 кб)

И еще совет: при экспериментах с форматированием в сторонке от массовых данных сделайте ячейку с тестовыми данными и её форматируйте, а затем её формат специальной вставкой распространяйте на боевой диапазон, например:

В чём радость? Вы меняете форматирование только одной ячейки, а не диапазона.

Большой мастер и энтузиаст!

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Megaloman

Модератор
137394
1850

11.03.2021, 18:56

общий

11.03.2021, 19:32

В какой функции? При использовании функции =ДЕНЬНЕД подсветилась подсказка:

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Megaloman

Модератор
137394
1850

11.03.2021, 19:15

общий

Адресаты:

Я Вам отослал ответ, не хочу его менять. Я подразумевал в нём, что Вы форматируете ячейки или с датами, или пустые.
Если там возможны и текстовки, то лучше написать:

Код:

через неделю
=И(ЕЧИСЛО(J2);J2>СЕГОДНЯ()+7+ДЕНЬНЕД(СЕГОДНЯ()+7;3))

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

youtuxmail

Посетитель
404864
10

11.03.2021, 21:20

общий

(К сожалению вставить скрины в тело сообщения не получилось через "Вставить тег изображения"

Приложил скрины).....

[u]По отдельности формулы отрабатывают так:[/u]
1. "ПОЗАПРОШЛАЯ И ДАЛЕЕ"
=И(ЕЧИСЛО(B2);B2<СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3))
- формула работает отлично! (рисунок 1).

https://ibb.co/1skQV8k
--------------

2. "ПРОШЛАЯ"
=И(СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3)<=B2;B2<=СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3))
- формула залила правильно с 01.03 по 07.03. НО захватила с 08.03 по 14.03 (т.е. "текущую") и ещё 15.03 (Пн "следующей") (рисунок 2).

https://ibb.co/4Ps72fk
--------------

3. "ТЕКУЩАЯ"
=И(СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();3)<=B2;B2<=СЕГОДНЯ()+ДЕНЬНЕД(СЕГОДНЯ();3))
- формула залила правильно с 08.03 по 14.03. НО захватила ещё 15.03 и 16.03 (Пн и Вт "следующей") (рисунок 3).

https://ibb.co/P9kWPXk
--------------

4. "СЛЕДУЮЩАЯ"
=И(СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3)<=B2;B2<=СЕГОДНЯ()+7+ДЕНЬНЕД(СЕГОДНЯ()+7;3))
- формула залила правильно с 15.03 по 21.03. НО захватила ещё 22.03 и 23.03 (Пн и Вт "через неделю") (рисунок 4).

https://ibb.co/3mqrNs0
--------------

5. "ЧЕРЕЗ НЕДЕЛЮ"
=B2>СЕГОДНЯ()+7+ДЕНЬНЕД(СЕГОДНЯ()+7;3)
- формула пропустила 22.03 и 23.03 (Пн и Вт "через неделю") и унеслась вдаль ) (рисунок 5).

Можно ограничить только "через неделю"?
https://ibb.co/q5TqVH8

Прикрепленные файлы:

ea442aa8770e6fc3a4c6fc3c045553b1.jpg

скачать (1.85 МБ)

давно

youtuxmail

Посетитель
404864
10

11.03.2021, 21:24

общий

И xlsx приложил.

Прикрепленные файлы:

704f01418ae6e8b82ea8dfb5f8535ffe.xlsx

скачать (12.00 кБ)

давно

Megaloman

Модератор
137394
1850

11.03.2021, 22:44

общий

11.03.2021, 23:15

Адресаты:

И открыл, и ничего не менял и не увидел повода к рёву

Всё правильно! Чтобы не напрягаться, вот скриншот с датами по порядку:

Правый столбец отформатирован в соответствии с пожеланием:

В формуле для форматирования через неделю заменить:

Код:

=И(СЕГОДНЯ()+14-ДЕНЬНЕД(СЕГОДНЯ()+14;3)<=B2;B2<=СЕГОДНЯ()+14+ДЕНЬНЕД(СЕГОДНЯ()+14;3))

Ранее сделал, как понимал логику расцветки.
Форматирование работает правильно. Не знаю, как Вам удалось извратить

результат. Подозреваю, меняли дату на компьютере, а данные в таблице не меняли. В этом случае надо пересчитать формулы принудительно

Чтобы принудительно пересчитать всю книгу или рабочий лист, используйте горячие клавиши Excel: F9 – пересчёт всех листов во всех открытых книгах Shift+F9 – пересчёт активного листа Ctrl+Alt+F9 – пересчёт всех листов во всех открытых книгах независимо от того, вносились ли в них изменения с момента последнего пересчёта

Либо сохранить книгу, закрыть, открыть заново.
ya21031122-out2.xlsx (15.9 кб)
Здесь на Вашем листе ничего не менял. На другом листе - пример с датами по порядку для лёгкого контроля работы форматирования.

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

youtuxmail

Посетитель
404864
10

12.03.2021, 07:00

общий

12.03.2021, 07:20

Спасибо, что помогаете, Megaloman! Сам я никогда до всего такого не додумался бы...

----------------

- вот вот

Поэтому я и написал в самом начале:

Если тестировать КАЖДУЮ ОТДЕЛЬНО формулу, то видно всё, что я в предыдущем посте наскриншотил.
Сделал скрины и с Вашим листом. (см. в прицепе)
(подписи над каждым рисунком).
----------------
0 - Открыл. Не менял. Все правила на месте. Визуально всё норм.

----

1 - только ПОЗАПРОШЛАЯ И ДАЛЕЕ. Всё хорошо!

----

2 - только ПРОШЛАЯ неделя + залила ещё и ТЕКУЩАЯ +Пн СЛЕДУЮЩАЯ

----

3 - только ТЕКУЩАЯ + залила Пн и Вт СЛЕДУЮЩАЯ

----

4 - только СЛЕДУЮЩАЯ +залила Пн и Вт ЧЕРЕЗ НЕДЕЛЮ

----

5 - только ЧЕРЕЗ НЕДЕЛЮ (новая). всё хорошо!

----------------
Задал я Вам задачку

...

Прикрепленные файлы:

568c6f8fe848942aec875b4c402056e3.jpg

скачать (3.97 МБ)

давно

Megaloman

Модератор
137394
1850

12.03.2021, 08:10

общий

12.03.2021, 08:13

Адресаты:

Давайте сначала. Вы задали вопрос, я предложил решение и предоставил его Вам. Вы:

0 - Открыл. Не менял. Все правила на месте. Визуально всё норм

То есть [b]моё[/b] решение, [b]моё[/b] форматирование работает. Не меняя [b]моё[/b] форматирование, меняйте данные в ячейках - всё работает правильно?
Далее, Вы экспериментируете

. Это здорово!

. Но это уже не совсем моё, тут надо искать [b]Ваши[/b] ошибки. Например, чисто визуально на Ваших скриншотах, Вы пишите форматирование для разных условий, но при этом на эти разные условия Вы прописываете одинаковые цвета: в чём тогда претензия?.
Я не очень понимаю, каким алгоритмом Вы руководствуетесь при Ваших экспериментах, давайте формулируйте словами, что-то [b]одно[/b] не получается - давайте разберём. Не будем всё сразу валить в кучу.

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

youtuxmail

Посетитель
404864
10

12.03.2021, 08:21

общий

Извиняюсь, что так непонятно объясняю....

Итак... Тот файл, что Вы скинули последний, я открыл и никакие данные в ячейках не трогал. Я хотел увидеть, как работает каждая формула отдельно (эта формула для этой недели, эта - для другой и т.д.). Например, если мне в будущем понадобиться заливать только ПРОШЛУЮ неделю, я просто удалю формулы для других недель и всё. НО вот тут-то и выяснилось, что по-отдельности некоторые формулы заливают почему-то лишние дни, а не в границах свое недели.

К примеру, если бы понадобилась одна формула только для ПРОШЛОЙ недели, она бы отличалась от той, что сейчас?

давно

Megaloman

Модератор
137394
1850

12.03.2021, 08:33

общий

12.03.2021, 08:39

Конечно нет. Только пусть в условном форматировании при этом будет эта одна единственная формула. Если хотите комбинировать несколько условий, прописывайте им разные форматы, например, цвета.
На Ваших скриншотах я вижу по два условия с одинаковыми цветами.
Я делаю так: беру одну ячейку, форматирую ее, убеждаюсь, что результат адекватный моим устремлениям,
ctrl/c
выделяю необходимый диапазон ячеек
Спец вставка - форматы.

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

youtuxmail

Посетитель
404864
10

12.03.2021, 08:48

общий

12.03.2021, 09:43

Хорошо.) Пойдём так....
Предположим, что требуется залить синим только даты прошлой недели. Возьмём только формулу для ПРОШЛОЙ недели:
=И(СЕГОДНЯ()-7-ДЕНЬНЕД(СЕГОДНЯ()-7;3)<=B2;B2<=СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ()+7;3))

Как я понимаю, она должна залить только ячейки прошедшей недели. На данный момент "прошедшая" неделя - это даты с 01.03 по 07.03. Их и должна залить формула. Она их и заливает.

Но она заливает и с 08.03 по 14.03, и 15.03 (т.е. и даты текущей недели, и даже Пн следующей), что не нужно.
Вопрос: Как использовать эту формулу, чтобы залить ТОЛЬКО даты прошлой недели и не трогать никакие другие даты?

Вот так я и рассуждал по каждой формуле - правильно ли работает она в том диапазоне дат, для которого писалась?

давно

Megaloman

Модератор
137394
1850

12.03.2021, 11:12

общий

12.03.2021, 11:26

Адресаты:

Виноват! Мои извинения! В формуле для определения даты конца недели вкралась ошибка, и, по трагической случайности, я её писал в четверг, когда она отрабатывала правильно.

. Я изменил ответ, перезалил файл, посмотрите, что получилось.

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Megaloman

Модератор
137394
1850

12.03.2021, 12:28

общий

12.03.2021, 12:29

Адресаты:

[offtop]У кого то из нас бифуркация: в почте в извещении я вижу: "youtuxmail (Посетитель) положительно оценил[b]а[/b] Ваше сообщение", а в постах: "Вот так я и рассуждал". Надо быть последовательным: или исправить регистрационные данные, либо изменить окончания глаголов

[/offtop]

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

youtuxmail

Посетитель
404864
10

12.03.2021, 16:28

общий

12.03.2021, 16:33

- эээээ... я вообще не видел при регистрации каких-то эм/жо граф.
А что там движок сайта придумывает, мне неведомо

....

- уж не знаю из-за чего путаница )))

UPD: щас в Личные данные заглянул: Пол: женский

!! При регистрации про пол вопросов не помню! И почему именно женский выставился

....

давно

Алексеев Владимир Николаевич

Мастер-Эксперт
259041
7459

12.03.2021, 16:30

общий

Адресаты:

Увидев Вашу сложную формулу вычисления номера недели в году, я вспомнил о наличии изящной команды в PowerShell, в которую вводишь любую дату и получаешь N недели в году и всё прочее, что нужно от операций с время/датами.
В Консоли всё легко: команда
"Для даты " + (Get-Date '1.1.2021' -UFormat "%d %b %Y %A N недели в году = %V")
возвратила: Для даты 01 янв 2021 пятница N недели в году = 1

Я обрадовался, хотел предложить Вам в помощь кмд вызова PowerShell-строки, но решил проверить в динамике, написал скрипт с циклом 40 дней. Оказалось, PowerShell инкрементит N недели не по понедельникам, а по прошествии каждых 7 суток от начала года. Я поискал в инете как исправить? Но увидел варианты не проще Вашего, тк в разных странах неделя начинается не всегда по понедельникам.

Прикрепленные файлы:

39ff7252746472780f3c545ee4d901e9.png

скачать (5.00 кБ)

давно

Megaloman

Модератор
137394
1850

12.03.2021, 16:59

общий

12.03.2021, 18:32

Адресаты:

Цитата: Библия. Притчи 16 стих 25

Есть пути, которые кажутся человеку прямыми, но конец их – путь к смерти.

В Excel есть аналогичная функция - [i][b]НОМНЕДЕЛИ[/b][/i]: берёшь дату, получаешь номер недели в году. И я сначала на неё клюнул, но построить логику работы формулы, корректно работающей на границе декабрь-январь на этой функции мне показалось сложным. Вот если бы можно было взять номер недели от сотворения мира ... Для Excel это 01.01.1900
Если бы youtuxmail позволил использовать макрос, тогда можно было бы написать свою функцию, которая до минимума сократила бы длину формулы в форматировании.
Возможно из любопытства это проделаю.
Возможно из мазохизма попытаюсь всё же применить [i][b]НОМНЕДЕЛИ[/b][/i]
И вообще, номер недели в решении я никак не вычисляю.

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Megaloman

Модератор
137394
1850

12.03.2021, 17:02

общий

Адресаты:

Гендер в личных данных не столь важен - мне интереснее результат моего ответа: Вы достигли поставленных целей?

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

youtuxmail

Посетитель
404864
10

12.03.2021, 17:25

общий

Да, да.. Только что протестил формулы. Всё работает как надо!
Отличная работа!!!

ОГРОМНОЕ спасибо за Ваш труд и знания.

- любимая книга.

давно

Megaloman

Модератор
137394
1850

12.03.2021, 18:34

общий

Адресаты:

Обращайтесь! Диалог с человеком, который хочет разобраться, полезен обоим.

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Megaloman

Модератор
137394
1850

12.03.2021, 21:06

общий

[b]Уважаемый Алексеев Владимир Николаевич![/b] Позвольте в очередной раз выразить Вам моё восхищение и совершеннейшее почтение!

Общение с Вами побуждает к плодотворным размышлениям.
Итак, как я писал,
- И вообще, номер недели в решении я никак не вычисляю.
- В Excel есть аналогичная функция - НОМНЕДЕЛИ: берёшь дату, получаешь номер недели в году. И я сначала на неё клюнул, но построить логику работы формулы, корректно работающей на границе декабрь-январь на этой функции мне показалось сложным. Вот если бы можно было взять номер недели от сотворения мира ...
«И тут Остапа понесло…»: а кто мне мешает посчитать абсолютный номер недели относительно 01.01.1900 (числовое значение = 1), ведь даты в Excel - это числа по порядку. И, оказалось, формулы существенно подсократились и капитально унифицировались: всего-то надо посчитать разницу между абсолютными номерами недель сегодня и заданной датой и сопоставить эту разницу нужному интервалу.
Работает! Картиночка с результатом и формулами ниже:

ya21031221.xlsx (16.7 кб)

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Алексеев Владимир Николаевич

Мастер-Эксперт
259041
7459

13.03.2021, 16:29

общий

Адресаты:

Вы писали : "номер недели в решении я никак не вычисляю" - а я как увидел текущую задачу (ещё без Вашего Ответа), то сразу подумал, будто НомерНедели - ключевой параметр в Алгоритме решения. Потому что именно разница номеров недели м-ду текущей неделей и исследуемой как раз и нужна Автору Вопроса для назначения цвета исследуемым ячеям.
Да Вы и сами заметили "всего-то надо посчитать разницу между абсолютными номерами недель сегодня и заданной датой и сопоставить эту разницу нужному интервалу."

"взять номер недели от сотворения мира" + "абсолютный номер недели относительно 01.01.1900" - я подозреваю, Вы немного ошиблись. За начало компьютерной эры принята дата 1янв1970 . В системном реестре Windows есть много параметров, где ДатаВремя хранится в формате числа секунд, прошедших от 1янв1970 0:00 . Например, ДатаУстановки ОперСистемы вписана в HKLM\Software\Microsoft\Windows NT\CurrentVersion\InstallDate в виде числа типа 1477716376 . Для пересчёта в осмысливаемую дату надо к этому числу секунд прибавить тчк отсчёта 1янв1970 и смещение от Гринвича (для меня это 10 час * 3600 сек/час).

Для UNIX-систем за начало отсчёта времени тоже принято 1янв1970, 0 часов 0 минут (см "Форматы времени и даты в PowerShell" Ссылка )

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

"В Excel есть аналогичная функция - НОМНЕДЕЛИ" - я проверил : эта функция работает правильно, но только для Америки и англо-язычных. В ней номер недели инкрементится в Воскресенье - первый день недели в США. В ПанелиУправления \ "Язык и РегиональныеСтандарты" на вкладке Дата задано ПервыйДеньНедели = Понедельник. Я менял эту настройку на др дни; в HKCU\Control Panel\International\ сис-реестре я менял iFirstDayOfWeek = 0 ==> 1 и iFirstWeekOfYear = 0 ==> 1 … Но упрямый Excel игнорит системные настройки, а своих настроек недели у него нет либо они так глубоко, что я не докопался.

Так что Ваше Решение - гениально и макси-оптимально! С чем я Вас и поздравляю! Вы не подсчитывали, сколько трудов Вы вложили в своё Решение? Я прикидываю, не менее 20 часов?

давно

Megaloman

Модератор
137394
1850

13.03.2021, 19:50

общий

13.03.2021, 19:55

Цитата: Я

"взять номер недели от сотворения мира"

- я утрирую. Смысл в том, чтобы посчитать номер дня или недели от какой-то заведомо далёкой даты, которая гарантированно не встретится в задаче. Насчёт первого дня недели, настройка в самой функции Excel НОМНЕДЕЛИ

Цитата: Справочная система в функции НОМНЕДЕЛИ Excel

Описание
Возвращает номер недели для определенной даты. Например, неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.

Для этой функции могут использоваться две разные системы.
Система 1 Неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.
Система 2 Неделя, на которую приходится первый четверг года, считается первой неделей, и для нее возвращается число 1.

Синтаксис
НОМНЕДЕЛИ(дата_в_числовом_формате;[тип])Аргументы (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.) функции НОМНЕДЕЛИ описаны ниже.

Дата_в_числовом_формате Обязательный. Дата, по которой определяется неделя. Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. следует воспользоваться выражением ДАТА(2008;5;23). Если даты вводятся как текст, это может привести к возникновению проблем.
Тип Необязательный. Число, которое определяет, с какого дня начинается неделя. Значение по умолчанию — 1. Тип Начало недели Система
1 или опущен Воскресенье 1
2 Понедельник 1
11 Понедельник 1
12 Вторник 1
13 Среда 1
14 Четверг 1
15 Пятница 1
16 Суббота 1
17 Воскресенье 1
21 Понедельник 2

Замечание
В приложении Microsoft Excel даты хранятся в виде порядковых номеров, над которыми могут выполняться вычисления. По умолчанию дата 1 января 1900 г. имеет номер 1, а 1 января 2008 г. — 39 448, поскольку это 39 448-ой день, так как интервал между этими датами составляет 39 448 дней.
Если значение "дата_в_числовом_формате" находится вне диапазона для текущего базового значения даты, возвращается ошибка #ЧИСЛО!.
Если значение "тип" не входит в диапазон, указанный в таблице выше, возвращается ошибка #ЧИСЛО!.

Я привык, что понедельник - первый день недели, но не понял разницы между 2 и 11 типом (оба в Системе 1).

Кстати, что Excel, что VBS определяет абсолютный номер дня относительно 1 января 1900 г. одинаково, например, в ячейке Excel при форматировании ячейки с датой 31.12.2020
в число получим 44196, то же число выдаст VBS-скрипт:

Код:

a = DateValue("31.12.2020")
MsgBox CStr(a) + vbCrLf + CStr(CLng(a))

[Size=1][Offtop]

Я не раз писал, что я лентяй и верхогляд. Если время считать как в хоккее, то 2-3 часа, не более, причем ощутимая доля - переписка и оформление ответа. Другое дело, я получаю задачу, возникает идея, как решать, не прокатывает, бросаю её и подсознание придумывает новую идею и я её щупаю уже руками.[/Offtop][/Size]

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Алексеев Владимир Николаевич

Мастер-Эксперт
259041
7459

14.03.2021, 14:09

общий

Адресаты:

Вы писали: "что Excel, что VBS определяет абсолютный номер дня относительно 1 января 1900 г. одинаково" - меня удивило Ваше упорство про 1900 г, и я решил проверить, потому что я много лет полагал и убеждался, будто отсчёт комп-эры ведётся от 1970г. Но Вы правы: это 2й стандарт комп-эры! Я написал скрипты (нужны ли они Вам?) и убедился: VBS , VBA и Excel отсчитывают дни от 29дек1899 ! И значит, VBS - это детище MsOffice-разработчиков, а не ОперСистемщиков. Буду знать! Спасибо Вам за просвещение!

А для исправления бага в PowerShell в рунете ничего найти не удалось. Пришлось запрашивать на английском по фразе:
PowerShell How to change number of week in a year on Mondays? (Как изменять нумерацию дней недели в году по понедельникам?).
Я нашёл 2 серьёзных источника : Ссылка1 и Ссылка2. Читаю…

давно

Megaloman

Модератор
137394
1850

14.03.2021, 16:42

общий

Адресаты:

Позвольте не согласиться!
Вот распечатка, как преобразуется число в даты в Excel и VBA (=vbs)

Код:

S=""
S=S + CStr(1) + vbTab + DataVBA(1) + vbCrLf
S=S + CStr(2) + vbTab + DataVBA(2) + vbCrLf
S=S + CStr(3) + vbTab + DataVBA(3) + vbCrLf
S=S + CStr(4) + vbTab + DataVBA(4) + vbCrLf

S=S + CStr(58) + vbTab + DataVBA(58) + vbCrLf
S=S + CStr(59) + vbTab + DataVBA(59) + vbCrLf
S=S + CStr(60) + vbTab + DataVBA(60) + vbCrLf
S=S + CStr(61) + vbTab + DataVBA(61) + vbCrLf
S=S + CStr(62) + vbTab + DataVBA(62) + vbCrLf
S=S + CStr(63) + vbTab + DataVBA(63)
MsgBox S
' ==================================
Function DataVBA(i)
    DataVBA = CStr(CDate(i))
End Function

В Excel идёт отсчет от 01.01.1900, а в VBA/VBS от 31.12.1899
Разница в 1 день даты сохраняется до 60 дня (29.02.1900 в Excel и 28.02.1900 в VBA/VBS), затем оба способа совпадают от даты 01.03.1900 и до сегодняшних дней. Не знаю, можно ли когда-нибудь об это споткнуться

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Алексеев Владимир Николаевич

Мастер-Эксперт
259041
7459

15.03.2021, 11:07

общий

Адресаты:

Вы писали : "В Excel идёт отсчет от 01.01.1900, а в VBA/VBS от 31.12.1899" - про Excel спорить не буду, там свои настройки коррекции-подтасовки, отличающиеся от системных зачем-то…
А в VBS я проверил ещё раз и убедился: Нуль отсчёта - 30 дек 1899. Это для НЕдокументированной команды cLng(Дата), которой я ранее никогда не пользовался, потому что для вычисления разницы м-ду 2мя величинами в формате ДатаВремя существует официальная надёжная команда
DateDiff("ЕдИзмерения",Дата1,Дата2)

Откуда Вы вообще взяли вычислитель cLng(Дата) - предмет нашего спора? Я подозреваю, его случайно или по ошибке обнаружил какой-то умелец, поделился с др программистами находкой, и на прог-сайтах породилось расхожее понятие "отсчет". А Вы пользуетесь этим сомнительным средством для вычисления Даты, не проверив его.

Я примерно пол-жизни работал в цехах КИПиАвтоматики. Мне, как самому добросовестному зануде, не верящему авторитетам, поручали поверку новых и отремонтированных и измерительных приборов (КИП - КонтрольноИзмерительные Приборы). Поверка - сличение показаний поверяемого прибора с образцовым, обычно состоит из 3х этапов : Проверка/коррекция Нуля, Коррекция Диапазона, и проверка линейности выборочно по характерным оцифрованным точкам. Нельзя отдавать НЕповеренный прибор в горячие цеха, чтоб не множить проблемы работников с вредными условиями труда.
Также нельзя использовать сомнительную команду в скриптах для вычисления даты и проч операций, пока не сделана поверка этой кмд.

Так, команда PowerShell (см ~5 постов выше) Get-Date 'дата' -UFormat %V прошла успешно мой первый этап поверки на 0-точке : для 1янв она правильно возвратила N недели = 1. Но на 2м этапе оказалось, кмд врёт, и ею нельзя пользоваться. Я заметил это на прошлой неделе, тк живу в деградирующей стране. А программисты развитых и развивающихся стран заметили этот глюк в 2010г (см "Powershell - week of the year" Ссылка)

Для поверки Вашей кмд cLng(Дата) я тоже написал поверочный скрипт, прилагаю его ниже. Я не буду засорять эту страницу кодом, кому интересно, смогут открыть скрипт в ТекстРедакторе (я пишу в простом Блокноте) и почитать мои подробные комменты.

Прикрепляю скрин. На нём Вы можете сопоставить результаты Здравому смыслу и официальной, проверенной команде DateDiff().

Как видите, нулевая точка отсчёта для поверяемого вычислителя дней cLng(Дата) - это 30 декабря 1899г. Если поддаться Вашему авторитету "отсчет … в VBA/VBS от 31.12.1899" - это всё равно, что выпустить из поверки в эксплуатацию глубиномер со смещённым нулём на 1 м. На производстве такое легкомыслие не прощают, чёрная метка разгильдяя приклеится на много лет.

"Не знаю, можно ли когда-нибудь об это споткнуться" - Вы правы, за 10 лет программирования кмд cLng(Дата) не понадобилась мне ни разу изза наличия удобной альтернативы DateDiff(). И вряд ли понадобится отсчёт от 1899г. Поэтому я тоже не вижу смысла продолжать этот спор и развивать тему.

Прикрепленные файлы:

970d741e9b99458aee24c146772793a7.zip

скачать (927 байт)

давно

Megaloman

Модератор
137394
1850

15.03.2021, 15:03

общий

Адресаты:

Есть Excel, в нём инструмент создания макросов - VBA, он там задокументирован и в справочной системе для функций преобразования типов данных видим:

Код:

The function name determines the return type as shown in the following:

Function Return Type 	Range for expression argument 
CBool	 Boolean	 Any valid string or numeric expression. 
CByte	 Byte		 0 to 255. 
CCur	 Currency	 -922,337,203,685,477.5808 to 922,337,203,685,477.5807. 
CDate	 Date		 Any valid date expression. 
CDbl	 Double 	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. 
CDec	 Decimal 	+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is +/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001. 
CInt	 Integer 	-32,768 to 32,767; fractions are rounded. 
CLng	 Long 		-2,147,483,648 to 2,147,483,647; fractions are rounded. 
CLngLng	 LongLong 	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807; fractions are rounded. (Valid on 64-bit platforms only.) 
CLngPtr	 LongPtr 	-2,147,483,648 to 2,147,483,647 on 32-bit systems, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems; fractions are rounded for 32-bit and 64-bit systems.  
CSng	 Single 	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values. 
CStr	 String 	Returns for CStr depend on the expression argument. 
CVar	 Variant 	Same range as Double for numerics. Same range as String for non-numerics.

Это не чьи-либо выдумки, это официальный источник.
У нас с Вами разговор о разном.
Я уже цитировал из документации Excel: "В приложении Microsoft Excel даты хранятся в виде порядковых номеров, над которыми могут выполняться вычисления. Я показал, что в Excel день с номером 1 это 01.01.1900, а в VBA/VBS 31.12.1899 и что начиная с 60 дня эта разница устранена, что позволяет как в Excel, так и в VBA/VBS работать одинаково с номером дня относительно начала отсчета. Всё!

[Size=1]~~И не вижу повода к раздражительности и агрессии.~~[/Size]

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Megaloman

Модератор
137394
1850

15.03.2021, 15:28

общий

15.03.2021, 15:38

Адресаты:

[OffTop]Мы родом из страны советов

Позволю поэтому, совет: я использую в Excel средство создания/редактирования макросов в качестве редактора при создании vbs, это позволяет контролировать синтаксис, смотреть справочник по функциям, редактор немного улучшает форматирование кода, его читаемость, так как приводит написание функций к единому стандарту, позволяет легче отлаживать код. Редко, очень редко, приходится что-то допилить потом, в файле VBS. [/OffTop]

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Алексеев Владимир Николаевич

Мастер-Эксперт
259041
7459

16.03.2021, 14:05

общий

Адресаты:

Спасибо Вам за совет! Я тоже любил Excel в 2003г, когда корпорация Microsoft писала свои лучшие продукты WinXP и OfficeXP с макси-уважением к пользователям. Примерно 10 лет я был даже лучшим Excel-спецом в фирмах, где я работал. Но сейчас продукты Microsoft стали навязывать нам избыточные навороты и ограничивать полезные инструменты. Работать в Маткаде стало гораздо приятнее, чем в Excel , работать в html-формате - гораздо приятнее, чем в Word .

Вы - гений Excel , Ваша фраза "начиная с 60 дня эта разница устранена, что позволяет как в Excel, так и в VBA/VBS" означает, что Excel для Вас - образцовый прибор, с которым Вы сравниваете показания Ваших скриптов. Предложенный Вами РедакторКода хорош для отладки небольших программ.

Но у меня - другая ситуация: я вообще не люблю и редко загружаю тяжёлые продукты (Excel , Хром-браузер, антивирус…). Но мои скрипты очень большие и делают громадную работу в Консоли с прокруткой экрана. Например Optimizer.vbs 374 кБ содержит 5600 строк кода для очистки сохранённых html-документов от JavaScript , фреймов, РекламБаннеров, избыточных мусор-тэгов с глубокими вложениями… Объём очищеных докум-файлов уменьшается в 10…100 раз, избавляя меня от покупки дорого-стоящих жёстких дисков в Б колич-ве.

Спец-РедакторКода имеет моно-ширинный мелкий шрифт. При увеличении размера шрифта увеличивается меж-строчный итервал и уменьшается макси-ширина строки в символах. Резко увеличивется число страниц визуально-раздутого кода.
А в Блокноте я задаю самый легко-читаемый шрифт Arial с переменной и компактной шириной кегля, без дурацких засечек, но с макси-плотностью текста. Главное: не пользовать одно-буквенные имена переменных (i , n , x…). И когда var-имена уникальны, то Блокнот-поиск мигом находит заданные мною фразы и блоки горячими клав-сочетаниями, чтоб сделать прогу проще и надёжнее.

давно

Megaloman

Модератор
137394
1850

16.03.2021, 16:24

общий

16.03.2021, 16:27

Адресаты:

[offtop]Вы очень преувеличиваете мои возможности - я всего лишь немного продвинутый пользователь, способный решать задачи в Excel, в том числе с привлечением автозаписи макросов, поиска подсказок в инете. Не более того.

[/offtop]

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

Форма ответа

Отправка постов/ответов доступна только зарегистрированным и подтвержденным пользователям.

Если Вы уже зарегистрированы на Портале - войдите в систему, если Вы еще не регистрировались - пройдите простую процедуру регистрации.