Сравните несколько столбцов для любых двух или более ячеек в одной строке, имеющих одинаковое содержимое

Question

Мне нужна формула для сравнения нескольких столбцов для любых двух или более ячеек в одной строке, имеющих одинаковое содержание. Если это правда, тогда отобразите "ТЕКСТ А" (может быть что угодно, например "ИСТИНА"). Если все значения различны, выведите "TEXT B" или просто "FALSE".

Я использую формулу IF но для сравнения столбцов потребуется много времени, поэтому мне нужна более качественная формула.

=IF((B2=C2);"YES";IF((B2=D2);"YES";IF((C2=D2);"YES";"ALL DIFFERENT")))

То же самое относится и к аналогичной функции с OR (в результате истина или ложь)

=AND(($C2<>$D2);($C2<>$E2);($D2<>$E2))

Ниже приведен скриншот рабочего листа, который является лишь примером. Моя настоящая работа имеет более 4 столбцов.

Выделенные строки - это те, в которых есть две или более ячейки, содержащие один и тот же текст (группа 2 также должна быть выделена), поэтому в них должно отображаться сообщение "ТЕКСТ А".

Посмотреть мою таблицу онлайн

Answer 1

Это VBa делает это (как добавить VBa). Я предоставил несколько вариантов, чтобы вы могли масштабировать их в будущем, посмотрите первые 12 строк или около того, где вы можете вводить различные «ответы». Вы можете выбрать начальную и конечную строки, где будут отображаться результаты и какие слова показывать, если текстовое совпадение или нет! Обратите внимание, что выделение происходит из-за предоставленного вами документа Excel и не имеет никакого отношения к коду.

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

Sub DoTheThing()

'Answer these questions or ye walk the plank

Dim row As Integer
row = 2

Dim firstColumn As String
firstColumn = "B"

Dim lastColumn As String
lastColumn = "D"

Dim resultsColumn As String
resultsColumn = "G"

Dim isFoundText As String
isFoundText = "YES"

Dim isNotFoundText As String
isNotFoundText = "Good Job"

'***Below be for the cap'ains eyes only.

Do While Range("A" & row).Value <> ""

    Dim startChar As Integer
    startChar = Asc(firstColumn)

    Dim endChar As Integer
    endChar = Asc(lastColumn)

    Dim i As Integer

    Dim hasMatch As Boolean
    hasMatch = False

    For i = startChar To endChar

    If Range(Chr(i) & row).Value = Range(Chr(i + 1) & row).Value Then

        hasMatch = True

    End If

    If Range(Chr(startChar) & row).Value = Range(Chr(i + 1) & row).Value Then

        hasMatch = True

    End If

    Next i

    If (hasMatch) Then
        Range(resultsColumn & row).Value = isFoundText
    Else
        Range(resultsColumn & row).Value = isNotFoundText
    End If

row = row + 1


Loop

End Sub

Я написал результаты полковнику G (чтобы сохранить ваш оригинал как есть)

После запуска VBA

Answer 2

Двухшаговое решение без формул массива:

Шаг 1: Для каждого столбца рассчитайте количество вхождений. Если оно больше 1, тогда введите 1, в противном случае пустую строку. Формула для ячейки D2 на скриншоте:

=IF(COUNTIF($A2:$C2,A2)>1,1,"")

Эту формулу можно перетащить (или дважды щелкнуть) по нижнему правому углу, чтобы охватить все строки, затем всю выбранную строку D можно перетащить по нижнему правому углу, чтобы охватить все столбцы.

Шаг 2: Для каждой строки проверьте, есть ли "1"

Формула для ячейки G2 на скриншоте:

=IF(SUM($D2:$F2)>0,"YES","Good Job")

Эту формулу можно перетащить (или дважды щелкнуть) по нижнему правому углу, чтобы охватить все строки.

Чтобы сделать его лучше, вы можете подумать о переносе промежуточных вычислений на другой лист.

Answer 3

Формула для D2 на скриншоте:

=IF(MAX(COUNTIF($A2:$C2, $A2:$C2))>1,"YES","Good job")

Введите его как формулу массива , нажав Ctrl+Shift+Enter при редактировании формулы. После этого его можно перетащить (или дважды щелкнуть) по нижнему правому углу, чтобы закрыть все строки.

Answer 4

Я бы использовал Power Query Add-In для этого. Он имеет команду Group с функцией Count Distinct, которая может выяснить это по любой комбинации строк и / или столбцов.

Я создал прототип, который вы можете просмотреть или загрузить - его демонстрацию Power Query - сравните несколько столбцов для уникальных values.xlsx в моем One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Обратите внимание, что вы можете продолжать добавлять столбцы и / или строки на лист ввода, а затем просто нажать «Обновить все» на ленте данных, чтобы повторно обработать запросы Power.

90% этого было построено, просто щелкая по ленте Power Query. Единственным исключением был простой оператор if, который я написал на последнем шаге запроса "Сравнить несколько столбцов". Таким образом, кода для обслуживания гораздо меньше по сравнению с решением на основе формулы или VBA.

Answer 5

Для ячейки F2:

=IF(SUMPRODUCT(--(FREQUENCY(MATCH(B2:D2,B2:D2,0),COLUMN(B2:D2)-COLUMN(B2)+1)>0))=3,TRUE,FALSE)

Это даст вам True, когда в этих трех столбцах есть три разных значения.

Адаптировано с сайта, который содержит больше объяснений и опций для работы с пробелами:https://exceljet.net/formula/count-unique-text-values-in-a-range

На этом же сайте затрагиваются показатели и потенциальные проблемы с производительностью больших массивов данных:https://exceljet.net/formula/count-unique-values-in-a-range-with-countif

Answer 6

Существует простой способ сделать это с помощью формул. Позвольте мне отметить, что в этом примере показаны случаи, в которых используются только совпадения, включающие первый столбец. Любая формула должна быть проверена на примере, который содержит случайные совпадения.

Я объясню это в двух частях, так как количество столбцов может варьироваться. Первая часть - это выражение, которое вы строите для необходимого количества столбцов. Я добавил столбец данных, чтобы лучше проиллюстрировать формулу, и включил несколько случаев со случайными совпадениями:

Выражение для четырех столбцов данных выглядит следующим образом (показано для строки 2, первой строки данных):

    COUNTIF(C2:E2,B2)+COUNTIF(D2:E2,C2)+(D2=E2)

Если N - количество столбцов данных, формула содержит N-1 членов. Первый член подсчитывает, сколько столбцов имеют значения, равные первому. Второй подсчитывает, сколько из оставшихся столбцов равно второму. И так далее. Все они могут быть COUNTIF, но в последнем случае я использовал более простой термин. Вместо того, чтобы считать один столбец, я просто проверяю, равен ли последний последним последний. Результат True распознается как значение 1, False как значение 0. Если все значения уникальны, это выражение будет равно 0. В противном случае это будет большее число. Выражение входит в тест IF:

    =IF(<expression>=0,"Unique Message","Match Message")

Таким образом, для четырех столбцов данных формула будет иметь вид:

    =IF(COUNTIF(C2:E2,B2)+COUNTIF(D2:E2,C2)+(D2=E2)=0,"Unique Message","Match Message")

Результат выглядит так:

Answer 7

Вы можете использовать эту формулу:

=IF(Cell Number > 2nd cell Number, result)