Учебник по преобразованию ячеек Excel – преобразование единиц, текста и чисел, валют, часовых поясов и др.

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

В этом руководстве рассматриваются следующие темы: (Щелкните по любому заголовку в оглавлении ниже или справа, чтобы перейти к соответствующему разделу.)

1. Преобразование между различными единицами измерения в Excel

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

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

1.1 Преобразование между различными единицами измерения с помощью функции CONVERT

В Excel есть встроенная функция "Insert Comment", которая позволяет легко добавить комментарий к ячейке.

Как показано на скриншоте ниже, чтобы преобразовать значения в дюймах из диапазона B3:B10 в футы, сантиметры и миллиметры по отдельности, вы можете воспользоваться функцией CONVERT.

Синтаксис

CONVERT(число, из_единицы, в_единицу)

Аргументы

В Excel существует множество единиц измерения. Чтобы просмотреть доступные единицы для каждой категории, которые можно использовать в аргументах "from_unit" и "to_unit" функции CONVERT, пожалуйста, нажмите здесь.

Преобразование дюймов в футы:

Выберите ячейку (например, D3), скопируйте или введите в нее следующую формулу и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты. См. скриншот:

=CONVERT(B3,"in","ft")

Преобразование дюймов в сантиметры:

Выберите ячейку (например, E3), скопируйте или введите в нее следующую формулу и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=CONVERT(B3,"in","cm")

Преобразование дюймов в миллиметры:

Выберите ячейку (например, F3), скопируйте или введите в нее следующую формулу и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=CONVERT(B3,"in","mm")

Примечания:

1.2 Преобразование сантиметров (CM) в футы и дюймы в Excel с помощью формулы

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

1. Выберите пустую ячейку (например, D2), введите формулу ниже и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=TRUNC(B2/2.54/12)&' '&ROUND(MOD(B2/2.54,12),0)&""""

Примечание: Чтобы сохранить2 знака после запятой для дюймов, используйте следующую формулу.

=INT(CONVERT(B2,"cm","ft")) & "' " & TEXT(12*(CONVERT(B2,"cm","ft")-INT(CONVERT(B2,"cm","ft"))),"0.00") & """"

1.3 Преобразование метров (M) в футы и дюймы в Excel с помощью формулы

Чтобы преобразовать метры в футы и дюймы, как показано на скриншоте ниже, используйте следующие формулы.

1. Выберите пустую ячейку, например D2, введите следующую формулу и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные футы и дюймы.

=TRUNC(B2*100/2.54/12)&' '&ROUND(MOD(B2*100/2.54,12),0)&""""

Примечание: Чтобы сохранить2 знака после запятой для дюймов после преобразования, используйте следующую формулу.

=INT(CONVERT(B2,"m","ft")) & "' " & TEXT(12*(CONVERT(B2,"m","ft")-INT(CONVERT(B2,"m","ft"))),"0.00") & """"

1.4 Преобразование футов и дюймов в дюймы и наоборот в Excel с помощью формулы

Как показано на скриншоте ниже, если вы хотите преобразовать измерение в футах и дюймах только в дюймы или наоборот в Excel, используйте следующие формулы.

Преобразование футов и дюймов в дюймы

Чтобы преобразовать значение в футах и дюймах в дюймы, используйте формулу на основе функций LEFT, FIND, MID и SUBSTITUTE следующим образом.

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

=LEFT(B3,FIND("'",B3)-1)*12+SUBSTITUTE(MID(B3,FIND("'",B3)+1,LEN(B3)),"""","")

Преобразование дюймов в футы и дюймы

Чтобы преобразовать дюймы в футы и дюймы, выполните следующие действия.

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

=INT(B3/12)&' '&MOD(B3,12)&""""

1.5 Преобразование углов между градусами и радианами с помощью формулы

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

Преобразование градусов в радианы

Чтобы преобразовать градусы в радианы, как показано на скриншоте ниже, используйте функцию RADIANS.

Синтаксис

=RADIANS(угол)

Аргумент

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

=RADIANS(B3)

Преобразование радиан в градусы

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

Синтаксис

=DEGREES(угол)

Аргумент

Выберите пустую ячейку (например, D3), введите следующую формулу и нажмите Enter, чтобы получить первый градус. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные значения в градусах.

=DEGREES(B3)

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

=B3*180/PI()

где функция PI возвращает число3.14159265358979 — математическую константу пи. Щелкните, чтобы узнать больше о функции PI.

1.6 Легко преобразовывайте между различными единицами измерения с помощью удобного инструмента

Здесь мы рекомендуем удобную функцию – Конвертирование единиц Kutools для Excel. Она собирает различные виды единиц измерения в одном диалоговом окне, чтобы вы могли легко преобразовать число из одной системы измерения в другую. Ознакомьтесь с инструкцией ниже.

Включает следующие системы измерения:

1. Выделите диапазон ячеек с числами, которые вы хотите преобразовать из одной системы измерения в другую, затем включите функцию Конвертирование единиц, нажав Kutools > Текст (или Преобразование в разных режимах панели инструментов) > Конвертирование единиц. См. скриншот:

2. В диалоговом окне Конвертирование единиц выполните следующие настройки.

В этом примере я собираюсь преобразовать углы из градусов в радианы, поэтому выбираю Угол из списка, затем отдельно выбираю Градус и Радиан в левых и правых полях, и нажимаю OK.

Советы:

2. Преобразование чисел в различные системы счисления в Excel

В этом разделе показано, как преобразовать числа в различные системы счисления в Excel.

2.1 Преобразование чисел в различные системы счисления с помощью встроенных функций

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

Преобразование десятичного числа в шестнадцатеричное

Чтобы преобразовать десятичное число в шестнадцатеричное, используйте функцию DEC2HEX.

Синтаксис

DEC2HEX(число, [разрядов])

Аргументы

Выберите ячейку, введите формулу ниже и нажмите Enter, чтобы получить первое шестнадцатеричное число. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные значения.

=DEC2HEX(B3)

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

Выберите нужную функцию и примените ее, как показано выше.

2.2 Легко преобразовывайте числа в различные системы счисления с помощью удобного инструмента

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

1. Выделите диапазон ячеек, где вы хотите преобразовать числа из одной системы в другую, затем включите функцию, нажав Kutools > Текст (или Преобразование) > Конвертация систем счисления.

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

В этом случае мне нужно преобразовать десятичные числа в шестнадцатеричные, поэтому я выбираю Десятичная и Шестнадцатеричная в левой и правой части соответственно.

Советы:

3. Преобразование между числом и текстом (число, сохраненное как текст) в Excel

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

3.1 Преобразование числа в текст (число, сохраненное как текст) в Excel

Иногда требуется преобразовать числа в текст. Например, если нужно сохранить длинное число (более11 знаков) в таблице, его нужно преобразовать в текст, чтобы Excel не преобразовал его в экспоненциальный формат. Здесь мы предлагаем три способа преобразования чисел в текст.

3.1.1 Преобразование числа в текст с помощью функции TEXT

Чтобы преобразовать числа в таблице ниже в текст, используйте функцию TEXT.

1. Выберите ячейку (например, D3), введите формулу ниже и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=TEXT(B3,"0")

Щелкните здесь , чтобы узнать больше о функции TEXT в Excel.

Теперь необходимо преобразовать формулы в значения.

3. Выделите все ячейки с результатами, нажмите Ctrl + C для копирования, затем одновременно нажмите Ctrl + Alt + V или Alt + E + S, чтобы открыть диалоговое окно Специальная вставка.

4. В диалоговом окне Специальная вставка выберите опцию Значения в разделе Вставить и нажмите OK для сохранения изменений. См. скриншот:

Теперь вы увидите маленький зеленый треугольник в верхнем левом углу каждой ячейки в выделении. Числа преобразованы в текст. См. скриншот:

Примечания:

3.1.2 Преобразование числа в текст с помощью команды Формат ячеек

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

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

2. Щелкните правой кнопкой мыши по выделению и выберите Формат ячеек в контекстном меню.

3. В открывшемся окне Формат ячеек выберите Текст в списке Категория на вкладке Число и нажмите OK для сохранения изменений.

Теперь выбранные ячейки отформатированы как текст.

3.1.3 Преобразование числа в текст с помощью команды Текст по столбцам

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

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

2. Нажмите Данные > Текст по столбцам.

3. В открывшемся мастере Текст по столбцам нажимайте кнопки Далее, чтобы пройти шаги 1 и2. На шаге3 выберите опцию Текст в разделе Формат данных столбца и нажмите Готово. См. скриншот:

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

3.1.4 Преобразование числа в текст с помощью удобного инструмента

Кроме вышеуказанных методов, Преобразовать между текстом и числом Kutools для Excel позволяет легко преобразовать диапазон чисел в текст. Установите и попробуйте следующий способ.

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

2. Нажмите Kutools > Текст (или Преобразование) > Преобразовать между текстом и числом.

3. В диалоговом окне Преобразовать между текстом и числом выберите опцию Число в текст, затем нажмите OK.

Теперь все выбранные числа преобразованы в текст, как показано на скриншоте ниже.

3.2 Преобразование текста (число, сохраненное как текст) в число в Excel

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

3.2.1 Преобразование текста в число с помощью опции Преобразовать в число

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

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

2. Щелкните по значку предупреждения и выберите Преобразовать в число из выпадающего меню.

Теперь выбранные тексты преобразованы в числа, как показано на скриншоте ниже.

3.2.2 Преобразование текста в число с помощью команды Специальная вставка

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

1. Выберите пустую ячейку, нажмите Ctrl + C для копирования.

2. Выделите диапазон ячеек, которые нужно преобразовать в числа, затем одновременно нажмите Ctrl + Alt + V или Alt + E + S.

3. В диалоговом окне Специальная вставка выберите Значения в разделе Вставить, выберите Сложение в разделе Вычисление и нажмите OK.

См. следующий скриншот:

Теперь выбранные ячейки сразу преобразованы в числа.

3.2.3 Преобразование текста в число путем изменения формата ячейки

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

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

2. Щелкните правой кнопкой мыши по выделению и выберите Формат ячеек в контекстном меню.

3. В открывшемся окне Формат ячеек останьтесь на вкладке Число и выполните следующие действия.

Теперь выбранные текстовые числа преобразованы в обычные числа.

3.2.4 Легко преобразовывайте текст в число с помощью удобного инструмента

Здесь мы рекомендуем удобный инструмент — функцию Преобразовать между текстом и числом Kutools для Excel. С ее помощью вы можете легко преобразовать текст в выделении или нескольких выделениях в числа.

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

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

2. Нажмите Kutools > Текст (или Преобразование) > Преобразовать между текстом и числом.

3. В диалоговом окне Преобразовать между текстом и числом выберите опцию Текст в число и нажмите OK.

Теперь тексты в выбранном диапазоне сразу преобразованы в числа.

4. Преобразование валют в Excel

Допустим, у вас есть большой валютный отчет в долларах США, и вы хотите преобразовать USD в валюту вашей страны, например, евро. По умолчанию в Excel нет встроенных инструментов для конвертации валют. В этом разделе вы узнаете, как преобразовать между любыми двумя валютами в Excel двумя способами.

4.1 Преобразование между любыми двумя валютами с учетом обменных курсов в Excel

Перед использованием этого метода необходимо узнать текущий обменный курс между двумя валютами. В этом случае я хочу преобразовать доллары США в евро, поэтому использую текущий курс:1 USD =0,95 EUR.

1. Введите обменный курс0,95 в пустую ячейку, например, D3.

2. Выберите верхнюю ячейку E3, введите следующую формулу и нажмите Enter, чтобы преобразовать первый доллар США в евро. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=C3*$D$3

Теперь все доллары США преобразованы в евро, как показано на скриншоте выше.

4.2 Легко преобразовывайте между любыми двумя валютами с помощью удобного инструмента

Каждый раз искать актуальный курс валют — неудобно. Функция Конвертация валют Kutools для Excel собирает все валюты в одном диалоговом окне. С ее помощью вы можете легко конвертировать одну валюту в другую по текущему курсу.

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

2. Нажмите Kutools > Текст (или Преобразование) > Конвертация валют.

2. В открывшемся диалоговом окне Конвертация валют выполните следующие настройки.

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

5. Преобразование между часовыми поясами в Excel

В этом разделе показано, как преобразовать время из одного часового пояса в другой. Как показано на скриншоте ниже, чтобы преобразовать время в Пекине из диапазона C3:C8 во время других городов, используйте следующий способ.

5.1 Преобразование времени из одного часового пояса в другой с помощью формулы

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

1. Сначала определите разницу во времени между двумя часовыми поясами.

В этом случае я ввожу соответствующую разницу во времени в каждую ячейку диапазона F3:F8.

Например, Пекин, Китай, опережает Нью-Йорк, США, на12 часов, поэтому я ввожу разницу -12 в ячейку рядом с Нью-Йорком. См. скриншот:

2. Выберите ячейку, введите следующую формулу и нажмите Enter, чтобы получить время в Нью-Йорке. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить время в других городах.

=MOD(C3+(F3/24),1)

Примечание: при работе с датами, которые содержат и дату, и время, например5/10/20222:55:00 PM, используйте следующую формулу.

=datetime+(hours/24)

6. Преобразование между арабскими и римскими числами в Excel

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

6.1 Преобразование арабских чисел в римские с помощью функции ROMAN

Встроенная функция ROMAN в Excel позволяет преобразовать арабское число в римское в виде текста.

Синтаксис

ROMAN(число, [форма])

Аргументы

Чтобы преобразовать числа в таблице ниже в римские, используйте функцию ROMAN следующим образом.

1. Выберите ячейку, введите следующую формулу и нажмите Enter, чтобы преобразовать первое арабское число в римское. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=ROMAN(B3)

Советы: Щелкните, чтобы узнать больше о функции ROMAN.

6.2 Преобразование римских чисел в арабские с помощью формулы

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

1. Выберите пустую ячейку, например D3, введите следующую формулу и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=MATCH(B3,INDEX(ROMAN(ROW(INDIRECT("1:4000"))),0),0)

Примечание: В формуле B3 — это ячейка с арабским числом, которое вы хотите преобразовать в римское.

6.3 Легко преобразовывайте между арабскими и римскими числами с помощью удобного инструмента

Запоминать разные формулы для преобразования между арабскими и римскими числами в Excel неудобно. Вот удобный инструмент — Kutools для Excel. С помощью функции Конвертация между римскими и арабскими числами вы можете легко преобразовывать между арабскими и римскими числами всего за несколько кликов.

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

2. Нажмите Kutools > Текст (или Преобразование) > Конвертация между римскими и арабскими числами.

3. В диалоговом окне Конвертация между римскими и арабскими числами выберите опцию Арабские в римские или Римские в арабские в зависимости от исходных данных, затем нажмите OK. См. скриншот:

Теперь выбранные данные сразу преобразованы в римские или арабские числа.

7. Преобразование формул в результаты в Excel

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

7.1 Преобразование формул в результаты с помощью команды Специальная вставка

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

1. Выделите ячейки с формулами, которые нужно преобразовать в результаты, затем нажмите Ctrl + C для копирования.

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

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

7.2 Быстрое преобразование формул в результаты одним кликом

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

1. Выделите диапазон или несколько диапазонов с формулами, которые нужно преобразовать.

2. Нажмите Kutools > К фактическим.

Теперь все формулы в выбранном диапазоне(ах) сразу преобразованы в результаты.

8. Преобразование чисел в порядковые номера в Excel

Как показано на скриншоте ниже, чтобы преобразовать числа из B3:B12 в порядковые номера, в этом разделе приведены три способа.

8.1 Преобразование чисел в порядковые номера с помощью формулы

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

1. Выберите пустую ячейку (например, C3), скопируйте или введите формулу ниже и нажмите Enter, чтобы получить порядковый номер первого числа. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные порядковые номера.

=B3&IF(OR(--RIGHT(B3,2)={11,12,13}),"th",IFERROR(CHOOSE(RIGHT(B3),"st","nd","rd"),"th"))

Примечание: B3 — это первая ячейка диапазона с числами, которые нужно преобразовать в порядковые номера. При необходимости измените ее.

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

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

1. Одновременно нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, затем скопируйте следующий код VBA в окно модуля.

VBA-код: Преобразование чисел в порядковые номера в Excel

Function Addth(pNumber As String) As String
'Updateby20220516
Select Case CLng(VBA.Right(pNumber, 1))
    Case 1
    Addth = pNumber & "st"
    Case 2
    Addth = pNumber & "nd"
    Case 3
    Addth = pNumber & "rd"
    Case Else
    Addth = pNumber & "th"
End Select
Select Case VBA.CLng(VBA.Right(pNumber, 2))
    Case 11, 12, 13
    Addth = pNumber & "th"
End Select
End Function

3. Нажмите Alt + Q, чтобы закрыть окно Microsoft Visual Basic for Applications.

4. Выберите пустую ячейку рядом с первым числом, которое нужно преобразовать в порядковый номер, введите формулу ниже и нажмите Enter, чтобы получить результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=Addth(B3)

8.3 Быстрое преобразование чисел в порядковые номера одним кликом

Для новичков в Excel вышеуказанные методы могут быть не очень удобны. Рекомендуем воспользоваться функцией Преобразовать число в порядковое Kutools для Excel. С ее помощью вы можете массово преобразовать диапазон чисел в порядковые всего одним кликом.

Как показано в демо ниже, просто выделите диапазон ячеек, из которых нужно преобразовать числа в порядковые, затем нажмите Kutools > Текст > Преобразовать число в порядковое.

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

Щелкните, чтобы узнать больше об этой функции.

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

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

9.1 Преобразование чисел в английские слова с помощью пользовательской функции

Как показано на скриншоте ниже, чтобы отобразить числа в виде английских слов в Excel, используйте следующую пользовательскую функцию. Попробуйте.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, затем скопируйте следующий код VBA в окно модуля.

VBA-код: Преобразование чисел в английские слова

Function NumberstoWords(ByVal MyNumber)
'Update by Extendoffice 20220516
Dim xStr As String
Dim xFNum As Integer
Dim xStrPoint
Dim xStrNumber
Dim xPoint As String
Dim xNumber As String
Dim xP() As Variant
Dim xDP
Dim xCnt As Integer
Dim xResult, xT As String
Dim xLen As Integer
On Error Resume Next
xP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
xNumber = Trim(Str(MyNumber))
xDP = InStr(xNumber, ".")
xPoint = ""
xStrNumber = ""
If xDP > 0 Then
xPoint = " point "
xStr = Mid(xNumber, xDP + 1)
xStrPoint = Left(xStr, Len(xNumber) - xDP)
For xFNum = 1 To Len(xStrPoint)
xStr = Mid(xStrPoint, xFNum, 1)
xPoint = xPoint & GetDigits(xStr) & " "
Next xFNum
xNumber = Trim(Left(xNumber, xDP - 1))
End If
xCnt = 0
xResult = ""
xT = ""
xLen = 0
xLen = Int(Len(Str(xNumber)) / 3)
If (Len(Str(xNumber)) Mod 3) = 0 Then xLen = xLen - 1
Do While xNumber <> ""
If xLen = xCnt Then
xT = GetHundredsDigits(Right(xNumber, 3), False)
Else
If xCnt = 0 Then
xT = GetHundredsDigits(Right(xNumber, 3), True)
Else
xT = GetHundredsDigits(Right(xNumber, 3), False)
End If
End If
If xT <> "" Then
xResult = xT & xP(xCnt) & xResult
End If
If Len(xNumber) > 3 Then
xNumber = Left(xNumber, Len(xNumber) - 3)
Else
xNumber = ""
End If
xCnt = xCnt + 1
Loop
xResult = xResult & xPoint
NumberstoWords = xResult
End Function
Function GetHundredsDigits(xHDgt, xB As Boolean)
Dim xRStr As String
Dim xStrNum As String
Dim xStr As String
Dim xI As Integer
Dim xBB As Boolean
xStrNum = xHDgt
xRStr = ""
On Error Resume Next
xBB = True
If Val(xStrNum) = 0 Then Exit Function
xStrNum = Right("000" & xStrNum, 3)
xStr = Mid(xStrNum, 1, 1)
If xStr <> "0" Then
xRStr = GetDigits(Mid(xStrNum, 1, 1)) & "Hundred "
Else
If xB Then
xRStr = "and "
xBB = False
Else
xRStr = " "
xBB = False
End If
End If
If Mid(xStrNum, 2, 2) <> "00" Then
xRStr = xRStr & GetTenDigits(Mid(xStrNum, 2, 2), xBB)
End If
GetHundredsDigits = xRStr
End Function
Function GetTenDigits(xTDgt, xB As Boolean)
Dim xStr As String
Dim xI As Integer
Dim xArr_1() As Variant
Dim xArr_2() As Variant
Dim xT As Boolean
xArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
xStr = ""
xT = True
On Error Resume Next
If Val(Left(xTDgt, 1)) = 1 Then
xI = Val(Right(xTDgt, 1))
If xB Then xStr = "and "
xStr = xStr & xArr_1(xI)
Else
xI = Val(Left(xTDgt, 1))
If Val(Left(xTDgt, 1)) > 1 Then
If xB Then xStr = "and "
xStr = xStr & xArr_2(Val(Left(xTDgt, 1)))
xT = False
End If
If xStr = "" Then
If xB Then
xStr = "and "
End If
End If
If Right(xTDgt, 1) <> "0" Then
xStr = xStr & GetDigits(Right(xTDgt, 1))
End If
End If
GetTenDigits = xStr
End Function
Function GetDigits(xDgt)
Dim xStr As String
Dim xArr_1() As Variant
xArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
xStr = ""
On Error Resume Next
xStr = xArr_1(Val(xDgt))
GetDigits = xStr
End Function

3. Нажмите Alt + Q, чтобы закрыть окно Microsoft Visual Basic for Applications.

4. Выберите пустую ячейку для вывода первого результата, например D3. Введите формулу ниже и нажмите Enter. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=NumberstoWords(B3)

9.2 Преобразование чисел в валютные слова с помощью пользовательской функции

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

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. В открывшемся окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, затем скопируйте следующий код VBA в окно модуля.

VBA-код: Преобразование чисел в английские валютные слова

Function SpellNumberToCurrency(ByVal pNumber)
'Update by Extendoffice 20220516
Dim Dollars, Cents
arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
pNumber = Trim(Str(pNumber))
xDecimal = InStr(pNumber, ".")
If xDecimal > 0 Then
    Cents = GetTens(Left(Mid(pNumber, xDecimal + 1) & "00", 2))
    pNumber = Trim(Left(pNumber, xDecimal - 1))
End If
xIndex = 1
Do While pNumber <> ""
    xHundred = ""
    xValue = Right(pNumber, 3)
    If Val(xValue) <> 0 Then
        xValue = Right("000" & xValue, 3)
        If Mid(xValue, 1, 1) <> "0" Then
            xHundred = GetDigit(Mid(xValue, 1, 1)) & " Hundred "
        End If
        If Mid(xValue, 2, 1) <> "0" Then
            xHundred = xHundred & GetTens(Mid(xValue, 2))
        Else
            xHundred = xHundred & GetDigit(Mid(xValue, 3))
        End If
    End If
    If xHundred <> "" Then
        Dollars = xHundred & arr(xIndex) & Dollars
    End If
    If Len(pNumber) > 3 Then
        pNumber = Left(pNumber, Len(pNumber) - 3)
    Else
        pNumber = ""
    End If
    xIndex = xIndex + 1
Loop
Select Case Dollars
    Case ""
        Dollars = "No Dollars"
    Case "One"
        Dollars = "One Dollar"
    Case Else
        Dollars = Dollars & " Dollars"
End Select
Select Case Cents
    Case ""
        Cents = " and No Cents"
    Case "One"
        Cents = " and One Cent"
    Case Else
        Cents = " and " & Cents & " Cents"
End Select
SpellNumberToEnglish = Dollars & Cents
End Function
Function GetTens(pTens)
Dim Result As String
Result = ""
If Val(Left(pTens, 1)) = 1 Then
    Select Case Val(pTens)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        Case 12: Result = "Twelve"
        Case 13: Result = "Thirteen"
        Case 14: Result = "Fourteen"
        Case 15: Result = "Fifteen"
        Case 16: Result = "Sixteen"
        Case 17: Result = "Seventeen"
        Case 18: Result = "Eighteen"
        Case 19: Result = "Nineteen"
        Case Else
    End Select
Else
Select Case Val(Left(pTens, 1))
    Case 2: Result = "Twenty "
    Case 3: Result = "Thirty "
    Case 4: Result = "Forty "
    Case 5: Result = "Fifty "
    Case 6: Result = "Sixty "
    Case 7: Result = "Seventy "
    Case 8: Result = "Eighty "
    Case 9: Result = "Ninety "
    Case Else
End Select
Result = Result & GetDigit(Right(pTens, 1))
End If
GetTens = Result
End Function
Function GetDigit(pDigit)
Select Case Val(pDigit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
End Select
End Function

3. Нажмите Alt + Q, чтобы закрыть окно Microsoft Visual Basic for Applications.

4. Выберите пустую ячейку для вывода первого результата, например D3. Введите формулу ниже и нажмите Enter. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

=SpellNumberToCurrency(B3)

9.3 Легко преобразовывайте числа в английские/валютные слова с помощью удобного инструмента

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

1. Выделите диапазон ячеек, которые нужно преобразовать в английские слова или валютные слова, затем нажмите Kutools > Текст > Преобразовать в слова. См. скриншот:

2. В диалоговом окне Преобразовать в валютные слова выполните следующие настройки.

Чтобы преобразовать выбранные числа в английские слова:

Чтобы преобразовать выбранные числа в английские валютные слова:

Теперь выбранные числа преобразованы в английские слова или валютные слова в зависимости от вашей настройки.

Щелкните, чтобы узнать больше об этой функции.

10. Преобразование чисел в даты в Excel

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

10.1 Преобразование серийных номеров в даты в Excel

Иногда Excel возвращает серийный номер (5-значное число) вместо введенной даты (см. скриншот ниже). Это происходит потому, что Excel хранит дату и время как серийные числа. Чтобы преобразовать серийный номер в дату, используйте один из трех способов ниже.

10.1.1 Преобразование серийных номеров в даты с помощью функции Формат ячеек

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

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

2. В окне Формат ячеек выполните следующие действия:

Теперь выбранные серийные номера преобразованы в даты, как показано на скриншоте ниже.

10.1.2 Преобразование серийных номеров в даты с помощью формулы

Также можно использовать формулу для преобразования серийного номера в дату в Excel.

1. Выберите ячейку, введите формулу ниже и нажмите Enter, чтобы преобразовать первый серийный номер в дату. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные даты.

=TEXT(B3,"m/d/yyyy")

Примечание: В формуле вы можете изменить "m/d/yyyy" на другой формат даты по необходимости.

10.1.3 Легко преобразовывайте серийные номера в даты с помощью удобного инструмента

Пробовали ли вы использовать сторонние инструменты для работы в Excel? Если вы хотите повысить производительность и сэкономить время, рекомендуем воспользоваться функцией Применить формат даты Kutools для Excel. Она позволяет преобразовать серийные номера в диапазоне или нескольких диапазонах в нужный формат даты.

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

2. Нажмите Kutools > Формат > Применить формат даты.

3. В диалоговом окне Применить формат даты выполните следующие действия:

Примечание: После выбора формата даты вы можете предварительно просмотреть результат в поле Предварительный просмотр.

Теперь все выбранные числа в диапазоне(ах) сразу преобразованы в даты.

Щелкните, чтобы узнать больше об этой функции.

10.2 Преобразование8-значных чисел в даты, распознаваемые Excel

10.2.1 Преобразование8-значных чисел в даты с помощью формул

Чтобы преобразовать8-значное число в дату, распознаваемую Excel, используйте функцию DATE в сочетании с функциями RIGHT, LEFT и MID. Выполните следующие действия.

1. Выберите ячейку, введите формулу ниже и нажмите Enter, чтобы преобразовать первое8-значное число в дату. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз, чтобы получить остальные даты.

=DATE(LEFT(B3,4),MID(B3,5,2),RIGHT(B3,2))

10.2.2 Преобразование8-значных чисел в даты с помощью команды Текст по столбцам

Встроенная команда Текст по столбцам помогает преобразовать диапазон8-значных чисел в даты, распознаваемые Excel.

1. Выделите диапазон8-значных чисел, нажмите Дата > Текст по столбцам.

2. В диалоговом окне Текст по столбцам выполните следующие настройки:

Теперь все выбранные8-значные числа преобразованы в даты, распознаваемые Excel.

10.2.3 Легко преобразовывайте8-значные числа в даты с помощью удобного инструмента

Здесь мы представляем функцию Распознавание даты Kutools для Outlook, которая позволяет легко преобразовать8-значные числа в одном или нескольких диапазонах в даты, распознаваемые Excel, всего одним кликом.

1. Выделите8-значные числа в одном или нескольких диапазонах.

2. Нажмите Kutools > Текст > Распознавание даты. Теперь все выбранные числа сразу преобразованы в даты, как показано в демо ниже.

Щелкните, чтобы узнать больше об этой функции.

11. Преобразование числа во время или наоборот в Excel

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

11.1 Преобразование числа во время (чч:мм:сс) в Excel

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

11.1.1 Преобразование десятичных часов во время (чч:мм;сс)

Чтобы преобразовать часы в десятичном формате в формат времени чч:мм:сс Excel, разделите число на24 (общее количество часов в сутках), затем отформатируйте ячейку как время. Выполните следующие действия.

1. Выберите ячейку, введите формулу ниже и нажмите Enter. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз на остальные ячейки.

=B3/$C$3

В этом случае я ввел число24 в ячейку C3. Вы также можете напрямую ввести24 в формулу, например:

=B3/24

2. Выделите все ячейки с результатами и одновременно нажмите Ctrl +1, чтобы открыть окно Формат ячеек.

3. В окне Формат ячеек выполните следующие действия:

Теперь результаты отформатированы как время чч:мм:сс, как показано на скриншоте ниже.

11.1.2 Преобразование десятичных минут во время (чч:мм;сс)

Чтобы преобразовать минуты в десятичном формате в формат времени чч:мм:сс Excel, разделите число на1440 (общее количество минут в сутках), затем отформатируйте ячейку как время. Шаги такие же, как выше.

1. Выберите ячейку, введите формулу ниже и нажмите Enter, чтобы получить первый результат. Затем выберите эту ячейку и перетащите маркер автозаполнения вниз на остальные ячейки.

=B3/$C$3

В этом случае я ввел число1440 в ячейку C3. Вы также можете напрямую ввести24 в формулу, например:

=B3/1440

2. Затем отформатируйте ячейки с результатами как время. См. шаги выше.

В итоге вы получите время, как показано на скриншоте ниже.

11.1.3 Преобразование десятичных секунд во время (чч:мм;сс)

Шаги такие же, как выше, для преобразования секунд в десятичном формате в формат времени чч:мм:сс Excel. Просто измените делитель на86400 (общее количество секунд в сутках), затем отформатируйте ячейку как время.

В этом случае формула будет следующей:

=B3/$C$3

или

=B3/86400

Затем отформатируйте ячейки с результатами как время. См. шаги выше.

В итоге вы получите время, как показано на скриншоте ниже.

11.2 Преобразование времени (чч:мм:сс) в десятичное число в Excel

В этом разделе показаны различные формулы для преобразования времени в часы, минуты, секунды в Excel.

11.2.1 Преобразование времени в часы с помощью формул

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

1. Примените следующую формулу:

=B3*$C$3

где B3 содержит значение времени, а $C$3 — число24.

Или вы можете напрямую ввести число24 в формулу следующим образом:

=B3*24

После применения формул результаты отображаются как время.

Примечание: Если не нужно сохранять дробную часть результата, используйте формулу ниже.

=INT(B3*24)

2. Необходимо отформатировать ячейки с результатами как Число.

Выделите ячейки с результатами, нажмите Ctrl +1 для открытия окна Формат ячеек. В окне выберите Число в списке Категория на вкладке Число, укажите количество знаков после запятой в поле Десятичные знаки и нажмите OK для сохранения изменений.

Теперь ячейки с результатами отображаются как числа, как показано на скриншоте ниже.

11.2.2 Преобразование времени в минуты с помощью формул

Чтобы преобразовать время в минуты, просто умножьте время на1440 (количество минут в сутках).

Примените следующую формулу:

=B3*$C$3

где B3 содержит значение времени, а $C$3 — число1440.

Или вы можете напрямую ввести число1440 в формулу следующим образом:

=B3*1440

Примечание: Если не нужно сохранять дробную часть результата, используйте формулу ниже.

=INT(B3*1440)

Затем отформатируйте ячейки с результатами как Число. См. шаги выше.

В итоге вы получите минуты, как показано на скриншоте ниже.

11.2.3 Преобразование времени в секунды с помощью формул

Чтобы преобразовать время в секунды, умножьте время на86400 (количество секунд в сутках).

Примените следующую формулу:

=B3*$C$3

где B3 содержит значение времени, а $C$3 — число86400.

Или вы можете напрямую ввести число86400 в формулу следующим образом:

=B3*86400

Примечание: Если не нужно сохранять дробную часть результата, используйте формулу ниже.

=INT(B3*86400)

Затем отформатируйте ячейки с результатами как Число. См. шаги выше.

В итоге вы получите секунды, как показано на скриншоте ниже.

11.2.4 Легко преобразовывайте время в часы/минуты/секунды с помощью удобного инструмента

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

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

2. Нажмите Kutools > Текст > Преобразование времени.

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

Щелкните, чтобы узнать больше об этой функции.

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