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:
À 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.
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 (somme.si.ens) et de Sumif (somme.si), je vous invite à relire les articles suivants:
- Fonction Excel: Somme.si (sumif)
- Excel: Somme.si.ens et nb.si.ens (sumifs et countifs)
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 function Index/Match ou Index/Equiv, vous pouvez relire l’article suivant: Fonction Excel: Index/Equiv (Index/Match).
NOTRE OFFRE DE FORMATIONS
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]
Jeu de briques
Snake
Pacman
Bubble