Erreur de formule avec des données en tableaux dans Excel

Vous êtes plusieurs à rencontrer des problèmes typiques dans les tableaux Excel, principalement en lien avec une erreur de formule avec des données mises sous forme de tableau. Cet article sert à expliquer les dangers d’utiliser des fonctions “traditionnelles” dans les tableaux Excel (données mises sous forme de tableau) et les bonnes pratiques pour vous éviter toute problématique éventuelle en lien avec des erreurs de formule Excel.

Calculs de fréquence et d’occurrence

Lorsque vous travaillez directement dans la feuille Excel, vous pouvez utiliser une fonction NB.SI pour savoir combien de fois un élément revient dans une liste. Par exemple, ci-dessous, on utilise une fonction NB.Si pour déterminer combien de fois le client A revient dans la liste.

Grâce aux $ (références absolues), on peut copier la formule et obtenir combien de fois le client B apparaît dans la liste et ainsi de suite. Ce faisant, on obtient 3 dans la colonne Fréquence pour chacune des 3 lignes faisant référence au client B, puisque 3 lignes au total font référence au client B.

Pour calculer l’occurrence, on peut aussi utiliser une fonction NB.SI. Cette fois-ci par contre, on fixera seulement l’une des deux bornes de la plage de référence, soit la cellule de départ, puisqu’on veut savoir à quelle occurrence on est rendu pour le client de cette ligne en particulier.

Ainsi, la première fois que le client B apparaît, on obtient 1, la deuxième fois 2 et la troisième fois 3.

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.

Adapter cette formule aux données mise sous forme de tableaux

Afin d’automatiser les calculs, on pourrait décider de mettre les données sous forme de tableau. Par exemple, ci-dessous, j’ai mis les données sous forme de tableau (option du menu d’accueil) et j’ai ensuite nommé le tableau Occ_Frequ (option du menu contextuel des tableaux).

Je n’ai pas changé les formules. Elles sont demeurées les mêmes.

En ajoutant une nouvelle ligne de données, on aperçoit un triangle vert (Excel vous indique qu’il y a un problème!).

En étudiant la formule, on remarque en effet que la formule de la ligne 13 est incorrecte. Si vous cherchez à corriger le tout avec une formule conventionnelle d’Excel, vous deviendrez fou avant d’avoir réussi :p.

C’est dans ce genre de scénarios qu’on comprend que pour éviter une erreur de formule, il est largement préférable d’utiliser les formules adaptées pour les tableaux Excel. Je vous invite d’ailleurs à relire l’article suivant à ce sujet : Excel: Ce qu’il faut savoir sur les formules appliquées à des données sous forme de tableau

Pour résoudre précisément la problématique du calcul de fréquence, j’ai utilisé les en-têtes de colonnes du tableau. Pour savoir comment faire référence à une en-tête de colonne d’un tableau, allez dans une cellule de votre fichier Excel, insérez le signe “=” et cliquez sur le titre de colonne en question. Vous verrez comment on doit y référer dans la barre de formules.

Sachant que l’on réfère toujours à un élément d’une ligne active avec le symbole @, on peut alors modifier la formule afin qu’elle ressemble à ce qui suit:

Finalement, on peut ajouter des éléments au tableau et les calculs s’effectueront sans problème!

Éviter une erreur de formule en faisant une référence au tableau lui-même

En conclusion, si vous utilisez des formules qui font référence à des cellules et des plages de cellules dans vos tableaux (données mises sous forme de tableau) et que ceci entraîne des problématiques, pensez à utiliser les références du tableau lui-même (en-têtes, totaux, données, colonne, ligne active, etc.).

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