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

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

Я бы сделал что-то вроде следующего. Предположим, что ваш список кодов товаров находится в столбце A Листа 1 и, скажем, в строках 2–20, а ваш список поиска находится в столбцах A и B Листа 2, в строках 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, а затем скопировал вниз для всех строк на Листе1.

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

Answer 2

Короткий ответ — «Да», один из способов — довольно длинный.

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

Предполагая, что они были проанализированы (в трех столбцах B:D, начиная со строки 2), далее все просто, с помощью вашей таблицы поиска (той, что на отдельном листе), скажем, с именем 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 3

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

На листе 1 коды, которые нужно найти, начинаются в столбце A листа 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».