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

Функция ТЕКСТ преобразует значение в текст в заданном числовом формате.

Синтаксис

ТЕКСТ(значение;формат)

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

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

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

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

Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста. Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК, но в отличие от функции ПОИСК функция НАЙТИ учитывает регистр и не допускает использования подстановочных знаков.

Синтаксис:

НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)

ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)

Искомый_текст — это искомый текст.

Просматриваемый_текст — это текст, включающий искомый текст. Для функции ПОИСК можно использовать знаки шаблона: знак вопроса (?) и звездочка (*). Знак вопроса соответствует любому знаку; звездочка соответствует любой последовательности знаков. Если нужно найти один из этих знаков, то следует поставить перед ними знак тильда (~).

Нач_позиция — это позиция знака, с которой следует начинать поиск. Первый знак в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1.

Функции НАЙТИ и ПОИСК можно использовать, например, в качестве аргументов функций ЛЕВСИМВ и ПРАВСИМВ.

Пример:

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

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

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

Для обработки текста в ячейке А2 формула будет выглядеть так:

=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-НАЙТИ(" ";A2;1)-1)

Функция НАЙТИ использована здесь 4 раза для вычисления местоположения нужной подстроки. Первый пробел в ячейке А2 имеет порядковый номер НАЙТИ(" ";A2;1). Следовательно имя начинается с символа номер НАЙТИ(" ";A2;1)+1.

Второй пробел идет сразу за именем. Чтобы найти его, нужно искать пробел не с начала строки, а начиная с символа, следующего за первым пробелом. Получается конструкция НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1). Если из полученного числа вычесть место первого символа имени, мы получим длину имени.

Иногда могут оказаться полезными функции ЗАМЕНИТЬ и ПОДСТАВИТЬ.

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

Синтаксис:

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)

Текст — это либо текст, либо ссылка на ячейку, содержащую текст, в котором подставляются знаки.

Стар_текст — заменяемый текст.

Нов_текст — текст, на который заменяется стар_текст.

Номер_вхождения — определяет, какое вхождение текста стар_текст нужно заменить на нов_текст. Если номер_вхождения определен, то заменяется только это вхождение текста стар_текст. В противном случае, каждое вхождение текста стар_текст в текстовой строке заменяется на текст нов_текст.

Функция ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста.

Синтаксис:

ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)

Старый_текст — текст, в котором желательно заменить некоторые знаки.

Нач_поз — позиция знака в тексте старый_текст, начиная с которой знаки заменяются текстом нов_текст.

Число_знаков — число знаков в тексте старый_текст, которые заменяются текстом новый_текст.

Новый_текст — текст, который заменяет знаки в тексте старый_текст.

Пример:

Формула =ПОДСТАВИТЬ(A2;"(095)";"(495)") заменит старый телефонный код Москвы на новый.

Функция СЖПРОБЕЛЫ удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать избыточные пробелы. СЖПРОБЕЛЫ(текст)

Функция СЦЕПИТЬ()

Функция работает аналогично символу амперсанда (&) – сцепляет несколько значений в единую текстовую строку.

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

=СЦЕПИТЬ("До Нового года осталось ";ДАТА(2007;1;1)-СЕГОДНЯ(); " дней")

вернет строку "До Нового года осталось 36 дней".

Функции ЛЕВСИМВ(), ПРАВСИМВ() и ПСТР()

Функция ЛЕВСИМВ() возвращает указанное количество символов начиная с левого края строки. Формула

=ЛЕВСИМВ("кашалот";4)

Вернет строку "каша".

Аналогично, функция ПРАВСИМВ() возвращает заданное число символов с правого края строки.

=ПРАВСИМВ("кашалот";3)

дает значение "лот".

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

=ПСТР("башмаки";4;3)

вернет "мак".

Функция ДЛСТР()

Эта функция возвращает длину строки. Функцию обычно используют вместе с другими текстовыми функциями. Например, если у нас есть список основных средств, в котором в каждой строке содержатся инвентарные номера длиной 3 символа и наименования объектов различной длины. Тогда выделить наименование из записи, находящейся в ячейке А1, можно с помощью функции

=ПСТР(A1;4;ДЛСТР(A1)-3)

Дата добавления: 2014-10-17; просмотров: 562; Нарушение авторских прав

Мы поможем в написании ваших работ!