Сравнения разницы времени в Excel
Пытаюсь сравнить время в Excel для вывода информации о "переработки" или "недоработки" в табеле, столкнулся с проблемой какой-то невиданной до этого.
Есть несколько ячеек:
- Эталон часов A1 - "[ч]:мм" "8:00" (Подставленное значение из функции ВПР)
- Фактически часов A2 - "[ч]:мм" "8:00" (Вычисленное значение из других 3х ячеек со временем
B2 (20:00) - B1 (11:00) - B3 (1:00), где форматыB1,B2,B3 - "ч:мм") - Результат A3 - "[ч]:мм" Вычисляет переработку или недоработку путем разницы во времени по формуле:
=ЕСЛИ(([@[Фактически часов]]-[@[Эталон часов]])>0;"Переработка: "&ТЕКСТ([@[Фактически часов]]-[@[Эталон часов]];"[ч]:мм");ЕСЛИ(([@[Фактически часов]]-[@[Эталон часов]])=0;"Отработано";"Недоработка: "&ТЕКСТ([@[Эталон часов]]-[@[Фактически часов]];"[ч]:мм")))
По факту имею то переработку, то недоработку в "0:00" часов. Не понимаю что делаю не так. Как-то по другому время нужно сравнивать?
заметил еще одну особенность: если сначала вычислить значение в отдельной ячейке, а потом ее сравнивать - то все работает нормально. Но если сразу написать в ячейке: =A2-A1>0 то почему-то получается не 0.
upd: Вычислил и перевел значение =ТЕКСТ(tbl_01[@[Фактически часов]]-tbl_01[@[Эталон часов]]; "0,00E+00") в итоге имею число 1.11E-16... Я конечно догадывался, что проблема какая-то в плавающих запятых. Но что делать-то? Как мне из 8 часов вычесть 8 часов и получить 0? :-(
Ответы (1 шт):
Ошибку дает стандарт вычислений IEEE 754 (стандарт двоичной арифметики с плавающей точкой (запятой)), применяемый в вычислительной технике. Не вдаваясь в подробности (об этом много доступной информации): ошибка возникает при арифметических операцях в 15 разряде после запятой.
Сутки - 1 (единица), время - часть суток:
1 час - 1/24; 1 минута - 1/(24*60) = 1/1440; 1 секунда 1/(24*60*60) = 1/86400
Т.к. время - число, то операции с ним тоже подвержены этой ошибке.
Обход ошибки - округление результата вычислений до нужной точности (а если есть промежуточные расчеты - то и их нужно округлять, чтобы не накапливалась ошибка).
Но какая точность для минут? Проверим.
В любую ячейку (например, D2) пишем формулу и протягиваем ее вниз на 59 строк:
=СТРОКА(A1)/1440
Получим значения минут (от 1 до 59) в формате времени.
Переведем формат ячеек в Общий. Явно видно, что для времени 9, 18, 27... минут число имеет 5 разрядов после запятой, в промежутках между этими минутами числа не имеют окончательной точности (0,...333...; 0,...667...; 0,...111...), т.е. с шестого разряда - бесконечное число (ну, бесконечным оно быть не может, т.к. в Excel ограничение вычислений - 15 значащих цифр, остальное преобразуется в нули).
Вот и имеем необходимую точность округления. Проверим.
В соседний столбец с нашими значениями пишем формулу округления (берем несколько разрядов с запасом) и тянем вниз:
=ОКРУГЛ(D2;9)
Получаем те же минуты, но с необходимой точностью, оставив ошибку в отрезаном хвосте.
С часами, так же, но можно брать 4-6 разрядов. Соответственно, для секунд количество разрядов больше. Хотя, 9-10 разрядов должны дать необходимую точность для любого времени.
Этот совет с округлением - только для обхода ошибки стандарта. Eсли из времени нужно извлекать отдельные составляющие (часы, минуты, секунды), то для этого в Excel есть соответствующие функции листа.