Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Вопрос по Excel . имеется надо преобразовать число в текст (формат денежный) +

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

FIXED(число; число_знаков; не разделено)

Число — это число, которое округляется и преобразуется в текст
«

Преобразует значение в текст в указанном числовом формате.

Существует несколько возможных значений для значения: числовое значение, формула, расчет которой дает числовое значение, или ссылка на ячейку, содержащую числовое значение.

Формат — числовой формат в текстовом виде из списка Числовые форматы на вкладке Число диалогового окна Формат ячеек.

Звездочка (*) может не отображаться в формате.

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

[1C]
Public N(1 To 14) As Byte ‘ в каждом разряде — число из суммы
Public a, строка As String
Public A1_муж, A1_жен, a2, a3, a0

Public Function Пропись(Сумма, Optional Показывать_ноль_копеек As Boolean)
‘Сумма прописью в диапазоне от 0 до 999 млрд. с копейками

A1_муж = Array(«», «один «, «два «, «тpи «, «четыpе «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
A1_жен = Array(«», «одна «, «две «, «тpи «, «четыpе «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
a0 = Array(«десять «, «одиннадцать «, «двенадцать «, «тринадцать «, «четырнадцать «, «пятнадцать «, «шестнадцать «, «семнадцать «, «восемнадцать «, «девятнадцать «)
a2 = Array(«», «десять «, «двадцать «, «тpидцать «, «сорок «, «пятьдесят «, «шестьдесят «, «семьдесят «, «восемьдесят «, «девяносто «)
a3 = Array(«», «сто «, «двести «, «тpиста «, «четыреста «, «пятьсот «, «шестьсот «, «семьсот «, «восемьсот «, «девятьсот «)
миллиарды = Array(«миллиард», «миллиарда», «миллиардов»)
миллионы = Array(«миллион», «миллиона», «миллионов»)
тысячи = Array(«тысяча», «тысячи», «тысяч»)
рубли = Array(«рубль», «рубля», «рублей»)
копейки = Array(«копейка», «копейки», «копеек»)

Читайте так же:
Как извлечь текст до или после тире из ячеек в Excel?

a = «» ‘ собираемая строка суммы прописью
Позиция_разделителя = InStr(1, Сумма, «=», 1) + InStr(1, Сумма, «-«, 1) + InStr(1, Сумма, «.», 1) + InStr(1, Сумма, «,», 1)
If Позиция_разделителя = 0 Then
коп = «00»
Позиция_разделителя = Len(Сумма) + 1
Else
коп = Left(Mid(Сумма, Позиция_разделителя + 1, 2) & «00», 2)
End If

строка = Right(«000000000000» & Mid(Сумма, 1, Позиция_разделителя — 1), 12)

If Val(строка) < 0 Or Val(строка) > 999999999999.99 Then
Пропись = «Cумма выходит за границы допустимого диапазона (0-999999999999.99).»
Exit Function
End If
For i = 1 To 12 ‘ рубли
N(i) = Val(Mid(строка, i, 1))
Next i
For i = 13 To 14 ‘ копейки
N(i) = Val(Mid(коп, i — 12, 1))
Next i
If Разбор(0) Then ‘миллиарды
a = a & миллиарды(Склонение(0)) + » »
End If
If Разбор(3) Then ‘миллионы
a = a & миллионы(Склонение(3)) + » »
End If
If Разбор(6) Then ‘тысячи
a = a & тысячи(Склонение(6)) + » »
End If
Разбор (9) ‘рубли
If a <> «» Then
a = a & рубли(Склонение(9)) & » »
Else
a = «Ноль рублей »
End If
a = UCase(Mid(Trim(a), 1, 1)) & Mid(Trim(a), 2) ‘ первая прописная
If Not Показывать_ноль_копеек And коп = «00» Then Else _
a = a & » » & коп & » » & копейки(Склонение(11))
Пропись = a
End Function

Function Разбор(Сдвиг) As Boolean
If Val(Mid(строка, 1 + Сдвиг, 3)) <> 0 Then
a = a & a3(N(1 + Сдвиг))
If N(2 + Сдвиг) = 1 Then
a = a & a0(N(3 + Сдвиг))
Else
a = a & a2(N(2 + Сдвиг)) & IIf(Сдвиг = 6, A1_жен(N(3 + Сдвиг)), A1_муж(N(3 + Сдвиг)))
End If
Разбор = True
Else
Разбор = False
End If
End Function

Public Function Склонение(Сдвиг)
If N(2 + Сдвиг) = 1 Then
Склонение = 2
Else
Select Case N(3 + Сдвиг)
Case 1
Склонение = 0
Case 2 To 4
Склонение = 1
Case Else
Склонение = 2
End Select
End If
End Function
[/1C]

Как отобразить текст и число в одной ячейке

Чтобы объединить текст и значение в одной ячейке, вы можете использовать следующие методы:

  • Конкатенация;
  • Функция СЦЕПИТЬ;
  • Функция ТЕКСТ;
  • Пользовательский формат.
Читайте так же:
Как масштабировать все вкладки в один размер в Excel?

Давайте рассмотрим каждый из этих методов и разберем все плюсы и минусы.

Использование конкатенации

Одним из самых простых способов объединения текста и значения является использование конкатенации (символа &).

Допустим ячейка A1 содержит итоговое значение 123,45, тогда в любой другой ячейке можно записать формулу
="Итого: "&A1

Итого за содержание ячеек — 123,45.

Это простое решение, но у него много недостатков.

  1. Формула выдает текстовое значение, которое нельзя использовать в дальнейших расчетах.
  2. Значение в ячейке A1 будет выведено в общем формате без возможности форматирования. Поэтому данный метод не всегда применим.

Применение конкатенации в Excel

Применение функции СЦЕПИТЬ

Аналогичное простое решение, но с теми же недостатками — использование функции СЦЕПИТЬ. Применяется она так: =СЦЕПИТЬ("Итого: ";A1). Результаты ее использования аналогичные:

Использование функции СЦЕПИТЬ

Применение функции ТЕКСТ

Функция ТЕКСТ позволяет не только объединить текст и значение, но еще и отформатировать значение в нужном формате. Если мы применим следующую формулу =ТЕКСТ(A1;"""Итого: ""##0"), то мы получим такой результат Итого: 123.

Функция TEXT принимает в качестве второго аргумента строку числового формата. Подробнее о числовых форматах читайте в разделе Использование пользовательских форматов.

Функция Текст

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

Использование пользовательского формата

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

  1. Выберите ячейку A1.
  2. Откройте диалоговое окно Формат ячейки.
    Вызов Формата ячейки
  3. В поле Тип укажите нужный формат. В нашем случае "Итого: "# ##0.
    Использование пользовательского формата

Итак, в ячейке A1 находится значение 123.

Вы можете использовать ячейку A1 как число, но оно будет отображаться так, как вы хотите в дальнейших вычислениях.

Читайте так же:
Как извлечь или получить год, месяц и день из списка дат в Excel?

Плюсы и минусы методов

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

Как убрать иероглифы в Excel и даты вместо чисел

Да, совсем недавно столкнулся с этим в который раз. Классический случай — база данных экспортируется в csv — файл, а при открытии вижу иероглифы в Excel, а числа с точкой заменяются датами. Официально такое преобразование в даты в Excel не отключается. Можно преобразовать исходный файл, но вставлять какие-то символы не всегда удобно. Разберём разные способы, которые всегда работают для меня. Все изображения кликабельны.

Как убрать иероглифы в Excel

В простых случаях можно открыть csv — файл в Блокноте и автозаменой заменить все точки на запятые. Это поможет избавиться от преобразования чисел с точкой в даты. Давайте попробуем.

Для примера я взял csv — файл в кодировке Windows UTF-8, пока с запятыми. Вот, как он выглядит, если открыть его в блокноте:

Кодировка csv-файла с иероглифами

Вот как это выглядит при открытии в Excel:

иероглифы в Excel

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

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

Устанавливаем разделитель ячеек понятный для Excel

Итак, мы видим, что Excel не совсем корректно распознаёт такие файлы. Excel корректно воспроизводит лишь несколько видов формата csv, например, с разделением ячеек с помощью символа ; .Открываем исходный файл в каком-нибудь продвинутом Блокноте и через поиск и замену меняем табуляцию на ; .

Читайте так же:
Как извлечь первые или первые два слова из текстовых строк в листе Google?

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

Если перетащить наш csv — файл в Блокнот, то он откроется нормально и покажет кодировку. А вот Excel опять будет капризничать. Мы не заменили кодировку.

Меняем кодировку

Снова откройте Блокнот и выберите кодировку, в которую вы хотите преобразовать наш файл.

Кодировка csv

Теперь попробуем открыть его в Excel. Все работает. Таблица открывается нормально.

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

Мы по-разному обрабатываем сложные случаи. Перед всеми преобразованиями заменяем в «Блокноте» точки с запятой и запятые другими знаками, например, $ и @. Затем преобразуем текст, вставив ; в качестве разделителя, а в открытом файле в Excel заменяем $ точкой с запятой, а @ — запятой.

Вот универсальный метод. В отличие от других методов, он работает в любом табличном редакторе.

Что делать, если числа с точкой заменяются датами

Замените в нашем файле примера точки вместо запятых.

Попробуем открыть его в Excel. Вот что мы видим. Кроме того, у нас возникли проблемы с числами. Excel преобразовал их в даты.

Давайте попробуем избавиться от ошибок непосредственно в Excel.

Отключаем преобразование в даты и иероглифы в Excel

Мы открыли файл с иероглифами, и числа превратились в даты. Давайте теперь исправим это. Перейдите на вкладку Данные. Выбираем Получить данные из файла CSV. Открываем наш файл, устанавливаем формат, разделитель (у нас уже есть тот, который понимает Excel) и отключаем автоматическое преобразование чисел в даты.

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

Читайте так же:
Как извлечь текст между запятыми / скобками / круглыми скобками в Excel?

Для большинства случаев это самый простой и даже универсальный метод. Таким же образом мы решали вопрос Как получить курсы валют из Интернета в Excel или Google Spreadsheets. Подстановка символов, разделение данных и раздельная обработка — все это методы, которые необходимо комбинировать в сложных случаях.

Надеюсь, что эти два универсальных способа помогут вам восстановить данные из любого csv — файла.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector