EXCEL - Подсчет пар / квадратов чисел в строках. Как считать?

Question

У меня есть 300+ строк, содержащих 40+ столбцов, каждый столбец имеет случайное число 1-40.

Я хочу знать, как искать в каждой строке пары или числа, которые встречаются вместе в строке.

Пример.

5   10  12  14  16  17  19  30  31  32  33  40  51  
4   7   11  16  17  23  24  26  32  37  39  46  47  
1   2   4   7   11  15  18  31  34  35  37  38  43  
3   6   14  16  19  21  24  27  33  34  38  42  47      
5   7   8   15  17  22  23  24  26  27  30  37  40  

Я хочу знать, сколько раз 1 и 2 происходят вместе или 23 и 24, или 5 и 30.

Как бы я это сделал? какую формулу я бы использовал и как бы ее реализовать.

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

Answer 1

Вам нужно объединить IF , AND и COUNTIF .

Например,

=IF(AND(COUNTIF(A1:J1,23)>0,COUNTIF(A1:J1,24)>0),1,0)

Это решение дает 1, ЕСЛИ как 23, так и 24 произошло по крайней мере один раз от А1 до J1, и возвращает 0, если не оба они появляются в строке.

Answer 2

Из-за вложенности я бы порекомендовал пользовательскую функцию (UDF) для решения этой проблемы.

Этот код ...

Function CountPairs(theRange As Range, P As Variant, Q As Variant) As Long
Dim PRow As Range
Dim PCell As Range, QCell As Range
Dim Result As Long
' initialize
Result = 0

For Each PRow In theRange.Rows      'search each row for P Value
    For Each PCell In PRow.Cells
        If PCell.Value = P Then
            For Each QCell In PRow.Cells 'if P Value found, search for Q Value
                If QCell.Value = Q Then
                    Result = Result + 1
                    Exit For
                End If
            Next QCell
            Exit For
        End If
    Next PCell
Next PRow

' clean up
Set PRow = Nothing
Set PCell = Nothing
Set QCell = Nothing

CountPairs = Result

End Function

Дает эти результаты ...

... где я использовал условное форматирование, чтобы выделить значения "P" и "Q"

---

Однако, с помощью столбца "помощник", это может быть решено с помощью встроенной формулы ...

Где, в этом случае вспомогательный столбец содержит эту формулу, заполненную ...

=IF(COUNTIF($B4:$N4,"="&$B$2)>0,IF(COUNTIF($B4:$N4,"="&$C$2)>0,1,0),0)

и ячейка A2 содержит простую сумму ...

=SUM(A4:A8)