Поиск нескольких закодированных значений в строке, а затем возвращение значения поиска для каждого найденного кода

Question

У меня есть 600 кодов товара, и каждый кодируется с атрибутами в общем коде товара. Например, у нас может быть простой элемент 600, затем 600BK (черный корпус) и 600BKR (черный корпус, деталь красного цвета) и 600BKR-YEL (черный корпус, деталь красного цвета, желтая основа).

Итак, у меня есть список кодов товаров:

600
600BK
600BKR
600BKR-YEL

и затем на отдельном рабочем листе, список поиска каждого кода и что он обозначает:

BK    Black Body
R     Red Detail
YEL   Yellow Base 

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

600
600BK         Black Body
600BKR        Black Body Red Detail
600BKR-YEL    Black Body Red Detail Yellow Base

Это возможно?

Answer 1

Короткий ответ: "Да", один из способов, как это довольно долго.

Поскольку в вашем вопросе не исключено много возможностей (например, простые элементы, закодированные длиннее трех цифр или буквенно-цифровые, более трех кодов для простого элемента, коды различаются по значению в зависимости от простого элемента и т.д.), Я бы посоветовал безопаснее начать с разбора коды. Это должно избежать осложнений, когда GR похож на серый корпус с красной деталью (часто это совместимая цветовая схема!) или зеленое тело.

Предполагая, что они были проанализированы (в трех столбцах B:D, начиная с Row2), после этого легко, с вашей таблицей поиска (на отдельной странице) произнесите именованные codes:

=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)

в вашем коде товара. Чтобы получить пробелы между значениями, я предположил, что все записи в правом столбце вашего массива кода заканчиваются пробелом (достаточно легко упорядочить, например, с копиями вроде = A2 & "" и т.д.).

Таким образом, сложная часть может состоять в том, чтобы проанализировать коды из кодов элементов, для которых я бы предложил добавить столбцы, как показано ниже (больше, если существует более трех кодов):

с формулами, как показано ниже:

ColumnB должен определить, с чего начать поиск кодов (если простые коды отличаются от трех числовых). ColumnsC:D для того, чтобы начать поиск следующего кода / длины следующего кода. Я бы согласился не элегантно, но относительно универсально. Убедитесь в правильности разбора слева, прежде чем работать вправо.

После успешного разбора я предлагаю Копировать / Вставить Специальные / Значения (чтобы избавиться от формул), а затем заменить пробел на точку (чтобы избежать ошибки при поиске формулы, не усложняя ее) *. Кроме того, предполагая, что ваш список кодов элементов находится в ColumnA, удалите ColumnsB:F, прежде чем применять формулу поиска, как описано выше (или отрегулируйте ссылки в соответствии с требованиями), и добавьте дополнительные поиски, если это необходимо.

* Надзор: убедитесь, что в ячейке в каждом столбце таблицы поиска добавлена полная остановка.

Добавьте столбцы для c5, c6 и c7, чтобы учесть (a) максимальную длину 7 с (b) сценарием «наихудшего случая» (т. Е. Все одиночные символы).

Answer 2

Я бы сделал что-то вроде следующего. Предполагая, что ваш список кодов элементов находится в столбце A Sheet1 и, скажем, строки 2-20, а ваш список поиска находится в столбцах A и B Sheet2, строки 2-10:

  =IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")

в ячейке B2, а затем скопированы для всех строк в Sheet1.

Несмотря на то, что поиск помещает в одну формулу, этот подход будет очень громоздким, если у вас много кодов. Я бы рекомендовал вставлять вспомогательные строки в Sheet1 для каждой позиции кода (например, в середине (A2,4,2)), а затем объединять их в одну строку.

Answer 3

Вот версия, которая использует функцию поиска (версия find без учета регистра). Установка заключается в следующем.

На листе 1 коды для поиска начинаются в столбце А Листа 1. Окончательный результат будет в столбце B. В столбцах C, D и т.д. Имеются уникальные коды, расположенные горизонтально в строке 1, т. Е. "BK" в C1, "R" в D2 и т.д. Практический способ добиться этого - просто скопировать список кодов в справочной таблице и вставить специальную транспонирование по горизонтали в ячейки C1, D1 и т.д.

Затем сначала в ячейку B2 введите следующую формулу:

  =IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")

Скопируйте эту формулу по строкам из столбца C во сколько столбцов кода, которые вы создали в строке 2.

Наконец, в ячейке C2 объедините все результаты для строки 2, т. Е. Формула

 =D2&" "&E2&" "&F2

и т. д. для всех столбцов с кодами в строке 1. Этот шаг утомителен, но его можно замкнуть с помощью следующей функции VBA, которая позволяет объединять все ячейки в диапазоне:

  Function Concat(useThis As Range, Optional delim As String) As String
     ' this function will concatenate a range of cells and return the result as a single string
     ' useful when you have a large range of cells that you need to concatenate
     ' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

  Dim retVal As String, dlm As String, cell As Range
  retVal = ""
  If delim = Null Then
     dlm = ""
  Else
     dlm = delim
  End If
  For Each cell In useThis
      If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
          retVal = retVal & CStr(cell.Value) & dlm
      End If
  Next
  If dlm <> "" Then
     retVal = Left(retVal, Len(retVal) - Len(dlm))
  End If
  Concat = retVal
  End Function

Вы должны вставить и скопировать эту функцию в модуль в Developer VBA. Использование простое - concat(C1:D1, ""), например.

Обратите внимание, что этот подход работает для всех 2-символьных кодов и всех 1-символьных кодов, если их нет в кодах из 2+ символов, то есть, если нет пар кодов, таких как "R" и "BR".