☰

Сравнение двух столбцов с частичными значениями и получение информации о совпадении и номере столбца

tag-icon • tag-icon microsoft-excel worksheet-function
Сравнение двух столбцов с частичными значениями и получение информации о совпадении и номере столбца

Я использовал VLOOKUP, MATCH, INDEX и даже дополнение Fuzzy. Я уверен, что что-то из этого или их комбинация могут сработать, просто у меня нет знаний, как заставить это работать и получать "#N/A" все время...

Проблема: У меня есть 2 столбца с разным общим количеством элементов, полных ссылок. В одном из них частично совпадают цифры с другим, с некоторыми изменениями, например:

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

Но у меня есть тысячи чисел, и я хочу сопоставить каждую ячейку от A до всего диапазона B, и если есть совпадение, даже в ячейке B6544, я хочу узнать и получить что-то (в столбце C) вроде «True B6544».

(Небольшой) пример того, с чем я имею дело (и это не точные ссылки, это внутренние разумные данные):

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

Как видите, J1 частично совпадает с B1, поэтому H1 возвращает «True, B1».

Как я уже сказал выше, я пробовал MATCH, VLOOKUP, INDEX и дополнение Fuzzy. Я понимаю, что мне нужно что-то вроде:

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

Но, похоже, ничего не помогает... Мы будем признательны за любую помощь!

решение1

Попробуйте эту небольшую пользовательскую функцию:

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

введите описание изображения здесь

Как показано на иллюстрации, вК1входить:

=partialmatch($B$1:$B$10,J1)

и копируем вниз.

Процедура удаляет начальную цифру из подстроки и пытается найти ее в столбце.

решение2

Поскольку Excel не поддерживает регулярные выражения, я не думаю, что есть формула, которой не нужен вспомогательный столбец. В моем примере,

  • A содержит числа со знаком «-»
  • B содержит совпадающие числа
  • C содержит значения B без первой цифры
  • D равно «ЛОЖЬ», если значение A не совпадает ни с одним значением в столбце B, или «ИСТИНА» + ссылка на соответствующую ячейку.

Формула в C1:

=RIGHT(B1,LEN(B1)-1)

Это удаляет первую цифру B1.

Формула в D1:

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

Обратите внимание, что хотя это должно дать желаемый результат, я бы рекомендовал разделить ИСТИНА/ЛОЖЬ и ссылку на ячейку на два столбца. Поэтому я объясню только ту ADDRESSчасть, которая даст вам ссылку на ячейку, если совпадение будет найдено, или ошибку в противном случае.

VLOOKUPздесь бесполезен, поскольку вернет значение в той же строке, что и совпавшее значение. MATCH, с другой стороны, возвращает строку совпавшего значения.

  • MID(A1,2,SEARCH("-",A1)-2)возвращает подстроку A1, начиная со второго символа, до и исключая первое вхождение "-". Это и есть искомое нами значение ( lookup_value).
  • MATCH(lookup_value, C$1:C$10, 0)вернет строку первого вхождения lookup_value. Поскольку мы не можем применить функцию к диапазону, а Excel не поддерживает регулярные выражения, нам нужен вспомогательный столбец C. Последний параметр ( 0) необходим, поскольку значения не сортируются. Обратите внимание, что возвращаемое значение строки относительно указанного диапазона, поэтому, если ваш диапазон не начинается со строки 1, вам придется это учесть (например, добавив ROW([first cell])-1к результату MATCH).
  • ADDRESS(matched_row, COLUMN(B1))создает ссылку на ячейку. Вы можете использовать абсолютный номер строки вместо , COLUMN(B1)если хотите, но это будет не так удобно для чтения человеком.

Вы должны решить для себя, какие ссылки должны быть абсолютными или относительными.

решение3

Я пытаюсь частично ответить на ваш вопрос, поскольку у нас пока нет полной картины.

Я добавил два вспомогательных столбца для обработки данных и их сравнения, что даст вам лучшее понимание.

Столбец C-helperудаляет 0в начале и конце - number.
Столбец I- helperудаляет начальную цифру (или первую цифру) из J col.
Затем я выполнил vlookupof data in I-helperпротив C - helperи выяснил, найдены ли данные или нет на основе результата vlookup().

Формула, которую я имею в

  • C - helper:==MID(B4,2,LEN(B4)-3)
  • I - helper:=MID(D4,2,LEN(D4)-1)
  • vlookup col: =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

Следующая часть проблемы — найти, где эта запись была найдена. Если запись найдена — возвращается ее адрес, иначе строка Not_Available is returned. Образец данных теперь выглядит так

ABC - помощник JI - помощник vlookup col HI X-помощник
1 025983553-1 25983553 225983553 25983553 найдено True, B1 $C$4
2 025973223-1 25973223 222222345 22222345 не найдено ЛОЖЬ Недоступно
3 025965463-2 25965463 233444667 33444667 не найдено ЛОЖЬ Недоступно
4 025911122-4 25911122 211198989 11198989 не найдено ЛОЖЬ Недоступно
5 025998764-1 25998764 212989238 12989238 не найдено ЛОЖЬ Недоступно
6 025925925-3 25925925 224397501 24397501 не найдено ЛОЖЬ Недоступно
7 025900000-2 25900000 225973223 25973223 найдено True, B2 $C$5
8 025999999-5 25999999 223334445 23334445 не найдено ЛОЖЬ Недоступно
9 025965453-6 25965453 211100110 11100110 не найдено ЛОЖЬ Недоступно
10 025943536-2 25943536 225911122 25911122 найдено True, B4 $C$7

Редактировать

Формула, которую я имею в

  • X - helper:=IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")

Связанный контент