2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вставить двоеточие между числами, чтобы автоматически использовать их в качестве формата времени в Excel?

Особенности ввода данных и их редактирование

Чтобы ввести любые данные в ячейку, необходимо выполнить следующие действия:

1) Щелкните ячейку, чтобы выделить ее.

2) Введите данные в выделенную ячейку с клавиатуры.

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

Чтобы завершить ввод, нажмите Enter или щелкните по значку галочки в строке формул. При вводе данных вы можете использовать клавишу Esc для отмены или нажать на крестик в строке формул, чтобы отменить ввод.

Ввод чисел

В русской версии Excel запятая разделяет целые и дробные числа. В английской версии то же самое делает точка.

В ячейке могут быть как числа, так и специальные символы, например + — E e ( ) , % /

Для ввода чисел со специальными символами существуют специальные функции:

1) Для ввода отрицательного числа перед числом ставится знак "-" (минус).

2) Число, заключенное в скобки, воспринимается программой как отрицательное. То есть (7) — для Excel означает -7.

3) Символ E или e используется для представления числа в экспоненциальной форме. Например, 7e2 означает 7*100, записывается в Excel как 7.00E+02 в экспоненциальном формате, а в числовом формате как 700.

4) Чтобы облегчить ввод больших чисел, можно поставить пробел между цифрами (987 654.00). После нажатия клавиши Enter в ячейке будет отображаться число с пробелами, а в строке формул пробелов не будет.

Знаки пяти процентов используются для ввода процентного формата.

6) Вы можете использовать косую черту (/) для ввода дробных значений. Если программа может распознать введенное число как дату, например 14/11, в ячейке будет отображаться дата 14 ноября. Если число должно быть представлено в виде дроби, перед числом необходимо ввести ноль (0 14/11), и в ячейке отобразятся 1 и 3/11.

Читайте так же:
Как быстро ранжировать значения, игнорируя ошибки в Excel?

Число, которое Excel не может распознать как дату, например 14/28, будет представлено дробью.

Ввод времени.

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

Ввод даты.

День, месяц и год — это три элемента даты. В Excel есть три разделителя: дефис (-), косая черта (/) и точка (. ). День и месяц будут автоматически вставлены в Excel в строку формул, и будет отображена полная дата.

По состоянию на 01.01.2015 Excel присваивает порядковый номер 42005 для самой ранней даты, которой является 1 января 1900 года, таким образом, дата 01.01.2015 соответствует 42005. Таким образом, возможность вычислений между датами возможна, поэтому вы можете узнать, какая дата будет или была через определенное количество дней. Даты до 1900 года воспринимаются как текст.

Ввод текста.

В Excel можно вводить текст в несколько строк. Для этого нажмите клавиши Alt+Enter.

Использование команды Переместить текст позволит вам вместить длинный текст в ячейку. Для этого необходимо:

1) Выберите ячейку с содержимым, которое не помещается в ячейке

2) На вкладке Главная в группе Выравнивание нажмите кнопку Перенос текста

В отличие от предыдущего случая, строка формул по-прежнему содержит одну строку текста.

При необходимости можно настроить ширину столбцов или высоту строк.

Чтобы ввести символ, которого нет на клавиатуре, вы должны:

1) На вкладке Вставка выберите Символ из группы Символы.

Читайте так же:
Как быстро вставить текущую дату с определенным форматом даты в Excel?

В открывшемся диалоговом окне можно выбрать нужный шрифт. Выбрав набор символов, вы упростите процесс поиска. Таким же образом Excel показывает последние символы, которые вы использовали для вставки.

Чтобы вставить выделенный символ, дважды щелкните по нему или нажмите кнопку Insert.

Редактирование данных.

Данные в ячейке можно редактировать несколькими способами: непосредственно в самой ячейке или в строке формул.

Чтобы внести изменения в поле, вы должны

1) Дважды щелкните по ячейке, которую собираетесь редактировать.

2) Внесите необходимые изменения в ячейку.

Если необходимо отменить изменение, нажмите Esc.

Чтобы отредактировать данные в строке формул

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

3) В строке формул нажмите на поле редактирования.

3) Повторить шаги 2 и 3 предыдущего метода.

Вы также можете использовать клавишу Enter и клавишу Cancel в строке формул для сохранения и отмены внесенных изменений.

Исправление чисел, превратившихся в даты

Иногда при импорте данных в Excel из внешних источников возникает неприятная проблема: дробные числа превращаются в даты.

Испорченные данные

Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью — точка), а в Excel у вас российские настройки (где между целой и дробной частью — запятая, а точка используется как разделитель в дате).

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

Читайте так же:
Как быстро найти и отфильтровать ячейки по формату курсива в Excel?

Ниже приведены возможные сценарии на примере испорченных данных на изображении выше:

  • В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
  • В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года — самая ранняя дата, с которой может работать Excel — 1 января 1900 г.
  • А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:

Неправильная дата

Еще одна неправильная дата

Это варианты. И если текстовые числа еще можно вылечить, заменив полную остановку запятой, то числа, преобразованные в даты, не работают. А попытка изменить их формат на числовой выведет не исходные значения, а внутренние коды даты Excel — количество дней от 01.01.1900 до текущей даты:

Неправильное число после изменения формата

Ко всей этой истории можно подойти тремя различными способами.

Способ 1. Заранее в настройках

Используя Файл — Параметры — Дополнительно, вы можете заранее отделить целую часть от дробной, даже если данные еще не загружены:

Настройка разделителей в окне параметров Excel

Снимите флажок Использовать системные разделители и введите точку в поле Десятичный разделитель.

После этого вы можете спокойно импортировать данные — никаких проблем.

Способ 2. Формулой

Когда данные уже загружены, можно использовать простую формулу для получения исходных чисел из испорченной кашицы дата-текст-число:

Формула исправления чисел из дат

=—ЕСЛИ( ЯЧЕЙКА(«формат»;A1)=»G» ; ПОДСТАВИТЬ(A1;». «;»,») ; ТЕКСТ(A1;»М,ГГГГ») )

В английском варианте это будет :

=—IF (CELL («format «;A1)=»G»; SUBSTITUTE (A1;».»;»,»); TEXT (A1;»M ,YYYY «))

В этом случае логика проста:

  • Функция CELL определяет числовой формат исходной ячейки и выводит «G» для текста/чисел или «D3» для дат.
  • Если исходная ячейка содержит текст, используйте функцию SUBSTITUTE для замены полной остановки запятой.
  • Если исходная ячейка содержит дату, напечатайте ее как «номер месяца — запятая — год» с помощью функции ТЕКСТ.
  • Чтобы преобразовать полученное текстовое значение в реальное число, выполните нелепую математическую операцию — добавьте перед формулой два знака минус, имитируя двойное умножение на -1.

Способ 3. Макросом

Когда необходимо исправить большое количество испорченных чисел, разумно автоматизировать этот процесс с помощью макроса. Для этого нажмите Alt + F11 или Visual Basic на вкладке Developer, вставьте пустой модуль в нашу книгу через Insert — Module и скопируйте туда код:

Остается выделить проблемные ячейки и запустить макрос, созданный нажатием Alt + F8 или с помощью опции Макросы на вкладке Разработчик. Испорченные числа будут немедленно исправлены.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector