Здравствуйте Уважаемые! Помогите пожалуйста! Есть база данных учеников школы. там есть дни недели когда у ученика будут проходить занятия и в какое время, также есть дата когда начинаются занятия и когда заканчиваются. В ячейке "плановое кол-во занятий" (выделил желтой заливкой) нужно забить логику чтобы в ней автоматически просматривалась строка 7 в интервале столбцов от С до I, и если в этом интервале появляется значение времени, то брать день недели соответствующий этому столбцу и рассчитывалось сколько дней (допустим в моем примере вторников и четвергов) будет за расчетный период от даты начала до даты окончания занятий. Сразу дополнительный вопрос задам, как вычитать праздничные дни когда занятий проходить не будет.
А допустим вот такую формулу (см.ниже) нельзя как то модернизировать чтобы она проверяла 7 строку, столбцы от C до I и если в каких-нибудь из этих ячеек не пусто, то считала эти дни недели? вот в моем случае в двух ячейках этого диапазона не пусто, что соответствует Вт и Чт.
или может другой вариант какой нибудь есть, но так чтобы решалось только одной формулой, без нагромождения и создания дополнительного листа....??? Думаю можно еще как то с функцией "ИНДЕКС" решить эту задачу но как не знаю. Помогите пожалуйста.
Уважаемый Blood81 ! Действительно, в этой формуле такое впечатление что в функции "ДЕНЬНЕД" стоит тип=1, и начало недели с Вс а не с понедельника. Проверил на апреле. Дней недели должно быть Пн=5 ВТ=5 Ср=4 Чт=4 Пт=4 Сб=4 Вс=4, а в формуле которую вы предложили получается Пн=4 Вт=5 Ср=5 Чт=4 Пт=4 Сб=4 Вс=4 как раз сдвиг вправо на 1 получился. Подскажите пожалуйста отредактированную формулу. Спасибо.
Отлично! Спасибо боооооольшое! Очень сильно помогли за сутки! Админам-тему можно закрывать! Имеем решение двумя разными подходами! Тут уже у кого какой случай...., кому так подойдет, кому эдак ! Еще раз спасибо парни!
Все таки еще один вопрос....! А можно к этой формуле добавить что если вдруг считаем вторники и вторник выпадает на 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 написал: и напоследок вариант с удалением праздничных дат (список дат конечно прийдется заполнить вручную)=СУММ(--(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(--(СТРОКА(ДВССЫЛ("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 часа утра=)
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 написал: И последний вопрос по этой теме.