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 de formules dans les tableaux Excel. 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.

 

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.

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.

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.

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.

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

Tableau occurence fréquence

 

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

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

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.

Problème occurence

 

C’est dans ce genre de scénarios qu’on comprend qu’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.

En-tête tableaux

 

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

Formule occurence tableau

 

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

Résolution

 

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 :

Stéphanie Moulin
Écrit il y a 2 mois
J'apprécie grandement l'accès aux vidéos à long terme

Excellente formation, claire, professionnelle et right to the point. J'apprécie grandement l'accès aux vidéos à long terme pour s'y référer lors de l'élaboration de nos futurs tableaux de bord. Merci!

Sylvie Martin
Écrit il y a 2 mois
Merci Sophie pour tes explications claires!

J'ai déjà hâte de mettre en application les connaissances acquises lors de ma formation.

Jean-François Dostie
Écrit il y a 2 ans
excellent

ça rend évident que plusieurs PME sont tout simplement non-performante par rapport à la gestion de leurs données

Bernard Faucher
Écrit il y a 2 ans
Hâte de mettre en application!

Je suis bien content d'avoir suivi cette formation et J'ai hâte de retourner au travail demain pour commencer à la mettre en application 🙂

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

 

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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top