Repeater-zone.ru

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

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

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

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

  1. ЛЕВСИМВ;
  2. ПРАВСИМВ;
  3. ДЛСТР;
  4. НАЙТИ;
  5. ЗАМЕНИТЬ;
  6. ПОДСТАВИТЬ;
  7. ПСТР;
  8. СЖПРОБЕЛЫ;
  9. СЦЕПИТЬ.

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Список текстовых функций Excel

По сути, эта формула использует функцию ПСТР для извлечения символов, начиная с предпоследнего пробела. Функция ПСТР принимает 3 аргумента: текст, с которым нужно работать, начальную позицию и количество извлекаемых символов.
Текст берется из столбца B, и количество символов может быть любым большим числом, которое обеспечит извлечение последних двух слов. Задача состоит в том, чтобы определить начальную позицию, которая находится сразу после предпоследнего пробела. Умная работа выполняется в первую очередь с функцией ПОДСТАВИТЬ, у которой есть необязательный аргумент, называемый номером экземпляра. Эта функция используется для замены предпоследнего пробела в тексте символом «@», который затем находится с функцией НАЙТИ.
В приведенном ниже фрагменте кода показано, сколько пробелов в общем тексте, из которого вычитается 1.

В показанном примере в тексте 5 пробелов, поэтому приведенный выше код возвращает 4. Это число передается во внешнюю функцию ПОДСТАВИТЬ как номер экземпляра:

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

Результат функции НАЙТИ — 14, к которому добавляется 1, чтобы получить 15. Это начальная позиция, которая переходит в функцию ПСТР в качестве второго аргумента. Для простоты количество извлекаемых символов жестко задано как 100. Это число произвольно и может быть изменено в зависимости от ситуации.

Извлечь из ячейки последние N слов

Эту формулу можно обобщить для извлечения из ячейки последних N слов, заменив жестко запрограммированную 1 в примере на (N-1). Кроме того, если вы извлекаете много слов, вы можете заменить жестко запрограммированный аргумент в ПСТР, 100, на большее число. Чтобы гарантировать, что число достаточно велико, вы можете просто использовать функцию ДЛСТР следующим образом:

Читать еще:  Программы для самостоятельного изучения английского языка с нуля

Как посчитать возраст по дате рождения в Excel?

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

Пример 3.

Для возврата строки с фамилией и текущим возрастом используем следующую формулу:

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

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

А теперь давайте начнем обзор практических примеров, как возможно применение функции ПСТР .

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

Сама таблица выглядит следующим образом.

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

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

После этого получаем такой результат.

Функция ПСТР для разделения текста на части в Excel - пример использования

2

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

Здесь мы снова видим, что использовали функцию НАЙТИ , с помощью какой в этом примере ищем первоначальную позицию, содержащую пробел. Также мы добавили единицу к содержимому аргумента, чтобы перенести взор программы на первый символ марки товара. Чтобы упростить задачу поиска последнего символа мы просто решили написать число 100, которое гарантированно превышает длину строки.

Так можете делать и вы.

После того, как программа выполнит все необходимые расчеты, получаем следующую таблицу.

Функция ПСТР для разделения текста на части в Excel - пример использования

3

Синтаксис ПСТР в Excel

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

Читать еще:  Скачать драйвера для AMD Radeon HD 6470M

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

  • Текст
    Текстовая строка, часть которой требуется получить. Это может быть ссылка на ячейку (чаще всего), текстовая константа (имеет смысл только для обучения) или результат работы другой функции.
  • Начальная позиция
    Считается слева и указывает функции ПСТР, откуда начинается нужный нам фрагмент строки (подстрока). Нумерация начинается с 1, а не с нуля!
  • Число знаков
    Сколько символом нужно получить. Минимум 1. Если указать 0, то на выходе получим пустую строку (ничего).

Сама формула в обобщённом виде выглядит следующим образом:
ПСТР(текст; начальная_позиция; число_знаков)

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

На выходе вы получим подстроку или ошибку вида «#ЗНАЧ!», если какие-то параметры были заданы неверно. Например, позиция начала подстроки может быть указана с нуля или число символов в подстроке отрицательное. Про обработку ошибок мы писали ранее.

Можно запомнить следующие особенности функции ПСТР:

  • Если значение «начальная_позиция» больше, чем длина текста, то функция ПСТР возвращает строку «» (пустую строку).
  • Если значение «начальная_позиция» меньше, чем длина текста, но сумма значений «начальная_позиция» и «число_знаков» превышают длину текста, функция ПСТР возвращает знаки вплоть до конца текста.
  • Если значение «начальная_позиция» меньше 1, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
  • Если значение «число_знаков» отрицательно, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
  • Если значение «число_байтов» отрицательно, то функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.

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

Читать еще:  Hamachi не удалось подключиться к сетевому адаптеру

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

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

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

  • текст № 1, текст № 2— тексты или ссылка на тексты которые будут сравниваться для получения результата.

Пример применения:

Tekstovie function part2 7 Текстовые функции в Excel. Часть №2

Поиск с конца строки в Excel

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

Вот этот набор функций, не очень то очевидно, согласитесь:

=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("Заданный текст";A1;1)-ДЛСТР("Заданный текст"))

Для удобства в надстройку VBA-Excel добавлена функция КОНЕЦСТРОКИ. Она выполняет поиск заданного текста с конца строки и возвращает текст от искомой позиции до конца строки. И интерфейс у нее намного понятнее:

Функция =КОНЕЦСТРОКИ(ТЕКСТ; НАЙТИ; [ Старт ]; [ Регистр ]) имеет 4 аргумента:

  • ТЕКСТ — Исходный текст, в котором необходимо найти начало строки.
  • НАЙТИ — Подстрока, до которой будет возвращаться начало строки
  • [ Старт ] — Необязательный параметр. Позиция внутри исходного текста, с которой начинается поиск подстроки. По умолчанию параметр равен 1.
  • [ Регистр ] — Необязательный аргумент, указывающий необходимость учета регистра. По умолчанию равен 1 — регистр учитывается. Укажите значение 0 если не хотите учитывать регистр при сравнении.

Пример 1

Найти текст до слова СТОП. Обратите внимание не важно в какой части текста находится заданное слово.

Найти текст с конца строки

Пример 2

Чтобы вернуть текст без первого слова укажите пробел в параметре НАЙТИ.

Вернуть текст без первого слова

Пример 3

Можно начинать поиск с заданной позиции (аргумент Старт ). Может понадобиться в случае если стоп-слово встречается не один раз.

Функция КОНЕЦСТРОКИ

Пример 4

В предыдущих примерах регистр искомого текста учитывался. Для того чтобы заглавные и строчные буквы НЕ учитывались при поиске, установите параметр Регистр = 0.

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