Additions Excel sur des colonnes alternées

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


Sujets que vous pourriez aussi aimer :


Sommes toutes les N colonnes

Dans une astuce précédente, nous avons appris à réaliser des additions sur des lignes alternées. Ici, l'idée est de pouvoir ignorer certaines colonnes dans la somme, selon un facteur dynamique.



Dans l'exemple finalisé illustré par la capture, des chiffres d'affaires sont énumérés sur quatre lignes pour les 12 mois de l'année, donc sur 12 colonnes. Un facteur est inscrit en avant-dernière colonne. Il doit être exploité pour réaliser les additions alternées (Toutes les 1 colonnes, toutes les 2 colonnes etc...).

Pourquoi ce principe est-il intéressant ? On peut imaginer un tableau énumérant des chiffres d'affaires comme ici, mais pas seulement. Des colonnes pourraient être intercalées pour énoncer des jours de semaines, des quantités ou encore des codes articles par exemple. Et dans ce contexte, seuls les chiffres d'affaires doivent être sommés pour être consolidés sur la période. Donc il est nécessaire de pouvoir dérouler l'opération toutes les N colonnes afin d'ignorer celles qui ne portent pas de chiffres.

Classeur source
Pour la présentation de cette astuce, nous proposons de nous appuyer sur un classeur offrant déjà ces chiffres d'affaires à consolider.

• Télécharger le classeur sommer-n-colonnes.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é,

Les coefficients à considérer pour réaliser ces sommes alternées sont donc inscrits en colonne N. Il s'agit respectivement de réaliser les opérations pour toutes les colonnes, 1 colonne sur 2, 1 sur 3 et 1 sur 6. Et ces résultats doivent être livrés dynamiquement en colonne O voisine.

Additions alternées dynamiquement
L'astuce consiste à recouper des matrices dans la fonction Excel SommeProd. La première doit exploiter la fonction Mod sur l'ensemble des chiffres de la ligne avec le coefficient inscrit en colonne N comme diviseur. Il doit en résulter uniquement les colonnes pour lesquelles le reste de la division est nul. En recoupant cette matrice de résultats listant des 1 et des 0 avec la matrice des chiffres d'affaires sur la même ligne, nous additionnerons bien uniquement les chiffres d'affaires correspondant à l'alternance demandée par le coefficient.

• Sélectionner la première somme alternée à trouver en cliquant sur sa cellule O4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

De cette manière, nous affichons l'assistant Excel pour la fonction SommeProd. Il va livrer des résultats intermédiaires qui vont nous permettre de mieux appréhender la construction de la formule matricielle.

• Ouvrir une parenthèse pour accueillir la matrice conditionnelle,
• Inscrire la fonction pour le reste d'une division, suivie d'une parenthèse, soit : Mod(,
• Inscrire la fonction pour la position en colonne, suivie d'une parenthèse, soit : Colonne(,
• Désigner l'ensemble des chiffres d'affaires de la ligne, soit la plage de cellules B4:M4,
• Fermer la parenthèse de la fonction Colonne,

La fonction Colonne raisonne de façon absolue. Or nous souhaitons agir sur les bornes du tableau pour les sommes alternées. Il débute son énumération en deuxième colonne mais pourrait commencer dans une autre rangée. Nous devons donc corriger ce décalage fonction de la colonne vide en amont et de la position de départ du calcul.

• Taper le symbole moins (-) pour annoncer la soustraction à suivre,
• Inscrire de nouveau la fonction pour la position en colonne avec une parenthèse : Colonne(,
• Désigner le premier chiffre de la ligne en cours de calcul en cliquant sur sa cellule B4,
• Fermer la parenthèse de la fonction Colonne,
• Ajouter une unité à ce résultat, soit : +1,

Grâce à cet ajustement, nous sommes placés sur la première colonne en début de calcul. Puis, elles sont toutes passées en revue une à une selon le raisonnement matriciel.

• Taper un point-virgule (;) pour passer dans l'argument du diviseur de la fonction Mod,
• Désigner le premier coefficient par ses coordonnées, soit : N4,
• Fermer la parenthèse de la fonction Mod,
• Puis, taper l'égalité suivante : =0,

Si le reste de la division est nul, cela implique que l'indice de colonne en cours d'analyse concorde avec le coefficient. Cette colonne doit donc être considérée dans le calcul pour la somme alternée.

• Fermer la parenthèse de la matrice conditionnelle,

Aussitôt, vous voyez apparaître une matrice de booléens sur la droite de la zone Matrice1. Elle renseigne sur les positions des colonnes concordantes. Pour le premier calcul, puisque le coefficient est fixé sur l'unité, toutes les colonnes sont à considérer. C'est la raison pour laquelle chaque colonne est repérée par l'indicateur Vrai.



Nous devons transformer ces booléens en chiffres pour autoriser la multiplication de chacun des chiffres d'affaires à passer en deuxième argument de la fonction SommeProd.

• Taper le symbole de l'étoile suivi du chiffre 1 : *1, pour forcer la conversion,

Nous obtenons bien une matrice de chiffres répondant positivement pour chaque colonne ainsi repérée. Dans le cas du deuxième coefficient réglé à 2, ces indicateurs numériques auraient été alternés une fois sur deux.

• Cliquer dans la zone Matrice2 pour l'activer,
• Sélectionner de nouveau tous les chiffres d'affaires pour la ligne en cours, soit la plage B4:M4,

Tous les chiffres de la première matrice sont ainsi multipliés avec les chiffres d'affaires de la seconde. Dans ce premier cas, en raison du coefficient fixé à 1, tous les chiffres d'affaires de la seconde matrice sont restitués et additionnés à l'issue par la fonction SommeProd. Toutes les divisions par 1 conduisent à un reste nul. Le total est d'ailleurs livré en bas de la boîte de dialogue. Mais avec un autre coefficient, des zéros repèreront les positions des colonnes à ignorer. En effet, la multiplication par zéro conduit bien à zéro.

• Valider la formule matricielle en cliquant sur le bouton Ok de l'assistant fonction,

Le premier résultat tombe et il correspond bien à l'addition des 12 mois.

• Double cliquer sur la poignée du résultat,

Nous répandons ainsi la logique du calcul sur les autres lignes. Mais en raison du facteur dynamique, la somme alternée change.

Repérer les colonnes alternées
Pour renforcer l'impact de cette solution et rendre plus évidente la cohérence des résultats, nous proposons de repérer pour chaque ligne, les cellules impliquées dans les sommes alternées. Il suffit donc de vérifier que l'indice de colonne en cours d'analyse est en concordance avec le facteur. Et pour ce faire, dans une règle de mise en forme conditionnelle, nous n'avons qu'à exploiter le même calcul impliquant la fonction Mod comme dans la matrice conditionnelle.

• Sélectionner toutes les cellules concernées par le repérage, soit la plage B4:M7,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, répliquer la syntaxe du précédent calcul pour repérer les colonnes :

=MOD(COLONNE()- COLONNE($B$4)+1; $N4)=0

Attention néanmoins, dans cette analyse chronologique et non matricielle, les références absolues entrent en jeu. La cellule B4 doit être complètement figée. Elle est le repère, donc le point de départ pour corriger le décalage du fait du décompte en valeur absolue par la fonction Colonne. La cellule N4 doit être seulement figée en colonne (Dollar devant le N). Pour chaque ligne, chaque diviseur est immuablement placé dans sa colonne. Lorsque le reste de la division vaut zéro, la cellule impliquée dans le calcul de la somme alternée, doit ressortir explicitement et visuellement du lot.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Activer l'onglet Remplissage de la nouvelle boîte de dialogue,
• Dans la palette de couleurs, choisir un bleu clair pour le fond de la cellule,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la deuxième liste déroulante et choisir un gris foncé pour la couleur de texte,
• Valider ces réglages d'attributs par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle explicite de quelle façon les cellules impliquées dans le calcul et selon le coefficient, doivent ressortir.

• Valider la création de la règle en cliquant sur le bouton Ok de la boîte de dialogue,

Aussitôt, toutes les cellules concernées par les sommes alternées surgissent. Pour la première ligne, elles sont naturellement toutes mises en valeur. Pour la deuxième, une cellule sur deux ressort, en cohérence avec le coefficient mentionné en colonne N. Puis, seulement une sur trois et une sur six sont mises en valeur pour les deux dernières lignes. Si vous changez la valeur de l'un des coefficients, vous constatez, en même temps que le calcul s'adapte, que la mise en forme s'ajuste automatiquement.