Comment trier une liste par ordre alphabétique sans VBA.

Les fonctions de tri dans VBA sont très puissantes et rapides mais parfois on souhaite faire cela sans macro. Voici différentes formules utiles pour cela et leur explication pas à pas de façon à ce que vous puissez les reproduire et les adapter à votre problématique.

Trier par formule (1 colonne)

comment passer par formule de la colonne de gauche à celle de droite ?

La plage des prénoms (qui commence ici en A10) est nommée (insertion/nom/définir ou onglet formules/définir un nom) de façon dynamique par la formule

=DECALER('feuil1'!$A$10;1;0;NBVAL('feuil1'!$A:$A)-NBVAL('feuil1'!$A$1:$A$10))

Pour plus de détails sur la fonction décaler voir le Pas à pas sur DECALER
NB :

NBVAL('feuil1'!$A:$A)-NBVAL('feuil1'!$A$1:$A$10)

permet de définir la hauteur de la plage même si on ajoute des données dans les cellules A1:A10

La formule à utiliser est la suivante :

=INDEX(Prénoms;EQUIV(PETITE.VALEUR(NB.SI(Prénoms;"<"&Prénoms);LIGNE(1:1));NB.SI(Prénoms;"<"&Prénoms);0))

C'est une formule matricielle.
Mettez la par exemple en F3, validez de façon matricielle, c'est à dire en appuyant simultanément sur les touches CTRL+Maj+entrée.
Puis étendez cette formule vers le bas sur autant de lignes qu'il y en a dans votre tableau de départ.
Si vous prévoyez que votre tableau de départ s'agrandisse au cours du temps, vous pouvez tirer la formule sur un plus grand nombre de lignes mais dans ce cas vous verrez des #N/A en bas de tableau.

Explications de la formule

Pour plus de simplicité dans les explications, réduisons notre plage de départ aux trois premiers prénoms et ne gardons que leur première lettre.
Comment passer donc de CAB à ABC.
Comme presque toujours, pour décortiquer une formule il faut commencer par son "centre".
Si vous ne voyez pas immédiatement où celui-ci se situe (ça peut se comprendre !) utilisez l'évaluation de formules dans excel. Vous verrez comment excel procède !

NB.SI(Prénoms;"<"&Prénoms)

NB.SI (plage;critère) Compte le nombre de cellules dans une plage répondant au critère.
Comme la formule est une formule matricielle, excel "l'écrit" en mémoire vive de la façon suivante :

NB.SI({C;A;B};"<"&C;"<"&A;"<"&B)

et évalue ensuite chacun des termes

{C;A;B};"<"&C

Ce qu'on peut développer en

C<C; A<C; B<C

ce qui "vaut" respectivement

FAUX; VRAI; VRAI

FAUX= 0 et VRAI = 1 donc l'évaluation de cette partie {C;B;A};"<"&C renvoie la valeur 2 (0+1+1)
De même
{C;A;B};"<"&A renvoie 0
et
{C;A;B};"<"&B renvoie 1

NB.SI(Prénoms;"<"&Prénoms)

renvoie par conséquent une matrice de 3 valeurs : {2;0;1}

PETITE.VALEUR(NB.SI(Prénoms;"<"&Prénoms);LIGNE(1:1))

Vu ce qui précède,

PETITE.VALEUR(NB.SI(Prénoms;"<"&Prénoms) ;LIGNE(1:1))

est équivalent à

PETITE.VALEUR({2;0;1};1) 

La première plus petite valeur de cet array est 0, ce que renvoie donc cette portion de formule.

L'intérêt de ligne(1:1) ici c'est qu'en tirant ensuite la formule vers le bas, ligne(1:1) devient ligne (2:2) et donc la formule s'incrémente toute seule.

EQUIV(PETITE.VALEUR(NB.SI(Prénoms;"<"&Prénoms);LIGNE(1:1));NB.SI(Prénoms;"<"&Prénoms);0)

EQUIV(Valeur_cherchée;tableau_recherche; type)

renvoie la position de la valeur cherchée dans le tableau de recherche.
On utilise 0 comme argument pour type vu que notre tableau (de départ !) n'est pas trié et qu'on cherche la valeur exacte.
Remplaçons par ce qui est déjà évalué :

EQUIV(0;{2;0;1};0)

O est en deuxième position dans notre array donc cette portion de la formule renvoie 2

INDEX(Prénoms;EQUIV(PETITE.VALEUR(NB.SI(Prénoms;"<"&Prénoms);LIGNE(1:1));NB.SI(Prénoms;"<"&Prénoms);0))

INDEX(Tableau_contenant_la_valeur_a_renvoyer ; position relative de la ligne dans le tableau; position relative de la colonne dans le tableau)

Comme nous n'avons qu'une colonne ici dans le tableau contenant la valeur à renvoyer, le dernier argument qui est optionnel est omis.

INDEX(prénoms;2) 

renvoie donc le 2° prénom de notre liste de départ soit A.

Après avoir validé de façon matricielle cette formule, en la tirant vers le bas, ligne(1:1) s'incrémente et on récupère ainsi le 1°, puis le 2°, 3°... élément du tableau contenant les différents prénoms.