Функция текст в excel: Функция ТЕКСТ - Служба поддержки Майкрософт

Содержание

ТЕКСТ Функция в Excel — voxt.ru

Текстовая функция в Excel

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

Текстовая формула

Ниже приведена формула ТЕКСТА в Excel:

значение: что мы хотим отформатировать

format_text: код формата, который мы хотим применить

Как использовать функцию ТЕКСТ в Excel? (с примерами)

Функция ТЕКСТ в Excel очень проста и удобна в использовании. Давайте разберемся с работой TEXT in excel на нескольких примерах.

Вы можете скачать этот шаблон Excel с функцией ТЕКСТ здесь — Шаблон Excel с функцией ТЕКСТ

Пример # 1

Например, у нас есть время и дата в столбцах A и B, и мы хотим, чтобы оба значения объединялись с пробелом в качестве разделителя.

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

7:00:00 19.06.2018, 7:15:00 19.06.2018, и так далее.

Теперь, когда мы пытаемся объединить оба значения, мы получаем значения, показанные ниже:

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

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

На время мы хотим отобразить его как часы: минуты: секунды AM / PM и дату как Месяц / Дата / Год

Excel предоставляет список настраиваемых форматов и нужный нам формат. Мы можем проверить, открыв окно ячеек формата.

Нажмите crtl + 1 в окнах и +1 в Mac, чтобы открыть Формат ячеек окно. в номер вкладка, перейдите в Custom.

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

В разделе Тип: скопируйте формат даты (м / д / гггг) и времени (ч: мм: сс AM / PM).

Теперь в C2 мы используем функцию ТЕКСТ в Excel, которая принимает два аргумента: значение и код формата, который мы хотим применить к этому значению. Итак, формула ТЕКСТ в Excel становится

= ТЕКСТ (A2; «ч: ММ: СС AM / PM») & »« & ТЕКСТ (B2; «м / д / гггг»)

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

Примечание: код формата должен быть заключен в двойные кавычки, иначе; функция ТЕКСТ в Excel выдаст ошибку # ИМЯ?

пример

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

Пример # 2

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

Предположим, у нас есть список клиентов с номерами их мобильных телефонов в столбцах A и B. Номера мобильных телефонов указаны с кодом страны.

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

Теперь мы хотим, чтобы он был в таком формате, чтобы код страны и номер мобильного телефона можно было легко прочитать. Мы можем сделать его читаемым с помощью функции ТЕКСТ Excel.

Код страны мобильного номера состоит из 12 цифр, первые две обозначают код страны, а оставшаяся часть — контактный номер мобильного телефона. Итак, код формата, который мы здесь используем, — «############».

Итак, формула ТЕКСТ в Excel становится,

= ТЕКСТ (B2, ”############”)

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

Код формата текстовой функции при вручении даты

При работе с датами ниже приведен список кодов формата.

Пример # 3

Конкатенация со строкой

Допустим, у нас есть список детей и дата их рождения.

Теперь мы хотим отобразить имя и дату рождения в столбце C, например, Джон родился 8 декабря 2015 года и аналогично для других детей.

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

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

Итак, формат для даты указан, воспользуемся кодом формата «Д мммм, гггг».

Если мы хотим использовать аббревиатуру месяца вместо полного имени, мы изменим mmmm на ‘ммм. ‘

Если мы хотим отобразить день с нулем в начале, если это день с одним значением, например (1-9), мы заменим ‘d‘ с участием ‘дд. ‘

Итак, в зависимости от требований и формата, в котором мы хотим отображать значение, мы можем изменить код формата. Мы также можем настроить встроенные коды формата в зависимости от требований.

Пример # 4

Предположим, у нас есть валовая сумма и сумма расходов, и мы хотим отобразить в ячейке A13 «Чистая прибыль» & B11

Когда мы применяем формулу ТЕКСТ в Excel,

= «Чистая прибыль» & B11 к B11, получаем

Формула в A13 не отображает отформатированное число, которое выражено в долларах. Итак, пересмотренная формула ТЕКСТ в Excel, которая использует функцию ТЕКСТ для применения к форматированию значения в A13:

= «Чистая прибыль равна» & ТЕКСТ (B11, «# $, ## 0,00 ″)

Эта формула ТЕКСТ в Excel отображает текст вместе с красиво отформатированным значением: Чистая прибыль составляет 52 291,00 долларов США.

Функция ТЕКСТ в видео Excel

УЗНАТЬ БОЛЬШЕ >>

Post Views: 383

Как обрезать числа и текст в Excel (методы 2) • BUOM

Автор: редакционная команда Indeed

13 апреля 2022 г.

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

Что такое усечение в Excel?

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

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

Как используется усечение?

Вот некоторые обстоятельства, при которых вы можете обрезать число в Excel:

Чтобы создать целое число

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

Чтобы упростить данные в стандарт

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

Чтобы определить дату

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

Использование этой функции таким образом может помочь вам более четко организовать ваши данные. Например, если в вашей ячейке указано «29.11.21 13:30», вы можете обрезать первые пять символов из ячейки, чтобы результатом была дата «29.11.21», то есть дата ввода.

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

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

1. Подготовьте данные

Первый шаг — разместить все ваши данные на листе Excel, на котором показаны все десятичные дроби. Для этого выберите столбец данных, который вы хотите обрезать, и щелкните вкладку «Главная» на панели инструментов в верхней части программы. Щелкните значок «Число», чтобы получить доступ к параметрам форматирования чисел в электронной таблице. Два значка показывают десятичные точки с нулями и стрелками, указывая, увеличиваются или уменьшаются нули. Выберите значок со стрелкой, указывающей влево, пока не будут показаны все ненулевые десятичные точки. Видя эти десятичные дроби, вы сможете предотвратить округление данных.

Если вы еще не отформатировали, вы также можете изменить форматирование ячеек рабочего листа на числа, а не на текст или дроби. Вы можете сделать это, выбрав значок «Число» и выбрав «Число» в появившемся раскрывающемся меню.

2. Определите количество цифр, которые вы хотите

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

Вы также можете обрезать до разных десятичных знаков. Чтобы иметь уникальные цифры для каждой строки, вы можете создать столбец справа от данных, которые вы хотите усечь. Назовите этот столбец «Цифры». В этом столбце вы можете ввести количество цифр, которые вы хотите отображать после запятой для каждой отдельной строки. Например, если у вас есть три числа, 12,1223, 14,664 и 2,3443, и вы хотите, чтобы каждое число усекалось до другого десятичного знака, вы можете создать два столбца, которые отражают исходное число и цифры, которые вы хотите урезать:

DataDigits12.1223114.566412.34433

3. Создайте столбец результатов

Чтобы использовать функцию TRUNC, вам нужно добавить столбец в электронную таблицу, чтобы вставить функцию и заполнить ее результатом. Выберите столбец справа от данных или столбца «Цифры». Размещение данных справа может упростить сортировку данных, поиск по ним или скрытие исходных данных, чтобы ваша электронная таблица выглядела чище.

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

4. Вставьте формулу TRUNC.

В первой ячейке столбца, который вы хотите заполнить усеченным числом, введите знак «=», чтобы указать, что вы используете формулу. Затем введите TRUNC. Используйте открывающую скобку, чтобы добавить операторы формулы. Первое число, которое вы вводите, — это число, которое вы хотите обрезать. Возможно, вам придется перепроверить свою формулу, потому что опечатки могут привести к проблемам, которые могут ограничить вашу возможность скопировать формулу или изменить исходное число.

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

=ОТРЕЗ (13.565656,2)

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

5. Добавьте ссылки на ячейки

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

=ОБРЕЗ(A2, B2)

Ячейка A2 содержит число 13,565656, а ячейка B2 содержит число два, указывающее количество цифр после запятой. Эта формула возвращает число 13,56 с четырьмя дополнительными цифрами, преобразованными в нули и больше не появляющимися.

6. Скопируйте формулу вниз по столбцу

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

Как обрезать текст в Excel

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

1. Подготовьте данные

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

= TRIM (ссылка на ячейку), где ссылка на ячейку — это ячейка, содержащая данные, которые вы хотите обрезать.

2. Определите количество символов, которое вы хотите вернуть

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

3. Выберите между функцией ВПРАВО или ВЛЕВО

При усечении текста вы также можете решить, хотите ли вы, чтобы формула возвращала символы, начинающиеся с правой или левой стороны ячейки, с помощью функции ВПРАВО или ВЛЕВО. Например, если у вас есть список фамилий и вы хотите вернуть только первые три буквы каждой, вы можете использовать функцию ПРАВИЛЬНО. Если у вас есть список городов с аббревиатурой штата, указанной после него, вы можете использовать функцию ВЛЕВО, чтобы указать, что вы хотите, чтобы результат отображал последние два символа в ячейке.

4. Введите формулу

Каждая функция начинается со знака равенства, чтобы указать, что вы используете формулу. В зависимости от ваших потребностей введите RIGHT или LEFT после знака равенства, а затем откройте скобки. Первый оператор после круглой скобки может быть ссылкой на ячейку, указывающей текстовую строку, которую вы хотите обрезать. Введите запятую, чтобы указать новую запись, затем введите количество символов, которые вы хотите вернуть, или ссылку на ячейку, указывающую ту же информацию. Закройте скобки и нажмите «Ввод», чтобы заполнить ячейку результатом.

Например, если вы использовали функцию ВЛЕВО, чтобы найти первые три буквы в имени, ваша функция может быть такой:

=LEFT(A2,3), если A2 ссылается на первую ячейку в столбце фамилий

Ячейка может содержать RILEY, и результатом будет RIL. Если Вы использовали функцию ПРАВИЛЬНО, чтобы найти аббревиатуры штатов в списке городов, функция может быть следующей:

=ВПРАВО(A2,2), если A2 ссылается на первую ячейку в столбце городов

Ячейка может содержать «Бруклин, Нью-Йорк», и результатом будет «Нью-Йорк».

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.

10 лучших текстовых функций в Excel

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

Текстовые функции Excel

Определение: Функция НАЙТИ используется в Excel для поиска позиции требуемой текстовой строки в другой доступной текстовой строке

Аргументы/Синтаксис: НАЙТИ(найти_текст, внутри_текста, [начальный_номер])

найти_текст: Это обязательный аргумент. Требуется текст, который пользователь хочет найти

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

start_num: Это необязательный аргумент. Он указывает символ, с которого следует начать поиск. По умолчанию принимает значение 1.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет найти позицию текста «an» в имени человека, можно использовать функции НАЙТИ; например, НАЙТИ(«an»,»Roland Watson»,1).

Выход этой функции равен 4, поскольку текст начальной позиции «an» равен 4 в «Roland Watson».

СЛЕВА:

Определение: ЛЕВАЯ функция в Excel возвращает левые символы из текстовой строки на основе количества символов, указанного пользователем.

Аргументы/Синтаксис: LEFT(текст, [число_знаков])

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

num_chars: Это необязательный аргумент. Он указывает количество символов, которое пользователь хочет извлечь с левой стороны. По умолчанию принимает значение 1.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет извлечь первые 5 букв имени, можно использовать ЛЕВЫЕ функции; например, ЛЕВЫЙ(«Роланд Ватсон»,5). Результатом этой функции является «Rolan».

СПРАВА:

Определение: ПРАВАЯ функция в Excel возвращает правые символы из текстовой строки на основе количества символов, указанного пользователем.

Аргументы/Синтаксис: ПРАВО(текст, [число_символов])

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

num_chars: Это необязательный аргумент. Он указывает количество символов, которое пользователь хочет извлечь с правой стороны. По умолчанию принимает значение 1.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет извлечь последние 5 букв имени, можно использовать функцию ПРАВИЛЬНО; например, ПРАВО(«Роланд Ватсон», 5). Выход этой функции — «атсон».

СРЕДНИЙ:

Определение: Функция MID в Excel возвращает количество символов из текстовой строки на основе начальной позиции и количества символов, указанных пользователем.

Аргументы/Синтаксис: MID(text, start_num, num_chars)

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

start_num: Это обязательный аргумент. Он указывает символ, с которого следует начать извлечение.

num_chars: Обязательный аргумент. Он указывает количество символов, которое пользователь хочет извлечь из начальной позиции

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет извлечь текст «и» из имени, можно использовать функцию MID; например, MID(«Роланд Ватсон»,4,3). Результатом этой функции является «и».

ОТДЕЛКА:

Определение: Функция TRIM в Excel удаляет все лишние пробелы из текста, кроме одиночных пробелов между словами. Он используется, когда в тексте есть неравномерные интервалы между словами.

Аргументы/Синтаксис: TRIM(текст)

текст: Обязательный аргумент. Это текстовая строка, содержащая неравномерный интервал между словами

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Но между именем («Роланд») и фамилией («Ватсон») два пробела. Теперь, если пользователь хочет удалить лишние пробелы, можно использовать функцию TRIM; например, TRIM(«Роланд Ватсон»). Результатом этой функции является «Роланд Ватсон».

СОБСТВЕННЫЙ:

Определение: Функция PROPER в Excel делает заглавной первую букву каждого слова в строке и преобразует все остальные буквы слов в нижний регистр.

Аргументы/Синтаксис: PROPER(текст)

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

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «РОЛАНД УОТСОН». Теперь, если пользователь хочет сделать имя правильно, можно использовать функцию PROPER; например, PROPER(«РОЛАНД УОТСОН»). Результатом этой функции является «Роланд Ватсон».

ЗАМЕНА:

Определение: Функция ПОДСТАВИТЬ в Excel используется для замены любой части старой текстовой строки новой текстовой строкой.

Аргументы/Синтаксис: ПОДСТАВИТЬ (текст,старый_текст,новый_текст,[instance_num])

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

old_text: Обязательный аргумент. Это текст, который пользователь хочет заменить

new_text: Обязательный аргумент. Это текст, который пользователь хочет заменить старым текстом на

. [номер_экземпляра]: необязательный аргумент. Указывает, какое вхождение old_text пользователь хочет заменить новым_текстом. Если пользователь указывает instance_num, заменяется только этот экземпляр old_text. В противном случае каждое вхождение old_text в тексте заменяется на new_text.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет заменить «Роланд» на «Джон», можно использовать функцию ЗАМЕНА; например, ПОДСТАВИТЬ («Роланд Ватсон», «Роланд», «Джон»).

Результатом этой функции является «Джон Ватсон».

ВЕРХНИЙ:

Определение: ПРОПИСНАЯ функция в Excel делает заглавными все буквы каждого слова в строке Аргументы/Синтаксис: ПРОПИСНЫЕ(текст)

текст: Это обязательный аргумент. Он берет текстовую строку, которую пользователь хочет полностью использовать заглавными буквами.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «РОЛАНД УОТСОН». Теперь, если пользователь хочет преобразовать имя в полные заглавные буквы, можно использовать функцию ПРОПИСНЫЕ; например, ВЕРХНИЙ(«РОЛАНД УОТСОН»). Выход этой функции: «РОЛАНД УОТСОН»

НИЖНИЙ:

Определение: НИЖНЯЯ функция в Excel преобразует все буквы каждого слова в строке в маленькие буквы

Аргументы/Синтаксис: НИЖНЯЯ(текст)

текст: Это обязательный аргумент. Он принимает текстовую строку, которую пользователь хочет полностью преобразовать в маленькие буквы.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «РОЛАНД УОТСОН». Теперь, если пользователь хочет полностью преобразовать имя в строчные буквы, можно использовать функцию LOWER; например, LOWER(«РОЛАНД УОТСОН»). Результатом этой функции является «roland watson» 9.0003

ПОВТОР:

Определение: Функция ПОВТОР в Excel повторяет указанный текст заданное количество раз. Функция ПОВТОР может использоваться для заполнения ячейки несколькими экземплярами текстовой строки

Аргументы/Синтаксис: ПОВТОР(текст, число_время)

текст: Это обязательный аргумент. Он принимает текстовую строку, которую пользователь хочет повторить несколько раз

число_раз: Это обязательный аргумент. Требуется положительное число, указывающее, сколько раз повторять текст

Пример: REPT(«-«,5) = «—–» Отображает тире (-) 5 раз

Функция ТЕКСТ в Excel: обзор, примеры, как использовать?

Функция Excel ТЕКСТ преобразует число в текстовую строку на основе формата, указанного пользователем. Этот формат предоставляется в качестве аргумента функции ТЕКСТ. Поскольку результирующие выходные данные представляют собой текстовые представления чисел, их нельзя использовать в формулах в том виде, в каком они есть. Поэтому рекомендуется сохранить исходные числа и создать отдельную строку или столбец для преобразованных чисел.

Например, формула «= ТЕКСТ («10/2/2022″», мммм дд, гггг»)» возвращает 10 февраля 2022 г. Исключите начальные и конечные двойные кавычки при вводе этой формулы в Excel.

Целью использования функции ТЕКСТ в Excel является отображение числа в нужном формате. Поскольку эта функция также помогает комбинировать числа с другими текстовыми строками, она делает вывод более разборчивым. Функция ТЕКСТ особенно используется, когда необходимо сделать числовые форматы различных наборов данных идентичными.

Приблизительное время чтения: 21 минута

Содержание

Что такое текстовая функция в Excel?
- Синтаксис функции ТЕКСТ в Excel
- Как использовать функцию ТЕКСТ в Excel?
  - Пример 1. Префикс текстовых строк перед значениями даты в новом формате
  - Пример 2. Объединение значений даты и времени в новом формате
  - Пример 3. Извлечение номера мобильного телефона из его научной записи Префикс текстовой строки к первоначально отформатированному денежному значению 9
  - Рекомендуемые статьи следующее изображение:
    Функция ТЕКСТ в Excel принимает следующие аргументы:
    - Значение: Это число, которое нужно преобразовать в текстовую строку. Помимо числа, в функцию TEXT excel также можно передать дату, время или ссылку на ячейку. Ссылка на ячейку может содержать либо число, либо результат другой функции, который может быть числом или датой.
    - Format_text: Это формат, применяемый к аргументу «значение». Его также называют кодом формата. Он вводится в двойных кавычках в формуле ТЕКСТ Excel. Например, «0.00», «дд/мм/гггг», «чч:мм:сс» и т. д. являются кодами формата.
    Оба указанных аргумента являются обязательными.
    Примечание 1: Код формата «0.00» отображает число с двумя десятичными разрядами. В коде формата «дд/мм/гггг», «дд», «мм» и «гггг» обозначаются дни (две цифры), месяцы (две цифры) и годы (четыре цифры) соответственно. .
    Аналогично, «чч», «мм» и «сс» обозначают часы (две цифры), минуты (две цифры) и секунды (две цифры) соответственно.
    Значение различных форматов даты см. в заголовке «Форматы даты в Excel», приведенном после примера № 4 этой статьи.
    Примечание 2: Функция ТЕКСТ относится к категории текстовых/строковых функций Excel. Функция ТЕКСТ доступна во всех версиях Excel.
    Как использовать функцию ТЕКСТ в Excel?
    Давайте рассмотрим несколько примеров, чтобы понять работу функции ТЕКСТ в Excel.
    Пример №1. Префикс текстовых строк перед значениями даты в новом формате
    В следующей таблице показаны имена пяти детей вместе с датами их рождения. В настоящее время даты представлены в формате м/д/гггг. Рассмотрим два столбца и шесть строк таблицы как столбцы A и B и строки с 1 по 6 Excel.
    Мы хотим выполнить следующие задачи:
    - Присоедините имя и дату рождения строки 2, используя оператор амперсанда. Между соединяемыми значениями не должно быть пробелов.
    - Преобразование каждой даты в формат «дд ммм, гггг». Добавьте префикс имени ребенка и строку «родился» к каждой дате.
    - Показать выходные данные, когда дата строки 2 имеет формат «д ммм, гггг». Пусть префиксы предыдущего пункта остаются прежними.
    - Показывать форматы даты «дд ммм, гггг» (в ячейке C2) и «д ммм, гггг» (в ячейке C6) в одном столбце.
    Объясните результаты, полученные во втором и четвертом пунктах списка. Используйте функцию ТЕКСТ Excel.
    Name of Kid Date of Birth
    John 12/8/2015
    Patricia 1/12/2014
    Ram 3/11/ 2016
    Anita 11/11/2017
    Davis 5/6/2014
    The steps to perform the given tasks by using the TEXT function of Excel are listed as follows:
    Шаг 1: Введите следующую формулу в ячейку C2. Исключить начальные и конечные двойные кавычки при вводе данной формулы.
    «=A2&»»&B2»
    Эта формула (показана на изображении шага 2) объединяет значения ячеек A2 и B2 без пробелов между ними.
    Примечание: Оператор амперсанда (&) помогает соединить значения двух или более ячеек. Он называется оператором конкатенации и используется в качестве альтернативы функции СЦЕПИТЬ в Excel. Нет ограничений на количество значений ячеек, к которым может присоединяться амперсанд.
    Шаг 2: Нажмите клавишу «Ввод». Результат появится в ячейке C2, как показано на следующем рисунке.
    Обратите внимание, что Excel объединил значения ячеек A2 и B2 без пробелов между ними. Однако вывод не в читаемом формате. Причина в том, что дата (8.12.2015) была преобразована в порядковый номер. Число 42346 представляет дату 8 декабря 2015 г.
    Примечание. Дата хранится в Excel как порядковый или порядковый номер. Это связано с тем, что серийный номер упрощает выполнение вычислений. Чтобы просмотреть порядковый номер даты, обратитесь к «примечанию» перед шагом 3 примера № 2.
    Шаг 3: Чтобы применить формат «дд ммм, гггг» и вставить указанные префиксы, введите следующую формулу в ячейку C2.
    «=A2&» родился в «&TEXT(B2»dd mmm, yyyy»)»
    Нажмите клавишу «Enter». Затем перетащите формулу из ячейки C2 в ячейку C6, используя маркер заполнения. Выходные данные показаны на следующем изображении.
    Объяснение выходных данных: В столбце C предыдущего изображения имя ребенка (в столбце A) и текстовая строка (дата рождения) были добавлены к каждой дате столбца B. Поскольку дата указана в подходящий формат, вывод теперь читается.
    Объединенные значения (выходные данные) столбца C представлены в форме выражений, которые легче понять, чем отдельные значения столбцов A и B.
    Обратите внимание, что для вставки пробелов в качестве разделителей в выводе мы пробелы в соответствующих местах в формуле шага 1. Кроме того, запятая также ставится после обозначения месяцев (ммм). Эту запятую можно увидеть в каждой дате вывода (в столбце C).
    Шаг 4: Чтобы увидеть выходные данные, когда формат даты «д ммм, гггг» и префиксы на месте, введите следующую формулу в ячейку C2.
    =A2&» родился в «&TEXT(B2»,d mmm, yyyy»)
    Нажмите клавишу «Enter». Результат появится в ячейке C2, как показано на следующем рисунке.
    Обратите внимание, что начальный нуль перед датой 8 декабря 2015 г. (в ячейке C2) исчез. Этот ноль был в ячейке C2 предыдущего изображения. Он исчез, потому что в текущем коде формата количество дней представлено одной буквой «d».
    Одиночная буква «d» пропускает начальные нули, когда количество дней выражается однозначной цифрой.
    Шаг 5: Результаты, полученные после применения разных форматов даты в одном и том же столбце (столбец C), показаны на следующем рисунке.
    Объяснение выходных данных: Обратите внимание, что на предыдущем изображении дата ячейки C2 имеет формат «д ммм, гггг», а дата ячейки C6 — «дд ммм, гггг». Единственная разница между этими двумя форматами даты заключается в количестве дней. Начальный нуль отсутствует в ячейке C2 и присутствует в ячейке C6.
    Следовательно, с помощью функции ТЕКСТ в Excel можно использовать разные форматы даты в разных ячейках одного и того же столбца. Помните, что код формата изменяет только внешний вид значения; это не изменяет само значение. Можно создать код формата в зависимости от требования.
    Пример 2. Объединение значений времени и даты в новом формате
    В следующей таблице время и даты показаны в двух отдельных столбцах. Рассматривайте эти столбцы как столбцы A и B Excel. Мы хотим выполнить следующие задачи:
    - Соединить (объединить) каждое значение времени с соответствующей датой. Используйте оператор амперсанда (&) и убедитесь, что между двумя значениями нет пробела.
    - Покажите, как скопировать форматы «ч:мм:сс am/pm» и «m/d/yyyy» из окна «Формат ячеек». Преобразовывайте каждый раз в прежний и дату в последний формат.
    - Соедините преобразованные значения времени и даты с помощью оператора амперсанда (&). Убедитесь, что между двумя значениями есть пробел.
    - Показать вывод, если формат даты не заключен в двойные кавычки.
    Объясните результаты, полученные в первом и третьем пунктах списка. Используйте функцию ТЕКСТ Excel для данных задач.
    Time Date
    7:00:00 AM 6/19/2018
    7:15:00 AM 6/19/2018
    7 :30:00 19.06.2018
    7:45:00 19.06.2018
    8:00/00:002/2018
    8:15:00 AM 6/19/2018
    8:30:00 AM 6/19/2018
    8:45:00 AM 6/ 19/2018
    Шаги для выполнения данных задач с помощью функции ТЕКСТ Excel перечислены ниже:
    Шаг 1: Введите следующую формулу (без начальных и конечных двойных кавычек) в ячейку С2.
    «=A2&»»&B2»
    Эта формула показана на изображении шага 2. Она объединяет значения ячеек A2 и B2 без пробела между ними.
    Шаг 2: Нажмите клавишу «Ввод». Затем перетащите формулу из ячейки C2 в ячейку C9, используя маркер заполнения. Маркер заполнения отображается в нижней правой части ячейки C2.
    Выходы показаны на следующем рисунке.
    Объяснение выходов: В столбце C число 43270 (в конце) одинаково во всем диапазоне C2:C9. Это порядковый номер даты 19 июня 2018 г. Все десятичное число, предшествующее этому порядковому номеру, является временем. Итак, десятичное число 0,2916667 (в ячейке C2) представляет время 7:00:00.
    Вывод, полученный в столбце C предыдущего изображения, не читается. Причина в том, что Excel преобразовал время (столбца A) в десятичные числа, а даты (столбца B) — в последовательные числа. Кроме того, объединение десятичных дробей с последовательными числами усложнило вывод.
    Чтобы можно было прочитать вывод, его необходимо преобразовать в соответствующие коды формата. Это преобразование показано далее в этом примере.
    Примечание: Excel хранит даты как порядковые (или серийные) числа, а время — как десятичные числа. Excel рассматривает значение времени как часть суток. Чтобы просмотреть число, представляющее дату, выполните следующие действия:
    - Выберите любую ячейку диапазона B2:B9.
    - Нажмите одновременно клавиши «Ctrl+1». «Формат ячеекФормат ячеек в ExcelФорматирование ячеек — важный метод, который необходимо освоить, поскольку он делает любые данные презентабельными, четкими и в предпочтительном для пользователя формате. Форматирование ячейки зависит от характера представленных данных. Откроется окно «Подробнее».
    - Выберите «общие» в разделе «категория» на вкладке «номер».
    Серийный номер можно увидеть в разделе «образец». Нажмите «Отмена», чтобы закрыть окно «Форматировать ячейки», или «ОК», чтобы изменить дату на серийный номер.
    Точно так же десятичное число, представляющее время, также можно увидеть, выбрав любую ячейку в диапазоне A2:A9 и выполнив действия «b» и «c», перечисленные выше.
    Шаг 3: Чтобы преобразовать значения времени и даты в читаемый формат, давайте сначала скопируем коды формата из окна «Формат ячеек». Итак, выделите ячейку C2 и нажмите одновременно клавиши «Ctrl+1».
    Откроется окно «Формат ячеек», как показано на следующем рисунке. На вкладке «Номер» выберите «Пользовательский» в разделе «Категория». Excel предоставляет список форматов в разделе «тип».
    Шаг 4: Прокрутите список форматов, указанный в разделе «Тип». Скопируйте формат «ч:мм:сс am/pm». Для копирования достаточно выбрать код формата и нажать клавиши «Ctrl+C».
    Код формата показан на следующем рисунке. После того, как формат был скопирован, закройте окно «Формат ячеек».
    Шаг 5: Скопируйте код формата «m/d/yyyy» для преобразования значений даты. Этот код формата также доступен в разделе «тип», как показано на следующем рисунке.
    Закройте окно «Формат ячеек» после копирования указанного кода формата.
    Шаг 6: Чтобы применить новые форматы к значениям времени и даты и соединить полученные значения, введите следующую формулу в ячейку C2.
    «=ТЕКСТ(A2″,ч:ММ:СС AM/PM»)&» «&ТЕКСТ(B2″,м/д/гггг»)»
    Эта формула показана на изображении шага 7. Если коды форматов были скопированы на предыдущих шагах, их можно вставить в соответствующие места формулы, нажав сочетание клавиш «Ctrl+V».
    В соответствии с этой формулой значения времени и даты в ячейках A2 и B2 форматируются в соответствии с кодами «ч:ММ:СС AM/PM» и «м/д/гггг» соответственно. Отформатированные (или преобразованные) значения затем объединяются с помощью оператора амперсанда.
    Обратите внимание, что в формуле также был введен пробел в паре двойных кавычек (например, &“ ”&). Этот пробел будет вставлен в вывод именно в том месте, где он был введен в формуле.
    Шаг 7: Нажмите клавишу «Ввод» после ввода формулы ТЕКСТ. Перетащите формулу из ячейки C2 в ячейку C9.
    Выходы показаны на следующем рисунке.
    Объяснение выходных данных: Значения времени и даты в столбцах A и B были преобразованы в соответствующие форматы в столбце C. Значения времени отображают часы (ч) в виде одной цифры, минуты (мм) в виде двух цифрами, а секунды (сс) также двузначными. Поскольку все значения времени относятся к периоду до полудня, они отображают «am» в конце.
    Точно так же даты отображают месяцы (m) в виде одной цифры, дни (d) в виде одной цифры и годы (yyyy) в виде четырех цифр.
    Обратите внимание, что между двумя объединенными значениями в столбце C есть пробел. Несмотря на то, что мы скопировали код «h:mm:ss am/pm» (на шаге 4) и вставили «h:MM:SS AM/PM» (на шаге 6) мы получили правильные результаты в столбце C (на шаге 7). Это связано с тем, что коды формата не чувствительны к регистру, что означает, что «мм» обрабатывается так же, как «ММ».
    Шаг 8: Чтобы увидеть, что происходит, когда код формата даты вводится без двойных кавычек, введите следующую формулу в ячейку C9.
    «=ТЕКСТ(A2»,ч:ММ:СС AM/PM»)&» «&ТЕКСТ(B2,м/д/ГГГГ)»
    Нажмите клавишу «Ввод». Excel возвращает «#ИМЯ?» ошибка, как показано на следующем рисунке. Поэтому для работы функции excel ТЕКСТ код формата обязательно должен быть заключен в двойные кавычки.
    Имеется два изображения с названиями «изображение 1» и «изображение 2». Дана следующая информация:
    - На изображении 1 показано число, состоящее из одиннадцати девяток в ячейке B2. Когда к этому числу добавляется 1, его цифры увеличиваются до 12. Excel отображает полученное 12-значное число (в ячейке B3) в экспоненциальном представлении.
    - На изображении 2 показаны имена некоторых людей (в столбце A) и их случайные мобильные номера в экспоненциальном представлении (в столбце B). В строке формул отображается номер мобильного телефона человека А. Каждый номер мобильного телефона состоит из двенадцати цифр, включая двухзначный код страны в начале.
    Обратите внимание, что научная записьНаучная запись В Excel В Excel научная запись — это особый стиль записи чисел в научной и экспоненциальной формах. Научное представление компактно помогает отображать значения, позволяя нам сравнивать и использовать одно и то же в вычислениях. Подробнее (или научный формат) часто отображает очень большие или очень маленькие числа в сокращенной форме. Мы хотим выполнить следующие задачи:
    - Показать весь 12-значный номер мобильного телефона без пробелов между ними.
    - Отделите код страны от остальной части номера с помощью дефиса.
    Используйте функцию ТЕКСТ Excel для заданных задач.
    Изображение 1
    Изображение 2
    Шаги для выполнения данных задач с помощью функции ТЕКСТ Excel перечислены ниже:
    Шаг 1: Введите следующую формулу в ячейку D.
    «=ТЕКСТ(B2″,############»)»
    Эта формула показана в строке формул следующего изображения. Обратите внимание, что в формуле есть 12 хэшей, которые представляют 12 цифр номера мобильного телефона.
    Шаг 2: Нажмите клавишу «Ввод». Результат появится в ячейке D2. Чтобы получить выходные данные для всего столбца D, перетащите формулу из ячейки D2 в ячейку D7. Используйте маркер заполнения ячейки D2 (отображается в правом нижнем углу) для перетаскивания.
    Выходные данные столбца D показаны на следующем изображении.
    Примечание: Результат отображается (в столбце D) только тогда, когда в качестве входных данных вводится номер мобильного телефона (в столбце B), который автоматически (или вручную) преобразуется в научный формат Excel. В случае, если научный формат набирается вручную в столбце B; его нельзя преобразовать в мобильный номер столбца D.
    Шаг 3: Чтобы отделить код страны от остальной части номера с помощью дефиса, введите следующую формулу в ячейку D2.
    «=ТЕКСТ(B2,«##-##########»)»
    Нажмите клавишу «Ввод». Затем перетащите формулу из ячейки D2 в ячейку D7 с помощью маркера заполнения. Формула ячейки D2 и выходные данные показаны на следующем изображении.
    Обратите внимание, что в формуле дефис ставится именно там, где это требуется в выводе. Поскольку должны быть разделены только первые две цифры каждого мобильного номера, дефис ставится после двух решеток формулы.
    Пример № 4. Префикс текстовой строки к первоначально отформатированному денежному значению
    Имеется два изображения с названиями «изображение 1» и «изображение 2». Дается следующая информация:
    - На рисунке 1 показаны валовая прибыль, расходы и чистая прибыль организации. Все эти цифры в долларах.
    - На рисунке 2 показано, как рассчитывается чистая прибыль. Для расчета чистой прибыли расходы вычитаются из валовой прибыли.
    Мы хотим выполнить следующие задачи:
    - Показать вывод, когда перед строкой «чистая прибыль» стоит сумма чистой прибыли. Используйте для этой цели оператор амперсанда.
    - Перед строкой «чистая прибыль» укажите сумму чистой прибыли, используя амперсанд. Убедитесь, что в выводе суммы чистой прибыли отображается знак доллара ($) и запятая в правильных местах (например, 52 291 доллар). Для этого используйте функцию ТЕКСТ Excel.
    Объясните вывод, полученный в конце.
    Изображение 1
    Изображение 2
    Шаги для выполнения данных задач перечислены следующим образом:
    Шаг 13 В ячейку A13: 900 Шаг 13.
    «=» Чистая прибыль равна «&B11»
    Исключить начальную и конечную двойные кавычки формулы при вводе ее в Excel. Эта формула показана на изображении шага 2.
    Данная формула добавляет префикс строки «чистая прибыль» к значению ячейки B11. Амперсанд помогает присоединить указанную строку к сумме чистой прибыли (в ячейке B11).
    Шаг 2: Нажмите клавишу «Ввод». Результат появится в ячейке A13, как показано на следующем рисунке.
    Обратите внимание, что в выходных данных опущены знак доллара и запятая в сумме чистой прибыли (показанной в ячейке B11). Хотя префикс строки «чистая прибыль составляет» указан правильно. Пробелы также были вставлены в нужных местах в ячейке A13.
    Шаг 3: Чтобы сохранить знак доллара и запятую в сумме, введите следующую формулу в ячейку A13.
    «=»Чистая прибыль» &TEXT(B11,»$#,##0.00″)»
    Нажмите клавишу «Enter». Формула и выходные данные показаны в ячейке A13 на следующем изображении.
    Обратите внимание, что по ошибке перед амперсандом в формуле вставлен пробел. Однако даже в этом случае в ячейке A13 был получен правильный вывод.
    Объяснение вывода: На этот раз сумма чистой прибыли была правильно записана (включая знак доллара и запятую) в ячейке A13. Причина в том, что сумма чистой прибыли была преобразована в соответствующий формат в дополнение к префиксу текстовой строки.
    Итак, с помощью функции ТЕКСТ Excel можно соединить число с любой строкой и в то же время сохранить исходное форматирование числа.
    Форматы даты в Excel
    Различные коды форматов для дат были описаны следующим образом:
    Часто задаваемые вопросы
    1. Определите функцию ТЕКСТ в Excel с помощью примера.
    Функция ТЕКСТ в Excel помогает преобразовать числовое значение в текстовую строку. Преобразование осуществляется в соответствии с форматом, указанным пользователем. После преобразования числовое значение становится текстовым представлением числа.
    
    Формула «= ТЕКСТ («12.02.2021», «дддд-мммм-гггг»)» преобразует дату 12.02.2021 в четверг-декабрь-2021. Убедитесь, что начальные и конечные двойные кавычки исключены при вводе этой формулы ТЕКСТ в Excel.
    Примечание: Для работы с функцией ТЕКСТ в Excel см. примеры в этой статье.
    2. Почему в Excel используется функция ТЕКСТ?
    Функция ТЕКСТ используется в Excel по следующим причинам:
    • Помогает преобразовать числовое значение в текстовую строку нового формата.
    • Упрощает соединение числового значения с текстовой строкой.
    • Помогает сохранить формат существующего числового значения после конкатенации (объединения).
    • Это делает значения даты или времени, представленные в виде последовательных или десятичных чисел, более читабельными и понятными для пользователей.
    • Помогает добавлять начальные нули к числу.
    • Позволяет вставлять в вывод определенные символы (такие как +,-,(),<>,: и т.д.). 9), косая черта (/), амперсанд (&), пробел ( ), тильда (~) и восклицательный знак (!).
    Символы должны быть введены в коде формата. Эти символы отображаются именно в тех местах (в выводе), где они были введены в коде формата.
    3. Как функция ТЕКСТ используется для добавления начальных нулей к числу в Excel?
    Шаги добавления ведущих нулей к числу перечислены ниже:
    a. Введите «=TEXT», а затем открывающую скобку.
    б. Введите число в качестве аргумента «значение».
    в. Введите столько нулей в коде формата, сколько цифр требуется для вывода.
    д.