Conte quantas vezes uma cadeia de comprimento N aparece em uma célula

Question

Eu tenho uma célula que contém números de pedido, separados por uma vírgula. (ou seja, 1689519145, 2944241, 1689611938 ).

Estou tentando obter uma fórmula para contar quantas vezes o número do pedido de 7 dígitos aparece na célula, se presente.

Minha pesquisa me leva a essa fórmula, que conta quantas vezes aparece entre os delimitadores, mas não consigo descobrir como analisar APENAS números de pedido de 7 dígitos: =LEN(P9)-LEN(SUBSTITUTE(P9,",",""))+1

    

Answer 1

Se você está contando todos os números de sete dígitos, você pode usar algo assim

{=SUM(IF((1*TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))=1),99)))>999999,IF((1*TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))=1),99)))<10000000,1)))}

que conta todos os números entre 999.999 e 10.000.000.

Como alternativa, se você quiser contar quantas vezes um número específico aparece, você pode adaptar a fórmula a algo como isso

=SUM(IF((1*TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)))=1),99)))=2944241,1))

onde você também pode substituir os números de 7 dígitos específicos como (2944241 neste caso) por uma referência.

Chaves para fazer a fórmula funcionar:

1. Verifique se as instâncias de ROW(OFFSET($A$1 permanecem ROW(OFFSET($A$1 ou pelo menos na primeira linha
2. Insira a fórmula como uma matriz (toda a ctl + shift + enter)

Como a fórmula funciona:

1. SUBSTITUTE(A1,",",REPT(" ",99) passa e divide cada número por 99 espaços
2. LEN(A3)-LEN(SUBSTITUTE(A3,",","")) conta quantos itens existem
3. ROW(OFFSET($A$1,, cria uma matriz com tantos itens
4. o primeiro array é multiplicado por 99
5. um é adicionado ao primeiro elemento da matriz
6. MID corta o array tantas vezes quanto os elementos, cortando um elemento diferente a cada vez
7. TRIM remove os espaços extras
8. o array é multiplicado por um para transformá-los de strings em números
9. o SUM conta quantas da matriz atendem aos critérios do IF

O crédito vai para Ron Rosenfeld durante a maior parte do levantamento pesado

    

Answer 2

Se você quiser fazer isso usando Len () / Substitute (), então

=(LEN(A2)+2-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A2&",","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|")," |||||||,","")))/LEN(" |||||||,")

Em vez de | você pode colocar qualquer outro símbolo que não esteja na string.

    

Answer 3

Parece-me que você deseja o número de vezes que um número específico de sete dígitos exatos apareça na célula-alvo, em vez de quantos números de qualquer comprimento e dígito aparecerem na célula. (Isso também pode ser descrito como quantos "elementos" estão na célula de destino, como se diria em "elementos de uma matriz".)

E da fórmula que sua pesquisa produz, eu acredito que você está vendo respostas para ambos e não percebendo que eles diferem, então as fórmulas apropriadas também devem ser diferentes e então usar a errada para sua necessidade aqui.

Primeiro, quantos elementos existem? Cada um é separado por uma vírgula, por isso, no entanto, existem muitas vírgulas, há uma mais (porque o último elemento não tem vírgula seguindo assim que você tem que adicionar mais um). Portanto, se você SUBSTITUAR () "nada" ("" no mundo do Excel) para cada vírgula, o comprimento da cadeia de destino será menor em quantas vírgulas existem na cadeia original. Sua fórmula faz isso. Mas você faz a parte "+1" incorretamente nela. Dado o par de outra forma desnecessário () em torno da função SUBSTITUTE (), acho que você sabia colocar o "+1" dentro deles, mas apenas digitou errado aqui. Se você deixá-lo fora, você tem que subtrair ("-1"). Subtraia o comprimento menor E o 1 do tamanho original e você tem quantos elementos.

Mas ... se você realmente precisa saber quantas vezes uma sequência exata de sete dígitos aparece, precisamente isso e nada mais, então você precisa usar o mesmo conceito, mas de forma ligeiramente diferente. Comece usando SUBSTITUTE () para trocar um "nada" pela string que você deseja contar. Subtraia isso do tamanho original. Finalmente, divida essa diferença pelo comprimento da string que você queria saber: sete dígitos aqui, talvez algum outro comprimento, algum outro use algum outro tempo e lugar. Então:

= ( LEN(P9) - SUBSTITUTE(P9,"1234567","") ) / 7

Claro, se você tiver a string em alguma outra célula, você pode colocá-la na fórmula em vez do meu "1234567" e tornar sua fórmula mais útil!

Se você quiser saber quantos EXATAMENTE sete dígitos, não mais, não menos longos, os números estão em P9, você pode usar as funções de matriz para examinar cada elemento em comprimento e contar aqueles que têm apenas sete dígitos . Meio complicado e se você precisar adicionar letras e ignorar esses elementos, é mais que isso. E ... bem, você entendeu a ideia.

    

Answer 4

Siga estas etapas para contar quantas vezes o número do pedido de 7 dígitos aparece na célula.

Estou assumindo que você tem dados em Intervalo A2:A4 .

1. Aplique o comando Texto à coluna no intervalo de dados para dividir os números.

Escreva esta fórmula em Cell D2 & preencha.

   =SUMPRODUCT(--(LEN(TRIM(A2:C2))=7))

Ajustar referências de célula na fórmula conforme necessário.