Le tri de données intégré à Excel est incroyable, mais il n'est pas dynamique. Si vous triez des données et que vous y ajoutez des données, vous devrez les trier à nouveau.
Trier les données par ordre alphabétique
Dans cet article, je vais vous montrer différentes manières de trier les données par ordre alphabétique à l'aide de formules. Cela signifie que vous pouvez ajouter des données et qu'il les triera automatiquement pour vous.
Lorsque les données ne sont que du texte sans doublons
Supposons que vous ayez les données ci-dessous :
Dans cet exemple, toutes les données sont au format texte (pas de chiffres, de blancs ou de doublons). Pour trier cela, je vais utiliser une colonne d'aide. Dans la colonne à côté des données, utilisez la formule COUNTIF suivante :
=COUNTIF($A$2:$A$9,"<="&A2)
Cette formule compare une valeur de texte avec toutes les autres valeurs de texte et renvoie son rang relatif. Par exemple, dans la cellule B2, il renvoie 8, car il y a 8 valeurs de texte inférieures ou égales au texte « US » (ordre alphabétique).
Maintenant, pour trier les valeurs, utilisez la combinaison suivante de fonctions INDEX, MATCH et ROWS :
=INDICE($A$2:$A$9,MATCH(LIGNES($B$2:B2),$B$2:$B$9,0))
Cette formule extrait simplement les noms dans l'ordre alphabétique. Dans la première cellule (C2), il recherche le nom du pays qui a le numéro le plus bas (l'Australie en a 1). Dans la deuxième cellule, il renvoie Canada (qui porte le numéro 2) et ainsi de suite…
Allergique aux colonnes auxiliaires ??
Voici une formule qui fera la même chose sans la colonne d'aide.
=INDICE($A$2:$A$9,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),0))
Ceci est une formule matricielle, utilisez donc Contrôle + Maj + Entrée au lieu d'entrer.
Je vous laisse le décoder.
Essayez-le vous-même… Téléchargez le fichier d'exemple
Cette formule fonctionne bien si vous avez du texte ou des valeurs alphanumériques.
Mais il échoue lamentablement si :
- Vous avez des doublons dans les données (essayez de mettre US deux fois).
- Il y a des blancs dans les données.
- Vous avez un mélange de chiffres et de texte (essayez de mettre 123 dans l'une des cellules).
Lorsque les données sont un mélange de nombres, de texte, de doublons et de blancs
Maintenant, celui-ci est un peu délicat. J'utiliserai 4 colonnes d'aide pour vous montrer comment cela fonctionne (puis vous donner une formule énorme qui le fera sans les colonnes d'aide). Supposons que vous ayez les données ci-dessous :
Vous pouvez voir qu'il y a des valeurs en double, des blancs et des nombres. Je vais donc utiliser des colonnes d'aide pour résoudre chacun de ces problèmes.
Aide Colonne 1
Entrez la formule COUNTIF suivante dans la colonne d'assistance 1
=COUNTIF($A$2:$A$9,"<="&A2)
Cette formule effectue les opérations suivantes :
- Il renvoie 0 pour les blancs.
- En cas de doublons, il renvoie le même numéro.
- Le texte et les nombres sont traités en parallèle et cette formule renvoie le même nombre pour le texte et le nombre (par exemple, 123 et l'Inde obtiennent tous les deux 1).
Aide Colonne 2
Saisissez la fonction IS suivante dans la colonne d'assistance 2 :
=--ISNUMBER(A2)
Aide Colonne 3
Entrez la formule suivante dans la colonne d'assistance 3 :
=--ISBLANC (A2)
Aide Colonne 4
Entrez la formule suivante dans la colonne d'assistance 4
=SI(ESTNUMERO(A2),B2,SI(ESTBLANC(A2),B2,B2+$C$10))+$D$10
L'idée de cette formule est de séparer les blancs, les nombres et les valeurs de texte.
- Si la cellule est vide, elle renvoie la valeur de la cellule B2 (qui serait toujours 0) et ajoute la valeur de la cellule D10. En un mot, il renverra le nombre total de cellules vides dans les données
- Si la cellule est une valeur numérique, elle renverra le classement comparatif et ajoutera le nombre total de blancs. Par exemple, pour 123, il renvoie 2 (1 est le rang de 123 dans les données, et il y a 1 cellule vide)
- S'il s'agit de texte, il renvoie le classement comparatif et ajoute le nombre total de valeurs numériques et de blancs. Par exemple, pour l'Inde, il ajoute le classement comparatif du texte dans le texte (qui est 1) et ajoute le nombre de cellules vides et le nombre de valeurs numériques.
Résultat final - Données triées
Nous allons maintenant utiliser ces colonnes d'aide pour obtenir la liste triée. Voici la formule :
=SIERREUR(INDICE($A$2:$A$9,MATCH(PETIT($E$2:$E$9,LIGNES($F$2:F2)+$D$10),$E$2:$E$9,0)) ,"")
Cette méthode de tri devient désormais infaillible. Je vous ai montré la méthode pour 8 éléments, mais vous pouvez l'étendre à autant d'éléments que vous le souhaitez.
Essayez-le vous-même… Téléchargez le fichier d'exemple
Une formule pour tout trier (sans colonnes d'aide)
Si vous pouvez gérer des formules extrêmes, voici une formule tout-en-un qui triera les données par ordre alphabétique (sans aucune colonne d'aide).
Voici la formule :
=IFERREUR(INDICE($A$2:$A$9,MATCH(SMALL(NOT($A$2:$A$9="")*SI(ISNUMBER($A$2:$A$9),COUNTIF($A$2 : $A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(--ISNUMBER($A $2:$A$9))), LIGNES($A$2:A2)+SUM(--ISBLANK($A$2:$A$9))),NOT($A$2:$A$9="")*IF (ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<=" &$A$2:$A$9)+SUM(--ISNUMBER($A$2:$A$9))),0)),"")
Entrez cette formule dans une cellule et faites-la glisser vers le bas pour obtenir la liste triée. De plus, puisqu'il s'agit d'une formule matricielle, utilisez Contrôle + Maj + Entrée au lieu d'entrer.
Cette formule a une utilité réelle. Qu'en penses-tu? J'aimerais apprendre de vous. Laissez vos empreintes dans la section commentaires!
