3

Я автоматизирую столбец Excel, чтобы назначать значения учетной записи на основе оплаты поставщика. Например, если AT & T получит оплату, значение учетной записи будет « Utilities:Telephone , а при оплате почтового отделения значение учетной записи будет кодироваться с помощью « Почтовые расходы Postage and Shipping . Я использую следующую формулу

=VLOOKUP(B2,$E$2:$F$8,2,1)

рассчитать значение в столбце Notes . ExpectedValue жестко закодировано, и Valid основан на том, равно ли Notes моему ExpectedValue

Если я сделал то, на что надеялся, Description сопоставляется с моим LookupValue и, если это близкое совпадение, поле заполняется соответствующим значением из DecodedValue .

Исходя из этого, строка 1 в OK . Тем не менее, строки 2-8 являются BAD , так или иначе, строки совпадают и декодируются как первое значение индекса в моей поисковой таблице. Поскольку все строки, начинающиеся с PAYPAL , в OK , я подумал, что это могут быть пробелы или знаки пунктуации в таблице поиска. Но, если это так, строки 5, 8 и 15 должны пройти. Поэтому я подумал, что поиск соответствовал только в том случае, если значение поиска было в начале ячейки. Но тогда линия 1 не прошла бы.

15 строк данных CSV, вставлены ниже и ссылки выше. 

Line,Description,Notes,ExpectedValue,Valid,LookupValue,DecodeValue
1,DDA PUR ATT*PAYMEN 800-331-0500 TX 300100860296,Utilities:Telephone,Utilities:Telephone,OK,ATT*PAY,Utilities:Telephone
2,DDA PUR THE HOME D MILWAUKEE    WI,Utilities:Telephone,Repairs,BAD,NETFLIX,Supplies:Research
3,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,PAYPAL,Supplies
4,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,STONE CREE,Craft Service
5,DDA PUR WALGREENS SHOREWOOD    WI,Utilities:Telephone,Medical,BAD,WALGREENS,Medical
6,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,THE HOME D,Repairs
7,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,USPS,Postage and Shipping
8,DDA PUR NETFLIX.CO NETFLIX.COM  CA,Utilities:Telephone,Supplies:Research,BAD,,
9,PAYPAL           INST XFER,Supplies,Supplies,OK,,
10,PAYPAL           INST XFER,Supplies,Supplies,OK,,
11,PAYPAL           INST XFER,Supplies,Supplies,OK,,
12,PAYPAL           INST XFER,Supplies,Supplies,OK,,
13,PAYPAL           INST XFER,Supplies,Supplies,OK,,
14,DDA PUR THE HOME D MILWAUKEE    WI,Utilities:Telephone,Repairs,BAD,,
15,DDA PUR USPS 56064 MILWAUKEE    WI,Utilities:Telephone,Postage and Shipping,BAD,,
|источник

1 ответ1

0

Как уже объяснялось в комментариях, параметр range_lookup для VLOOKUP не является нечетким поиском. Скорее, он предполагает, что список находится в порядке возрастания и находит наиболее близкое совпадение.

Вот формула массива, которую вы должны были использовать (введено используется CTRL+ALT+ENTER)

{=IF(MIN(LEN(SUBSTITUTE($B2,$F$2:$F$8,"")))=MAX(LEN(SUBSTITUTE($B2,$F$2:$F$8,""))),"",INDEX($G$2:$G$8,MATCH(MIN(LEN(SUBSTITUTE($B2,$F$2:$F$8,""))),LEN(SUBSTITUTE($B2,$F$2:$F$8,"")),0)))}

И вот как это работает:

Одна часть формулы многократно повторяется: LEN(SUBSTITUTE($B2,$F$2:$F$8,""))
(Я просто собираюсь использовать LEN(...) в будущем, чтобы сэкономить место)
Он дает нам массив целых чисел, основанный на замене каждого LookupValue пробелами в Description и измерении длины результата. Чем короче длина, тем больше текста соответствует. Взятие MIN этого массива говорит нам самое длинное LookupValue которое существует в Description . Конечно, это также означает, что THE HOME D всегда будет побежден THE HOME DESIGNERS или чем-то другим, поэтому будьте осторожны / точны в своем поле LookupValue .

Разбирая эту часть, давайте покажем формулу, подобную этой, чтобы упростить ее следование:

{=IF(MIN(LEN(...)))=MAX(LEN(...))),"",INDEX($G$2:$G$8,MATCH(MIN(LEN(...))),LEN(...)),0)))}

Первая часть IF(MIN=MAX) проверяет, чтобы убедиться, что хотя бы одна из записей LookupValue существует в описании. Если ничего не происходит, то SUBSTITUTE ничего не делает, и все значения LEN будут одинаковыми.

INDEX($G$2:$G$8,MATCH(MIN(LEN(...))),LEN(...)),0))

Как только мы пройдем эту проверку, настоящее мясо появится в комбинации INDEX + MATCH . Мы сопоставляем самую короткую длину (т. Е. Самую длинную LookupValue которая существует в Description) в массиве длин, чтобы использовать первое LookupValue . Вставьте это в массив DecodeValue и из него появится нужное значение Notes .

Ключевой момент - сделать поле LookupValue максимально длинным и точным, чтобы избежать повторяющихся совпадений, и тогда все должно работать нормально.

Вот CSV-версия моих результатов:

Line, описание, заметки, ExpectedValue, Действительно, LookupValue, DecodeValue
1, DDA PUR ATT PAYMEN 800-331-0500 TX 300100860296, Утилиты: Телефон, Утилиты: Телефон, OK, ATT PAY, Утилиты: Телефон
2, DDA PUR THE HOME D MILWAUKEE WI, Ремонт, Ремонт, ОК, NETFLIX, Расходные материалы: Исследования
3, DDA PUR STONE CREE SHOREWOOD WI, Craft Service, Craft Service, хорошо, PAYPAL, Поставки
4, DDA PUR STONE CREE SHOREWOOD WI, Craft Service, Craft Service, ОК, STONE CREE, Craft Service
5, DDA PUR WALGREENS SHOREWOOD WI, Медицинский, Медицинский, ОК, WALGREENS, Медицинский
6, DDA PUR STONE CREE SHOREWOOD WI, Craft Service, Craft Service, ОК, ДОМ D, Ремонт
7, DDA PUR STONE CREE SHOREWOOD WI, Craft Service, Craft Service, хорошо, USPS, почтовые расходы и доставка
8, DDA PUR NETFLIX.CO NETFLIX.COM CA, Поставки: Исследования, Поставки: Исследования, ОК,
9, PAYPAL INST XFER, Поставки, Поставки, ОК,
10, PAYPAL INST XFER, Поставки, Поставки, ОК,
11, PAYPAL INST XFER, Поставки, Поставки, ОК,
12, PAYPAL INST XFER, Поставки, Поставки, ОК,
13, PAYPAL INST XFER, Поставки, Поставки, ОК,
14, DDA PUR HOME D MILWAUKEE WI, Ремонт, Ремонт, ОК,
15, DDA PUR USPS 56064 MILWAUKEE WI, Почтовая и доставка, Почтовая и Доставка, ОК,

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .