Округление времени в excel до часа
Округление чисел — распространенная задача, и Excel предоставляет достаточно функций, которые округляют значения разными путями (табл. 111.1). Вы должны понимать разницу между округлением и форматированием значения.
Когда вы форматируете число так, чтобы оно отображало определенное количество знаков после запятой, формулы, которые ссылаются на это число, работают с его фактическим значением, которое может отличаться от отображаемого. Когда вы округляете числа, то формулы, которые ссылаются на эти значения, используют округленные числа.
Таблица 111.1. Функции округления Excel
Функция | Действие |
---|---|
ОКРВЕРХ | Округляет число в большую сторону (от нуля) до ближайшего указанного кратного |
РУБЛЬ.ДЕС | Преобразует цену, выраженную в виде дроби, в десятичное число |
РУБЛЬ.ДРОБЬ | Преобразует цену, выраженную десятичным числом, в дробное число |
ЧЁТН | Округляет в большую сторону (от нуля) положительные числа до ближайшего четного целого; округляет в меньшую сторону (от нуля) отрицательные числа до ближайшего четного целого |
ОКРВНИЗ | Округляет число в меньшую сторону (к нулю) до ближайшего указанного кратного |
ЦЕЛОЕ | Округляет число в меньшую сторону до целого |
ОКРУГЛ | Округляет число до указанного кратного |
НЕЧЁТ | Округляет числа в большую сторону (от нуля) до ближайшего нечетного целого; округляет в меньшую сторону (от нуля) отрицательные числа до ближайшего нечетного целого |
ОКРУГЛ | Округляет число до указанного количества цифр |
ОКРУГЛВНИЗ | Округляет число в меньшую сторону (к нулю) до указанного количества цифр |
ОКРУГЛВВЕРХ | Округляет число в большую сторону (от нуля) до указанного количества цифр |
ОТБР | Урезает число до указанного количества значащих цифр |
В следующих разделах приведены примеры формул, которые используют различные типы округления.
Округление до ближайшего кратного числа
Функция ОКРУГЛТ полезна для округления значений до ближайших кратных. Например, вы можете использовать ее, чтобы округлить число до ближайшего, кратного 5. Следующая формула возвращает 135: =ОКРУГЛТ(133;5) .
Округление денежных значений
Часто приходится округлять валютные значения. Например, рассчитанная цена может оказаться равной 45,78923 р. В таком случае вы захотите округлить ее до ближайшей копейки. Этот процесс может показаться простым, но для округления значений такого типа вам предлагается на выбор один из трех следующих способов:
- в большую сторону до ближайшей копейки;
- в меньшую сторону до ближайшей копейки;
- до ближайшей копейки (округление может быть или в большую, или в меньшую сторону).
Следующая формула предполагает, что значение рублей и копеек содержится в ячейке А1. Формула округляет значение до ближайшей копейки. Например, если ячейка А1 содержит 12,421 р., формула возвращает 12,42 р.: =ОКРУГЛ(А1;2) .
Если вам необходимо округлить значение в большую сторону до ближайшей копейки, используйте функцию ОКРВВЕРХ. Следующая формула, например, округляет значение в ячейке А1 до ближайшей копейки (если ячейка А1 содержит значение 12.421 р., формула возвращает 12,43 р.): =ОКРВВЕРХ(А1;0,01) .
Чтобы округлить рублевое значение в меньшую сторону, используйте функцию ОКРВНИЗ. Следующая формула, например, округляет такое значение в ячейке А1 в меньшую сторону до ближайшей копейки (если ячейка А1 содержит 12.421 р., то формула возвращает 12.42 р.): =ОКРВНИЗ(А1;0,01) .
Чтобы округлить рублевое значение до пяти копеек, используйте эту формулу: =ОКРВВЕРХ(А1;0,05) .
Использование функций ЦЕЛОЕ и ОТБР
На первый взгляд, функции ЦЕЛОЕ и ОТБР могут показаться похожими. Они обе преобразовывают значение в целое число. Функция ОТБР просто удаляет дробную часть числа. Функция ЦЕЛОЕ округляет в меньшую сторону до ближайшего целого числа, основываясь на значении дробной части числа.
На практике ЦЕЛОЕ и ОТБР возвращают разные значения только при использовании отрицательных чисел. Например, эта формула возвращает -14.0: =ОТБР(-14,2) .
Следующая формула возвращает -15,0, поскольку число -14,2 округляется в меньшую сторону до следующего целого числа: =ЦЕЛОЕ(-14,2) .
Функция ОТБР принимает дополнительный (необязательный) аргумент, который может быть полезен для усечения десятичных значений. Например, следующая формула возвращает 54,33 (значение усекается до двух десятичных знаков): =ОТБР(54.3333333;2) .
Округление до N значащих цифр
В некоторых ситуациях может понадобиться округлить значение до определенного количества значащих цифр. Например, вы хотите выразить число 1432,187 в терминах двух значащих цифр (то есть как 1400,000). Число 84 356 выражается в трех значимых цифрах как 84 300.
Если значение является положительным числом без десятичных знаков, то вам поможет следующая формула. Она округляет число в ячейке А1 до двух значащих цифр. Для округления до различного количества значащих цифр замените 2 в этой формуле нужным вам числом: =ОКРУГЛВНИЗ(А1;2-ДЛСТР(А1)) .
Для нецелых и отрицательных чисел решение будет немного более сложным. Следующая формула предлагает более общее решение, округляющее значение в ячейке А1 до количества значащих цифр, указанного в ячейке А2. Эта формула работает для положительных и отрицательных целых и нецелых чисел: =ОКРУГЛ(Al;А2-1-ЦЕЛОЕ(LOG10(ABS(А1)))) .
Например, если ячейка А1 содержит число 1,27845, а в ячейке А2 хранится 3, то формула вернет 1,28000 (значение, округленное до трех значащих цифр).
В ремонт металлических дверей замков входит починка дверных петель, коробок и полотен, которые с течением времени и из-за неправильного использования могут расклеиваться и перекашиваться. Бывает так, что они просто перестают плотно держаться в массиве, потому что их плохо прижимают шурупы, которые, в свою очередь, могут выходить из массива вследствие плохо нарезанной резьбы или сырой древесины, куда они были вкручены. В результате после усыхания материала отверстия стали шире и шурупы вышли из них.
В программе Excel по умолчанию предусмотрены функции для работы с датой и временем: ЧАС, МИНУТЫ и СЕКУНДЫ. Детально рассмотрим эти три функции в действии на конкретных примерах. Как, когда и где их можно эффективно применять, составляя различные формулы из этих функций для работы с временем.
Примеры использования функций ЧАС, МИНУТЫ и СЕКУНДЫ для расчетов в Excel
Функция ЧАС в Excel предназначена для определения значения часа из переданного времени в качестве параметра и возвращает данные из диапазона числовых значений от 0 до 23 в зависимости от формата временной записи.
Функция МИНУТЫ в Excel используется для получения минут из переданных данных, характеризующих время, и возвращает данные из диапазона числовых значений от 0 до 59.
Функция СЕКУНДЫ в Excel применяется для получения значения секунд из данных в формате времени и возвращает числовые значения из диапазона от 0 до 59.
Контроль по часам суточного времени в Excel используя функцию ЧАС
Пример 1. Получить текущее время, определить, сколько часов прошло с начала текущих суток, сколько часов осталось до начала новых суток.
Определим текущий момент в коде времени Excel:
Вычислим количество часов от начала суток:
Определим количество часов до окончания суток:
- 24 – количество часов в сутках;
- B3 – текущее время в часах, выраженное в виде числового значения.
Примечание: пример демонстрирует то, что результатом работы функции ЧАС является число, над которым можно выполнять любые арифметические действия.
Преобразование чисел в формат времени с помощью функций ЧАС и МИНУТЫ
Пример 2. Из приложения были подгружены моменты прохождения некоторых событий, которые были распознаны Excel в виде обычных чисел (например, 13:05 было распознано как число 1305). Необходимо преобразовать полученные значения в формат времени, выделить часы и минуты.
Исходная таблица данных:
Для преобразования данных используем функцию:
- B3 – значение, распознанное Excel в качестве обычного числа;
- "00\:00" – формат времени.
В результате получим:
С помощью функций ЧАС и МИНУТЫ выделим искомые величины. Аналогично определим требуемые значения для остальных событий:
Пример использования функции СЕКУНДЫ в Excel
Пример 3. Рабочий день начинается в 8:00 утра. Один работник систематически опаздывал на протяжении предыдущих 10 рабочих дней на несколько секунд. Определить общее время опозданий работника.
Внесем данные в таблицу:
Определим опоздание в секундах. Где B3 – данные о времени прихода на работу в первый день. Аналогично определим секунды опозданий для последующих дней:
Определим общее количество секунд опозданий:
Где C3:C12 – массив ячеек, содержащих значения секунд опозданий. Определим целое значение минут опозданий, зная, что в 1 мин = 60 секунд. В результате получим:
То есть, общее опоздание сотрудника за 10 дней составило 263 секунды, что более 4-х минут.
Особенности синтаксиса функций ЧАС, МИНУТЫ и СЕКУНДЫ в Excel
Функция ЧАС имеет следующую синтаксическую запись:
время_в_числовом_формате – единственный аргумент функции (обязательный для заполнения), характеризующий данные времени, которые содержат данные о часах.
Функция МИНУТЫ имеет следующий синтаксис:
время_в_числовом_формате - обязательный для заполнения аргумент, характеризующий значение, из которого будут вычислены минуты.
Синтаксис функции СЕКУНДЫ в Excel:
время_в_числовом_формате – единственный аргумент, представленный в виде данных, из которых будут вычислены значения секунд (обязателен для заполнения).
В этом руководстве говорится о округлении времени до ближайшего часа, минуты или секунды в Excel, как показано ниже.
Время округления до ближайшего часа / минуты / секунды
Время округления до ближайшего часа / минуты / секунды
Время округления до ближайшего 1 часа
Выберите ячейку и введите эту формулу = ОКРУГЛ (A2; «1:00») в него и нажмите Enter и перетащите дескриптор автозаполнения в нужные ячейки.
Советы:
1. Вам необходимо отформатировать ячейки как формат времени, сначала выбрав ячейки и щелкнув правой кнопкой мыши, чтобы выбрать Формат ячеек в контекстном меню; в Формат ячеек диалог, выбирая Время in Категория список, а затем выберите тип времени в Тип список и нажав OK.
2. Вы также можете использовать эти формулы для округления времени до ближайшего часа. =ROUND(A2/(1/24),0)*(1/24) or = ОКРУГЛ (A2,1 / 24).
3. Если вы хотите округлить время до предыдущего 1 часа, вы можете использовать эту формулу = ЭТАЖ (A2,1 / 24).
4. В приведенных выше формулах вы можете изменить 1 на другое число по своему усмотрению.
Время округления до ближайших 15 минут
Выберите ячейку и введите эту формулу =ROUND(A2*(24*60/15),0)/(24*60/15) в него и нажмите Enter и перетащите дескриптор автозаполнения в нужные ячейки.
Наконечник:
1. Вам необходимо отформатировать ячейки как формат времени, сначала выбрав ячейки и щелкнув правой кнопкой мыши, чтобы выбрать Формат ячеек в контекстном меню; в Формат ячеек диалог выбора Время in Категория список, выбрав тип времени в Тип список и нажав OK.
2. Вы также можете использовать эту формулу = ОКРУГЛ (A2,15 / 60/24) округлить время до ближайшей минуты.
3. Если вы хотите округлить время до предыдущих 15 минут, используйте эту формулу. = ЭТАЖ (A2,15 / 60/24) .
4. В приведенных выше формулах вы можете изменить 15 на другое число по своему усмотрению.
Время округления до ближайших 10 секунд
Выберите ячейку и введите эту формулу = ОКРУГЛ (A2; 10/86400) в него и нажмите Enter и перетащите дескриптор автозаполнения в нужные ячейки.
Наконечник:
1. Вам необходимо отформатировать ячейки как формат времени, сначала выбрав ячейки и щелкнув правой кнопкой мыши, чтобы выбрать Формат ячеек в контекстном меню, затем выберите тип времени в Формат ячеек Диалог.
2. В приведенной выше формуле вы можете изменить 10 на другое число по своему усмотрению. Например, используйте = ОКРУГЛ (A2; 20/86400) округлить время до ближайших 20 секунд.
В этой статье я расскажу о том, как округлить / округлить вверх / вниз столбец формата времени до ближайшей четверти или получаса на листе, как показано на следующем снимке экрана.
Округление / Округление вверх / Время округления до ближайшей четверти / получаса с формулами
Если вы хотите округлить / округлить в большую / меньшую сторону время до ближайшей четверти или получаса, вам могут помочь следующие формулы.
1. Введите эту формулу: = ОКРУГЛ (A2; «0:15») or = ОКРУГЛ (A2; «0:30») в пустую ячейку рядом с вашими данными, которые необходимо округлить до ближайшей четверти или получаса, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, и вы получите список десятичных чисел, см. снимок экрана:
2. Затем отформатируйте числа в формате времени, выберите список чисел, а затем щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню см. снимок экрана:
3. В выскочившем Формат ячеек диалоговое окно, нажмите На заказ из Категория панели и выберите [Ч]: мм: сс Формате в Тип список, см. снимок экрана:
4. Затем нажмите OK, и все ячейки округлены до ближайшей четверти часа, см. снимок экрана:
Ноты:
1. Чтобы округлить время до ближайших четверти или получаса, примените следующую формулу: = ПОТОЛОК (A2; "00:15") or = ПОТОЛОК (A2; "00:30") , и не забудьте отформатировать ячейки как формат времени.
2. Чтобы округлить время до ближайших четверти или получаса, примените следующую формулу: = ЭТАЖ (A2; «00:15») or = ЭТАЖ (A2; «00:30») , и не забудьте отформатировать ячейки как формат времени.
Как преобразовать время в целые часы или минуты в Excel?
Как правило, при преобразовании времени в десятичные часы или минуты с помощью формулы = ячейка * 24 или ячейка * 1440 результатом будут десятичные числа. Но иногда вы просто хотите преобразовать время в целые часы или минуты, как показано ниже, как вы можете решить эту проблему в Excel?
Преобразование времени в целые часы или минуты с помощью формулы
С помощью формулы INT вы можете преобразовать время в целые часы или минуты.
Выделите ячейку, в которую хотите поместить результат, выберите нужную формулу из ниже:
- Преобразовать в целые часы = ЦЕЛОЕ (A2 * 24)
- Преобразовать в целые минуты = ЦЕЛОЕ (A2 * 1440)
Затем нажмите Enter ключ для получения результата. При необходимости перетащите маркер заполнения вниз, чтобы заполнить ячейки формулой.
Преобразование времени в целые часы или минуты с помощью Kutools for Excel
Если у вас есть Kutools для Excel установлен, вы можете применить его Конвертировать время утилита для преобразования времени в десятичные числа, а затем используйте Круглые утилита для округления чисел в большую или меньшую сторону до целых.
1. Выберите время, которое вы хотите преобразовать, нажмите Кутулс > Content > Конвертировать время > Время до часов/Время до минут.
2. Появится диалоговое окно с напоминанием о количестве преобразованных ячеек, щелкните OK чтобы закрыть это. Затем, не снимая выделения с преобразованных ячеек, щелкните Кутулс > Круглые.
3. в Раунд без формулы диалог, тип 0 в Десятичное число текстовое поле, выберите одну круглую форму Опция .
4. Нажмите Ok чтобы округлить все выбранное десятичное время до целых чисел.
Читайте также: