Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формулой посчитать сколько дней работал магазин в том или ином месяце, если известны дата открытия и ремонты
 
#1
27.02.2018 23:19:26
Всем доброго времени суток, в файле-примере таблица. В ней приведены данные по магазинам. По каждому известно когда его открыли, а некоторые уже перестали работать (закрылись), также есть информация о том, когда магазины закрывались на ремонт.
Не могу сообразить какую формулу (то что приходит на ум слишком сложно, но, чувствуется, что есть простое решение) использовать, чтобы посчитать количество дней которое проработал тот или иной магазин в том или ином периоде.
Изменено: Konstantin Zhi - 27.02.2018 23:19:44
 
 
 
#2
28.02.2018 07:35:43
Добрый день!
Я просто нашел разность дат, так не подходит?
 
 
 
#3
28.02.2018 09:51:11
Цитата
Arturus написал:
Я просто нашел разность дат, так не подходит?
нет, так не подходит )
посмотрите, что делает Ваша формула: в таблице первая строка с данными это январь 2015, в нем, как известно, 31 день а не 1916
 
 
 
#4
28.02.2018 10:13:58
А может так? (В ячейку H3  и протянуть вниз)
Код
 =ЕСЛИ(E3&F3&G3<>"";ЕСЛИ(E3<>"";РАЗНДАТ(D3;E3;"d");РАЗНДАТ(D3;F3;"d"));РАЗНДАТ(D3;СЕГОДНЯ();"d"))

Не увидел пост #3. Вы в примере покажите куда и какой результат Вам нужен.
Изменено: abduvs77 - 28.02.2018 10:20:05
 
 
 
#5
28.02.2018 10:36:47
Konstantin Zhi, я вообще не использовал столбцы "Дата 1" и "Дата 2", т.к. не понял, для чего они нужны, если есть дата открытия и закрытия магазина. Их я и использовал. Как раз получается 1916 с 2010 по 2015 год
 
 
 
#6
28.02.2018 10:53:55
Если дата открытия магазина раньше чем период с дата1 по дата2 и дата закрытия магазина позже чем период с дата1 по дата2 и не было ремонтов, то кол-во дней работы равно количеству дней в периоде с дата1 по дата2.
Иначе нужно из количества дней периода с дата1 по дата2 вычесть дни связанные с открытием/закрытием магазина (если это событие "внутри" дата1 и дата2), а также дни связанные с ремонтом (по тому же принципу)
Изменено: Konstantin Zhi - 28.02.2018 10:55:25
 
 
 
#7
28.02.2018 10:54:14
Может это?
Код
=ЕСЛИ(C3>E3&F3&G3;ЕСЛИ(E3&F3&G3<>"";ЕСЛИ(E3<>"";РАЗНДАТ(D3;E3;"d")+1;РАЗНДАТ(D3;F3;"d")+1);РАЗНДАТ(D3;СЕГОДНЯ();"d"))+1;РАЗНДАТ(B3;C3;"d")+1)
Изменено: abduvs77 - 28.02.2018 11:02:59
 
 
 
#8
28.02.2018 11:23:32
У меня получилось 2 формулы:
1. Количество рабочих дней (без учёта ремонта):
Код
=МИН([@Дата2];ЕСЛИ(ЧЗНАЧ([@[Дата закрытия магазина]])=0;СЕГОДНЯ();[@[Дата закрытия магазина]]))-МАКС([@Дата1];[@[Дата открытия магазина]])+1
2. Количество дней ремонта
Код
=ЕСЛИ(И(ЧЗНАЧ([@[Закрыт на ремонт С]])>=[@Дата1];ЧЗНАЧ([@[Закрыт на ремонт С]])<=[@Дата2]);РАЗНДАТ([@[Закрыт на ремонт С]];МИН([@Дата2];ЕСЛИ(ЧЗНАЧ([@[Закрыт на ремонт ПО]])=0;СЕГОДНЯ();[@[Закрыт на ремонт ПО]]));"d");0)
Потом просто вычесть. Не стал их объединять, чтобы это можно было хоть как-то прочесть.
Изменено: Wiss - 28.02.2018 11:25:36 (Косяк во второй формуле, должно быть "больше или равно", а не просто "больше/меньше")
Я не волшебник, я только учусь.
 
 
 
#9
28.02.2018 12:03:37
Цитата
Wiss написал:
1. Количество рабочих дней (без учёта ремонта):
Вставил эту формулу в файл. В некоторых случаях в результате работы формулы получается отрицательно число (пример: -31,-62,-91,-122 и т.д.)
Могли бы Вы приложить к Вашему ответу файл с формулами?
 
 
 
#10
28.02.2018 12:08:19
Предлагаю считать это не багом, а фитчей. Отрицательное число - количество дней от закрытия магазина до даты в ячейке Дата1. Это можно или условным форматированием скрыть или в формулу проверку на отрицательное число вставить.
Я не волшебник, я только учусь.
 
 
 
#11
28.02.2018 12:15:38
Цитата
Wiss написал:
Предлагаю считать это не багом, а фитчей
хорошо, но.. но все же не очень понятно, что именно считает формулы ))
почему количество дней работы по первому магазину в феврале 2015 равно 27
почему количество дней работы по первому магазину в марте 2015 равно 30
и т.д.
 
 
 
#12
28.02.2018 12:27:30
Сорри. Мой косяк. Отправил без крайнего сохранения. В расчёте дней работы не хватает "+1" в конце формулы (в сообщении текст формулы выложен верно).
Подумал и всё-таки добавил проверку на отрицательные значения:
Код
=ЕСЛИ(МИН([@Дата2];ЕСЛИ(ЧЗНАЧ([@[Дата закрытия магазина]])=0;СЕГОДНЯ();[@[Дата закрытия магазина]]))-МАКС([@Дата1];[@[Дата открытия магазина]])+1<0;0;МИН([@Дата2];ЕСЛИ(ЧЗНАЧ([@[Дата закрытия магазина]])=0;СЕГОДНЯ();[@[Дата закрытия магазина]]))-МАКС([@Дата1];[@[Дата открытия магазина]])+1)

Формула считает разницу между "датой конца" и "датой начала" магазина:
-за "дату начала" берётся наибольшее значение из  "Даты1" и "Даты открытия магазина";
-за "дату конца" берётся наименьшее значение из "Даты2" и "Даты закрытия магазина". При этом, так как "Даты закрытия магазина" может и не быть, то происходит проверка и вместо "Даты закрытия магазина" подставляется сегодняшняя дата.
Я не волшебник, я только учусь.
 
 
 
Страницы: 1
Наверх