Comment trier des adresses par nom de rue/numéro dans Excel ?
Lorsque vous gérez une liste d'adresses dans Excel, il est souvent nécessaire d'organiser ou d'analyser les données en triant les adresses soit par nom de rue, soit par numéro de rue. Par exemple, si vous devez regrouper des clients vivant sur la même rue, ou si vous devez traiter des livraisons dans l'ordre des numéros de maison, le tri par ces composants est essentiel. Cependant, comme les formats d'adresse typiques mélangent les noms de rue et les numéros dans une seule cellule, un tri simple ne donnera pas les résultats attendus. Dans cet article, nous discuterons de méthodes pratiques pour trier des adresses par nom de rue ou numéro de rue dans Excel, analyserons leurs avantages et scénarios d'application, et proposerons des solutions de dépannage et des alternatives pour divers besoins des utilisateurs.
Trier les adresses par nom de rue avec une colonne d'aide dans Excel
Trier les adresses par numéro de rue avec une colonne d'aide dans Excel
Trier les adresses à l'aide de VBA pour extraire et trier automatiquement par nom de rue ou numéro
Trier les adresses par nom de rue ou numéro avec Power Query (sans colonnes d'aide)
Trier les adresses par nom de rue avec une colonne d'aide dans Excel
Pour trier des adresses par nom de rue dans Excel, vous devrez d'abord extraire uniquement les noms de rue dans une colonne d'aide. Cette approche est simple et fonctionne bien lorsque le format d'adresse est cohérent, tel que "123 Rue Apple". Elle convient aux projets rapides ou aux listes d'adresses simples.
1. Sélectionnez une colonne vide adjacente à votre liste d'adresses. Entrez la formule suivante dans la première cellule de la colonne d'aide pour extraire le nom de la rue :
=MID(A1,FIND(" ",A1)+1,255)(Ici, A1 fait référence à la cellule supérieure de vos données d'adresse - ajustez si vos données commencent ailleurs.)
Après avoir tapé la formule, appuyez sur Entrée puis faites glisser la poignée de recopie vers le bas pour appliquer la formule à toutes les lignes de votre plage d'adresses. Cette formule fonctionne en trouvant le premier espace dans chaque adresse, puis en renvoyant tout ce qui suit cet espace - le nom de la rue et tout suffixe. Assurez-vous que vos adresses suivent la même structure ; sinon, la formule peut ne pas se diviser comme prévu.
2. Mettez en surbrillance toute la colonne d'aide (la colonne avec les noms de rue extraits), puis allez à l'onglet Données et cliquez sur Trier de A à Z. Cela triera les noms de rue dans l'ordre croissant (alphabétique).
3. Dans la boîte de dialogue Avertissement de tri qui apparaît, sélectionnez Développer la sélection pour vous assurer que les informations complètes de l'adresse restent ensemble lors du tri.
4. Cliquez sur Trier. Votre liste d'adresses sera maintenant réorganisée en fonction des noms de rue, regroupant les rues similaires ensemble.
Remarque : Cette méthode fonctionne mieux avec des formats d'adresse standardisés. Si vos cellules d'adresse contiennent des motifs irréguliers ou plusieurs espaces avant le nom de rue, la formule peut nécessiter des ajustements. Vérifiez toujours quelques résultats pour leur exactitude après avoir utilisé la formule.
Avantages : Simple, ne nécessite pas d'outils supplémentaires.
Inconvénients : Dépend d'un formatage cohérent ; un travail supplémentaire est requis si le format d'adresse varie.
Trier les adresses par numéro de rue avec une colonne d'aide dans Excel
Si vous avez besoin de trier une liste d'adresses par le numéro de rue numérique - par exemple, pour attribuer un ordre de livraison, ou identifier des adresses voisines - il est facile d'extraire le numéro et de l'utiliser pour le tri. Cela est également efficace même lorsque les adresses sont sur différentes rues.
1. Dans une cellule vide à côté de votre liste d'adresses, entrez la formule suivante pour extraire le numéro de rue :
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Où A1 est la première adresse de votre liste - ajustez si nécessaire.) Appuyez sur Entrée après l'avoir tapé. Cette formule fonctionne en localisant le premier espace et en renvoyant les caractères avant celui-ci, en les convertissant en valeur numérique. Si vos adresses ont des chiffres au début comme numéros de rue, cette formule fonctionnera correctement. Ensuite, faites glisser la poignée de recopie vers le bas pour appliquer la formule au reste de votre liste.
2. Sélectionnez la colonne d'aide que vous venez de créer, allez à l'onglet Données, et cliquez sur Trier de A à Z (ou Trier du plus petit au plus grand pour les versions plus récentes d'Excel).
3. Dans la boîte de dialogue Avertissement de tri, choisissez Développer la sélection pour trier les lignes entières.
4. Cliquez sur Trier pour appliquer. Vos adresses sont maintenant triées par le numéro de rue extrait.
Astuce : Si vous préférez conserver le numéro de rue en tant que texte, ou si vous n'avez pas besoin d'effectuer un tri numérique, vous pouvez également utiliser :
=LEFT(A1,FIND(" ",A1)-1)Cette version extraira le numéro sous forme de chaîne de texte.
Précautions : Si les adresses commencent par des mots au lieu de chiffres (comme "Main Street5"), ces formules ne fonctionneront pas comme prévu. Revérifiez vos données d'adresse avant d'utiliser la formule.
Avantages : Rapide et convivial si le format d'adresse est simple.
Inconvénients : Ne gère pas les adresses avec des noms/suffixes précédant le numéro, ni les adresses avec plusieurs numéros.
Code VBA - Automatiser le tri des adresses en extrayant les noms/numéros de rue et en triant la liste avec une macro
Pour ceux qui travaillent avec des listes d'adresses plus volumineuses et complexes, ou dont les données incluent des structures d'adresse variables, automatiser le processus de tri à l'aide de VBA peut être très efficace. Le VBA vous permet d'extraire rapidement les noms de rue ou les numéros, de trier automatiquement votre liste d'adresses et de minimiser les étapes manuelles. Cette solution est adaptée lorsque vous devez effectuer régulièrement des tris, ou souhaitez intégrer le tri dans un flux de travail.
Remarque : Ce macro VBA extrait le nom de rue (la partie après le premier espace) de chaque adresse dans la colonne A et trie toute la liste en fonction de ces noms. Il fonctionne également pour extraire et trier par numéro de rue avec de légères modifications.
1. Cliquez sur Onglet Développeur > Visual Basic. Dans la fenêtre qui apparaît, cliquez sur Insérer > Module, et collez le code VBA suivant dans la fenêtre du module :
Sub SortAddressesByStreetName()
Dim ws As Worksheet
Dim lastRow As Long
Dim tempCol As Long
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
tempCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
' Create helper column with street names
For i = 1 To lastRow
ws.Cells(i, tempCol).Value = Trim(Mid(ws.Cells(i, 1).Value, InStr(ws.Cells(i, 1).Value, " ") + 1))
Next i
' Sort the whole data range by the helper column
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(1, tempCol), ws.Cells(lastRow, tempCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, tempCol))
.Header = xlNo
.Apply
End With
' Delete helper column
ws.Columns(tempCol).Delete
End Sub2. Pour exécuter le code, avec la liste d'adresses active, cliquez sur le 
bouton ou appuyez sur F5. Votre liste d'adresses dans la colonne A sera maintenant triée par ordre alphabétique de nom de rue.
Cette version extrait uniquement le nombre avant le premier espace et trie par ordre numérique.
Dépannage :
- Confirmez que les adresses sont dans la colonne A ou mettez à jour le code pour l'emplacement de vos données.
- Si vos données incluent un en-tête, vous devrez peut-être ajuster Header = xlYes pour éviter de trier la ligne d'en-tête.
- Créez toujours une sauvegarde avant d'exécuter du code VBA en masse.
Avantages : Pas de colonnes d'aide requises ; fonctionne pour les grands ensembles de données ou les tris répétitifs.
Inconvénients : La configuration initiale nécessite des autorisations de macro et une compréhension de base du VBA.
Autres méthodes intégrées d'Excel - Utilisez Power Query pour diviser les colonnes d'adresses et trier directement dans Power Query sans colonnes d'aide
Power Query, disponible dans les versions modernes d'Excel (Excel 2016 et versions ultérieures, ainsi que Microsoft 365), offre une méthode flexible et sans formule pour diviser les adresses en composants tels que le numéro de rue et le nom de rue. Cette solution est idéale si vous préférez éviter les formules et les colonnes d'aide, ou si vos adresses suivent des formats variés que les formules de base ne peuvent pas gérer efficacement. Power Query peut également enregistrer vos étapes afin que vous puissiez les mettre à jour à mesure que vos données augmentent.
1. Sélectionnez vos données d'adresse et allez à l' Données onglet, puis choisissez À partir du tableau/plage (créez un tableau si vous y êtes invité).
2. Dans la fenêtre Power Query, sélectionnez votre colonne d'adresse, puis cliquez sur Diviser la colonne > Par délimiteur. Choisissez Espace comme délimiteur, et sélectionnez le premier délimiteur le plus à gauche pour le Diviser à type.
3. Cela divisera l'adresse en deux colonnes : le numéro de rue et le reste du nom de rue/adresse. Renommez les nouvelles colonnes si nécessaire.
4. Pour trier, cliquez sur la flèche dans l'en-tête de colonne de la colonne de nom de rue ou de numéro de rue et sélectionnez Trier par ordre croissant ou Trier par ordre décroissant.
5. Cliquez sur Fermer & Charger pour insérer les résultats triés dans votre feuille de calcul.
Conseils supplémentaires :
- Si votre motif d'adresse n'est pas cohérent, vous pouvez manipuler davantage les colonnes dans Power Query en utilisant des divisions personnalisées ou des transformations.
- Les étapes de Power Query sont automatiquement enregistrées ; vous pouvez actualiser les données facilement si votre source change.
- Cette méthode ne modifie pas vos données originales, améliorant ainsi la sécurité des enregistrements d'origine.
Avantages : Pas de modification permanente de votre feuille ; robuste pour des motifs d'adresse complexes ; pas de formules à gérer.
Inconvénients : Nécessite Excel 2016 ou version ultérieure ; l'interface peut être inconnue pour les nouveaux utilisateurs.
Résumé et suggestions de dépannage :
- N'oubliez pas de vérifier la cohérence de votre format d'adresse avant d'appliquer des formules ou du VBA.
- Prévisualisez toujours les résultats du tri pour confirmer l'exactitude, surtout après avoir utilisé des colonnes d'aide ou du code.
- Pour les données ayant une structure inattendue (telles que des numéros manquants ou des noms de rue à la fin), ajustez les formules ou envisagez Power Query pour un fractionnement plus robuste.
- Effectuez des sauvegardes régulières avant d'utiliser du VBA ou des outils de données avancés pour éviter une perte de données accidentelle.
- Choisissez une solution (formules, VBA, Power Query) qui correspond le mieux à votre volume de données, votre version d'Excel et votre niveau de confort avec l'outil.
- Si vous n'êtes pas sûr de la meilleure méthode, Power Query offre souvent la plus grande flexibilité et est la plus sûre pour une édition non destructive.
Articles connexes :
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels