Работа со строками excel

Автор: | 03.05.2022
Нет комментариев

Функции Excel для работы с элементами строк

Следующие функции находят и возвращают части текстовых строк или создают большие строки из маленьких: FIND, SEARCH, RIGHT, LEFT, AVERAGE, REPLACE, REPEAT, REPLACE, CONCATENATE).

Функции НАЙТИ и ПОИСК

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

= НАЙТИ (поиск_текст; поиск_текст; начальная_позиция)
= ПОИСК (поиск_текст; поиск_текст; начальная_позиция)

Lookup_text — это текстовая строка для поиска, а lookup_text — текст для поиска. Каждый из этих аргументов может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку. Необязательный аргумент start_num указывает позицию в тексте предварительного просмотра, с которой следует начать поиск. Аргумент start_num следует использовать, когда lookup_text содержит несколько вхождений искомого текста. Если этот аргумент опущен, Excel возвращает позицию первого вхождения.

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

Например, чтобы определить положение буквы «г» в строке «Гаражные ворота», нужно воспользоваться формулой:

Эта формула возвращает 5.

Если вы не знаете точную последовательность символов искомого текста, вы можете использовать функцию ПОИСК и включить в строку search_text подстановочные знаки: вопросительный знак (?) И звездочку (*). Знак вопроса соответствует случайно набранному символу, а звездочка заменяет любую последовательность символов в указанной позиции. Например, чтобы найти положение имен Анатолий, Алексей, Акакий в тексте, который находится в ячейке А1, нужно использовать формулу:

Функции ПРАВСИМВ и ЛЕВСИМВ

Функция RIGHT возвращает крайние правые символы строки аргумента, а функция LEFT возвращает первые (левые) символы. Синтаксис:

= ВПРАВО (текст; число_символов)
= ЛЕВЫЙ (текст; количество_каров)

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

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

Функция ПСТР

Функция MID возвращает указанное количество символов из текстовой строки, начиная с указанной позиции. Эта функция имеет следующий синтаксис:

= MID (текст, начальная_позиция, количество_ символов)

Текстовый аргумент — это текстовая строка, содержащая символы для извлечения, start_position — это позиция первого символа, который нужно извлечь из текста (относительно начала строки), а num_characters — это количество символов для извлечения.

Функции ЗАМЕНИТЬ и ПОДСТАВИТЬ

Эти две функции заменяют символы в тексте. Функция REPLACE заменяет часть текстовой строки другой текстовой строкой и имеет синтаксис:

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

Old_text — это текстовая строка, на которую нужно заменить символы. Следующие два аргумента указывают символы для замены (относительно начала строки). Аргумент new_text указывает текстовую строку для вставки.

Например, ячейка А2 содержит текст «Вася Иванов». Чтобы вставить такой же текст в ячейку A3, заменив имя, вам необходимо ввести в ячейку A3 следующую функцию:

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

= ЗАМЕНИТЬ (текст; старый_текст; новый_текст; номер_вхождения)

Entry_number не является обязательным. Указывает Excel заменить только указанное вхождение строки old_text.

Например, ячейка A1 содержит текст «Ноль меньше восьми». Вам нужно заменить слово «ноль» на «ноль».

Число 1 в этой формуле означает, что вам нужно изменить только первую букву «o» в строке ячейки A1. Если номер_вхождения опущен, Excel заменяет все вхождения old_text на new_text.

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

Функция REPEAT позволяет заполнить ячейку символьной строкой, которая повторяется указанное количество раз. Синтаксис:

= ПОВТОР (текст; повторы)

Аргументом текста является строка умноженных символов, заключенная в кавычки. Аргумент repeat_num указывает, сколько раз повторять текст. Если repetition_number равен 0, функция REPEAT оставляет ячейку пустой, а если это не целое число, эта функция удаляет десятичные разряды.

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

Функция CONCATENATE эквивалентна текстовому оператору & и используется для объединения строк. Синтаксис:

= СЦЕПИТЬ (текст1; текст2;.)

В одной функции можно использовать до 30 аргументов.

Например, ячейка A5 содержит текст «первая половина года», следующая формула возвращает текст «Всего за первую половину года»:

= ЦЕПЬ («Всего за»; A5)

В верхней части страницы

В верхней части страницы

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

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

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

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

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

Синтаксис: = LEFT (текст; [число_символов])

  • текст — строка или ссылка на ячейку, содержащую текст, из которого вы хотите вернуть подстроку;
  • num_chars — необязательный аргумент. Целое число, указывающее, сколько символов нужно вернуть из текста. По умолчанию 1.

Пример использования:

Формула: = LEFT («Произвольный текст»; 8) — возвращаемое значение «Свободно».

Функция ПРАВСИМВ

Эта функция похожа на функцию LEFT, за исключением того, что символы возвращаются с конца строки.

Подробнее: найти подстроку в строке в Excel

Пример использования:

Формула: = ПРАВО («свободный текст»; 5) — возвращаемое значение «текст».

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

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

Синтаксис: = DLSTR (текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся появлением первого символа подстроки искомого текста. Если текст не найден, возвращается ошибка «# ЗНАЧ!».

Синтаксис: = НАЙТИ (текст_поиска; текст_поиска; [начальная_позиция])

  • search_text — строка для поиска;
  • text_for_search — текст, в котором ищется первый аргумент;
  • start_position не является обязательным. Принимает целое число, указывающее начальный символ search_text. По умолчанию 1.

Пример использования:

Из отрывка из стихотворения великого поэта С.А. Есенина находим появление первого символа строки «птица». Поиск ведется с начала строки. Если в данном примере поиск производился по 40 символам, то функция вернет в результате ошибку, так как позиция совпадения не найдена.

Функция ЗАМЕНИТЬ

Эта функция заменяет часть строки в указанном количестве символов, начиная с указанного символа, новым текстом.

Синтаксис: REPLACE (old_text; start_num; num_characters; new_text)

  • old_text — строка или ссылка на ячейку, содержащую текст
  • start_position — порядковый номер символа слева направо, с которого вы хотите заменить;
  • number_of_characters — количество символов, начиная с start_num, которое необходимо заменить новым текстом;
  • new_text — это строка, которая заменяет часть старого текста, указанного в start_num и num_chars.

Пример использования:

Здесь, в строке, содержащейся в ячейке A1, слово «старый», которое начинается с 19-го символа и состоит из 6 символов, заменяется словом «новый».

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

  • Аргумент «start_position» заменяется функцией «НАЙТИ»;
  • Вместо аргумента number_of_chars поставьте функцию DLSTR».

В результате получаем формулу: = ЗАМЕНИТЬ (A1; НАЙТИ («старый»; A1); DLSTR («старый»); «новый»)

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

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

Эта функция заменяет вхождения указанной подстроки в тексте новым текстом, что похоже на функцию «ЗАМЕНИТЬ», но между ними есть фундаментальное различие. Если функция «ЗАМЕНИТЬ» вручную изменяет указанный текстовый символ за символом, функция «ЗАМЕНИТЬ» автоматически находит вхождения указанной строки и изменяет их.

Синтаксис: SUBSTITUTE (текст; старый_текст; новый_текст; [номер_входа])

  • текст — строка или ссылка на ячейку, содержащую текст;
  • old_text — это подстрока первого заменяемого аргумента;
  • new_text — строка для замены старого текста
  • entry_number — необязательный аргумент. Примите целое число, указывающее порядковый номер заменяемого вхождения old_text, все остальные вхождения не будут затронуты. Если вы оставите аргумент пустым, все вхождения будут заменены.

Пример использования:

Строка в ячейке A1 содержит текст, содержащий 2 подстроки типа «старый». Нам нужно заменить строку «новый» на первое вхождение. В результате часть текста «.. старое-старое…» заменяется на «.. новое-старое…».

Если бы последний аргумент был опущен, результатом была бы строка «строка, содержащая новый-новый текст».

Функция ПСТР

MID возвращает из указанной строки часть текста в указанном количестве символов, начиная с указанного символа.

Синтаксис: MID (текст; начальное_число; число_знаков)

  • текст — строка или ссылка на ячейку, содержащую текст;
  • start_position — порядковый номер символа, начиная с которого должна быть возвращена строка;
  • number_of_characters — натуральное целое число, указывающее количество возвращаемых символов, начиная с start_num.

Пример использования:

Из текста в ячейке A1 вам нужно вернуть последние 2 слова, общая длина которых составляет 12 символов. Первый символ возвращенного предложения имеет порядковый номер 12.

Аргумент num_chars может превышать допустимую длину возвращаемых символов. Те, если в рассматриваемом примере вместо number_of_characters = 12 было указано значение 15, то результат не изменился и функция также вернула строку «MID function».

Для удобства использования этой функции ее аргументы можно заменить функциями «НАЙТИ» и «DLSTR», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

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

Синтаксис: = TRIM (текст)

Пример использования:

= TRIMS («Текст с дополнительными пробелами между словами и по краям «)

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

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

Используя функцию СЦЕПИТЬ, вы можете объединить несколько строк вместе. Максимальное количество строк для объединения — 255.

Синтаксис: = CONCATENATE (текст1; [текст2]; …)

Функция должна содержать хотя бы один аргумент

Пример использования:

Функция вернет строку: «Word1 Word2».

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

Вместо этой функции можно использовать амперсанд «&». Он также объединяет строки. Например: «=» Word1 ″ & »« & «Word2″».

Работа со строками в Excel. Текстовые функции Excel

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

Функции категории «Текстовые»

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

  • BATTEXT (Value) — функция, преобразующая число в тип текста;
  • DLSTR (Value) — очень полезная вспомогательная функция при работе со строками. Возвращает длину строки, то есть количество символов, содержащихся в строке;
  • REPLACE (старый текст, начальная позиция, количество символов, новый текст) — заменяет указанное количество символов с определенной позиции в старом тексте новым;
  • ЗНАЧЕНИЕ (Текст) — преобразовать текст в число;
  • LEFT (String, Number of characters) — очень полезная функция, возвращает указанное количество символов, начиная с первого символа;
  • ПРАВО (Строка, Количество символов) — аналог функции ВЛЕВО, с той лишь разницей, что символы возвращаются с последнего символа строки;
  • НАЙТИ (текст для поиска, текст, в котором ищем, начальная позиция) — функция возвращает позицию, с которой начинается вставка искомого текста. Учитывается регистр символов. Если вам нужно не учитывать регистр, используйте функцию ПОИСК. Возвращается только позиция первого вхождения в строке!
  • REPLACE (текст, старый текст, новый текст, позиция) — интересная функция, на первый взгляд она выглядит как функция REPLACE, но функция REPLACE может заменить все вхождения в строке новой подстрокой, если аргумент «позиция» опущен»;
  • MID (текст, начальная позиция, количество символов) — функция аналогична LEFT, но может возвращать символы с указанной позиции:
  • СЦЕПИТЬ (Текст1, Текст 2…. Текст 30) — функция позволяет соединить до 30 строк. Также вы можете использовать символ «&», он будет выглядеть так: «=» Text1 »&« Text2 »&« Text3”»;

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

Пример 1
Учитывая кучу строк:

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

Мы извлекаем номера счетов в столбце B. Для этого находим так называемый символ или ключевое слово. В нашем примере вы можете видеть, что каждому номеру счета предшествует «Нет» а длина номера счета — 6 знаков. Мы используем функции FIND и PSTR. Запишем в ячейку B2 следующую формулу :

= MID (A2; НАЙТИ («N.»; A2) +1; 6)

Разберем формулу. Из строки A2 с позиции, следующей за найденным символом «No.», извлекаем 6 символов числа.

Теперь извлечем дату. Здесь все просто. Дата находится в конце строки и состоит из 8 символов. Формула для C2 следующая:

= ВПРАВО (A2.8)

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

= ЗНАЧЕНИЕ (ПРАВО (A2,8))

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

И последнее, для удобства дальнейшей фильтрации строк введем столбец месяца, который мы будем получать по дате. Чтобы создать месяц, нам нужно отбросить день и заменить его на «01». Формула для D2:

= VALUE (CONCATENATE («01», RIGHT (A2,6))) или = VALUE («01» & RIGHT (A2,6))

Установите формат ячейки на «ММММ ГГГГ». Результат:

Пример 2
В строке «Пример работы со строками в Excel» необходимо заменить все пробелы знаком «_», просто перед словом «Excel» добавить «MS».

Формула будет следующей:

= ЗАМЕНИТЬ (ЗАМЕНИТЬ (A1; ПОИСК («excel»; A1); 0; «MS»); «»; «_»)

Чтобы понять эту формулу, разбейте ее на три столбца. Начните с ПОИСКА, последний будет ЗАМЕНЕН.

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