Ajuster dynamiquement la plage du calcul avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Ajuster dynamiquement les plages de calculs

Nous avons déjà appris à ajuster des plages de calcul grâce à la fonction Excel Decaler. Mais jusqu'alors, nous avons réalisé ces ajustements dynamiques sur la base d'un seul critère. Ici, nous souhaitons adapter la position en colonne de la plage de calcul, ainsi que sa hauteur, sur la base de deux conditions à recouper.



Sur l'exemple finalisé illustré par la capture, l'utilisateur choisit un mois à l'aide d'une première liste déroulante et une année à l'aide d'une seconde liste. Il en résulte la somme des ventes sur l'année pour les premiers mois jusqu'à celui défini. Nous déplaçons donc premièrement la plage de calcul sur la colonne coïncidant avec l'année désignée. Puis, nous ajustons sa hauteur dans cette rangée pour ne pas considérer les mois au-delà de la période.

Source et présentation
Pour réaliser ces travaux, nous proposons de récupérer un tableau synthétisant les chiffres d'affaires développés sur plusieurs années.

• Télécharger le classeur plages-sur-criteres-recoupes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Un tableau situé entre les colonnes B et F et les lignes 5 et 17 énumère tous les chiffres d'affaires réalisés par l'entreprise pour chacun des mois sur quatre années. Une première liste déroulante permet de choisir l'un de ces mois en cellule H6. En fonction de ce choix, la somme des chiffres doit être réalisée pour tous les premiers mois de l'année jusqu'à ce dernier. Et pour cela, une seconde liste déroulante permet de définir l'année en question, en cellule H9. Le total des chiffres sur la période ainsi ajustée doit être livré en cellule H13. Dans le même temps, une mise en forme conditionnelle doit surligner la plage de cellules dynamique impliquée dans le calcul.

Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous remarquez que des plages ont été nommées. Les mois portent l'intitulé mois. Les années portent l'intitulé annees. Et les chiffres du tableau sont reconnus sous le nom ca. Nous exploiterons ces noms dans la construction des syntaxes.

Somme selon le mois et l'année
C'est bien sûr la fonction Excel Decaler qui permet d'ajuster la position et la dimension de la plage de calcul, selon les conditions recoupées sur le mois et l'année. Il s'agit premièrement d'ajuster la position en colonne en fonction de l'emplacement de l'année cherchée dans le tableau. Il s'agit ensuite d'ajuster la hauteur en fonction de la position du mois repérée dans ce même tableau. Et c'est la fonction Equiv, utile à deux reprises, qui permet de déceler ces positions d'ajustement.

• Sélectionner la cellule H13 du résultat à trouver,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Sélectionner le premier chiffre d'affaires, soit C6, comme référence pour le point de départ,
• Taper deux points-virgules, soit : ;;, pour ignorer le décalage en ligne,

De fait, nous sommes propulsés dans l'argument du décalage en colonne. Par rapport au point de départ fourni en premier argument de la fonction Decaler, nous devons nous déplacer sur la colonne correspondant à l'année choisie. C'est la fonction Equiv qui permet de retourner cet emplacement.

• Taper la fonction pour la position d'une valeur cherchée, suivie d'une parenthèse, soit : Equiv(,
• Sélectionner l'année choisie en cliquant sur sa cellule H9,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la ligne des années par son nom de plage, soit : annees,

La fonction Equiv doit donc chercher l'année choisie dans la ligne des années. Il va en résulter sa position.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, retrancher une unité à son résultat, soit : -1,

En effet, la position de départ est incluse dans la manoeuvre. Pour corriger ce décalage initial, nous devons retrancher sa présence.

• Taper un point-virgule (;) pour passer dans l'argument facultatif de la hauteur variable,

Cette fois, cette hauteur doit être déterminée par la position du mois choisi dans la première colonne du tableau, celle de l'énumération des mois de l'année. Nous devons donc rechercher cette information, une fois encore grâce à la fonction Equiv.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner le mois choisi en cliquant sur sa cellule H6,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la colonne des mois par son nom, soit : mois,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Fermer la parenthèse de la fonction Decaler,
• Puis, fermer la parenthèse de la fonction Somme,
• Enfin, valider le calcul à l'aide de la touche Entrée du clavier,

Le résultat de la somme tombe. Et bien entendu, il ajuste dynamiquement la plage de calcul selon les critères émis. Pour le constater, il vous suffit de changer le mois et l'année.



La formule que nous avons construite est la suivante :

=SOMME(DECALER(C6;;EQUIV(H9; annees; 0)-1;EQUIV(H6; mois; 0)))

Pour valider la bonne cohérence des résultats livrés dynamiquement, vous pouvez sélectionner les chiffres pour l'année définie et jusqu'au mois désigné.



Dès lors, en consultant la barre d'état en bas à droite de la fenêtre Excel, vous constatez que la somme des cellules sélectionnées recoupe le résultat de la somme sur la plage ajustée dynamiquement.

Repérer la plage dynamique de calcul
Pour une solution aboutie, nous suggérons de poser une règle de mise en forme conditionnelle sur les chiffres d'affaires du tableau. Son rôle est de mettre en valeur la plage utilisée par le calcul, en fonction des deux critères émis. L'utilisateur saura instantanément identifier l'ensemble des cellules impliquées conduisant au résultat consolidé en H13. La règle doit émettre un double critère. Il s'agit de vérifier la position de l'année définie en colonne. Dans le même temps, elle doit s'assurer que les positions des mois du tableau sont inférieures à la ligne du mois choisi.

• Sélectionner tous les chiffres d'affaires du tableau, soit la plage de cellules C6:F17,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie du dessous, construire la syntaxe suivante :

=ET(LIGNE(C6)-5<=EQUIV($H$6;mois; 0); $H$9=C$5)

Nous exploitons tout d'abord la fonction Excel ET pour recouper les deux conditions. Pour chaque chiffre passé en revue, nous vérifions que sa position en ligne est bien inférieure ou égale à celle du mois cherché par la fonction Equiv. Mais comme cette dernière raisonne dans les bornes du tableau, nous retranchons cinq unités à la position de chaque cellule. Ainsi, nous annihilons le décalage opéré par les cinq lignes situées au-dessus du tableau. Puis, dans le même temps, nous vérifions l'égalité des positions en colonne pour chaque chiffre, par équivalence sur les années. De ce fait, la cellule C5 est libérée en colonne et figée en ligne (C$5). Ainsi, chaque année de la ligne de titre peut être comparée à l'année choisie ($H$9), dans le raisonnement chronologique de la règle.

Lorsque ces conditions sont vérifiées, nous devons faire ressortir explicitement du lot les cellules concernées.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un jaune foncé pour le texte,
• Valider ces attributs par Ok et la création de la règle par Ok de nouveau,

Comme vous le constatez, au gré de la modification des critères, la plage de calcul réajustée dynamiquement est parfaitement mise en valeur par la règle pour recouper les résultats.