Bonjour

Je ne suis pas sûr que vous avez déjà digéré ce que nous avons vu hier (cliquer ici): rapport dynamique mensuel avec zone de liste déroulante. Aujourd’hui, j’aimerai qu’on aille un pas plus haut avec le même travail mais plutôt de manière trimestriel.

Nous partirons toujours du tableau initial ci-dessous:

 

Nous voulons avoir comme résultat de pouvoir retrouver les valeurs trimestrielles (sommées) par nom et classées par ordre décroissant. Un peu comme ci-dessous:

 

Ne croyez surtout pas que c’est comme dans le précédent article. Voici les armes dont vous aurez besoin pour réussir cela:

• Savoir nommer rapidement une plage de cellule;
• Insérer une zone de liste déroulante;
• La connaissance des formules matricielles est bienvenue;
• Les formules logiques
• Et bien sûr une tasse de café, peut-être même deux tasses de café

 Je vous conseille tout de même de bien lire l’article précédent avant de se jeter dans cet article. En effet je ne vais pas revenir en détail sur les éléments similaires.

 

Allé on y va! 

• Nommer les plages trimestre1, trimestre2…trimestre4:

  • Sélectionner le tableau (sans la ligne des totaux): A3:M10
  • Dans le ruban, Formules/Nom définis/Depuis la sélection
  • Dans la nouvelle boite de dialogue, cocher uniquement ligne du haut, comme ci-dessous

 

Avec cette action nous avons créé les noms: Janv, Févr, Mars… Déc . Maintenant nous allons créer la liste de texte qui entrera dans notre zone de liste déroulante

• Se mettre par exemple en D15 écrire Trimestre1 , en D16 Trimestre2 jusqu’à Trimestre4 en D18
• Sélectionner D15:D18, dans la zone de nom écrire Lestrimestres

 

Maintenant nous allons nommer les plages de cellules qui correspondent à trimestre1, 2, 3 et 4. Huum, une petite gorgée de café serait un bonne idée maintenant

• Dans le ruban, Formules/Noms définis/Définir un nom
• Dans boite de dialogue écrire simplement dans Noms:Trimestre1 et dans Fait référence à, écrire: =Janv+Févr+Mars (regardez ci-dessous)

 

 

N’oublions pas que nous avons déjà nommé les plages de cellules Janv, Févr et Mars respectivement par B4:B10, C4:C10, D4:D10. Et nous avons fait pareil pour les autres mois

• Ensuite on nommera Trimestre2 par =Avri+Mai+Juin
• Trimestre3 par =Juil+Août+Sept
• Trimestre4 par = Oct+Nov+Déc

 En fait, il s’agit d’une formule matricielle. Elle considérera la somme de trimestre et la rangera dans une matrice « fictive ».

 

• Insérer la zone de liste déroulante dans une autre feuille

  • Dans le ruban, Développeur/Insérer/Contrôles/ cliquer sur Zone de liste déroulante
  • Avec la souris positionnez-la dans la nouvelle feuille
  • Cliquez dessus avec le bouton droit de la souris et choisissez « Format de contrôle »
  • Remplissez cette boite de dialogue comme ci-dessous

 

 

• Retrouver les meilleurs valeurs par trimestre

(Je pense que c’est le moment de prendre une…non plusieurs autres gorgées de café)

Pour y arriver, la 1ère étape sera d’indiquer  à Excel qu’on a choisi Trimestre1 ou trimestre2… ou trimestre4, ensuite on fera une formule pour retrouver les valeurs les plus élevées pour chaque trimestre.

En B1, B2, B3, B4 et B5, on va mettre des formules qui diront respectivement  si on a choisi Trimestre1, 2, 3 et 4. Pour cela la formule donnera comme résultat 1 pour oui et 0 pour non dans chaque cellule.

• Ecrire en B1: =(A1=1)*1 (ainsi lorsqu’on aura choisi dans la liste déroulante Trimestre1, la cellule B1 donnera comme résultat 1 et 0 si ce n’est pas le cas)
• Ecrire en C1: =(A1=2)*1, en D1: =(A1=3)*1 et en E1: =(A1=4)*1

Bien sûr toutes ces formules peuvent être remplacées par la fonction SI(). Pour bien comprendre cette astuce avec les formules logiques cliquer ici.

Nous allons mettre en C8:C14 les montants classés par ordre décroissant par trimestre choisi.

• En C8 on insèrera la formule

 =GRANDE.VALEUR(Trimestre1*$B$1+Trimestre2*$C$1+Trimestre3*$D$1+Trimestre4*$E$1;LIGNE()-7) et valider avec Ctrl+Maj+Entré

• Incrémenter cette formule jusqu’en C14

Quelques explications:

La fonction GRANDE.VALEUR() comme nous l’avons vu dans l’article précédent permet de retrouver la kième plus grande valeur. Sa syntaxe: Grande.valeur(plage_de_recherche;kième). Dans la formule ci-dessus, Ligne()-7 permet d’avoir comme résultat 1, puis 2, 3…7 représentant respectivement le 1er, 2ème… 7ème plus grande valeur.

Trimestre1*$B$1+Trimestre2*$C$1+Trimestre3*$D$1+Trimestre4*$E$1 cette partie permet de savoir quel trimestre on a choisi. En effet tout à l’heure on a mis les valeurs de B1, C1, D1, E1 comme 0 ou 1 selon le choix du trimestre.

Et pour finir on valide avec Ctrl+Maj+Entrée, ce qui nous permet de garder cette formule comme une matrice.

 

• Retrouver les noms des valeurs

  • En B8 on insèrera la formule:

=INDEX(Noms;EQUIV(Feuil3!C8;Trimestre1*$B$1+Trimestre2*$C$1+Trimestre3*$D$1+Trimestre4*$E$1;0)) qu’on valide avec Ctrl+Maj+Entrée

• Cette formule sera incrémentée jusqu’en B14

 

Pour la mise en forme conditionnelle, nous utiliserons exactement la même technique que dans l’article précédent.

Voici le résultat final:

 

Une dernière gorgée de café pour la route?

 

Pour télécharger le fichier, cliquer ici ou aller dans Ressources/Téléchargements/Techniques Tdb et choisir Rapport dynamique trimestriel.

Si j’ai réussi à être super clair, tant mieux. Sinon n’hésitez pas à utiliser les commentaires pour poser des questions .

N’hésitez pas à apprendre la technique des 3T (tableau, tableaux croisés dynamiques, tableau de bord) pour construire facilement des tableaux de bord dans Excel dans mes ebooks: « Tableau de bord commercial » (cliquer ici) et « Booster votre reporting financier » (cliquer ici).