text
Открыть свежий номер журнала>> / Подписка со скидкой 40% >> ✆ 8 (800) 550-07-98
Журнал

Как быстро упорядочить перечень наименований готовой продукции с помощью Excel

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

Если формат исходных данных не удовлетворяет требованиям и существенно усложняет работу, их можно вернуть на доработку исполнителям или обработать самостоятельно. Чтобы не затрачивать много времени, можно воспользоваться специальными функциями Excel.

Как быстро разделить номенклатуру товаров по группам в Excel

Если нужно быстро разделить текстовые данные в Excel на несколько столбцов, можно воспользоваться встроенной функцией «Текст по столбцам». К примеру, если список сотрудников, предоставленный службой персонала, детализирован по имени и отчеству, а достаточно только аббревиатуры или данные подготовлены в виде текстового документа, непригодного для анализа.

Ассортимент компании «Альфа» представлен на рисунке 1. Для анализа рентабельности товаров необходимо разделить этот ассортимент по группам: черный и зеленый чай. А для этого сначала нужно выделить наименование товара из строки отчета в отдельный столбец (см. как показано стрелками на рисунке 1).

Сначала следует добавить в таблицу еще один столбец, справа от графы «Ассортимент» (столбец А). Затем выделить столбец А, полностью или ограничившись только диапазоном, требующим разделения (А4:А10), и перейти в главном меню во вкладку «Данные». В области «Работа с данными» нужно нажать на иконку «Текст по столбцам» (см. рисунок 2. Порядок разделения текста по столбцам).

В открывшемся диалоговом окне следует поставить галочку в пункте «с разделителями» поля «Укажите формат данных» (см. рисунок 3. Выбор формата данных при разделении списка товаров в Excel) и нажать «Далее».

На следующем шаге, показанном на рисунке 4, необходимо выбрать символ, с которого следует разделить данные. Это может быть знак табуляции, пунктуации – точка, запятая либо какой-то другой знак. Для заданного списка это «–». Иначе говоря, все, что указано в наименовании товара после дефиса, нужно отсечь и переместить в другой столбец.

Наконец, на последнем шаге нужно указать формат данных и диапазон ячеек, куда их следует поместить – это может быть новая книга, лист или ячейки в исходной таблице. Если информация будет отображаться в исходной ячейке, то Excel выведет дополнительный запрос на разрешение замены этих ячеек новыми данными. Важно, чтобы диапазон был не менее количества частей, на которые разобьется текст. В заданном списке требуется выделить диапазон ячеек А4:В10.

Результат этой операции представлен на рисунке 6.

Этот метод подходит, если есть четко означенный символ разделения данных – пробел или конкретный знак.

Как разделить текст из одной ячейки на несколько с помощью формулы Excel

Если из содержания ячейки Excel нужно выделить только часть информации, не ограниченной разделителями, стандартная функция «Текст по столбцам» не подойдет. Для этого следует воспользоваться специальной формулой. К примеру, ассортимент готовой продукции компании «Альфа» представлен с учетными кодами, однако при анализе рентабельности их необходимо исключить.

Сначала в таблицу нужно добавить еще один столбец справа от колонки «Ассортимент» (столбец А). На рисунке 8 это графа «Штрих код» (столбец В), в которой будут расчетные формулы. Если количество символов исходного текста, который нужно выделить в новый столбец, фиксированное, например, номер кода имеет одинаковое число знаков, то можно воспользоваться функциями:

В этих формулах «текст» – ссылка на ячейку данных, текст которой требуется исправить, а «число_знаков» – количество знаков, которые необходимо выделить.

На рисунке 8 показан результат для компании «Альфа». В ячейке В4 стоит формула ПРАВСИМВ(А4;5), где 5 – количество цифр в коде.

Однако в столбце А по-прежнему остаются данные, которые не подходят для анализа. Из них необходимо удалить кодировку. Добавляем еще один пустой столбец в таблицу и вводим в него формулу:
ЛЕВСИМВ(А4;ДЛСТР(А4)-6) – эта функция оставит в новом столбце только наименование чая без кода. Здесь:

Итог показан на рисунке 9.

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

Как разделить список с произвольным текстом по столбцам в Excel

Если в ячейке Excel содержится информация с произвольным числом символов, то, чтобы ее разделить на два или более столбцов, нужно написать формулу вручную. Для этого понадобится сразу несколько вложенных функций Excel.

К примеру, необходимо подготовить данные для отчета о продажах. В ячейке А на рисунке 10 стоит наименование готовой продукции и ее код согласно учетной системе. Число знаков в коде различается, из-за чего сложно задать правило на извлечение определенного количества символов. Но есть простое решение – увеличить число пробелов между словами (в т. ч. кодом) по максимуму. Буквально 100 или 999, главное, чтобы знаков пробелов было не меньше, чем символов в исходном тексте. Так будет проще задать новое правило и указать для программы любое число извлекаемых цифр кода.

Записав в ячейке В4 выражение: =ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A4;" ";ПОВТОР(" ";100));100*1);1;100);" ";""), получим искомый код из ячейки А4.

Подробнее рассмотрим эту формулу. Сначала используем функцию ПОДСТАВИТЬ(), чтобы увеличить число пробелов в исходном тексте и расширить расстояние между словами. В новом столбце в ячейке В4 необходимо записать:
=ПОДСТАВИТЬ(A4;" ";ПОВТОР(" ";100),
где:

Теперь, используя функции ПСТР() и ПРАВСИМВ(), извлекаем последнее слово из исходной строки, но с пробелами. Для этого в ячейке В4 формулу нужно изменить так:
=ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A4;" ";ПОВТОР(" ";100));100*1);1;100),
где:

Таким образом, эта функция извлекает последние 100 символов, включая пробелы, начиная с конца.

Остается убрать лишние пробелы, чтобы оставить только искомое выражение. Для этого необходимо снова обратиться к функции ПОДСТАВИТЬ() – в нее нужно заключить все вышеуказанное выражение в качестве исходного текста:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A4;" ";ПОВТОР(" ";100));100*1);1;100);" ";"").
Она устранит все пробелы и представит итог как на рисунке 13.

Если требуется извлечь второе с конца слово из ячейки, то в формуле вместо 100*1 нужно указать 100*2 – и т. д.

Иветта Новикова , финансовый менеджер ООО «ТЕТ-а-ТЕТ»

Быстро извлечь из текстовой информации числа поможет специальная надстройка для Excel «Финансист». В ней есть два варианта разделения данных – извлечение числа из текста или текста из числа, разделение одной графы на несколько. Однако важно иметь в виду:

Исходные данные об ассортименте компании «Альфа» представлены таблицей, где и наименование и код согласно учетной системе записаны единой строкой (см. рисунок 14. Исходные данные).

Из этой таблицы нужно извлечь коды готовой продукции в отдельную графу. Сначала необходимо добавить столбец слева от графы «Ассортимент» (см. рисунок 15. Разделение кодов готовой продукции). Его можно назвать «Код».

Теперь следует открыть в меню надстройки для Excel «Финансист» вкладку «Работа с текстом». Если из чисел в столбце В находятся только коды, то достаточно выбрать функцию «Извлечение числа из текста» (см. рисунок 16. Извлечение числа из текста).

В открывшемся диалоговом окне необходимо установить параметры извлечения, а именно:

Установив необходимые параметры, можно нажать кнопку «Извлечь». Все числа из столбца В переместятся в столбец А.

Второй способ разделения текста с помощью надстройки «Финансист» – нужно установить курсор на свободную ячейку B4 (см. рисунок 15. Разделение кодов готовой продукции), а затем во вкладке «Работа с текстом» выбрать опцию «Разбить текст». В новом окне, которое показано на рисунке 19, необходимо задать параметры разделения. В отличие от предыдущей функции здесь нельзя задать целый диапазон, то есть выбирать сразу несколько ячеек. К ячейке А4 привязываются данные из ячейки В4, поэтому в графе «Текст» поля «Аргументы функции» нужно сослаться только на нее. В поле «Разделитель» нужно выбрать тот символ, по которому будет отсчитываться позиция (слова), – в данном случае это пробел. В поле «Позиция» нужно ввести номер слова, которое требуется извлечь из строки. Поскольку количество символов в тексте различается, то, чтобы вывести последнее слово, нужно задать большой номер позиции, например, 10. Чтобы вывести первое – 0, второе – 1 и т. д.

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

Вы получите доступ к уникальному контенту, который закрыт для незарегистрированных пользователей!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.