Trier les données de tableaux par formule Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Trier les tableaux par calculs

Nous pourrions nous demander quel est l'intérêt de trier un tableau par les calculs. Imaginez un tableau lié dynamiquement à une source de données. Dans ces conditions, son contenu évolue et s'ajuste périodiquement. Mais ses informations ne sont pas pour autant organisées comme désiré. Si les calculs de tris sont enclenchés, les données sont extraites et organisées automatiquement dans l'ordre souhaité, pour un traitement plus confortable.



Dans l'exemple illustré par la capture ci-dessus, le tableau de gauche est la source originale, considérée comme liée dynamiquement à une base externe. Le tableau de droite est sa restitution stricte. Mais comme vous pouvez le voir, les informations de champs y sont réorganisées dans un ordre croissant. Et tout ce mécanisme s'opère automatiquement par calculs matriciels préconçus. C'est le défi que nous proposons de relever dans cet acte.

Source et présentation
Pour mener à bien ces travaux, nous suggérons d'oeuvrer à partir d'une source de données existante.

• Télécharger le classeur trier-tableau-par-calcul.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons donc une base de données sur la gauche de la feuille. Elle recense des départements, des activités et des villes. Toutes ces informations ont été purgées de leurs doublons grâce aux travaux aboutis lors de la formation précédente.

Le tableau sur la droite est vide pour l'instant. Il est destiné à recevoir les calculs, sous forme de formule unique réplicable, pour réagencer ces informations dans l'ordre croissant.

Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous notez que les colonnes de la base de données sont identifiées par leurs titres respectifs.



Nous exploiterons ces noms pour simplifier la syntaxe de la formule matricielle.

Extraire et trier par calcul
Premièrement, pour extraire les données, les fonctions Excel Index et Equiv sont nécessaires, mais dans un raisonnement matriciel. La mission de la fonction Equiv est de fournir tour à tour les indices de lignes des informations triées à la volée. Pour cela, en guise d'élément de recherche, nous devons exploiter la fonction Grande.Valeur. La fonction Grande.Valeur, dans sa version classique, n'est opérante que sur des données numériques. Dans ce raisonnement matriciel et en guise de plage de cellules à étudier, nous allons lui fournir une plage à dénombrer conditionnellement, grâce à la fonction Nb.Si. Le critère consiste à savoir si un texte est plus grand qu'un autre. Ces opérateurs d'inégalité fonctionnent parfaitement sur des chaînes de caractères. La lettre a est associée à un chiffre plus petit que les suivantes tandis que la lettre z est considérée comme la plus grande. Lorsque la fonction Nb.Si renverra le plus grand nombre, nous saurons qu'il s'agit du texte qui s'est fait dépasser le plus grand nombre de fois par les autres. En conséquence, nous en déduirons qu'il s'agit du premier à restituer dans l'ordre alphabétique, et ainsi de suite pour les autres. Pour qu'ils soient tous traités ensemble, nous devons fournir une matrice de même hauteur que le tableau au rang de la fonction Grande.Valeur. C'est ainsi que tous les rangs seront concernés dans un ordre décroissant pour fournir un classement alphabétique croissant.

Pour une extraction matricielle, les cellules concernées par les résultats doivent être présélectionnées.

• Sélectionner par exemple la plage de cellules F6:F100,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,

Le champ de recherche peut être désigné par les entêtes respectifs du tableau d'extraction. Mais pour que les contenus de ces cellules soient considérés comme des plages, nous devons exploiter la fonction d'interprétation.

• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le titre au-dessus du calcul en cliquant sur sa cellule F5,

Il fait bien référence à la colonne des départements, reconnue par son nom.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne à trouver,
• Saisir la fonction cherchant cet indice, suivie d'une parenthèse, soit : Equiv(,
• Inscrire la fonction des grandes valeurs suivie d'une parenthèse, soit : Grande.Valeur(,
• Taper la fonction de dénombrement suivie d'une parenthèse, soit : Nb.Si(,
• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner la plage de cellules à analyser en cliquant sur la cellule F5,
• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument du critère à compter sur cette plage,
• Saisir le critère concaténé suivant : '>='&Indirect(F5),

C'est ainsi et comme nous le disions que nous cherchons à compter les textes pour lesquels ce dénombrement est le plus important, en considérant toutes les données tour à tour, puisqu'incluses dans un raisonnement matriciel. Plus le décompte est élevé, plus la donnée est située en haut de la liste pour une restitution par ordre alphabétique croissant.

• Fermer la parenthèse de la fonction Nb.Si,
• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,
• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Taper la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Saisir la borne de départ de la plage entre guillemets, comme suit : '1:',
• Taper le symbole de concaténation pour annoncer la borne inférieure à joindre,
• Inscrire la fonction comptant les lignes d'une plage, suivie d'une parenthèse, soit : Lignes(,
• Saisir de nouveau la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner la plage des départements en cliquant sur la cellule F5 au-dessus du calcul,
• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Lignes,
• Fermer la parenthèse de la première fonction Indirect,
• Puis, fermer la parenthèse de la fonction Ligne,

En guise de rang, nous venons de passer à la fonction Grande.Valeur, une matrice de la même hauteur que la colonne analysée. Par raisonnement matriciel et donc inspection ligne à ligne des matrices, tous les rangs possibles seront considérés tour à tour, du plus grand au plus petit, selon les indications retournées par le dénombrement de la fonction Nb.Si. Il doit en résulter l'extraction ordonnée alphabétiquement des informations contenues dans la colonne des départements.

• Fermer la parenthèse de la fonction Grande.Valeur,
• Taper un point-virgule (;) pour l'argument du tableau de recherche de la fonction Equiv,

Pour que la correspondance soit opérée et que l'extraction ordonnée soit réalisée, ce tableau de recherche doit correspondre à une matrice restreinte selon les mêmes conditions que celles imposés dans la plage de la fonction Grande.Valeur.

• Copier et coller l'intégralité de la plage contrainte par la fonction Nb.Si, soit :

Nb.Si(Indirect(F5); '>='&Indirect(F5))

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,

Il n'est pas utile en effet de renseigner l'argument sur l'indice de colonne pour la fonction Index. Comme le tableau désigné pour l'extraction est une colonne unique, la fonction Index pointera implicitement et naturellement sur cette dernière.

• Puis, valider nécessairement le calcul matriciel par le raccourci clavier CTRL + MAJ + Entrée,

Sans grande surprise, nous obtenons la restitution des départements triés par ordre alphabétique croissant. En effet, ceux-ci étaient déjà ordonnés dans la liste de départ contrairement aux deux autres sur les activités et les villes. C'est donc notre formule matricielle :

{=INDEX(INDIRECT(F5); EQUIV(GRANDE.VALEUR(NB.SI(INDIRECT(F5); '>='&INDIRECT(F5)); LIGNE(INDIRECT('1:' & LIGNES(INDIRECT(F5))))); NB.SI(INDIRECT(F5); '>='&INDIRECT(F5)); 0))}

Que nous devons adapter pour opérer sur les deux colonnes de droite. C'est la cellule F5 qui doit être ajustée à chaque fois qu'elle est employée pour pointer sur les bonnes plages à analyser. Elle désigne indirectement la colonne des départements. Elle doit premièrement être remplacée par la cellule G5 pour pointer sur les activités puis sur la cellule H5 pour pointer sur les villes.

• Sélectionner les activités à extraire, soit par exemple la plage de cellules G6:G100,
• Adapter la formule matricielle comme suit :

{=INDEX(INDIRECT(G5); EQUIV(GRANDE.VALEUR(NB.SI(INDIRECT(G5); '>='&INDIRECT(G5)); LIGNE(INDIRECT('1:' & LIGNES(INDIRECT(G5))))); NB.SI(INDIRECT(G5);'>='&INDIRECT(G5)); 0))}

• Puis, valider le calcul par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez le voir, nous restituons bien toutes les activités. Mais elles sont extraites dans l'ordre alphabétique croissant grâce aux prouesses de cette formule matricielle.

• Sélectionner les villes à extraire, soit la plage de cellules H6:H100 par exemple,
• Adapter la formule matricielle comme suit :

{=INDEX(INDIRECT(H5); EQUIV(GRANDE.VALEUR(NB.SI(INDIRECT(H5); '>='&INDIRECT(H5)); LIGNE(INDIRECT('1:' & LIGNES(INDIRECT(H5))))); NB.SI(INDIRECT(H5);'>='&INDIRECT(H5)); 0))}

• Puis, valider le calcul par le raccourci clavier CTRL + MAJ + Entrée,

Là encore, nous obtenons bien la liste des villes réorganisées dans un ordre alphabétique croissant.



Nous sommes donc parvenus à réorganiser l'information d'un tableau Excel grâce aux calculs matriciels.