Как массово преобразовать числа, хранящиеся в виде текста, в числа в 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.
- Три значения изменяют выравнивание с левой стороны ячейки на правую.
- Индикаторы ошибок в виде зеленых треугольников удаляются из ячеек, содержащих данные.
Подготовьте рабочий лист
Чтобы преобразовать текстовые значения в числовые данные, сначала введите несколько чисел в виде текста. Это делается путем ввода апострофа ( ‘) перед каждым числом, когда оно вводится в ячейку.
- Откройте новый лист в Excel.
- Выберите ячейку D1 , чтобы сделать ее активной.
- Введите в ячейку апостроф, за которым следует число 23 ( ’23 ).
- Нажмите Ввод. Как показано на рисунке выше, ячейка D1 имеет зеленый треугольник в верхнем левом углу ячейки, а число 23 выровнено по правой стороне. Апостроф не виден в клетке.
- Выберите ячейку D2 .
- Введите в ячейку апостроф, за которым следует число 45 ( ’45 ).
- Нажмите Ввод.
- Выберите ячейку D3.
- Введите в ячейку апостроф, за которым следует число 78 ( ’78 ).
- Нажмите Ввод.
- Выберите ячейку E1.
- Введите число 1 (без апострофа) в ячейку и нажмите Enter .
Цифра 1 выравнивается по правой стороне ячейки, как показано на рисунке выше.
Чтобы увидеть апостроф перед числами, введенными в ячейки с D1 по D3, выберите одну из этих ячеек, например D3. На панели формул над рабочим листом отображается запись ‘ 78.
Введите функцию суммы
- Выберите ячейку D4 .
- Enter = SUM (D1 : D3) .
- Нажмите Enter .
- В ячейке D4 появится ответ 0, потому что значения в ячейках D1-D3 были введены как текст.
Помимо ввода текста, существуют и другие способы ввода функции SUM в ячейки электронной таблицы:
- Ярлыки клавиатуры
- Диалоговое окно функции Сумма
- Автосумма.
Преобразование текста в числа с помощью специальной вставки
- Выберите ячейку E1 , чтобы сделать ее активной.
- Выберите Главная >Копировать .Вокруг ячейки E1 появляется пунктирная линия, которая указывает, что содержимое этой ячейки копируется в буфер обмена.
- Выделите ячейки от D1 до D3.
- Нажмите стрелку вниз Вставить , чтобы открыть раскрывающееся меню.
- Выберите Специальная вставка , чтобы открыть диалоговое окно Специальная вставка.
- В разделе «Операция» выберите Умножить , чтобы активировать эту операцию.
- Выберите ОК , чтобы закрыть диалоговое окно и вернуться на лист.
Результаты рабочего листа
На рисунке выше вы можете видеть, как эта операция изменяет рабочий лист: