Consolider sur plusieurs colonnes avec une fonction Excel

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


Sujets que vous pourriez aussi aimer :


Consolider sur plusieurs colonnes

Dans le volet précédent, nous avons découvert simplement la fonction Excel matricielle Pivoter.Par pour consolider des données en lignes et en colonnes. Ici, nous allons voir qu'elle est capable, tout en croissant des informations recoupées en lignes et en colonnes, de livrer des résultats de synthèse sur plusieurs colonnes conjointes.



C'est ce que présente le résultat illustré par la capture. Nous travaillons à partir d'un tableau de fruits vendus par Pays. Sont mentionnés les quantités vendues, les prix unitaires et les ventes globales. Sur la droite, pour chaque fruit en ligne recoupé avec chaque Pays en colonne, nous livrons la moyenne des quantités et des ventes. Pourtant, dans le tableau d'origine, ces deux colonnes ne sont pas voisines. Nous allons donc dégainer une fameuse astuce grâce à une fameuse fonction Excel à imbriquer dans la fonction de consolidation Pivoter.Par.

Classeur Excel à télécharger
Nous suggérons de baser l'étude sur un classeur Excel offrant ce tableau des ventes.

• Télécharger le classeur consolider-plusieurs-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 dans le bandeau de sécurité,

Nous retrouvons bien le tableau des ventes sur la gauche de la feuille. Sur la droite, la grille de synthèse est naturellement vide à ce stade, au détail près que les cellules sont préformatées.

Nous souhaitons donc regrouper les produits en lignes à recouper avec les pays regroupés en colonnes et pour chacun d'eux, les synthèses cumulées en moyennes sur les quantités livrées et sur les ventes.

Recouper les produits et les Pays
Nous l'avons appris lors du volet précédent, les deux premiers paramètres de la fonction Excel Pivoter.Par attendent respectivement les données à regrouper et à placer en lignes et les données à regrouper et à placer en colonnes. Nous le répétons, il s'agit premièrement des produits et ensuite des Pays.

• Cliquer sur la cellule I3 à gauche de la case orangée, pour la sélectionner,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Inscrire la fonction de consolidation dynamique, suivie d'une parenthèse, soit : Pivoter.Par(,
• Désigner tous les fruits avec l'entête en sélectionnant la plage de cellules C3:C33,
• Taper un point-virgule (;) pour passer dans l'argument de la plage à recouper en colonnes,
• Désigner tous les Pays en sélectionnant la plage de cellules D3:D33,

Les cumuls
Sur ces données recoupées, nous souhaitons offrir les synthèses en moyennes à la fois sur les quantités et les ventes. Problème, ces deux colonnes ne sont pas contigües. Si nous les sélectionnons à la souris, la colonne intermédiaire, celle des PUHT va être englobée, ce que nous nesouhaitons pas. Si nous les sélectionnons indépendamment avec la touche CTRL du clavier, un point-virgule va s'intercaler. Or ce dernier, dans une fonction, signifie de passer au paramètre suivant. Donc ça ne va pas marcher non plus. L'astuce consiste à les assembler virtuellement grâce à la fabuleuse fonction Excel Assemb.H pour placer à côté les deux rangées verticales.

• Taper un point-virgule (;) pour passer dans l'argument des valeurs à synthétiser,
• Inscrire la fonction d'assemblage, suivie d'une parenthèse, soit : Assemb.H(,
• Désigner toutes les quantités en sélectionnant la plage de cellules E3:E33,
• Taper un point-virgule (;) pour passer dans l'argument de la seconde plage à assembler,
• Désigner toutes les ventes en sélectionnant la plage de cellules G3:G33,
• Puis, fermer la parenthèse de la fonction Assemb.H,

La synthèse
Nous souhaitons obtenir la synthèse sur la moyenne des quantités vendues et des ventes effectuées.

• Taper un point-virgule (;) pour passer dans l'argument suivant,
• Inscrire la fonction de la moyenne, soit : Moyenne,

Terminer la consolidation
Il nous reste à restituer les entêtes à distiller notamment.

• Taper un point-virgule (;) suivi du chiffre 3 pour redistribuer les entêtes,
• Puis, taper trois points virgules suivis du chiffre 0 : ;;;0,

De cette manière, nous ne conservons pas les totaux généraux.

• Fermer la parenthèse de la fonction Pivoter.Par,
• Enfin, valider la formule par la touche Entrée du clavier,

Nous obtenons une synthèse redoutable pour constater que l'Espagne arrive en tête et dans l'ordre, la France et l'Italie suivent. Il aurait été bien difficile de se faire une telle idée à la première lecture du tableau à plus forte raison s'il avait été fait de plus nombreux produits et de plus nombreux Pays. Merci donc à cette fabuleuse fonction Pivoter.Par qui court-circuite enfin les tableaux croisés dynamiques, lourds de gestion et de construction. Nous passons à une nouvelle ère.