Разное

Эксель функции текстовые: Текстовые функции Excel в примерах

Текстовые функции Excel в примерах

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

СЦЕПИТЬ

Для объединения содержимого ячеек в Excel, наряду с оператором конкатенации, можно использовать текстовую функцию СЦЕПИТЬ. Она последовательно объединяет значения указанных ячеек в одной строке.

СТРОЧН

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

ПРОПИСН

Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр.

Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.

ПРОПНАЧ

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

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

ДЛСТР

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

ЛЕВСИМВ и ПРАВСИМВ

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.

ПСТР

Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.

СОВПАД

Функция СОВПАД

позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

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

СЖПРОБЕЛЫ

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

В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.

Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:

Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ.

ПОВТОР

Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.

НАЙТИ

Текстовая функция НАЙТИ находит вхождение одной строки в другую и возвращает положение первого символа искомой фразы относительно начала текста.

Данная функция чувствительна к регистру…

… и может начинать просмотр текста с указанной позиции. На рисунке ниже формула начинает просмотр с четвертого символа, т.е. c буквы “

r“. Но даже в этом случае положение символа считается относительно начала просматриваемого текста.

ПОИСК

Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.

ПОДСТАВИТЬ

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

Приведенная ниже формула заменяет все вхождения слова “Excel” на “Word”:

Заменяет только первое вхождение слова “Excel”:

Удаляет все пробелы из текстовой строки:

ЗАМЕНИТЬ

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

ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение “2013”. Применительно к нашему примеру, формула заменит “2010” на “2013”.

Заменяет первые пять символов текстовой строки, т.е. слово “Excel”, на “Word”.

Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!

Оцените качество статьи. Нам важно ваше мнение:

Текстовые функции в Excel — Информационные технологии

Регистр в Microsoft Office Excel — это высота букв, их расположение в ячейках табличного массива. В Экселе не предусмотрена специальная функция смены регистра символов. Однако его можно поменять…

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

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

Люди, которые активно работают в Excel, часто подготавливают отчеты для различных государственных структур, регулярно сталкиваются с ситуациями, когда весь текст из документа, написанный обычными буквами, необходимо заменить на…

В текстовом процессоре Эксель имеется множество операторов, позволяющих производить манипуляции с текстовой информацией. Функция ПРАВСИМВ реализует извлечение конкретного числового значения из заданной ячейки. В статье мы детально изучим…

Инструменты Microsoft Excel наиболее часто используются для работы с числами. Иногда необходимо, чтобы число, например денежная сумма, должно быть записано прописью. Это становится особенно важным при составлении финансовых…

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

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

В табличном процессоре существует специальная функция СЦЕПИТЬ, которая реализует объединение содержимого 2-х и более ячеек. Умение пользоваться этим оператором позволяет эффективно и быстро осуществлять работу над большими объёмами…

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

Этот нелегкий процесс можно автоматизировать с помощью слияния данных…

Функции в Excel. Как использовать?

Тут вы найдете подробные описания 75+ самых важных функций Excel. Для каждой функции есть живые примеры эффективного использования.

  • DATE (ДАТА) – выдает значение даты в в виде года, месяца, дня
  • DATEVALUE (ДАТАЗНАЧ) – конвертирует значение даты в текстовом формате в числовое значение
  • DAY (ДЕНЬ) – выдает значение дня из любой даты
  • HOUR (ЧАС) – выдает значение часа из любой даты
  • MINUTE (МИНУТЫ) – выдает значение минут из любой даты
  • NETWORKDAYS (ЧИСТРАБДНИ) – вычисляет количество рабочих дней между двумя датами
  • NOW (ТДАТА) – выдает текущую дату и время
  • SECOND (СЕКУНДЫ) – преобразует дату в секунды
  • TODAY (СЕГОДНЯ) – выдает текущую дату
  • WEEKDAY (ДЕНЬНЕД) – выдает порядковое число дня недели (от 1 до 7)
  • WORKDAY (РАБДЕНЬ) – рассчитывает отстающую от данной на заданное количество рабочих дней
  • DATEDIF (РАЗНДАТ) – если вы хотите подсчитать количество лет, месяцев или дней между двумя указанными датами. Хорошим примером может быть вычисление возраста.
  • AND (И) – может быть использована, если вы хотите проверить несколько условий
  • FALSE (ЛОЖЬ) – вставляет логическое значение ЛОЖЬ
  • IF (ЕСЛИ) – выполняет проверку условий
  • IFERROR (ЕСЛИОШИБКА) – лучшая формула для коррекции отображения вычислений с ошибками
  • NOT (НЕ) – меняет на противоположное логическое значение своего аргумента.
  • OR (ИЛИ) – проверяет несколько аргументов
  • TRUE (ИСТИНА) – добавляет логическое значение ИСТИНА
  • COLUMN (СТОЛБЕЦ) – определяет номер столбца конкретной ячейки
  • COLUMNS (ЧИСЛСТОЛБ) – определяет количество столбцов в определенном диапазоне данных
  • HLOOKUP (ГПР) – определяет значение в первой строке массива и выдает значение из ячейки в найденном столбце и указанной строке
  • INDEX (ИНДЕКС) – определяет значение ссылки или массива по индексу
  • INDIRECT (ДВССЫЛ) – преобразует ссылки написанные как текст, в действующие вычисления из этих ссылок
  • MATCH (ПОИСКПОЗ) – определяет значение в ссылке или массиве
  • OFFSET (СМЕЩ) – определяет смещение ссылки относительно заданной ячейки
  • ROW (СТРОКА) – определяет номер строки ячейки
  • ROWS (ЧСТРОК) определяет количество строк в заданном диапазоне данных
  • VLOOKUP (ВПР) – ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце

Статистические функции в Excel

  • RANK (РАНГ) – определяет ранг числа в списке чисел
  • AVERAGE (СРЗНАЧ) – определяет среднее значение между двумя аргументами
  • AVERAGEIF (СРЗНАЧЕСЛИ) – определяет среднее значение между двумя аргументами по заданному условию
  • AVERAGEIFS (СРЗНАЧЕСЛИМН) – определяет среднее значение между двумя аргументами по заданным условиям
  • COUNT (СЧЕТ) – определяет количество чисел в списке аргументов
  • COUNTA (СЧЕТЗ) – подсчитывает количество значений в списке аргументов
  • COUNTBLANK (СЧИТАТЬПУСТОТЫ) – определяет количество пустых ячеек в заданном диапазоне
  • COUNTIF (СЧЕТЕСЛИ) – определяет количество чисел в диапазоне по заданному критерию
  • COUNTIFS (СЧЕТЕСЛИМН) – определяет количество чисел в заданном диапазоне по заданным критериям
  • LARGE (НАИБОЛЬШИЙ) – находит порядковое (например, 3-е) наибольшее значение из множества данных
  • MAX (МАКС) – определяет максимальное значение из списка аргументов
  • MIN (МИН) – определяет минимальное значение из списка аргументов
  • SMALL (НАИМЕНЬШИЙ) – находит порядковое (например, 3-е) наименьшее значение из множества данных

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

  • CONCATENATE (СЦЕПИТЬ) – объединяет 2 и более буквы или строчки
  • FIND (НАЙТИ) – ищет совпадение одного текста с другим (с учетом регистра)
  • LEFT (ЛЕВИСМВ) – извлекает текст из левой части строки в заданном количестве символов
  • LEN (ДЛСТР) – определяет количество знаков в заданной строке
  • LOWER (СТРОЧН) – преобразует все буквы в текстовой строке в строчные
  • MID (ПСТР) – выдает определенное число знаков из строки текста, начиная с указанной позиции
  • PROPER (ПРОПНАЧ) – преобразует в каждом слове текста первую букву в прописную
  • REPLACE (ЗАМЕНИТЬ) – заменяет знаки в тексте
  • REPT (ПОВТОР) – повторяет текст заданное количество раз
  • RIGHT (ПРАВСИМВ) – извлекает текст из правой части строки в заданном количестве символов
  • SEARCH (ПОИСК) – ищет совпадение одного текста с другим (без учета регистра)
  • SUBSTITUTE (ПОДСТАВИТЬ) – заменяет в текстовой строке старый текст новым
  • TEXT (ТЕКСТ) – форматирует числов в текстовый формат
  • TRIM (СЖПРОБЕЛЫ) – удаляет из текста строки лишние пробелы
  • UPPER (ПРОПИСН) – преобразует все буквы текста в прописные

Рассказать друзьям

Текстовые функции (справка) — служба поддержки Office

Функция ASC

Изменяет полноширинные (двухбайтовые) английские буквы или катакану в строке символов на символы половинной ширины (однобайтовые)

БАТТЕКСТ функция

Преобразует число в текст с использованием денежного формата ß (бат)

Функция СИМВОЛ

Возвращает символ, указанный в кодовом номере

ЧИСТЫЙ функция

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

КОД (функция КОД)

Возвращает числовой код для первого символа в текстовой строке

СЦЕПИТЬ функция

Объединяет текст из нескольких диапазонов и / или строк, но не предоставляет аргументы разделителя или IgnoreEmpty.

СЦЕПИТЬ функция

Объединяет несколько текстовых элементов в один текст

Функция DBCS

Изменяет полушириные (однобайтовые) английские буквы или катакану в строке символов на полноширинные (двухбайтовые) символы

ДОЛЛАР функция

Преобразует число в текст с использованием денежного формата $ (доллар)

ТОЧНАЯ функция

Проверяет идентичность двух текстовых значений

НАЙТИ, НАЙТИ функции

Находит одно текстовое значение в другом (с учетом регистра)

ФИКСИРОВАННАЯ функция

Форматирует число как текст с фиксированным числом десятичных знаков

LEFT, LEFTB функции

Возвращает крайние левые символы из текстового значения

Функции LEN, LENB

Возвращает количество символов в текстовой строке

НИЖНИЙ функция

Преобразует текст в нижний регистр

MID, функции MIDB

Возвращает определенное количество символов из текстовой строки, начиная с указанной вами позиции

ЧИСЛО, функция

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

ФОНЕТИЧЕСКАЯ функция

Извлекает фонетические символы (фуригана) из текстовой строки

Функция PROPER

Делает первую букву в каждом слове текстового значения с заглавной

Функции REPLACE, REPLACEB

Заменяет символы в тексте

Функция ПОВТОР

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

ВПРАВО, ВПРАВОB функции

Возвращает крайние правые символы из текстового значения

ПОИСК, ПОИСКБ функции

Находит одно текстовое значение в другом (без учета регистра)

ЗАМЕНА функция

Заменяет старый текст новым текстом в текстовой строке

Функция T

Преобразует аргументы в текст

ТЕКСТ функция

Форматирует число и преобразует его в текст

Функция ОБЪЕДИНЕНИЕ

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

Функция ОБРЕЗАТЬ

Удаляет пробелы из текста

Функция УНИХАР

Возвращает символ Юникода, на который ссылается заданное числовое значение

Функция UNICODE

Возвращает число (кодовую точку), соответствующее первому символу текста.

Функция ВЕРХНИЙ

Преобразует текст в верхний регистр

ЗНАЧЕНИЕ (функция ЗНАЧЕНИЕ)

Преобразует текстовый аргумент в число

Формула Excel: разделение текста и чисел

Обзор

Формула выглядит сложной, но на самом деле механика довольно проста.

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

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

Оригинал Текст Номер
Яблоки 30 Яблоки 30
персики 24 персики 24
апельсины12 апельсина 12
персики 0 персики 0

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

 
 = МИН (НАЙТИ ({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789"))

Когда у вас есть позиция, чтобы извлечь только текст, используйте:

И, чтобы извлечь только число, используйте:

В первой формуле выше мы используем функцию НАЙТИ, чтобы определить начальную позицию числа. Для find_text мы используем константу массива {0,1,2,3,4,5,6,7,8,9}, это заставляет функцию FIND выполнять отдельный поиск для каждого значения в константе массива. Поскольку константа массива содержит 10 чисел, результатом будет массив с 10 значениями. Например, если исходный текст — «яблоки30», результирующий массив будет:

 
 {8,10,11,7,13,14,15,16,17,18}

Каждое число в этом массиве представляет позицию элемента в константе массива внутри исходного текста.

Затем функция MIN возвращает наименьшее значение в списке, которое соответствует положению первого числа , которое появляется в исходном тексте. По сути, функция НАЙТИ получает все числовые позиции, а MIN дает нам первую числовую позицию: обратите внимание, что 7 — это наименьшее значение в массиве, которое соответствует позиции числа 3 в исходном тексте.

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

Эта часть формулы объединяет все возможные числа 0–9 с исходным текстом в B5. К сожалению, FIND не возвращает ноль, если значение не найдено, поэтому это просто умный способ избежать ошибок, которые могут возникнуть, если число не найдено.

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

Если исходный текст не содержит чисел, будет возвращена «фиктивная» позиция, равная длине исходного текста + 1. С этой фиктивной позицией формула LEFT выше по-прежнему будет возвращать текст, а формула RIGHT вернет пустую строку («»).

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

На главную »Встроенные функции Excel» Текстовые функции Excel »Текстовая функция Excel

Связанные функции:

ДОЛЛАРНАЯ функция
ФИКСИРОВАННАЯ функция

Описание функции

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

Синтаксис функции:

ТЕКСТ (значение, формат_текст)

Аргументы функции:

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

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

Примеры текстовых функций

Пример 1

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

Формулы:

A B
1 Значение Форматированное значение
2
2 9048 A2, «мм / дд / гггг»)
3 42192 = ТЕКСТ (A3, «мм / дд / гггг»)
4 42192 = ТЕКСТ (A4, «ммм дд гггг «)
5 18:00 = ТЕКСТ (A5,» чч: мм «)
6 0. 75 = ТЕКСТ (A6, «чч: мм»)
7 36,363636 = ТЕКСТ (A7, «0,00»)
8 0,5555, AEXT (A7, «0,00») % «)
9 567,9 = ТЕКСТ (A9,» $ #, ## 0.00 «)

Результаты:

Все результаты, возвращаемые функцией Text, являются текстовыми значениями.


Пример 2

Одним из наиболее распространенных способов использования функции Excel Text является вставка дат в текстовые строки.

Без использования функции Text простое объединение даты возвращает базовое целочисленное значение даты.Это показано в примере ниже:

A B
Значение Форматированное значение
2 07.07.2015 07.07.2015
3 42192 07/07/2015
42192 07 июля 2015
5 18:00 18:00
6 0.75 18:00
7 36.363636 36,36
8 0,5555 55,6%
9 567,9 567,9

Формулы:

A B C
1 Имя DOB DOB
2 Джон СМИТ 03.03.1976 = A2 & «» & B2
3 = A2 & «» & ТЕКСТ (B2, «мм / дд / гггг «)

Результаты:

A B C
1 Имя
2 Джон СМИТ 03.03.1976 Джон СМИТ 27822
3 Джон СМИТ 03. 03.1976

Дополнительные сведения и примеры функции Excel Text см. На веб-сайте Microsoft Office.


Общая ошибка функции текста Excel

У некоторых пользователей возникают проблемы, когда функция текста Excel возвращает # ИМЯ? ошибка:

Общая ошибка

# ИМЯ? —

Это возвращается из функции Excel Text, если вы опустите кавычки вокруг аргумента format_text.

Например, формула

= ТЕКСТ (A2, мм / дд / гггг)

вернет #NAME? Ошибка .

Решение: добавьте кавычки вокруг определения форматирования.

Например, приведенный выше пример будет исправлен как:

= ТЕКСТ (A2, «мм / дд / гггг»)

Вернуться к текстовым функциям Excel Страница
Вернуться к списку всех встроенных функций Excel

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

Функция ТЕКСТ Excel (пример + видео)

Когда использовать функцию ТЕКСТ Excel

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

Что возвращает

Возвращает текст в указанном формате.

Синтаксис

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

Входные аргументы

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

Дополнительные примечания

  • Это полезная функция, когда необходимо отображать числа в формате или сочетать числа с текстом / символами.
    • Например, если у вас есть значение 2000 в ячейке A1. и вы хотите показать текст — Продажи составили 2000 долларов, вы можете использовать следующую формулу: = »Продажи были« & ТЕКСТ (A1, «$ #, ###»)
  • Числа преобразуются в текст, и, следовательно, не могут быть использованы в расчетах.
  • Если вам нужно использовать эти числа в формулах / вычислениях, используйте настраиваемое форматирование чисел.

Функция Excel TEXT — Живой пример

Функция Excel TEXT — Видеоурок

Связанные функции Excel:

Вам также могут понравиться следующие руководства по Excel:

.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *