☰

Excel. Как найти общие текстовые значения в нескольких (>2) строках?

tag-icon • tag-icon microsoft-excel worksheet-function
Excel. Как найти общие текстовые значения в нескольких (>2) строках?

По сути, это вариант вопроса, заданного здесь: Excel. Как найти общие текстовые значения в нескольких (>2) столбцах?

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

Источник Координата1 Координата2 Координата3 Координата4 Координата5 Координата6
Источник1 (4,0) (5,0)
Источник2 (3,0) (4,0) (5,0) (3,1) (4,1) (5,1)
Источник3 (4,0) (5,1) (5,0)
Источник4 (3,0) (4,0) (5,0) (3,1) (3,2)
Источник5 (2,3) (3,2) (4,1) (4,2) (5,0)

В указанной теме пользователь XOR-LX предоставил очень полезный метод для сравнения по столбцам. Действительно, если я транспонирую указанные выше данные, его метод работает отлично, возвращая (5,0) как уникальное местоположение в первой выходной ячейке, но я не смог изменить его для использования по строкам, так как мои данные организованы. До сих пор я пытался изменить его следующим образом, но безуспешно:

  • Диапазон1 =$B$2:$G$6
  • Арри1 =ROW(Range1)-MIN(ROW(Range1))
  • Арри2 =COLUMN(INDEX(Range1,1,))-MIN(COLUMN(INDEX(Range1,1,)))+1
  • Арри3 = MMULT(0+COUNTIF(OFFSET(INDEX(Range1,1,),Arry1,,,),INDEX(Range1,1,))>0),COLUMN(INDIRECT(ROWS(Range1)&":1"))^0)

Окончательный ввод:
=IFERROR(INDEX(INDEX(Range1,1,),SMALL(IF(FREQUENCY(IF(INDEX(Range1,1,)<>"",IF(Arry3=ROWS(Range1),MATCH(INDEX(Range1,1,),INDEX(Range1,1,),0))),Arry2),Arry2),COLUMNS(A:$A))),"")

Используя указанную выше модификацию, я просто не получаю вывода (и сообщений об ошибках), просто пустую ячейку. По сути, я попытался просто "обратить" его метод, поменяв СТРОКУ на СТОЛБЕЦ и так далее, но я подозреваю, что решение может быть немного сложнее, особенно учитывая мою слабость с матричными функциями Excel.

Любая помощь будет высоко оценена.

решение1

Вы можете попробовать этот код в модуле рабочего листа, он не очень надежный и может повторять цвета, но он может сработать. Если нет, отправьте сюда. Просто измените компонент диапазона (в настоящее время B1 на G100) в соответствии с вашими требованиями.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, c As Range
Dim MyCI As Long

If Intersect(Target, Range("$B$1:$G$100")) Is Nothing Then Exit Sub

For Each cell In Range("$B$1:$G$100")
    cell.Interior.ColorIndex = 0
Next cell

For Each cell In Range("$B$1:$G$100")
    If WorksheetFunction.CountIf(Range("$B$1:$G$100"), cell.Value) > 1 Then
    If cell.Interior.ColorIndex = -4142 Then
    MyCI = Int((56 - 1 + 1) * Rnd + 1)
        For Each c In Range("$B$1:$G$100")
            If c.Value = cell.Value Then c.Interior.ColorIndex = MyCI
        Next c
   End If
   End If
Next cell

End Sub

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