Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Подсчитать количество дней в диапазоне дат по условию другого столбца
 
#1
15.10.2017 19:48:20
Уважаемые форумчане!

Пожалуйста, подскажите как формулой высчитать количество дней в диапазоне дат (текущая ячейка даты - 6 дней) столбца "Date", по условию столбца "Time", где ячейка = 0:00.
Т.е. количество дней (в пределах последних 7 дней), где "Time" = 0:00

Пример вложен, желаемый результат в столбце "Total free days in last 7 days"
 
 
 
#2
15.10.2017 21:08:41
Понятно, что результат желаемый, но непонятна логика получения этого результата  :)  
 
 
 
#3
15.10.2017 21:57:51
_Igor_61, выбираем диапазон в столбце "Date" = дата текущей строки - 6, затем смотрим в этом диапазоне строк столбец "Time". Находим даты в которых "Time" = 0:00. Считаем количество дней где "Time" = 0:00

В столбце "Total time for last 7 days" реализовано суммирование времени столбца "Time" в диапазоне = дата текущей строки - 6 (помощь участника форума - StepanWolkoff)
Изменено: ac1-caesar - 15.10.2017 23:16:04
 
 
 
#4
15.10.2017 22:18:21
Цитата
ac1-caesar написал:
дата текущей строки - 6
минус 6 дней от даты или 6 строк вниз? Или 6 октября? Или еще что-то, что знаете только Вы? Если от даты, то как получилось "1" в первой строке? 01.10 - (черточка - это "минус") - 6 дней =  должно быть 25.09, которого в таблице нет. А вообще для таких задач есть "СЧЁТЕСЛИ" и "СЧЁТЕСЛИМН", только задачу нужно правильно понять, чтобы ее решить
 
 
 
#5
15.10.2017 22:53:36
_Igor_61, к примеру ячейка D2 должна получить диапазон дат = A2 (01.10.17) : A2 минус 7 дней. Соответственно между датами 01.10.17 и 25.09.17.
Ячейка D4 должна получить диапазон дат = A4 (02.10.17) : A4 минус 7 дней. Соответственно между датами 02.10.17 и 26.09.17, ну и т.д.
Теперь в этих диапазонах нужно вычислить количество дней где "Time" = 0:00
 
 
 
#6
15.10.2017 22:58:46
Цитата
_Igor_61 написал:
А вообще для таких задач есть "СЧЁТЕСЛИ" и "СЧЁТЕСЛИМН"
Предполагаю, но к сожалению не хватает опыта для реализации :)  
 
 
 
#7
15.10.2017 23:26:28
По-моему Вы что-то не договариваете  :)  В примере от  08.10 назад на 6 дней - до 02.10 Time=0:00  встречается 6 раз, а в ожидаемом результате у Вас стоит "3". И для чего остальные цифры (в строках где нет 0:00) в столбце с ожидаемым результатом? Чтобы еще больше запутать?
 
 
 
#8
15.10.2017 23:59:12
Цитата
_Igor_61 написал:
 В примере от  08.10 назад на 6 дней - до 02.10 Time=0:00  встречается 6 раз, а в ожидаемом результате у Вас стоит "3"
Все верно в этом диапазоне ячейки с данными 0:00 встречаются 6 раз, но если Вы посмотрите даты в которых "Time" = только 0:00, всего 3 дня - это  03.10.17, 05.10.17, 07.10.17.
 
 
 
#9
16.10.2017 00:13:41
Здравствуйте ac1-caesar,. Вы свой пример смотрели? В примере таких дат 5 если повтор седьмого числа, считать за одну дату. А так 3; 4; 5; 7; 7; 8, по этому то и не понятно. По описанию, вроде бы должна работать формула
Код
=СЧЁТЕСЛИМН($A$1:A1;"<"&A2;$A$1:A1;">"&A2-7;$B$1:B1;$B$2)
но она выдает не такой результат как у Вас,  это и не понятно, что у Вас за логика подсчета. Похоже что объясняете Вы по своему варианту, а на форум выложили другой.
Изменено: gling - 16.10.2017 00:22:01
 
 
 
#10
16.10.2017 07:32:45
Здравствуйте gling, извиняюсь если мои объяснения не раскрывают сути. Логика проста в выбраном диапазоне нужно выбрать даты, где "Time" = 0:00. Если к примеру дата 04.10.17 имеет значения 1:11, 0:00 и 1:27, то соответственно "Time" этой даты не равен 0:00 (а равен 2:38) и значит 04.10.17 в счет не должна попасть.А вот к примеру 07.10.17 имеет 2 раза по 0:00, соответственно "Time" этой даты все равно будет равен 0:00, поэтому эту дату считаем.
 
 
 
#11
16.10.2017 07:58:12
Давайте, как рассматривал _Igor_61, возьмем дату 08.10.17 - ячейка D14. Эта ячейка должна выбрать диапазон дат между 08.10.17 и 02.10.17. У нас это получается строки с 4 по 14.

И теперь по датам:
02.10.17 = 1:10
02.10.17 = 1:30
Итог 02.10.17 = 2:40 (не считаем)

03.10.17 = 0:00 (считаем)

04.10.17 = 1:11
04.10.17 = 0:00
04.10.17 = 1:27
Итог 04.10.17 = 2:38 (не считаем)

05.10.17 = 0:00 (считаем)

06.10.17 = 1:15 (не считаем)

07.10.17 = 0:00
07.10.17 = 0:00
Итог 07.10.17 = 0:00 (считаем)

08.10.17 = 1:17 (не считаем)

Итого: для ячейки D14, диапазон между 08.10.17 и 02.10.17, в диапазоне 3 дня равные 0:00 - 03.10.2017, 05.10.2017, 07.10.2017.

Файлы удалены: превышение допустимого размера вложения [МОДЕРАТОР]
Изменено: ac1-caesar - 16.10.2017 17:11:55
 
 
 
#12
16.10.2017 14:40:17
Видимо сложновато для формул.
Может хотя бы посчитать количество ячеек со значениями 0:00 в диапазоне 7 дней?

Эта формула считает все ячейки со значениями 0:00.
Код
=СЧЁТЕСЛИМН($A$2:[@Date];">="&[@Date]-6;$B$2:[@Time];"=0:00")
Изменено: ac1-caesar - 16.10.2017 17:26:31
 
 
 
#13
16.10.2017 18:54:59
Теперь понятно. Но тогда Вы подскажите в каком из сообщений я просмотрел, что сумма времени за сутки должна равняться нулю.
Цитата
ac1-caesar написал:
Может хотя бы посчитать количество ячеек со значениями 0:00 в диапазоне 7 дней?
Формула из сообщения #9 это выполняет, но без учета текущего дня. Например против даты 7.10.2017 будут считать нулевые значения в датах с 1.10.2017 по 6.10.2017. Если нужно учитывать и значение против результирующей ячейки, то нужно изменить диапазон в формуле.
Изменено: gling - 16.10.2017 19:02:01
 
 
 
#14
16.10.2017 19:04:05
gling, не совсем корректно работает. К примеру D2, D3 должны иметь 1, так как 01.10.17 равен 0:00.
Только для ячеек, работает формула в сообщении 12.
Но задача чуть сложнее.
Изменено: ac1-caesar - 16.10.2017 22:45:44
 
 
 
#15
16.10.2017 19:22:09
Цитата
gling написал:
Если нужно учитывать и значение против результирующей ячейки, то нужно изменить диапазон в формуле.
Прбовал, результат некорректный.
 
 
 
#16
16.10.2017 21:07:46
Можно сказать результат подогнал под Ваш. Но опять таки не понятно, если при подсчете 1 числа у Вас стоят единицы, то почему 7 число не входит в диапазон подсчета? Против 7 числа стоит число 4, а это 1; 1; 3 и 5. По такой логике против 1 числа должно быть 0, так как значений выше нет, что у меня и получилось.Или при подсчете 7 числа должно быть не 4, а 5 или 6,в зависимости от того как считать нули в одну дату.
 
 
 
#17
16.10.2017 21:22:22
Цитата
gling написал:
почему 7 число не входит в диапазон подсчета?
Входит, и это 1, 3, 5 и 7
 
 
 
#18
16.10.2017 21:25:00
01.10.17 = 0:00 (считаем), а значит и против первого числа стоит 1
 
 
 
#19
16.10.2017 21:36:51
Еще вариант, всё совпадает кроме 10 числа.
 
 
 
#20
16.10.2017 22:41:59
Не ожидал конечно, что задача на столько сложная.
Хотелось формулой обойтись, но видимо придется код VBA писать.
gling, спасибо Вам за уделенное внимание и потраченное время!
 
 
 
#21
16.10.2017 22:44:56
Да не за что. Но всё таки интересно, в последнем файле Вар2 опять не то? Вроде логику понял, или всё таки не понял?
Изменено: gling - 16.10.2017 22:46:12
 
 
 
#22
16.10.2017 22:53:33
gling, работает не корректно, почему то 10-е число неправильно суммирует, результат тянется за уши. Перегружено - создан доп столбец, именованный диапазон, выборка на 2 строки вниз. В общем закручено сильно. Это же только пример, в реальной таблице может встретится что на одну дату 5 раз 0:00 + 3 раз значения или 10 раз 0:00, ну и т.д. всевозможные вариации.
 
 
 
#23
16.10.2017 22:59:52
Доп строка создана была когда счет велся До текущей даты, эту строку можно убрать, а вот про 10 число не понятно. Объясните по чему там должно быть 3? Ведь 10-7 получается 3 число и в диапазон от 3 попадает 3; 5; 7; 9, а почему результат должен быть 3?
То что с доп столбцом, конечно плохо. Проще не смог придумать, слишком много условий, считать только 7 дней из большого списка + чтобы сумма по каждому дню посчиталась и была равна нулю+несколько нулей в один день считались как один.  
Изменено: gling - 16.10.2017 23:04:23
 
 
 
#24
17.10.2017 06:57:58
Цитата
gling написал:
Объясните по чему там должно быть 3?
На 10-е число диапазон с 04.10.17 по 10.10.17 и в него входят только 5, 7, 10
 
 
 
#25
17.10.2017 07:01:13
Цитата
gling написал:
Ведь 10-7 получается 3 число
10-е число тоже считается, поэтому с 04.10.17.
Т.е. 4, 5, 6, 7, 8, 9, 10 - 7 дней
 
 
 
#26
17.10.2017 20:09:47
Теперь так же как и у Вас.
 
 
 
#27
18.10.2017 14:25:32
gling, сейчас вроде верно, заметил - если на последнюю ячейку Вар 2 встать на редактирование и нажать ENTER, то значение меняется.
Можно ли все это теперь в одну формулу вкорячить?
 
 
 
Страницы: 1
Наверх