Formule générique
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Sommaire
Pour trier dynamiquement les données avec des nombres et du texte dans l'ordre alphabétique, vous pouvez utiliser une formule pour générer un rang numérique dans une colonne d'aide, puis utiliser INDEX et MATCH pour afficher les valeurs basées sur le rang. Dans l'exemple illustré, la formule en C5 est:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
où "données" est la plage nommée B5: B13.
Explication
Cette formule génère d'abord une valeur de classement à l'aide d'une expression basée sur COUNTIF:
=COUNTIF(data,"<="&B5)
qui est expliqué plus en détail ici. Si les données contiennent toutes les valeurs de texte ou toutes les valeurs numériques, le classement sera correct. Cependant, si les données comprennent à la fois du texte et des nombres, nous devons "décaler" le rang de toutes les valeurs de texte pour tenir compte des valeurs numériques. Ceci est fait avec la deuxième partie de la formule ici:
+(COUNT(data)*ISTEXT(B7))
Ici, nous utilisons la fonction COUNT pour obtenir un nombre de valeurs numériques dans les données, puis multiplions le résultat par le résultat logique de ISTEXT, qui teste si la valeur est du texte et renvoie VRAI ou FAUX. Cela annule effectivement le résultat COUNT lorsque nous travaillons avec un nombre dans la ligne actuelle.
Gestion des doublons
Si les données contiennent des doublons, la formule peut être modifiée comme indiqué ci-dessous pour attribuer un rang séquentiel aux valeurs qui apparaissent plusieurs fois:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Cette version ajuste la logique de la fonction initiale COUNTIF et ajoute un autre COUNTIF avec une référence en expansion pour incrémenter les doublons.
Afficher les valeurs triées
Pour récupérer et afficher les valeurs triées par ordre alphabétique à l'aide de la valeur de classement calculée, E5 contient les formules INDEX et MATCH suivantes:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
où «données» est la plage nommée B5: B13 et «rang» est la plage nommée C5: C13.
Pour plus d'informations sur le fonctionnement de cette formule, consultez l'exemple ici.
Gérer les blancs
Les cellules vides généreront un rang de zéro. En supposant que vous souhaitiez ignorer les cellules vides, cela fonctionne bien car la formule INDEX et MATCH ci-dessus commence à 1. Cependant, vous verrez des erreurs # N / A à la fin des valeurs triées, une pour chaque cellule vide. Un moyen simple de gérer cela est d'encapsuler la formule INDEX et MATCH dans IFERROR comme ceci:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
