Colorer la partie inférieure d'une courbe lissée, c'est possible !

Dans Excel, pour colorer l'aire sous une courbe (non lissée), il n'y a qu'une solution disponible : Les graphiques de type Aires.

Ce sont des graphiques dont la courbe, en segments de droites, ne peut pas être lissée et utilisant des catégories pour l'axe des abscisses.

L'objectif de cet article est de proposer une solution permettant d'obtenir des graphiques de type Aires avec une courbe lissée et même d'utiliser un tracé de type Nuage de points (abscisse "standard" et non en catégories) pour générer la courbe de graphique.

Je pars du principe que vous êtes capable d'ajouter des nouvelles séries/tracés à un graphique, modifier le type des graphiques..., me concentrant que sur le principe des manipulations.

Situation 1 : Graphique de type Courbe non lissée

Il suffit d'utiliser un graphique de type Aires classique.

Remarquer l'axe horizontal constitué de catégories (ici de prénoms). On a le même écartement horizontal entre chaque catégorie/point.

Graphique de type Nuage de points

Ici l'axe horizontal sera constitué d'abscisses. Nous n'aurons pas forcément le même écartement horizontal entre les points.

! Il faudra que le tableau de coordonnées soit trié en ordre croissant sur la colonne des abscisses (abscisses en ordre classique).

Situation 2 : Courbe en Nuage de points non lissé

Remarquer l'axe horizontal des abscisses où l'écartement entre chaque point n'est pas égal.

Problématique

• Les graphiques en Aires d'Excel utilise des catégories pour l'axe horizontal donc il ne semble pas être utilisable ici (une solution plus simple permettant l'utilisation d'un graphique en Aires est cachée plus bas 😁 !).
• Il n'existe pas d'outils dans Excel permettant d'appliquer une couleur dans la zone sous la courbe d'un graphique en Nuage de points.

Principes de la solution proposée

1. On ne va pas utiliser cette courbe en Nuage de points mais en créer une nouvelle (toujours en Nuage de points) avec de nombreux points intermédiaires (nombre à adapter : dépendant de la largeur du graphique et du nombre de points de départ, de l'échelle des abscisses).
2. Le remplissage sera obtenu par le tracé de barre d'erreurs suffisamment nombreuse (lié au nombre de points de notre nouvelle courbe) et suffisamment larges/épaisses pour remplir horizontalement toute la zone inférieure à la courbe.

La création d'une courbe "zigzagante" sous la courbe pour le remplissage est aussi possible. Cette solution nécessitait la création d'une série de données beaucoup plus importante que la solution présenté ici (pour ne pas laisser de "blancs"), je l'ai donc écartée.

Formule de génération des points

La formule génère une matrice d'abscisses et d'ordonnées à partir de la plage des coordonnées de la courbe de base (ici abscisses en B2:B7 et ordonnées en C2:C7) ainsi que le "Pas" de subdivision des abscisses à générer (ici 0,05).

=LET(mx;B2:B7;my;C2:C7;px;0,05;
  mxg;SEQUENCE((MAX(mx)-MIN(mx))/px+1;;MIN(mx);px);
  myg;MAP(mxg;LAMBDA(x;LET(p;EQUIVX(x;mx;-1);
  y_1;INDEX(my;p);
  x_1;INDEX(mx;p);
  y_1+(x-x_1)*(INDEX(my;p+1)-y_1)/(INDEX(mx;p+1)-x_1))));
  ASSEMB.H(mxg;myg)
)

Interprétation de la formule

• mxg : Matrice séquence des abscisses partant de la plus petite abscisse à la plus grande par pas égale à px.
• myg : Matrice des ordonnées correspondantes aux valeurs de mxg calculée via une interpolation linéaire (voir Interpolation linéaire).
  • On utilise : y = Y1 + (x - X1) * (Y2 - Y1) / (X2 - X1)
• Résultat : Matrice constituée des 2 matrices mxg et myg.

Résultat de la formule

Tableau de départ

Tableau des résultats

Réalisation du graphique

• Si l'on veut afficher les marques des points (les triangles bleus dans l'image d'illustration), on garde la série mais on supprime le trait de la série/courbe d'origine (pas la série !).
• On ajoute la nouvelle série/courbe générée par la formule au graphique.
• On ajoute des barres d'erreur verticales inférieurs à cette nouvelle série.
  
• On supprime les barres d'erreur horizontales (sélection puis Supp).
• On sélection les barres d'erreur verticales puis via le volet Format on les paramètre :
  • Pour le sens, on sélectionne Moins (barres inférieurs) et l'option Sans Empâtements.
  • En Marge d'erreur (valeurs), via Personnalisé, on sélectionne la plage de ordonnées de la nouvelle série en tant que valeurs négatives.
  • On choisit la couleur de barres qui sera la couleur de l'aire sous la courbe.
  • On choisit leur épaisseur afin qu'elles se touchent horizontalement, remplissant ainsi la partie inférieure de la courbe.
    • Pour masquer l'effet de crénelage du départ des barres, il suffit d'augmenter l'épaisseur du tracé de la nouvelle courbe (pour le rendre "invisible", lui donner la même couleur que les barres d'erreurs).
    • Si cette épaisseur devient trop importante, il faudra augmenter le nombre de points du tracé (diminuer la valeur de px dans la formule).

Dans ce cas précis (non lissé), il existe une solution beaucoup plus simple que celle présentée mais celle-ci me sert d'introduction pour présenter la solution pour les cas des courbes lissées.

Solution directe plus simple (disponible que pour un type Nuage de points NON lissée !)

Nous allons indiquer à Excel que nos abscisse sont des dates même si ce n'est pas vrai (évident !).

1. On fait notre graphique en utilisant le type Aires (Oui c'est bien ça, avec des catégories en axe horizontale).
   • On sélectionne seulement les ordonnées pour créer le graphique.
   • Dans un 2ème temps, via Sélectionner des données (ruban Création de graphique), on indique comme étiquette pour l'axe Horizontale la plage de abscisses.
     => Les abscisses s'affichent les une derrière les autre avec le même écartement (se sont des catégories pour Excel).
2. On sélectionne l'axe Horizontale et le paramètre avec l'option "Date sur les axes".

C'est terminé, les catégories s'espacent en fonction de la valeur des abscisses !

Situation 3 : Courbe en Nuage de points lissé

Problématiques

1. Il n'existe pas d'option de lissage pour les graphiques de type Aires.
2. Ne connaissant pas l'algorithme de lissage utilisé par Excel, on ne peut déterminer les limites de la zone à colorer.
3. Même en connaissant cette limite, il n'y a pas d'outils pour appliquer cette couleur dans cette zone.

Principes de la solution proposée

1. On ne va pas utiliser le lissage intégré mais créer le nôtre. Pour cela nous allons générer un graphique en Nuage de points correspondant au lissage que nous aurions obtenu avec l'option standard. Ce sera une série de coordonnées correspondant aux points de la courbes et a de nombreux points intermédiaires (nombre à adapter, dépendant de la largeur du graphique et du nombre de points de départ).
2. Le remplissage sera obtenu là aussi par le tracé de barre d'erreurs suffisamment nombreuse (lié au nombre de points de notre lissage) et suffisamment larges/épaisses pour remplir toute la zone inférieure.

Formule de génération des points

La formule génère une matrice d'abscisses et d'ordonnées à partir de la plage des coordonnées de la courbe de base (ici abscisses en B2:B7 et ordonnées en C2:C7) ainsi que le "Pas" de subdivision des abscisses à générer (ici 0,05).

=LET(mx;B2:B7;my;C2:C7;px;0,05;
  n;NB(mx);
  sn;SEQUENCE(n);
  sn_1;SEQUENCE(n-1);
  h;MAP(sn_1;LAMBDA(i;INDEX(mx;i+1)-INDEX(mx;i)));
  d;MAP(sn_1;LAMBDA(i;(INDEX(my;i+1)-INDEX(my;i))/INDEX(h;i)));
  m;MAP(sn;LAMBDA(i;SI(i=1;
                       INDEX(d;i);
                       SI(i=n;
                          INDEX(d;i-1);
                          SI((INDEX(d;i-1)*INDEX(d;i))>0;
                             (2*INDEX(d;i-1)*INDEX(d;i))/(INDEX(d;i-1)+INDEX(d;i));
                             0)))));
  mxg;SEQUENCE((MAX(mx)-MIN(mx))/px+1;;MIN(mx);px);
  myg;MAP(mxg;LAMBDA(x;LET(
    p;EQUIVX(x;EXCLURE(mx;-1);-1;1);
    h_j;INDEX(mx;p+1)-INDEX(mx;p);
    t;(x-INDEX(mx;p))/h_j;
    ((1+2*t)*INDEX(my;p)+t*h_j*INDEX(m;p))*(1-t)^2+((3-2*t)*INDEX(my;p+1)+(t-1)*h_j*INDEX(m;p+1))*t^2
)));
ASSEMB.H(mxg;myg)
)

Interprétation de la formule

Je crains que ça ne soit pas passionnant donc je ne vais pas développer les explications 😁.
Pour les curieux :

• Interpolation d'Hermite
• Spline cubique d'Hermite

Formule "assez simple" par rapport à ma version 1 (sans Hermite, dérivées non contraintes).

=LET(mx;$B$2:$B$7;my;$C$2:$C$7;px;0,05;
  n;NB(mx);
  sn;SEQUENCE(n);
  sn_1;SEQUENCE(n-1);
  snr;SEQUENCE(n;;n;-1);
  h;EXCLURE(ASSEMB.V(EXCLURE(mx;1);0)-mx;-1);
  a;MAP(sn_1;LAMBDA(i;SI(i=1;
                         0;
                         (3/INDEX(h;i)*(INDEX(my;i+1)-INDEX(my;i)))
                             -(3/INDEX(h;i-1)*(INDEX(my;i)-INDEX(my;i-1))))));
  f;SCAN(0;sn;LAMBDA(cu;i;cu-cu+SI(OU(i=1;i=n);
                                   1;
                                   2*(INDEX(mx;i+1)-INDEX(mx;i-1))
                                       -INDEX(h;i-1)*SI(i=2;0;INDEX(h;i-1)/cu))));
  mu;MAP(sn;LAMBDA(i;SI(OU(i=1;i=n);0;INDEX(h;i)/INDEX(f;i))));
  z;SCAN(0;sn;LAMBDA(c;i;c-c+SI(OU(i=1;i=n);0;(INDEX(a;i)-INDEX(h;i-1)*c)/INDEX(f;i))));
  ic;SCAN(0;snr;LAMBDA(cu;i;cu-cu+SI(i=n;0;INDEX(z;i)-INDEX(mu;i)*cu)));
  c;MAP(snr;LAMBDA(i;INDEX(ic;i)));
  b;MAP(sn_1;LAMBDA(i;(INDEX(my;i+1)-INDEX(my;i))/INDEX(h;i)
                          -INDEX(h;i)*(INDEX(c;i+1)+2*INDEX(c;i))/3));
  d;MAP(sn_1;LAMBDA(i;(INDEX(c;i+1)-INDEX(c;i))/(3*INDEX(h;i))));
  mxg;SEQUENCE((MAX(mx)-MIN(mx))/px+1;;MIN(mx);px);
  myg;MAP(mxg;LAMBDA(x;LET(
  p;EQUIVX(x;EXCLURE(mx;-1);-1;1);
  X_1;INDEX(mx;p);
  X_2;INDEX(mx;p+1);
  Y_1;INDEX(my;p);
  Y_2;INDEX(my;p+1);
  Y_1+INDEX(b;p)*(x-X_1)+INDEX(c;p)*(x-X_1)^2+INDEX(d;p)*(x-X_1)^3)));
  ASSEMB.H(mxg;myg)
)

Résultat de la formule

Tableau de départ

Tableau des résultats

Réalisation du graphique

• Si l'on veut afficher les marques des points (les ronds orange dans l'image d'illustration), on garde la série mais on supprime le trait de la série/courbe d'origine (pas la série !).
• On ajoute la nouvelle série/courbe générée par la formule au graphique.
• On ajoute les barres d'erreur verticales inférieurs à cette nouvelle série.
  • On supprime les barres d'erreur horizontales.
  • On sélection les sens Moins (barres inférieurs) et l'option Sans Empâtements.
  • En Marge d'erreur (valeurs), via Personnalisé, on sélectionne la plage de ordonnées de la nouvelle série en tant que valeurs négatives.
  • On choisit la couleur de barres qui sera la couleur de l'aire sous la courbe.
  • On choisit leur épaisseur afin qu'elles se touchent horizontalement, remplissant ainsi la partie inférieure de la courbe.
    • Pour masquer l'effet de crénelage du départ des barres, il suffit d'augmenter l'épaisseur du tracé de la nouvelle courbe (pour le rendre "invisible", lui donner la même couleur que les barres d'erreurs).
    • Si cette épaisseur devient trop importante, il faudra augmenter le nombre de points du tracé (diminuer la valeur de px dans la formule).

Situation 4 : Graphique de type Courbe lissée

(Les étiquettes de graduation du haut et à droite ont été laissées afin de faciliter la compréhension)

Problématiques supplémentaires

On va devoir "mélanger" pour cela un graphique de type Courbe (points de départ) avec un graphique de type Nuage de points (nouvelle série générant le lissage comme précédemment).

Si le nombre de points du tracé en Nuage de points est égale au nombre de catégories du tracé de type Courbe, ce genre de "superposition" est facile (tous les points s'alignent bien).

Malheureusement dans notre cas, nous allons devoir générer ne nombreux points intermédiaires. Il va donc falloir utiliser l'axe X secondaire (activable après activation de l'axe Y secondaire).

Il y aura aussi un problème de superposition des séries si on laisse les marques des points (les triangles verts de l'image seront partiellement masqués).

Principes de la solution proposée

La même chose que précédemment.

Il faudra agir sur l'axe de X secondaire afin de recaller horizontalement le nouveau tracé (début à 0,5, fin à "dernière catégorie + 0 , 5" 🤪 =< ici catégorie 7 donc 7,5).

Pour les marques du tracé de type Courbe, il faudra utiliser un autre tracé en Nuage de points pour pallier un problème de superposition d'affichage.

Formule de génération des points

La formule génère une matrice d'abscisses et d'ordonnées à partir de la plage des valeurs de la courbe de base (ici C2:C8) ainsi que le "Pas" de subdivision des abscisses à générer (ici 0,05).

=LET(my;C2:C8;px;0,05;
  n;NB(my);
  sn;SEQUENCE(n);
  sn_1;SEQUENCE(n-1);
  h;MAP(sn_1;LAMBDA(i;INDEX(sn;i+1)-INDEX(sn;i)));
  d;MAP(sn_1;LAMBDA(i;(INDEX(my;i+1)-INDEX(my;i))/INDEX(h;i)));
  m;MAP(sn;LAMBDA(i;SI(i=1;
                       INDEX(d;i);
                       SI(i=n;
                          INDEX(d;i-1);
                          SI((INDEX(d;i-1)*INDEX(d;i))>0;
                          (2*INDEX(d;i-1)*INDEX(d;i))/(INDEX(d;i-1)+INDEX(d;i));
                          0)))));
  mxg;SEQUENCE((n-1)/px+1;;MIN(sn);px);
  myg;MAP(mxg;LAMBDA(x;LET(
    p;EQUIVX(x;EXCLURE(sn;-1);-1;1);
    h_j;INDEX(sn;p+1)-INDEX(sn;p);
    t;(x-INDEX(sn;p))/h_j;
    ((1+2*t)*INDEX(my;p)+t*h_j*INDEX(m;p))*(1-t)^2+((3-2*t)*INDEX(my;p+1)+(t-1)*h_j*INDEX(m;p+1))*t^2
)));
ASSEMB.H(mxg;myg)
)

Interprétation de la formule

• La matrice mx de la formule précédente est remplacée par la matrice séquence de valeurs sn.

Résultat de la formule

Présentation identique à la formule précédente.

Réalisation du graphique

Comme précédemment avec les éléments supplémentaires suivants :

• Pour la nouvelle série on active l'axe Y secondaire, ce qui permet d'ajouter l'axe X secondaire (procéder comme pour les barres d'erreurs).
  
• On modifie les graduations de l'axe X secondaire afin de commencer à 0,5 et de se terminer une demi-graduation après le nombre de catégorie (ici de 0,5 à 7,5).
• Pour les marques du tracé de type Courbe :
  • Étant en partie masqué par le graphique en Nuage de points, on les supprime tout simplement.
  • On ajoute un nouveau graphique en Nuage de points (axe secondaire) utilisant comme abscisse de valeur de 1 à nombre de catégorie (ici de 1 à 7) et en ordonnées les valeurs de chaque catégorie (valeur de la courbe). On ne gardera affiché que les marques de ce tracé.
• Finalement, le graphique de type Courbe n'est pas visible (pas de bordure ni de marques), il ne sert qu'à générer les catégories affichées sur l'axe des abscisses.

Merci pour votre attention bienveillante.