Функция в excel преобразования числа в текст

Преобразование чисел-как-текст в нормальные числа

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

Причем иногда такой индикатор не появляется (что гораздо хуже).

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

• перестает нормально работать сортировка – “псевдочисла” выдавливаются вниз, а не располагаются по-порядку как положено:
  
• функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
  
• при фильтрации псевдочисла отбираются ошибочно
• многие другие функции Excel также перестают нормально работать:
• и т.д.

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой – не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК – и ничего не происходит! Совсем!

Возможно, “это не баг, а фича”, конечно, но нам от этого не легче. Так что давайте-к рассмотрим несколько способов исправить ситуацию – один из них вам обязательно поможет.

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

Все числа в выделенном диапазоне будут преобразованы в полноценные.

Если зеленых уголков нет совсем, то проверьте – не выключены ли они в настройках вашего Excel (Файл – Параметры – Формулы – Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

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

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

Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.

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

Способ 4. Специальная вставка

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

• в любую пустую ячейку введите 1
• скопируйте ее
• выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
• щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
• в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

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

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert – Module и скопируйте туда следующий код:

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик – Макросы (Developer – Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) – и моментально преобразовать псевдочисла в полноценные.

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

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения – те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Источник: www.planetaexcel.ru

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

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

Нет функции по умолчанию, отображающий чисел в виде английских слов на листе Excel, но вы можете добавить эту возможность путем вставки следующий код функции SpellNumber в модуле VBA (Visual Basic for Applications). Эта функция позволяет преобразовывать доллара и кратное сумм для слов с помощью формулы, 22,50 читается как двадцати двух центы и т. Это может быть очень полезным при использовании Excel как шаблон для печати проверки.

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

Примечание: Microsoft примеры программирования только для иллюстрации и без гарантий выраженное или подразумевается. Включает в себя, но не ограничивается гарантий окупаемость или Фитнес для определенной цели. В этой статье предполагается, что вы знакомы с языком VBA программирования и с помощью средств, которые используются для создания и отладки процедур. Сотрудники службы поддержки Майкрософт могут пояснить конкретной процедуры. Однако они не изменять эти примеры для обеспечения функциональных возможностей или создания процедур в соответствии с конкретными требованиями.

Создание функции SpellNumber для преобразования чисел в слова

Используйте сочетание клавиш Alt + F11 , чтобы открыть редактор Visual Basic (VBE).

Примечание: Вы также можете открывать редактор Visual Basic, добавив вкладку “Разработчик” на ленту.

На вкладке Insert (Вставка) нажмите кнопку Module (Модуль).

Скопируйте приведенный ниже код.

Примечание: Известные как Пользовательской функции (UDF)этот код Автоматизация задач для преобразования чисел в разных частях листа.

Вставьте строки кода в поле Module1 (Code) (Модуль 1 — код).

Нажмите клавиши Alt + Q , чтобы вернуться в Microsoft Excel. Функция SpellNumber теперь готов к использованию.

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

Использование функции SpellNumber в отдельных ячейках

Введите формулу = SpellNumber ( A1) в ячейку, которую вы хотите отобразить письменного номер, где A1 — ячейку, содержащую число вы хотите преобразовать. Можно также вручную ввести значение как = SpellNumber(22.50).

Нажмите клавишу Ввод, чтобы подтвердить формулу.

Сохранение книги с функцией SpellNumber

Excel не может сохранить книгу с помощью функции макросов в формате стандартной книги без поддержки макросов (XLSX). Если щелкнуть файл > Сохранить. Откроется диалоговое окно проекта VB. Нажмите кнопку Нет.

Можно сохранить файл в формате Книга Excel с поддержкой макросов (xlsm), чтобы сохранить файл в текущем формате.

На вкладке Файл выберите команду Сохранить как.

Щелкните раскрывающееся меню Тип файла и выберите Книгу Excel с поддержкой макросов.

Источник: support.office.com

Число прописью в Excel: инструкция, как написать сумму

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

Microsoft Edge: браузер, который нужен всем. ну или почти всем

NewsBar Windows: нон-стоп новости 24/7

Windows 10 выводит из строя Mac: Microsoft признал вину, но что дальше?

Плановая переустановка Виндовс 7 на 10: мошенники против пенсионеров

Полноэкранная реклама от Windows – раздражающая новинка, которую можно и нужно отключить

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

Как в Excel сделать сумму прописью

Чтобы получить эту возможность, вы можете скачать надстройку «Сумма прописью» в Excel бесплатно на нашем сайте.

Разместите его в любом удобном каталоге и подключите. Для этого следует пройти следующие этапы:

• Запустите Excel и нажмите «Файл».
  
• Щелкните на параметры.
  
• Далее переходите в надстройки.
  
  И кликайте по кнопке «Перейти».
  
• Чтобы подгрузить скачанный файл жмите «Обзор».
  
• И ищите его в папке, куда сохраняли. Вам предложат скопировать файл в папку надстроек, рекомендуем согласиться. Так Excel всегда будет видеть эту надстройку и вы легко найдете функцию для суммы прописью.
  
• Нужная библиотека Excel подключена. Галочка означает, что ее можно использовать. Жмите ОК.
  
• Нужные действия реализованы в виде функции СУММА_ПРОПИСЬЮ. Чтобы применить ее, введите в ячейку A1 число и установите указатель рядом, на B1. Нажмите значок Fx около строки формул.
  
• И в полном алфавитном перечне найдите функцию СУММА_ПРОПИСЬЮ. Рекомендуем начать печатать название. В таком случае Excel сам перемотает ближе к нужному разделу.
  
• Жмите ОК и в окне мастера укажите адрес A1. Можно просто мышкой кликнуть по ячейке.
  
• После нажатия ОК вы увидите сумму прописью.
  

Эта функция «сумма_прописью» работает в Excel 2007, 2010, 2016

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

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

Как видите, нам стали доступны:

• Сумма Прописью
• Сумма Прописью Доллары
• Сумма Прописью Евро
• Сумма Прописью Евро

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

После нажатия ОК получаем результат.

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

Формула суммы прописью в Excel – как написать число

Для вывода числа прописью в Excel можно создать формулу. Приведем пример:

Для ее использования вам необходимо указать несколько массивов Excel:

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

В англоязычном Excel формула суммы прописью будет выглядеть следующим образом:

SUBSTITUTE(PROPER(INDEX(n_4,MID(TEXT(A1,n0),1,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),2,1)+1,MID(TEXT(A1,n0),3,1)+1)&IF(-MID(TEXT(A1,n0),1,3),”миллиард”&VLOOKUP(MID(TEXT(A1,n0),3,1)*AND(MID(TEXT(A1,n0),2,1)-1),мил,2),””)&INDEX(n_4,MID(TEXT(A1,n0),4,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),5,1)+1,MID(TEXT(A1,n0),6,1)+1)&IF(-MID(TEXT(A1,n0),4,3),”миллион”&VLOOKUP(MID(TEXT(A1,n0),6,1)*AND(MID(TEXT(A1,n0),5,1)-1),мил,2),””)&INDEX(n_4,MID(TEXT(A1,n0),7,1)+1)&INDEX(n1x,MID(TEXT(A1,n0),8,1)+1,MID(TEXT(A1,n0),9,1)+1)&IF(-MID(TEXT(A1,n0),7,3),VLOOKUP(MID(TEXT(A1,n0),9,1)*AND(MID(TEXT(A1,n0),8,1)-1),тыс,2),””)&INDEX(n_4,MID(TEXT(A1,n0),10,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),11,1)+1,MID(TEXT(A1,n0),12,1)+1)),”z”,” “)&IF(TRUNC(TEXT(A1,n0)),””,”Ноль “)&”рубл”&VLOOKUP(MOD(MAX(MOD(MID(TEXT(A1,n0),11,2)-11,100),9),10),<0,"ь ";1,"я ";4,"ей ">,2)&RIGHT(TEXT(A1,n0),2)&” копе”&VLOOKUP(MOD(MAX(MOD(RIGHT(TEXT(A1,n0),2)-11,100),9),10),<0,"йка";1,"йки";4,"ек">,2)

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

Преобразование с помощью макросов Excel

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

В него добавьте следующий код.

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

В качестве аргумента укажите адрес с числом.

Перевод в сумму прописью осуществляется только для целых чисел.

Дробное число требует дополнительной формулы:

Вместо ячейки A7 подставляйте свой адрес. Копейки в таком исполнении будут выводиться в виде числа.

Мы рассмотрели все способы представления суммы прописью в Excel. Выбирайте для себя самый удобный и пользуйтесь с удовольствием!

Источник: windowstips.ru

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

Табличный редактор Microsoft Office Excel автоматически определяет формат введенной информации. Практически всегда данная операция выполняется правильно, но редактор может ошибаться, в таком случае придется менять формат самостоятельно. Также смена формата может понадобиться по другой причине, для выполнения данной операции понадобится:

— Программа Excel(советуем использовать лицензионный Microsoft Office 2016, который включает в себя данную программу).

Инструкция

1. Откройте программу Excel и загрузите в нее нужный документ. Обозначьте ячейки, формат которых нужно изменить. Если все ячейки расположены в одном ряду или столбце, то для выделения данного диапазона следует нажать на его заголовок.
2. Убедитесь, что в редакторе открыта вкладка «Главная» и раскройте выпадающий список в разделе «Число». Ознакомьтесь с возможными форматами ячеек и укажите нужный вариант, формат ячеек будет изменен на выбранный.
3. Для выполнения данной операции можно использовать другой метод: выделите нужный диапазон ячеек и вызовите для него контекстное меню. Нажмите «Формат ячеек» и откройте вкладку «Число». Укажите нужный формат в колонке «Числовые форматы» и нажмите Enter.

Ткстовый формат в колонке «Числовые форматы»
Иногда может быть удобным использовать для смены формата опцию «Текст». Данный метод отлично подойдет в тех случаях, когда не нужно изменять формат больших групп ячеек, а нужно произвести эти действия в выборочных ячейках или текст должен стать элементом одной из формул. Выделите нужную ячейку и перейдите на вкладку редактора «Формулы», в разделе опций «Библиотека функций» Вкладка «Формулы», раздел опций «Библиотека функций»

нажмите на выпадающий перечень «Тестовые» и нажмите на вариант «Текст» левой клавишей мышки.

Видео: Числа в текстовом формате в Excel

Источник: besthard.ru

Функция ЗНАЧЕН в Excel для преобразования текста в число

Функция ЗНАЧЕН в Excel выполняет операцию преобразования строки в числовое значение в тех случаях, где это возможно. Данная функция возвращает число в случае удачно выполненного процесса преобразования или код ошибки #ЗНАЧ!, если преобразование текста в число невозможно.

Примеры функции ЗНАЧЕН в Excel и особенности ее использования

Данная функция имеет следующую синтаксическую запись:

Единственный аргумент – текст является обязательным для заполнения. То есть, данная функция принимает на вход текстовые данные, которые могут быть представлены в виде текстовой строки, введенной в кавычках (например, ЗНАЧЕН(“9 300 ₽”) или ссылки на ячейку таблицы, в которой содержится текст.

1. Формат текста, принимаемого в качестве параметра функции ЗНАЧЕН, должен быть одним из предусмотренных в Excel (время, дата, число, денежный). Результатом выполнения функции будет являться ошибка #ЗНАЧ!, если исходный текст не соответствует одному из этих типов данных.
2. На практике функция ЗНАЧЕН используется довольно редко, поскольку в Excel реализован механизм автоматического преобразования значений подобно некоторым языкам программирования, в которых присутствует неявное преобразование типов данных. Эта функция реализована с целью совместимости с прочими программными продуктами, поддерживающими работу с таблицами.
3. Функция полезна в случаях, когда числовые значения внесены в ячейки с установленным текстовым форматом данных. Также в Excel можно использовать надстройку, выполняющую поиск чисел, отформатированных в качестве текста и последующее преобразование их в числовой формат.
4. Чаще всего функция ЗНАЧЕН используется для преобразования результатов работы функций ПСТР и ЛЕВСИМВ (ПРАВСИМВ), однако для корректного отображения к результатам вычислений данных функций можно просто прибавить 0 (нуль). Подробнее рассмотрим это в одном из примеров.



Примеры использования функции ЗНАЧЕН в Excel

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

Часть таблицы выглядит следующим образом:

Для расчета общего количества представленных 5 наименований продукции используем следующую формулу:

Функция СУММ принимает числовые значения и производит их суммирование. В данном примере наименование и количество единиц некоторых товаров записаны в одной строке. Функция ПРАВСИМВ «отрезает» часть строки кроме двух последних символов, отображающих числовое значение – количество товаров. С помощью функции ЗНАЧЕН, принимающей в качестве параметра результат работы функции ПРАВСИМ, мы производим прямое преобразование выделенных символов в числовое значение.

В результате получим следующее:

То есть, в данной части таблицы представлены 265 единиц товаров.

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

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

Изначально таблица выглядит следующим образом:

Для конвертирования денежных данных в числовые была использована функция ЗНАЧЕН. Расчет выполняется для каждого сотрудника по-отдельности. Приведем пример использования для сотрудника с фамилией Иванов:

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

Функция ЗНАЧЕН и формат времени

Пример 3. Самолет вылетел согласно расписанию в 13:40 и должен был совершить посадку в конечном пункте перелета в 17:20. Во время полета произошел инцидент, в связи которым рейс был задержан на определенное время. Самолет приземлился в 19:13. Необходимо определить время задержки в часах и минутах. Данные о времени вылета, расчетного и фактического времени прибытия занесены в ячейки таблицы формата «Дата».

Таблица имеет следующий вид:

Для решения задачи запишем следующую формулу:

Примечание: функция ЗНАЧЕН использована для явного преобразования дат в числа в коде времени Excel. Фактически, записи «=ЗНАЧЕН(C2)-ЗНАЧЕН(B2)» и «C2-B2» являются эквивалентными, поскольку Excel выполняет неявное преобразование. Прямое преобразование имеет практический смысл при использовании полученных значений в других программных продуктах, не поддерживающих форматы даты и времени Excel.

В результате будет получено число в коде времени Excel:

Для получения искомого значения необходимо преобразовать формат данных ячейки в «Время»:

То есть, самолет опоздал на 1 час и 53 минуты.

Источник: exceltable.com

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

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

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

Очень часто при экспорте из баз данных, числовые значения в таблицах заменяются текстом. Если число выражено явно, Excel сам преобразует его в текст, либо появится смарт-тег с командой «преобразовать в число». Но иногда, особенно при работе с датами, этого не происходит. Тогда придётся воспользоваться функцией ЗНАЧЕН(Строка).

Эта функция содержит один обязательный аргумент, указывающий на строку для преобразования. Если функция не сможет преобразовать строку в число, будет выведена ошибка #ЗНАЧ!

Применение функции ЗНАЧЕН

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

Другая задача, которая может доставить проблем – объединить строчные и числовые данные в одной строке. Представьте, что у вас есть значение в денежном формате и текст, которые нужно объединить. Обычные функции объединения дадут не тот результат, который нам хотелось бы:

Сцепление текста с числом без форматирования

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

Чтобы отформатировать число и сохранить, как текст, используем функцию ТЕКСТ(Число; Формат). Формат придётся задать вручную. О пользовательских форматах я расскажу в отдельной статье.

Давайте запишем формулу перевода числа в текст с указанными требованиями: =ТЕКСТ(A3;»$# #,00″) . Эту функцию можно «вложить» в формулу объединения и получить корректный результат. Функция ТЕКСТ записывает число в указанном формате и преобразует его в текст.

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

Совсем другое дело, теперь число записано так, как нам нужно. Ведь правда, это очень полезная функция?

Как проверить ячейку на содержимое

Еще одна текстовая функция – условная. Она проверяет строку на тип данных. Если в строке расположен текст – он выводится на экран. Если же это число, функция возвращает пустую строку. Синтаксис функции такой: =Т(Значение).

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

Это последний пост, рассказывающий о текстовых функциях Excel. А в следующей статье мы попробуем применить их на практике. А дальше — будем изучать работу с датами и временем. Заходите почитать, будет полезно!

Источник: officelegko.com