Se você trabalha com um conjunto de dados de nomes, classificá-lo é uma das tarefas comuns que você deve executar com frequência.
É fácil classificar os dados em ordem alfabética pelo nome completo, onde o Excel usa o primeiro caractere do nome para classificar.
No entanto, se você quiserClassificar dados por sobrenome no ExcelcondutaClassificarComo fazer?
Embora não seja tão simples, ainda pode ser feito (também depende muito de como os dados do nome são estruturados).
Qualquer que seja o método usado, você precisa extrair de alguma forma o sobrenome do nome completo e colocá-lo em uma coluna separada.Você pode usar essa coluna para classificar os dados em ordem alfabética por sobrenome.
Neste tutorial do Excel, mostrarei como classificar uma coluna com nomes com base no sobrenome.
Então vamos começar!
Conteúdo
Extraia e classifique por sobrenome usando Localizar e substituir
O primeiro passo para classificar por sobrenome é obter o sobrenome em uma coluna separada.
Você pode fazer isso substituindo tudo antes do sobrenome por espaços em branco, para que fique apenas com o sobrenome.
Suponha que você tenha um conjunto de dados como o abaixo e queira classificar esses dados em ordem alfabética pelo sobrenome.
Aqui estão as etapas para classificar por sobrenome:
- Selecione o conjunto de dados que contém o título (neste caso será A1:A10)
- copie-o na coluna adjacente (se a coluna adjacente não estiver vazia, insira uma nova coluna e copie os nomes)
- Renomeie os cabeçalhos de coluna copiados.Neste exemplo, vou chamá-lo de "Sobrenome"
- Selecione todos os nomes copiados (não selecione títulos)
- Mantenha pressionada a tecla Control e, em seguida, pressione a tecla H.Isso abrirá a caixa de diálogo Localizar e substituir.
- No campo Localizar, digite * (um asterisco seguido por um caractere de espaço)
- Deixe o campo Substituir por em branco
- Clique em Substituir tudo.Isso substituirá todos os nomes de uma vez e você ficará apenas com os sobrenomes.
As etapas acima manterão o sobrenome e excluirão tudo antes dele.Isso funciona mesmo se você tiver um nome do meio ou prefixo, como Sr. ou Sra.
Depois de ter sobrenomes em colunas adjacentes, você pode classificar facilmente o conjunto de dados (incluindo nomes completos) em ordem alfabética por sobrenome.
Aqui estão as etapas para classificar por sobrenome:
- Selecione todo o conjunto de dados com títulos (incluindo nomes completos e sobrenomes extraídos).Você também pode incluir outras colunas para classificar com o nome
- Clique na guia Dados
- Clique para classificar
- Na caixa de diálogo Classificar, verifique se Meus dados têm cabeçalhos está marcado.
- Na opção "Classificar por", selecione o nome da coluna apenas com o sobrenome
- Em Classificar por, selecione Valor da célula
- Na opção "Ordem", selecione "A a Z"
- Clique OK
As etapas acima classificarão todo o conjunto de dados selecionado com base no sobrenome.
Uma vez feito, você pode descartar a coluna com o sobrenome.
Dicas profissionais: sempre que você achar que pode precisar voltar aos dados originais, você precisa ter uma maneira de desclassificar esse conjunto de dados.Para isso, nas colunas adjacentes (esquerda ou direita), há números de sequência antes da ordenação.Agora, se você precisar recuperar os dados originais, poderá obtê-los classificando com base em números.
Extraia e classifique em ordem alfabética por sobrenome usando a fórmula
Embora o método mostrado acima (usando localizar e substituir) seja o que eu prefiro para obter todos os sobrenomes e classificar com base nele, uma de suas limitações é que os dados resultantes são estáticos.
Isso significa que, se eu adicionar mais nomes à lista, terei que passar pelo mesmo processo novamente para obter o sobrenome.
Se isso não for o que você deseja, você pode usar um método de fórmula para classificar os dados pelo sobrenome.
Suponha que você tenha o conjunto de dados mostrado abaixo.
Aqui está a fórmula para extrair o sobrenome do nome completo:
= DIREITO (A2, LEN (A2) -FIND ("", A2))
A fórmula acima depende de um padrão com nomes completos (neste exemplo, apenas nomes e sobrenomes).O padrão é que haverá um caractere de espaço entre o nome e o sobrenome.
A função FIND é usada para obter a posição do caractere de espaço.Esse valor é então subtraído do comprimento total do primeiro nome para obter o número total de caracteres no sobrenome.
Em seguida, use esse valor na função DIREITA para obter o sobrenome.
Depois de ter a coluna de sobrenome, você pode classificar esses dados (isso é detalhado no primeiro método).
A fórmula acima funcionará quando você tiver apenas nome e sobrenome.
Mas e se você também tiver um nome do meio.ou pode ter uma saudação antes do nome (por exemplo, Sr. ou Sra.)
Neste caso, você precisa usar a seguinte fórmula:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
A fórmula acima encontra a posição do último caractere de espaço e o usa para extrair o sobrenome.
Sugiro que você use a segunda fórmula em todos os casos, é mais simples e trata de todos os casos (desde que o sobrenome esteja no final do primeiro nome).
Observação: essas duas fórmulas dependem da condição de que haja apenas um caractere de espaço entre cada elemento de nome.Esta fórmula dará resultados incorretos se houver espaços duplos ou espaços à esquerda/à direita.Nesse caso, é melhor usar a função TRIM para remover primeiro os espaços iniciais, finais e duplos e, em seguida, usar a fórmula acima.
Embora isso possa parecer uma abordagem complicada, o benefício de usar uma fórmula é que ela torna os resultados dinâmicos.Se você adicionar mais nomes à lista, tudo o que você precisa fazer é copiar a fórmula e ela lhe dará o sobrenome.
Usar texto para colunas
Texto para colunas é outra maneira fácil de dividir células no Excel.
Você pode especificar um delimitador (como uma vírgula ou espaço) e usá-lo para dividir o conteúdo de uma célula.Depois de dividir os elementos em colunas separadas, você pode usar a coluna com sobrenomes para colocar os dados em ordem alfabética.
Suponha que você tenha um conjunto de dados parecido com este:
Aqui estão as etapas para classificar por sobrenome usando Text to Column:
- Selecione colunas com nomes (excluindo cabeçalhos)
- Clique na guia Dados
- No grupo Ferramentas de Dados, clique na opção Texto para Colunas.Isso abrirá o assistente de Texto para Colunas
- Na Etapa 1 do Assistente para Converter Texto em Colunas, selecione Delimitado e clique em Avançar
- Na etapa 2, selecione "Espaço" como delimitador (desmarque qualquer outra coisa se estiver marcada) e clique no botão Avançar.
- Na etapa 3, selecione a coluna de nome na visualização de dados e selecione a opção "Não importar coluna (pular)".Isso garante que o primeiro nome não faça parte do resultado, você obtém apenas o sobrenome.
- Também na etapa 3, altere a célula de destino para a célula adjacente aos dados originais.Isso garantirá que você obtenha apenas o sobrenome e que os dados do nome original estejam completos.
- Clique em Concluir
Depois de obter os resultados, você pode classificar por sobrenome.
Você também pode usar texto em coluna para separar nomes e sobrenomes quando tiver vírgulas como delimitadores.
Preencha com glitter
Outra maneira rápida de obter o sobrenome é usar o recurso Flash Fill.
Introduzido no Excel 2013, o Flash Fill ajuda a manipular dados identificando padrões.Para fazer isso, você precisa exibir o Flash Fill várias vezes para obter o resultado esperado.
Uma vez que ele reconhece o padrão, ele rapidamente faz o resto para você.
Suponha que você tenha o seguinte conjunto de dados de nomes.
Aqui estão as etapas para obter o sobrenome usando o Flash Fill e usá-lo para classificar:
- Na célula B2, digite o texto "Maury".Isso é o que você esperaria em uma célula.
- Vá para a próxima célula e digite o sobrenome do nome na célula adjacente (Elliot neste caso).
- selecione duas células
- Passe o cursor sobre a parte inferior direita da seleção.Você notará que o cursor muda para um ícone de mais.
- Clique duas vezes nele (ou clique e arraste para baixo).Isso lhe dará algum resultado na célula (improvável o que você deseja)
- Clique no ícone Opções de preenchimento automático.
- Clique em Preenchimento Rápido
Isso lhe dará um resultado que pode ser o sobrenome em todas as células.
Digo talvez porque o Flash Fill pode não funcionar em alguns casos.Como depende do modo de reconhecimento, nem sempre pode fazer isso.Ou, às vezes, o padrão que decifra pode não ser o padrão correto.
Nesse caso, você deve inserir o resultado esperado em uma ou mais células e, em seguida, executar as etapas 4-7.
Depois de ter todos os sobrenomes na coluna, você pode classificar os dados com base nesses sobrenomes.
Portanto, você pode classificar os dados por sobrenome de quatro maneiras diferentes.A melhor maneira é usar técnicas de localizar e substituir, mas se você quiser tornar seus resultados dinâmicos, os métodos de fórmula são o caminho a seguir.
Espero que você tenha achado este tutorial útil.
