Exibe as linhas que atendem aos critérios do COUNTIF

Question

Não tenho certeza se isso é possível, mas ao usar a função COUNTIF é possível realmente encontrar as linhas que atendem aos critérios.

Por exemplo, eu tenho uma fórmula COUNTIF que analisa uma grande quantidade de dados e retorna com o resultado de '5'

Então, 5 linhas atendem aos critérios do COUNTIF, é possível encontrar facilmente essas 5 linhas, se sim, como?

Agradecemos antecipadamente:)

    

Answer 1

Aqui está um exemplo muito simples. Queremos contar o número de valores na coluna B que excedem 10 . Colocamos os critérios na célula C1 e em C2 digite:

=COUNTIF(B:B,C1)

Agorasabemosqueexistem11itensquecontribuemparaacontagem.Agoraqueremosencontrá-los.
Insiraaseguintefunçãodefinidapelousuárioemummódulopadrão:

PublicFunctionCountIfFinder(rngAsRange,critAsString)AsStringDimrAsRange,DQAsStringDQ=Chr(34)crit=DQ&crit&DQCountIfFinder=""
    Set rng = Intersect(rng, rng.Parent.UsedRange)

    For Each r In rng
        s = "=countif(" & r.Address & "," & crit & ")"
        If Evaluate(s) = 1 Then CountIfFinder = CountIfFinder & "," & r.Address(0, 0)
    Next r

    CountIfFinder = Mid(CountIfFinder, 2)
End Function

Escolha uma célula (diga D1 ) e digite:

=CountIfFinder(B:B,C1)

FunçõesDefinidaspeloUsuário(UDFs)sãomuitofáceisdeinstalareusar:

1. ALT-F11exibeajaneladoVBE
2. ALT-IALT-Mabreumnovomódulo
3. coleomaterialefecheajaneladoVBE

Sevocêsalvarapastadetrabalho,aUDFserásalvacomela.SevocêestiverusandoumaversãodoExcelposteriora2003,deverásalvaroarquivocomo.xlsmemvezde.xlsx

PararemoveroUDF:

1. abrirajaneladoVBEcomoacima
2. limpeocódigo
3. fecheajaneladoVBE

ParausaroUDFdoExcel:

=myfunction(A1)

Parasabermaissobremacrosemgeral,consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!

    

Answer 2

Veja a solução abaixo, embora todas as etapas tenham sido detalhadas na imagem anexada.

Vou esboçar uma explicação detalhada de cada uma das etapas mostradas abaixo para explicar como isso funciona.

Passo1:

Demonstraacondiçãodepesquisaqueestabelecemos.Nesteexemplo,estamosprocurandotodasaslinhasemqueGender=M

AfunçãoCOUNTIFequivalentefoimostradaabaixopararetornaronúmerodelinhasencontradascomestacondiçãoé3

Passo2:

Estabeleçaumafórmuladematriz=IF($B$2:$B$8=$B$11,ROW($B$2:$B$8)).EstaéumafórmuladematrizeusaumaextensãodafunçãoIFregular.ElecomparaosvaloresnamatrizB2:B8comB11eretornaosresultadosdacomparaçãocomoumamatrizdevalores.Quandoacomparaçãoéverdadeira,oresultadoéonúmeroROW(),senãoFALSE(porquenenhumvaloréfornecidoquandoacomparaçãoéfalsa).

Paraentendermelhor,vocêpodecomeçarcomasfórmulasIFmaissimples,conformeabaixo,eexperimentardiferentesopçõesemvalue_if_trueevalue_if_falseeentenderosresultados'SE(B2=B11,LINHA(B2),)'

'SE(B2=B11,LINHA(B2),"incompatibilidade")'

Agora tente o mesmo mudando B11 para F e, em seguida, veja o que acontece com os resultados.

Etapa 3: Aqui estamos usando a função SMALL para retornar o enésimo menor valor em uma matriz. No entanto, o truque aqui é alterar o enésimo valor em cada linha. Portanto, a primeira linha deve mostrar o menor valor na matriz F2:F8 , a segunda linha deve retornar a segunda menor e a terceira linha deve retornar o terceiro menor valor.

Portanto, usamos o ROW () - 1 para obter a enésima configuração da variável correspondente, e o resto é fácil.

Etapa 4:

No final da etapa 3, temos o número de linhas em que nossa condição de pesquisa é satisfeita. Agora, nesta etapa, tudo o que precisamos fazer é usar a função INDEX para extrair os valores de linha correspondentes a esses números de linhas.

Para conseguir isso, primeiro selecione as células M2: O2, pressione F2 e seu cursor estará localizado na célula M2. Insira a fórmula INDEX($A$1:$C$8,J2,0) e pressione Ctrl + Shift + Enter juntos para que isso funcione como uma fórmula de matriz. O 0 nessa fórmula força a devolução da linha inteira em vez de valores de uma coluna específica do intervalo A1: C8.

Agora, selecione M2: O4 e pressione Ctrl + D para copiar a fórmula na linha superior até as células abaixo.

BINGO!

Poste seus comentários se precisar de esclarecimentos e ficarei mais do que feliz em esclarecer. Eu usei uma série de simplificações e detalhei as etapas para explicar o funcionamento. Todas essas fórmulas podem ser combinadas para alcançar os mesmos resultados de uma só vez.

Também outra simplificação: escolhendo inserir as fórmulas no número exato de linhas, no entanto, quando você não sabe o número de linhas que serão retornadas pela condição de pesquisa, você pode fazer a matriz de resultado final tão grande quanto o intervalo do conjunto de dados original para potencialmente atender se todas as linhas forem retornadas. Você também pode adicionar a entrega de erros em cada uma das fórmulas para mostrar linhas em branco quando o número de linhas retornadas for menor que a área de resultados. Espero que isso faça sentido!