EXCEL- Contando pares / quadrantes de números em linhas. Como contar?

Question

Eu tenho mais de 300 linhas contendo mais de 40 colunas, cada coluna tem um número 1-40 aleatoriamente.

Eu quero saber como pesquisar em cada linha os pares ou números que ocorrem juntos na linha.

Exemplo.

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  

Eu quero saber quantos horários 1 e 2 ocorrem juntos ou 23 e 24, ou 5 e 30.

Como eu faria isso? qual fórmula eu usaria e como eu a implementaria?

Eu olhei para countifs, pivot tables, sumproducts, totals. E estou mais confuso do que quando comecei.

    

Answer 1

Você precisa combinar os dois IF , AND e COUNTIF .

Por exemplo,

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

Esta solução produz 1, SE ambos 23 E 24 ocorreram pelo menos uma vez de A1 a J1, e produz 0 se não ambos aparecerem na linha.

    

Answer 2

Por causa do aninhamento envolvido, eu recomendaria uma função definida pelo usuário (UDF) para resolver isso.

Este código ...

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

Gera esses resultados ...

...ondeuseiformataçãocondicionalparadestacarosvalores"P" e "Q"

No entanto, com uma coluna "auxiliar", ela pode ser resolvida com a fórmula incorporada ...

Onde,nestecaso,acolunaauxiliarcontémestafórmulapreenchida...

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

e a célula A2 contém uma soma simples ...

=SUM(A4:A8)