Fonctions matricielles dynamiques

Liste de fonctions expliquées sur cette page

Recherchex
Trier
Trierpar
Unique
Sequence
Tableau.alea
Equivx
Choisircols
Choisirlignes
Danscol
Dansligne

Excel offre certaines fonctions dont le résultat va prendre plusieurs cellules. Chaque valeur qui sera générée par ces fonctions ira dans sa propre cellule. Ce groupe de cellules est appelé une matrice et les fonctions qui les génèrent sont appelées des fonctions matricielles. 

Parmi ceux-ci, il y a les fonctions  Droitereg, Frequence et Transpose. Afin de générer la matrice, ils doivent aussi la combinaison de touches CTRL+MAJ+ENTRÉE pour s’activer. Ces fonctions, d’autres et le fonctionnement avancé seront expliqués dans la prochaine section de ce document.
 
Malheureusement ces fonctions ont aussi des limitations. Les usagers désiraient de nouvelles fonctions qui permettaient de s’étendre ou de se « propager » sur plusieurs cellules selon les besoins. En septembre 2018, l’équipe de développement d’Excel a démontré huit nouvelles fonctions qui permettent cela. Ces fonctions sont appelées des fonctions matricielles dynamiques. Ces fonctions sont Recherchex, Trier, Trierpar, Filtre, Unique, Sequence, Tableau.alea et Equivx. Les fonctions Choisircols, Choisirlignes, Danscol et Dansligne ont été ajoutés plus tard. Cette section va vous donner des explications sur le fonctionnement de ces fonctions ainsi que des options avancées.

=Recherchex( valeur à rechercher ; étendue avec la valeur ; étendu avec la valeur à afficher )

La fonction recherche une valeur dans la première étendue de cellules. Supposons qu’elle a retrouvé à la cinquième position de l’étendue, elle va donc afficher comme résultat la cinquième valeur de la seconde étendue. Note : la première et seconde étendue doivent être de la même dimension.

Rechechex vs. Recherchev

Cette approche est plus pratique que la fonction Recherchev expliquée précédemment dans ce document dans certains cas. Les étendues de cellules peuvent être éloignées l’une de l’autre. Ceci n’est pas le cas pour Recherchev. Recherchex peut seulement retrouver une valeur précise. Rechechev fonctionnement principalement dans des situations « Entre X et Y », mais peut être utilisée pour trouver une valeur précise.

Voici un exemple : trouvez le total des devenus selon la valeur du produit écrit dans la cellule E1. La liste des produits est entre A2 à A4 et les revenus entre B2 à B4.

=RECHERCHEX(E1;A2:A4;B2:B4)
Vous pouvez changer la valeur en E1 pour voir l’impact sur le résultat.

Recherchex avec plusieurs critères

La fonction peut aussi réaliser des recherches en utilisant plusieurs variables. Elle utilise le caractère « & » pour combiner les variables ainsi que les étendues de cellules.

Le caractère « & » est normalement utilisé pour combiner différents éléments situés dans les cellules d’Excel.

="Le poids du véhicule est de "&A2&" kg."

Par exemple, la formule ci-dessus combine du texte avec le contenu de la cellule A2. C’est très pratique pour améliorer la présentation du modèle.

Pour Recherchex, le « & » est utilisé pour combiner les variables et les zones de recherche. Les valeurs recherches sont dans les cellules F1 et F2. Les zones de recherches sont de B2 à B7 pour le produit (F1) et de A2 à A7 pour la région (F2).

=RECHERCHEX(F1&F2;B2:B7&A2:A7;C2:C7)

Entrez des valeurs dans les cellules F1 et F2 pour voir l’impact sur le résultat dans la cellule F4.

=TRIER(étendue de cellules, colonne à trier ; ordre du tri ; [tri par colonne])

=TRIER(B2:B4)

La propagation des fonctions dynamiques

• Entrez le chiffre 1 dans la cellule A7.

Il est possible que vous voyiez initialement seulement des ###. Agrandissez la largeur de la colonne A pour voir le message d’erreur #PROPAGATION. Ce message indique que le contenu de la cellule A7 empêche la fonction Trier de se « propager ». Laissez assez de place au bas et à la droite de la cellule ayant une fonction matricielle dynamique pour opérer.

• Effacez le contenu de la cellule A7 pour que la fonction Trier pour avoir le bon résultat.
• Dans la cellule C6, entrez la formule =MIN(A6#).
• Dans la cellule C7, entrez la formule =MAX(A6#).

Vous allez vouloir utiliser d’autres fonctions sur l’étendue de cellules dynamique générée pour les fonctions de cette section. Cependant, puisqu’elles sont dynamiques, comment déterminer l’étendue ? On ne peut pas utiliser des références tels que A6:A8 comme pour les autres formules. Le caractère « # » est maintenant utilisé pour les étendues dynamiques. Vous indiquez l’adresse de la première cellule de l’étendue suivie du « # ». Excel va s’ajuster selon les changements de données.

Trier sur plusieurs valeurs

Il est aussi possible de trier la fonction Trier pour trier sur plusieurs colonnes.

Ex. : Trier les ventes selon les produits en ordre croissant et les ventes en ordre décroissant. Placez la formule en E1.

=TRIER(A2:C7;{2;3};{1;-1})

Le premier paramètre détermine l’étendue à trier (A2 à C7). Le second paramètre indique le numéro de la colonne à trier (la deuxième suivit de la troisième). Le troisième paramètre indique l’ordre du tri : croissant (1) ou décroissant (-1). Dans ce cas, la colonne des produits sera triée en ordre croissant et celle des revenus en ordre décroissant. Vous avez aussi remarqué que les caractères { et } sont utilisés pour regrouper les colonnes à trier ainsi que l’ordre des tris.

=Trierpar(étendue de cellules; colonne à trier ; ordre du tri ; [seconde colonne à trier] ; [ordre du tri de seconde colonne] …)

Comme la fonction précédente, la fonction Trierpar permet de trier des tableaux de données ou des étendues de cellules. Cependant, il est plus facile avec celle-ci de trier sur plusieurs critères. Le premier paramètre détermine l’étendue de cellules à trier. Le paramètre suivant indique la colonne à trier et le paramètre suivi indique l’ordre du tri. Les paramètres suivants sont une répétition de la colonne à trier et l’ordre de tri. Donc, il est facile d’appliquer un tri sur une seconde, troisième ou même quatrième valeur.

Ex. : Trier sur les régions en ordre croissant suivi des revenus en ordre décroissant.

=TRIERPAR(A2:C7;A2:A7;1;C2:C7;-1)

C’est un peu plus facile à gérer que d’avoir à utiliser les caractères { } comme pour la fonction Trier.

=Filtre( étendue de cellules ; colonne et condition ; texte si aucune valeur )

Cette fonction permet de visionner seulement la ou les colonnes de données qui répondent à un ou plusieurs critères.

Ex. : Ressortez les données sur les produits annoncés dans la cellule F1.

=FILTRE(A2:C7;B2:B7=F1;"Aucune valeur")
Le premier paramètre indique l’étendue de cellules qui sera filtrée. Cela inclut toutes les lignes et colonnes de l’étendue. Le second paramètre inclut la zone de recherche suivie du critère de recherche. Ces opérateurs peuvent être =, >, >=, <, <= et <>. C’est suivi de l’élément de recherche. Cela peut faire référence à une cellule comme la cellule F1 dans ce cas. Mais cela aurait aussi pu être le texte "A". Cela donnerait la formule suivante :

=FILTRE(A2:C7;B2:B7="A";"Aucune valeur")

Le désavantage de cette approche est qu’il faut modifier la formule chaque fois qu’on recherche un élément différent.

Changez la valeur dans la cellule F1 pour avoir des résultats différents. Vous aurez le message « Aucune valeur » si vous n’entrez pas le produit A, B ou C.

Filtrer sur plusieurs critères

Comme les fonctions précédentes, il est possible de filtrer sur plusieurs critères. Encore une fois, c’est une approche différente pour sélectionner plusieurs étendues de cellules.

Ex. : Filtrer pour trouver les revenus du produit A de la région Nord.

=FILTRE(A2:C7;(B2:B7=F1)*(A2:A7=F2);"Aucune valeur")

Le premier paramètre indique toujours l’étendue de cellules qui sera filtrée. Le deuxième paramètre indique la zone de recherche et la condition à remplir. Mais on semble utiliser une formulation mathématique pour retrouver le résultat. Cette approche est aussi utilisée pour les formules matricielles. Vrais est représenté par 1 et faux par 0. Si les deux critères sont vrais, cela donne 1 X 1 = 1 et donc vrai. Toute autre combinaison sera fausse et ne sera pas considérée.

Il y a une autre façon de filtrer avec plusieurs valeurs d’une même colonne.

Ex. : Filtrez l’étendue A2 à C7 pour voir seulement les produits A et B.

=FILTRE(A2:C7;(B2:B7="A")+(B2:B7="B");"Aucune valeur")

Vous allez utiliser le caractère * pour l’équivalent de ET et le caractère + pour l’équivalent de OU. Vous pouvez aussi combiner ces conditions comme pour avoir tous les produits A et B de la région Nord.

=FILTRE(A2:C7;((B2:B7="A")+(B2:B7="B"))*(A2:A7="Nord");"Aucune valeur")

Il faut mettre les deux possibilités des produits (+) entre parenthèses avant de le multiplier à la région.

=UNIQUE( étendue de cellules )

Cette fonction ressort une liste de valeurs uniques de l’étendue de cellules choisie. Elle élimine les répétitions.

Ex. : Déterminez la liste de valeurs de l’étendue A1 à A7.

=UNIQUE(A1:A7)

Mettre la liste en ordre croissant

Cette liste ne sera généralement pas en ordre croissant ou décroissant. Vous pouvez combiner la fonction Unique avec la fonction Trier.

=TRIER(UNIQUE(A1:A7))

=SEQUENCE( nombre de valeurs par ligne ; [nombre de chiffres par colonne] ; [valeur de départ] ; [pas])

Ex. : Générez une liste un tableau de valeurs de 7 lignes par 3 colonnes commençant le chiffre 100 et dont chaque valeur augmente par 5.

=SEQUENCE(7;3;100;5)

Vous n’êtes pas obligé de placer un chiffre pour les colonnes. C’est une option. Vous pouvez simplement mettre un chiffre pour le nombre de lignes, le point de départ et la valeur entre les chiffres.

Ex. :  =SEQUENCE(20;;10;10)

Cet exemple va générer 20 lignes commençant par le chiffre 10 et augmentant de 10 pour chaque ligne.

=TABLEAU.ALEA(nombre de lignes ; [nombre de colonnes] ; [valeur minimale] ; [valeur maximale] ; [entier] )

Cette fonction permet de générer une liste ou un tableau de valeurs aléatoires qui pourra être utilisé dans des simulations ou dans des modèles de recherche opérationnelle qui consiste à prendre de meilleures décisions avec les données disponibles avec des simulations ou des techniques d’optimisation.

Le premier paramètre détermine le nombre de lignes qui seront requises pour le tableau. Si nécessaire, le second paramètre déterminé le nombre de colonnes qui seront requises dans le tableau. Celle-ci est optionnelle. Le troisième et quatrième paramètres déterminent respectivement la valeur minimale et maximale que le tableau pourra avoir. Celles-ci sont aussi optionnelles. Sans celles-ci, le tableau aura des valeurs aléatoires entre 0 et 1. Par défaut, Excel va générer des chiffres avec des décimales. Le dernier paramètre force la fonction à générer des chiffres entiers, sans décimales, si la valeur est Vrai.

Ex. : générez un tableau aléatoire de 5 lignes et 3 colonnes dont les valeurs sont des entiers entre 1 et 100.

=TABLEAU.ALEA(5;3;1;100;VRAI)

Comme pour les fonctions Alea et Alea.entre.bornes, Excel peut régénérer les valeurs du tableau chaque fois que vous appuyez sur la touche F9.

=EQUIVX(Valeur recherchée ; étendue de cellules ; [mode correspondance] ; [mode recherche])

Cette fonction retourne la position relative d’une valeur dans une étendue de cellules.

=EQUIVX(D1;A1:A10)

Dans cet exemple, la recherche de Y dans l’étendu de cellules va retourner le chiffre 10 puisque le Y est en 10e position dans cette liste. Cette fonction, ainsi qu’Equiv() sont souvent utilisés avec la fonction Index() pour retrouver la valeur équivalente d’une autre liste selon sa position pour donner =index(étendue de cellules avec valeur recherchée; equiv(valeur à comparer; liste de valeurs à comparer)). C’était l’approche utilisée pour rechercher une valeur selon le contenu d’une autre liste avant l’arrivée de la fonction Recherchex. Celle-ci simplifie la tâche en n’ayant pas à combiner deux fonctions.

=Choisircols(étendu de cellules; choix colonne 1; [choix colonne 2] …)

Cette fonction affiche seulement les colonnes que vous avez choisies parmi la zone originale dans l’ordre de votre choix.

Ex. : E1 = =CHOISIRCOLS(A1:C3;3;2)

Le résultat de cette formule va afficher premièrement le contenu de la troisième colonne avant celle de la deuxième colonne.

=choisirlignes(étendu de cellules ; choix de ligne 1 ; [choix de ligne 2] …)

Cette fonction affiche seulement les lignes demandées dans l’ordre de votre choix.

Ex. : E1 = =CHOISIRLIGNES(A1:C3;3;2)

Le résultat de cette formule montre la troisième ligne du tableau suivi de la deuxième.

=Danscol(étendu de cellules; [ignorer] ; [par colonne])

Cette fonction prend le contenu d’un tableau de données ayant plusieurs lignes et colonnes et les places dans une seule colonne. Le premier paramètre ou argument détermine l’étendue de cellules. Le second paramètre, qui est optionnel, détermine si certaines valeurs devraient être ignorées.

Le dernier paramètre, aussi optionnel, détermine si l’ordre de lecture des données se fait par colonne. Si vrai, les données sont lues colonne par colonne. Sinon, par défaut, les données sont lues une ligne à la fois.

Ex. : =DANSCOL(A1:C3;1)

Cette formule va afficher en une seule colonne toutes les valeurs du tableau A1 à C3. Notez qu’on saute directement de « b » à « Y ». La cellule vide entre ces valeurs n’est pas considérée à cause du second paramètre. Les changements que vous apportez aux données sources seront automatiquement représentés dans le résultat.

=dansligne(étendu de cellules; [ignorer] ; [par colonne])

Cette formule va afficher une série de cellules sur une seule ligne. Les paramètres sont identiques à Danscol() ci-dessus.

Ex. : =DANSLIGNE(A1:C3;1)

 La série de données est maintenant représentée sur une seule ligne. Comme dans le dernier exemple, la cellule vide n’est pas représentée dans le résultat.