Calculs sur les tableaux filtrés avec Excel

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


Sujets que vous pourriez aussi aimer :


Opérations sur les données filtrées

Nous avions appris à filtrer les données de tableaux volumineux dans Excel, afin d'offrir une vue synthétisée de l'information résultante correspondant aux critères. Et à ce titre, nous avions réussi à mettre en oeuvre des conditions complexes et recoupées grâce à la puissance des filtres que propose Excel. Nous livrions ainsi des résultats extraits facilement exploitables.

Désormais, nous souhaitons être en mesure d'enrichir les données résultantes avec des calculs de synthèse. L'objectif par exemple est de pouvoir calculer le total du chiffre d'affaires réalisé sur une période donnée. Cependant, lorsqu'un tableau est filtré, les cellules ne vérifiant pas le critère sont masquées et non supprimées. En conséquence, une somme classique sur la colonne, ignore le filtre et intègre les données masquées, si bien que le résultat du calcul est erroné par rapport à la vue de synthèse.



Pour résoudre ce problème, nous proposons deux solutions. La première consiste à exploiter les filtres avancés permettant d'extraire l'information. Une fois les données extraites de la source, n'importe quel calcul peut être enclenché. La seconde solution consiste à employer une fonction Excel dédiée, peu connue, très puissante et excessivement simple à mettre en oeuvre.

Filtrer une base de données Excel
Afin de synthétiser des informations volumineuses, nous avons besoin de travailler à partir d'un tableau Excel adapté. C'est pourquoi nous proposons de récupérer la source :

• Télécharger le classeur calculs-donnees-filtrees.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du ruban si nécessaire,

Ce classeur est constitué d'une seule feuille nommée synthèse. Elle propose un tableau représentant les chiffres réalisés par les vendeurs d'une société au cours du premier semestre de l'année 2017. C'est ce qu'illustre l'extrait de la capture ci-dessus. Pour y voir plus clair et exploiter les résultats de ses troupes, la direction pourrait choisir de filtrer les données. Il s'agit d'un moyen par exemple d'obtenir une synthèse du chiffre sur le premier trimestre, avec les ventes réalisées spécifiquement pour l'un des salariés, pourquoi pas. Et ces critères pourraient être recoupés. Mais dans tous les cas, aucune ligne de résultat n'afficherait explicitement la somme et/ou le nom pour le filtre activé.

Nous proposons dans un premier temps d'activer les filtres automatiques d'Excel, afin de réaliser les premiers constats.

• Cliquer dans n'importe quelle cellule du tableau central, par exemple D10,
• Dans la section Edition du ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Filtrer,

Vous constatez l'apparition de petites flèches en entêtes de colonnes. Chacune d'entre elles permet de réaliser un filtre selon la nature des données présentes dans la rangée.

• Cliquer sur la flèche de filtre de la colonne Date,
• Dans le menu contextuel qui apparaît, décocher la case Sélectionner tout,
• Puis ne cocher que les cases Janvier et Février,

Le filtre s'applique instantanément. Nous isolons ainsi les chiffres réalisés par l'ensemble des vendeurs sur les deux premiers mois de l'année.

• Cliquer ensuite sur la flèche de filtre de la colonne Vendeur,
• Dans le menu contextuel, comme précédemment, décocher la case Sélectionner tout,
• Puis cocher seulement les cases des vendeurs Galls et Houda,

Nous obtenons rapidement une vue synthétisée des chiffres réalisés par ces deux vendeurs au cours des deux premiers mois de l'année. La présentation du tableau s'en trouve simplifiée et les données a priori plus simples à exploiter. Vous pouvez constater sur la gauche de la fenêtre Excel que les numéros d'étiquettes de lignes apparaissent en bleu. Cette symbolique confirme que des lignes ne correspondant pas aux critères recoupés sont masquées.

Cependant, il n'en demeure pas moins compliqué de connaître, d'un seul coup d'oeil, la somme des chiffres d'affaires et le nombre de commandes réalisées pour ces conditions imbriquées. Vous notez la présence de deux cellules dédiées à ces synthèses en bas du tableau, précisément en D55 et D56.

Pour calculer le nombre de commandes réalisées, nous pouvons exploiter la fonction Excel Nbval qui retourne la quantité de cellules non vides sur une plage de cellules désignée. Nous l'avions déjà exploitée pour construire des listes déroulantes dynamiques et évolutives. Bien entendu, pour calculer le total des chiffres d'affaires, nous proposons d'exploiter la fonction Somme.

• Cliquer dans la cellule D55 pour l'activer,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de dénombrement soit : nbval,
• Ouvrir la parenthèse et sélectionner tous les CA de la colonne filtrée, soit la plage D5:D20,
• Fermer la parenthèse et valider le calcul par la touche Entrée,
• En cellule D56, taper le symbole = pour débuter un nouveau calcul,
• Taper le nom de la fonction pour sommer soit : somme,
• Ouvrir la parenthèse et sélectionner la colonne des chiffres d'affaires, soit la plage D5:D20,
• Fermer la parenthèse et valider le calcul à l'aide de la touche Entrée.

Les résultats s'affichent instantanément mais comme vous l'aurez si tôt remarqué, ils ne sont pas cohérents. La valeur retournée par la fonction Nbval attire tout de suite l'attention. Elle comptabilise 16 ventes alors que seulement 8 correspondent aux critères des filtres. Il en va de même pour la somme qui conduit à un résultat fortement majoré. L'une et l'autre considèrent toutes les cellules comprises entre D5 et D20, soit la plage sélectionnée, sans tenir compte des cellules masquées à ne pas intégrer.

Pour un utilisateur non initié, ces résultats sont dangereux dans la mesure où ils peuvent conduire à de mauvaises interprétations et prises de décision.



Une petite balise active attire l'attention sur une soi-disant erreur de calcul dans la formule réalisée. Cependant, elle n'est pas d'un grand secours, elle consiste même à agrandir l'erreur puisqu'elle suggère d'inclure les cellules manquantes dans le calcul, soit d'aller au-delà de la ligne 20. Vous l'aurez compris, la solution est ailleurs.

Opérations de calculs sur les données extraites
Nous avions appris à exploiter les filtres avancés d'Excel de manière à extraire les données correspondant aux critères. Si les données sont effectivement extraites, elles ne font plus partie du tableau d'origine, aucune ligne n'est masquée et les opérations de synthèse peuvent être posées.

• Sélectionner les précédents résultats de calculs, soit les cellules D55 et D56,
• Supprimer leur contenu, soit leur formule (Touche Suppr du clavier),
• Dans la section Edition du ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, cliquer sur Filtrer afin de désactiver le filtre automatique,

Nous retrouvons la configuration d'origine avec l'affichage de l'ensemble des lignes du tableau des chiffres d'affaires. Comme ce tableau propose un grand nombre d'informations, les lignes de synthèse ne sont plus visibles. Nous proposons de figer les lignes situées au-dessus de la ligne 8 de manière à pouvoir visualiser les calculs du bas de la feuille, en même temps que les titres des colonnes. Cette technique se nomme figer les volets dans une feuille Excel.

• Cliquer sur l'étiquette de ligne 8 sur la gauche de la fenêtre Excel,
• Cliquer sur l'onglet Affichage en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Fenêtre, cliquer sur le bouton Figer les volets,
• Puis dans la liste, cliquer sur la rubrique Figer les volets,

Comme nous avions sélectionné une ligne complète avant la manipulation, seules les lignes du dessus sont figées. Les colonnes conservent leur mobilité. Si vous faites défiler les cellules vers le bas à l'aide de l'ascenseur vertical, vous atteignez les cellules présentant les calculs de synthèse tout en conservant l'affichage de la partie haute du tableau (Cf. figure ci-dessus).

L'extraction des données par les filtres avancés consiste à d'abord indiquer quels sont les critères à vérifier. C'est la raison pour laquelle une petite zone de critères est prévue à cet effet entre les cellules H4 et J6. Nous devons sélectionner, comme précédemment, les données des vendeurs Galls et Houda pour les deux premiers mois de l'année.

• En cellule H5, taper le critère : >=01/01/2017,
• En cellule I5, taper le critère : <01/03/2017,
• En cellule J5, taper le critère : Galls,
• En cellule H6, taper le critère : >=01/01/2017,
• En cellule I6, taper le critère : <01/03/2017,
• En cellule J6, taper le critère : Houda,

Dans le mode de fonctionnement des filtres avancés, lorsque les critères sont énumérés les uns à côtés des autres, ils se recoupent (ET). Lorsqu'ils sont énumérés les uns en dessous des autres, ils se combinent (OU). Ainsi, nous demandons d'extraire pour le vendeur Galls, toutes les données comprises entre le 1_er Janvier et le 1_er Mars exclu, ainsi que toutes les données du vendeur Houda sur la même période.

• Cliquer à l'intérieur du tableau de synthèse, par exemple en D9,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Trier et filtrer, cliquer sur le bouton Avancé,
• Dans la boîte de dialogue qui suit, cocher la case Copier vers un autre emplacement,
• Cliquer dans le champ Zone de critères pour l'activer,
• Puis, sélectionner les cellules H4 à J6,
• Cliquer dans le champ Copier dans,
• Puis sélectionner les cellules d'entêtes prévues pour l'extraction, soit la plage H10:J10,
• Cliquer sur Ok pour lancer l'extraction,

Nous n'avons pas eu besoin de définir la zone Plages de la boîte de dialogue du filtre avancé. Comme nous avons préalablement sélectionné l'une des cellules avant d'enclencher le filtre, Excel a détecté automatiquement les bornes du tableau. Comme vous l'avez remarqué, nous avons pris soin d'inclure les cellules d'entête pour la zone de critères. Ces titres de colonnes sont essentiels pour que le filtre avancé sache sur quel champ appliquer les conditions énumérées. De même dans la zone d'extraction, nous avons précisément sélectionné les titres à partir desquels les données doivent être extraites. C'est ainsi que nous pouvons préciser sans ambiguïté les informations de sortie souhaitées (Date, Vendeur et Réalisé).

Quoiqu'il en soit, nous obtenons bien une extraction fidèle aux critères recoupés et combinés. Il n'y a pas de lignes masquées ici. Donc nous pouvons réaliser les calculs de synthèse fidèles aux données correspondant aux critères d'extraction.

• Sélectionner la cellule D55 pour le dénombrement des ventes réalisées,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction permettant de compter les cellules non vides, soit nbval,
• Ouvrir la parenthèse et désigner la plage de cellules d'extraction des vendeurs : I11:I52,
• Fermer la parenthèse et valider le calcul,

La fonction Nbval retourne le chiffre 8 correspondant aux 8 ventes réalisées par les deux vendeurs désignés pour les deux premiers mois de l'année. Cette fois le décompte est parfaitement correct. Vous l'avez noté, comme la fonction nbval ignore toutes les cellules vides, nous nous sommes permis de prolonger la plage de cellules à compter sur la hauteur du tableau d'origine. Ainsi pour une extraction correspondant à d'autres critères et pour lesquels le résultat fournit un plus grand nombre de lignes, le calcul continuera de fonctionner. Il en va de même avec la fonction Somme. Cette dernière ignore les cellules vides dans le calcul. Donc nous allons la prévoir là encore, sur la hauteur totale du tableau.

• En cellule D56, réaliser le calcul suivant :

=SOMME(J11:J52)

La somme retourne un chiffre d'affaires de 162 200 Euros, soit un résultat beaucoup plus cohérent que précédemment et surtout correct.

• En cellules I5 et I6, changer le critère : <01/03/2017, par : <01/05/2017,
• Cliquer alors dans le tableau des ventes, par exemple en D9,
• Puis, activer le ruban Données et cliquer sur le bouton Avancé,
• Cocher la case Copier vers un autre emplacement et valider par Ok,

Comme nous avions déjà défini les zones de critères et d'extraction, il n'était pas nécessaire de recommencer. Bien entendu, vous constatez que l'extraction conduit à un plus grand nombre de lignes puisque la période définie est plus importante. Et si vous faites défiler le tableau jusqu'aux cellules de calculs de synthèse, vous constatez qu'ils se sont mis à jour en intégrant les nouvelles données extraites.



Certes nous avons réussi à réaliser des calculs de synthèse s'adaptant aux critères des données filtrées, ou plutôt extraites dans notre cas, mais il existe une dernière solution.

Sous totaux des données filtrées avec Excel
Excel propose une fonction de calcul dédiée aux données filtrées et aux tableaux proposant des plans avec des regroupements. Bref, il s'agit d'une fonction de calcul, permettant de réaliser de nombreuses opérations, selon le paramètre indiqué et surtout capable d'ignorer les cellules masquées.

• Supprimer les résultats des calculs en cellules D55 et D56,
• Cliquer n'importe où dans le tableau des ventes, par exemple D9, pour le désigner,
• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Filtrer afin de faire réapparaître les flèches de filtre ssur les entêtes,
• Cliquer sur la flèche de filtre de la colonne Date,
• Dans la liste, décocher Sélectionner tout puis cocher seulement Janvier et Février,
• Cliquer sur la flèche de filtre de la colonne Vendeur,
• Dans la liste, décocher la case Sélectionner tout puis cocher seulement Galls et Houda,
• Sélectionner ensuite la cellule de la somme du CA, soit D56,
• Puis, cliquer sur le bouton Somme automatique du ruban Accueil ou bien réaliser le raccourci clavier ALT + =,
• Réajuster la plage proposée en sélectionnant seulement les CA filtrés, soit D5:D20,
• Puis valider le calcul en enfonçant la touche Entrée du clavier,

Aussi étonnant que cela puisse paraître, le résultat proposé est correct. Il correspond à la somme obtenue depuis l'extraction précédente, sur la base des mêmes critères recoupés.



Mais en réalité, Excel s'est adapté au contexte. Remarquant la présence de données filtrées, le bouton somme automatique n'a pas proposé la fonction Somme mais la fonction Sous.total, comme l'illustre la capture ci-dessus. Non seulement cette fonction ignore les cellules masquées mais en plus, elle permet de réaliser de nombreux calculs de synthèse très utiles, selon le premier paramètre qu'il lui est passé. Le chiffre 9 correspond bien à la somme des données filtrées. Pour l'exploiter, il suffit de se laisser guider par l'aide contextuelle que propose l'info-bulle au fur et à mesure du remplissage de la fonction. C'est ce que nous allons démontrer.

• Sélectionner la cellule du calcul du nombre de ventes, soit D55,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction, soit sous.total et ouvrir sa parenthèse,

Comme vous le remarquez, toute une liste d'arguments possibles apparaît. Chaque numéro correspond à un calcul de synthèse différent, qui respectera les critères du filtre automatique. Ainsi en tapant ou en sélectionnant le 101, nous réalisons la moyenne des ventes filtrées. Dans notre cas, le dénombrement consiste à utiliser la fonction nbval sur les données filtrées. Il s'agit donc du chiffre 3 comme l'indique la capture ci-dessus.

• Taper le chiffre 3 suivi d'un point-virgule,
• Sélectionner la plage de cellules des chiffres réalisés, soit D5:D20,
• Fermer la parenthèse et valider le calcul par la touche Entrée.

Cette fois encore et pour les mêmes raisons que précédemment, le calcul du dénombrement est correct. Les cellules masquées ne sont pas considérées. La fonction Excel Sous.total est donc très précieuse pour enrichir les synthèses fournies par les filtres automatiques afin d'accéder directement aux résultats.

Mais pour être plus juste et assurer que les calculs fonctionnent dans tous les cas, c'est-à-dire quels que soient les filtres enclenchés, nous aurions dû sélectionner l'ensemble des cellules du tableau, bien que les lignes situées au-delà de la 20_ème soient masquées.

• Sélectionner la cellule D55 et enfoncer la touche F2 du clavier pour accéder à sa modification,
• Dans le deuxième argument de la plage de cellules, remplacer la référence D20 par D52,
• Puis, valider le calcul par la touche Entrée comme toujours,

Plutôt que de modifier la formule en lieu et place grâce à la touche F2, nous aurions aussi pu réaliser ces modifications depuis la barre de formule. Quoiqu'il en soit, bien que l'intégralité du tableau soit désormais désignée, le résultat ne change pas. En effet, les lignes au-delà de la ligne 20 sont masquées dans ce contexte correspondant à ces critères recoupés. Mais si nous devions engager d'autres filtres, la formule considérerait désormais les nouvelles lignes, ce que nous vérifierons.

• De la même manière, adapter le calcul de la somme (Sous Total) en D56 pour qu'il considère la plage de cellules D5:D52,
• Cliquer sur la flèche de filtre du champ Date,
• Dans la liste, ajouter le mois de Mars en cochant sa case,

Comme vous le constatez, de nouvelles lignes surgissent fort logiquement et en effet, nos calculs se mettent parfaitement à jour. Non seulement nous savons réaliser des opérations de synthèse sur les tableaux filtrés, mais en plus de cela, nous savons les rendre dynamiques. Nous pourrions même faire en sorte qu'ils soient évolutifs si le tableau source venait à s'enrichir en exploitant la fonction Excel Decaler, pour adapter dynamiquement les bornes de la plage de cellules, en fonction des nouvelles lignes.