Como faço para contar valores / caracteres diferentes em uma coluna de células?

Question

Eu tenho o nome completo e o endereço de um cliente em uma única célula, por exemplo,;% Mr Smith, 1 High Street, London NW12 1AB

Tenho centenas de nomes, endereços e códigos postais em uma planilha que preciso pesquisar e filtrar por sobrenome, nome da rua e código postal.

Estou tentando identificar e contar o número de clientes em cada rua e postar código. Por exemplo, quantos clientes temos no SE12 e quantos clientes temos no NW12 1AB? Eu acho que o meu problema é pesquisar cada célula por três bits diferentes de informação.

Eu também preciso identificar as mesmas informações em duas planilhas. Por exemplo, quantos Srs. Smiths temos em duas planilhas. Quantos clientes na High Street ou no mesmo código postal aparecem nas duas planilhas.

Eu não sou um especialista no Excel e não ficarei ofendido se você mantiver a linguagem muito simples.

    

Answer 1

Como observa o Math, você pode ter mais flexibilidade se converter seus dados em colunas individuais. Isso pode ser facilmente alcançado usando o comando Text to Columns na guia Data , com o tipo de dados definido como Delimited e o delimitador definido como Comma .

Dito isto, não é difícil obter contagens de clientes por ruas ou códigos postais e filtrar clientes em determinadas ruas, códigos de códigos postais ou sobrenomes sem converter os dados em colunas.

Digamos que você tenha seus dados no intervalo A2: A500 na Planilha1 e o mesmo intervalo na Planilha2 e ambas as planilhas estejam na mesma pasta de trabalho.

Para contar o número de clientes no código postal "NW12 1AB" na Planilha1, use a fórmula:

=COUNTIF(Sheet1!$A$2:$A$500, "*NW12 1AB*")

Para contar o número de "Smiths" em Sheet1 e Sheet2, use:

=COUNTIF(Sheet1!$A$2:$A$500,"*Smith*") + COUNTIF(Sheet2!$A$2:$A$500,"*Smith*")

Para contar o número de "Grimes" no código postal "NW12 1AB", use:

=COUNTIFS(Sheet1!$A$2:$A$500,"*Grimes*", Sheet1!$A$2:$A$500,"*NW12 1AB*")

Para contar o número de clientes na Planilha2 que são chamados de "Salton" ou que moram na rua "Alta", use:

=COUNTIF(Sheet2!$A$2:$A$500,"*Salton*") + COUNTIF(Sheet2!$A$2:$A$500,"*High Street*") -
 COUNTIFS(Sheet2!$A$2:$A$500,"*Salton*", Sheet2!$A$2:$A$500,"*High Street*")

Para filtrar Sheet1 para obter os Smiths que vivem no código postal "NW12 1AB",

• Destaque o intervalo de dados A2: A500 e selecione Sort & Filter - > Filter na guia Home da faixa de opções,

• Escolha Text Filter - > Custom Filter no menu suspenso na parte superior da coluna.

• Em seguida, defina as caixas de entrada como Contains "Smith" e Contains "NW12 1AB".

Você está limitado a definir duas condições usando esse método.

Se você quiser filtrar a lista e tiver mais de duas condições (ou uma combinação de condições And e Or), realce o intervalo de dados e pressione Advanced na seção Sort & Filter da guia de dados.

Você será solicitado para o intervalo da lista e um intervalo de critérios.

Como o filtro Advanced cria uma cópia da lista filtrada, você também será solicitado a especificar onde deseja que a nova lista filtrada seja gravada.

Aqui está um exemplo de um intervalo de critérios que filtra a lista de todos os Smiths que vivem na "High Street":

Notequeusaro"* Smith *" como padrão para o sobrenome Smith pode não ser a melhor escolha: como um comentarista observa, ele combina com um sobrenome como Smithson (ou como Smith-Jones) e um endereço de rua como Caminho Smithsoniano.

Um padrão melhor seria "* Smith, *, *, *". Mesmo isso daria o que poderia ser considerado um falso positivo para o nome "Donald Smith Jr." (Esse nome também causaria problemas para uma conversão de texto em coluna usando a vírgula como um delimitador).

O fato de o Excel reconhecer apenas dois curingas, "*" e "?", sem semântica do tipo disponível com expressões regulares, significa que os resultados desse tipo de correspondência precisam ser inspecionados para casos especiais.

    

Answer 2

Caso você não queira adulterar seus dados existentes, você pode extrair os três campos em colunas auxiliares com fórmulas de string do Excel. Supondo que o nome completo e o endereço do seu cliente estejam na célula A1 , configure

• B1 - =FIND(",", A1)
• C1 - =TRIM(LEFT(A1, B1-1))
• D1 - =FIND(",", A1, B1+1)
• E1 - =TRIM(MID(A1, B1+1, D1-B1-1))
• F1 - =TRIM(RIGHT(A1, LEN(A1)-D1))

Em seguida, para os dados de exemplo que você forneceu, você obterá os seguintes resultados:

• B1 - 9
• C1 - Mr Smith
• D1 - 24
• E1 - 1 High Street
• F1 - London NW12 1AB

A função TRIM() remove espaços do início e do final de uma string. Sem essa função, se os dados originais tiverem espaços após as vírgulas, então E1 e F1 terão espaços no início, o que pode corromper a análise posterior. TRIM() também colapsará vários espaços em espaços únicos; por exemplo, se seus dados originais disserem Mr   Smith , então B1 ainda seria Mr Smith .

Se alguns valores de A1 tiverem menos ou mais do que duas vírgulas, isso pode ser manipulado com soluções mais complicadas.