Prefixos ou sufixos do Excel

Question

Tenho uma coluna no Excel que contém uma lista delimitada por vírgulas:

Header
1, 61
61
1, 61, 161
5, 55

Gostaria de extrair esses dados para poder contar as ocorrências de cada item para ter os seguintes resultados:

Count of Items
1    |    2
5    |    1
55   |    1
61   |    3
161  |    1

Tentei countif com "*" mas é uma bagunça porque neste caso tenho prefixos ou sufixos (1,61,161)

POR FAVOR AJUDE!

Answer 1

Opção 1:

Eu gostaria de sugerir UDF (Função Definida pelo Usuário), que não apenas conta a ocorrência de números separados por vírgula, mas também conta texto.

Como funciona:

• ImprensaAlt+F11para obter o editor VB entãocópia de&Colareste código comoMódulo.

  Option Explicit
  
  Function CountOccurrence(SearchRange As Range, Phrase As String) As Long
  
  Dim RE As Object, MC As Object
  Dim sPat As String
  Dim V As Variant
  Dim I As Long, J As Long
  
  V = SearchRange
  
  Set RE = CreateObject("vbscript.regexp")
  With RE
  .Global = True
  .MultiLine = True
  .ignorecase = True
  .Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
  End With
  
  For I = 1 To UBound(V, 1)
  If RE.test(V(I, 1)) Then J = J + 1
  Next I
  
  CountOccurrence = J
  
  End Function
  

• Insira os critérios no intervalo H18:H26e, em seguida, insira esta fórmula na célula I18e preencha-a.

=CountOccurrence($G$18:$G$24,H18)

Opção 2:

Insira esta fórmula na célula I18e preencha-a.

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

Ajuste as referências de células conforme necessário.

Answer 2

1. Primeiro você precisa dividir seus números em células separadas:
   • selecione seus dados
   • na aba de dados selecione "dividir em colunas"
   • selecione "delimitado", próximo
   • os dados selecionados são separados por "vírgula", finalize
2. agora você pode usar COUNTIF, por exemplo
   =COUNTIF($A$2:$C$5,E2)

Answer 3

Parece que você já tem uma solução, mas vou lançar uma solução não-VBA que lida com dados dinâmicos. Ele usa algumas colunas auxiliares que você pode preencher previamente em um intervalo arbitrariamente grande. Quando não houver dados associados, as células ficarão em branco. Algumas colunas auxiliares poderiam ser eliminadas; elas são incluídas para minimizar a repetição, mas todas as colunas auxiliares podem ser ocultadas.

Seus dados estão na coluna A. A coluna B determina o número de valores em cada entrada com base no número de vírgulas. A fórmula em B3 é:

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

A coluna C é apenas a contagem cumulativa de componentes da coluna B. C2 é inserido como 0. A fórmula em C3 é:

=IF(ISBLANK(A3),"",SUM(B3:B$3))

Preencha as colunas B e C com quantas linhas você tiver dados. Você sempre pode estender essas colunas, se necessário.

A coluna E é apenas por conveniência. Ele fornece um índice para os valores analisados. Você pode codificar 1e adicionar 1 a ele para cada linha sucessiva. Sem um bom motivo, baseei-me no número da linha (o -2da fórmula abaixo é para ajustar os valores para começar em 1). As células além do número total de valores ficam em branco. Minha fórmula no E3:

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

A coluna F serve apenas para evitar a repetição de fórmulas. Ele extrai a entrada relevante da Coluna A da qual o componente atual será analisado. A fórmula em F3 é:

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

Ele encontra a entrada apropriada comparando o número do componente na coluna E com a contagem cumulativa do componente na coluna C.

A coluna G contém os valores dos componentes analisados, todos em uma única coluna contígua que é fácil de trabalhar. A fórmula em G3 é:

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

Isso determina qual elemento analisar da entrada da coluna F, subtraindo a contagem cumulativa de elementos do último registro de entrada "concluído" do número do elemento atual.

As colunas E a G devem ser propagadas para linhas suficientes para cobrir o número previsto de valores dos componentes (pelo menos várias vezes o número de linhas de dados). Observe que todas as fórmulas acima que se referem ao intervalo $C$2:$C$10 devem ser ajustadas para incluir o intervalo completo dos seus dados.

Agora que você tem todos os elementos analisados ​​em uma bela coluna, há várias maneiras de agregá-los e obter as contagens. Usei uma tabela dinâmica, que também fornece a lista de valores exclusivos ao mesmo tempo.

Selecione todo o intervalo pré-preenchido da coluna G para a tabela dinâmica. Use esse campo para a janela de linhas e a janela de valores (selecione contagem como agregação). O intervalo incluirá espaços em branco nas linhas não utilizadas, portanto, use o filtro integrado para desmarcar os espaços em branco.

Quando os dados mudarem, basta atualizar a tabela dinâmica e verificar se quaisquer novos valores de elemento estão selecionados no filtro.