Как вытащить число или часть текста из текстовой строки в Excel
30.11.2012 Григорий Цапко Полезные советы, Управленческий учет
Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.
Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.
Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.
И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом — программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.
Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. — не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).
Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.
Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.
Вся трудность, в том чтобы извлечь код из текстовой строки.
Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.
В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.
=ЛЕВСИМВ(текст; [количество_знаков])
=ПРАВСИМВ(текст; [количество_знаков])
Где:
Текст — обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).
«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.
Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.
Сложнее если нужные нам символы находятся в середине текста.
Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.
=ПСТР(текст; начальная_позиция; количество_знаков)
Где:
Текст — обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Начальная_позиция — обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.
Количество_знаков — обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.
Самый простой случай — если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ….»
Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.
В нашем случае формула будет иметь вид:
=ПСТР(С7;29;2)
Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:
Первый символ — первая буква в наименовании филиала, второй символ — это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.
Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])
Где:
Искомый_текст — обязательный аргумент. Текст, который требуется найти.
Просматриваемый_текст — обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.
Нач_позиция — необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:
=ПСТР(C7;ПОИСК(“?Ф00000”;C7);2)
Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.
В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.
Бесплатная версия Excel Как сделать размер шрифта в Excel очень маленьким или очень большим
