Как управлять преобразованием данных в Excel для Windows и Mac

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

По умолчанию существует четыре таких варианта поведения преобразования Excel; в 2021 году Microsoft представила опции переключения, позволяющие пользователям выбирать, как реализовать три из них, но не преобразование строк. Хорошие новости: версии для Windows и Mac теперь включают опцию, позволяющую отключить преобразование строк в Excel. Ниже приведены более подробные сведения об этой проблеме Excel и ее решении, характерном для Microsoft 365.

Перейти к:

Функция преобразования Excel часто допускает ошибки Новые параметры Excel позволяют отключить функцию преобразования.

Функция преобразования Excel часто допускает ошибки

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

Давайте рассмотрим пример: March1 — это короткое название гена пальца 1, связанного с мембраной, кольцевого CH-типа, и оно выглядит как дата 1 марта. Когда вы вводите March1, Excel автоматически преобразует его в дату. Это связано с тем, что Excel, стремясь быть полезным, предполагает, что значение на самом деле является датой.

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

Функция преобразования Excel преобразует четыре типа входных значений:

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

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

Рисунок А

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

Новые параметры Excel позволяют отключить функцию преобразования.

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

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

Чтобы найти эти параметры в Microsoft 365, выполните следующие действия:

Откройте вкладку Файл. Выберите «Параметры» или выберите «Дополнительно», а затем «Параметры». Нажмите Данные на левой панели. (Изначально настройки находились в разделе «Дополнительно», раздел «Данные» имеет больше смысла и его будет легче найти.) В разделе «Автоматическое преобразование данных» вы увидите несколько опций (рис. B).

Рисунок Б

Теперь вы можете отключить все внутренние преобразования. Чтобы отключить все варианты поведения, вы можете снять флажок с первого варианта: «Включить все преобразования данных по умолчанию ниже при вводе, вставке или загрузке текста в Excel». Или вы можете оставить этот параметр включенным и при необходимости снять флажок с одного или нескольких других параметров.

Последний вариант, «При загрузке файла .CSV или аналогичного файла, уведомлять меня о любых автоматических преобразованиях чисел», удобен, когда вы импортируете данные, которые Excel может преобразовать автоматически. Это простой вариант, который предупреждает вас перед преобразованием входящих данных. На этом этапе вы можете выбрать импорт без преобразования данных.

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