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

Таблицы Excel содержат, в основном, числовые данные, над которыми можно выполнять различные математические, логические и прочие операции. В то же время, немалая часть таблиц имеет в своем составе ячейки с текстом. Например, прайс-листы или списки клиентов с адресами непременно включают в себя текстовые данные. Для того, чтобы выполнять над этими данными различные преобразования, существуют текстовые функции Excel. Эти функции значительно облегчают процесс обработки текстовых данных. Важно не только знать о существовании этих функций и о том, как они работают, но и уметь применять эти знания на практике.
Текстовая функция ДЛСТР
Текстовые функции ЗАМЕНИТЬ и ПОДСТАВИТЬ
Текстовые функции ЛЕВСИМВ и ПРАВСИМВ
Текстовые функции НАЙТИ и ПОИСК
Текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ
Текстовая функция ПОВТОР
Текстовая функция ПСТР
Текстовая функция СЦЕПИТЬ

Текстовая функция ДЛСТР

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

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

Текстовые функции ЗАМЕНИТЬ и ПОДСТАВИТЬ

Функция ЗАМЕНИТЬ, судя по названию, меняет текст в ячейке на указанный текст. Например, нужно в столбце с паролями одинаковой длины указать только первые и последние знаки, а остальные пометить звездочками. Как это сделать?

  1. Установите курсор в ячейку, где будет отображаться результат замены символов. В главном меню Excel на вкладке «Формулы» нажмите на пункт «Текстовые» и выберите функцию «ЗАМЕНИТЬ».
  2. В окне аргументов функции укажите нужные параметры:
    • «Старый текст». Щелкните мышкой по ячейке, текст которой надо преобразовать, или укажите адрес ячейки вручную.
    • «Нач_поз». Укажите, начиная с какой позиции должна осуществляться замена текста.
    • «Число знаков». Укажите, сколько знаков должно быть заменено в тексте. Если поставить ноль, выбранный текст будет вставлен с указанной позиции.
    • «Новый_текст». Укажите текст для замены символов. Функция ЗАМЕНИТЬ
  3. Нажмите «ОК».
  4. Потяните за уголок первую ячейку, чтобы скопировать формулу для следующих ячеек. ЗАМЕНИТЬ Excel

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

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

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

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

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ

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

Рассмотрим пример. Имеется таблица, в одном из столбцов которой содержится цена вместе с кодом валюты. Необходимо коды валют вынести в отдельный столбец. Для этой цели можно использовать функцию ПРАВСИМВ.

  1. Установите курсор в первой ячейке столбца, в котором будут находиться коды валют. Во вкладке «Формулы» выберите пункт «Текстовые» и в появившемся списке функций выберите функцию ПРАВСИМВ.
  2. В открывшемся окне «Аргументы функции» укажите адрес первой ячейки столбца, из которого нужно извлечь код валюты, и укажите количество знаков, которые нужно скопировать, начиная с последнего знака строки. Функция ПРАВСИМВ
  3. Нажмите «ОК».
  4. Потяните за правый нижний угол первой ячейки столбца с валютами, чтобы скопировать формулу для остальных ячеек. Функция ЛЕВСИМВ

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

Текстовые функции НАЙТИ и ПОИСК

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

Рассмотрим пример. В строке «Компьютерный стол» нужно найти номер позиции, с которой начинается слово «стол». Для этой цели будем использовать функцию «НАЙТИ».

  1. Установите курсор в ячейке, где будет отображаться результат поиска. Во вкладке «Формулы» зайдите в раздел «Текстовые» и в появившемся списке функций выберите функцию НАЙТИ.
  2. Введите аргументы функции:
    • Искомый_текст – «стол».
    • Просматриваемый_текст – А1 (адрес ячейки с текстовой строкой).
    • Нач_позиция – можно не указывать. В этом случае поиск будет производиться, начиная с первого символа. Номер позиции нужно указывать, когда в тексте строки искомый текст встречается несколько раз.
  3. Нажмите «ОК».
  4. В результате этих действий в ячейке с результатом появится номер позиции, с которой начинается слово «стол». Функция НАЙТИ

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

Чем отличаются функции ПОИСК и НАЙТИ? Функция НАЙТИ, в отличие от функции ПОИСК, чувствительна к регистру. То есть, если в данном примере в качестве аргумента указать слово «Стол» с большой буквы, оно не будет найдено.

В то же время, функция ПОИСК, в отличие от функции НАЙТИ, позволяет использовать подстановочные знаки. К ним относятся, например, звездочка (*) и знак вопроса(?). Первый знак обозначает любой тест, а второй знак – любой символ. То есть, в данном примере для аргумента «с??л» в первой ячейке функция ПОИСК выдаст значение 14, в то время как функция НАЙТИ укажет, что значение не было найдено.

Текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ

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

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

Текстовая функция ПОВТОР

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

Рассмотрим пример, когда в таблице с данными аккаунтов пользователей нужно заменить пароли, заданные в явном виде, символом звездочка (*).

  1. Установите курсор в первую ячейку колонки, где будут находиться скрытые пароли. На вкладке «Формулы» в разделе «Тестовые» выберите функцию ПОВТОР.
  2. В окне «Аргументы функции» в поле «Текст» укажите символ звездочки (*). В поле «Число повторений» укажите формулу ДЛСТР(B1), которая будет подсчитывать количество знаков в каждом пароле и отображать столько звездочек, сколько символов в пароле. Функция ПОВТОР
  3. Нажмите кнопку «ОК» и скопируйте полученную формулу в нижние ячейки столбца. ПОВТОР в Excel

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

Текстовая функция ПСТР

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

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

  1. Установите курсор мыши в колонке, где будут размещаться результаты работы функции, и в списке текстовых функций выберите функцию ПСТР.
  2. В появившемся окне аргументов функции в поле «Текст» укажите адрес ячейки, из которой будет извлекаться текст. В поле «Начальная позиция» укажите позицию, с которой начинается извлекаемый текст (учитывая пробелы между словами). В поле «Количество знаков» укажите количество символов, которые нужно извлечь. Функция ПСТР
  3. После нажатия кнопки «ОК» нужный фрагмент текста будет помещен в указанную ячейку.

К сожалению, чаще всего количество извлекаемых знаков в ячейках столбца бывает неодинаковым, поэтому функцию ПСТР рекомендуется использовать совместно с функцией НАЙТИ и осуществлять поиск по какому-нибудь критерию, например, по наличию пробела между словами. Извлечь текст из строки Excel
В этом примере формула для первой ячейки столбца с результатами будет иметь вид: =ПСТР(A1;НАЙТИ(” “;A1)+1;255). В этой формуле «+1» означает, что позицию искомого текста тоже нужно учитывать, но возвращать ее не следует.

Текстовая функция СЦЕПИТЬ

При помощи функции СЦЕПИТЬ можно соединить несколько текстовых строк в одну строку. Соединяемых строк может быть очень много – до 255. В качестве аргументов функции можно использовать текст, цифры или адреса ячеек.

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

  1. Установите курсор мыши в ячейке, где будет находиться результат работы функции.
  2. Зайдите в раздел текстовых функций и выберите функцию СЦЕПИТЬ.
  3. В открывшемся окне аргументов функции поочередно укажите адреса соединяемых ячеек. Не забудьте о пробелах между словами. Пробелы указываются в кавычках. Функция СЦЕПИТЬ
  4. После нажатия кнопки «ОК» в ячейке с результатом работы функции появится текст, состоящий из имени, отчества и фамилии. Соединить строки Excel

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