Мастер-класс по сложным формулам в Excel: как функция ПСТР поможет извлекать любой текст из строк
У бухгалтера периодически возникает необходимость перенести информацию в Excel из одного столбца отчета или списка в другой. Но не полностью, а, например, только часть номера или только фамилию сотрудника. Эксперты школы Action Digital School на спецуроке рассказали, как это делать с помощью простой формулы. И вместо 8 часов, потратить на работу всего 5 минут!
Задание для бухгалтера, которое нужно выполнить, используя формулы в Excel
Представьте, руководитель поставил вам задачу. У вас есть список номеров заказов, но потерялись даты, когда этот заказ был сделан. При этом в сам принцип формирования номера заказа у уже заложена дата. Она идет после букв LR и состоит из восьми символов: сначала год, потом месяц, потом день. Вам нужно перенести эту дату из одной ячейки в другую в формате «дд.мм.гггг».
Если вы не знаете, как использовать формулы в Excel, то такая работа может занять у вас целый рабочий день. Сэкономить ваше время поможет инструкция по формулам от преподавателя школы Action Digital School Егора Лысянского. На уроке курса «Из бухгалтера в аналитика данных» он дал пошаговый алгоритм с подробным решение этой задачи.
Итак, эксперт предлагает два способа как извлечь любой текст из строк Excel с помощью пары-тройки действий.
Решение №1. Используйте функции «ЛЕВСИМВ» и «ПРАВСИМВ»
Функция «ПРАВСИМВ» в Excel копирует в заданную ячейку указанное количество символов справа от текстовой строки. Чтобы применить функцию, в ячейку нужно прописать формулу =ПРАВСИМВ (Аргумент 1; Аргумент 2). Здесь аргумент 1 — текстовая строка, содержащая извлекаемые символы; аргумент 2 — количество символов, возвращаемых функцией. Функция «ЛЕВСИМВ» — выполнит те же действия слева от строки. возвращает первый символ или несколько символов справа от текстовой строки.
Итоговая формула будет выглядеть так: =ЛЕВСИМВ(ПРАВСИМВ(B2;ДЛСТР(B2)-2);4)
Функции «ЛЕВСИМВ и ПРАВСИМВ» занимают гораздо меньше времени, чем перенос даты вручную, но все равно объем работы остается большой.
Пять профессий в IT-сфере, которые легко освоит бухгалтер
Эксперты школы Action Digital School подготовили специально для бухгалтеров спецкурсы, на которых можно получить новую востребованную профессию или современные цифровые навыки всего за несколько месяцев.
Решение №2. Используйте функцию «ПСТР»
Функция «ПСТР» помогает извлечь из указанного текста в ячейке нужное вам число печатных знаков, включая пробелы, начиная с указанного по счету слева символа. Функция выглядит следующим образом: =ПСТР (текст; начальная позиция; количество символов).
➤ Шаг 1. Перенести дату в новую ячейку
Итак, как вытащить дату заказа с помощью функции «ПСТР»? В качестве текста выступает номер заказа. С ним мы и будем работать.
Начальная позиция копируемых символов — третья, потому что в столбце «B» год в номере заказа начинается с третьего знака. А вытащить нужно 8 символов даты.
Получается формула: =ПСТР(B2;3;8),
где B2 — ячейка с номером заказа, 3 — начало отсчета копируемых символов, 8 — количество переносимых знаков.
Применив эту формулу, вы получаете такой результат:
➤ Шаг 2. Отформатировать дату заказа
Текст мы с вами извлекли, но его вид не похож на стандартный формат даты. Читать данные в таком виде будет неудобно. Чтобы привести дату в нужный формат, поделимся еще одним инструментом Excel — функция «Дата».
Чтобы отформатировать дату заказа с помощью функции «Дата» необходимо заполнить три аргумента:
Год. Год начинается с третьего символа и состоит из четырех символов. Записываем ПСТР(B2;3;4),
Месяц. Месяц начинается с седьмой позиции и состоит из двух символов — ПСТР(B2;7;2).
День. Начиная с девятой позиции начинается день. Он тоже состоит из двух символов — ПСТР(B2;9;2).
Задача выполнена, вы перенесли нужные даты в отдельный столбик и отформатировали их в виде «дд.мм.гггг». Такая работа даже у новичка занимает не больше 5-10 минут. Согласитесь, по сравнению с целым рабочим днем это серьезная экономия вашего времени.
Если вы и дальше хотите получать полезные лайфхаки от экспертов Action Digital School, приглашаем на курс «Из бухгалтера в аналитика данных». В его программу входят несколько разделов: глубокое изучение возможностей Excel, обработка данных с помощью языка запросов SQL, программирование на Python для решения сложных задач с данными. Пройдя курс, вы не только получите новую престижную профессию, но и освоите инструменты аналитика данных.
Научитесь анализировать большие данные. Освойте новую профессию на базе вашего опыта
Приходите на новый поток курса «Аналитик данных»
Реклама: ООО «Актион-диджитал», ИНН: 7715670503, erid: LjN8KMZy3
