Как преобразовать числа из текстового формата в числовой – ЭКСЕЛЬ ХАК

Преобразовать числа в текстовом формате в настоящие очень просто. В этой статье вы узнаете как конвертировать такие числа в настоящие.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

1. Числовой формат ячейки

Данные в ячейках могут иметь различный смысл: текст, числа, дроби, даты, проценты. Вот пример: какой процент от 20000 составляет 4000?

Но догадаться, что речь идет о процентах, можно только из объяснения или из подписи.

Если мы видим, что рядом с числом стоит значок «₽», то сразу понятно, что речь идет о рублях, а значок «%» говорит о процентах. Если нам надо складывать минуты, то речь идет о 60-тиричной системе счисления, а если часы – то 12-тиричной системе счисления.

Excel, как программа, работает с числами, поэтому важно указать программе какой формат числе сейчас в работе.

Задается формат ячейки на ленте Главная → группа команд Число → кнопка выпадающего меню Числовой формат:

числовой формат

  1. Общий – только для чисел и текста
  2. Числовой – для чисел с возможностью десятичных дробей
  3. Денежный – понятно, что рубли фунты, тугрики. Причем назначение формата британский фунт будет учитывать всю сложность британской системы счисления денежных знаков
  4. Финансовый – я не финансист и поэтому не знаю, чем этот формат отличается от денежного
  5. Краткий формат даты – день, месяц и год показаны числами
  6. Длинный формат даты – месяц прописан полностью с учетом нашей русской орфографии, за что большое спасибо переводчикам
  7. Время – сразу обращаем внимание на синтаксис написания времени – часы, минуты и секунды разделяются двоеточием
  8. Процентный – пояснять не надо
  9. Дробный – показывает простые дроби
  10. Экспоненциальный ‒ используется для отображения числа в экспоненциальном представлении. При этом происходит замена части числа на E + n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n (честно списала с официального руководства пользователя)
  11. Текстовый – пояснять не надо.

Если вы не назначили никакого формата данным, тогда будет отображаться формат Общий:

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

  1. Выделить ячейку (или диапазон ячеек), к которым нужно присвоить формат.
  2. Нажать на кнопке со стрелкой в поле списка Формат чисел и щелкнуть на нужном формате.

числовой формат

Команды с присвоением формата Денежный и Процент находятся на ленте Главная → группа команд Число:

числовой формат

  1. Денежный
  2. Процент
  3. Формат с разделителем
  4. Увеличить разрядность (увеличить количество знаков после запятой в десятичной дроби)
  5. Уменьшить разрядность (уменьшить количество знаков после запятой в десятичной дроби)

Формат с разделителем очень полезная опция – позволяет создать в числах промежутки между разрядами:

числовой формат

Обратите внимание на строку формул – в ней просто число без всяких разделителей.

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

Если мы сложим 54+32, то получим в результате 86, а если мы сложим 54 мин+32 мин, то получим 1 час 26 минут. Так что идентификатор ячейки очень важен при вычислениях. Вот небольшой кусочек из моего отчета по созданию дистанционного курса:

числовой формат

Поскольку формат ячеек «Время», то общая длительность курса подсчитана в шестидесятеричной системе (1 час = 60 минут и 1 минута = 60 секунд спасибо древним шумерам за изобретение этой системы счисления).

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

числовой формат

  1. Команда «Формат ячеек» из контекстного меню
  2. Команда «Другие форматы ячеек» из выпадающего меню «Числовой формат»
  3. Лента Главная → группа команд Число → кнопка группы

Рассмотрим пример числового формата Дата:

числовой формат

Как видите, выбор форматов даты очень обширен.

А вот пример выбора денежного формата:

числовой формат

Причины, почему не меняется форматирование в Excel

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

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

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

Типы файлов в Excel

При помощи табличного редактора Excel можно открыть не только файлы с расширением xls и xlsx. Это приложение может открывать более 25 различных типов файлов. Ниже приведен их список с соответствующими расширениями.

типы данных в excel

Перед началом работы добавьте «Изменить формат даты» в Excel

«Изменить формат даты» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

download

Скачать XLTools для Excel

– пробный период дает 14 дней полного доступа ко всем инструментам.

Как конвертировать текст в числа в Excel

Конвертация текста в числа через меню ошибки

Если в левом верхнему углу ячеек с числовыми значениями появился зеленый треугольник, то:

  • Выделите диапазон таких ячеек
  • Кликните по иконке с желтым ромбом и восклицательным знаком
  • В выпадающем списке выберите пункт меню “Преобразовать в число”

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

Применять этот метод следует на небольшое количество ячеек. Не рекомендую использовать этот метод для конвертации сотен, тысяч строк. Excel может “зависнуть” в процессе конвертации.

Как конвертировать текст в число с помощью смены формата ячейки

Есть еще один способ изменить формат значений текстовой ячейки на числовой. Сделать это можно с помощью смены формата ячейки на числовой. Как это сделать:

  • Выделите левой клавишей мыши диапазон ячеек
  • На панели инструментов перейдите на вкладку “Главная”
  • Перейдите в подгруппу “Число” и в выпадающем списке выберите формат “Числовой”

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

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

2. Копирование форматов в другие ячейки

Применить к данным формат, уже использованный в какой-то части рабочего листа, можно с помощью кнопки «Формат по образцу» (выделяем ячейку как образец для формата → лента Главная → группа команд Буфер обмена → щелчок ЛМ по команде Формат по образцу → щелчок ЛМ по любой другой ячейке):

Формат по образцу

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

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

См. также

Форматирование чисел в виде денежных единиц в Excel

Форматирование чисел

3. Пользовательский формат

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

Для создания собственного формата открываем диалоговое окно «Формат ячейки» и выбираем «(все форматы)»:

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

Не будем пугаться, а попробуем разобраться.

0 (ноль) одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 135 применить формат 0000, то получится 0135, а если к числу 3,14159265358979 применить формат 0,00 – получится 3,14
(решетка) одно необязательное знакоместо – примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
(пробел) используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.
[ ] в квадратных скобках указывается цвет шрифта.
Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой

Сделаем пользовательский формат для отображения мобильных телефонов. В поле «Тип» записываем выражение:

Тип +7пробел(000)пробел000-00-00

Получаем:

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

Это мой номер телефона, звоните – буду рада пообщаться.

Что касается текста, то существует правило:

Понять и запомнить! Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) – надо обязательно заключать в кавычки
Тип # ##0″ кг»

Перед «кг» ставим пробел.

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

Если мы хотим добавить десятичные дроби, то:

Тип # ##0,00″ кг»

То получим в результате:

75, 00 кг
34,00 кг
54,00 кг

К ячейке можно применить до 4 видов числовых форматов:

1 позиция 2 позиция 3 позиция 4 позиция
Если в ячейке положительное число Если в ячейке отрицательное число Если в ячейке «0» Если в ячейке текст

Форматы разделяются точкой с запятой (и никаких пробелов!)

Например. Если в поле Тип мы введем:

Тип «да;»нет»;»да»

то получим такой результат:

Усложним задачу и поработаем с цветом:

Тип [Красный]+0″°С»;[Синий]-0″°С»;[Зеленый]0″°С»

Получаем такую красоту:

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

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

Понять и запомнить! Пользовательский формат можно удалить, встроенный формат – нельзя!

Теперь вы сможете:

  1. Пользоваться числовым форматом
  2. Копировать формат по образцу
  3. Создавать собственный формат чисел

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

Format-yacheek-v-Microsoft-Excel-1.png

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

Как конвертировать текст в числа с помощью функции ЗНАЧЕН()

Для конвертации числовых значений в текстовом формате в числа часто используют функцию ЗНАЧЕН(). Функция предназначена для перевода текста в числа. Напишите функцию в адресной строке и укажите ссылку на ячейку для конвертации.

Эта функция работает со всеми форматами ячеек.

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

Обратите внимание!

«Преобразовать текст в число Excel» — не совсем правильная формулировка. Правильнее будет сказать: «Преобразование из текстового формата в числовой».

Автор: Пашкова Екатерина Ивановна, преподавать основ компьютерной грамотности, офисных программ.

Автор скриншотов: Белицкий Алексей Викторович, учитель МКОУ «СОШ №7», с. Пелагиада, Шпаковский район, Ставропольский край.

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

Комментировать Разместить на своем сайте

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте?

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Кстати, такая возможность есть
на всех страницах нашего сайта

Сведения о вопросе

msoffice.new.svg

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Разберитесь с установленными на вашем устройстве, Разделителями целой и дробной части как в Операционной системе, так и в программе Excel.

В смысле? У меня в операционной системе разделитель целой и дробной части — запятая, разделитель разрядов — пробел, а в Экселе — точка как разделитель целой и дробной части, а запятая — как разделитель разрядов. Так удобнее. По крайней мере мне. При этом если в операционной системе поставлю точку как разделитель целой и дробной части, а запятую — как разделитель разрядов, то запросы в PowerQuery не могут определить числовой формат в файлах и выдают ошибку.

Так как, все-таки, должно быть, что это проклятый дата-формат не вылезал постоянно?

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

На данном Форуме, поддержка оказывается Домашним Пользователям

2. Дополните, Вы используете Excel на вашем сервере? Какие на сервере и в Excel у Вас установлены разделители?

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Извините, не знал о разделении на корпоративщиков и домашних пользователей.

По сути вопроса.

Офис стоит на ноутбуке (Вин10х64_Ru). Подписка Офис365Про+.

Разделители в операционной системе: по умолчанию (дробная часть отделяется запятой, разряды — пробелом).

Разделители в Эксель: галочка «использовать системные разделители» снята, вручную выставлены разделители целой и дробной части — точка, разделители разрядов — запятая.

Указанная проблема, видимо, не наблюдается у тех, кто использует в Экселе системные разделители. Я правильно Вас понял.

Спасибо за ответ. Все свои будущие вопросы буду адресовать на форум ТекНет.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв.

Дело в том, что воспроизводить эту проблему мне не удается наверняка.

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

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

Эта проблема повторяется периодически и я не заметил никакой зависимости. Сегодня нормально, завтра — ОК, а через месяц — бах и изменился формат. Меняю все взад и продолжаю работать, затем снова трансформация. Зачем, почему — причина совершенно непонятна.

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

Этот ответ помог 2 польз.

Это помогло устранить вашу проблему?

К сожалению, это не помогло.

Великолепно! Спасибо, что пометили это как ответ.

Насколько Вы удовлетворены этим ответом?

Благодарим за отзыв, он поможет улучшить наш сайт.

Вкладка Выравнивание

Переходим на вкладку Выравнивание. Здесь задается расположение числа или текста внутри ячейки: по вертикали, горизонтали, относительно центра, по направлению и т.д.

lazy_placeholder.gif

Расположение и назначение команд, как и в других окнах Excel, интуитивно понятно. Достаточно прочитать названия. Тут выравнивание по вертикали, тут – горизонтали. Можно объединить несколько ячеек в одну или длинное название переносить по словам. Обращу лишь внимание на пару интересных моментов.

Когда в ячейку вносится число, оно автоматически «прилипает» к правому краю, а текст – к левому краю. Если число будет в левом столбце, а текст в следующем справа, то содержимое обоих столбцов будет выглядеть примерно так.

lazy_placeholder.gif

Читать такое не удобно (все сливается и нужно присматриваться). Поэтому правильно сделать отступ текста от левого края. Для этого достаточно выбрать в списке по горизонтали пункт по левому краю (отступ) и установить рядом в окошке значение отступа. См. рисунок ниже.

lazy_placeholder.gif

Теперь глазу намного приятнее.

lazy_placeholder.gif

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

И еще один полезный совет. При создании таблицы в шапке часто объединяют ячейки, чтобы общее название «накрывало» сразу несколько столбцов. К примеру, как на картинке ниже.

lazy_placeholder.gif

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

lazy_placeholder.gif

Получится тот же эффект, только ячейки не будут объединены.

lazy_placeholder.gif

Рекомендую взять на вооружение этот способ форматирования. В конце статьи находится видео о том, как расположить надпись в середине диапазона, не используя объединение ячеек.

Значительная часть команд из вкладки Выравнивание также вынесена на ленту.

lazy_placeholder.gif

Следующие вкладки Шрифт, Граница, Заливка имеют понятное назначение и вид. С их помощью выбирается шрифт, цвет, размер и т.д.

lazy_placeholder.gif

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

lazy_placeholder.gif

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

lazy_placeholder.gif

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

Самые часто используемые команды есть на ленте.

lazy_placeholder.gif

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

При использовании форматирования следует помнить о том, что слишком пестрая раскраска сбивает с толку и рассеивает внимание. Поэтому формат желательно использовать только для увеличения читабельности и привлечения внимания к наиболее важной информации. Увлекаться не стоит.

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

lazy_placeholder.gif

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

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



Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...