Формула форматирует число суммы как текст в одной ячейке Excel
Часто в отчетах Excel необходимо объединять текст с числами. Проблема заключается в том, что вовремя объединения текста с числом нельзя сохранить числовой формат данных ячейки. Число в ячейке отформатировано как текст.
Отформатировать число как текст в Excel с денежным форматом ячейки
Рассмотрим, например, отчет, который изображен ниже на рисунке. Допустим, например, необходимо в отчете поместить список магазинов и их результаты продаж в одной ячейке:
Обратите внимание, что каждое число после объединения с текстом (в столбце D) не сохраняет свой денежный формат, который определен в исходной ячейке (столбца B).
Чтобы решить данную задачу, необходимо поместить ссылку к ячейкам с числами денежных сумм в функцию ТЕКСТ. Благодаря ей можно форматировать числовые значения прямо в текстовой строке. Формула изображена на рисунке:
Формула позволяет решить данную задачу если речь идет только об одном типе валюты. Когда валют будет несколько придется использовать в формуле макрофункцию – об этом речь пойдет ниже. А пока разберемся с этой формулой.
Функция ТЕКСТ требует 2 обязательных для заполнения аргумента:
- Значение – ссылка на исходное число (в данном случае).
- Формат – текстовый код формата ячеек Excel (может быть пользовательский).
Число можно форматировать любым способом, важно лишь соблюдать правила оформления форматов, который должен распознаваться в Excel.
Например, ниже заполненная аргументами функция ТЕКСТ возвращает число в денежном формате пересчитанному по курсу 68 руб./1$:
Измененная ниже формула возвращает долю в процентах от общей выручки:
Простой способ проверки синтаксиса кода формата, который распознает Excel – это использование окна «Формат ячеек». Для этого:
- Щелкните правой кнопкой мышки по любой ячейке с числом и выберите из появившегося контекстного меню опцию «Формат ячейки». Или нажмите комбинацию горячих клавиш CTRL+1.
- Перейдите на вкладку «Число».
- В секции «Числовые форматы:» выберите категорию «(все форматы)».
- Введите в поле «Тип:» свой пользовательский код формата и в секции «Образец» наблюдайте как он будет распознан в Excel и отображен в ячейке.
Функция РУБЛЬ для форматирования числа как текст в одной ячейке
Более упрощенным альтернативным решением для данной задачи может послужить функция РУБЛЬ, которая преобразует любое число в текст и отображает его в денежном формате:
Данное решение весьма ограничено по функциональности и подходит только для тех случаев если соединяемое число с текстом является денежно суммой в валюте рубли (или той которая является по умолчанию и указана в региональных стандартах панели управления Windows).
Функция РУБЛЬ требует для заполнения только 2 аргумента:
- Число – ссылка на числовое значение (обязательный аргумент для заполнения).
- [Число_знаков] – Количество символов после запятой.
Пользовательская макрофункция для получения формата ячейки в Excel
Если в исходном столбце содержаться значения в ячейках с разным форматом валют тогда нам потребуется распознать все форматы в каждой ячейке. По умолчанию в Excel нет стандартной функции, которая умеет распознавать и возвращать форматы ячеек. Поэтому напишем свою пользовательскую макрофункцию и добавим ее в нашу формулу. Макрофункция будет называться ВЗЯТЬФОРМАТ (или можете назвать ее по-своему). Исходный VBA-код макрофункции выглядит так:
Public Function ВЗЯТЬФОРМАТ(val As Range) As String
Dim money As String
money = WorksheetFunction.Text(val, val.NumberFormat)
ВЗЯТЬФОРМАТ = Replace(money, Application.ThousandsSeparator, " ")
End Function
Скопируйте его в модуль («Insert»-«Module») VBA-редактора (ALT+F11). При необходимости прочитайте:
Примеры как создать пользовательскую функцию в Excel.
Теперь изменяем нашу формулу и получаем максимально эффективный результат:
Благодаря функции ВЗЯТЬФОРМАТ написанной на VBA-макросе мы просто берем значение и формат из исходной ячейки и подставляем его как текстовую строку. Так наша формула с пользовательской макрофункцией ВЗЯТЬФОРМАТ автоматически определяет валюту для каждой суммы в исходных значениях ячеек.