Excel : Quelques conseils concernant les fonctions matricielles (array formulas)

Les fonctions matricielles sont adulées par un bon nombre d’utilisateurs Excel et avec raison, puisqu’elles permettent de rédiger des formules simples afin de résoudre des calculs complexes. Toutefois, il faut savoir que l’usage de ces fonctions peut, dans certains cas, affecter la rapidité de calcul d’un fichier Excel, à cause de leur structure, qui réfère souvent à un grand nombre de cellules et de calculs sous-jacents.

Exemples de fonctions matricielles tirées d’articles passés:

• Astuces Excel: Somme.si (sumif) avec plusieurs conditions
• Excel: Compter le nombre de cellules selon divers critères
• Fonction DroiteReg (Linest)

Mise en contexte

Rappelons que les fonctions matricielles nécessitent que l’on appuie sur CTRL+SHIFT+ENTER au lieu de cliquer directement sur ENTER après avoir inséré la formule dans la barre de formules. C’est ce qui ajoute les accolades autour de la formule, comme sur l’image suivante:

Les fonctions matricielles sont appliquées à des plages ou des séries de données plutôt qu’à des cellules individuelles. Il existe deux sortes de fonctions matricielles:

• Les fonctions matricielles qui sont appliquées à des plages ou des séries de données et qui agrègent ensuite ces données par le biais d’une somme, d’une moyenne ou d’un compte, et qui retournent donc un résultat unique dans une cellule unique (voir l’article suivant pour un exemple: Astuces Excel: Somme.si (sumif) avec plusieurs conditions)
• Les fonctions matricielles qui retournent un ensemble de résultats sur une plage de cellules (enfin, sur plus d’une cellule!) (voir article suivant pour un exemple: Fonction DroiteReg (Linest))

Quelques exemples

Dans l’exemple ci-bas, lorsque j’insère la fonction Row(A1:A10) (Ligne en français) dans une seule cellule, j’obtiens 1 lorsque je clique sur Enter directement (i.e., lorsque j’insère une fonction non matricielle) et j’obtiens 1 également lorsque je clique sur CTRL+SHIFT+ENTER (i.e. lorsque j’insère une fonction matricielle). Voir les cellules D4 et D5 dans l’image ci-bas.

Par contre, si j’imbrique cette formule dans une somme (ou sum en anglais), on voit que la fonction matricielle renvoit un résultat de 55 (cellule D8) et la fonction non-matricielle un résultat de 1 (cellule D7). C’est normal puisque la fonction matricielle fait la somme de tous les numéros de lignes désignés par les cellules A1 à A10. D’ailleurs afin d’obtenir les chiffres de 1 à 10 dans la colonne F, j’ai d’abord sélectionné les cellules F4 à F13 et j’en ensuite entré la fonction matricielle de lignes. C’est ce qui a donné les résultats de 1 à 10 que l’on aperçoit dans l’image ci-bas (F4 à F13).

Les fonctions matricielles sont souvent utilisées avec des conditions, comme dans l’exemple ci-bas, où je calcule la moyenne des chiffres de la plage de données F4 à F13, qui sont supérieurs à 5. Vous pouvez jeter un coup d’oeil à la formule utilisée sur l’image ci-bas. Afin de résoudre ce calcul, Excel procède ligne par ligne et retourne un Vrai (True) ou un Faux (False) selon que le critère est rencontré ou non. Ensuite, il fait la moyenne des chiffres dont le critère est rencontré. Évidemment, tout ce que vous voyez, c’est le résultat final, soit le 8, dans une cellule unique.

Dans l’exemple ci-bas, j’ai utilisé les fonctions matricielles afin d’obtenir la somme des ventes de Toyota rouges et le nombre de Toyota rouges vendues. Ici, les fonctions matricielles sont utilisées dans un contexte de critères multiples (plusieurs conditions). À noter qu’il existe une série de conventions dans Excel pour appliquer des logiques de calculs différentes dans les fonctions matricelles (et, ou, etc.).

Habitué de travailler avec Excel et besoin de vous mettre à jour avec les nouvelles fonctionnalités qu’offre maintenant Excel 365 ? Pour concevoir des solutions en phase avec les développements technologiques actuels, jetez un œil à nos formations sur Excel 365.

Maximiser la rapidité de calcul de la fonction

Pour maximiser la rapidité de calcul d’une fonction matricielle, il faut s’assurer que la quantité de cellules et d’expressions évaluées par la fonction soit la plus petite possible. En effet, les fonctions matricielles ressemblent, à certains égards, aux fonctions volatiles, en ce sens que si une des cellules à laquelle réfère la fonction matricielle a changé, est volatile ou est recalculée, la fonction matricielle recalcule toutes les cellules impliquées dans la formule.

Voici quelques trucs afin de maximiser la rapidité de calcul des fonctions matricielles:

• Isoler les expressions et les plages de cellules de référence à l’extérieur des fonctions matricielles et utiliser ensuite une fonction Somme.si (Sumif)
  • C’est ce qui est fait dans l’exemple ci-haut dans les cellules F5 à I13.
• Fusionner tous les critères en un critère unique et utiliser ensuite une fonction Somme.si (Sumif)
• Ne pas faire référence à plus de lignes et de colonnes que nécessaire (les fonctions matricielles recalculent toutes les cellules, même si elles sont vides donc éviter notamment de référer à une colonne entière ou une ligne entière)
• • Utiliser les références sous forme de tableaux lorsque possible (versions Excel 2007 et plus)
    • Voir l’article: Découvrez la magie des tableaux Excel
  • Utiliser les plages de référence dynamiques lorsque possible (versions antérieures à Excel 2007)
    • Voir l’article: Excel: Créer une base de données dynamique sans l’option Mettre sous forme de tableau
• Faire attention aux fonctions matricielles qui réfèrent à la fois à une ligne et une colonne
• Faire attention aux fonctions matricielles qui réfèrent à une partie d’une plage de cellules qui sont calculées via une autre fonction matricielle (ceci arrive souvent avec les séries temporelles)
• Faire attention aux larges sections de fonctions matricielles sur des lignes uniques, formant des colonnes de données, avec une somme au bas de chaque colonne
• Faire attention aux plages de données de référence qui se superposent

Remplacer les fonctions matricielles par d’autres fonctions lorsque possible

Lorsque vous êtes confronté à un problème de performance lié à des fonctions matricielles, demandez-vous si vous ne pourriez pas les remplacer par d’autres types de fonctions. Par exemple, dans un contexte de somme avec critères multiples, vous pourriez choisir une fonction parmi les suivantes:

• Fonction Sommeprod (Sumproduct)
• Fonctions Somme.si.ens (Sumifs), Nb.si.ens (Countifs) et Moyenne.si.ens (Averageifs), à partir de la version Excel 2007
• Fonctions de bases de données comme BDSomme (DSum)

Par exemple, dans l’exemple suivant, j’utilise ces trois fonctions alternatives pour résoudre les 2 mêmes calculs soit les ventes totales de Toyota rouges et le nombre total de Toyota rouges vendues. À noter qu’il est aussi possible d’utiliser la démarche illustrée dans les cellules F5 à I13 mais cette démarche est un peu plus laborieuse.

Un point important à savoir concernant la fonction SommeProd (Sumproduct)

Dans le cas de calculs comme les moyennes pondérées, il faut multiplier une plage de données par une autre plage de données et ensuite en faire la somme. Les 3 fonctions suivantes peuvent être utilisées. Elles produiront toutes le même résultat. Toutefois, on dit que la 3ième alternative, soit la fonction SommeProd (SumProduct) avec la virgule, peut être jusqu’à 25% plus rapide que la première alternative.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour comprendre et maîtriser les nouvelles possibilités offertes par la version Excel d’Office 365, suivez la formation Excel O365 – Bienvenue dans l’ère moderne.

Voici quelques commentaires d’apprenants ayant suivi la formation Excel O365 – Bienvenue dans l’ère moderne :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.