Как на самом деле суммировать часы и минуты в Excel

Вот, попросили помочь. Суть задачи - имеем время в часах и минутах (формат ЧЧ:ММ, если сложить его обычной функцией СУММ, Excel начинает складывать часы и минуты просто как время по 24-часовой системе, так что 23:40 + 0:25 дадут 0:05, а не 24:05 (см.рис.).

Excel неверно для нашей задачи складывает время

Посмотрел бегло в инете - масса бредовых решений, типа, хранить время как число с дробной частью или вводить дополнительные столбцы для выделения часов и минут. На самом деле всё просто - СУММ(МИНУТЫ(диапазон)) не взять, значит, засунем в СУММ(ЕСЛИ(бредовое_условие;диапазон;0)) - как любой расчёт, где нужно взять функцию от диапазона ячеек с наложенным на каждую из них условием.

Пользуясь этим нехитрым приёмом, получим время в минутах по ячейкам A1 и A2 (естественно, так же по любым другим). Сунем формулу, к примеру, в ячейку A5:

=СУММ(ЕСЛИ(ЧАС(A1:A2)>-1;ЧАС(A1:A2);0))*60+СУММ(ЕСЛИ(МИНУТЫ(A1:A2)>-1;МИНУТЫ(A1:A2);0))

Как любую формулу с диапазоном, завершать комбинацией Ctrl+Shift+Enter - иначе не сработает. Теперь у нас в ячейке A5 есть полное количество минут. Осталось получить часы и минуты (формула для ячейки A6):

=ЦЕЛОЕ(A5/60)&":"&(A5-ЦЕЛОЕ(A5/60)*60)

Это уже завершается обычным нажатием Enter. Формула неплохая, но мне не нравится, что она покажет 24:5, а не 24:05.

Из эстетических соображений сделаю ещё такую:

 Хм, полезная получилась мелочь, полную версию положу на сайт

Надеюсь, теперь Света сможет доделать табель учёта рабочего времени :)

15.10.2010, 21:41 [14070 просмотров]