Excel: Erreur ❌ de formule avec des données mises sous forme de tableau

Publié le 22 août 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Image fréquence

Excel: Erreur ❌ de formule avec des données mises sous forme de tableau

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.

 

Erreurs de formules - Formule Fréquence

 

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.

 

Erreurs de formules - Formule fréquence 2

 

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.

 

Erreurs de formules - Formule occurence 1

 

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.

 

Erreurs de formules - Formule occurence 2

 

 

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

 

Erreurs de formules - Tableau occurence fréquence

 

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

 

Erreurs de formules - Tableau fréquence

 

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

 

Erreurs de formules - Triangle vert

 

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.

 

Erreurs de formules - Problème occurence

 

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.

 

Erreurs de formules - En-tête tableaux

 

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:

 

Erreurs de formules - Formule occurence tableau

 

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

 

Erreurs de formules - Résolution

 

 

É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 :

Le CFO masqué - Commentaires formation - Excel - Tableaux de bord (niveau 2)
CFO-Masque_Formations-en-ligne_FBLa 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é.

5 réflexions sur “Excel: Erreur ❌ de formule avec des données mises sous forme de tableau”

  1. Bonjour Sophie,
    Merci ce petit rappel très très loin d’être inutile.
    Par contre, après un test, il semblerait que l’on ne puisse pas utiliser les références structurées dans les mises en forme conditionnelles.
    Ou alors, je me suis trompé dans la syntaxe ….
    Cordialement,
    Laurent

  2. Bonjour Sophie,
    Merci pour cet article très sympa intéressant et ô combien nécessaire. Je me souviens avoir rencontré le problème auparavant et l’avoir contourné d’une manière pas forcément optimale. Ma question désormais est :est-il possible d’avoir le même article sur powerquery ? En gros comment compter les occurrences en M? Merci d’avance

Laisser un commentaire

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

Scroll to Top