Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Найти количество дней в указанных интервалах за вычетом конкретных дат, если они входят в эти интервалы.
 
#1
16.12.2021 11:14:44
Всем здравия и здравствовать.

Имею и дано:
1. интервалы дат
2. даты, которые не учитывать
______________________________
Желал бы найти: Суммарное количество дней во всех интервалах, но без учета дней из п.2

Ищу более короткое и изящное решение, нежели мной созданное (вложением).
Почти подходит функция ЧИСТРАБДНИ.МЕЖД если бы она могла не вычитать третий параметр "выходные".
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#2
16.12.2021 12:13:39
Сергей Евдокимов, Варианты в файле. Насчет изящности не уверен.
 
 
 
#3
16.12.2021 12:25:31
для 365/2021:
=LET(x;B2:D2;y;B3:D3;z;A7:A27;СУММ(y-x)-СУММ((z>=x)*(z<=y))+СЧЁТ(x))

На всякий случай, вариант без LET:
=СУММ(B3:D3-B2:D2)-СУММ((A7:A27>=B2:D2)*(A7:A27<=B3:D3))+СЧЁТ(B2:D2)

подходит для любой версии экселя, в 365/2021 можно нажать Enter, а в старых нужно Ctrl+Shift+Enter
Изменено: Бахтиёр - 16.12.2021 15:10:36
 
 
 
#4
16.12.2021 12:29:07
Бахтиёр, хрена, как там можно  8-0
Меня очень пугает, что наш ведмедь начнёт строгать с этими новыми приблудами  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#5
16.12.2021 12:50:39
Всем спасибо, господа. Здорово!

P/S/ новые вещи для освоения узрел: МУМНОЖ, Let.
Никогда еще не использовал. Надо как-то разбираться...
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#6
16.12.2021 13:17:00
memo, Бахтиёр
вас не затруднит пояснить, с чем связана (или чем порождается) необходимость этого действия: +СЧЁТ(B2:D2)
Почему и для чего мы его осуществляем ?
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#7
16.12.2021 13:49:19
Цитата
Jack Famous написал:
Меня очень пугает, что наш ведмедь начнёт строгать с этими новыми приблудами
))))
 
 
 
#8
16.12.2021 13:49:39
Цитата
Сергей:  +СЧЁТ(B2:D2)
Если период, допустим 04.12.2021 - 10.12.2021, чтобы посчитать количество дней в периоде, нужна формула: = 10.12.2021 - 04.12.2021 + 1
А у нас в В2:D2 начала периодов, в B3:D3 концы периодов, и формула была бы = B3:D3  - B2:D2 + 3, так как периодов - 3 (три).

Но, универсальней будет вместо 3 написать СЧЁТ(B2:D2), потому что, при применении в реальном файле и последующем изменении диапазонов внутри формулы, СЧЁТ(...) будет всегда возвращать актуальное число периодов, так как вместе с другими диапазонами мы будем менять и и диапазон внутри СЧЁТ().
А так, пришлось бы вручную считать это количество и написать вместо 3 допустим 6 или 10 или ещё какое-то число, исходя из реальных данных

То есть, на файле-примере можно вместо +СЧЁТ(B2:D2) можно просто написать +3, так как мы знаем, что периодов - 3.
Но, в последующем применении в реальных данных, пользователь, меняя диапазоны внутри формулы, мог бы не понять с первого раза смысл +3, и мог бы оставить его без изменения. Если в реальных данных количество периодов будет другим (не 3), то такая формула возвращала бы неправильный результат.
Изменено: Бахтиёр - 16.12.2021 14:00:31
 
 
 
#9
16.12.2021 14:05:38
Раз Бахтиёр уже все объяснил добавлю, что у меня в формулах вместо СЧЁТ() - СЧЁТЗ которая считает и нечисловые значения. От привязки к значениям можно вообще отказаться если применить функцию COLUMNS($B$1:$D$1).
И напоследок еще варианты:
Код
=SUM(SUMPRODUCT($B$3:$D$3-$B$2:$D$2)-MMULT(COUNTIFS($A$7:$A$27;">="&$B$2:$D$2;$A$7:$A$27;"<="&$B$3:$D$3);{1;1;1}))+COLUMNS($B$1:$D$1)

.... и массивный (с претензией на изящность) :)
Код
=SUM(($B$3:$D$3-$B$2:$D$2)-COUNTIFS($A$7:$A$27;">="&$B$2:$D$2;$A$7:$A$27;"<="&$B$3:$D$3);COLUMNS($B$1:$D$1))
Изменено: memo - 16.12.2021 23:52:20 (Небольшое дополнение)
 
 
 
#10
16.12.2021 15:20:39
, спасибо вам огромное. Очень уж подробно объяснили ))
И вам, , тоже.

P/S/ Не знаю почему, но у меня все время слетает имя форумчан, к которым обращаюсь посредством ссылки "имя". Косяк какой-то, похоже, технический.
Последнее обращение было к Бахтиёр и memo.
Изменено: Сергей Евдокимов - 16.12.2021 15:23:22
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#11
16.12.2021 15:52:36
Цитата
:  у меня все время слетает имя форумчан
Это временный глюк форума, обещали исправить.
 
 
 
#12
16.12.2021 16:05:56
Цитата
Бахтиёр: временный глюк форума, обещали исправить
исправили, но потом вернули, как было — видимо, платёжка не прошла  :D
Изменено: Jack Famous - 16.12.2021 16:06:10
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#13
19.12.2021 12:15:43
Как-то так
Код
=СУММПРОИЗВ(ЧИСТРАБДНИ.МЕЖД(+B2:D2;+B3:D3;"0000000";A7:A27))
 
 
 
#14
19.12.2021 13:55:38
Цитата
Сергей Евдокимов написал:  все время слетает имя форумчан
Создавайте сообщение в режиме ВВ-Code
 
 
 
#15
19.12.2021 15:36:55
Цитата
Андрей Лящук : =СУММПРОИЗВ(ЧИСТРАБДНИ.МЕЖД(+B2:D2;+B3:D3;"0000000";A7:A27))
Вот это да! Вот это круть! И все работает. Я в шоке.

Скажите, плиз, как вместо параметра выходных дней, вы засунули в функцию какие-то 8 чудо-нолей, еще и в кавычках. Что это такое и как это понимать ?
И что за плюсы перед диапазонами ? Без них не работает. Это всё какие-то фокусы ?
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#16
19.12.2021 16:14:06
Цитата
Сергей Евдокимов написал:
8 чудо-нолей
7 нолей, если быть точным). Они дают понять функции, что все дни в диапазоне рабочие. Можно заменить на единички, и дни будут рассматриваться как нерабочие. Можно чередовать, например так: "0100100", здесь вторник и пятница нерабочие дни, остальные рабочие.
Что касается плюсиков предполагаю, они нужны, чтобы функция принимала диапазон дат и выдавала массив серийных номеров.
Скрин
Изменено: memo - 19.12.2021 16:23:56
 
 
 
#17
19.12.2021 17:59:54
Цитата
memo: Они дают понять функции, что все дни в диапазоне рабочие. Можно заменить на единички...
Не знаю как максимально точно сформулировать свой вопрос.
Откуда возникает понимание, что можно так написать, так указать ?
Какие материалы для этого надо читать, где информация о том, что так возможно?

Ведь даже сам Excel, предоставляя выбор параметров...
...не дает и не показывает такой возможности

Как вы понимаете, что именно в таком виде и таким синтаксисом можно задать нужный параметр ?
Вероятно, навык, опыт и всё такое..., но ведь всё-равно первоисточник этих сведений где-то имеется. Вы же не родились с этими знаниями. Где их черпать ?
Изменено: Сергей Евдокимов - 19.12.2021 18:06:14
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#18
19.12.2021 18:11:44
Цитата
Сергей Евдокимов написал:
Где зреть сии ценные сведения ?
Как ни странно, но в справке
 
 
 
#19
19.12.2021 18:36:37
RAN, здорово. Спасибо. А про плюсы перед диапазонами ?
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
 
 
#20
19.12.2021 19:24:26
Сергей Евдокимов, а + перед диапазоном в формуле преобразует ссылку на диапазон в массив значений.
 
 
 
#21
19.12.2021 20:19:07
krosav4ig !
 
 
 
#22
19.12.2021 20:24:47
Цитата
написал:
krosav4ig
Он заскучал в миру. 😀
По вопросам из тем форума, личку не читаю.
 
 
 
#23
19.12.2021 20:47:24
Да его тут  господа М сгрызли!
 
 
 
#24
19.12.2021 21:15:08
Не его, а четверку 😀
По вопросам из тем форума, личку не читаю.
 
 
 
#25
19.12.2021 21:44:00
Цитата
RAN написал:
krosav4ig
ага :)
 
 
 
Страницы: 1
Наверх