Normalement, le résultat d'une formule s'affiche dans la cellule où elle est écrite. Mais, dans certains cas, le résultat doit être ecrit dans plusieurs cellules. Excel appelle cette étendue de cellules une "matrice". C'est pour cette raison qu'on utilise le terme fonction matricielle.
La prochaine partie consiste d'exercices qui expliquent le fonctionnement des fonctions matricielles Frequence(), Transpose() et Droitereg(). La partie suivante explique comment utiliser une fonction sur une matrice tout en appliquant une condition. La dernière partie explique comment utiliser une fonction sur une matrice en ayant plusieurs conditions. La structure dans ce cas est totalement différente que lorsqu'il y a qu'une seule condition.
Les formules matricielles
La fonction Frequence()
=frequence(table de données;liste des intervalles)
Crée un tableau, une matrice est le terme d’Excel, de fréquences selon les intervalles de votre choix. Elle vous permet de connaître le nombre de valeur qui se retrouve dans chaque intervalle que vous avez déterminé.
L’utilisation de cette fonction est très différente des autres puisqu’elle sert à créer un tableau et s’applique sur plusieurs cellules à la fois. Excel appelle l’étendue de cellules nécessaires pour cette opération une matrice. Voici les étapes pour l’utiliser correctement.
Il vous faut trois éléments pour créer un tableau de fréquences : des données, une liste d’intervalles et une étendue pour les fréquences.
Entrez les valeurs dans les cellules appropriées. Sélectionnez les cellules D1 à D3.
La liste des intervalles doit être entrée en ordre croissant. De plus, vous écrivez la plus grande valeur de la liste. Pour l’exemple ci-dessus, les intervalles sont de 10 et toutes valeurs sous celle-ci, de 20 jusqu’à 10 (non inclusif) et de 30 jusqu’à 20 (non inclusif).
Entrez la formule : =frequence(A1:A12;C1:C3)
ATTENTION : Ne validez pas la formule avec la touche Entrée.
En gardant un doigt sur les touches Ctrl et MAJ (Shift), appuyez sur la touche Entrée.
Il faut appuyer sur les touches Ctrl, MAJ et Entrée en même temps pour qu’Excel génère un tableau (matrice) de fréquences.
Cette fonction permet d’inverser, ou de transposer, un tableau de données. Puisqu’il s’agit d’appliquer cette fonction sur plusieurs cellules, le processus est légèrement différent. Suivez attentivement les étapes.
Entrez les chiffres dans les cellules appropriées.
Sélectionnez les cellules A4 à B6.
Le tableau de données et composé de 2 lignes et de 3 colonnes. Le tableau transposé devra alors avoir 3 lignes et 2 colonnes.
Entrez la formule =transpose(A1:C2) .
ATTENTION : N’appuyez pas sur la touche Entrée pour confirmer la formule.
Appuyez sur les touches Ctrl, MAJ (shift) et Entrée.
En utilisant cette combinaison de touches, la fonction sera appliquée à une étendue de cellules ou une « matrice » pour utiliser le terme d’Excel. Le tableau sera inversé. Changez un chiffre tableau original et il changera aussi dans le tableau inversé.
=droitereg(étendu de valeurs y; étendu de valeurs x)
Détermine la droite de régression d'une série de valeurs (x,y)
Une droite est calculée
y = a * x + b. Cette formule calcule la pente (a) ainsi que la constante de cette équation. Pour cet exemple, la droite est y = 2x + 5.
Sélectionnez les cellules A7 et B7. Entrez la formule =droitereg(b1:b5;a1:a5).
ATTENTION : Ne validez pas la formule avec la touche Entrée.
En gardant un doigt sur les touches Ctrl et MAJ (Shift), appuyez sur la touche Entrée.
Le résultat sera dans deux cellules. La première cellule va montrer la valeur de la pente qui sera 2 pour cet exemple. La seconde cellule affichera la valeur de la constante qui est 5 pour ce cas.
Sélectionnez les cellules A7 à B11. Entrez la formule =droitereg(b1 :b5;a1 :b5;;vrai) Validez la formule en appuyant sur les touches Ctrl, MAJ (Shift) et Entrée.
Vous venez de voir et d’appliquer les fonctions de ce manuel. Vous pouvez devenir encore plus performant en appliquant des formules matricielles. L’un des grands avantages de ces formules est qu’elles combinent des fonctions et analyse une masse de données en prenant très peu d’espace dans votre chiffrier.
Excel utilise le terme « matrice » pour décrire une étendue de cellules inséparables. Toute l’étendue est requise pour trouver la solution. Le logiciel va reconnaître une formule matricielle seulement si vous la validez en utilisant les touches Ctrl, MAJ (Shift) et Entrée. L’application va vous retourner le message d’erreur #VALEUR si vous appuyez seulement sur la touche Entrée. Pour vous démontrer qu’il s’agit d’une formule matricielle, Excel va placer des accolades ( { } ) devant et après celle-ci. Par exemple : {=somme(si(A1:A5>= "c";B1:B5))} . N’essayez pas de placer des accolades par vous-même. Excel va les placer lorsque vous validerez une formule matricielle avec les touches Ctrl, MAJ (Shift) et Entrée.
Les formules matricielles sont souvent utilisées pour trouver un résultat sur une masse de données que l’on doit premièrement filtrer. Pour le prochain exemple, il faut trouver la somme des lignes où le texte de la première colonne est de « c » ou plus grand.
Entrez les chiffres dans les cellules appropriées. Placez le pointeur dans la cellule B7. Entrez la formule : =somme(si(A1:A5>="c";B1:B5)) Validez la formule en utilisant les touches Ctrl, MAJ (Shift) et Entrée.
Le résultat sera de 12. Il faut maintenant expliquer cette formule. Vous connaissez bien les fonctions =somme() et =si(). Dans une formule matricielle, on applique le critère sur une étendue de cellules (A1 :A5) au lieu d’une seule cellule à la fois. Si le résultat d’une ligne est vrai, le chiffre de la ligne du bloc de B1 à B5 sera ajouté à la somme recherchée. Dans cet exemple, les lignes 3, 4 et 5 répondent au critère (>= « c »). La formule va donc chercher les chiffres de ces mêmes lignes de la colonne B (3, 4, et 5) pour les additionner. Cette formule fait donc la somme de chiffres tout en filtrant les données.
Vous pourriez recréer ce résultat sans utiliser de formules matricielles. Il faudrait ajouter une formule =si(Ax>= « c »;Bx;0) pour les lignes 1 à 5. Ensuite, il faut créer une formule qui utilise le fonction somme pour additionner les résultats de la colonne avec les formules =si(). Pour ce petit exemple, il faudrait prendre une colonne et 6 cellules de plus pour arriver au même résultat que la formule matricielle!
La fonction =somme.si() aurait aussi donné le résultat voulu avec la formule =SOMME.SI(A1:A5;">=c";B1:B5) . Mais cette fonction n’aurait pas aidé si on désirait utiliser d’autres fonctions. Voici une courte liste de formules matricielles qui utilisent d’autres fonctions.
Il est aussi possible de créer des formules matricielles qui utilisent plusieurs critères. Mais avant de vous montrer la formulation requise, vous devez savoir que le résultat d’un critère qui donne VRAI équivaut au chiffre 1. Tout résultat de critère qui est FAUX équivaut à 0. Ceci est important parce que le principe pour filtrer les données consiste à multiplier les résultats des critères aux données. Si l’un des critères est faux, la donnée sera multipliée par 0 et ne sera pas prise en considération par la fonction.
Le prochain exercice à faire la somme des chiffres qui répondent à deux critères. Dans ce cas, le contenu de la première colonne doit être plus grand que « a ». Le contenu de la deuxième colonne doit être « bb ».
Entrez les données dans les cellules appropriées. Placez le pointeur dans la cellule A11. Entrez la formule =SOMME((A1:A9>"a")*(B1:B9="bb")*(C1:C9)) .
N’oubliez pas de placer des parenthèses autour de chaque critère. Excel place la priorité aux opérateurs mathématiques (+,-,*,/,^,()) avant les opérateurs logiques (=,>,>,>=,>=,<>).
Validez la formule en appuyant sur les touches Ctrl, MAJ (Shift) et Entrée.
Le résultat devrait être de 16 (2+6+8).
Prenons l’exemple de la ligne 5. Le premier critère de la formule est vrai et donne un résultat de 1. Mais le second critère est faux et donne un comme résultat la valeur 0. Le résultat de la multiplication des critères donne donc 0.
Il est possible d’appliquer cette formulation avec d’autres fonctions et avec plus de critères si vous ajustez la formulation. Cependant, il y a certains points pour lesquels vous devez faire très attention.
Par exemple, la fonction =min() donnera un résultat de 0 à moins que les données contiennent une valeur négative. Le calcul requis pour déterminer peut donner des valeurs de 0 si les critères ne sont pas respectés. Aussi, la fonction =médiane() ne donnera pas le bon résultat.
La formule matricielle qui utilise la fonction =moyenne() avec plusieurs critères ne donnera pas le bon résultat. Celle-ci va diviser le total des valeurs qui répondent aux critères par le nombre total de données. Il faut trouver la somme des valeurs et le diviser par le nombre de valeurs qui répondent aux critères. La formulation ressemblerait à ceci : =SOMME((A1:A9>"a")*(B1:B9="bb")*(C1:C9))/SOMME((A1:A9>"a")*(B1:B9="bb")*1).
La seconde partie de la formule calcule le nombre de lignes qui répondent aux critères.
La dernière formulation implique qu’elles peuvent seulement s’appliquer qui on ajoute l’élément ET entre les critères. C’est l’équivalent de (critère1) ET (critère 2). Mais que faire si on veut utiliser l’élément OU entre les critères? La formulation ressemblerait à ceci : =SOMME(((A1:A9>"a")+(B1:B9="bb"))*(C1:C9))
Le problème avec cette dernière formulation est qu’il est possible qu’une ligne réponde aux deux critères, le résultat de la somme des critères serait 2, ce qui fausserait le résultat. Il faut donc ajuster cette formule de cette façon :
=SOMME(SI((A1:A9>"a")+(B1:B9="bb")>0;1)*(C1:C9))
Le résultat est de 37 (2+3+4+5+6+8+9).
La grande majorité des fonctions d’Excel affiche leur résultat dans une seule formule. Mais certaines fonctions peuvent afficher plusieurs informations et requièrent donc plusieurs cellules. Retournez voir les fonctions Droitereg(), Fréquence(), et Transpose() pour avoir des exemples.
Abonnez-vous à l'infolettre
Abonnez-vous à l'infolettre du site pour recevoir le manuel " 150 fonctions Excel expliquées ". Vous recevrez aussi les prochaines infolettres sur les nouveautés du site et des formations détaillées que vous ne trouverez nulle part ailleurs.
Cliquez ici pour voir les produits
Entrez les valeurs dans les cellules appropriées.
