Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 22 août 2019

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

 

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

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils 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.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse Laurent ,

    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

    • réponse Thibault ,

      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

      • réponse CompteAdminCFO ,

        Bonjour,

        Oui, bien sûr. Je vais écrire un article sur le sujet bientôt. Surveillez notre blogue.

        Au plaisir,

        Sophie

      • réponse Emmanuelle ,

        Génial pour déboguer!
        Merci

        Laisser un commentaire