Manipuler efficacement les tableaux Excel

Publié le 05 novembre 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Fonction Excel Indirect

Manipuler efficacement les tableaux Excel

L’inspiration pour cet article provient directement d’une question posée sur notre forum. Paul cherchait à obtenir une formule unique pour calculer les résultats des cases D14 et D15 de son exemple, qui sera présenté ci-bas. Le problème, c’est que les calculs des cellules D14 et D15 font référence à des champs de “tableaux” Excel. Pour revoir les notions de “tableaux” dans Excel, je vous invite à relire l’article suivant: Découvrez la magie des tableaux Excel.

 

Chaque trimestre, le CFO masqué remet une formation en ligne gratuite à la personne ayant été la plus active et la plus pertinente sur le forum.

 

Problématique

Dans son exemple, Paul permet d’abord aux usagers de son fichier, de sélectionner un numéro de salle. Ensuite, il introduit une fonction Nb.si (ou Countif en anglais) pour calculer le nombre de sociétés rattachées à ce numéro de salle. Ensuite, Paul souhaite introduire une formule unique pour calculer les recettes et les charges de cette salle, par société. Son premier réflexe a donc été de rédiger la formule suivante:

Fonction Excel Indirect

À noter d’abord que la fonction SumIfs se traduit par Somme.si.ens en français et ensuite, qu’on aurait pu utiliser simplement la fonction Sumif (Somme.si en français) pour effectuer ce calcul (nous y reviendrons plus loin). À noter également que la table de référence, dans cet exemple, a été nommeé “bdd”.

 

Solution possible: Fonction Indirect

Lorsque l’on crée une fonction qui doit lire dans un tableau Excel, ce sont les noms de champs qui apparaissent dans la formule et non les coordonnées des cellules. On ne peut donc pas faire intervenir les concepts de cellules relatives, absolues et mixtes. Ainsi, pour contourner l’aspect “hardcoding” des références aux tableaux dans une formule, on pourra utiliser la fonction Indirect, tel qu’exposé ci-bas.

Fonction Excel Indirect

 

Pour revoir la fonction Indirect en action, vous pouvez relire l’article: Excel: La fonction Indirect pour créer des sommaires exécutifs en un clic de souris.

 

Sumifs (somme.si.ens) ou Sumif (somme.si)

Dans l’exemple présenté par Paul, une simple fonction Sumif (somme.si en français) aurait suffit puisque la somme ne repose que sur un seul critère.

Fonction Excel Indirect

 

Pour revoir les notions de Sumifs (somme.si.ens) et de Sumif (somme.si), je vous invite à relire les articles suivants:

 

Autre solution possible: Fonction Index

Toutefois, la fonction Indirect d’Excel est volatile et pour cette raison, vous souhaiterez peut-être utiliser une fonction non volatile pour parvenir au même résultat, surtout si vous travaillez dans un gros fichier. Dans ce cas, vous pourriez plutôt utiliser la fonction Index et Match (Equiv en français), tel qu’illustré ci-bas.

Tableaux Excel

 

Dans le contexte de Paul, voici de quoi cette formule aurait eu l’air.

Index tableau Excel

 

À noter que la function Match correspond à Equiv en français. Pour revoir comment utiliser la function Index/Match ou Index/Equiv, vous pouvez relire l’article suivant: Fonction Excel: Index/Equiv (Index/Match).

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

6 réflexions sur “Manipuler efficacement les tableaux Excel”

  1. BJR Sophie,
    Ceci concerne un type particulier de tableau: les matrices.
    J’ai à élever une matrice carrée 47×47 (matrice de Markov) à des puissances Pn. J’utilise la formule “produitmat”. La taille des matrices acceptées par cette formule est limitée à 5640 valeurs. Sauf… au-delà du carré! Le produit de la matrice élevée au carré multipliée par la matrice initiale, soit P3, donne “valeur”. J’ai réussi à contourner la difficulté en travaillant sur des copies “chiffres seuls”. Mais c’est très lourd. Existe-t-il une procédure plus rapide?
    Ce cas est sans doute peu fréquent. Néanmoins le calcul matriciel est puissant et souvent indispensable pour traiter des données nombreuses et reliées ou alternatives. Je ne joins pas ici la matrice en cause car encombrante et lourde. Je le ferais à ta demande.

    Merci encore pour tout ce que j’ai appris à ta lecture.

  2. Bonjour Sophie,

    Je trouve cette solution intéressante. Je connais INDEX + EQUIV mais je ne l’ai jamais imbriqué dans une fonction SOMME.SI.ENS. Elle a en revanche l’inconvénient que la formule est longue.
    Vous dites que la fonction indirect est “volatile”, pourquoi ?

    Paul

    PS : merci pour la citation

    1. Bonjour Paul,

      C’est un fait connu et documenté par Microsoft. La fonction Indirect, comme la fonction Offset (Decaler) par exemple, sont des fonctions volatiles. Dans de petits fichiers, personne n’y observera de différence mais dans de très gros fichiers, ces fonctions peuvent considérablement nuire à la performance puisqu’elles requièrent notamment que tout soit recalculé.

      Au plaisir,

      Sophie

  3. Nathalie GOURGOUILLON

    Bonjour
    Pour quoi la fonction indirect semble ne pas marcher dans les graphiques ?
    je veux remplacer
    =SERIE([PAGE7_69.xls]DEE!C2;[PAGE7_69.xls]DEE!H2:H49;[PAGE7_69.xls]DEE!B38:B49;3)
    par
    =SERIE(INDIRECT(“[PAGE7_”&DPT&”.xls]DEE!C2″); INDIRECT(“[PAGE7_”&DPT&”.xls]DEE! H2:H49″); INDIRECT(“[PAGE7_”&DPT&”.xls]DEE!B38:B49;3)
    pour paramétrer mon numéro de département
    et j’obtiens “fonction invalide” au final
    Bien entendu mon classeur de données est ouvert.
    Merci de votre aide

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top