Bfsoft.ru

Программы, сервисы, полезные советы о компьютере и интернете
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как в Excel преобразовать число в текст

Как в Excel преобразовать число в текст

Зеленые треугольники, которые обозначают ячейки с числами, хранящимися как текст

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

1. Выделите столбец

Выделенный столбец

Выделите столбец с такими ячейками. Если вы не хотите преобразовывать весь столбец, можно выбрать одну или несколько ячеек. Ячейки должны находиться в одном и том же столбце, иначе этот процесс не будет работать. (Если такие ячейки есть в нескольких столбцах, см. раздел «Другие способы преобразования» ниже.)

2. Нажмите эту кнопку

Вкладка "Данные", кнопка "Текст по столбцам"

Кнопка «столбцы» обычно применяется для разделения столбцов, но ее также можно использовать для преобразования столбца текста в числа. На вкладке Данные нажмите кнопку Текст по столбцам.

3. Нажмите кнопку «Применить»

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

4. Задайте формат

Выбран денежный формат

Нажмите клавиши CTRL+1 (или Изображение значка кнопки команд в Mac+1 на Mac). Выберите нужный формат.

Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы. Откройте вкладку Формулы и отключите параметр Показать формулы.

Другие способы преобразования

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

1. Вставьте новый столбец

Новый столбец рядом с числами в виде текста

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

2. Примените функцию ЗНАЧЕН

Ячейка F23 с формулой =ЗНАЧЕН(E23) и результат 107,71

В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23.

3. Наведите указатель мыши

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

Теперь нужно заполнить формулу вниз. Вот как это сделать: Наведите указатель на правый нижний угол ячейки, чтобы он принял вид знака плюс (+).

4. Щелкните и перетащите вниз

Указатель перетащен вниз, ячейки заполнены формулой

Щелкните и перетащите вниз, чтобы заполнить формулой другие ячейки. После этого можно будет использовать новый столбец или скопировать и ввести новые значения в исходный столбец. Вот как это сделать: выберем ячейки с новой формулой. Нажмите CTRL+C. Щелкните первую ячейку исходного столбца. Затем на вкладке «Главная» щелкните стрелку под кнопкой «Вировать» и выберите «Специальная>».

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

Выделите пустую ячейку, в которой нет этой проблемы, введите в нее цифру 1 и нажмите клавишу ВВОД.

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

Выделите ячейки с числами, которые сохранены как текст.

На вкладке Главная щелкните Вставить и выберите пункт Специальная вставка.

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

Нажмите клавиши CTRL+1 (или Изображение значка кнопки команд в Mac+1 на Mac). Выберите нужный формат.

Сумма прописью в Excel

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

Можно ли сделать такую формулу? Можно! А главное, что вам делать ничего не придется (только скопировать), потому что я уже сделал её за вас! Но обо всем по порядку.

Сопоставление чисел и их эквивалентов прописью

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

  • в зависимости от того, на какую цифру заканчивается число, его падеж и число могут быть:
    • единственное, именительный (двадцать один рубль)
    • единственное, родительный (двадцать два/три/четыре рубля)
    • множественное, родительный (двадцать, двадцать пять/шесть/семь/восемь/девять рублей)

    И это далеко не все нюансы. Есть ли варианты, которые их все учитывают?

    Число прописью в Excel — формула

    Большинство руководств в интернете предлагают решения на VBA и надстройки. Также предлагаются формулы на основе именованных диапазонов.

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

    В случае с именованными диапазонами — нужно будет создавать их заново (а их около десятка!).

    Число прописью в Excel — примеры сопоставления

    Возможна ли формула без применения VBA, макросов и надстроек?

    Черт возьми, да! Я долго размышлял, прежде чем ее сделать, но мне это удалось.

    Вот эта формула! Вставьте ее в любую ячейку, кроме A1, т.к. она преобразует значение этой самой ячейки:

    Тройной клик по формуле выделит ее целиком.

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

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

    Сумма прописью с копейками в Excel

    Для записи суммы прописью с копейками по всем канонам, нужно соблюсти ряд условий:

    • Рубли пишутся словами, первая буква обычно заглавная
    • Словоформа слова «рубль» должна соответствовать количеству
    • Копейки пишутся цифрами, но падеж и число слова «копейка» тоже должны соответствовать числу

    Пример написания суммы прописью с копейками в Excel

    Сумма прописью с копейками в Excel — пример таблицы соответствия суммы и текста

    Трудно в это поверить, но и такая формула тоже существует. Аналогично, без применения VBA, макросов и надстроек.

    Формула огромна, но замечательно справляется с любыми суммами до миллиарда. Работать будет в любой версии Excel от 2007 и старше — в 2003 не была доступна такая глубина вложений функций.

    Вот и она, просто скопируйте ее, и она преобразует числовое значение ячейки A1 в сумму прописью с копейками.

    В русских рублях, все буквы строчные

    Формула выводит сумму в рублях, копейки оставляет цифрами. Если регистр первой буквы не важен, можно использовать её.

    В русских рублях, первая буква заглавная

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

    Тройной клик по формуле выделит ее целиком.

    В русских рублях с копейками прописью

    Если нужно, чтобы копейки тоже были написаны прописью, то формула будет еще чуть длиннее, такой:

    В белорусских рублях

    Тройной клик по формуле выделит ее целиком.

    Если вам не нужно делать первую букву заглавной, просто «оберните» всю эту гигантскую формулу функцией СТРОЧН.

    Сумма прописью в Excel — UDF-функции

    Для тех, кто не готов пользоваться огромными формулами, есть простое решение — установить надстройку, в которой присутствует код, скрывающий все эти вычисления «под капот». Одна из таких — разработанная мной надстройка !SEMTools.

    В ней для решения задачи преобразования числа и суммы в их прописной вариант есть 2 лаконичные функции:

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

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

    Сумма и число прописью в Гугл таблицах

    Прелесть формул выше в том, что они с минимальными модификациями портируются в Google Spreadsheets, где их замечательно можно использовать.

    Ниже пример таблицы с указанными формулами:

    Посмотреть сам файл и скопировать к себе можно по ссылке:

    Сумма прописью — формула для Openoffice Calc

    У формулы выше были проблемы с импортом в Openoffice, т.к. тот в отличие от Excel (2007 и старше), поддерживает максимум 42 уровня вложенности в формулах, а не 64.

    Поэтому пришлось полностью переделать логику формулы и сделать ее сложной формулой массивов.

    Так она выглядит в Excel:

    А так — после импорта в Openoffice Calc:

    Макрос

    Для тех, кто ценит время и не готов прибегать к функциям, в надстройке для Excel !SEMTools доступны макросы преобразования в 1 клик.

    Вызов процедуры находится в меню «Изменить символы» в группе «ИЗМЕНИТЬ» на панели надстройки. Макрос преобразует числа на месте, превращая их в текст. Можно выделить как одну, так и несколько ячеек:

    Функционал доступен в полной версии надстройки.

    Перевод чисел прописью в другие падежи

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

    Например, «сумма в размере пятисот тысяч трехсот трех рублей». Надстройка !SEMTools справляется и с этой задачей — смотрите раздел склонение по падежам в Excel.

    У этой записи 61 комментариев

    Нужно доработать функцию для другой валюты? Нашли недочеты или есть предложения? Оставляйте в комментариях!

    Отличное решение! Совершенно неожиданное.
    Вы избавились от кучи именованных диапазонов.
    А с числом 16 Вы специально сделали провокацию?
    Большое спасибо за что-то свежее.

    Не специально, но идея неплохая 🙂
    Спасибо, поправил.

    А можно в этой формуле сделать, так чтобы сумма начиналась с маленькой буквы и вместо 00 копеек было просто ноль копеек?

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

    Добавил вариант с копейками прописью в статью. Чтобы сделать все с маленькой буквы, можно обернуть ее функцией СТРОЧН.

    большое спасибо, очень помогло. для долларов США и евро было бы отлично!)

    5 способов преобразования текста в числа в Excel

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

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

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

    Является ли текст данных ячейки или числа?

    Есть несколько способов узнать, отформатирован ли номер или набор чисел в столбце или строке как текст в Excel.

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

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

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

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

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

    1. Преобразовать в число

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

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

    1. Выберите этот символ. В раскрывающемся списке выберите «Преобразовать в число».

    Это обновит все выбранные вами текстовые числа до общего числового формата данных.

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

    2. Использование текста в столбце

    Еще один простой способ преобразования текста в числа в Excel — это одновременное преобразование целого столбца значений. Вы можете сделать это, используя функцию Text to Column.

    1. Выберите весь столбец данных, которые вы хотите преобразовать из текста в числа.

    1. Выберите «Данные» в меню, а затем выберите «Текст в столбцы» в разделе «Инструменты данных» на ленте.

    1. В окне мастера оставьте выбранным разделитель по умолчанию и выберите Далее.

    1. На следующей странице мастера оставьте выбранную вкладку по умолчанию и снова выберите Далее.

    1. Наконец, на последней странице мастера убедитесь, что в разделе Формат данных столбца выбрано Общее. В поле «Место назначения» вы можете выбрать новый столбец, в который нужно перенести числовые данные, или просто оставить текущий выбранный столбец без изменений. Выберите Готово.

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

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

    3. Изменение формата ячейки

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

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

    Выберите меню «Главное» и в группе «Число» на ленте выберите раскрывающийся список с текстом в нем.

    1. Вы увидите список форматов на выбор. Выберите Общие, чтобы преобразовать в числовой формат. Или вы можете выбрать Число, Валюта, Учет или Процент, если вы хотите, чтобы эти конкретные числовые форматы применялись к вашим числовым данным.

    4. Использование значений вставки

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

    1. Выберите группу пустых ячеек, в которую вы хотите поместить вывод числовых данных. Выберите «Формат ячеек» во всплывающем меню.

    1. В открывшемся окне убедитесь, что в качестве числового формата выбран «Общий», и нажмите «ОК».

    1. Выберите весь столбец ячеек, который вы хотите преобразовать из текста в числа, щелкните правой кнопкой мыши и выберите «Копировать».

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

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

    5. Использование функции VALUE

    В Excel есть специальная функция, которая преобразует число в формате текста в числовое значение. Это функция VALUE.

    Чтобы использовать эту функцию, выберите ячейку, в которую вы хотите преобразовать число, и введите:

    Замените «G2» выше на ячейку с номером, который вы хотите преобразовать. Если вы конвертируете целый столбец чисел, начните только с первой ячейки.

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

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

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

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

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

    Преобразование чисел в текст в Excel

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

    Шаги для преобразования чисел в текст в Excel

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

    Шаг 1 — Двойным щелчком мыши выберите ячейку, для которой вы хотите изменить данные, и введите апостроф перед числом, введенным в ячейку.

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

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

    Шаг 3 — Выберите ячейку, которая уже изменилась в текст, при этом выделите ячейку со значком окна сообщения с раскрывающимся списком, который появляется слева

    • Как только вы укажете на значок окна сообщения, появится соответствующий текст, который показывает, преобразуется ли число в ячейке в текст?

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

    Шаг 4 — Выбрав выпадающую стрелку, можно изменить текст на его старую форму, используя Преобразовать в число.

    Примеры для преобразования чисел в текст в Excel

    Это следующие примеры для преобразования чисел в текст:

    1. Использование общей формулы

    2. С помощью команды форматирования ячеек

    3. Использование дополнительных инструментов

    Пример № 1

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

    = Текст (значение, формат_текста)

    • Где значение относится к ячейке, которая содержит данные, которые вы хотите преобразовать. И следующая часть format_text представляет, в каком формате вы хотите увидеть данные.
    Пример № 2
    • В ячейку C2 введите формулу = текст (B2, «0»).

    • Затем нажмите клавишу ввода.

    • Перетащите и заполните диапазон, который вы хотите применить. Результаты показаны ниже.
    • После применения формулы значения вставляются в один лист. Числа изменены в текстовом формате. Зеленая индикация в верхнем углу ячейки обычно показывает, когда вы ввели текстовое значение в ячейку. Или когда вы меняете формат любых данных на текстовый формат.

    Пример № 3
    • Чтобы изменить формат результата, мы можем изменить формулу как = Текст (B1, «000000»), в результате чего целые данные будут отображаться шестизначными числами. Недостающие значения места будут заполнены «0». Если вы хотите преобразовать все данные в уникальный формат или уникальное количество десятичных знаков, тогда этот формат предпочтителен.
    • В приведенном выше примере столбец Data1 состоит из целых чисел с различным количеством десятичных знаков. Поэтому здесь мы хотим сделать это уникальным способом. Чтобы заменить заданные числа аналогичным количеством десятичных значений, мы применили формулу. В формате он дал 6 десятичных знаков = текст (B1, «000000»). Таким образом, числа меняются на 6-значные, а недостающие десятичные разряды заполняются нулями.

    Пример № 4

    Чтобы преобразовать числа в текст в Excel с разными десятичными точками, нужно изменить формулу

    = Текст (B1, »00, 000' ). Что будет хранить данные в аналогичном формате таким образом, чтобы число цифр после десятичной точки? На рисунке приведен результат после применения формулы в приведенных данных.

    В этом примере мы хотим превратить число цифр после десятичной точки в уникальное число. Таким образом, формула изменяется на = Text (B1, «00.000». Это будет показывать 3 цифры после десятичной точки.

    Использование команды Format Cells

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

    • Команда Format_Cells обычно используется для изменения данных данных ячеек в другой формат в соответствии с вашей рекомендацией.

    Автоматически установить тип данных пустых ячеек

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

    Как автоматически установить тип данных в Excel?

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

    Как избежать пропущенных чисел при преобразовании чисел в текст?

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

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

    • Начальное «0» будет потеряно, так как вы не указали тип данных для этого столбца. То же самое произойдет, когда вы попытаетесь скопировать и вставить те же номера с этого листа на другой.

    Использование дополнительных инструментов

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

    Шаг к загрузке Kutools
    • Зайдите в Google и найдите бесплатные Kutools и загрузите настройки Kutools оттуда.

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

    • Который показывает две разные опции: текст в числа и цифры в текст.
    • Нажав опцию « Числа в текст», вы можете изменить выбранные числа в текст.

    Использование макросов Excel

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

    Что нужно помнить о преобразовании чисел в текст в Excel

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

    Рекомендуемые статьи

    Это было руководство для преобразования чисел в текст в Excel. Здесь мы обсудим, как использовать Преобразование чисел в текст в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

    голоса
    Рейтинг статьи
    Читайте так же:
    Как включить NPAPI в браузере Yandex
Ссылка на основную публикацию