Ускорение ввода значений в формате ВРЕМЯ в EXCEL

history

При частом вводе данных в формате времени (2:30), необходимость ввода двоеточия «:» серьезно снижает скорость работы. Возникает вопрос: Можно ли обойтись без ввода двоеточия?


В EXCEL при вводе данных в формате времени есть возможность избежать ввода двоеточия.

Изменяем формат ячейки

Пример Пусть нам требуется вводить часы и минуты каких-нибудь событий.

  • Выделите ячейку А1 .
  • Отобразите вкладку Число окна Формат ячеек ( Главная/ Число/ Формат ячеек или нажав CTRL+1 выберите вкладку Число).
  • В списке Числовые форматы выберите Все форматы.
  • В поле Тип введите #":"00 или 00":"00 (в чем разница будет показано ниже)
  • Нажмите ОК .

Теперь можно вводить время, используя только числа. Введем в A1 число 230 и в ячейке увидим значение 02:30, если был установлен формат 00":"00 и 2:30, если формат #":"00. Для 4-х значных чисел оба формата дадут одинаковый результат.

Необходимо учитывать, что ячейка не содержит значение в формате времени, она содержит число 230. В этом можно убедиться, посмотрев на Строку формул – в ней отображается 230, а не 2:30. Изменив формат ячейки на пользовательский , мы изменили лишь формат отображения значения. 

Если со значениями времени не нужно выполнять никаких действий, то задачу можно считать выполненной. Но если значения нужны для расчетов, то нужно разобраться, что делает EXCEL при вводе в ячейку значения с двоеточием, т.е. после нажатия клавиши ENTER .

Преобразование в формат времени

EXCEL интерпретирует введенное значение 2:30 (т.е. «2 двоеточие 30»), как время, т.е. устанавливает Формат ячейки Время, затем вводит в ячейку число 0,104167 (но в Строке Формул отображается 2:30:00. Если изменить формат ячейки на Число , то увидим 0,104167). Откуда взялось это число 0,104167? В форматах Время и Дата принято, что одним суткам соответствует число единица. Соответственно, один час в формате даты это 1/24 (т.к. в сутках 24 часа), а одна минута – 1/(24*60)=1/1440. Число 0,104167 из 2:30 получается так: 2 часа/24 + 30 минут/1440.

Для тех, кто не хочет возиться с двоеточием и кому нужны значения в формате Времени - алгоритм такой (см. файл примера ):

  • в ячейку А1 вводим дату без двоеточия, в виде числа, например 230;
  • устанавливаем пользовательский формат времени 00":"00 (если необходимо);
  • в любой другой ячейке, например В1 , пишем формулу =(ЦЕЛОЕ(A1/100)/24)+ ((A1-(ЦЕЛОЕ(A1/100)*100))/1440) (получаем 0,104167);
  • меняем формат ячейки В1 на Время и получаем полноценное время – 2:30. С этим значением можно производить вычисления в формате ВРЕМЯ.

В файле примера также показано как ускорить ввод в формате чч:мм:сс (обязательно должны быть указаны часы, т.е. число должно быть >10000, т.е. 1:00:00).

Там же, "по просьбе телезрителей", показано как преобразовать любое число от 1 до 235959 в формат чч:мм:сс. Причем можно указывать только секунды, минуты и секунды, ну и конечно часы, минуты и секунды.

Еще задача: Если пользователь захотел сохранить значение времени 5 часов 23 минуты в числовом формате 5,23, т.е. часы в целой части числа, а минуты в дробной после запятой, то формула для перевода в стандратный формат времени следующая:

=ВРЕМЯ(ЦЕЛОЕ(A41);ОКРУГЛ((A41-ЦЕЛОЕ(A41))*100;0);)

в итоге, после применения формата ячейки ч:мм, получим 5:23.

 


Комментарии

Только для авторизованных пользователей

Аноним, 5 ноября 2017 г.
В самом начале статьи у Вас написано (в формате времени (2:30:55)), но далее Вы забываете про 55 и пишете все про 2:30, вопросик а можно ли вводить время 23055 без двоеточий?
Михаил, 5 ноября 2017 г.
в файле примера добавил про секунды
Аноним, 9 ноября 2017 г.
[id295933596|Михаил], Огромное спасибо, но получилось ситуация когда человек не доработал час и при вводе 005545 выдает #ЗНАЧ. Помогите пожалуйста.
Михаил, 12 ноября 2017 г.
[id156613356|Аня], я перезалил файл примера, добавил новую формулу
Аноним, 15 апреля 2018 г.
[id295933596|Михаил], у вас есть ошибка, в описание таблицы там написано чч сс мм , а в табличных формулах рассчитывается чч мм сс
Аноним, 9 июня 2018 г.
Спасибо за урок! Но есть один вопрос, относящийся как раз к проблеме того, что формат получившейся ячейки не является форматом времени и операций со временем не провести. Вы, в конце статьи, объяснили, как привести ячейку В1 к формату времени взяв значение ячейки А1 и преобразовав его уже в В1 по формуле, получить формат время. А как провести то же самое, но используя только одну ячейку? Поясню. У меня есть таблица учета рабочего времени. Я хочу ввести в первую ячейку "900", получив, после нажатия на enter, "9:00" в этой же ячейке, но она уже не будет являться именно временем, она только визуально похожа на нее. Продолжаем. Во вторую ячейку я хочу ввести "1800" и получить "18:00". А в третьей ячейке по формуле проводится операция и на выходе получается отработанное время. Но, естественно, операция не может отработать потому, что физически в первых двух ячейках "900" и "1800", а не время. Если следовать вашему совету, то мне нужно удвоить количество используемых ячеек, чтобы в них провелась операция перевода из текста в формат времени, но это неудобно. Ведь мне придется заполнять какую то другую первую таблицу, чтобы уже во второй точно такой же таблице проводились операции перевода по вашей инструкции и уже проводились операции учета времени. Как мне ввести в ячейку А1 просто "900", чтобы прямо в этой же ячейке это число преобразовалось и визуально и физически в формат времени, то бишь в "9:00"? Заранее благодарю!
Михаил, 9 июня 2018 г.
Это сможет только макрос. Я с макросом не помогу.
Аноним, 13 августа 2019 г.
а как сделать наоборот? мне нужно ввести 2:30 а в ячейке отображалось 150 минут?
Михаил, 15 августа 2019 г.
В соседней ячейке введите формулу =ЧАС(A1)*60+МИНУТЫ(A1) В той, в которую вводите, отобразить не получится.
(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved