Excel : Calculer une fréquence “conditionnelle” dans une plage de données mise sous forme de tableau

Publié le 14 juin 2018
par Sophie Marchand M.Sc., CPA, CGA, MVP
Bonne réponse

Excel : Calculer une fréquence “conditionnelle” dans une plage de données mise sous forme de tableau

L’article suivant m’a été inspiré d’une question posée par une lectrice sur notre forum. Il y a déjà longtemps, quand l’option “Mettre sous forme de tableau” est apparue dans Excel, j’avais remarqué un comportement bizarre mais je l’avais contourné sans me questionner. Suite à une question sur le forum, j’ai soumis une réponse, un peu rapidement, et j’ai à nouveau fait face à la problématique. Cet article vise à expliquer la problématique en question et la façon de l’éviter. Plus précisément, il illustre comment insérer une colonne de fréquence “conditionnelle” dans une plage de données mise sous forme de tableau.

 

Pour en savoir davantage sur la fonctionnalité “Mettre sous forme de tableau”, je vous invite à lire l’article suivant: Excel – Mettre sous forme de tableau.

 

Présentation de la problématique

Ci-dessous, autant dans le premier que le deuxième tableau, nous cherchons à obtenir la fréquence de l’apparition d’une combinaison “nom” et “Cours”. À priori, que l’on utilise la fonction de droite ou la fonction de gauche, on obtient les résultats recherchés.
 
NB.SI.ENS dans un tableau
 
 

Le problème survient lorsqu’on ajoute des lignes à notre tableau. Dans le cas du tableau de gauche, vous verrez une erreur apparaître à l’avant dernière-ligne (voir triangle vert).
 
Début du problème
 
Et le problème s’aggrave lorsque l’on ajoute des lignes supplémentaires.
 
concrétisation du problème
 
 

En effet, si on regarde la formule de la cellule E20, on va s’apercevoir que les plages de données des critères ne s’arrêtent plus à la ligne active, dans ce cas-ci la ligne 20, mais qu’elles incluent l’ensemble des lignes du tableau. Ce faisant, à partir de l’endroit où on s’est mis à ajouter des données dans le tableau, la formule retournera toujours le même résultat pour une même combinaison “Nom” et “Cours”.
 
Erreur
 
 

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

 

Pour éviter le problème, il est donc préférable d’utiliser la formule du tableau de droite. Dans cette formule, les plages de données des critères s’arrêteront toujours à la ligne active. C’est spécifiquement ce que fait le @ dans l’expression @[Colonne].
 
Bonne réponse
 
 


 

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

Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.
 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

 

Mélissa Paquin
Écrit il y a 1 mois
Certains sujets aideront à améliorer les rapports actuels

Formation intéressante. Certains sujets aideront à améliorer les rapports actuels que j'utilise en tant qu'analyste financier.

Roger Martin Nguyen
Écrit il y a 5 mois
Exemples bien expliquées

Exemples bien expliquées et surtout, les exercices sont détaillées avec la démarche à suivre pour arriver au résultat.

Nathalie Tardif
Écrit il y a 5 mois
J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail.

Excellent! J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail. Merci beaucoup!

Lucie Dupuis
Écrit il y a 5 mois
La formation en ligne est idéale

Excellente formation qui donne des trucs pour des situations que nous pouvons et/ou avons rencontré dans notre travail. La formation en ligne est idéale car ça nous permet de réécouter/revoir les explications que nous n'avons pas bien saisi. La durée des modules n'est jamais trop longue.


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

 

8 réflexions sur “Excel : Calculer une fréquence “conditionnelle” dans une plage de données mise sous forme de tableau”

  1. Gérard DEZAMIS

    Bonjour,
    Au début de votre excellente démonstration vous indiquez que vous aviez eu à faire fasse..
    N’y a-t-il pas là un problème de ” face à face” ?
    Bien cordialement
    GD

  2. Bonjour
    Belle solution, j’ignorai qu’il était possible de définir une plage en mélangeant les références $A$9:[@Nom]

    Il reste un inconvénient : la suppression de la ligne 9.

    Personnellement j’utilise beaucoup la fonction DECALER dans les formules de tableau afin d’éviter toute référence en dur à une cellule.
    Par exemple DECALER([@Nom];-1;) pour comparer 2 noms successifs

    Avec l’index de la ligne en cours donné par la formule : LIGNE([@Nom])-LIGNE([Nom])+1
    Votre formule devient
    =NB.SI.ENS(DECALER([Nom];;;LIGNE([@Nom])-LIGNE([Nom])+1;);[@Nom];DECALER([Cours];;;LIGNE([@Nom])-LIGNE([Nom])+1;);[@Cours])

    Pour aller plus loin, si vous voulez que la formule fonctionne si vous filtrez des lignes (par exemple sur les dates) je propose d’ajouter une colonne “Affiché?” avec la formule =SOUS.TOTAL(3;[@Nom])
    et d’ajouter un critère au NB.SI.ENS :
    …;DECALER([Affiché?];;;LIGNE([@Nom])-LIGNE([Nom])+1;);1)

    Quand j’ai besoin de déterminer l’élément affiché précédent, J’utilise une colonne “Index” avec une formule =SOUS.TOTAL(3;[@Nom])*(LIGNE([@Nom])-LIGNE([Nom])+1)
    puis pour cet élément précédent :
    =SIERREUR(INDEX([Nom];MAX(DECALER([Index];;;LIGNE([@Index])-LIGNE([Index]);)));””)
    ou
    =SIERREUR(DECALER([@Nom];MAX(DECALER([Index];;;LIGNE([@Index])-LIGNE([Index]);))-[@Index];);””)
    Cordialement

    Stéphane

    1. Bonjour Stéphane,

      Merci pour vos autres suggestions!

      C’est vrai que si l’on supprime la ligne 9, la formule ne fonctionnera plus. Par contre, il serait très simple de reconstituer l’adresse de la première cellule du tableau avec une fonction Adresse dans une cellule (se basant sur le numéro de ligne et de colonne) à laquelle la formule se réfèrerait. Cette adresse serait donc dynamique. Ce serait beaucoup plus léger que ce que vous proposez. De plus, il faut rappeler que la fonction DECALER est une fonction volatile dans Excel. Donc, bien que fort versatile, lorsqu’il est possible de l’éviter, mieux vaut le faire.

      Au plaisir,

      Sophie

  3. Bonjour Sophie
    DECALER est une fonction volatile ; quels sont les inconvénients d’une telle fonction ?
    Elle n’est peut-être pas évidente à maîtriser mais extrêmement pratique et rapide en particulier pour les mises en forme conditionnelle. Je l’utilise sur des grosses masses de données sans problème (plus de 100 000 lignes de 30 colonnes).
    L’usage d’ADRESSE(LIGNE([Nom]);COLONNE([Nom])) permet de retrouver $A$9 mais il faut ajouter un INDIRECT devant. et INDIRECT est une autre fonction volatile.

    Donc pour comparer une cellule avec celle qui précède si je veux éviter de faire référence à la cellule en question (car #REF en cas de suppression de la ligne) j’ai le choix entre DECALER(Macellule;-1;) et INDIRECT(ADRESSE(LIGNE(Macellule)-1;COLONNE(Macellule))). Je ne sais pas quel est le meilleur choix.
    Cordialement
    Stéphane

    1. Bonjour,

      Concernant votre commentaire ci-dessous, voici quelques retours:

      1 – D’abord, lorsque je mentionne que j’utiliserais plutôt la fonction ADRESSE c’est sans la fonction INDIRECT. Je sais qu’habituellement on utilise les deux ensemble mais ici, c’est différent. Je veux utiliser la fonction ADRESSE seulement pour obtenir l’adresse de la cellule qui débute la plage à sommer. Je pourrais insérer cette fonction dans la cellule juste au-dessus des en-têtes de colonnes de ma table, par exemple et trouver l’adresse avec le numéro de colonne et le numéro de ligne + 2. Si la ligne 9 disparaissait, j’éviterais ainsi la référence.

      2 – La fonction DECALER n’est pas inintéressante ni difficile à comprendre. Je l’enseigne en classe et les apprenants la comprennent plutôt bien. Elle serait toutefois à éviter autant que possible et souvent, on peut la remplacer par d’autres fonctions, comme INDEX, par exemple.

      3 – Les fonctions volatiles sont des fonctions qui forcent le recalcule et “peuvent” engendrer de graves problèmes de performance si on les utilise trop abondamment. Les problèmes engendrés par de telles formules sont largement documentés. Je vous fournis quelques liens ci-dessous. En même temps, si vous avez lu mon article d’hier sur le logiciel OAK, un logiciel qui audite les fichiers Excel, vous verrez qu’il attribue le plus haut risque aux cellules d’un fichier Excel qui utilisent les fonction DECALER et INDIRECT.

      4 – J’utilise personnellement la fonction INDIRECT sur des plages de champs nommés de temps en temps et la fonction DECALER pour calculer de l’amortissement linéaire de façon dynamique mais lorsque je le fais, c’est en toute connaissance de cause et je saurais remplacer ces fonctions par d’autres, en cas de nécessité.

      Liens sur fonctions volatiles:

      Handle Volatile Functions like they are dynamite – https://chandoo.org/wp/handle-volatile-functions-like-they-are-dynamite/
      Handle With Care: Volatile Functions in Excel – https://www.spreadsheetweb.com/handle-with-care-volatile-function-excel/
      Volatile Formulas Detected in Excel – Keep Your Distance – https://trumpexcel.com/excel-volatile-formulas/
      Volatile Functions – What’s the Big Deal? – https://www.vertex42.com/blog/excel-formulas/volatile-functions.html

      Au plaisir,

      Sophie

  4. Bonjour Sophie
    Merci pour votre réponse, je vais regarder ces liens

    Sinon une autre solution pour éviter la référence à la cellule $A$9 est d’utiliser la fonction INDEX dans sa forme référentielle :
    Dans sa forme “habituelle”, la formule INDEX([Nom];1) renverra “Paul” mais en référentielle (avec le :[@Nom]) elle renverra la cellule A9.

    La formule devient
    =NB.SI.ENS(INDEX([Nom];1):[@Nom];[@Nom];INDEX([Cours];1):[@Cours];[@Cours])

    Cordialement
    Stéphane

Laisser un commentaire

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

Scroll to Top