PROCX Valores Duplicados
Este tutorial demonstra como fazer o PROCX de valores duplicados no Excel. Se a sua versão do Excel não for compatível com o PROCX, leia como usar os valores duplicados do PROCV.
Pesquisa de Valores Duplicados
No Excel 365, a pesquisa de valores duplicados é mais fácil graças à função FILTRO. Primeiro, demonstraremos a função FILTRO e, em seguida, demonstraremos como procurar valores duplicados com o PROCX.
Com a função FILTRO, basta inserir a matriz (por exemplo, Notas) que queremos retornar e o intervalo de critérios (por exemplo, ID do Estudante) com os critérios (por exemplo, 2021-A).
=FILTRO(C3:C7;B3:B7=E2)
Também podemos aninhar a função FILTRO dentro da função ÍNDICE e inserir um núm_linha (por exemplo, F3) para retornar uma enésima correspondência que desejamos em vez de retornar todas as correspondências.
=ÍNDICE(FILTRO(C3:C7;B3:B7=E3);F3)
Os cenários acima mostram que a função FILTRO é a solução mais conveniente para esses tipos de problemas no Excel 365. No entanto, os cenários de pesquisa que exigem uma lista de valores de pesquisa geralmente são mais bem resolvidos por meio de fórmulas PROCX porque podemos convertê-las em fórmulas de matriz dinâmica, o que as torna mais flexíveis do que a Função FILTRO.
Vamos analisar primeiro a solução de matriz não dinâmica para entender como ela funciona antes de usar a matriz dinâmica PROCX.
PROCX – Valores de Pesquisa Duplicados
Digamos que você queira pesquisar uma lista de valores duplicados usando a função PROCX. Abaixo, à direita, temos nossos valores de pesquisa. À esquerda, temos a tabela de pesquisa. Queremos pesquisar cada valor duplicado e gerar em linhas separadas.
Podemos usar a solução de fórmula de matriz não dinâmica abaixo:
=PROCX(F3&"-"&CONT.SE($F$3:F3;F3);$D$3:$D$7;$C$3:$C$7)
Vamos examinar a fórmula:
ID Única – CONT.SE
Primeiro, juntamos o ID original (por exemplo, ID do Estudante) com a função CONT.SE para criar uma lista de IDs únicos que distinguirá a primeira entrada de um item de suas duplicatas.
=B3&"-"&CONT.SE($B$3:B3;B3)
O bloqueio de uma parte da referência de intervalo dentro da função CONT.SE nos permite contar a ocorrência de um valor à medida que o intervalo se expande.
=CONT.SE($B$3:B3;B3)
Agora, usamos o operador & para unir a função CONT.SE ao ID original. Também adicionamos um separador (por exemplo, “-“).
=B3&"-"&D3
A combinação de tudo resulta em nossa fórmula de ID exclusivo:
=B3&"-"&CONT.SE($B$3:B3;B3)Valores de Pesquisa Duplicados – Enésima Correspondência
Assim como no caso do ID original, também precisamos criar uma lista de IDs únicos para os valores de pesquisa. Aplicando o mesmo método:
=G3&"-"&CONT.SE($G$3:G3;G3)
Função PROCX
Agora, inserimos a nova matriz de pesquisa e a lista de valores de pesquisa na função PROCX:
=PROCX(H3;$E$3:$E$7;$C$3:$C$7)
PROCX – Valores de Pesquisa Duplicados (Matriz Dinâmica)
Em vez de adicionar novas colunas e copiar ou arrastar fórmulas, podemos converter as fórmulas PROCX anteriores em uma fórmula de matriz dinâmica que pode gerar o mesmo resultado.
Veja como fica:
=PROCX(E3:E7&"-"&CONT.SE(DESLOC(E3;0;0;SEQUÊNCIA(LINS(E3:E7)));E3:E7);
B3:B7&"-"&CONT.SE(DESLOC(B3;0;0;SEQUÊNCIA(LINS(B3:B7)));B3:B7);
C3:C7)
Vamos analisar a fórmula acima:
Assim como no método anterior, primeiro precisamos criar uma lista de IDs únicos usando a função CONT.SE. O desafio é criar uma fórmula de matriz que gerará uma matriz de referências de intervalo (por exemplo, B3, B3:B4, B3:B5 e assim por diante).
Fórmula DESLOC-SEQUÊNCIA-LINS
Podemos usar a combinação das funções DESLOC, SEQUÊNCIA e LINS para retornar uma matriz de referências de intervalo.
Vamos começar com a lista da matriz de pesquisa (por exemplo, B3:B7):
=DESLOC(B3;0;0;SEQUÊNCIA(LINS(B3:B7))
Observação: A fórmula acima não funcionará por si só. O Excel não pode retornar uma matriz de intervalos ou matrizes, mas pode avaliá-la, como veremos mais adiante.
Função LINS
Primeiro, precisamos determinar o número total de linhas usando a função ROWS.
=LINS(B3:B7)
Função SEQUÊNCIA
Em seguida, inserimos o resultado da função LINS na função SEQUÊNCIA para gerar uma lista de números de contagem, que também representa o número de células por intervalo na matriz de intervalos.
=SEQUÊNCIA(C2)
Fórmula DESLOC da Matriz
Em seguida, inserimos os resultados da função SEQUÊNCIA na altura (4o argumento) da função DESLOC para criar os intervalos.
=DESLOC(B3;0;0;C3:C7)
Como mencionado anteriormente, isso não funcionará, mas é assim que a matriz de intervalos deve se parecer:
Começamos com B3 e o expandimos para cada altura (por exemplo, B3:B7).
Observação: DESLOC é uma função volátil, o que significa que ela recalcula sempre que há uma alteração na planilha, mesmo que não esteja relacionada às entradas da própria DESLOC.
Função CONT.SE da Matriz
Agora que temos uma matriz de intervalos, nós a inserimos na função CONT.SE e retornamos uma matriz de enésimas ocorrências.
=CONT.SE(DESLOC(B3;0;0;C3:C7);B3:B7)
ID Único da Matriz
Por fim, concatenamos a saída da matriz da função CONT.SE à matriz de pesquisa (por exemplo, B3:B7) para gerar a matriz de IDs únicos.
=B3:B7&"-"&F3:F7
A combinação de todas as funções resulta em nossa matriz ID Única:
=B3:B7&"-"&CONT.SE(DESLOC(B3;0;0;SEQUÊNCIA(LINS(B3:B7))),B3:B7)Também fazemos o mesmo com os valores de pesquisa:
=I3:I7&"-"&CONT.SE(DESLOC(I3:I7;0;0;SEQUÊNCIA(LINS(I3:I7)));I3:I7)
A combinação de tudo resulta em nossa fórmula de matriz original:
=PROCX(E3:E7&"-"&CONT.SE(DESLOC(E3;0;0;SEQUÊNCIA(LINS(E3:E7)));E3:E7);
B3:B7&"-"&CONT.SE(DESLOC(B3;0;0;SEQUÊNCIA(LINS(B3:B7)));B3:B7);
C3:C7)
