Un exemple de formule "complexe" pour pivoter des données malgré des manques.
La connaissance des tableaux dynamiques est nécessaire (Voir Formules de tableaux dynamiques, une nouvelle logique de conception).
D'où l'on part ou où on va.
Tableau de départ
Limité à 6 mois pour simplifier l'exemple.
Tableau final
Une formule unique génère ce tableau.
Difficulté : Les années ne sont pas répétées.
On pourrait facilement compléter les années (voir plus bas Astuce pour remplir facilement les années manquantes dans le tableau de départ) mais on va se débrouiller sans.
Analyse du problème
On va décomposer le résultat attendu en plusieurs étapes.
- Générer la table dynamique des Années (filtrer les années non vides).
- Générer la table dynamique des Mois (supprimer les doublons).
- Générer la table dynamique des Valeurs (créer la table à la bonne dimension en fonction de la liste des Mois et Année et on la remplit).
- Assembler les 3 tables.
Pour structurer et simplifier on va passer par le Gestionnaire de noms pour mémoriser une partie des formules.
La table dynamique des Années
On crée le nom ListeAnnee (Rappel : ruban Formules > Définir un nom).
ListeAnnee> =FILTRE(Tableau1[Année];Tableau1[Année]<>"")Voir La fonction de calcul FILTRE d'Excel.
On filtre les années en supprimant les valeurs vides.
La table dynamique des Mois
On crée le nom ListeMois.
ListeMois> =UNIQUE(Tableau1[Mois])On supprime les doublons de la liste des mois.
La table dynamique des Valeurs
Pour dimensionner la table de Valeurs, on a besoin du nombre de Mois (NbMois) et d'Années (NbAnnees).
NbMois> =NBVAL(ListeMois)
NbAnnees> =NBVAL(ListeAnnee)Pour créer la liste de valeurs on va utiliser la fonction MAKEARRAY et la remplir avec la fonction INDEX.
TableValeurs> =MAKEARRAY(NbMois;NbAnnees;LAMBDA(l;c;INDEX(Tableau1[Valeur];l+(c-1)*NbMois)))Pour informations :
- MAKEARRAY va remplir la table ligne par ligne en incrémentant les 2 arguments de la fonction LAMBDA (ici l et c) de 1 à la valeur indiqué (ici NbMois et NbAnnées).
- l+(c-1)*NbMois permet de chercher les valeurs dans cette ordre 1, 7, 13 puis 2, 8, 14...
On assemble le tout
=ASSEMB.V(
ASSEMB.H("Mois\Année";TRANSPOSE(ListeAnnee));
ASSEMB.H(ListeMois;TableValeurs))Voir Fonctions ASSEMB.V/ASSEMB.H.
- On assemble les étiquettes de colonne ("Mois\Année" et ListeAnnee pivoté en colonne).
- On assemble les étiquettes de lignes (ListeMois) et les valeurs (TableValeurs).
- On assemble enfin ces 2 tables.
Astuce pour remplir facilement les années manquantes dans le tableau de départ
J'en profite pour montrer cette astuce.
- Sélectionner la plage (ici A2 à A19).
- Ruban Accueil > Sélectionner les cellules (groupe Edition) > Cellules vides.
- Saisir = B2 et valider par CTRL + ENTRER.
- On écrase les formules par un copier/coller valeurs de la plage (ici A2 : A19) sur place.
Merci pour votre attention bienveillante.
