Укажите данные, которые Excel интерпретирует как числовые

Обновлено: 24.11.2024

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Обзор

Когда вы вводите дату, используя двузначное число года (например, 98), Microsoft Excel использует определенные правила, чтобы определить, какое столетие использовать для даты. В этой статье объясняется, как Microsoft Excel определяет век.

Подробнее

Когда вы вводите дату в ячейку, если вы опускаете цифры века из года, Excel автоматически определяет, какой век использовать для даты.

Например, если вы введете 05.07.98, Excel автоматически использует 1998 год и изменит дату на 05.07.1998 в строке формул.

В следующих разделах объясняются правила по умолчанию, которые использует Excel.

Использование региональных настроек в панели управления

Excel сначала интерпретирует даты в соответствии с порядком дат, заданным настройкой стиля краткой даты в разделе «Региональные настройки» на панели управления, например, M/d/yy.

Если вы используете Microsoft Windows 98 или более позднюю версию, вы можете использовать параметр Когда вводится год из двух цифр, интерпретировать год между настройкой в разделе «Региональные настройки» на панели управления, чтобы определить крайний год для столетия. Значение по умолчанию — 2029, но вы можете изменить его на любое значение от 99 до 9999.

Вы можете изменить параметр Когда вводится год из двух цифр, интерпретировать год между настройками на значение, несовместимое с Excel. Если вы введете несовместимое значение, Excel вернется к правилам, описанным в разделе «Правило 2029» этой статьи.

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

Нажмите "Пуск", выберите "Настройки" и выберите "Панель управления".
Дважды щелкните значок региональных настроек.
Перейдите на вкладку "Дата".
В поле Когда вводится год из двух цифр, интерпретируйте год между, введите нужный крайний год и нажмите OK.

В следующей таблице показано, как разные крайние годы будут иметь значение при вводе двузначного года в Excel:

< td>2039

Настройка	Дата ввода	Дата использования
7/9/70	7/9/1970
2039	2 /3/27	3/2/2027
2075	7/9/70	07.09.2070
2099	3.02.27	3.02.2027

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

Правило 2029 года

По умолчанию Excel определяет столетие, используя крайний 2029 год, что приводит к следующему поведению:

Действительны даты в диапазоне от 1 января 1900 г. (01.01.1900) до 31 декабря 9999 г. (31.12.9999) включительно.

При вводе даты с двумя цифрами года Excel использует следующие столетия:

< /tbody>

Год вводится двумя цифрами	Использованный век
00-29	21 (2000 год)
30-99	20 (1900 год)

Например, когда вы вводите следующие даты, Excel интерпретирует их следующим образом:

Если вы хотите ввести дату до 1 января 1930 года или после 31 декабря 2029 года, введите год полностью из четырех цифр. Например, чтобы использовать дату 4 июля 2076 г., введите 04.07.2076.

Ввод дат, содержащих только компоненты день/месяц или месяц/год

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

Когда вы вводите дату, содержащую только два из трех компонентов даты, Excel предполагает, что дата имеет форму день/месяц или месяц/год. Excel сначала пытается разрешить запись как запись дня/месяца в текущем году. Если не удается разрешить запись в форме «День/месяц», Excel пытается разрешить запись в форме «Месяц/год», используя первый день этого месяца. Если не удается разрешить запись в форме «Месяц/год», Excel интерпретирует запись как текст.

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

В этой таблице предполагается, что текущий год — 1999.

Запись	Разрешение
12/01	12 /1/1999
12/99	1/12/1999
11/95	1/11/1995
13/99	13/99 (текст)
1/30	30/1/1999
1/99	1/1/1999
28/12	28/12/1999

В этой таблице показано, как Excel сохраняет дату, а не то, как дата отображается в ячейке. Формат отображения даты зависит от форматов даты, примененных к ячейке, и текущих настроек в разделе «Региональные настройки» на панели управления.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Когда вы вводите дату или время в ячейку, они отображаются в формате даты и времени по умолчанию. Этот формат по умолчанию основан на региональных параметрах даты и времени, указанных в панели управления, и изменяется при изменении этих параметров в панели управления. Вы можете отображать числа в нескольких других форматах даты и времени, большинство из которых не зависят от настроек панели управления.

В этой статье

Отображать числа в виде даты или времени

Дату и время можно форматировать по мере ввода. Например, если вы введете 2/2 в ячейку, Excel автоматически интерпретирует это как дату и отобразит в ячейке 2 февраля. Если это не то, что вам нужно — например, если вы предпочитаете отображать в ячейке 2 февраля 2009 г. или 2 февраля 2009 г., — вы можете выбрать другой формат даты в диалоговом окне «Формат ячеек», как описано ниже. процедура. Точно так же, если вы введете в ячейке 9:30 a или 9:30 p, Excel интерпретирует это как время и отобразит 9:30 или 21:30. Опять же, вы можете настроить способ отображения времени в диалоговом окне «Формат ячеек».

На вкладке "Главная" в группе "Число" нажмите кнопку запуска диалогового окна рядом с номером.

Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно "Формат ячеек".

В списке «Категория» нажмите «Дата» или «Время».

В списке Тип выберите нужный формат даты или времени.

Примечание. Форматы даты и времени, начинающиеся со звездочки (*), соответствуют изменениям в региональных настройках даты и времени, указанных в панели управления. На форматы без звездочки настройки панели управления не влияют.

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

Число в активной ячейке выбора на листе отображается в поле «Образец», чтобы вы могли предварительно просмотреть выбранные параметры форматирования чисел.

Создать собственный формат даты или времени

На вкладке "Главная" нажмите кнопку вызова диалогового окна рядом с номером.

Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно "Формат ячеек".

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

В поле "Категория" нажмите "Пользовательский". В поле Тип вы должны увидеть код формата, соответствующий формату даты или времени, который вы выбрали на шаге 3. Встроенный формат даты или времени нельзя изменить или удалить, поэтому не беспокойтесь о его перезаписи.< /p>

В поле "Тип" внесите необходимые изменения в формат. Вы можете использовать любой код из следующих таблиц:

Дни, месяцы и годы

Используйте этот код

Месяцы с января по декабрь

Месяцы как первая буква месяца

Дни с воскресенья по субботу

Годы как 1900–9999

Если вы используете "m" сразу после кода "h" или "hh" или непосредственно перед кодом "ss", Excel отображает минуты вместо месяца.

Часы, минуты и секунды

Используйте этот код

Минуты от 00 до 59

Секунды в формате 00–59

Время как 4:36:03 P

Прошедшее время в часах; например, 25.02

Прошедшее время в минутах; например, 63:46

Прошедшее время в секундах

Доли секунды

AM и PM Если формат содержит AM или PM, час основан на 12-часовом формате, где "AM" или "A" указывает время с полуночи до полудня, а "PM" или "P" указывает время. с полудня до полуночи. В противном случае час основан на 24-часовом формате. Код "m" или "mm" должен стоять сразу после кода "h" или "hh" или непосредственно перед кодом "ss"; в противном случае Excel отображает месяц вместо минут.

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

Советы по отображению даты или времени

При попытке отменить формат даты или времени, выбрав «Общие» в списке «Категория», Excel отображает числовой код. Когда вы снова вводите дату или время, Excel отображает формат даты или времени по умолчанию. Чтобы ввести конкретный формат даты или времени, например январь 2010 г., вы можете отформатировать его как текст, выбрав Текст в списке категорий.

Чтобы быстро ввести текущую дату на листе, выберите любую пустую ячейку и нажмите CTRL+; (точка с запятой), а затем при необходимости нажмите клавишу ВВОД. Чтобы вставить дату, которая будет обновляться до текущей даты каждый раз, когда вы снова открываете лист или пересчитываете формулу, введите =СЕГОДНЯ() в пустой ячейке и нажмите клавишу ВВОД.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

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

Числа, отформатированные как текст, выравниваются в ячейке по левому краю, а не по правому, и часто помечаются индикатором ошибки.

Что вы хотите сделать?

Прием 1. Преобразование чисел в текстовом формате с помощью проверки ошибок

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

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

Выделите на листе любую отдельную ячейку или диапазон ячеек с индикатором ошибки в левом верхнем углу.

Как выделить ячейки, диапазоны, строки или столбцы

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

Диапазон ячеек

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

Вы также можете выбрать первую ячейку в диапазоне, а затем нажать F8, чтобы расширить выделение с помощью клавиш со стрелками. Чтобы остановить расширение выделения, снова нажмите F8.

Большой диапазон ячеек

Щелкните первую ячейку в диапазоне, а затем, удерживая нажатой клавишу Shift, щелкните последнюю ячейку в диапазоне. Вы можете прокрутить, чтобы последняя ячейка стала видимой.

Все ячейки на листе

Нажмите кнопку "Выбрать все".

Чтобы выделить весь лист, вы также можете нажать Ctrl+A.

Если рабочий лист содержит данные, Ctrl+A выбирает текущую область. Нажатие Ctrl+A во второй раз выделяет весь лист.

Несмежные ячейки или диапазоны ячеек

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

Вы также можете выделить первую ячейку или диапазон ячеек, а затем нажать Shift+F8, чтобы добавить к выделению еще одну несмежную ячейку или диапазон. Чтобы прекратить добавление ячеек или диапазонов к выделенному, снова нажмите Shift+F8.

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

Вся строка или столбец

Нажмите заголовок строки или столбца.

<р>2. Заголовок столбца

Вы также можете выделить ячейки в строке или столбце, выбрав первую ячейку и нажав клавиши Ctrl+Shift+стрелка (стрелка вправо или влево для строк, стрелка вверх или стрелка вниз для столбцов).

Если строка или столбец содержат данные, Ctrl+Shift+клавиша со стрелкой выбирает строку или столбец до последней использованной ячейки. При повторном нажатии клавиш Ctrl+Shift+стрелка выделяется вся строка или столбец.

Смежные строки или столбцы

Перетащите мышью заголовки строк или столбцов.Или выберите первую строку или столбец; затем, удерживая нажатой клавишу Shift, выберите последнюю строку или столбец.

Несмежные строки или столбцы

Нажмите заголовок столбца или строки первой строки или столбца в выбранном вами фрагменте; затем, удерживая нажатой клавишу Ctrl, щелкайте заголовки столбцов или строк других строк или столбцов, которые вы хотите добавить к выборке.

Первая или последняя ячейка в строке или столбце

Выберите ячейку в строке или столбце, а затем нажмите Ctrl+клавиша со стрелкой (стрелка вправо или влево для строк, стрелка вверх или стрелка вниз для столбцов).

Первая или последняя ячейка на листе или в таблице Microsoft Office Excel

Нажмите Ctrl+Home, чтобы выбрать первую ячейку на листе или в списке Excel.

Нажмите Ctrl+End, чтобы выбрать последнюю ячейку на листе или в списке Excel, содержащем данные или форматирование.

Ячейки до последней использованной ячейки на листе (нижний правый угол)

Выберите первую ячейку, а затем нажмите Ctrl+Shift+End, чтобы расширить выбор ячеек до последней использованной ячейки на листе (нижний правый угол).

Ячейки в начало листа

Выберите первую ячейку, а затем нажмите Ctrl+Shift+Home, чтобы расширить выделение ячеек до начала рабочего листа.

Больше или меньше ячеек, чем активное выделение

Удерживая нажатой клавишу Shift, щелкните последнюю ячейку, которую вы хотите включить в новый выбор. Прямоугольный диапазон между активной ячейкой и ячейкой, которую вы щелкнули, становится новым выделением.

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

Рядом с выбранной ячейкой или диапазоном ячеек нажмите появившуюся кнопку ошибки.

В меню выберите Преобразовать в число. (Если вы хотите просто избавиться от индикатора ошибки без преобразования числа, нажмите «Игнорировать ошибку».)

Это действие преобразует числа, сохраненные в виде текста, обратно в числа.

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

Прием 2. Преобразование чисел в текстовом формате с помощью специальной вставки

В этом методе вы умножаете каждую выбранную ячейку на 1, чтобы принудительно преобразовать число в текстовом формате в обычное число. Поскольку вы умножаете содержимое ячейки на 1, результат в ячейке выглядит идентичным. Однако Excel фактически заменяет текстовое содержимое ячейки числовым эквивалентом.

Выберите пустую ячейку и убедитесь, что ее числовой формат является общим.

Как проверить числовой формат

На вкладке "Главная" в группе "Число" нажмите стрелку рядом с полем "Числовой формат" и выберите "Общие".

В ячейке введите 1 и нажмите клавишу ВВОД.

Выберите ячейку и нажмите Ctrl+C, чтобы скопировать значение в буфер обмена.

Выберите ячейки или диапазоны ячеек, содержащие числа, сохраненные в виде текста, который вы хотите преобразовать.

Как выделить ячейки, диапазоны, строки или столбцы

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

Диапазон ячеек

Большой диапазон ячеек

Все ячейки на листе

Нажмите кнопку "Выбрать все".

Чтобы выделить весь лист, вы также можете нажать Ctrl+A.

Несмежные ячейки или диапазоны ячеек

Вся строка или столбец

Нажмите заголовок строки или столбца.

<р>2. Заголовок столбца

Смежные строки или столбцы

Перетащите мышью заголовки строк или столбцов. Или выберите первую строку или столбец; затем, удерживая нажатой клавишу Shift, выберите последнюю строку или столбец.

Несмежные строки или столбцы

Первая или последняя ячейка в строке или столбце

Первая или последняя ячейка на листе или в таблице Microsoft Office Excel

Нажмите Ctrl+Home, чтобы выбрать первую ячейку на листе или в списке Excel.

Нажмите Ctrl+End, чтобы выбрать последнюю ячейку на листе или в списке Excel, содержащем данные или форматирование.

Ячейки до последней использованной ячейки на листе (нижний правый угол)

Ячейки в начало листа

Выберите первую ячейку, а затем нажмите Ctrl+Shift+Home, чтобы расширить выделение ячеек до начала рабочего листа.

Больше или меньше ячеек, чем активное выделение

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

На вкладке "Главная" в группе "Буфер обмена" нажмите стрелку под надписью "Вставить" и выберите "Специальная вставка".

В разделе "Операция" выберите "Умножение" и нажмите "ОК".

Чтобы удалить содержимое ячейки, введенной на шаге 2, после успешного преобразования всех чисел, выберите эту ячейку и нажмите клавишу DELETE.

Некоторые бухгалтерские программы отображают отрицательные значения в виде текста со знаком минус (–) справа от значения. Чтобы преобразовать текстовую строку в значение, вы должны использовать формулу для возврата всех символов текстовой строки, кроме самого правого символа (знака отрицания), а затем умножить результат на –1.

Например, если значение в ячейке A2 равно "156–", следующая формула преобразует текст в значение –156.

Прием 3. Применение числового формата к числам в текстовом формате

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

Выберите ячейки, содержащие числа, сохраненные в виде текста.

Как выделить ячейки, диапазоны, строки или столбцы

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

Диапазон ячеек

Большой диапазон ячеек

Все ячейки на листе

Нажмите кнопку "Выбрать все".

Чтобы выделить весь лист, вы также можете нажать Ctrl+A.

Несмежные ячейки или диапазоны ячеек

Вся строка или столбец

Нажмите заголовок строки или столбца.

<р>2. Заголовок столбца

Смежные строки или столбцы

Перетащите мышью заголовки строк или столбцов. Или выберите первую строку или столбец; затем, удерживая нажатой клавишу Shift, выберите последнюю строку или столбец.

Несмежные строки или столбцы

Первая или последняя ячейка в строке или столбце

Первая или последняя ячейка на листе или в таблице Microsoft Office Excel

Нажмите Ctrl+Home, чтобы выбрать первую ячейку на листе или в списке Excel.

Нажмите Ctrl+End, чтобы выбрать последнюю ячейку на листе или в списке Excel, содержащем данные или форматирование.

Ячейки до последней использованной ячейки на листе (нижний правый угол)

Ячейки в начало листа

Выберите первую ячейку, а затем нажмите Ctrl+Shift+Home, чтобы расширить выделение ячеек до начала рабочего листа.

Больше или меньше ячеек, чем активное выделение

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

На вкладке "Главная" в группе "Число" нажмите кнопку запуска диалогового окна рядом с номером.

В поле "Категория" выберите числовой формат, который вы хотите использовать.

Чтобы эта процедура завершилась успешно, убедитесь, что числа, сохраненные в виде текста, не содержат лишних пробелов или непечатаемых символов внутри или вокруг чисел. Дополнительные пробелы или символы иногда появляются при копировании или импорте данных из базы данных или другого внешнего источника. Чтобы удалить лишние пробелы из нескольких чисел, которые хранятся в виде текста, вы можете использовать функцию ОБРЕЗАТЬ или функцию ОЧИСТИТЬ. Функция TRIM удаляет пробелы из текста, за исключением одиночных пробелов между словами. Функция CLEAN удаляет из текста все непечатаемые символы.

Отключить проверку ошибок

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

Перейдите на вкладку "Файл".

В разделе "Справка" нажмите "Параметры".

В диалоговом окне "Параметры Excel" выберите категорию "Формулы".

В разделе Правила проверки ошибок снимите флажок Числа, отформатированные как текст или с предшествующим апострофом.

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

С первого взгляда легко определить, отформатировано ли число как текст, поскольку оно будет выровнено по левому краю, а не по правому краю в ячейке.

Выделите ячейку или диапазон ячеек, содержащих числа, которые вы хотите отформатировать как текст. Как выделить ячейки или диапазон.

Совет. Вы также можете выделить пустые ячейки, а затем ввести числа после того, как отформатируете ячейки как текст. Эти числа будут отформатированы как текст.

На вкладке "Главная" в группе "Число" щелкните стрелку рядом с полем "Числовой формат" и выберите "Текст".

Примечание. Если вы не видите параметр "Текст", используйте полосу прокрутки, чтобы прокрутить список до конца.

Чтобы использовать десятичные разряды в числах, которые хранятся в виде текста, вам может потребоваться включить десятичные точки при вводе чисел.

При вводе числа, начинающегося с нуля, например кода продукта, Excel по умолчанию удалит нулевое значение. Если это не то, что вам нужно, вы можете создать собственный числовой формат, который заставляет Excel сохранять начальный нуль.Например, если вы вводите или вставляете десятизначные коды продуктов на листе, Excel изменит такие числа, как 0784367998, на 784367998. В этом случае вы можете создать собственный числовой формат, состоящий из кода 0000000000, который заставит Excel отображать все десять цифр кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в разделах Создание или удаление пользовательского числового формата и Сохранение ведущих нулей в числовых кодах.

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

Вы также можете использовать функцию ТЕКСТ для преобразования числа в текст в определенном числовом формате. Примеры этого метода см. в разделе Сохраняйте ведущие нули в числовых кодах. Информацию об использовании функции ТЕКСТ см. в разделе Функция ТЕКСТ.

Читайте также: