Excel - Como contar a ocorrência de caracteres em strings em intervalos de comprimentos variáveis

Question

Plano de fundo: estou analisando a frequência com que os tópicos aparecem em um conjunto de e-mails. Cada tópico tem um caractere correspondente (a-z), e cada e-mail foi atribuído a vários desses caracteres sempre que algum aspecto do tópico surgiu:

Noexemploacima,oMAIL2tocounotópico'c'2vezese'd'2vezes.

Pergunta:Comofaçopararesumiressasocorrênciasparacadae-mailseparadamenteseonúmerodelinhasecaracteresquecorrespondemacadae-mailforimprevisível(entre1e~12)?

Estoupensandoemformatarosresultadosdaseguinteforma:

Eu posso dar a cada linha seu rótulo de e-mail correspondente. Eu acho que eu teria cada ocorrência de contagem de células cinzas de um caractere (por exemplo, 'a' para C2 ) encontrado em algum tipo de string concatenada de todas as células que estão em linhas marcadas como MAIL1? Mas ficarei grato por qualquer tipo de solução que permita resultados separados para cada e-mail.

    

Answer 1

Por favor, note que as fórmulas nesta resposta precisam ser inseridas na célula C2 , então copiadas / preenchidas conforme necessário.

EDITAR: Acontece que é uma maneira de resolver o problema sem usar células auxiliares:

=
SUMPRODUCT(
  ($B2=$A$2:INDEX($A:$A,MATCH("*",$B:$B,-1)))*
  (LEN($B$2:INDEX($B:$B,MATCH("*",$B:$B,-1)))-LEN(SUBSTITUTE($B$2:INDEX($B:$B,MATCH("*",$B:$B,-1)),C$1,"")))
)

Explicação: O termo $A$2:INDEX($A:$A,MATCH("*",$B:$B,-1)) e a versão correspondente da coluna B são usados para ajustar dinamicamente os intervalos de origem. A fórmula é equivalente à seguinte versão estática aplicável à planilha de exemplo:

=
SUMPRODUCT(
  ($B2=$A$2:$A$11)*
  (LEN($B$2:$B$11)-LEN(SUBSTITUTE($B$2:$B$11,C$1,"")))
)

A solução mais simples seria a seguinte fórmula:

=COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*")

Infelizmente, isso falha no caso especial em que há mais de uma letra em uma célula. Por exemplo, o 'f' na célula B11 seria contado apenas uma vez.


Nós podemos resolver este problema de uma maneira feia e limitada, somando as contagens de múltiplas ocorrências. Por exemplo, o seguinte funcionará até um máximo de três ocorrências de qualquer caractere:

=COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*")
+COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*"&C$1&"*")
+COUNTIFS($A:$A,$B2,$B:$B,"=*"&C$1&"*"&C$1&"*"&C$1&"*")

Como alternativa, podemos usar células auxiliares. A seguinte fórmula copiada / preenchida em cada célula no intervalo C2:H11 faz exatamente isso:

=
IF(
  ""<>$A2,
  LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")),
  SUMIF($A:$A,$B2,C:C)
)

Breve explicação:

""<>$A2 seleciona se a célula é uma célula auxiliar (true) ou uma célula cinza resultante (false).

LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")) calcula o número de ocorrências da letra apropriada na célula apropriada.

SUMIF($A:$A,$B2,C:C) soma as contagens da letra apropriada.