Calculs sur les lignes filtrées avec critère

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


Sujets que vous pourriez aussi aimer :


Sommes sur les lignes visibles avec critère

Nous savons déjà réaliser des opérations sur des tableaux filtrés grâce à la fonction Excel Sous.Total. Elle permet de définir le calcul à effectuer sur des lignes filtrées pour ne considérer que celles qui ne sont pas masquées. Mais ici, il s'agit d'aller plus loin. Non seulement le calcul doit être opéré uniquement sur les lignes visibles, mais il doit aussi répondre à un critère supplémentaire.



Dans l'exemple illustré par la capture, nous travaillons sur un tableau de synthèse des ventes réalisées par les commerciaux d'une entreprise. L'utilisateur peut synthétiser l'affichage à l'aide de filtres sur les mois à isoler par exemple. Aussitôt, nous devons résumer le chiffre d'affaires total engrangé, le chiffre d'affaires total sur le filtre appliqué et le chiffre d'affaires sur les lignes visibles restantes pour le commercial désigné.

Source et présentation
Pour résoudre ce cas, nous proposons tout d'abord de récupérer ce tableau des ventes.

• Télécharger le classeur sommes-conditionnelles-filtres.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 dates des ventes sont énumérées en colonne B. Les vendeurs les ayant réalisées sont listés en colonne C. Les chiffres atteints sont retranscrits pour chacun en colonne D.



Vous notez la présence de flèches de filtre en entêtes de colonnes. Par exemple, la première permet de restreindre l'affichage sur un ou des mois spécifiés. Ce sont ces restrictions que nous devons considérer premièrement dans les calculs de synthèse. Mais nous l'avons dit, ce n'est pas tout.

Vous remarquez aussi la présence d'une rangée intermédiaire en colonne G. Son But est d'aider à atteindre le résultat sur les opérations filtrées avec critère. C'est la raison pour laquelle elle n'est pas formatée.

Le tableau des synthèses attendues est placé sur la droite de la feuille. En cellule I5, un calcul résume sans aucune restriction, le chiffre d'affaires global généré : =SOMME(D6:D53). Il s'agit d'une simple somme sur l'ensemble de la colonne des ventes réalisées. En cellule I6, nous devons répondre par le chiffre d'affaires généré sur les lignes visibles lorsqu'un filtre est enclenché. Nous devons encore affiner ce résultat en cellule I7 par le biais d'un critère recoupé. Il s'agit de calculer la somme des ventes pour les lignes non masquées et pour le commercial désigné en cellule adjacente H7. A ce titre, vous notez que cette dernière est munie d'une liste déroulante pour opérer un choix sur le nom du commercial.

Enfin, pour parfaire cette présentation, si vous déployez la liste déroulante de la zone Nom en haut à gauche de la fenêtre Excel, vous notez que chaque colonne est intitulée en fonction de son titre de champ.



Nous exploiterons ces noms pour simplifier la syntaxe des formules.

Sommes des lignes non masquées
La fonction Somme impliquée en cellule I5 ne réagit pas aux filtres appliqués sur les tableaux. Que les cellules soient visibles ou masquées, elles les considèrent toutes dans l'opération. Nous l'avions appris, c'est la fonction Excel Sous.Total qui permet de s'adapter aux filtres. De plus, elle offre de choisir parmi de nombreuses opérations à effectuer.

• Sélectionner la cellule du CA filtré à calculer, soit la cellule I6,
• Taper le symbole égal (=) pour démarrer le calcul,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Sous.Total(,
• Avec la liste des propositions, choisir l'opération de la somme,

Elle se traduit donc par le chiffre 9 passé en premier argument de cette fonction Sous.Total.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne à sommer,
• Désigner les chiffres réalisés par le nom de colonne, soit : Réalisé,
• Fermer la parenthèse de la fonction Sous.Total,
• Puis, valider la formule à l'aide de la touche Entrée du clavier,

A ce stade, rien d'étonnant, nous obtenons exactement la même consolidation que la somme globale. Aucun filtre n'est actif sur le tableau.

• Cliquer sur la flèche de la colonne Date,
• Puis, décocher les cases Février, Avril et Mai,
• Ensuite, valider par le bouton Ok,

Comme vous pouvez le voir, seuls subsistent les chiffres réalisés pour les mois de Janvier et Mars. Toutes les lignes qui ne correspondent pas sont masquées. Ce phénomène est simple à confirmer en consultant les sauts d'énumération dans les étiquettes de ligne sur la gauche de la feuille. Et bien entendu, la somme du CA filtré conduit à un résultat bien moins important que celui de la somme globale. Bref, notre opération a parfaitement réagi au filtre enclenché pour n'intervenir que sur les lignes visibles. Ce résultat peut être simplement confirmé en regroupant dans une même sélection les ventes visibles. Dès lors, la barre d'état, en bas de la fenêtre Excel, rappelle la somme des cellules sélectionnées. Et elle recoupe parfaitement notre calcul. La syntaxe de la formule que nous avons construite est la suivante : =SOUS.TOTAL(9;Réalisé).

Consolider les ventes visibles
Désormais, dans l'optique d'ajouter un critère sur le nom du commercial à ces ventes filtrées, nous devons tout d'abord réaliser un calcul intermédiaire en colonne G. Celui-ci consiste à répliquer le chiffre pour chaque ligne visible et à le neutraliser le cas échéant. Nous pourrons alors facilement agir sur cette plage pour n'extraire que les ventes du commercial à recouper. La fonction Excel Agregat permet d'effectuer des opérations de consolidation en tenant compte de certains facteurs, comme celui des cellules masquées.

• Cliquer sur la flèche du filtre dans la colonne Date,
• Dans la liste, choisir la commande Effacer le filtre,

Nous affichons ainsi toutes les lignes et récupérons toutes les données.

• Sélectionner alors la première case du calcul intermédiaire, soit la cellule G6,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction de consolidation suivie d'une parenthèse, soit : Agregat(,
• Dans la liste qui apparaît, choisir la somme,
• Taper un point-virgule (;) pour passer dans l'argument suivant,
• Dans la nouvelle liste qui se propose, choisir l'option 5,

Comme l'indique la précision, il s'agit d'honorer le calcul en ignorant les cellules masquées par le filtre.

• Taper un point-virgule (;) pour passer dans l'argument de la matrice,
• Sélectionner le premier chiffre de la ligne en cliquant sur sa cellule D6,

C'est là que l'astuce réside. En guise de matrice, nous passons à la fonction le chiffre réalisé par le commercial. En cas de cellule visible, la somme conduira au chiffre lui-même et à zéro dans le cas contraire.

• Fermer la parenthèse de la fonction Agregat,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous gardons active la cellule du résultat pour l'exploiter tout de suite.

• Double cliquer sur la poignée de ce résultat pour répliquer la logique sur tout le tableau,

Comme aucun filtre n'est enclenché, tous les chiffres d'affaires réalisés sont pour l'instant strictement répliqués. La syntaxe du calcul que nous avons bâti est la suivante : =AGREGAT(9;5;D6).

Somme sur filtre avec critère
Nous devons exploiter ces derniers résultats pour calculer la somme des ventes visibles uniquement pour le salarié désigné en cellule H7. La fonction Excel Somme.Si est dédiée :

=Somme.Si(Plage_critère; Critère; Plage_somme)

Elle permet de vérifier un critère (Le vendeur) à passer en deuxième argument sur une plage de cellules (Celle des vendeurs) à passer en premier argument. Dès que le critère est honoré, la valeur doit être prélevée et sommée avec les autres à partir d'une troisième plage (Celle des chiffres), à passer en troisième argument.

• Sélectionner la cellule I7 et taper le symbole égal (=) pour démarrer la construction,
• Inscrire la fonction pour la somme conditionnelle suivie d'une parenthèse, soit : Somme.Si(,
• Désigner la plage des vendeurs par son nom, soit : Vendeur,
• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Taper les coordonnées de la cellule H7 pour désigner le vendeur choisi avec la liste déroulante,
• Taper un point-virgule (;) pour passer dans l'argument de la plage pour la somme,
• Désigner la plage des chiffres consolidés par son nom, soit : Inter,
• Fermer la parenthèse de la fonction Somme.Si,
• Puis, valider la formule avec la touche Entrée du clavier,

Nous obtenons cette fois un total restreint correspondant aux ventes réalisées par le commercial désigné par défaut.

• Cliquer sur la flèche du filtre dans la colonne Date,
• Comme précédemment, masquer les mois de Février, Avril et Mai,
• Puis, valider par le bouton Ok,

Le CA total ne bouge pas et c'est fort logique. Le CA filtré s'ajuste pour afficher la somme des ventes réalisées pour les lignes encore visibles. Le dernier CA se rétrécit un peu plus pour calculer les ventes réalisées par le vendeur désigné sur les lignes non masquées, soit pour les mois de Janvier et Mars. Et si vous changez de vendeur avec la liste déroulante en cellule H7, vous notez que le total correspondant s'actualise parfaitement. La syntaxe de la formule que nous avons construite est la suivante :

=SOMME.SI(Vendeur; H7; Inter)



Grâce à ce critère recoupé, nous pouvons conserver l'affichage d'origine pour l'ensemble des vendeurs sur les deux mois sélectionnés. Nous n'avons pas l'obligation d'enclencher un filtre supplémentaire sur le nom du commercial.

Pour parachever la solution à l'issue, il convient de masquer les cellules intermédiaires en colonne G. Et précisément, nous aurions pu éviter cette étape intermédiaire grâce à une formule matricielle :

=SOMMEPROD(SOUS.TOTAL(9;DECALER(D6; LIGNE(Réalisé)-LIGNE(D6); )); --(Vendeur=H7))

Elle consiste à exploiter lafonction SommeProd pour recouper des conditions sur des matrices. La première matrice est énoncée dans la fonction Sous.Total pour ne considérer que les lignes visibles. Et pour honorer le raisonnement matriciel, nous désignons chaque cellule de cette dernière grâce à la fonction Decaler. C'est le décompte des lignes en deuxième argument de la fonction Decaler qui offre cette astuce. En deuxième paramètre de la fonction SommeProd, nous passons une matrice conditionnelle. Sur les lignes visibles imposées par la première matrice, elle consiste à recouper le calcul avec le critère sur le nom du vendeur. La syntaxe : -- en préfixe de cette seconde matrice agit comme une double négation pour forcer la conversion en chiffres.