Regarder la vidéo - Comment trier par nom de famille dans Excel
Si vous travaillez avec des ensembles de données de noms, le tri est l'une des tâches courantes que vous auriez à faire souvent.
Il est assez facile de trier les données par ordre alphabétique en fonction du nom complet, où Excel utilise le premier caractère du nom à trier.
Mais que faire si vous voulez trier les données par le nom de famille dans Excel?
Bien que ce ne soit pas aussi simple, cela peut toujours être fait (beaucoup dépend aussi de la façon dont les données de noms sont structurées).
Quelle que soit la méthode que vous utilisez, vous devrez, d'une manière ou d'une autre, 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 vos données par nom de famille par ordre alphabétique.
Dans ce didacticiel Excel, je vais vous montrer comment trier une colonne avec des noms en fonction du nom de famille.
Alors, commençons!
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 séparée.
Vous pouvez le faire en remplaçant tout ce qui précède le nom de famille par un espace afin qu'il ne vous reste que le nom de famille.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez trier ces données par ordre alphabétique en utilisant le nom de famille.
Voici les étapes pour trier par nom de famille :
- Sélectionnez l'ensemble de données comprenant l'en-tête (dans cet exemple, ce serait A1:A10)
- Copiez-le dans la colonne adjacente (si la colonne adjacente n'est pas vide, insérez une nouvelle colonne puis copiez ces noms)
- Renommez l'en-tête de colonne copié. Dans cet exemple, je vais nommer est « Nom »
- Sélectionnez tous les noms copiés (ne sélectionnez pas l'en-tête)
- Maintenez la touche Ctrl enfoncée, puis appuyez sur la touche H. Cela ouvrira la boîte de dialogue Rechercher et remplacer.
- Dans le champ Rechercher, saisissez * (symbole astérisque suivi d'un espace)
- Laissez le champ Remplacer par vide
- Cliquez sur Remplacer tout. Cela remplacerait instantanément tous les prénoms et il ne vous restera que les noms de famille.
Les étapes ci-dessus conserveraient le nom de famille et supprimeraient tout ce qui le précède. Cela fonctionne bien même lorsque vous avez des prénoms ou des préfixes (comme M. ou Mme).
Une fois que vous avez les noms de famille dans la colonne adjacente, vous pouvez facilement trier l'ensemble de données (y compris les noms complets) par ordre alphabétique en fonction du nom de famille.
Voici les étapes pour trier par nom de famille :
- Sélectionnez l'ensemble de données complet avec les en-têtes (y compris les noms complets et les noms de famille extraits). Vous pouvez également inclure d'autres colonnes que vous souhaitez trier avec les noms
- Cliquez sur l'onglet Données
- Cliquez sur Trier
- Dans la boîte de dialogue Trier, assurez-vous que « Mes données ont des en-têtes » est sélectionné.
- Dans l'option 'Trier par', sélectionnez le nom de la colonne qui a juste le nom de famille
- Dans le "Trier sur", sélectionnez "Valeurs de cellule"
- Dans l'option Commande, sélectionnez « A à Z »
- Cliquez sur OK
Les étapes ci-dessus trieraient l'ensemble de données sélectionné en fonction du nom de famille.
Une fois cela fait, vous pouvez supprimer la colonne qui porte le nom de famille.
Conseil de pro: À tout moment, si vous pensez avoir besoin de récupérer les données d'origine, vous devez disposer d'un moyen de dé-trier cet ensemble de données. Pour ce faire, dans une colonne adjacente (gauche ou droite), disposer des numéros de série avant le tri. Maintenant, si vous avez besoin de récupérer les données d'origine, vous les récupérez en les triant en fonction des nombres.Extraire et alphabétiser par nom de famille à l'aide d'une formule
Bien que la méthode indiquée ci-dessus (à l'aide de Rechercher et remplacer) soit celle que je préfère pour obtenir tous les noms de famille et les trier en fonction de celle-ci, l'une de ses limitations est que les données résultantes sont statiques.
Cela signifie que si j'ajoute plus de noms à ma liste, je devrai refaire le même processus pour obtenir les noms de famille.
Si c'est quelque chose que vous ne voulez pas, vous pouvez utiliser la méthode de la formule pour trier les données par nom de famille.
Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous.
Vous trouverez ci-dessous la formule qui extraira le nom de famille du nom complet :
=DROITE(A2,LEN(A2)-TROUVER(" ",A2))
La formule ci-dessus repose sur le modèle avec un nom complet (qui ne contient que le prénom et le nom dans cet exemple). Le modèle est qu'il y aurait un caractère d'espace entre le prénom et le nom.
La fonction FIND est utilisée pour obtenir la position du caractère espace. Cette valeur est ensuite soustraite de la longueur totale du nom pour obtenir le nombre total de caractères dans le nom de famille.
Cette valeur est ensuite utilisée dans la fonction RIGHT 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 couvert en détail dans la première méthode).
La formule ci-dessus fonctionnerait lorsque vous n'avez que le prénom et le nom de famille.
Mais que faire si vous avez aussi un deuxième prénom. Ou peut-il y avoir une salutation avant le nom (comme M. ou Mme.)
Dans un tel cas, vous devez utiliser la formule ci-dessous :
=DROITE(A2,LEN(A2)-TROUVER("@",REMPLACER(A2," ","@",LEN(A2)-LEN(REMPLACER(A2," ","")))))
La formule ci-dessus trouve la position du dernier caractère espace, puis l'utilise pour extraire le nom de famille.
Je vous recommande d'utiliser la deuxième formule dans tous les cas, et elle est plus infaillible et peut gérer tous les cas (tant que le nom de famille est à la fin du nom).
Remarque : Ces deux formules reposent sur la condition qu'il n'y a qu'un seul caractère d'espace entre chaque élément de nom. S'il y a des espaces doubles ou des espaces de début/de fin, cette formule donnera des résultats incorrects. Dans un tel cas, il est préférable d'utiliser la fonction TRIM pour éliminer d'abord les espaces de début, de fin et doubles, puis d'utiliser la formule ci-dessus.
Bien que cela puisse sembler une méthode compliquée, l'avantage d'utiliser une formule est qu'elle rend les résultats dynamiques. Si vous ajoutez plus de noms à votre liste, tout ce que vous avez à faire est de copier la formule et elle vous donnera le nom de famille.
Utiliser du texte dans les colonnes
Text to Columns est encore une fois un moyen simple et facile de diviser des cellules dans Excel.
Vous pouvez spécifier le délimiteur (comme une virgule ou un espace) et l'utiliser pour diviser le contenu de la cellule. Une fois que vous avez divisé les éléments dans des colonnes séparées, vous pouvez utiliser la colonne portant le nom de famille pour classer les données par ordre alphabétique.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Vous trouverez ci-dessous les étapes à suivre pour utiliser Text to Column pour trier par nom de famille :
- Sélectionnez la colonne qui porte le nom (hors en-tête)
- Cliquez sur l'onglet Données
- Dans le groupe « Outils de données », cliquez sur l'option Texte en colonnes. Cela ouvrira l'assistant Texte en colonnes
- À l'étape 1 de l'assistant « Convertir le texte en colonnes », sélectionnez « Délimité » et cliquez sur Suivant
- À l'étape 2, sélectionnez « Espace » comme délimiteur (et décochez tout autre élément s'il est sélectionné), puis 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 les colonnes (ignorer) ». Cela garantit que le prénom ne fait pas partie du résultat et que vous n'obtenez que le nom de famille.
- Également à l'étape 3, remplacez la cellule de destination par celle qui est adjacente aux données d'origine. Cela garantira que vous obtenez le nom de famille séparément et que les données des noms d'origine sont intactes.
- Cliquez sur Terminer
Une fois que vous avez le résultat, vous pouvez trier par nom de famille.
Vous pouvez également Text to Columns pour séparer le prénom et le nom lorsque vous avez une virgule comme séparateur.
Utilisation du remplissage flash
Un autre moyen rapide et rapide d'obtenir les noms de famille consiste à utiliser la fonction Flash Fill.
Flash Fill a été introduit dans Excel 2013 et permet de manipuler les données en identifiant des modèles. Pour que cela fonctionne, vous devez montrer à Flash Fill le résultat attendu plusieurs fois.
Une fois qu'il aura identifié le motif, il fera rapidement le reste du travail pour vous.
Supposons que vous ayez l'ensemble de données de noms ci-dessous.
Vous trouverez ci-dessous les étapes à suivre pour utiliser Flash Fill pour obtenir le nom de famille, puis trier en l'utilisant :
- Dans la cellule B2, saisissez le texte « Maury ». C'est le résultat que vous attendez dans la cellule.
- Allez à la cellule suivante et entrez le nom de famille pour le nom dans la cellule adjacente (Elliot dans cet exemple).
- Sélectionnez les 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-le glisser vers le bas). Cela vous donnera un résultat dans les cellules (ce ne sera probablement pas le résultat souhaité)
- Cliquez sur l'icône Options de remplissage automatique.
- Cliquez sur Remplissage Flash
Cela vous donnera le résultat qui sera probablement les noms de famille dans toutes les cellules.
Je dis probablement, car Flash Fill peut ne pas fonctionner dans certains cas. Comme cela dépend de l'identification d'un modèle, il se peut qu'il ne puisse pas toujours le faire. Ou parfois, le motif qu'il déchiffre peut ne pas être le bon.
Dans de tels cas, vous devez entrer un résultat attendu dans une ou deux cellules supplémentaires, puis effectuer les étapes 4 à 7.
Une fois que vous avez tous les noms de famille dans une colonne, vous pouvez trier les données en fonction de ces noms de famille.
Ce sont donc quatre façons différentes que vous pouvez utiliser pour trier les données par nom de famille. La meilleure méthode serait d'utiliser la technique Rechercher et remplacer, mais si vous voulez rendre vos résultats dynamiques, la méthode de la formule est la voie à suivre.
J'espère que vous avez trouvé ce tutoriel utile.
