У меня есть список сокращенных номеров товаров с соответствующими данными о ставках комиссионных. Когда я загружаю данные о продажах, они содержат полные номера товаров. Я хочу сопоставить полный номер элемента с правильной аббревиатурой (еще одна вкладка в рабочей книге). Правильная аббревиатура соответствует началу соответствующего номера позиции - длина аббревиатур варьируется от 3 до 5 символов.
Как я могу получить соответствующую аббревиатуру, указав полный номер элемента?
Вот подход, который обращается к различным длинам номеров позиций и сокращенных кодов, а также к возможности того, что сокращенные коды различных длин имеют общие начальные цифры или буквы.
Он использует три вспомогательных столбца, которые точно соответствуют значениям VLOOKUPs в сокращенном коде первых 3, 4 и 5 символов, соответственно, каждого элемента. Соответствие может быть найдено в любом, всех или (в случае неверных данных) ни в одном из этих поисков сокращенного кода.
Четвертый столбец расчета содержит формулы, которые выбирают среди результатов трех VLOOKUPs .
Правила выбора просты. Если в списке сокращенных кодов найдено одно совпадение, соответствующий код возвращается. Если найдено два или три совпадения, возвращается совпадение с наибольшим количеством символов. Если для элемента не найдено совпадений, формулы возвращают значение ошибки # N/A.
Выражения выбора в четвертом столбце вычислений являются формулами массива и должны вводиться с помощью комбинации клавиш Control - Shift - Enter (т. Е. Формула в первой строке должна вводиться таким образом, а затем может быть скопирована вниз до список номеров товаров).
Для приведенных ниже примеров формул список полных номеров позиций находится в ячейках A2:A10 листа 1. Список сокращенных кодов находится в ячейках A2:A10 листа 2. Вспомогательные формулы находятся в столбцах B-D листа 1, а формулы массива выбора - в столбце E этого листа. (Пример формулы массива должен быть полностью виден при перемещении полосы прокрутки вправо.)
Формулы
Cell B2: =VLOOKUP(LEFT($A2,3),Sheet2!$A$2:$A$10,1,0)
Cell C2: =VLOOKUP(LEFT($A2,4),Sheet2!$A$2:$A$10,1,0)
Cell D2: =VLOOKUP(LEFT($A2,5),Sheet2!$A$2:$A$10,1,0)
Cell E2: =IFERROR(INDEX($B2:$D2,1,MAX(NOT(ISERROR($B2:$D2))*TRANSPOSE(ROW($1:$3)))),#N/A)
Я кратко объясню, как работает четвертая формула.
NOT(ISERROR($B2:$D2)) возвращает трехэлементный массив значений TRUE/FALSE, с TRUE для вспомогательных столбцов (в определенной строке), которые имеют совпадение, и FALSE в противном случае. Например, {TRUE, TRUE, FALSE}.
TRANSPOSE(ROW($1:$3)) просто предоставляет массив {1, 2, 3}, каждое число обозначает один из вспомогательных столбцов.
Их продукт - NOT(ISERROR($B2:$D2))*TRANSPOSE(ROW($1:$3))) - создает массив со значениями 1, 2 или 3 для столбцов с совпадением и нулем в противном случае, как в {1, 2, 0}.
Функция MAX возвращает наибольшее число в этом массиве, которое соответствует вспомогательному столбцу, который возвратил совпадение с наибольшим количеством символов. В массиве {1, 2, 0} MAX возвращает 2 для совпадения в столбце C, 2-й из вспомогательных столбцов.
Затем INDEX выбирает сокращенный код в столбце C.
Наконец, функция IFERROR возвращает # N/A, если не найдено соответствующего сокращенного кода.
Лист1
Sheet2
Если за номером товара всегда следуют две цифры / символа, как в примере данных (057GK12, 057GK20 и 057GK31), то следующий номер даст вам номер товара:
=LEFT(A1,LEN(A1)-2)
Если вы пытаетесь найти ячейку с аббревиатурой, вы должны сделать что-то вроде этого:
=MATCH(LEFT(A1,LEN(A1)-2),Sheet2!B:B,0)
Если вы пытаетесь найти полное значение из аббревиатуры, вы должны сделать что-то вроде этого:
=MATCH(B1&"??",Sheet1!A:A,0)
Они дают только первый результат в столбце. При этом также предполагается, что номера позиций и сокращения форматируются как текст, а не как значения.