La combinaison puissance
Combinant les formules SUMIF et OFFSET, une liste de validation et une zone de liste déroulante pour renvoyer un résumé des données à sélectionner par mois – vraiment une combinaison de puissance!
Étape 1: Sélection des éléments dans les listes de validation
Sélection d’un élément dans une liste de validation (colonne A dans la capture d’écran)
permet aux formules entrées dans les cellules des colonnes C et D d’identifier le texte et de renvoyer les résultats récapitulatifs à partir d’une colonne qui contient les critères (l’élément choisi) pour le mois sélectionné dans la zone de liste déroulante.
Pour ajouter une liste de validation à une plage de cellules:
-
Sélectionnez la plage de cellules (dans la capture d’écran de la page précédente, les cellules sélectionnées sont A12: A15)
-
Sélectionnez les cellules A19: A23.
-
Dans le menu Données, sélectionnez Validation.
-
Dans la boîte de dialogue Validation des données, sélectionnez l’onglet Paramètres et sélectionnez Liste dans la zone Autoriser.
-
Dans la zone Source, appuyez sur F3, sélectionnez le Nom défini pour la liste (Level3 dans cet exemple, voir la capture d’écran sur la dernière page de cette astuce) et cliquez sur OK.
Étape 2: Saisie des formules qui renvoient les soldes récapitulatifs des éléments choisis La formule dans les cellules C12: C15 est:
SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2))
La formule dans les cellules D12: D15 est:
SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2-12))
Explication:
La formule SUMIF de la colonne C résume les montants des soldes de la colonne de décembre 2003; la formule SUMIF de la colonne D résume les montants des soldes de la colonne de décembre 2002.
La formule SUMIF a trois arguments:
Premier argument:
La plage à évaluer selon les critères saisis dans le deuxième argument de la formule SUMIF. Dans cet exemple, la plage est DataLevel3, qui est le nom défini pour la colonne C dans la base de données du bilan. Il contient des éléments de niveau 3 du bilan, tels que la trésorerie, les comptes clients, les stocks, etc.
Deuxième argument:
Le critère est l’élément choisi dans la liste de validation Level3.
Troisième argument:
Colonne à partir de laquelle les données seront résumées. Celui-ci sera choisi selon la formule OFFSET pour la colonne du mois, qui est ajustée par le nombre sélectionné dans la liste des mois dans la zone de liste déroulante. La formule OFFSET permet de détourner le mois sélectionné de la colonne de base (colonne C dans la capture d’écran ci-dessous).
Comment fonctionne la formule OFFSET
La colonne 29 est le numéro de colonne pour décembre 2003 et le numéro de colonne pour décembre 2002 est 17, soit 12 colonnes avant (voir la capture d’écran ci-dessous).
Comment changer les titres des en-têtes dans une feuille de caractères en nombres: {vide} 1. Dans le menu Outils, sélectionnez Options.
-
Dans l’onglet Général, cochez le style de référence R1C1.
Lorsque décembre 2003 est sélectionné dans la liste déroulante Combo Box (liste des mois), le numéro du mois dans cette liste est 24 (il est calculé en déterminant le nombre de mois entre janvier 2002 et décembre 2003:
2 ans * 12 mois = 24). La cellule liée à la liste déroulante reçoit la valeur 24.
Dans la feuille de données, la colonne 3 est la colonne de base que la formule SUMIF évalue pour les critères du deuxième argument de la formule SUMIF.
Dans ce cas, décembre 2003 est éloigné de la colonne C de 24+ 2 = 26 (2 = Colonne D et Colonne E).
Dans le troisième argument, la Sum_range doit être distante de 26 colonnes de la colonne de base. La formule OFFSET renvoie le résultat de 26 et oblige la formule SUMIF à résumer les chiffres de la colonne décembre 2003.