Мастер-класс по сложным формулам в Excel: как функция ПСТР поможет извлекать любой текст из строк

Задание для бухгалтера, которое нужно выполнить, используя формулы в Excel

Представьте, руководитель поставил вам задачу. У вас есть список номеров заказов, но потерялись даты, когда этот заказ был сделан. При этом в сам принцип формирования номера заказа у уже заложена дата. Она идет после букв LR и состоит из восьми символов: сначала год, потом месяц, потом день. Вам нужно перенести эту дату из одной ячейки в другую в формате «дд.мм.гггг».

Если вы не знаете, как использовать формулы в Excel, то такая работа может занять у вас целый рабочий день. Сэкономить ваше время поможет инструкция по формулам от преподавателя школы Action Digital School Егора Лысянского. На уроке курса «Из бухгалтера в аналитика данных» он дал пошаговый алгоритм с подробным решение этой задачи.

Итак, эксперт предлагает два способа как извлечь любой текст из строк Excel с помощью пары-тройки действий.

Решение №1. Используйте функции «ЛЕВСИМВ» и «ПРАВСИМВ»

Функция «ПРАВСИМВ» в Excel копирует в заданную ячейку указанное количество символов справа от текстовой строки. Чтобы применить функцию, в ячейку нужно прописать формулу =ПРАВСИМВ (Аргумент 1; Аргумент 2). Здесь аргумент 1 — текстовая строка, содержащая извлекаемые символы; аргумент 2 — количество символов, возвращаемых функцией. Функция «ЛЕВСИМВ» — выполнит те же действия слева от строки. возвращает первый символ или несколько символов справа от текстовой строки.

Итоговая формула будет выглядеть так: =ЛЕВСИМВ(ПРАВСИМВ(B2;ДЛСТР(B2)-2);4)

Функции «ЛЕВСИМВ и ПРАВСИМВ» занимают гораздо меньше времени, чем перенос даты вручную, но все равно объем работы остается большой.

Пять профессий в IT-сфере, которые легко освоит бухгалтер

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

✅ Курс «Из бухгалтера в аналитика данных»

✅ Курс «1С программист»

✅ Курс «1С аналитик»

✅ Курс «Эксперт 1С»

✅ Курс «Тестировщик»

Решение №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