Как изменить число в Excel
Обновлено: 06.07.2024
Часто можно найти числа, хранящиеся в виде текста в Excel. Это приводит к неправильным вычислениям при использовании этих ячеек в функциях Excel, таких как СУММ и СРЗНАЧ (поскольку эти функции игнорируют ячейки, содержащие текстовые значения). В таких случаях необходимо преобразовать ячейки, содержащие числа в виде текста, обратно в числа.
Теперь, прежде чем мы двинемся дальше, давайте сначала рассмотрим несколько причин, по которым вы можете получить книгу, в которой числа хранятся в виде текста.
- Использование ‘ (апострофа) перед числом.
- Многие люди вводят апостроф перед числом, чтобы сделать его текстовым. Иногда это также происходит, когда вы загружаете данные из базы данных. Хотя это заставляет числа отображаться без апострофа, это влияет на ячейку, заставляя ее обрабатывать числа как текст.
- Получение чисел в результате формулы (например, LEFT, RIGHT или MID)
- Если вы извлекаете числовую часть текстовой строки (или даже часть числа) с помощью функций ТЕКСТ, результатом будет число в текстовом формате.
Теперь давайте посмотрим, как действовать в таких случаях.
Преобразование текста в числа в Excel
В этом руководстве вы узнаете, как преобразовать текст в числа в Excel.
Метод, который вам нужно использовать, зависит от того, как число было преобразовано в текст. Вот те, которые рассматриваются в этом руководстве.
- Используя параметр «Преобразовать в число».
- Измените формат с Текст на Общий/Число.
- Использование специальной вставки.
- Использование текста в столбцах.
- Использование комбинации функций VALUE, TRIM и CLEAN.
Преобразование текста в числа с помощью параметра «Преобразовать в число»
Когда к числу добавляется апостроф, формат числа изменяется на текстовый. В таких случаях вы заметите зеленый треугольник в верхней левой части ячейки.
В этом случае вы можете легко преобразовать числа в текст, выполнив следующие действия:
Это мгновенно преобразует все числа, сохраненные в виде текста, обратно в числа. Вы заметите, что после преобразования числа выравниваются по правому краю (в то время как при сохранении в виде текста они выравнивались по левому краю).
Преобразование текста в числа путем изменения формата ячейки
Когда числа отформатированы как текст, вы можете легко преобразовать их обратно в числа, изменив формат ячеек.
Вот шаги:
- Выделите все ячейки, которые вы хотите преобразовать из текста в числа.
- На главную –> Номер. В раскрывающемся списке "Числовой формат" выберите "Общий".
Это мгновенно изменит формат выбранных ячеек на Общий, а числа будут выровнены по правому краю. При желании вы можете выбрать любой из других форматов (например, числовой, валютный, бухгалтерский), что также приведет к тому, что значения в ячейках будут рассматриваться как числа.
Преобразование текста в числа с помощью специального параметра вставки
Чтобы преобразовать текст в числа с помощью параметра "Специальная вставка":
Преобразование текста в числа с использованием текста в столбец
Этот метод подходит в случаях, когда у вас есть данные в одном столбце.
Вот шаги:
Хотя вы по-прежнему можете обнаружить, что результирующие ячейки имеют текстовый формат, а числа по-прежнему выровнены по левому краю, теперь это будет работать в таких функциях, как СУММ и СРЗНАЧ.
Преобразование текста в числа с помощью функции ЗНАЧ
Вы можете использовать комбинацию функций VALUE, TRIM и CLEAN для преобразования текста в числа.
- Функция ЗНАЧ преобразует любой текст, представляющий число, обратно в число. удаляет все начальные и конечные пробелы.
- Функция CLEAN удаляет лишние пробелы и непечатаемые символы, которые могут появиться, если вы импортируете данные или загружаете их из базы данных.
Предположим, вы хотите преобразовать ячейку A1 из текста в числа, вот формула:
Если вы хотите применить это и к другим ячейкам, вы можете скопировать и использовать формулу.
Вам также могут понравиться следующие учебные пособия по Excel:
БЕСПЛАТНАЯ КНИГА EXCEL
Получите электронную книгу «51 совет по Excel», чтобы резко повысить свою продуктивность и быстрее выполнять работу
7 мыслей о «Преобразование текста в числа в Excel — пошаговое руководство»
Я вижу, что большинство советов очень полезны
Спасибо за это. На самом деле это не сработало, но натолкнуло меня на мысль умножить ячейки на 1, перетащить и использовать вместо них те, которые сработали
У меня есть числа из финансовой базы данных, отформатированные с суффиксом для базисных пунктов, например, 14bp . По умолчанию они считываются в Excel как текст.Я не нашел способа преобразовать их в числа, за исключением очень трудоемких методов: использовать текст в столбцах, чтобы избавиться от bp (что я могу сделать при начальном анализе, когда у меня еще есть пробелы, чтобы я мог использовать фиксированную ширину синтаксический анализ, но если я попытаюсь сделать это после первого синтаксического анализа, то Excel удалит начальные пробелы, и мне придется использовать синтаксический анализ с разделителями и указать, чтобы он рассматривал «b» как разделитель.) В противном случае я ограничиваюсь использованием левого (thenumber, len(thenumber)-2), который затем может потребоваться снова преобразовать в текст.
Есть ли у вас более быстрый способ сделать это? Я подумал, возможно, если бы я сделал bp настраиваемым числовым форматом, Excel распознал бы 14bp как число при чтении, но это не работает.
спасибо. теперь я могу суммировать числа, меняя их формат с текстового на числовой
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Числа, сохраненные в виде текста, могут привести к неожиданным результатам. Выберите ячейки, а затем щелкните, чтобы выбрать параметр преобразования. Или выполните следующие действия, если эта кнопка недоступна.
1. Выберите столбец
Выберите столбец с этой проблемой. Если вы не хотите преобразовывать весь столбец, вы можете вместо этого выбрать одну или несколько ячеек. Просто убедитесь, что выбранные вами ячейки находятся в одном столбце, иначе этот процесс не будет работать. (См. «Другие способы конвертации» ниже, если эта проблема возникает более чем в одном столбце.)
2. Нажмите эту кнопку
Кнопка «Текст в столбцы» обычно используется для разделения столбца, но ее также можно использовать для преобразования одного столбца текста в числа. На вкладке "Данные" нажмите "Текст в столбцы".
3. Нажмите Применить
Остальные шаги мастера преобразования текста в столбцы лучше всего подходят для разделения столбца. Поскольку вы просто конвертируете текст в столбце, вы можете сразу нажать кнопку «Применить», и Excel преобразует ячейки.
4. Установите формат
Нажмите CTRL + 1 (или + 1 на Mac). Затем выберите любой формат.
Примечание. Если вы по-прежнему видите формулы, которые не отображаются в виде числовых результатов, возможно, у вас включен параметр «Показать формулы». Перейдите на вкладку "Формулы" и убедитесь, что параметр "Показать формулы" отключен.
Другие способы преобразования:
Вы можете использовать функцию ЗНАЧ, чтобы вернуть только числовое значение текста.
1. Вставить новый столбец
Вставьте новый столбец рядом с ячейками с текстом. В этом примере столбец E содержит текст, хранящийся в виде чисел. Столбец F — новый столбец.
2. Используйте функцию ЗНАЧ
В одной из ячеек нового столбца введите =ЗНАЧ(), а в круглых скобках введите ссылку на ячейку, содержащую текст, хранящийся в виде чисел. В данном примере это ячейка E23.
3. Установите курсор здесь
Теперь вы заполните формулу ячейки вниз, в другие ячейки. Если вы никогда раньше этого не делали, вот как это сделать. Наведите курсор на правый нижний угол ячейки, пока он не примет вид плюса.
4. Нажмите и перетащите вниз
Нажмите и перетащите вниз, чтобы заполнить формулой другие ячейки. После этого вы можете использовать этот новый столбец или скопировать и вставить эти новые значения в исходный столбец. Вот как это сделать: Выберите ячейки с новой формулой. Нажмите CTRL + C.Щелкните первую ячейку исходного столбца. Затем на вкладке "Главная" нажмите стрелку под надписью "Вставить" и выберите "Специальная вставка" > "Значения".
Если описанные выше шаги не сработали, вы можете использовать этот метод, который можно использовать, если вы пытаетесь преобразовать более одного столбца текста.
Выберите пустую ячейку, в которой нет этой проблемы, введите в нее число 1 и нажмите клавишу ВВОД.
Нажмите CTRL + C, чтобы скопировать ячейку.
Выберите ячейки, в которых числа сохранены в виде текста.
На вкладке "Главная" нажмите "Вставить" > "Специальная вставка".
Нажмите «Умножить», а затем нажмите «ОК». Excel умножает каждую ячейку на 1 и при этом преобразует текст в числа.
Нажмите CTRL + 1 (или + 1 на Mac). Затем выберите любой формат.
В Excel вы можете форматировать числа в ячейках для таких вещей, как валюта, проценты, десятичные дроби, даты, номера телефонов или номера социального страхования.
Выберите ячейку или диапазон ячеек.
На вкладке "Главная" выберите "Число" в раскрывающемся списке.
Вы также можете выбрать один из следующих вариантов:
Нажмите CTRL + 1 и выберите "Число".
Щелкните правой кнопкой мыши ячейку или диапазон ячеек, выберите «Формат ячеек…» и выберите «Число».
Выберите средство запуска диалогового окна рядом с номером, а затем выберите номер.
Выберите нужный формат.
Числовые форматы
Чтобы просмотреть все доступные числовые форматы, нажмите кнопку вызова диалогового окна рядом с номером на вкладке "Главная" в группе "Число".
Описание
Числовой формат по умолчанию, который Excel применяет при вводе числа. По большей части числа, отформатированные в общем формате, отображаются именно так, как вы их вводите. Однако, если ширина ячейки недостаточна для отображения всего числа, в общем формате числа округляются до десятичных знаков. В общем числовом формате также используется научная (экспоненциальная) запись для больших чисел (12 и более цифр).
Используется для общего отображения чисел. Вы можете указать количество знаков после запятой, которое вы хотите использовать, хотите ли вы использовать разделитель тысяч и как вы хотите отображать отрицательные числа.
Используется для общих денежных значений и отображает символ валюты по умолчанию с цифрами. Вы можете указать количество знаков после запятой, которое вы хотите использовать, хотите ли вы использовать разделитель тысяч и как вы хотите отображать отрицательные числа.
Также используется для денежных значений, но выравнивает символы валюты и десятичные точки чисел в столбце.
Отображает порядковые номера даты и времени в виде значений даты в соответствии с указанным типом и языковым стандартом (местоположением). Форматы даты, начинающиеся со звездочки (*), соответствуют изменениям региональных настроек даты и времени, указанных в панели управления. На форматы без звездочки настройки панели управления не влияют.
Отображает порядковые номера даты и времени в виде значений времени в соответствии с указанным типом и языковым стандартом (местоположением). Форматы времени, начинающиеся со звездочки (*), соответствуют изменениям региональных настроек даты и времени, указанных в панели управления. На форматы без звездочки настройки панели управления не влияют.
Умножает значение ячейки на 100 и отображает результат с символом процента (%). Вы можете указать количество знаков после запятой, которое хотите использовать.
Отображает число в виде дроби в соответствии с указанным типом дроби.
Отображает число в экспоненциальном представлении, заменяя часть числа на E+n, где E (обозначающее Exponent) умножает предыдущее число на 10 в n-й степени. Например, в 2-значном научном формате 12345678901 отображается как 1,23E+10, что равно 1,23, умноженному на 10 в 10-й степени. Вы можете указать количество знаков после запятой, которое хотите использовать.
Обрабатывает содержимое ячейки как текст и отображает содержимое точно так, как вы его вводите, даже если вы вводите числа.
Отображает число в виде почтового индекса (почтового индекса), номера телефона или номера социального страхования.
Позволяет изменить копию существующего кода числового формата. Используйте этот формат для создания пользовательского числового формата, который добавляется в список кодов числовых форматов.Вы можете добавить от 200 до 250 пользовательских числовых форматов, в зависимости от языковой версии Excel, установленной на вашем компьютере. Дополнительные сведения о настраиваемых форматах см. в разделе Создание или удаление настраиваемого числового формата.
Вы можете применять различные форматы к числам, чтобы изменить их внешний вид. Форматы изменяют только способ отображения чисел и не влияют на значения. Например, если вы хотите, чтобы число отображалось в виде валюты, щелкните ячейку с числовым значением > Валюта.
Применение числового формата изменяет только способ отображения числа и не влияет на значения ячеек, которые используются для выполнения вычислений. Фактическое значение можно увидеть в строке формул.
Вот список доступных числовых форматов и способы их использования в Excel для Интернета:
Числовой формат по умолчанию. Если ячейка недостаточно широка, чтобы показать все число, этот формат округляет число. Например, 25,76 отображается как 26.
Кроме того, если число состоит из 12 или более цифр, в Общем формате значение отображается в экспоненциальном представлении.
Работает очень похоже на общий формат, но отличается тем, как он отображает числа с разделителями десятичных разрядов и отрицательные числа. Вот несколько примеров того, как оба формата отображают числа:
Также используется для денежных значений, но выравнивает символы валюты и десятичные точки чисел в столбце.
Показывает дату в следующем формате:
Показывает месяц, день и год в следующем формате:
Показывает порядковые номера даты и времени в виде значений времени.
Умножает значение ячейки на 100 и отображает результат с символом процента (%).
Используйте Увеличить десятичное число или Уменьшить десятичное число, чтобы указать нужное количество знаков после запятой.
Показывает число в виде дроби. Например, 0,5 отображается как ½.
Отображает числа в экспоненциальной записи, заменяя часть числа на E+n, где E (экспонента) умножает предыдущее число на 10 в n-й степени. Например, в 2-значном научном формате 12345678901 отображается как 1,23E+10, что равно 1,23, умноженному на 10 в 10-й степени. Чтобы указать количество десятичных разрядов, которые вы хотите использовать, примените Увеличить десятичное число или Уменьшить десятичное число.
Обрабатывает значение ячейки как текст и отображает его точно так, как вы его вводите, даже если вы вводите числа. Узнайте больше о форматировании чисел как текста.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Excel 2010 предоставляет различные числовые форматы, которые можно применять к значениям (числам), которые вы вводите на листе, чтобы упростить интерпретацию данных. Эти числовые форматы включают денежный, бухгалтерский, процентный, дату, время, дробь и научный формат, а также несколько специальных форматов.
Когда вы вводите значение в ячейку, Excel делает предположение о том, что это за число и как оно должно быть отформатировано. То, как вы вводите значения в рабочий лист Excel 2010, определяет тип числового формата, который Excel назначает ему. Вот несколько примеров:
Валюта: если вы вводите финансовое значение со знаком доллара и двумя десятичными знаками, Excel присваивает ячейке числовой формат валюты вместе с записью.
Проценты: если вы вводите значение, представляющее процент в виде целого числа, за которым следует знак процента без десятичных знаков, Excel присваивает ячейке числовой формат в процентах.
Даты: если вы вводите дату (даты тоже являются значениями), которая следует за одним из встроенных числовых форматов Excel, например, 10.11.09 или 10-ноября-09, программа присваивает формат даты. который следует шаблону даты.
Даже если вы действительно хорошо печатаете и предпочитаете вводить каждое значение именно так, как вы хотите, чтобы оно отображалось на листе, вы все равно используете числовые форматы, чтобы значения, рассчитанные по формулам, соответствовали другим введенным вами значениям. Excel применяет общий числовой формат ко всем вычисляемым значениям, а также ко всем введенным вами значениям, которые не соответствуют ни одному из других числовых форматов Excel. Общий формат удаляет все ведущие и конечные нули из записей. Это очень затрудняет выстраивание чисел в столбец по десятичным знакам. Единственное решение — отформатировать значения в другом числовом формате.
Выберите ячейки, содержащие числа, которые вы хотите отформатировать.
Выберите нужный числовой формат одним из следующих способов:
Выберите формат из раскрывающегося списка в группе «Число» на вкладке «Главная».
Нажмите кнопку запуска диалогового окна «Число» в правом нижнем углу группы «Число» на вкладке «Главная» и выберите нужный формат на вкладке «Число» диалогового окна «Формат ячеек».
Используйте сочетание клавиш Ctrl+1, чтобы открыть диалоговое окно «Формат ячеек». Просто имейте в виду, что сочетание клавиш — это нажатие клавиши Ctrl и клавиши цифры 1, а не функциональной клавиши F1.
Об этой статье
Эта статья взята из книги:
Об авторе книги:
Грег Харви, доктор философии, президент Mind Over Media, Inc. Он является автором всех выпусков книг Excel для чайников, Excel All-in-One для чайников, Excel Workbook для чайников, и Краткий справочник по Windows для чайников. Он также является опытным педагогом.
В Microsoft Excel доступно несколько форматов при работе с числовыми данными.
Тем не менее, приложение также предлагает возможность создавать собственные числовые форматы. То есть, если какое-либо из встроенных предложений не соответствует вашим потребностям.
В этой статье мы кратко рассмотрим идею числовых форматов в Excel.
Затем мы сосредоточимся на том, как настроить их в соответствии с вашими конкретными потребностями.
*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы все равно можете выполнить те же шаги.
< /p>
Каспер Лангманн, соучредитель Spreadsheeto
Оглавление
Получите БЕСПЛАТНЫЙ файл с упражнениями
Прежде чем начать:
В этом руководстве вам понадобится набор данных для практики.
Я включил один для вас (бесплатно).
Загрузите прямо ниже!
Загрузите БЕСПЛАТНЫЙ файл упражнения
Форматирование чисел нужно не только для внешнего вида
В Microsoft Excel можно выбирать из множества числовых форматов.
Отформатировать данные для просмотра всегда важно.
< /p>
Каспер Лангманн, соучредитель Spreadsheeto
Но это не основная причина форматирования чисел. Не все числовые форматы одинаковы по форме или функциям.
Если вы работаете с процентами, целые числа или денежный формат работать не будут.
Отформатировать данные с использованием числовых форматов очень просто.
Первое, что нужно сделать, это выделить данные. Затем перейдите в группу «Число» на вкладке «Главная».
По умолчанию числовые данные, которые вы вводите, имеют общий формат, пока вы его не измените.
Следующее, что нужно сделать, — нажать на маленькую стрелку вниз рядом с пунктом "Общие".
Если вы нажмете «Число», Excel изменит ваши значения в этом формате. Теперь вы должны увидеть свои данные с добавлением двух знаков после запятой.
Чтобы удалить эти десятичные разряды, выберите данные и снова перейдите в раздел "Число" на вкладке "Главная".
Затем можно несколько раз нажать кнопку "Уменьшить десятичную дробь".
Вы также можете изменить числовой формат своих данных на денежный. Выберите Валюта из раскрывающегося списка.
Это изменит ваши данные соответствующим образом.
Вы можете получить еще более подробную информацию о форматировании чисел. Нажмите маленькую стрелку под углом в правом нижнем углу группы «Число».
Это расширит группу Числовой формат.
Откроется диалоговое окно «Формат ячеек». Здесь вы найдете множество вариантов.
Давайте рассмотрим пример с данными даты. Допустим, у вас есть дата в следующем формате.
Выберите ячейку, содержащую дату.
Откройте диалоговое окно «Формат ячеек» и выберите «Дата» в списке «Категории».
Обратите внимание на множество вариантов формата в списке Тип. Еще одна вещь, на которую следует обратить внимание, — это пример, показанный выше. Тип.
Это дает вам быстрый взгляд на ваши фактические данные в выбранном типе формата.
Это будет меняться по мере того, как вы переключаетесь между различными типами, что дает вам быстрый предварительный просмотр. Давайте выберем длинный формат даты или второй вариант сверху.
Мы можем увидеть предварительный просмотр в окне Sample. Мы должны нажать OK, чтобы переформатировать наши данные.
Помимо встроенных параметров: пользовательское форматирование
Диалоговое окно «Формат ячеек» предлагает множество готовых опций. Доступные категории форматов и варианты типов выходят далеко за рамки базовых.
Но если то, что доступно, по-прежнему не соответствует вашим требованиям, вы всегда можете создать собственный формат.
Давайте рассмотрим набор чисел.
Мы начинаем с открытия диалогового окна «Формат ячеек», как и раньше. На этот раз мы выберем Custom из списка категорий.
Затем мы выбираем из списка Тип.
< /p>
Каспер Лангманн, соучредитель Spreadsheeto
Если мы хотим создать свой собственный формат валюты, первое, что мы можем сделать, это добавить знак доллара.
Мы можем ввести это в текстовое поле Type.
Одна из вещей, которую вы можете настроить, — это цвет шрифта.
Для этого перед числовым форматом следует указать название цвета, заключенное в квадратные скобки.
Мы добавляем форматирование синего шрифта к нашему текущему формату.
Предварительный просмотр в окне «Образец» не показывает изменения цвета.
Нам нужно нажать OK, чтобы увидеть изменения.
До сих пор мы работали только с числовыми форматами, состоящими из одного раздела кода.
Используя точку с запятой для разделения, у нас может быть до четырех таких разделов кода.
Это означает, что мы можем сделать что-то вроде форматирования как положительных, так и отрицательных чисел.
Например, мы можем захотеть, чтобы наши отрицательные числа отображались красным шрифтом. Это будет хороший контраст с синим цветом, который мы установили для положительных чисел.
В скобках также можно указать отрицательные числа.
Мы можем пойти еще дальше и установить третий раздел кода для нулевых значений.
Это изменит наш список числовых значений на нечто более осмысленное.
Это отличная демонстрация гибкости пользовательского форматирования чисел. Но давайте сделаем еще один шаг вперед.
Пользовательское форматирование позволяет даже добавлять текст в ваши форматы.
Вы можете быть еще более описательным в зависимости от того, положительны ваши значения или отрицательны.
Это можно сделать, добавив текст, заключив пользовательскую строку в двойные кавычки.
Мы добавили текст «Прибыль» в конец наших положительных значений. Мы также добавили текст «Убыток» в конец наших отрицательных значений.
Обратите внимание на пробелы после открывающей двойной кавычки и первой буквы каждой строки.
Без этих начальных пробелов не было бы пробела между числовым значением и текстом.
Читайте также: