Histogramme avec un mixe libre de barres empilées et non empilées

Dans cette article Un histogramme avec barres empilées et non empilées !, j'ai proposé une solution permettant d'avoir les 2 types d'histogramme dans un même graphique mais cette solution est très limitée.

Voici une solution permettant de choisir librement combien de barres aura l'histogramme et combien de valeurs empilées par barre (catégorie).

Le secret de cette technique

L'astuce vient de la disposition des données et que chaque barre est une barre empilée. Les barres "non empilés" sont constituées d'une valeur et d'une pile de valeurs à 0 donnant l'illusion de ne pas être empilé.

Toutes les barres se touchent (espacement à 0) et les catégories (groupe de barre, ici les années) sont simulées par des barres invisibles ayant toutes leurs valeurs à 0.

Mise en œuvre

La création du graphique est très rapide, c'est la préparation des données (structuration du tableau) qui est longue et fastidieuse !

Structuration des données

Tableau de départ

Tableau servant de base au graphique

Formule de génération du tableau servant au graphique

Bien conscient de la perte de temps que représente la génération d'une telle structure et posant de plus des problèmes de mise à jour, je vous propose une formule faisant tout le travail. 😮

Attention : Les colonnes doivent être triées par groupes à empiler (ici par département : les "Côte-d'Or" ensembles, les "Saône et Loire" ensembles...).

=LET(pl;B1:G5;mp;{0;3;2;1};
p;EXCLURE(pl;1);
t;SOMME(mp);
mpc;SCAN(0;mp;LAMBDA(c;v;c+v))-mp;
scp;SEQUENCE(;t;0);
mm;(scp>=mpc)*(scp<mpc+mp);
n;LIGNES(mm);
s;SEQUENCE(LIGNES(p)*n);
v;INDEX(p;ENT((s-1)/n)+1;scp+1)*INDEX(mm;MOD(s-1;n)+1;scp+1);
ASSEMB.V(PRENDRE(pl;1);v)
)

=LET(pl;B1:G5;mp;{0;3;2;1};
p;EXCLURE(pl;1);
t;SOMME(mp);
mpc;SCAN(0;mp;LAMBDA(c;v;c+v))-mp;
scp;SEQUENCE(;t;0);
mm;(scp>=mpc)*(scp<mpc+mp);
n;LIGNES(mm);
s;SEQUENCE(LIGNES(p)*n);
v;INDEX(p;ENT((s-1)/n)+1;scp+1)*INDEX(mm;MOD(s-1;n)+1;scp+1);
ASSEMB.V(PRENDRE(pl;1);v)
)

Interprétation de la formule

• pl : PLage des données sans la colonne des noms de catégories mais avec les étiquettes de colonnes (noms des séries).
• mp : Matrice Position décrivant la structure des catégories (ici Années) en indiquant les empilements désirés :
  • 0 pour un séparateur de catégorie (barre à 0),
  • 3 pour une barre avec 3 valeurs empilées,
  • 2 pour une barre avec 2 valeurs empilées,
  • 1 pour une barre avec 1 valeur "empilée". 🤪
    Attention : La somme de ces valeurs doit forcément être égale au nombre de colonne du tableau de départ.
• p : Matrice Plage correspondante à "pl" sans les étiquettes de colonnes.
• t : Total des valeurs de "mp" donnant le nombre de colonnes.
• mpc : Matrice Position Colonne correspondant à la conversion de "mp" (nombre de colonnes) en positions de colonne commençant avec un index commençant à 0 (ici 0, 0, 3, 5).
• scp : Matrice Séquence en Colonne de valeurs liée au nombre de colonne de "pl" (ici 0.1.2.3.4).
• mm : Matrice de masque (constituée uniquement de 0 ou de 1).
• n : Nombre de ligne de "mm", correspondant au nombre de valeurs de "mp" (ici 4).
• s : Matrice Séquence en ligne de valeurs liée au nombre de cellules à générer (ici de 1 à 96).
• v : Matrice des valeurs résultant du produit de chaque ligne de "p" avec la matrice "mm" (Produit matriciel de Hadamard / Schur 🤪).
  • ENT((s-1)/n)+1 pour générer une suite 1;1;1...2;2;2...3;3;3....
  • MOD(s-1;n)+1 pour générer une suite 1;2;3...1;2;3...
• Résultat : On assemble la ligne d'entêtes de colonne avec "v".

Création du graphique

1. Sélection des données (le tableau généré par la formule) avec les titres de colonnes.
2. On génère un histogramme empilé.

Remarquer les vides au-dessus des étiquettes 1, 5, 9, 16 qui correspondent aux piles de valeurs à 0 servant de séparateur.

3. On paramètre la largeur de l'intervalle entre les barres à 0 (parfois il faut redimensionner le graphique pour forcer la mise à jour écran)

4. Mise en place des étiquettes de l'axe des abscisses.
   Plusieurs solutions existent, par exemple en modifiant la source des données

C'est terminé ! 🥳

Merci pour votre attention bienveillante.