Pesquisa do Excel por substring e contagem de exibições

Question

Esta é uma pergunta extensa de este .

Suponha agora que tenho uma tabela a seguir com strings:

    A
1   CHI111
2   SEA222
3   SEA333
4   LA444
...

Eu também pretendo ter uma tabela para manter a contagem de palavras-chave CHI , SEA e LA :

    M    N
1   CHI  1
2   SEA  2
3   LA   1

Como faço para escrever uma fórmula para N1 ~ N3 para preencher a contagem?

Editar : adicionando uma pequena variante

Agora, a tabela tem duas palavras-chave com o mesmo número de entradas:

    A
1   CHI111
2   SEA222
3   SEA333
4   LA444
5   LA555
...

Como faço para exibir em uma célula com duas palavras-chave separadas por um separador (vírgula, barra, &). Assim:

    M    N   O
1   CHI  1   SEA, LA
2   SEA  2
3   LA   2

Além disso, se as contagens das três palavras-chave forem as mesmas, elas precisarão ser exibidas como "ALL"

    M    N   O
1   CHI  2   ALL
2   SEA  2
3   LA   2

OBRIGADO POR AJUDA !!

    

Answer 1

Para obter a contagem em N1, coloque:

=COUNTIF($A:$A,"*" & M1 & "*")

você pode usar essa fórmula para encontrar o valor com mais:

=INDEX(M:M,MATCH(MAX(N:N),N:N,0))

Emumafórmula,comseusprefixosaindaemM1:M3,useestafórmuladematriz:

=INDEX($M$1:$M$3,MATCH(MAX(COUNTIF($A$1:$A$4,"*"&$M$1:$M$3&"*")),COUNTIF($A$1:$A$4,"*"&$M$1:$M$3&"*"),0))

Sendo um array, ele precisa ser confirmado com Ctrl-Shift-Enter. Se feito corretamente, o Excel colocará {} em torno da fórmula.

Com fórmulas de matriz, queremos referenciar apenas os intervalos com dados e não usar referências de colunas completas.