Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Подсчет периодов времени в месяц по заданным условиям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет периодов времени в месяц по заданным условиям (Формулы/Formulas)
Подсчет периодов времени в месяц по заданным условиям
mikolafnv Дата: Среда, 11.11.2015, 22:17 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Уважаемые знатоки! Если это возможно, то помогите с формулой подсчета времени охраны в течении месяца, с учетом длительной охраны по 2 суток. Желательно без макросов. Спасибо! %)
П.С. Условия для формулы внутри примера.
К сообщению приложен файл: 3500866.xls (63.0 Kb)
 
Ответить
СообщениеУважаемые знатоки! Если это возможно, то помогите с формулой подсчета времени охраны в течении месяца, с учетом длительной охраны по 2 суток. Желательно без макросов. Спасибо! %)
П.С. Условия для формулы внутри примера.
Автор - mikolafnv
Дата добавления - 11.11.2015 в 22:17
gling Дата: Среда, 11.11.2015, 22:56 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2525
Репутация: 678 ±
Замечаний: 0% ±

2010
Считает, но не понятно то ли нужно. Есть промежутки без снятия, три взятия подряд, отметил в файле красным
К сообщению приложен файл: 2345965.xls (41.0 Kb)

ЯД-41001506838083
 
Ответить
СообщениеСчитает, но не понятно то ли нужно. Есть промежутки без снятия, три взятия подряд, отметил в файле красным
Автор - gling
Дата добавления - 11.11.2015 в 22:56
SLAVICK Дата: Четверг, 12.11.2015, 12:26 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
А если сводной таблицей?
К сообщению приложен файл: 3500866-1-.xls (82.0 Kb)

Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеА если сводной таблицей?
Автор - SLAVICK
Дата добавления - 12.11.2015 в 12:26
Мурад Дата: Четверг, 12.11.2015, 12:30 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, так в течение 1 дня, по данным, было несколько взятий и снятий. А у вас считает разность максимального времени снятия и максимального взятия.
 
Ответить
СообщениеSLAVICK, так в течение 1 дня, по данным, было несколько взятий и снятий. А у вас считает разность максимального времени снятия и максимального взятия.
Автор - Мурад
Дата добавления - 12.11.2015 в 12:30
Мурад Дата: Четверг, 12.11.2015, 12:36 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
У меня вопрос к автору. Как может быть 112 взятий и 89 снятий?
 
Ответить
СообщениеУ меня вопрос к автору. Как может быть 112 взятий и 89 снятий?
Автор - Мурад
Дата добавления - 12.11.2015 в 12:36
SLAVICK Дата: Четверг, 12.11.2015, 12:49 | Сообщение № 6
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Цитата Мурад, 12.11.2015 в 12:30, в сообщении № 4
А у вас считает разность максимального времени снятия и максимального взятия.

да есть такое :)
Просто если это охрана недвижимости - как правило берется под охрану вечером и до утра, или на выходные - тут автору виднее.

Цитата Мурад, 12.11.2015 в 12:36, в сообщении № 5
У меня вопрос к автору. Как может быть 112 взятий и 89 снятий?

На сколько я понял - это сработала сигнализацая, вот снятия не было, а взятие после отработки сигнализации происходит повторно :o
т.е. если по правильному - то нужно считать с момента ПЕРВОГО взятия и до момента снятия, и так дальше. Промежуточные взятия - нужно пропускать. - ну это я так думаю. Ждемс автора yes

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Четверг, 12.11.2015, 12:52
 
Ответить
Сообщение
Цитата Мурад, 12.11.2015 в 12:30, в сообщении № 4
А у вас считает разность максимального времени снятия и максимального взятия.

да есть такое :)
Просто если это охрана недвижимости - как правило берется под охрану вечером и до утра, или на выходные - тут автору виднее.

Цитата Мурад, 12.11.2015 в 12:36, в сообщении № 5
У меня вопрос к автору. Как может быть 112 взятий и 89 снятий?

На сколько я понял - это сработала сигнализацая, вот снятия не было, а взятие после отработки сигнализации происходит повторно :o
т.е. если по правильному - то нужно считать с момента ПЕРВОГО взятия и до момента снятия, и так дальше. Промежуточные взятия - нужно пропускать. - ну это я так думаю. Ждемс автора yes
Автор - SLAVICK
Дата добавления - 12.11.2015 в 12:49
Мурад Дата: Четверг, 12.11.2015, 13:18 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, а я предлагаю другой способ:
1. связать дату и время формулой и перевести в значение:
Код
=ЗНАЧЕН(ТЕКСТ(D4;"ДД.ММ.ГГГГ")&" "&ТЕКСТ(C4;"ч:мм:сс"))

2. Если бы число взятий было равно числу снятий, то рассчитать время охраны было бы просто:
Код
=СУММЕСЛИ($A$4:$A$404;"СНЯТИЕ  [ РИО ]";$E$4:$E$404)-СУММЕСЛИ($A$4:$A$404;"ВЗЯТИЕ  [ РИО ]";$E$4:$E$404)

Поскольку Сумма разностей каждого снятия и взятия = Сумма всех Снятий - Сумма всех Взятий
 
Ответить
СообщениеSLAVICK, а я предлагаю другой способ:
1. связать дату и время формулой и перевести в значение:
Код
=ЗНАЧЕН(ТЕКСТ(D4;"ДД.ММ.ГГГГ")&" "&ТЕКСТ(C4;"ч:мм:сс"))

2. Если бы число взятий было равно числу снятий, то рассчитать время охраны было бы просто:
Код
=СУММЕСЛИ($A$4:$A$404;"СНЯТИЕ  [ РИО ]";$E$4:$E$404)-СУММЕСЛИ($A$4:$A$404;"ВЗЯТИЕ  [ РИО ]";$E$4:$E$404)

Поскольку Сумма разностей каждого снятия и взятия = Сумма всех Снятий - Сумма всех Взятий
Автор - Мурад
Дата добавления - 12.11.2015 в 13:18
Мурад Дата: Четверг, 12.11.2015, 13:21 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, если сможете, доработайте формулу, чтоб считало Сумма (Сумма всех снятий за день - Сумма всех взятий за день)
[offtop]Хотя это опять то же самое :D [/offtop]


Сообщение отредактировал Мурад - Четверг, 12.11.2015, 13:22
 
Ответить
СообщениеSLAVICK, если сможете, доработайте формулу, чтоб считало Сумма (Сумма всех снятий за день - Сумма всех взятий за день)
[offtop]Хотя это опять то же самое :D [/offtop]
Автор - Мурад
Дата добавления - 12.11.2015 в 13:21
SLAVICK Дата: Четверг, 12.11.2015, 13:35 | Сообщение № 9
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Вот еще вариант формулой:
Код
=ЕСЛИОШИБКА(
ЕСЛИ(
ИЛИ(ИНДЕКС($A$1:A3;МАКС((($A3:A$4="СНЯТИЕ  [ РИО ]")*СТРОКА($A3:A$4));(($A3:A$4="ВЗЯТИЕ  [ РИО ]")*СТРОКА($A3:A$4)));1)="ВЗЯТИЕ  [ РИО ]";A4<>"ВЗЯТИЕ  [ РИО ]");
"";
(ИНДЕКС(A4:E996;ПОИСКПОЗ("СНЯТИЕ  [ РИО ]";A4:A96;0);3)+ИНДЕКС(A4:E996;ПОИСКПОЗ("СНЯТИЕ  [ РИО ]";A4:A96;0);4))-СУММ(C4:D4));0)

Она пропускает промежуточные взятия

Заменил файл
Еще раз заменил файл - добавил сводную по охране :D
К сообщению приложен файл: _3500866-1-1-20.zip (49.2 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Четверг, 12.11.2015, 13:47
 
Ответить
СообщениеВот еще вариант формулой:
Код
=ЕСЛИОШИБКА(
ЕСЛИ(
ИЛИ(ИНДЕКС($A$1:A3;МАКС((($A3:A$4="СНЯТИЕ  [ РИО ]")*СТРОКА($A3:A$4));(($A3:A$4="ВЗЯТИЕ  [ РИО ]")*СТРОКА($A3:A$4)));1)="ВЗЯТИЕ  [ РИО ]";A4<>"ВЗЯТИЕ  [ РИО ]");
"";
(ИНДЕКС(A4:E996;ПОИСКПОЗ("СНЯТИЕ  [ РИО ]";A4:A96;0);3)+ИНДЕКС(A4:E996;ПОИСКПОЗ("СНЯТИЕ  [ РИО ]";A4:A96;0);4))-СУММ(C4:D4));0)

Она пропускает промежуточные взятия

Заменил файл
Еще раз заменил файл - добавил сводную по охране :D
Автор - SLAVICK
Дата добавления - 12.11.2015 в 13:35
Мурад Дата: Четверг, 12.11.2015, 13:50 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, а вот эту фразу учитывает? :)
Также нужно учитывать время охраны в выходные и праздничные дни по 24 часа в сутки
 
Ответить
СообщениеSLAVICK, а вот эту фразу учитывает? :)
Также нужно учитывать время охраны в выходные и праздничные дни по 24 часа в сутки
Автор - Мурад
Дата добавления - 12.11.2015 в 13:50
SLAVICK Дата: Четверг, 12.11.2015, 13:57 | Сообщение № 11
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Цитата Мурад, 12.11.2015 в 13:50, в сообщении № 10
SLAVICK, а вот эту фразу учитывает?

А Вы файл смотрели?
В сутках 24-е часа а в сводной есть 26 :o ,
Учитывает B)
Правда в этом примере нет таких взятий - но можете поменять дату снятия и увидите разницу ;)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Четверг, 12.11.2015, 14:07
 
Ответить
Сообщение
Цитата Мурад, 12.11.2015 в 13:50, в сообщении № 10
SLAVICK, а вот эту фразу учитывает?

А Вы файл смотрели?
В сутках 24-е часа а в сводной есть 26 :o ,
Учитывает B)
Правда в этом примере нет таких взятий - но можете поменять дату снятия и увидите разницу ;)
Автор - SLAVICK
Дата добавления - 12.11.2015 в 13:57
Мурад Дата: Четверг, 12.11.2015, 14:41 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Я имел в виду проверку даты условием
Код
=ДЕНЬНЕД()
= 6 или 7
 
Ответить
СообщениеЯ имел в виду проверку даты условием
Код
=ДЕНЬНЕД()
= 6 или 7
Автор - Мурад
Дата добавления - 12.11.2015 в 14:41
SLAVICK Дата: Четверг, 12.11.2015, 15:16 | Сообщение № 13
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Цитата Мурад, 12.11.2015 в 14:41, в сообщении № 12
Я имел в виду проверку даты условием

а ЗАЧЕМ?
у нас есть дата и время сдачи товара под охрану и дата и время снятия - какие дни это были - не важно. Если объект поставили на охрану на месяц - формула посчитает количество часов за месяц охраны :D

Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
Цитата Мурад, 12.11.2015 в 14:41, в сообщении № 12
Я имел в виду проверку даты условием

а ЗАЧЕМ?
у нас есть дата и время сдачи товара под охрану и дата и время снятия - какие дни это были - не важно. Если объект поставили на охрану на месяц - формула посчитает количество часов за месяц охраны :D
Автор - SLAVICK
Дата добавления - 12.11.2015 в 15:16
pabchek Дата: Четверг, 12.11.2015, 16:10 | Сообщение № 14
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Мой вариант. В строках со "Снятием" - срок с момента первого взятия на охрану
массивная
Код
=ЕСЛИ(A3="СНЯТИЕ  [ РИО ]";C3+D3-МАКС(ЕСЛИ(A$1:A2="СНЯТИЕ  [ РИО ]";C$1:C2+D$1:D2))-1/НАИБОЛЬШИЙ(1/((A$2:A3="ВЗЯТИЕ  [ РИО ]")*(C$2:C3+D$2:D3)-МАКС(ЕСЛИ(A$1:A2="СНЯТИЕ  [ РИО ]";C$1:C2+D$1:D2)));1);"")

исправил, перезалил
К сообщению приложен файл: 4352070.xls (64.0 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Четверг, 12.11.2015, 16:27
 
Ответить
СообщениеМой вариант. В строках со "Снятием" - срок с момента первого взятия на охрану
массивная
Код
=ЕСЛИ(A3="СНЯТИЕ  [ РИО ]";C3+D3-МАКС(ЕСЛИ(A$1:A2="СНЯТИЕ  [ РИО ]";C$1:C2+D$1:D2))-1/НАИБОЛЬШИЙ(1/((A$2:A3="ВЗЯТИЕ  [ РИО ]")*(C$2:C3+D$2:D3)-МАКС(ЕСЛИ(A$1:A2="СНЯТИЕ  [ РИО ]";C$1:C2+D$1:D2)));1);"")

исправил, перезалил
Автор - pabchek
Дата добавления - 12.11.2015 в 16:10
mikolafnv Дата: Пятница, 13.11.2015, 15:26 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Доброго времени суток, уважаемые форумчане! Благодарю Вас за отзывчивость!
Промежуточные взятия - нужно пропускать. Нужен подсчет пары "взятие-снятие".
И еще есть некоторый нюанс - в первом столбце с разных объектов имеются различные сигналы (без кавычек):
"ВЗЯТИЕ [ РИО ]", "СНЯТИЕ [ РИО ]",
"ВЗЯТИЕ [ Сигнал BK-4 ]", "СНЯТИЕ [ Сигнал BK-4 ]"
"ВЗЯТИЕ(ОПС БЕЗ ВЫХ) [ УО Атлас-3 ]", "СНЯТИЕ [ УО Атлас-3 ]"
"ВЗЯТИЕ [ ППКОП 4 GSM ]", СНЯТИЕ [ ППКОП 4 GSM ]
так вот, как если возможно составить формулу чтобы только сигнальными для подсчета были понятия "взятие" и "снятие",
независимо от того какие символы будут после этих слов в этих ячейках.
Также вы правильно поняли, что если объект сдался под охрану на неделю или на месяц
тогда должно быть например 168 или 720 часов охраны соответственно.
К сообщению приложен файл: 6407004.xlsx (10.9 Kb)


Сообщение отредактировал mikolafnv - Пятница, 13.11.2015, 15:33
 
Ответить
СообщениеДоброго времени суток, уважаемые форумчане! Благодарю Вас за отзывчивость!
Промежуточные взятия - нужно пропускать. Нужен подсчет пары "взятие-снятие".
И еще есть некоторый нюанс - в первом столбце с разных объектов имеются различные сигналы (без кавычек):
"ВЗЯТИЕ [ РИО ]", "СНЯТИЕ [ РИО ]",
"ВЗЯТИЕ [ Сигнал BK-4 ]", "СНЯТИЕ [ Сигнал BK-4 ]"
"ВЗЯТИЕ(ОПС БЕЗ ВЫХ) [ УО Атлас-3 ]", "СНЯТИЕ [ УО Атлас-3 ]"
"ВЗЯТИЕ [ ППКОП 4 GSM ]", СНЯТИЕ [ ППКОП 4 GSM ]
так вот, как если возможно составить формулу чтобы только сигнальными для подсчета были понятия "взятие" и "снятие",
независимо от того какие символы будут после этих слов в этих ячейках.
Также вы правильно поняли, что если объект сдался под охрану на неделю или на месяц
тогда должно быть например 168 или 720 часов охраны соответственно.
Автор - mikolafnv
Дата добавления - 13.11.2015 в 15:26
gling Дата: Пятница, 13.11.2015, 18:41 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2525
Репутация: 678 ±
Замечаний: 0% ±

2010
Цитата
понятия "взятие" и "снятие",

Формула массива
Код
=ЕСЛИ(ЛЕВСИМВ(A2;6)="ВЗЯТИЕ";(ВПР("СНЯТИЕ";ЛЕВСИМВ($A3:$D$37;6);4;0)+ВПР("СНЯТИЕ";ЛЕВСИМВ($A3:$D$37;6);3;0))-(D2+C2);"")
К сообщению приложен файл: 7485978.xlsx (12.9 Kb)

ЯД-41001506838083
 
Ответить
Сообщение
Цитата
понятия "взятие" и "снятие",

Формула массива
Код
=ЕСЛИ(ЛЕВСИМВ(A2;6)="ВЗЯТИЕ";(ВПР("СНЯТИЕ";ЛЕВСИМВ($A3:$D$37;6);4;0)+ВПР("СНЯТИЕ";ЛЕВСИМВ($A3:$D$37;6);3;0))-(D2+C2);"")
Автор - gling
Дата добавления - 13.11.2015 в 18:41
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет периодов времени в месяц по заданным условиям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!