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-dessous. 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. Dans l’article ci-dessous, nous verrons plutôt comment créer une fonction efficace qui réfère à un tableau Excel.

Problématique de fonction qui réfère à un tableau Excel

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:

À 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 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 se réfère à 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.

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.

Pour revoir les notions de SUMIFS et de SUMIF, je vous invite à relire les articles suivants:

• Fonction Excel: SOMME.SI (SUMIF)
• Excel: SOMME.SI.ENS et NB.SI.ENS (SUMIFS et COUNTIFS)

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

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.

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

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

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

Afin d’approfondir vos connaissances avec les tableaux Excel, nous vous invitons à vous inscrire à notre formation Excel – Tableaux de bord (niveau 2).

Voici quelques commentaires d’apprenants ayant suivi cette formation :

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