Como classificar pelo sobrenome no Excel (Guia fácil)

Assistir ao vídeo - Como classificar por sobrenome no Excel

Se você trabalha com conjuntos de dados de nomes, classificá-los é uma das tarefas comuns que você teria que fazer com frequência.

É muito fácil classificar os dados em ordem alfabética com base no nome completo, onde o Excel usa o primeiro caractere do nome para classificar.

Mas e se você quiser classificar os dados pelo sobrenome no Excel?

Embora não seja tão simples, ainda pode ser feito (muito também depende da forma como os dados de nomes são estruturados).

Não importa o método usado, você terá que, de alguma forma, extrair o sobrenome do nome completo e colocá-lo em uma coluna separada. Você pode então usar esta coluna para classificar seus dados pelo sobrenome em ordem alfabética.

Neste tutorial do Excel, vou mostrar como classificar uma coluna com nomes com base no sobrenome.

Então vamos começar!

Extrair e classificar pelo sobrenome usando Localizar e substituir

A primeira etapa para classificar pelo sobrenome é colocar o sobrenome em uma coluna separada.

Você pode fazer isso substituindo tudo antes do sobrenome por um espaço em branco para que você tenha apenas o sobrenome restante.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja classificar esses dados em ordem alfabética usando o sobrenome.

Abaixo estão as etapas para classificar pelo sobrenome:

1. Selecione o conjunto de dados incluindo o cabeçalho (neste exemplo, seria A1: A10)
2. Copie-o na coluna adjacente (se a coluna adjacente não estiver vazia, insira uma nova coluna e copie esses nomes)
3. Renomeie o cabeçalho da coluna copiado. Neste exemplo, o nome é ‘Sobrenome’
4. Selecione todos os nomes copiados (não selecione o cabeçalho)
5. Segure a tecla Control e pressione a tecla H. Isso abrirá a caixa de diálogo Localizar e substituir.
6. No campo Localizar, digite * (símbolo de asterisco seguido por um caractere de espaço)
7. Deixe o campo Substituir por vazio
8. Clique em Substituir tudo. Isso substituirá instantaneamente todo o nome e você ficará apenas com os sobrenomes.

As etapas acima manteriam o sobrenome e removeriam tudo antes dele. Isso funciona bem mesmo quando você tem nomes do meio ou prefixos (como Sr. ou Sra.).

Depois de ter os sobrenomes na coluna adjacente, você pode facilmente classificar o conjunto de dados (incluindo os nomes completos) em ordem alfabética com base no sobrenome.

Abaixo estão as etapas para classificar pelo sobrenome:

1. Selecione todo o conjunto de dados com cabeçalhos (incluindo os nomes completos e os sobrenomes extraídos). Você também pode incluir outras colunas que deseja classificar junto com os nomes
2. Clique na guia Dados
3. Clique em Classificar
4. Na caixa de diálogo Classificar, certifique-se de que ‘Meus dados têm cabeçalhos’ esteja selecionado.
5. Na opção ‘Classificar por’, selecione o nome da coluna que contém apenas o sobrenome
6. Em ‘Sort On’, selecione ‘Cell Values’
7. Na opção Ordem, selecione ‘A a Z’
8. Clique OK

As etapas acima classificariam todo o conjunto de dados selecionado com base no sobrenome.

Uma vez feito isso, você pode excluir a coluna com o sobrenome.

Dica Profissional: A qualquer momento, se você achar que pode precisar dos dados originais de volta, é necessário encontrar uma maneira de desfazer a classificação deste conjunto de dados. Para fazer isso, em uma coluna adjacente (esquerda ou direita), tenha números de série antes da classificação. Agora, se você precisar dos dados originais de volta, poderá obtê-los classificando com base nos números.

Extraia e coloque em ordem alfabética pelo sobrenome usando a fórmula

Embora o método mostrado acima (usando Find and Replace) seja o que eu prefiro para obter todos os sobrenomes e classificar com base nele, uma limitação dele é que os dados resultantes são estáticos.

Isso significa que se eu adicionar mais nomes à minha lista, terei que fazer o mesmo processo novamente para obter os sobrenomes.

Se isso é algo que você não quer, você pode usar o método de fórmula para classificar os dados por sobrenomes.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo.

Abaixo está a fórmula que extrairá o sobrenome do nome completo:

= DIREITO (A2, LEN (A2) -FIND ("", A2))

A fórmula acima se baseia no padrão com um nome completo (que contém apenas o nome e o sobrenome neste exemplo). O padrão é que haveria 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 nome para obter o número total de caracteres do sobrenome.

Este valor é então usado na função RIGHT para obter o sobrenome.

Depois de ter a coluna de sobrenome, você pode classificar esses dados (isso é abordado no primeiro método em detalhes).

A fórmula acima funcionaria quando você tiver apenas nomes e sobrenomes.

Mas e se você também tiver um nome do meio. Ou pode haver uma saudação antes do nome (como Sr. ou Sra.)

Nesse caso, você precisa usar a fórmula abaixo:

= 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.

Eu recomendo que você use a segunda fórmula em todos os casos, e é mais infalível e pode lidar com todos os casos (desde que o sobrenome esteja no final do 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. No caso de haver espaços duplos, ou espaços iniciais / finais, esta fórmula dará resultados incorretos. Nesse caso, é melhor usar a função TRIM para primeiro se livrar de quaisquer espaços à esquerda, à direita e duplos e, em seguida, usar a fórmula acima.

Embora possa parecer um método complicado, a vantagem de usar uma fórmula é que ela torna os resultados dinâmicos. Se você adicionar mais nomes à sua lista, tudo que você precisa fazer é copiar a fórmula e ela lhe dará o sobrenome.

Usando texto para colunas

Texto para colunas é novamente uma maneira simples e fácil de dividir células no Excel.

Você pode especificar o delimitador (como vírgula ou espaço) e usá-lo para dividir o conteúdo da célula. Depois de dividir os elementos em colunas separadas, você pode usar a coluna com o sobrenome para colocar os dados em ordem alfabética.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Abaixo estão as etapas para usar Texto para coluna para classificar pelo sobrenome:

1. Selecione a coluna que tem o nome (excluindo o cabeçalho)
2. Clique na guia Dados
3. No grupo ‘Ferramentas de dados’, clique na opção Texto para colunas. Isso abrirá o assistente de Texto para Colunas
4. Na Etapa 1 do ‘Assistente de conversão de texto em colunas’, selecione ‘Delimitado’ e clique em Avançar
5. Na Etapa 2, selecione ‘Espaço’ como o Delimitador (e desmarque tudo o mais, se selecionado) e, em seguida, clique no botão Avançar.
6. Na Etapa 3, selecione a primeira coluna de nome na visualização de dados e, em seguida, selecione a opção ‘Não importar colunas (pular)’. Isso garante que o nome não faça parte do resultado e você obtenha apenas o sobrenome.
7. Também na Etapa 3, altere a célula de destino para aquela que está adjacente aos dados originais. Isso garantirá que você obtenha o sobrenome separadamente e os dados dos nomes originais estejam intactos.
8. Clique em Terminar

Assim que tiver o resultado, você pode classificar pelo sobrenome.

Você também pode enviar texto para colunas para separar nomes e sobrenomes quando tiver uma vírgula como separador.

Usando Flash Fill

Outra maneira rápida e rápida de obter os sobrenomes é usando o recurso Flash Fill.

O Flash Fill foi introduzido no Excel 2013 e ajuda a manipular os dados identificando padrões. Para que isso funcione, você precisa mostrar o resultado do Flash Fill algumas vezes.

Depois de identificar o padrão, ele fará rapidamente o resto do trabalho para você.

Suponha que você tenha o conjunto de dados de nomes abaixo.

Abaixo estão as etapas para usar o Flash Fill para obter o sobrenome e, em seguida, classificar usando-o:

1. Na célula B2, digite o texto ‘Maury’. Este é o resultado que você espera na célula.
2. Vá para a próxima célula e digite o sobrenome do nome na célula adjacente (Elliot neste exemplo).
3. Selecione ambas as células
4. Passe o cursor sobre a parte inferior direita da seleção. Você notará que o cursor muda para um ícone de mais.
5. Clique duas vezes nele (ou clique e arraste para baixo). Isso lhe dará algum resultado nas células (provavelmente não será o resultado que você deseja)
6. Clique no ícone Opções de preenchimento automático.
7. Clique em Flash Fill

Isso lhe dará o resultado que provavelmente serão os sobrenomes em todas as células.

Eu digo provável, pois o Flash Fill pode não funcionar em alguns casos. Como depende da identificação de um padrão, pode não ser capaz de fazer isso sempre. Ou, às vezes, o padrão que decifra pode não ser o correto.

Nesses casos, você deve inserir um resultado esperado em uma ou duas células a mais e, em seguida, executar as etapas 4 a 7.

Depois de ter todos os sobrenomes em uma coluna, você pode classificar os dados com base nesses sobrenomes.

Portanto, essas são quatro maneiras diferentes que você pode usar para classificar os dados pelo sobrenome. O melhor método seria usar a técnica Localizar e Substituir, mas se você quiser tornar seus resultados dinâmicos, o método da fórmula é o caminho a percorrer.

Espero que você tenha achado este tutorial útil.