Текстовые функции
Текстовые функции для отбора и исправления знаков в ячейке





Как взять из ячейки первые или последние несколько знаков? А из середины текста? Как правильно использовать функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР и другие?

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Текстовые функции для отбора и исправления знаков в ячейке

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

Текстовые функции, чтобы отобрать несколько букв/цифр или слов из ячейки

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Отбор знаков

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

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

Отобрать заданное количество знаков с начала строки поможет функция ЛЕВСИМВ [LEFT]: указываем из какой ячейки (аргумент текст [text]) и сколько знаков (аргумент количество_знаков [Num_chars]) нужно отобрать и получаем =ЛЕВСИМВ(C4;2)

А знаки с конца строки отбираются при помощи аналогичной функции ПРАВСИМВ [RIGHT]: =ПРАВСИМВ(C4;4)

В ситуации со Складом три знака нужно отобрать опять слева, но начиная не с первого, а с третьего, поэтому используем функцию ПСТР [MID], в которой как раз и можно указать, с которого по счету знака начать отбор (аргумент начальная_позиция [Start_num]): =ПСТР(C4;3;3)

Немного дольше придется поколдовать над формулами, чтобы вытащить из текстовой строки серийный номер, ведь количество знаков неизвестно, но есть закономерности, при помощи которых количество знаков серийного номера можно получить расчетным путем. Только что из каждой ячейки мы отобрали по 9 знаков, все оставшиеся знаки и есть серийный номер, таки образом если от общего числа знаков отнять 9, то мы и получим нужное число. А все знаки в ячейке может посчитать функция ДЛСТР [LEN]. То есть количество знаков серийного номера будет равно (для первой детали): =ДЛСТР(C4)-9, осталось только подставить этот алгоритм в качестве одного из аргументов функции ПСТР [MID]– ведь в итоге именно она отберет рассчитанное при помощи ДЛСТР [LEN] количество знаков, начиная с 6-го: =ПСТР(C4;6;ДЛСТР(C4)-9)

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

 

Отбор слов

А если нужно отобрать целое слово? Тогда поступим следующим образом – в том же файле на листе Отбор слов вытащим в отдельную ячейку фамилию. Очевидно, что нужно отобрать некоторое количество знаков с начала текстовой строки, а потому в основе расчета будет лежать функция ЛЕВСИМВ [LEF]. Сколько этих знаков – мы не знаем, но знаем, что отбор нужно выполнить до первого пробела. В таком случае, если вычислить номер знака пробела и отнять 1, то как раз нужное число знаков фамилии и получится. Порядковый номер пробела поможет обнаружить функция НАЙТИ [FIND]: =НАЙТИ(" ";A2) – ищем пробел в ячейке A2

От результата функции НАЙТИ [FIND] нужно отнять единицу, так как отобрать нужно все знаки ДО пробела, не включая его. Далее подставляем этот алгоритм расчета в функцию ЛЕВСИМВ [LEF] получаем результат: =ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1)

Интересует, как отобрать все знаки до второго пробела? Тогда исследуйте вот эту формулу – в ней заполнен необязательный аргумент функции НАЙТИ [FIND] – Нач_позиция [Start_num] – если он опущен (как в предыдущем примере), то поиск выполняется с первого знака, а можно сказать, что начать поиск пробела нужно со знака, чей порядковый номер на единицу больше, чем порядковый номер первого пробела. Вот так это будет выглядеть: =ЛЕВСИМВ(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2)+1)-1)

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

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

Строки, столбцы, листы - перемещение по документу и управление количеством и положением его элементов
Операции со строками, столбцами и листами
В этом материале подробно описаны операции вставки, удаления, скрытия, отображения, копирования и перемещения строк, столбцов и листов в Excel.

Правила и закономерности, по которым работает Excel
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

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

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.