Excel сопоставление двух массивов
У меня проблема, которая кажется довольно простой, но до сих пор не могу найти правильного решения, я хочу избежать использования vba.
У меня есть две таблицы в одной электронной таблице. оба имеют одинаковые столбцы - Имя, Город, Провинция. Моя цель - сравнить оба, и если три из трех значений в строке совпадают, то вытяните «1», если нет, вытащите 0.
Я использовал приведенные ниже формулы, но они не подходят для моего случая.
=IF(AND(A2=P:P,G2=M:M,H2=L:L),1,0)
=INDEX(A:P,MATCH(A2,P:P,FALSE),MATCH(G2,M:M,FALSE),2)
=INDEX(L:P,MATCH(A5,P:P,0),MATCH(G5,M:M,0),MATCH(H5,L:L,0))
=SUMPRODUCT(--(L2:L60=H2),--(M2:M60=G2),--(P2:P60=A2),B2:B60)
Кажется, что решение довольно простое, но я не могу его найти,
Заранее спасибо!
Ответы 2
Предположение: значения просто должны существовать, а не то, что они должны быть эквивалентной строки.
=If(IfError(Match(A2,P:P,0),0)*IfError(Match(G2,M:M,0),0)*IfError(Match(H2,L:L,0),0)>0,1,0)
Для каждой IfError вы выведете номер строки (> 0), если вы совпадете, или, если совпадения нет, будет выведен ноль. Умножьте что-нибудь на ноль, и вы получите ноль, что позволяет выводить 1 или 0 для истинного/ложного во всеобъемлющем операторе If.
Если они должны быть из одной строки, вы можете сравнить 2 совпадения, которые основаны на транзитивном свойстве (A=B, B=C, поэтому A=C):
=If(And(Match(A2,P:P,0)=Match(G2,M:M,0),Match(G2,M:M,0)=Match(H2,L:L,0)),1,0)
Редактировать1:
Согласно моему комментарию (к этому ответу) о ложных отрицательных результатах, UDF или подпрограмма в VBA были бы более подходящими, учитывая, что Match() возвращает строку первый, которая имеет совпадение.
Поскольку это не сообщение с тегом VBA, это немного выше ожидаемого ответа... Я бы порекомендовал:
A) Убедитесь, что вам удобно использовать VBA.
B) Сделайте сообщение о создании определяемой пользователем функции (обратите внимание, что любое сообщение здесь о VBA предполагает, что автор может взаимодействовать с эксперт по теме и приложит усилия для написания кода самостоятельно, поскольку StackOverflow не код для вас услуга).
Чтобы помочь понять, что мая должно быть в вашей пользовательской функции:
Цикл для прохождения значений от первой строки до последней строки в столбце поиска (т. е. L, M и P)
Переменная для динамического определения последней строки вашего столбца поиска.
Оператор if для сравнения значений из ваших значений поиска (например, A2, G2, H2) со значениями поиска на текущей итерации цикла.
Вывод 1 (совпадение) или 0 (нет совпадения).
Есть много способов сделать это с помощью VBA; надеюсь, это хорошее начало для вас, Ирина!
@Irina Обратите внимание, что это может дать ложноотрицательные результаты! Если у A2/G2/H2 есть несколько совпадений, они могут завершиться ошибкой из-за того, что Match() возвращает ПЕРВЫЙ экземпляр совпадения. Если вам нужен более надежный план, вам подойдет VBA, чтобы вы могли зацикливаться и выводить значение. Это можно сделать с помощью UDF (определяемой пользователем функции) или подпрограммы для вывода желаемого значения.
Кирилл, вы правы, он возвращает даже 0 - если совпало 2 из трех значений. не могли бы вы посоветовать, как я могу вам это UDF?
@Irina Я только что опубликовал свою рекомендацию для UDF в своем ответе. Это облегчит вам использование VBA, а также сделает еще один пост, относящийся к вашему UDF. Извините, что простая формула Excel не соответствует вашему желанию ='/
Ключевым моментом здесь является объединение столбцов вместе, их Match на этом.
Так
=IFERROR( IF( MATCH(H3&"_"&I3&"_"&J3, $C$2:$C$60&"_"&$B$2:$B$60&"_"&$A$2:$A$60,0), "Yes"), "No")
Выберите символ-разделитель, который иначе не отображается в ваших данных (я выбрал _)
Спасибо за ответ! Я сравниваю строки со строками в этих двух таблицах. Добавлю фото результата - если совпадет. Не могли бы вы добавить это как ответ, пожалуйста?