Страницы: 1
RSS
Количество дней недели между двумя датами
 
Здравствуйте Уважаемые! Помогите пожалуйста! Есть база данных учеников школы. там есть дни недели когда у ученика будут проходить занятия и в какое время, также есть дата когда начинаются занятия и когда заканчиваются. В ячейке "плановое кол-во занятий" (выделил желтой заливкой) нужно забить логику чтобы в ней автоматически просматривалась строка 7 в интервале столбцов от С до I, и если в этом интервале появляется значение времени, то брать день недели соответствующий этому столбцу и рассчитывалось сколько дней (допустим в моем примере вторников и четвергов) будет за расчетный период от даты начала до даты окончания занятий. Сразу дополнительный вопрос задам, как вычитать праздничные дни когда занятий проходить не будет.

Нашел в архиве форума подобную проблему,но адаптировать под свою таблицу не получается. Вот ссылка https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=18851
 
Вариант:
Изменено: _Igor_61 - 26.04.2019 17:14:25
 
А допустим вот такую формулу (см.ниже) нельзя как то модернизировать чтобы она проверяла 7 строку, столбцы от C до I и если в каких-нибудь из этих ячеек не пусто, то  считала эти дни недели? вот в моем случае в двух ячейках этого диапазона не пусто, что соответствует Вт и Чт.
Код
=ОТБР((N7-J7)/7)+(ИЛИ(ДЕНЬНЕД(J7;2)=2;ДЕНЬНЕД(N7;2)=2))

или может другой вариант какой нибудь есть, но так чтобы решалось только одной формулой, без нагромождения и создания дополнительного листа....??? Думаю можно еще как то с функцией "ИНДЕКС" решить эту задачу но как не знаю. Помогите пожалуйста.
 
Можно вот так, без дополнительного листа
Код
=СУММ(--(ЕСЛИ((--(СТРОКА(A:A)<=L7)*--(СТРОКА(A:A)>=J7));ДЕНЬНЕД(СТРОКА(A:A));0)=ЕСЛИ(--(C7:I7<>"");{1;2;3;4;5;6;7};"НЕТ")))

формула массива
Изменено: Blood81 - 26.04.2019 18:44:35
 
Почему-то формула из #4 показывает на день меньше...
Цитата
Stounv17 написал:
без нагромождения и создания дополнительного листа
Зато считает правильно, а лист скрыть можно, если страшно на него смотреть :)
 
Цитата
Blood81 написал: Можно вот так...
Уважаемый Blood81 ! Действительно, в этой формуле такое впечатление что в функции "ДЕНЬНЕД" стоит тип=1, и начало недели с Вс а не с понедельника. Проверил на апреле. Дней недели должно быть Пн=5 ВТ=5 Ср=4 Чт=4 Пт=4 Сб=4 Вс=4, а в формуле которую вы предложили получается Пн=4 Вт=5 Ср=5 Чт=4 Пт=4 Сб=4 Вс=4
как раз сдвиг вправо на 1 получился. Подскажите пожалуйста отредактированную формулу. Спасибо.  
 
Цитата
Stounv17 написал:
ДЕНЬНЕД(СТРОКА(A:A))
в данной части формулы не задан тип дат, для вашего примера необходимо в данной части формула проставить тип 2

=СУММ(--(ЕСЛИ((--(СТРОКА(A:A)<=L7)*--(СТРОКА(A:A)>=J7));ДЕНЬНЕД(СТРОКА(A:A);2);0)=ЕСЛИ(--(C7:I7<>"");{1;2;3;4;5;6;7};"НЕТ")))

 
Отлично! Спасибо боооооольшое! Очень сильно помогли за сутки!
Админам-тему можно закрывать! Имеем решение двумя разными подходами! Тут уже у кого какой случай...., кому так подойдет, кому эдак ! Еще раз спасибо парни!
 
Все таки еще один вопрос....! А можно к этой формуле добавить что если вдруг считаем вторники и вторник выпадает на 9 мая то этот день в расчет не идет. Так сказать указать даты которые не будут участвовать в расчете допустим через запятую?????=) последний вопрос=)
 
Тогда вам необходимо использовать вариант предложенный _Igor_61, в #3  сообщении. Откройте лист "Календарь" и перенесите на него все необходимые праздничные дни, тем более Вам _Igor_61, на этом листе сделал большую пометку!  
 
Здесь хочется немного добавить, во первых вы наверное заметили что формула массива работает, но вычисляет довольно медленно - это происходит из за использования в массиве целого столбца СТРОКА(A:A) - формула выглядит лаконично, но работает не быстро. Если ограничить эксель в используемых датах то можно сильно повысить скорость вычисления. Например используем минимальные и максимальные значения дат как границы вычисляемого диапазона вот так: СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))) этот прием позволяет задать произвольный дипазон только из используемых дат.

и напоследок вариант с удалением праздничных дат (список дат конечно прийдется заполнить вручную)
=СУММ(--(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))));$O$7:$O$9;0))*1<>1;ЕСЛИ((--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)))<=L7)*--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)))>=J7));ДЕНЬНЕД(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)));11);0);0)=ЕСЛИ(--(C7:I7<>"");{1;2;3;4;5;6;7};"НЕТ")))
Изменено: Blood81 - 26.04.2019 20:58:17
 
Цитата
Blood81 написал:
и напоследок вариант с удалением праздничных дат (список дат конечно прийдется заполнить вручную)=СУММ(--(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))));$O$7:$O$9;0))*1<>1;ЕСЛИ((--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))) =J7));ДЕНЬНЕД(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)));11);0);0)=ЕСЛИ(--(C7:I7<>"");{1;2;3;4;5;6;7};"НЕТ")))
вот это формула=) ладно.....завтра проверю, отпишусь=) в моем часовом поясе уже 4 часа утра=)  ;)  
 
=SUM(--(INDEX(C7:I7;N(INDEX(WEEKDAY(ROW(INDEX(A:A;J7):INDEX(A:A;N7));2);)))<>""))
Коротко и без доп.листа, но без учета праздников и переносов.
Изменено: БМВ - 26.04.2019 23:54:32
По вопросам из тем форума, личку не читаю.
 
Цитата
Blood81 написал:
Здесь хочется немного добавить, во первых вы наверное заметили что формула массива работает, но вычисляет довольно медленно - это происходит из за использования в массиве целого столбца СТРОКА(A:A) - формула выглядит лаконично, но работает не быстро. Если ограничить эксель в используемых датах то можно сильно повысить скорость вычисления. Например используем минимальные и максимальные значения дат как границы вычисляемого диапазона вот так: СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))) этот прием позволяет задать произвольный дипазон только из используемых дат.
Вот по этой формуле вычисление тоже не быстро идет. Что в ней можно ограничить чтобы характерного "тормозка" не возникало?
=СУММ(--(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))));$O$7:$O$9;0))*1<>1;ЕСЛИ((--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)))<=L7)*--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)))>=J7));ДЕНЬНЕД(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)));11);0);0)=ЕСЛИ(--(C7:I7<>"");{1;2;3;4;5;6;7};"НЕТ")))
 
И последний вопрос по этой теме.
Возьмем последний замечательный пример от Blood81 . Вопрос таков
Есть определенное количество занятий для усвоения курса и нужно в ячейку "дата окончания занятий" забить логику чтобы Excel сам считал дату последнего занятия в зависимости от продолжительности курса. Прикрепляю пример.
 
Исключить праздники не проблема
=SUM((INDEX(C7:I7;N(INDEX(WEEKDAY(ROW(INDEX(A:A;J7):INDEX(A:A;N7));2);)))<>"")*NOT(ISNUMBER(MATCH(ROW(INDEX(A:A;J7):INDEX(A:A;N7));Q7:Q9;))))
А вот
Цитата
Stounv17 написал:
И последний вопрос по этой теме.
не относится к теме.
Изменено: БМВ - 27.04.2019 19:35:03
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
А вот Цитата Stounv17  написал:И последний вопрос по этой теме.не относится к теме.
Уважаемые эксперты Excel !
В связи со сделанным замечанием, создал отдельную тему под последний вопрос. Вот ссылка на данную тему
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=116706&...
Страницы: 1
Наверх