Si vous travaillez avec un ensemble de données de noms, le trier est l'une des tâches courantes que vous devez effectuer fréquemment.
Il est facile de trier les données par ordre alphabétique par nom complet, où Excel utilise le premier caractère du nom pour trier.
Cependant, si vous souhaitezTrier les données par nom de famille dans ExcelmonterTrierComment faire?
Bien que ce ne soit pas si simple, cela peut toujours être fait (cela dépend aussi beaucoup de la façon dont les données de nom sont structurées).
Quelle que soit la méthode que vous utilisez, vous devez en quelque sorte extraire le nom de famille du nom complet et le mettre dans une colonne séparée.Vous pouvez ensuite utiliser cette colonne pour trier les données par ordre alphabétique du nom de famille.
Dans ce tutoriel Excel, je vais vous montrer comment trier une colonne avec des noms basés sur le nom de famille.
Alors, commençons!
Table des matières
Extraire et trier par nom de famille à l'aide de Rechercher et remplacer
La première étape du tri par nom de famille consiste à obtenir le nom de famille dans une colonne distincte.
Vous pouvez le faire en remplaçant tout ce qui précède le nom de famille par des blancs, de sorte qu'il ne vous reste plus que le nom de famille.
Supposons que vous disposiez d'un ensemble de données comme celui ci-dessous et que vous souhaitiez trier ces données par ordre alphabétique par nom de famille.
Voici les étapes pour trier par nom de famille :
- Sélectionnez le jeu de données qui contient le titre (dans ce cas ce sera A1:A10)
- copiez-le dans la colonne adjacente (si la colonne adjacente n'est pas vide, insérez une nouvelle colonne, puis copiez les noms)
- Renommez les en-têtes de colonne copiés.Dans cet exemple, je vais le nommer "Last Name"
- Sélectionnez tous les noms copiés (ne sélectionnez pas les titres)
- Maintenez la touche Contrôle enfoncée, puis appuyez sur la touche H.Cela ouvrira la boîte de dialogue Rechercher et remplacer.
- Dans le champ Rechercher, saisissez * (un astérisque suivi d'un espace)
- Laissez le champ Remplacer par vide
- Cliquez sur Remplacer tout.Cela remplacera tous les prénoms à la fois et il ne vous restera que les noms de famille.
Les étapes ci-dessus conserveront le nom de famille et supprimeront tout ce qui le précède.Cela fonctionne même si vous avez un deuxième prénom ou un préfixe tel que M. ou Mme.
Une fois que vous avez des noms de famille dans des colonnes adjacentes, vous pouvez facilement trier l'ensemble de données (y compris les noms complets) par ordre alphabétique de nom de famille.
Voici les étapes pour trier par nom de famille :
- Sélectionnez l'ensemble de données complet avec les titres (y compris les noms complets et les noms de famille extraits).Vous pouvez également inclure d'autres colonnes à trier avec le nom
- Cliquez sur l'onglet Données
- Cliquez pour trier
- Dans la boîte de dialogue Trier, assurez-vous que Mes données ont des en-têtes est cochée.
- Dans l'option "Trier par", sélectionnez le nom de la colonne avec uniquement le nom de famille
- Dans Trier par, sélectionnez Valeur de la cellule
- Dans l'option "Commande", sélectionnez "A à Z"
- Cliquez sur OK
Les étapes ci-dessus trieront l'ensemble de données sélectionné en fonction du nom de famille.
Une fois cela fait, vous pouvez supprimer la colonne avec le nom de famille.
Conseils de pro: À tout moment où vous pensez avoir besoin de revenir aux données d'origine, vous devez disposer d'un moyen de détrier cet ensemble de données.Pour cela, dans les colonnes adjacentes (gauche ou droite), il y a des numéros d'ordre avant le tri.Maintenant, si vous avez besoin de récupérer les données d'origine, vous pouvez les obtenir en triant en fonction des nombres.
Extraire et trier par ordre alphabétique par nom de famille à l'aide de la formule
Bien que la méthode présentée ci-dessus (en utilisant la recherche et le remplacement) soit ce que je préfère pour obtenir tous les noms de famille et les trier en fonction de ceux-ci, l'une de ses limites est que les données résultantes sont statiques.
Cela signifie que si j'ajoute d'autres prénoms à la liste, je devrai répéter le même processus pour obtenir le nom de famille.
Si ce n'est pas ce que vous voulez, vous pouvez utiliser une méthode de formule pour trier les données par nom de famille.
Supposons que vous disposiez du jeu de données ci-dessous.
Voici la formule pour extraire le nom de famille du nom complet :
= DROITE (A2, LEN (A2) -FIND ("", A2))
La formule ci-dessus repose sur un modèle avec des noms complets (dans cet exemple uniquement les noms et prénoms).Le modèle est qu'il y aura un espace entre le prénom et le nom de famille.
La fonction FIND est utilisée pour obtenir la position du caractère espace.Cette valeur est ensuite soustraite de la longueur totale du prénom pour obtenir le nombre total de caractères du nom de famille.
Utilisez ensuite cette valeur dans la fonction DROITE pour obtenir le nom de famille.
Une fois que vous avez la colonne du nom de famille, vous pouvez trier ces données (ceci est détaillé dans la première méthode).
La formule ci-dessus fonctionnera si vous n'avez que le nom et le prénom.
Mais que se passe-t-il si vous avez aussi un deuxième prénom.ou peut avoir une salutation avant le nom (par exemple, M. ou Mme)
Dans ce cas, vous devez utiliser la formule suivante :
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
La formule ci-dessus trouve la position du dernier espace et l'utilise ensuite pour extraire le nom de famille.
Je vous propose d'utiliser la deuxième formule dans tous les cas, c'est plus simple et gère tous les cas (tant que le nom est à la fin du prénom).
Remarque : Ces deux formules reposent sur la condition qu'il n'y ait qu'un seul espace entre chaque élément de nom.Cette formule donnera des résultats incorrects s'il y a des espaces doubles ou des espaces de début/fin.Dans ce cas, il est préférable d'utiliser la fonction TRIM pour supprimer d'abord les espaces de début, de fin et doubles, puis d'utiliser la formule ci-dessus.
Bien que cela puisse sembler une approche compliquée, l'avantage d'utiliser une formule est qu'elle rend les résultats dynamiques.Si vous ajoutez plus de noms à la liste, tout ce que vous avez à faire est de copier la formule et cela vous donnera le nom de famille.
Utiliser le texte dans les colonnes
Text to Columns est un autre moyen simple de diviser des cellules dans Excel.
Vous pouvez spécifier un délimiteur (comme une virgule ou un espace) et l'utiliser pour diviser le contenu d'une cellule.Après avoir divisé les éléments en colonnes séparées, vous pouvez utiliser la colonne avec les noms de famille pour classer les données par ordre alphabétique.
Supposons que vous ayez un ensemble de données qui ressemble à ceci :
Voici les étapes pour trier par nom de famille à l'aide de Text to Column :
- Sélectionner les colonnes avec des noms (hors en-têtes)
- Cliquez sur l'onglet Données
- Dans le groupe Outils de données, cliquez sur l'option Texte en colonnes.Cela ouvrira l'assistant Text to Columns
- À l'étape 1 de l'assistant de conversion de texte en colonnes, sélectionnez Délimité et cliquez sur Suivant
- À l'étape 2, sélectionnez "Espace" comme délimiteur (décochez toute autre option si elle est cochée) et cliquez sur le bouton Suivant.
- À l'étape 3, sélectionnez la colonne du prénom dans l'aperçu des données, puis sélectionnez l'option "Ne pas importer la colonne (ignorer)".Cela garantit que le prénom ne fait pas partie du résultat, vous n'obtenez que le nom de famille.
- Également à l'étape 3, remplacez la cellule cible par la cellule adjacente aux données d'origine.Cela garantira que vous n'obtenez que le nom de famille et que les données de nom d'origine sont complètes.
- Cliquez sur Terminer
Une fois que vous avez les résultats, vous pouvez trier par nom de famille.
Vous pouvez également utiliser le texte en colonne pour séparer les noms et prénoms lorsque vous avez des virgules comme délimiteurs.
Remplir de paillettes
Un autre moyen rapide d'obtenir le nom de famille consiste à utiliser la fonction Flash Fill.
Introduit dans Excel 2013, Flash Fill aide à manipuler les données en identifiant des modèles.Pour ce faire, vous devez afficher Flash Fill plusieurs fois pour obtenir le résultat attendu.
Une fois qu'il reconnaît le motif, il fait rapidement le reste pour vous.
Supposons que vous disposiez de l'ensemble de données de noms suivant.
Voici les étapes pour obtenir le nom de famille à l'aide de Flash Fill et l'utiliser pour trier :
- Dans la cellule B2, saisissez le texte "Maury".C'est ce à quoi on s'attendrait dans une cellule.
- Allez à la cellule suivante et entrez le nom de famille du nom dans la cellule adjacente (Elliot dans ce cas).
- sélectionner deux cellules
- Passez le curseur sur la partie inférieure droite de la sélection.Vous remarquerez que le curseur se transforme en icône plus.
- Double-cliquez dessus (ou cliquez et faites glisser vers le bas).Cela vous donnera un résultat dans la cellule (peu probable celui que vous voulez)
- Cliquez sur l'icône Options de remplissage automatique.
- Cliquez sur Remplissage rapide
Cela vous donnera un résultat qui peut être le nom de famille dans toutes les cellules.
Je dis peut-être parce que Flash Fill peut ne pas fonctionner dans certains cas.Comme cela dépend du mode de reconnaissance, cela peut ne pas toujours être le cas.Ou parfois, le modèle qu'il déchiffre peut ne pas être le bon modèle.
Dans ce cas, vous devez saisir le résultat attendu dans une ou plusieurs cellules, puis effectuer les étapes 4 à 7.
Une fois que vous avez tous les noms de famille dans la colonne, vous pouvez trier les données en fonction de ces noms de famille.
Par conséquent, vous pouvez trier les données par nom de famille de quatre manières différentes.Le meilleur moyen est d'utiliser des techniques de recherche et de remplacement, mais si vous souhaitez rendre vos résultats dynamiques, les méthodes de formule sont la solution.
J'espère que vous avez trouvé ce tutoriel utile.
