Трюк №74. Три способа отображения отрицательных значений времени в Excel 2010

Иногда вам бывает нужно отобразить отрицательные значения времени, но Excel выводит только ######. Есть несколько способов решить эту проблему.

Если вы введете время 12:00:00 (полдень) в любую ячейку, а затем отформатируете ее, выбрав формат Общий (General), то увидите, что ее числовое значение равно 0.5 (в английской версии). Схожим образом введите время 24:00:00 в любую ячейку. Посмотрите на строку формул и вы увидите 1/1/1900 12:00:00 AM.

Для Excel даты и время — это не что иное, как числа. В случае даты он по умолчанию считает, что у 1 января 1900 года числовое значение равно 1, у 2 января 1900 года — 2 и т. д. Время считается как десятичные доли, кроме полуночи, числовое значение которой равно 1. (Подробнее об этом — в разделе «Трюк № 38. Трюки с возможностями даты и времени в Excel».) Из-за этого в Excel возникает настоящая проблема с отрицательными значениями времени. Вот три способа заставить Excel отображать отрицательные значения времени.

Способ 1. Изменение системы дат Excel по умолчанию

Первый быстрый и простой способ научить Excel отображать отрицательные значения времени — выбрать команду пункт меню Файл → Параметры Excel → Вычисления (File → Parametres Excel → Calculation) и установить флажок Система дат 1904 (1904 date system). Система дат 1904 называется также датой и временем Macintosh.
[stextbox id=»warning»]Помните, что при этом в Excel изменятся начальные даты, от которых отсчитывается значение всех ячеек. Вместо 1 января 1900 года это будет 2 января 1904 года. Изменение этого параметра влияет только на ту рабочую книгу, в которую вы вносите изменения.[/stextbox]
Теперь можно вычитать значения времени и видеть на рабочем листе отрицательные значения времени.

Попробуйте. Выберите команду Файл → Параметры Excel → Вычисления (File → Parametres Excel → Calculation) и установите флажок Система дат 1904 (1904 date system). В ячейку А2 введите 5:00:00 AM, в ячейку A3 — 6:00:00 AM, в ячейку А4 — =А2-А3. Вы получите результат -1:00, но только если выберете систему дат 1904, как описано ранее.

Если изменение системы дат Excel по умолчанию на систему дат 1904 может вызвать проблемы в других вычислениях времени в рабочей книге, нужен другой способ, который изменяет вид ячеек, содержащих отрицательные значения.

Способ 2. Функция ТЕКСТ (TEXT)

Второй способ требует использования функции ТЕКСТ (TEXT). Для начала в ячейке А2 введите 5:00:00 AM, в ячейке A3 — 6:00:00 AM. В ячейке А4 введите следующую формулу: =ТЕХТ(МАХ($А$2:$А$3)-MIN($А$2:$А$3);"-Н::ММ"), в русской версии Excel =ТЕКСТ(МАКС($А$2:$А$3)-МИН($А$2:$А$3);"-Ч::ММ").

Используя эту вложенную формулу, вы вычтете A3 из А2, чтобы получить положительное значение времени. Затем отформатируете ячейку при помощи функции ТЕКСТ (TEXT), чтобы она могла отображать отрицательные значения времени. Комбинация функций МАКС (МАХ) и МИН (MIN) гарантирует, что более ранее время всегда будет вычитаться из более позднего.
[stextbox id=»warning»]Если вы захотите использовать полученное значение в другой формуле, нужно помнить, что возвращенный результат в действительности является текстовым значением, а не числовым.[/stextbox]

Способ 3. Пользовательский формат

Еще один способ отображения отрицательных значений времени без перехода к системе дат 1904 и с гарантированным возвращением числового значения — это использование пользовательского формата -ч:мм (-h:mm) в диалоговом окне Главная → Формат → Формат ячеек (Home → Format → Format Cells).

Этот способ работает только в том случае, если вы всегда хотите отображать только отрицательные значения времени. Он требует также, чтобы более раннее время вычиталось из более позднего. Это означает, что на самом деле возвращаться будут положительные значения времени, но перед ними будет выводиться знак минус.

Так, используя один из этих трех способов, вы сможете отображать отрицательные значения времени. Помните, что свой подвох заключен в каждом способе, поэтому применяйте их, не забывая о возможных подводных камнях.

Top