Как вытащить число или часть текста из текстовой строки в Excel
Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.
Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.
И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.
Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.
Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.
Вся трудность, в том чтобы извлечь код из текстовой строки.
Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.
В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).
«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.
Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.
Сложнее если нужные нам символы находятся в середине текста.
Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.
Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.
Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»
Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.
Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:
Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.
Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
Искомый_текст – обязательный аргумент. Текст, который требуется найти.
Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.
Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:
Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.
В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.
Как посчитать сумму в Экселе в столбце и в определенных ячейках
В программе Excel для округления до целого числа применяется также функция «ОТБР». Она просто отбрасывает знаки после запятой. По сути, округления не происходит. Формула отсекает цифры до назначенного разряда.
Как выделить время из даты в Excel?
Извлечь время только из даты и времени с помощью формулы
= ВРЕМЯ (ЧАС (A1); МИНУТА (A1); СЕКУНДА (A1)) (A1 — первая ячейка списка, из которого вы хотите извлечь время), нажмите Enter и перетащите маркер заполнения, чтобы заполнить диапазон.
Как в Excel посчитать сумму в столбце: все способы
- Выделите ячейку или столбец с текстом, который вы хотите разделить.
- На вкладке Данные нажмите кнопку Текст по столбцам.
- В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.
- Выберите разделители для своих данных. .
- Нажмите кнопку Далее.
Примечание. Это формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter, чтобы завершить его. Как только вы сделаете это, Excel автоматически заключит формулу в , как на скриншоте ниже. Ни в коем случае не следует вводить фигурные скобки вручную, это не будет работать.
