Calculs Excel sur des plages de largeurs variables

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


Sujets que vous pourriez aussi aimer :


Sommer sur un nombre variable de colonnes

Nous avons déjà appris à réaliser des opérations sur des plages dynamiques avec la fonction Excel Décaler. Mais nous allons le voir, une astuce Excel permet de réaliser les mêmes prouesses avec une syntaxe très épurée.



Sur l'exemple illustré par la capture, un tableau livre les chiffres réalisés par des commerciaux sur plusieurs semaines. L'utilisateur peut choisir de consolider ces données sur un nombre variable de semaines, grâce à une liste déroulante située en haut et à droite du tableau. Et en même temps que les résultats sont livrés pour la somme et la moyenne, une mise en forme conditionnelle surligne automatiquement en vert toutes les cellules impliquées dans la synthèse. Bref, cette solution permet d'engager des calculs sur un nombre variable de colonnes. Et nous le comprendrons, cette technique s'adapte très facilement pour faire des opérations sur un nombre variable de lignes.

Classeur Excel à télécharger
Pour la démonstration de cette nouvelle astuce, nous suggérons d'appuyer l'étude sur un classeur offrant déjà ces données ainsi que cette liste déroulante à actionner.

• Télécharger le classeur calculs-plages-dynamiques.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é,

Nous retrouvons le tableau des ventes que nous avons évoqué précédemment.



Sur la droite, une liste déroulante permet de définir un nombre de semaines à considérer pour la consolidation à effectuer par calcul. Ne vous fiez pas aux données qu'elle propose en apparence (==> Sem. 2 : Sur deux semaines). C'est un format personnalisé qui leurre l'affichage pour plus de clarté. Si vous consultez la barre de formule, vous remarquez que seule la valeur numérique est retenue. Et c'est elle que nous allons exploiter pour faire varier le nombre de colonnes à consolider pour la somme et pour la moyenne. Et précisément, ces résultats sont attendus en cellules respectives G7 et G10.

Somme sur une largeur variable
Pour additionner ces chiffres, la fonction Somme est forcément de mise. Pour les consolider sur un nombre de colonnes défini par l'utilisateur, l'astuce peu conventionnelle consiste à imbriquer la fonction Index dans la fonction Somme. Elle doit être utilisée comme borne inférieure de la plage d'addition. Bien qu'il s'agisse d'une fonction d'extraction à l'origine, elle va permettre de définir dynamiquement la borne de fin de la plage en faisant varier la largeur ou la hauteur.

• Sélectionner la case de la somme à calculer en cliquant sur sa cellule G7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Désigner la première vente en cliquant sur la cellule C4,

Nous définissons ainsi le point de départ de la plage à sommer.

• Taper le symbole deux points (:) pour annoncer la borne inférieure à suivre.

C'est elle qui doit varier dynamiquement en fonction du choix de l'utilisateur par le biais de la liste déroulante. S'il choisit la semaine 2, la borne de fin est D11 et E11 si le choix se porte jusqu'à la semaine 3. Vous l'avez remarqué, l'inscription du symbole deux points a répété la cellule C4.

• Supprimer cette seconde référence C4 tout en conservant les deux points,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner l'intégralité des valeurs numériques, soit la plage de cellules C4:E11,

A ce stade en effet et jusqu'à preuve du contraire, nous considérons que ce sont les trois semaines de chiffres qui doivent être consolidées. Mais c'est le troisième argument de la fonction Index qui va tout ajuster. Le deuxième peut être ignoré.

• Inscrire deux points virgules, soit : ;;, pour ignorer le deuxième argument de la fonction Index,

Ce deuxième paramètre concerne à l'origine l'indice de ligne à fournir pour l'extraction à produire. Dans ce contexte particulier, il permettrait de faire varier la plage du calcul en lignes, donc en hauteur. Nous y reviendrons.

• Désigner la cellule du choix utilisateur en cliquant sur G4,

Dans ce contexte une fois encore, ce numéro de colonne indique d'étendre ou de restreindre la largeur de la plage jusqu'au nombre de colonnes ainsi défini.

• Fermer la parenthèse de la fonction Index,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider la formule à l'aide de la touche Entrée du clavier,

Le résultat tombe. Il indique la somme des chiffres sur la période choisie dynamiquement. Si d'aventure vous en doutiez, il suffit de sélectionner la plage correspondante dans le tableau, de consulter l'information statistique fournie par Excel dans la barre d'état en bas de la fenêtre et de la confronter à la donnée du calcul.



Moyenne sur une largeur variable
Pour la consolidation de la moyenne, le principe est fort naturellement identique. Dans la syntaxe, il suffit de remplacer la fonction Somme par la fonction Moyenne.

• En cellule G10, adapter la précédente syntaxe comme suit :

=MOYENNE(C4:INDEX(C4:E11 ;; G4))

Là encore, en cas de doute, une sélection et un croisement avec l'information fournie dans la barre d'état permet de lever tout scepticisme.

Surligner les colonnes du calcul
Vous en conviendrez, l'emploi de la fonction Index dans ce contexte est très original. Et pourtant la solution est épatante car la syntaxe de la formule construite est fort simple, pour des résultats évolutifs. Pour une solution plus éclatante, nous proposons maintenant de faire ressortir dynamiquement les colonnes impliquées dans le calcul. L'idée est de faire évoluer la plage de couleur en fonction du nombre de semaines choisi par l'utilisateur avec la liste déroulante. Il est question de comparer l'indice de colonne en cours d'analyse par une règle de mise en forme conditionnelle, avec la valeur choisie dans la liste.

• Sélectionner toutes les données numériques du tableau, soit la plage de cellules C4:E11,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir la commande 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 : =Colonne()<=$G$4+2,

La fonction Colonne ainsi utilisée sans paramètre, renseigne sur l'indice de la colonne en cours d'étude. L'analyse d'une mise en forme conditionnelle est en effet chronologique. Ce sont toutes les cellules de chaque colonne et de chaque ligne qui sont passées en revue tour à tour. Cet indice est comparé à la valeur choisie par l'utilisateur ($G$4) réhaussée de deux unités en raison des deux colonnes qui précèdent le tableau des valeurs numériques.

Lorsque ce critère est vérifié, les colonnes impliquées doivent surgir en couleur.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un vert clair pour la couleur du texte,
• Valider ces attributs avec le bouton Ok,
• De retour sur la première boîte, valider la règle en cliquant de nouveau sur le bouton Ok,

Maintenant et comme vous pouvez l'apprécier, la plage concernée par le calcul de consolidation est automatiquement mise en valeur, au gré des changements de facteurs imposés par l'utilisateur avec la liste déroulante.

Pour terminer, nous l'avions évoqué, nous pouvons aussi faire évoluer la plage en hauteur sur un vecteur, soit sur une colonne, donc pour une semaine. En effet, lorsque seul un vecteur est fourni, le deuxième argument de la fonction Index fait varier la largeur ou la hauteur de la plage à consolider, selon la direction du vecteur. Avec cette formule :

=SOMME(C4:INDEX(C4:C11;G4))

Nous considérons uniquement les X premiers (G4) commerciaux sur la semaine 1.