Table des matières
Si vous avez besoin de trier la liste, il existe de nombreuses façons à votre service, dont la plus simple est les boutons de tri sur l'onglet ou dans le menu Données (Données — Trier). Cependant, il existe des situations où le tri de la liste doit être effectué automatiquement, c'est-à-dire des formules. Cela peut être nécessaire, par exemple, lors de la génération de données pour une liste déroulante, lors du calcul de données pour des graphiques, etc. Comment trier une liste avec une formule à la volée ?
Méthode 1. Données numériques
Si la liste ne contient que des informations numériques, le tri peut être facilement effectué à l'aide des fonctions MOINS (PETIT) и LINE (LIGNE):
Fonction MOINS (PETIT) extrait du tableau (colonne A) le n-ième plus petit élément d'affilée. Ceux. PETIT(A:A;1) est le plus petit nombre de la colonne, PETIT(A:A;2) est le deuxième plus petit, et ainsi de suite.
Fonction LINE (LIGNE) renvoie le numéro de ligne pour la cellule spécifiée, c'est-à-dire LIGNE(A1)=1, LIGNE(A2)=2 etc. Dans ce cas, il est simplement utilisé comme générateur d'une suite de nombres n=1,2,3… pour notre liste triée. Avec le même succès, il a été possible de faire une colonne supplémentaire, de la remplir manuellement avec la séquence numérique 1,2,3… et de s'y référer à la place de la fonction ROW.
Méthode 2. Liste de textes et formules régulières
Si la liste ne contient pas de chiffres, mais du texte, la fonction SMALL ne fonctionnera plus, vous devez donc emprunter un chemin différent, légèrement plus long.
Tout d'abord, ajoutons une colonne de service avec une formule où le numéro de série de chaque nom dans la future liste triée sera calculé à l'aide de la fonction COUNTIF (NB.SI):
Dans la version anglaise, ce sera :
=NB.SI(A:A,»<"&A1)+NB.SI($A$1:A1,"="&A1)
Le premier terme est une fonction pour compter le nombre de cellules qui sont inférieures à celle en cours. Le second est un filet de sécurité au cas où un nom apparaîtrait plus d'une fois. Alors ils n'auront pas le même nombre, mais des nombres croissants successivement.
Maintenant, les numéros reçus doivent être classés séquentiellement dans l'ordre croissant. Pour cela, vous pouvez utiliser la fonction MOINS (PETIT) de la première manière :
Bon, enfin, il ne reste plus qu'à sortir les noms de la liste par leurs numéros. Pour ce faire, vous pouvez utiliser la formule suivante :
Fonction PLUS EXPOSÉ (RENCONTRE) recherche dans la colonne B le numéro de série souhaité (1, 2, 3, etc.) et, en fait, renvoie le numéro de la ligne où se trouve ce numéro. Fonction INDEX (INDICE) extrait de la colonne A le nom à ce numéro de ligne.
Méthode 3 : formule matricielle
Cette méthode est, en fait, le même algorithme de placement que dans la Méthode-2, mais implémenté par une formule matricielle. Pour simplifier la formule, la plage de cellules C1:C10 a reçu le nom Liste (sélectionnez des cellules, appuyez sur Ctrl + F3 et bouton Création):
Dans la cellule E1, copiez notre formule :
=INDEX(Liste; MATCH(PETIT(NB.SI(Liste; "<"&Liste); LIGNE(1:1)); NB.SI(Liste; "<"&Liste); 0))
Ou dans la version anglaise :
=INDEX(Liste, MATCH(PETIT(NB.SI(Liste, "<"&Liste), LIGNE(1:1)), NB.SI(Liste, "<"&Liste), 0))
et pousser Ctrl + Maj + Entréepour le saisir sous forme de formule matricielle. Ensuite, la formule résultante peut être copiée sur toute la longueur de la liste.
Si vous souhaitez que la formule ne tienne pas compte d'une plage fixe, mais puisse s'ajuster lors de l'ajout de nouveaux éléments à la liste, vous devrez alors modifier légèrement la stratégie.
Tout d'abord, la plage de la liste devra être définie dynamiquement. Pour ce faire, lors de la création, vous devez spécifier non pas une plage fixe C3: C10, mais une formule spéciale qui fera référence à toutes les valeurs disponibles, quel que soit leur nombre. Cliquez sur Alt + F3 ou ouvrez l'onglet Formules - Gestionnaire de noms (Formules — Gestionnaire de noms), créez un nouveau nom et dans le champ Lien (Référence) entrez la formule suivante (je suppose que la plage de données à trier commence à partir de la cellule C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=DÉCALAGE(C1,0,0,SCHÖTZ(C1:C1000),1)
Deuxièmement, la formule matricielle ci-dessus devra être étirée avec une marge - dans l'attente de données supplémentaires saisies à l'avenir. Dans ce cas, la formule matricielle commencera à donner une erreur #NUMBER sur les cellules qui ne sont pas encore remplies. Pour l'intercepter, vous pouvez utiliser la fonction IFERREUR, qui doit être ajouté "autour" de notre formule matricielle :
=SIERREUR(INDEX(Liste; MATCH(PETIT(NB.SI(Liste; "<"&Liste); LIGNE(1:1)); NB.SI(Liste; "<"&Liste); 0));»»)
=IFERREUR(NDEX(Liste, MATCH(PETIT(NB.SI(Liste, "<"&Liste), LIGNE(1:1)), NB.SI(Liste, "<"&Liste), 0));"")
Il détecte l'erreur #NUMBER et génère un vide (guillemets vides) à la place.
:
- Trier la gamme par couleur
- Que sont les formules matricielles et pourquoi sont-elles nécessaires
- Tri SORT et tableaux dynamiques dans le nouvel Office 365