Мастер преобразования данных в Excel: от чисел к тексту и валюте

Чтобы превратить число в текст, дату или денежный формат в Excel, используйте функцию =ТЕКСТ(значение; "формат"). Например, =ТЕКСТ(A1; "0") сделает из числа строку, =ТЕКСТ(A1; "дд.мм.гггг") — дату, а =ТЕКСТ(A1; "0,00 ₽") — цену. Если нужно наоборот (из текста в число), применяйте функции ЗНАЧЕН или ДАТАЗНАЧ.

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

Базовый инструмент: функция ТЕКСТ

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

Синтаксис: =ТЕКСТ(значение; "код_формата")

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

Популярные коды форматирования

Конвертация чисел в читаемые даты

В Excel даты хранятся как порядковые номера (серийные числа), где 1 соответствует 1 января 1900 года. Часто при импорте данных вы видите вместо даты странное пятизначное число (например, 45386).

Способ 1: Быстрое изменение формата ячейки

Если число уже распознано Excel как дата, менять его формулой не нужно:

1. Выделите ячейку с числом.
2. Нажмите Ctrl+1 (Формат ячеек).
3. Выберите категорию Дата и нужный вид отображения.

Способ 2: Функция ТЕКСТ для экспорта

Если вам нужно получить дату именно как текст (например, для названия файла или объединения с другим текстом): =ТЕКСТ(A1; "дд.мм.гггг") → вернет "09.04.2026". =ТЕКСТ(A1; "дддд, д мммм гггг") → вернет "четверг, 9 апреля 2026".

Обратная задача: Текст в дату

Если у вас есть текст "09.04.2026", который Excel не считает датой (выравнивание по левому краю), используйте функцию ДАТАЗНАЧ: =ДАТАЗНАЧ(A1) После этого обязательно примените к ячейке с формулой формат "Дата", иначе вы снова увидите серийный номер.

Форматирование денежных сумм и валют

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

Добавление символа валюты

Используйте функцию ТЕКСТ с нужным символом внутри кавычек:

• Рубли: =ТЕКСТ(A1; "0,00 ₽")
• Доллары: =ТЕКСТ(A1; "$0.00") (точка как разделитель для англ. локали)
• Евро: =ТЕКСТ(A1; "0,00 €")

Умное форматирование с пробелами

Для красивого отображения больших сумм добавьте пробел перед знаком валюты и разделитель тысяч: =ТЕКСТ(A1; "# ##0,00 ₽") Результат: 1 250 000,00 ₽. Это улучшает читаемость финансовых отчетов.

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

Частая проблема: данные пришли из другой системы в виде текста ("1 200", "1,200"), и сумма по столбцу равна нулю.

Функция ЗНАЧЕН

Простая функция =ЗНАЧЕН(A1) работает, если текст записан в стандартном формате вашей локали.

• Работает с: "100", "12,5".
• Не работает с: "1 200" (если пробел мешает) или "1.200" (если система ждет запятую).

Функция ЧИСЛОЗНАЧ (NUMBERVALUE)

Более мощный инструмент, позволяющий вручную указать разделители. Синтаксис: =ЧИСЛОЗНАЧ(текст; [разделитель_дробной]; [разделитель_групп)]

Пример очистки сложного текста: =ЧИСЛОЗНАЧ(A1; ","; " ") Эта формула скажет Excel: "Считай запятую десятичным знаком, а пробел — разделителем тысяч". Это идеально подходит для импорта данных из европейских источников.

Очистка от лишних символов

Если в ячейке написано "100 руб.", сначала удалите текст функцией ПОДСТАВИТЬ, а потом преобразуйте: =ЗНАЧЕН(ПОДСТАВИТЬ(A1; " руб."; ""))

Практические сценарии использования

1. Создание уникальных идентификаторов

При объединении текста и числа лидирующие нули часто исчезают (число 007 превращается в 7). Чтобы сохранить их: = "Заказ-" & ТЕКСТ(A1; "0000") Результат: "Заказ-0007".

2. Динамические заголовки отчетов

Вместо ручного ввода даты в заголовке используйте формулу: = "Отчет за " & ТЕКСТ(СЕГОДНЯ(); "дд.мм.гггг") Это гарантирует, что дата всегда будет актуальной и в правильном формате.

3. Подготовка данных для выгрузки в 1С или другую систему

Некоторые системы требуют строгого формата чисел без пробелов и с точкой. =ПОДСТАВИТЬ(ТЕКСТ(A1; "0.00"); ","; ".") Эта цепочка сначала форматирует число с двумя знаками, а затем заменяет русскую запятую на точку для совместимости.

Частые ошибки

• #ЗНАЧ! при использовании ТЕКСТ: Проверьте код формата. Лишние пробелы внутри кавычек или неверные буквы кодов (например, mm вместо мм в русской локали) вызывают ошибку.
• Нулевая сумма в итогах: Вы применили функцию ТЕКСТ к исходным данным. Итоговая формула СУММ игнорирует текстовые значения. Решение: оставьте исходные данные числами, а форматирование примените через "Формат ячеек" или используйте ТЕКСТ только в отдельном столбце для печати.
• Сдвиг дат на 4 года: Частая ошибка при работе с американскими и европейскими датами. Если ДАТАЗНАЧ выдает неверный год, возможно, система путает день и месяц (01.02 интерпретируется как 2 января, а не 1 февраля). Явно указывайте формат при конвертации.

FAQ

Как убрать знак валюты, но оставить форматирование? Выделите ячейки, нажмите Ctrl+1, выберите "Числовой" или "Общий". Если нужно формулой: =ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1; "$"; ""); "₽"; "")).

Почему функция ТЕКСТ возвращает ошибку в английской версии Excel? В английской версии коды форматов пишутся латиницей: dd.mm.yyyy вместо дд.мм.гггг, h:mm вместо ч:мм. Также разделитель аргументов — запятая, а не точка с запятой.

Можно ли преобразовать весь столбец сразу? Да. Напишите формулу в первой ячейке соседнего столбца и протяните маркер заполнения вниз. Затем скопируйте результат и вставьте его на место исходных данных через "Специальную вставку" -> "Значения", чтобы удалить формулы.