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 :

Valérie Grenon
Écrit il y a 2 semaines
Le rythme est bon, les exemples clairs et pertinents.

C'est ma troisième formation avec le CFO masqué, dont 2 que j'ai fait de façon virtuelle et je les trouve vraiment excellentes.

Chantal Robert
Écrit il y a 2 semaines
Ça m'a permis de découvrir de nouveaux outils plus performants pour mes besoins.

J'ai beaucoup apprécié cette formation. Le matériel de formation est utile et pratique car je m'y réfère parfois en cas d'oubli.

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

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

Scroll to Top