Magazine Informatique

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

Publié le 21 avril 2015 par Sopmar01 @mon_cher_watson

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, cans 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:

Fonctions matricielles : 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:

Excel: Fonctions matricielles

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))

Fonctions matricielles: 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.

Fonction matricielle Excel

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).

Fonction matricielle Excel

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.

Fonction matricielle Excel

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.).

Fonction matricielle Excel

Maximiser la rapidité de calcul d’une fonction matricielle

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éfence à 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.

Fonction matricielle Excel

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.

Fonction matricielle vs sommeprod

NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Consulter la liste des formations offertes

Pour info: 514-605-7112 ou [email protected]


Retour à La Une de Logo Paperblog

A propos de l’auteur


Sopmar01 3934 partages Voir son profil
Voir son blog

l'auteur n'a pas encore renseigné son compte

Dossiers Paperblog