Consolider avec critère par formule 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 selon critère

Nous poursuivons notre apprentissage sur les possibilités offertes par la puissante fonction Excel Grouper.Par. Ici, nous allons découvrir qu'elle est capable de consolider des données sous condition.

Classeur Excel à télécharger
Nous suggérons d'appuyer l'étude sur un classeur Excel existant.

• Télécharger le classeur consolider-avec-critere.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 découvrons un tableau de synthèse sur les ventes de fruits et légumes dans quatre magasins d'une même enseigne. Cette synthèse, nous ne la souhaitons pas par magasin mais par fruit ou légume vendu. Problème, entre chaque magasin, une ligne de total est intercalée. Comment donc exploiter la fonction Excel Grouper.Par en contournant ce parasite.

Consolider sans critère
Pour bien comprendre le mécanisme, nous suggérons d'agir en deux temps. Nous allons commencer par entreprendre une consolidation classique des données, comme nous l'avons appris jusqu'alors grâce à cette fabuleuse fonction Grouper.Par.

• Cliquer sur le premier entête grisé du tableau de synthèse pour sélectionner la cellule F3,
• Taper le symbole égal (=) pour débuter la construction de la formule intermédiaire,
• Inscrire la fonction de consolidation, suivie d'une parenthèse, soit : Grouper.Par(,
• Désigner les produits à recouper en sélectionnant la plage de cellules C3:C23,
• Taper un point-virgule (;) pour passer dans l'argument des valeurs à cumuler,
• Désigner les ventes en sélectionnant la plage de cellules D3:D23,
• Taper un point-virgule (;) pour passer dans l'argument de l'opération de synthèse,
• Dans les propositions, choisir la première, soit la fonction Somme,
• Puis, taper un point-virgule suivi du chiffre 3,

Ainsi, dans cet argument field_headers, nous choisissons de reproduire les entêtes du tableau d'origine à l'identique dans la destination consolidée.

• Taper alors deux points-virgules suivis de la valeur -2, soit : ;;-2,

De cette manière, sur les résultats consolidés, nous commandons une organisation décroissante, de la plus grande valeur vers la plus petite.

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

Comme vous le constatez, nous obtenons bien la consolidation des ventes pour chaque fruit et légume émanant des quatre magasins. Mais une ligne résiduelle se greffe en entête. Il s'agit de celle des totaux qui interfère dans le tableau d'origine.

Consolider avec critère
Mais cette fonction Grouper.Par n'a pas fini de nous surprendre. En plus de sa puissance, elle est capable d'oeuvrer tout en finesse en émettant des conditions sur les données à consolider. Ici, il est question de faire comme avant, mais tout en excluant les lignes des totaux pour un rendu parfaitement exact. Son avant dernier paramètre se nomme filter_array. Il permet de filtrer donc. Ici, il est question d'exclure de la reconstitution toutes les lignes qui portent la mention Total.

• Cliquer sur la cellule du calcul pour resélectionner la cellule F3,
• Dans sa barre de formule, cliquer juste avant la parenthèse fermante,
• Taper un point-virgule (;) pour passer dans l'argument filter_array,
• Désigner la plage des magasins en sélectionnant la plage de cellules B3:B23,
• Puis, inscrire le critère d'inégalité suivant : <>"Total",

De cette manière, nous entendons exclure de l'équation toutes les lignes des sous-totaux.

• Enfin, valider l'adaptation de la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, la ligne résiduelle disparaît, grâce à cette condition.



Cette fois, nous obtenons bien la consolidation conditionnelle demandée. Et si toutes fois vous doutiez de la véracité de ces résultats, vous pouvez actionner la liste déroulante du dessous qui embarque la fonction Filtre dans la fonction Somme pour vérifier tous ces résultats :

=SOMME(FILTRE(D4:D23; C4:C23=F11))

De plus la formule de consolidation sous condition que nous avons bâtie, demeure très simple :

=GROUPER.PAR(C3:C23; D3:D23; SOMME; 3;;-2; B3:B23<>"Total")