Почему Excel обрабатывает длинные числовые строки как научную нотацию даже после изменения формата ячейки на текст
Я пытаюсь обработать некоторые данные в Excel. Данные включают числовые номера счетов и другие длинные числовые строки (например, MEID сотового телефона). Я не выполняю математические операции над этими строками, поэтому я хочу, чтобы Excel воспринимал их как простой текст.
- Возьмите длинный номер, например 1240800388917, и вставьте его в ячейку на новом листе.
- Формат ячейки по умолчанию в Excel является общим, поэтому строка представлена в научной нотации как 1.2408E + 12
- Щелкните правой кнопкой мыши по ячейке, выберите «Формат ячеек», установите формат «Текст».
Ячейка по-прежнему отображается в научной нотации, хотя формат текста был установлен.
- Отформатируйте пустую ячейку как текст. Щелкните правой кнопкой мыши по ячейке, выберите «Формат ячеек», установите формат «Текст».
- Возьмите длинный номер, например 1240800388917, и вставьте его в ячейку с форматированным текстом.
Теперь ячейка отображается в виде строки, а не в научной записи.
Результаты, оставшиеся в научной нотации, даже если ячейка отформатирована как текст, кажутся мне неработающими. Я видел предложенные обходные пути, такие как: используйте импорт CSV и установите формат текста, добавьте символ пробела в начало каждой числовой строки и другие.
Есть ли простой способ обойти эти строки в формате текста?
Ах, воспоминания о данных, возвращающихся с того времени, когда я проводил массовую перекрестную проверку чисел в Excel (больше никогда). Когда вы вводите длинные числовые строки в Excel, попробуйте, скажем, 12345678901234567890 (20 цифр), Excel обычно преобразует их для вас. Это означает, что число из 20 цифр, которое вы только что набрали, было сокращено до пятнадцати значащих цифр.
Также обратите внимание: преобразование является усечением, а не округлением. (12345678901234567890 — 1,2345678901234 6 E + 19, но в Excel появится 1,2345678901234 5 E + 19)
Это происходит в точке входа, поэтому, как только она будет введена, любые дополнительные детали будут потеряны, и поэтому даже если вы скажете Excel, что действительно имели в виду текст, а не число, вам отчасти не повезло, и, следовательно, почему Ваша первая последовательность не работает.
Это на самом деле немного странно — установка формата с общего на текст не сразу отобразит его как исходную строку, но если вы зайдете и отредактируете данные (как, например, просто нажмите в зону редактирования и нажмите ) он будет обновлен, чтобы быть точно тем, что вы вставили.
Быстрый взлом: установите форматирование в соответствующих столбцах, скопируйте вставку в блокнот, затем в Excel.
Это работало для меня в Excel 2010. Просто выберите столбец, щелкните правой кнопкой мыши, Форматировать ячейки, Пользовательский и выберите параметр, который говорит 0 (второй вариант ниже общего).
Это намного лучше, чем принятый ответ, который гласит: «тебе не повезло»! У меня был файл CSV, в котором была эта проблема, и решение для нестандартного форматирования работало отлично.
Это все еще кажется усеченным в моем тесте. Используя Excel 2013, я ввел следующее в ячейку 12345678901234567890. Когда я нажимаю клавишу ввода, это отображается как 1.23457E + 19, а базовое значение изменяется на 12345678901234500000, поэтому происходит потеря данных. Это не отвечает на оригинальный вопрос.
Один апостроф перед числом заставит Excel воспринимать число как текст (включая выравнивание по умолчанию по левому краю). И если у вас есть ошибки помечены, он будет отображаться как число, сохраненное как текстовая ошибка в ячейке.
Конечно, справедливость — никогда не стоит недооценивать способность Excel (или Microsoft) делать что-то совершенно необъяснимое или повторяемое. Однако апострофный трюк обычно работает так, как рекламируется.
ты можешь сделать это навалом? У меня есть данные с 60000 ячеек, которые я хочу преобразовать в текст.
Я обнаружил, что в Office 2010, если сначала отформатировать столбец, а затем вставить данные, большие цифры будут отображаться правильно.
Это, вероятно, потому что он хранится в виде строки. Что произойдет, если вы сделаете другую ячейку, скажем, с содержанием ячейки, умноженным на 2?
Это работает в Excel 2016 / Office 365. Я выбрал все ячейки, отформатировал их в текст, а затем вставил свои скопированные данные, и они не отображаются с назойливой научной нотацией. Спасибо!
Это то, что также работало для меня в Google Sheets . «Формат> Число> 0» до вставки в vals и не конвертировалось в научную нотацию, а также НЕ теряло конечные значения (округление до vals), что происходило слишком часто ,
где a1 содержит числовое значение. После этого скопируйте столбец и вставьте его в исходный столбец.
Это можно применить после вставки данных в столбец, но вы должны сделать это перед сохранением электронной таблицы.
Я много работаю со штрих-кодами (UPC) и Excel по умолчанию отформатирует их как научные обозначения. Сводит меня с ума! Почему это не вариант, я никогда не узнаю.
Мне известен возраст вопроса, но это тот, который я получил после поиска в Google и который не ответил на мой вопрос (почему форматирование как текст не работает и почему число сокращается до 15 цифр после форматирования печатать).
Краткий ответ: вы не можете работать со своим номером так, как вам нравится ПОСЛЕ того, как вы ввели числовое значение и нажали Enter . В тот момент, когда вы это сделаете, число будет усечено до 15 цифр и представлено как экспоненциальное.
Другой способ сделать это в больших количествах — импортировать значения из текстового файла. Смею сказать, что это единственный способ для большинства ситуаций. И обязательно импортируйте файл, а не просто открывайте его, так как Excel воспринимает csv тип как его собственный формат и обрезает большие числа до 15 цифр как само собой разумеющееся.
Теперь к последнему вопросу. Если вы введете большое числовое значение в ячейку Excel, отформатированную в виде текста, она все равно будет отображаться в научной нотации, если ячейка недостаточно широка. Это раздражает, но побочный эффект некоторых внутренних алгоритмов Excel. Просто сделайте ячейку шире, и вы будете видеть полную ценность каждый раз.
Почему Excel обрабатывает длинные числовые строки как научную нотацию даже после изменения формата ячейки на текст
Установка финансового или денежного формата позволяет отображать в ячейке число с указанием денежной единицы. На рис.9 показано отображение чисел с различными денежными единицами. В столбцах В и D приведены денежные форматы, а столбцах С и Е – финансовые.
Финансы в Excel
Ошибки
Потеря данных
Критическое завершение
При работе со сложными структурами расчетов Excel может критически закрыть программу без сохранения данных. Этот сбой особенно часто встречается в ранних версиях Excel. Так, например, легко «убить» файл Excel 2000-2003, попытавшись перемножить несколько миллионов ячеек на одном листе.
В случае критического завершения программы восстановить не удастся только несохраненные данные. При регулярном появлении таких критических сбоев рекомендуется переустановить Microsoft Office, а лучше перейти на более позднюю версию этого пакета. В качестве временного решения можно посоветовать применять опцию автосохранения файла.
Слишком много форматов
Для решения проблемы иногда приходится очищать от форматирования листы целиком. Удалите в первую очередь неиспользуемые пользовательские форматы числа. Может помочь также открытие файла в Excel 2007, сохранение сначала в новом, затем в старом формате. Если такой возможности нет, то можно попробовать переносить информацию в новый файл через последовательное копирование листов.
Неоконченные вычисления
Необходимо заметить, что такое поведение программы встречается достаточно редко и только в файлах с очень большим количеством сложных формул.
Ошибки отображения
В Excel 2007 разработчиками признана ошибка при умножении чисел, образующих результат в диапазоне 65534,99999999995 и 65535. Например, 850*77.1 показывает результат 100000. Причем, это именно ошибка отображения результата на экране – по факту в ячейке правильное значение 65535. Можно производить дальнейшие вычисления, используя именно это правильное значение.
С нашей точки зрения, это достаточно безобидная ошибка, так как вероятность столкнуться с ней на практике весьма низка.
Ошибки в формулах
Мы выделили главу с описанием «багов» в вычислениях в отдельную статью.
Дополнительно к описанным ситуациям некорректных расчетов часто приводятся замечания по точности в работе функций с плавающей точкой, а также некоторых статистических функций. При работе с экономическими моделями расчеты с высокой степенью точности (до десятых знаков после запятой) используются крайне редко, поэтому вряд ли имеет смысл обсуждать подобные проблемы.
Следует также заметить, что количество «багов» в вычислениях относительно допустимой сложности расчетов в Excel ничтожно мало – проблем с другими функциональными особенностями встречается значительно больше.
Псевдоошибки
В 99% случаев ситуации, преподносимые пользователями как «ошибки Excel», по факту таковыми не являются. Чаще всего проблемы связаны с непониманием формул или логики вычислений электронной таблицы.
Другая типичная причина проблем – это некорректное использование форматов чисел. Так, например, легко добиться визуальной ситуации, когда «2 * 2 = 5»:
В первом примере в ячейках A1:C1 на экране отображаются только целая часть чисел, в расчетах же участвуют вещественные числа: «2,3 * 2,3 = 5,29».
Во втором примере в ячейкe C2 установлен пользовательский формат отображения чисел. Для всех чисел отображается символ «5», вне зависимости от того, какое значение на самом деле хранится в ячейке. Если производить дальнейшие вычисления с ячейкой C1, то в расчетах будет участвовать правильное значение «4».
Про отображение чисел с использованием пользовательских форматов можно сказать, что подобная ситуация не является ошибкой Excel – это специально разработанная возможность программы. Ответственность за корректное использование форматов лежит на конечном пользователе.
Внутренний тип значения может изменяться только при обновлении ячейки (вводе данных) при этом действуют такие правила:
- Если текущий формат ячейки не «Текст», а вводимое значение является числом, датой или логической константой (TRUE, FALSE), то типу значения присваивается признак «число».
- Если текущий формат ячейки «Текст», либо вводимое значение начинается с символа «’» (апостроф), то типу значения присваивается признак «строка».
Необходимо заботиться только о двух вариантах типа значения: строка и число. Дата, время, проценты, логические константы и специальные форматы чисел хранятся с числовым признаком универсального типа.
Эта особенность Excel, с нашей точки зрения, также не является программной ошибкой. Но отловить такие нестандартные ситуации бывает чрезвычайно сложно. На практике они чаще всего встречаются при импорте информации из внешних баз данных.
Локализация
Интерфейс
В качестве примера не совсем корректного перевода в интерфейсе откройте диалог параметры Excel 2007 и выберите раздел «Формулы»:
Здесь почему-то использовано слово «узор», хотя кнопка служит для копирования, как формата, так и содержимого ячейки из соседней.
Таких примеров наберется достаточно много в различных версиях Excel. С одной стороны это вроде бы не влияет на работу, но негативно сказывается на общем впечатлении от программного продукта.
Формулы
Русская локализация формул Excel – это отдельная широко-обсуждаемая (в узких кругах специалистов) тема. Формулы рабочего листа имеют оригинальные и локализованные (переведенные) заголовки. Например, формула СУММ, используемая в русском Excel, в немецкой версии будет выглядеть как SUMME, а в английской (оригинальной) как SUM.
Excel хранит формулу в как в оригинальной, так и в локализованной версии – это можно увидеть, проверив свойства ячейки в VBA:
Решать проблемы Microsoft рекомендует с помощью специальных пакетов языковой поддержки:
К переводу формул на русский язык имеются большие претензии, высказываемые специалистами с момента первой локальной версии. Разделим недостатки перевода на несколько групп.
| Английское наименование функции | Русское наименование функции | Комментарий |
| COLUMNS ROWS |
ЧИСЛСТОЛБ ЧСТРОК |
разные префиксы «ЧИСЛ» и «Ч» |
| COUNT COUNTBLANK |
СЧЁТ СЧИТАТЬПУСТОТЫ |
существительное и глагол в русской версии |
| LEFT LEFTB |
ЛЕВСИМВ ЛЕВБ |
почему-то решили сэкономить 4 символа |
| DSUM DGET |
БДСУММ БИЗВЛЕЧЬ |
разные префиксы «БД» и «Б» только в русской версии, в английской всегда «D» |
2. Использования буквы «Ё», которая практически не используется при компьютерном наборе текстов:
| Английское наименование функции | Русское наименование функции | Комментарий |
| COUNT, COUNTIF | СЧЁТ, СЧЁТЕСЛИ | |
| ISEVEN | ЕЧЁТН |
3. Перевод на русский устоявшихся английских терминов:
| Английское наименование функции | Русское наименование функции | Комментарий |
| NPV | ЧПС | экономическое выражение |
| IRR | ВСД | экономическое выражение |
| BIN2HEX | ДВ.В.ШЕСТН | также и остальные функции конвертации |
4. Нелогичный неперевод на русский английских терминов. Если уж переводите все, то будьте последовательны:
| Английское наименование функции | Русское наименование функции | Комментарий |
| ABS | ABS | широко применяемая функция не только в математических задачах |
5. Использование смеси английского и русского в одной формуле. При вводе с клавиатуры необходимо переключать раскладку:
| Английское наименование функции | Русское наименование функции | Комментарий |
| IMABS | МНИМ.ABS | и другие функции работы с комплексными числами |
6. Слишком короткие наименования. Нелогичные аббревиатуры не способствуют запоминанию формулы:
| Английское наименование функции | Русское наименование функции | Комментарий |
| IPMT | ПРПЛТ | лучше ПЛАТЕЖИПРОЦЕНТОВ |
| MID | ПСТР | лучше ПОДСТРОКА |
| TRIM | СЖПРОБЕЛЫ | лучше назвать целиком СЖАТЬПРОБЕЛЫ |
7. Слишком длинные наименования. Совершенно непонятна причина, почему нельзя было для этих формул использовать сокращения:
| Английское наименование функции | Русское наименование функции | Комментарий |
| GETPIVOTDATA | ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ | похоже на издевательство над пользователем |
| SUBTOTAL | ПРОМЕЖУТОЧНЫЕ.ИТОГИ | очень полезная функция, почему не ПОДИТОГИ |
8. Неверный по смыслу перевод. Нет никакой интуитивной связи с действиями функций:
| Английское наименование функции | Русское наименование функции | Комментарий |
| TRUNC | ОТБР | по смыслу должно быть от слова «ОКРУГЛЯТЬ» |
| INDIRECT | ДВССЫЛ | что здесь может означать «ДВ»? |
| EFFECT | ЭФФЕКТ | перевод дословный, но не по смыслу функции |
| QUOTIENT | ЧАСТНОЕ | в русском языке понятие «частное» не подразумевает только целую часть числа |
9. Неверная работа формулы в различных локализациях, связанная с переводом. Это совершенно недопустимая ошибка с нашей точки зрения.
| Английское наименование функции | Русское наименование функции | Комментарий |
| INFO(«param») | ИНФОРМ(«параметр») | параметр указанный на английском языке (например, «osversion») работает в любой локальной версии, а на русском («версияос») только в русской версии, причем этот параметр не переводится автоматически |
| CELL(«param». ) | ЯЧЕЙКА(«параметр». ) | то же самое |
10. Просто смешные аббревиатуры (попробуйте произнести вслух):
| Английское наименование функции | Русское наименование функции | Комментарий |
| FVSCHEDULE | БЗРАСПИС | . |
| RANDBETWEEN | СЛУЧМЕЖДУ | . |
| TBILLPRICE | ЦЕНАКЧЕК | |
| MMULT | МУМНОЖ |
В качестве компенсации проблем с локализацией формул можно использовать средства интерфейса, облегчающие выбор функций и параметров (мастер формул, онлайн подсказки и пр.). Но это, во-первых, не особенно помогает в запоминании нелогичных аббревиатур. А, во-вторых, еще больше ставит под сомнение первоначальную идею перевода формул на другие языки.
При чтении статей нашего сайта можно использовать переключатель Язык формул, находящийся с левой стороны основного текста. Доступны русские и английские наименования функций.
Термины
Имеется несколько противоречивых терминов и в оригинальной документации Excel.
Самую большую путаницу вызывает, пожалуй, термин «Table» («Таблица»). Он употребляется в следующих случаях:
- Связанный диапазон ячеек, имеющих общую формулу. Такие таблицы вводятся через сочетание клавиш Ctrl+Shift+Enter. Сейчас больше употребляется термин «массив».
- Таблицы подстановки, использующие функцию ТАБЛИЦА().
- Устаревший редко используемый вариант таблиц для поиска информации через специальные функции типа DGET,DSUM и пр. В этом случае таблица представляет собой прямоугольный диапазон с заголовками столбцов, а также диапазоны для формирования выборок.
- Новый объект данных в Excel 2007. Такая таблица имеет общий стиль для строк и/или столбцов. Кроме того, есть возможность применять запись формул в специальной нотации со ссылками по имени таблицы. Изначально такая таблица была названа «List», затем все-таки переименована в «Table».
- В русском языке вообще любой файл Excel зачастую также называют «таблицей», что вносит дополнительную путаницу.
Интерфейс Excel 2007
Смирившись с навязанным новым пользовательским интерфейсом, появившимся в 2007м году, хочется высказать несколько замечаний, реально мешающих работе.
3. Найти некоторые диалоги старого меню без дополнительной справки чрезвычайно трудно. Так для примера попробуйте найти форму параметров группировки, вызываемую в старой версии через Данные \ Группы и структура \ Настройка. Честно признаюсь, сам не смог, пришлось «гуглить».
Высказав замечания, хочется отметить, что для новичков и не очень опытных пользователей интерфейс ленты действительно более привлекателен, чем поиски в старом меню.
[expert_bq id=»1570″]Если вы введете большое числовое значение в ячейку Excel, отформатированную в виде текста, она все равно будет отображаться в научной нотации, если ячейка недостаточно широка. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Это все еще кажется усеченным в моем тесте. Используя Excel 2013, я ввел следующее в ячейку 12345678901234567890. Когда я нажимаю клавишу ввода, это отображается как 1.23457E + 19, а базовое значение изменяется на 12345678901234500000, поэтому происходит потеря данных. Это не отвечает на оригинальный вопрос.Применение пользовательских форматов
- Возьмите длинный номер, например 1240800388917, и вставьте его в ячейку на новом листе.
- Формат ячейки по умолчанию в Excel является общим, поэтому строка представлена в научной нотации как 1.2408E + 12
- Щелкните правой кнопкой мыши по ячейке, выберите «Формат ячеек», установите формат «Текст».
По умолчанию в процентном формате число отображается без десятичных разрядов. Для установки необходимого количества десятичных разрядов процентного формата можно воспользоваться кнопками Увеличить разрядность или Уменьшить разрядность группы Число вкладки Главная (см. рис. 5 ).
Применение пользовательских форматов
В Excel имеется большое количество встроенных форматов, однако бывают случаи, когда необходимо создать собственный формат для более наглядной визуализации данных.
Создание пользовательских форматов
Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.
Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.
В поле Тип вы можете задать формат значения ячейки следующей строкой:
[цвет]»любой текст»КодФормата»любой текст»
Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)
Какие цвета можно применять
В квадратных скобках можно указывать один из 8 цветов на выбор:
Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.
Далее рассмотрим коды форматов в зависимости от типа данных.
Числовые форматы
| Символ | Описание применения | Пример формата | До форматирования | После форматирования |
|---|---|---|---|---|
| # | Символ числа. Незначащие нули в начале или конце число не отображаются | ###### | 001234 | 1234 |
| 0 | Символ числа. Обязательное отображение незначащих нулей | 000000 | 1234 | 001234 |
| , | Используется в качестве разделителя целой и дробной части | ####,# | 1234,12 | 1234,1 |
| пробел | Используется в качестве разделителя разрядов | # ###,#0 | 1234,1 | 1 234,10 |
Форматы даты
| Формат | Описание применения | Пример отображения |
|---|---|---|
| М | Отображает числовое значение месяца | от 1 до 12 |
| ММ | Отображает числовое значение месяца в формате 00 | от 01 до 12 |
| МММ | Отображает сокращенное до 3-х букв значение месяца | от Янв до Дек |
| ММММ | Полное наименование месяца | Январь — Декабрь |
| МММММ | Отображает первую букву месяца | от Я до Д |
| Д | Выводит число даты | от 1 до 31 |
| ДД | Выводит число в формате 00 | от 01 до 31 |
| ДДД | Выводит день недели | от Пн до Вс |
| ДДДД | Выводит название недели целиком | Понедельник — Пятница |
| ГГ | Выводит последние 2 цифры года | от 00 до 99 |
| ГГГГ | Выводит год даты полностью | 1900 — 9999 |
Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат «ДД.ММ.ГГГГ» отформатирует дату в привычный нам вид 31.12.2017, а формат «ДД МММ» преобразует дату в вид 31 Дек.
Форматы времени
Текстовые форматы
Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.
| ДО форматирования | ПОСЛЕ форматирования | Примененный формат |
|---|---|---|
| Россия | страна — Россия | «страна — «@ |
Создание пользовательских форматов для категорий значений
Все что мы описали выше применяется к ячейке вне зависимости от ее значения. Однако существует возможность указывать различные форматы, в зависимости от следующих категорий значений:
Для этого мы можем в поле Тип указать следующую конструкцию:
Формат положительных значений ; отрицательных ; нулевых ; текстовых
Соответственно для каждой категории можно применять формат уже описанного нами вида:
[цвет]»любой текст»КодФормата»любой текст»
В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.
Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».
Редактирование и копирование пользовательских форматов
Чтобы отредактировать созданный пользовательский формат необходимо:
- Выделить ячейки, формат которых вы хотите отредактировать.
- Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
- Изменить строку форматирования в поле Тип.
Распространить созданный пользовательский формат на другие ячейки можно следующими способами:
Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:
Форматы данных
- Если текущий формат ячейки не «Текст», а вводимое значение является числом, датой или логической константой (TRUE, FALSE), то типу значения присваивается признак «число».
- Если текущий формат ячейки «Текст», либо вводимое значение начинается с символа «’» (апостроф), то типу значения присваивается признак «строка».
Microsoft Excel позволяет отображать десятичные дроби в виде простых дробей. Дробный формат округляет десятичную дробь до ближайшей простой дроби в соответствии с выбранным типом. На рис.6 в строках 3:6 показано отображение чисел из строки 2 в различных дробных форматах.
| Английское наименование функции | Русское наименование функции | Комментарий |
| COUNT, COUNTIF | СЧЁТ, СЧЁТЕСЛИ | |
| ISEVEN | ЕЧЁТН |
