Как массово преобразовать числа, хранящиеся в виде текста, в числа в Excel?

Как перевести данные в тысячи/миллионы/… в Excel?

Здравствуйте дорогие читатели блога TutorExcel.Ru!

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

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

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

Для того чтобы решить проблему перевода чисел, у нас есть два пути:

• Формула. Непосредственное деление содержимого ячейки на требуемый делитель (тысяча, миллион, миллиард и т.д.).
  В этом случае меняется значение ячейки;
• Формат ячейки. Изменение отображения ячейки с помощью настройки формата ячейки.
  В этом случае значение ячейки не меняется, а меняется только формат отображения. Также мы можем настроить и подпись (для добавления текста к записи, к примеру «тыс.», «млн.» и т.д.)

Давайте начнем с самого простого и наиболее очевидного варианта.

Способ 1. Формула

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

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

Оставим столбец A неизменным, а работать будем со столбцом B.

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

Затем скопируйте эту ячейку и выберите диапазон ячеек (ячейки B2:B10), в которых мы изменяем размеры.

Воспользуемся инструментом Специальная вставка — щелкаем правой кнопкой мыши по выделенному диапазону и выбираем Специальную вставку (либо воспользуемся сочетанием клавиш Ctrl + Alt + V):

В появившемся окне в разделе Операция выберите Деление и нажмите OK, результат (для наглядности я добавил 1 дополнительный знак после запятой):

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

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

Теперь рассмотрим более сложную и красивую альтернативу.

Способ 2. Формат ячеек

Помимо изменения самих ячеек, мы можем изменить способ их отображения.

Более того, если нам нужно перевести, например, рубли в тысячи рублей, мы можем сделать это только таким способом (потому что предыдущий способ изменяет само число, но оставляет подпись неизменной).

Рассмотрим ту же таблицу, что и в предыдущем примере, выделим диапазон данных (B2:B10) тем же способом, щелкнем правой кнопкой мыши и перейдем к пункту Форматировать ячейки.

В правой части вкладки Numeric (все форматы) показано, как будет отображаться ячейка, в моем случае маска отображения «# ##0».

Для того, чтобы сделать число в тысячах добавим 1 пробел в конце записи числа в маске — «# ##0 » (для миллиона 2 подряд пробела и т.д.), нажимаем ОК и получаем аналогичный результат.
Если нужно добавить в запись «тыс. руб.», то в формат маски записываем «# ##0,0 » тыс. руб.»«, т.е. ставим пробел именно после записи формата числа (до добавления текстовой записи, а не после):

Скачать файл с примером.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь в комментариях.

Сумма прописью в Excel: как написать число текстом

Здравствуйте.

Нередко в различных документах Excel (особенно, связанных с бухгалтерией, подсчетом денег и т.д. 👌) требуется прописать полученную сумму «Итого» прописью (текстом). Например, рядом с числом «5100,45» — написать «пять тысяч сто рублей 45 копеек».

Простой встроенной функции для решения этой небольшой задачи в Excel нет (к сожалению). Конечно, чтобы не приходилось каждый раз работать «вручную», чтобы решить ее, лучше все автоматизировать!

Собственно, именно об этом и пойдет речь в этом небольшом посте.

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

Автоматически: число —> текст

👉 ШАГ 1

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

👉 Важно!

Скачайте этот специальный файл здесь: ссылка на Яндекс диске (проверена на вирусы, все чисто ✔). Совместим с Excel 2019, 2016, 2013 (совместимость с другими не проверялась).

Примечание к надстройке.

Это позволит вам написать сумму русскими словами:

• в рублях;
• в гривнах;
• в долларах;
• в евро.

👉 ШАГ 2

Откройте Excel и откройте меню Файл/Параметры.

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

Надстройки для Excel — перейти

Затем с помощью кнопки «Обзор» указываем загруженную надстройку (см. ШАГ 1 ☝), ставим галочку напротив «Сумма прописью» и нажимаем OK. См. пример ниже. 👇

👉 ШАГ 3

Если всё прошло корректно — то в Excel должно было появиться 4-ре новых функции. Чтобы проверить так ли это — попробуйте начать вводить функцию «=сум» (на русском!). 👇

В Excel есть 4 новые возможности

👉 ШАГ 4

Фактически, остается только написать формулу, например, «=Сумма словами ()». и в скобках указать ячейку, в которой находится число (сумма), которое вы хотите записать словами.

Как видите из примера ниже — всё работает! «Пятьдесят четыре тысячи сто семьдесят семь рублей 84 копейки» (прямо как во всех бухгалтерских документах) 👌

Преобразование текста в числа с помощью Excel Paste Special

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

Информация в данной статье относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 для Mac, Excel 2016 для Mac и Excel для Mac 2011.

Преобразовать импортированные данные из текста в числовой формат

На рисунке выше функция SUM установлена для суммирования трех значений (23, 45 и 78) в ячейках D1-D3.

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

Подсказки на листе

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

Числа 23, 45 и 78 на изображении выше выровнены по левой стороне ячеек, поскольку это текстовые значения. В ячейке D4 показан результат функции SUM, причем результат выровнен справа.

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

Исправление проблемных данных с помощью специальной вставки

Функция Excel VALUE и функция Paste Special позволяют преобразовать эти данные в числовой формат.

Специальная вставка – это расширенная версия команды вставки, которая предоставляет ряд опций относительно того, что передается между ячейками во время операции копирования/вставки. Эти параметры включают основные математические операции, такие как сложение и умножение.

Умножьте значения на 1 с помощью специальной вставки

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

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

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

Подготовьте рабочий лист

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

1. Откройте новый лист в Excel.
2. Выберите ячейку D1 , чтобы сделать ее активной.
3. Введите в ячейку апостроф, за которым следует число 23 ( ’23 ).
4. Нажмите Ввод. Как показано на рисунке выше, ячейка D1 имеет зеленый треугольник в верхнем левом углу ячейки, а число 23 выровнено по правой стороне. Апостроф не виден в клетке.
5. Выберите ячейку D2 .
6. Введите в ячейку апостроф, за которым следует число 45 ( ’45 ).
7. Нажмите Ввод.
8. Выберите ячейку D3.
9. Введите в ячейку апостроф, за которым следует число 78 ( ’78 ).
10. Нажмите Ввод.
11. Выберите ячейку E1.
12. Введите число 1 (без апострофа) в ячейку и нажмите Enter .

Цифра 1 выравнивается по правой стороне ячейки, как показано на рисунке выше.

Чтобы увидеть апостроф перед числами, введенными в ячейки с D1 по D3, выберите одну из этих ячеек, например D3. На панели формул над рабочим листом отображается запись ‘ 78.

Введите функцию суммы

1. Выберите ячейку D4 .
2. Enter = SUM (D1 : D3) .
3. Нажмите Enter .
4. В ячейке D4 появится ответ 0, потому что значения в ячейках D1-D3 были введены как текст.

Помимо ввода текста, существуют и другие способы ввода функции SUM в ячейки электронной таблицы:

• Ярлыки клавиатуры
• Диалоговое окно функции Сумма
• Автосумма.

Преобразование текста в числа с помощью специальной вставки

1. Выберите ячейку E1 , чтобы сделать ее активной.
2. Выберите Главная >Копировать .Вокруг ячейки E1 появляется пунктирная линия, которая указывает, что содержимое этой ячейки копируется в буфер обмена.
3. Выделите ячейки от D1 до D3.
4. Нажмите стрелку вниз Вставить , чтобы открыть раскрывающееся меню.
5. Выберите Специальная вставка , чтобы открыть диалоговое окно Специальная вставка.
6. В разделе «Операция» выберите Умножить , чтобы активировать эту операцию.
7. Выберите ОК , чтобы закрыть диалоговое окно и вернуться на лист.

Результаты рабочего листа

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