Функция текст в excel примеры

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

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

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

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

Формат не может содержать звездочку (*).

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

Клавиши для книг и листов

Предварительный просмотр и печать

CTRL+P или CTRL+SHIFT+F12 – Отображение диалогового окна Печать.
Используйте следующие клавиши в режиме предварительного просмотра (для перехода в режим предварительного просмотра нажмите клавиши ALT+F, а затем нажмите клавишу V).
Клавиши со стрелками – Перемещение по странице при просмотре в увеличенном масштабе.
PAGE UP или PAGE DOWN – Переход на одну страницу вперед или назад при просмотре в нормальном масштабе.
CTRL+СТРЕЛКА ВВЕРХ или CTRL+СТРЕЛКА ВЛЕВО – Переход к первой странице при просмотре в нормальном масштабе.
CTRL+СТРЕЛКА ВНИЗ или CTRL+СТРЕЛКА ВПРАВО – Переход к последней странице при просмотре в нормальном масштабе.

Работа с листами

SHIFT+F11 или ALT+SHIFT+F1 – Вставка нового листа.
CTRL+PAGE DOWN – Переход на следующий лист книги.
CTRL+PAGE UP – Переход на предыдущий лист книги.
SHIFT+CTRL+PAGE DOWN – Выделение текущего и следующего листов. Чтобы отменить выделение нескольких листов книги, нажмите клавиши CTRL+PAGE DOWN, чтобы выделить другой лист нажмите клавиши CTRL+PAGE UP.
SHIFT+CTRL+PAGE UP – Выделение текущего и предыдущего листов.
ALT+М Л И – Переименование активного листа (меню Формат, подменю Лист, команда переименовать).
ALT+П М – Перемещение или копирование активного листа (меню Правка, команда Переместить/скопировать лист).
ALT+П Л – Удаление активного листа (меню Правка, команда Удалить лист).

Перемещение и прокрутка внутри листа

Клавиши со стрелками – Переход на одну ячейку вверх, вниз, влево или вправо.
CTRL+клавиша со стрелкой – Переход к краю текущей области данных (Область данных. Диапазон ячеек, содержащих данные и окруженных пустыми ячейками или границами листа.).
HOME – Переход в начало строки.
CTRL+HOME – Переход в начало листа.
CTRL+END – Переход в последнюю ячейку на листе, расположенную в самой нижней используемой строке крайнего справа используемого столбца.
PAGE DOWN – Переход на один экран вниз.
PAGE UP – Переход на один экран вверх.
ALT+PAGE DOWN – Переход на один экран вправо.
ALT+PAGE UP – Переход на один экран влево.
F6 – Переход в следующую область в окне листа, разделенном на несколько областей (команда Разделить меню Окно).
SHIFT+F6 – Переход в предыдущую область в окне листа, разделенном на несколько областей.
CTRL+BACKSPACE – Прокрутка окна до текущей ячейки.
F5 – Отображение диалогового окна Переход.
SHIFT+F5 – Отображение диалогового окна Найти.
SHIFT+F4 – Повторение последнего действия Найти (аналогично нажатию кнопки Найти далее).
TAB – Переход между незащищенными ячейками на защищенном листе.

Перемещение в пределах выделенного диапазона

ENTER – Перемещение с верхней позиции в пределах выделенного диапазона в нижнюю позицию.
SHIFT+ENTER – Перемещение с нижней позиции в пределах выделенного диапазона в верхнюю позицию.
TAB – Перемещение с левой стороны выделенного диапазона к правой стороне. Если выделены ячейки в одном столбце — переместиться вниз.
SHIFT+TAB – Перемещение с правой стороны выделенного диапазона к левой стороне. Если выделены ячейки в одном столбце — переместиться вниз.
CTRL+ТОЧКА – Переход к следующему углу выделенного диапазона по часовой стрелке.
CTRL+ALT+СТРЕЛКА ВПРАВО – Переключение на следующее несмежное выделение справа.
CTRL+ALT+СТРЕЛКА ВЛЕВО – Переключение на следующее несмежное выделение слева.

Примечание. Можно изменить направление перемещения после нажатия клавиши ENTER или клавиш SHIFT+ENTER: нажмите клавиши ALT+Е, затем клавишу А (меню Сервис, команда Параметры), нажмите клавиши CTRL+TAB чтобы выбрать вкладку Правка, а затем измените настройки Переход к другой ячейке после ввода.

Перемещение и прокрутка в режиме «КНЦ»

КНЦ отображается в строке состояния, если режим включен.
Клавиша END – Включение или отключение режима КНЦ.
END+ клавиша со стрелкой – Перемещение на один блок данных внутри строки или столбца.
END+HOME – Переход в последнюю ячейку на листе, расположенную в самой нижней используемой строке крайнего справа используемого столбца.
END+ENTER – Переход к крайней правой ячейке в текущей строке. Эта комбинация клавиш не работает при включенных клавишах перемещения (меню Сервис, команда Параметры, вкладка Переход).

Перемещение и прокрутка при включенном режиме SCROLL LOCK

При использовании клавиш прокрутки (например, PAGE UP и PAGE DOWN) выделение перемещается на расстояние, эквивалентное прокрутке. Для прокрутки без перемещения выделения включите режим SCROLL LOCK.
SCROLL LOCK – Включение или выключение режима SCROLL LOCK.
HOME – Переход к ячейке в левом верхнем углу окна.
END – Переход к ячейке в правом нижнем углу окна.
СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ – Прокрутка окна на одну строку вверх или вниз.
СТРЕЛКА ВЛЕВО или СТРЕЛКА ВПРАВО – Прокрутка окно на один столбец влево или вправо.

Источник: www.myxcel.ru

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

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

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

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

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

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

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

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

Формула: =ЛЕВСИМВ(“Произвольный текст”;8) – возвращенное значение «Произвол».

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

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

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

Формула: =ПРАВСИМВ(“произвольный текст”;5) – возвращенное значение «текст».

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

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

Синтаксис: =ДЛСТР(текст)

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Функция счетесли в excel примеры

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

  • Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
  • В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(“старый”;A1);ДЛСТР(“старый”);”новый”)

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

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

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

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

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

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

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

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

Функция ПСТР

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

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

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

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

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

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

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

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

Синтаксис: =СЖПРОБЕЛЫ(текст)

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

=СЖПРОБЕЛЫ( ” Текст с лишними пробелами между словами и по краям “ )

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

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

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

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

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

Функция возвратит строку: «Слово1 Слово2».

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

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

Источник: office-menu.ru

Текстовые функции в Excel. Часть №3

Добрый день уважаемый читатель!

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

А теперь рассмотрим следующие 7 функций работы с текстовыми значениями, они будут последними в списке, но не последние по функционалу:

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

Функция Т

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

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

  • значение – ссылка на значение которое необходимо проверить.

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

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

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

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

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

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

Функция РУБЛЬ

Рассматриваемая функция умеет превращать число в текстовое значение и прикрепляет к нему аббревиатуру денежной единицы. Кстати название функции, а также обозначение валюты зависит от настроенных языковых параметров. Преобразованное число в текст имеет денежный формат: # ##0_р_.;-# ##0_р_. и округляется до указанного в формуле количества десятичных знаков.

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

= РУБЛЬ(_число_, _число_знаков_), где:

  • число – это ссылка на ячейку, которая содержит число, формула или же любое число, требующее преобразований;
  • число знаков – указывается сколько цифр после запятой вам нужно. Отрицательное значение делает округление слева от запятой, а положительное – справа. Если аргумент не указан, то значение по умолчанию 2 знака.

Пример применения: Стоить помнить, что форматирование ячейки с помощью функции РУБЛЬ, в отличие от стандартного форматирование через контекстное меню, превращает свой результат в текст, в отличие от других способов. Но, в принципе, использовать в формулах результат функции возможно, так как MS Excel имеет возможность превращать числа которые вносятся как текст, в процессе вычисления в числовые значения.

Функция ПОИСК

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

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

= ПОИСК(_искомый_текст_; _текст_для_начала_поиска_;[_начальная_позиция_]), где:

  • искомый текст – указывается текстовое значение которое необходимо отыскать. Можно использовать символы подстановки;
  • текст для начала поиска – указывается текст или ссылка на ячейку содержащую текстовое значение;
  • начальная позиция – является необязательным аргументом и при его отсутствии по умолчанию имеет значение 1. Указывает с какой позиции в тексте необходимо начать поиск.

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

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

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

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

Читайте также:  Как работать с функцией впр в excel для чайников

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

  • значение – указывается числовое значение или указывается ссылка на него;
  • формат – вводится формат который будет применен к аргументу «Значение».

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

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

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

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

= СЦЕПИТЬ(_текст№1_;_ [текст№2]_;….), где:

  • текст№1 – являет собой первый элемент который нужно сцепить с другими, обязательный аргумент;
  • текст№2 – не является обязательным аргументом. Предоставляет 255 значений, как элементы слияния.

Пример применения: Обращаю ваше внимание что функция не умеет ставить пробелы между аргументами, поэтому вы сделаете это самостоятельно. Так же при желании можно сцеплять текст с помощью знака амперсанда «&» не используя функцию. Более детально о возможностях вы прочтете в статье «Как используется функция СЦЕПИТЬ в Excel».

Функция ФИКСИРОВАННЫЙ

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

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

  • число – это ссылка на числовое значение или число, которое будет округлено и превращено в текст;
  • число знаков – указываем количество цифр после запятой;
  • без разделителей – этот аргумент является логическим значением и если он указан как ИСТИНА, то функция не будет включать разделители тысяч в текст который возвращается.

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

Ну вот и описаны все запланированные текстовые функции в Excel. Я надеюсь подборка в 21 функцию вам понравилась и стала полезной. Не забудьте также посмотреть часть 1 и часть 2 этой статьи.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Источник: topexcel.ru

Текстовые функции 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!

Источник: office-guru.ru

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

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

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

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

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

Читайте также:  Excel впр функция

Задача 1. Объединение текстовых строк

Есть список сотрудников. Фамилии, имена и отчества разнесены в отдельные столбцы. Необходимо объединить данные в один столбец.

Решение. Задача достаточно простая и для ее реализации воспользуемся функцией СЦЕПИТЬ.

В ячейку D1 запишем формулу =СЦЕПИТЬ(A1;” “;B1;” “;C1). Можно воспользоваться мастером функций.

Далее скопируем ее на весь необходимый диапазон столбца D.

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

Посмотрите на рисунок ниже. Результат преобразования в столбце D.

Окно мастера функции СЦЕПИТЬ

Задача 2. Разделение текстовых строк

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

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

Для отделения фамилии сотрудника и запишем в ячейку B1 формулу

=ЛЕВСИМВ(A1;НАЙТИ(” “;A1))

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

Для записи имени в ячейку C1 запишем следующую формулу

=ПСТР(A1;НАЙТИ(” “;A1)+1;ПОИСК(” “;A1;НАЙТИ(” “;A1)+1)-НАЙТИ(” “;A1)-1)

Если посмотреть на синтаксис записи данной функции, то получаем:

  1. Выражение НАЙТИ(” “;A1)+1 отвечает поиск позиции первого пробела в текстовой строке. А чтобы получить позицию первой буквы имени, прибавляется единица.
  2. Для определения количества символов в имени используется конструкция посложнее ПОИСК(” “;A1;НАЙТИ(” “;A1)+1)-НАЙТИ(” “;A1)-1. Количество символов определяется как разность позиций пробелов, отделяющих имя. Чтобы исключить из числа найденных символов сами пробелы, в начальной позиции прибавляется единица, а потом из полученного результата вычитается единица.

Отчество получается в ячейке D1 по более сложной формуле

=ПСТР(A1;ПОИСК(” “;A1;НАЙТИ(” “;A1)+1)+1;ДЛСТР(A1)-ПОИСК(” “;A1;НАЙТИ(” “;A1)+1)+1)

Здесь количество знаков в отчестве определяется как разность общего количества символов (ДЛСТР) и позицией второго пробела.

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

Задача 3. Укорачивание текстовых строк

В список сотрудников внести изменения. Записать в одном столбце Фамилии и инициалы.

Решение. В зависимости от исходного состояния списка возможны два варианта.

1 вариант. Исходные данные содержатся в одном столбце. ФИО разделены одинарным пробелом.

Записываем следующую формулу

=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(” “;A1));ПСТР(A1;НАЙТИ(” “;A1);2);”.”;ПСТР(A1;НАЙТИ(” “;A1; НАЙТИ(” “;A1)+1);2);”.”)

Преобразуем имя и отчество в инициалы (исходные данные в одном столбце)

2 вариант. Исходные данные содержатся в разных столбцах.

Формула для преобразования

=СЦЕПИТЬ(A1;” “;ЛЕВСИМВ(B1);”.”;ЛЕВСИМВ(C1);”.”)

Преобразуем имя и отчество в инициалы (исходные данные в разных столбцах)

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

Дорогой читатель! Вы посмотрели статью до конца. Получили вы ответ на свой вопрос? Напишите в комментариях пару слов. Если ответа не нашли, укажите что искали.

Источник: tvojkomp.ru

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

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

Примеры функции ТЕКСТ в Excel

Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).

Самая полезная возможность функции ТЕКСТ – форматирование числовых данных для объединения с текстовыми данными. Без использования функции Excel «не понимает», как показывать числа, и преобразует их в базовый формат.

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

Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:

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

Формула «для даты» теперь выглядит так:

Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.

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

Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «–»:

Обратите внимание, что значения теперь отображаются в числовом формате.

Функция разделения текста в Excel

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

  • ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
  • ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
  • ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

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

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

В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(” “;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

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

Вторая строка содержит имя, отчество и фамилию. Для имени используем такую же формулу:

Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(” “;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

Часть формулы – ПОИСК(” “;A3;ПОИСК(” “;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

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

Формула «для отчества» строится по тем же принципам:

Функция объединения текста в Excel

Для объединения значений из нескольких ячеек в одну строку используется оператор амперсанд (&) или функция СЦЕПИТЬ.

Например, значения расположены в разных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова – &. И так последовательно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

Использование функции СЦЕПИТЬ:

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

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:

Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?

Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.

Синтаксис функции ПОИСК:

  • «искомый текст» – что нужно найти;
  • «просматриваемый текст» – где искать;
  • «начальная позиция» – с какой позиции начинать искать (по умолчанию – 1).

Если нужно учитывать регистр, используется функция НАЙТИ.

Источник: exceltable.com