Как можно упростить формулу в EXCEL
Всем доброго времени суток, изначально задача ставилась подсчитать количество часов: исходные данные выглядят следующим образом: 09:00-13:00 13:45-18:00 19:00-24:00.
Первая проблема заключается в том, что в Google Таблицах все считает корректно, а в MS Office при вводе конечного времени -24:00 выдает ошибку.
И существует также необходимость в расчете данных при одном интервале (пример 09:00-13:00), двум и трем соответсвенно, в случае если данные в искомой ячейке отсутствуют то выдает ошибку #Знач пытался исправить добавлением =ЕСЛИОШИБКА но ругается на то, что "эта формула использует больше уровней вложенности, чем допускается текущем форматом файла"
Сама формула:
=ЕСЛИ(ДЛСТР(E20)>24;СУММ(СУММ(-ВРЕМЯ(ЛЕВСИМВ(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";1))-1); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";1))+1;2););ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";2))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";2))+1;2);));СУММ(-ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";3))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";3))+1;2););ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";4))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";4))+1;2);));СУММ(-ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";5))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";5))+1;2);));ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";6))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";6))+1;2);));ЕСЛИ(ДЛСТР(E20)<24;СУММ(СУММ(-ВРЕМЯ(ЛЕВСИМВ(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";1))-1); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";1))+1;2););ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";2))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";2))+1;2);));СУММ(-ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";3))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";3))+1;2););ВРЕМЯ(ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";4))-2;2); ПСТР(E20;НАЙТИ("*";ПОДСТАВИТЬ(E20;":";"*";4))+1;2);)))))
если в A1 написано время, например, 09:00-13:00
в B1 укажем дату начала =ВРЕМЗНАЧ(ЛЕВСИМВ(A1;5))
в C1 дату окончания =ВРЕМЗНАЧ(ПРАВСИМВ(A1;5))
в D1 разницу =ЕСЛИ(C1<B1;C1+ВРЕМЯ(23;59;59)-B1+ВРЕМЯ(0;0;1);C1-B1)
самое простое решение предоставил Сергей
Но если надо решение в одной ячейке тогда так:
=(ВРЕМЗНАЧ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;"24:00";"23:59");5))-ВРЕМЗНАЧ(ЛЕВСИМВ(A1;5)))+(ПСТР(A1;7;2)="24")*0,000694444 
при вводе конечного времени -24:00 выдает ошибку.
наверное стоит начать с того, что на часах не бывает 24:00...
Сама формула: ...
не особо понимаю зачем писать формулу с НАЙТИ() и ПОДСТАВИТЬ(), если мы уже знаем формат и искать ничего не надо...
как-то так можно попробовать... защиту от 24:00 добавил только на последний интервал
=ВРЕМЗНАЧ(ПСТР(E20;7;5))-ВРЕМЗНАЧ(ПСТР(E20;1;5))+ЕСЛИ(ДЛСТР(E20)>11;ВРЕМЗНАЧ(ПСТР(E20;19;5))-ВРЕМЗНАЧ(ПСТР(E20;13;5)))+ЕСЛИ(ДЛСТР(E20)>23;ЕСЛИ(ПСТР(E20;31;5)="24:00";1;ПСТР(E20;31;5))-ВРЕМЗНАЧ(ПСТР(E20;25;5))) в одну строку не влез, беда
Формулу можно упростить до безобразия, а конкретно до
=F1+F2+F3
если изменить первоначальное представление данных.
Для этого надо дважды воспользоваться "Текст по столбцам" на вкладке "Данные".
Чтобы упростить вашу формулу в Excel для подсчета количества часов, давайте рассмотрим несколько шагов и рекомендаций. Основная проблема, с которой вы столкнулись, связана с обработкой времени и ошибками, возникающими при использовании более 24 часов. Вот как можно упростить вашу задачу:
Упрощение формулы
Используйте функцию ВРЕМЯ:
Вместо сложной обработки строк с использованием функций ЛЕВСИМВ, ПСТР и НАЙТИ, попробуйте использовать функцию ВРЕМЯ, чтобы преобразовать текстовые значения времени в формат времени Excel.
Разделение интервалов:
Если ваши данные представлены в формате "09:00-13:00", вы можете использовать функцию ТЕКСТ для преобразования строк в формат времени и затем вычислить разницу.
Пример упрощенной формулы
Предположим, что ваши данные находятся в ячейке E20 и имеют формат "09:00-13:00 13:45-18:00 19:00-24:00". Вы можете использовать следующую формулу для подсчета общего количества часов:
text
=СУММ(ЕСЛИ(ДЛСТР(E20)=0;0;ВРЕМЯ(ЛЕВСИМВ(E20;НАЙТИ("-";E20)-1));ВРЕМЯ(ПСТР(E20;НАЙТИ("-";E20)+1;5))-ВРЕМЯ(ЛЕВСИМВ(E20;НАЙТИ("-";E20)-1))))
Обработка ошибок
Чтобы избежать ошибки #ЗНАЧ!, вы можете использовать функцию ЕСЛИОШИБКА:
text
=ЕСЛИОШИБКА(СУММ(...); 0)
Это позволит вам возвращать 0 вместо ошибки, если данные отсутствуют или не могут быть обработаны.
Форматирование ячеек
Для корректного отображения времени, превышающего 24 часа, убедитесь, что ячейка отформатирована следующим образом:
Выделите ячейку с результатом.
Нажмите Ctrl + 1 для открытия меню форматирования.
Выберите Числовой > Настраиваемый.
Введите формат [h]:mm.