Мастер класс «Извлечение характеристик из строки»
Для сотрудников отдела маркетинга.
Постановка задачи.
Допустим, у нас имеется коммерческое предложение от поставщика, в котором имеются характеристики товаров, включенные в название товара. Мы хотим разложить эти характеристики по отдельным столбцам, с тем, чтобы иметь возможность их загрузить в свою базу данных.
Т. е. у нас есть файл следующего вида:
и нам нужно получить файл следующего вида:
Функции.
Добиться этого можно с применением следующих функций Эксель:
«Найти» - ищет позицию указанного символа в строке,
«ДлСтр» - возвращает кол-во символов в строке,
«ПравСимв» - возвращает указанное число символов с конца строки
«ЛевСимв» - возвращает указанное число символов с начала строки.
Разделители.
Смотрим – какие символы разделяют различные виды характеристик. В нашем примере разделителем является символ «/ » (косая черта и пробел). Кроме этого, первая характеристика отделена от наименования пробелом. Пробел не самый лучший в данном примере символ – разделитель, поскольку встречается и наименовании и причем неопределенное число раз (может встретиться и два раза и три и четыре и если ориентироваться на пробелы, то мы не сможем гарантированно разложить характеристики по нужным колонкам).
Решение.
Для начала, добавляем несколько столбцов, в которых будем выводить нужные данные. Нам понадобится примерно втрое больше столбцов, чем то количество колонок, которое нужно для характеристик (можно обойтись и меньшим количеством колонок, но формулы при этом будут слишком сложными).
1) Для начала, определим позицию первого надежного разделителя «/ ». Для этого в первой из добавленных ячеек пишем формулу «=найти("/ ";B3)» и нажимаем Enter.
При этом программа выдаст результат.
2) Извлечение строки с характеристиками.
2.1) Далее, извлекаем строку, содержащую только нужные нам характеристики. Для этого в следующем столбце пишем формулу «=ПравСимв(B3;ДлСтр(B3)-C3)». Эта формула возвращает часть строки наименования, содержащую текст с характеристиками. Поскольку берем текст с конца строки, то в качестве количества знаков мы указываем «Длина строки» минус «позиция разделителя».
Нажимаем «Enter» и видим результат.
2.2) Этот результат подошел бы нам, но он не включает в себя первую из характеристик (давление – 160Мбар). Поэтому придется несколько доработать наши формулы.
Для этого, возьмем несколько большую строку, так, чтобы в неё умещались все характеристики (при этом возьмем с небольшим запасом, так чтобы и характеристика влезла любой возможно в данном случае длины и лишних пробелов не попало).
В данном случае, возьмем дополнительные 10 символов, для чего в нашу формулу допишем «+10» (получится формула «=ПРАВСИМВ(B3;ДЛСТР(B3)-C3+10)»).
В результате получим строку, содержащую все характеристики и небольшой дополнительный текст.
2.3) Теперь из неё аналогичным же образом получим строку, не содержащую лишних символов.
Для этого в следующем столбце сначала определяем позицию разделителя (на этот раз пробела – « » ).
А затем, в следующем столбце получаем нужную нам строку. При этом используется формула аналогичная той, что использовалась выше («=ПРАВСИМВ(D3;ДЛСТР(D3)-E3)») . Главное при этом не забыть поменять ячейки, на которые ссылается формула, и убрать добавление лишних 10 символов «+10».
Задание 1.
Возьмите приложенный Эксель файл и выведите в нем в отдельный столбец текст, содержащий только данные по характеристикам товаров.
Покажите результат инструктору.
3) Раскладывание характеристик по столбцам.
3.1) Теперь можно приступить к извлечению характеристик и раскладыванию их по столбцам. Для этого, в новой колонке опять считаем, по уже известной нам формуле позицию следующего разделителя в строке характеристик.
3.2) В следующей колонке выводим первую из наших характеристик. Для этого, пишем в ней формулу «=ЛевСимв(F3;G3-1)». На этот раз нам нужны символы с начала строки, поэтому мы пользуемся функцией «ЛевСимв». «-1» добавляем, чтобы лишняя косая черта не попадала в результирующее значение
.
3.3) В следующей колонке выводим строку с оставшимися характеристиками. Для этого пишем в ней формулу аналогичную той, что была в пункте (2.3) «=ПРАВСИМВ(F3;ДЛСТР(F3)-G3)».
Задание 2.
В том же файле выведите в отдельную колонку первую характеристику товара. В соседнюю с ней колонку выведите текст, содержащий оставшиеся характеристики.
Покажите результат инструктору.
3.4) Формулы следующих ячеек можно просто скопировать с последних трёх ячеек. Для этого выделяем их хватаем за правый нижний угол и тянем вправо.
Протягивая дальше, мы получаем в разных колонках нужные нам характеристики.
Задание 3.
Выведите в отдельные колонки все оставшиеся характеристики товара.
Покажите результат инструктору.
4) Протягиваем по всем товарам. имеющим аналогичный набор характеристик.
Выделяем ячейки со всеми созданными нами формулами, хватаем за правй нижгтй угол и тянем вниз.
Таким образом, получаем характеристики, разделенные по отдельным колонкам.
Теперь скрываем лишние ячейки и оставляем только ячейки с характеристиками.
Подготовленные таким образом данные можно использовать для массовой загрузки характеристик в базу данных вместо ручного внесения этих характеристик.
Задание 4.
Выполните задания 1-3 для второго приложения. Покажите результат инструктору.
Задание 5.
После последней колонки выведите наименование товара, не содержащее характеристик. Для этого используйте функции «ЛевСимв», «ДлСтр» и данные из уже созданных колонок. Покажите результат инструктору.
Подготовлено Прохором Лейкиным. 2012.04.13
