Extraire sur des critères de sélection recoupé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 :


Recouper les données pour des études commerciales

Dans cette formation Excel, nous revenons sur des notions fort précieuses qui permettent de recouper des critères sur des bases de données, afin de réaliser des études sociologiques ou commerciales. Dans ce dernier cas, l'objectif consiste à mieux cibler sa clientèle.

Dans l'exemple que nous proposons ici, un tableau présente une liste de clients franciliens. Les informations de cette base de données sont volontairement restreintes pour simplifier la démonstration. Mais le principe est strictement identique pour des bases de données volumineuses.

Ces clients sont référencés par leur nom, prénom, selon leur civilité, âge et département. A l'instant T, la direction souhaite connaître le nombre de personnes correspondant à plusieurs critères recoupés, pour une action commerciale par exemple.



Nous proposons deux méthodes intéressantes pour réaliser ces analyses statistiques multicritères. Nous exposerons la première méthode avec les fonctions classiques. Puis, nous exploiterons une fonction dédiée aux bases de données.

Source et présentation de la problématique
Toute étude mérite des données à manipuler. C'est pourquoi nous proposons de récupérer un classeur, afin de débuter les travaux plus rapidement.

• Télécharger le classeur etudes-conditions-selectives.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles nommées respectivement : Nb.Si.Ens et BdNb. Les deux proposent le même tableau de données. L'intérêt réside dans la comparaison des deux méthodes en termes de souplesse et d'efficacité. Dans les deux cas, l'objectif est d'obtenir un résultat statistique sur le nombre de personnes correspondant aux multiples critères de sélection recoupés.

Dans la première feuille, nous souhaitons réaliser 4 synthèses selon 4 niveaux de recoupement :

• Combien sont des hommes : Critère Mr,
• Combien sont des hommes âgés de +35 ans : Critères Mr et >=35,
• Combien sont des hommes âgés entre 35 et 55 ans : Critères Mr, >=35 et <=55,
• Combien sont des hommes âgés entre 35 et 55 ans et habitant Paris : Critères Mr, >=35 et <=55, et 75 pour le département,

Nous allons apprendre des fonctions Excel qui permettent de réaliser des dénombrements, tantôt sur un seul critère, tantôt sur plusieurs critères vérifiés sur plusieurs plages de cellules.

Vérifier une condition sur une plage de données
Le premier calcul à réaliser se situe en cellule H8. Il ne nécessite aucun recoupement. Il consiste simplement à dénombrer les clients selon leur civilité inscrite en cellule I4. La fonction Excel qui permet de compter une valeur sur une plage de cellules est la fonction Nb.Si. Sa syntaxe est la suivante :

=Nb.Si(Plages_de_cellules_où_compter ; Valeur_à_compter)

La plage de cellules sur laquelle peut être décelée la présence de la civilité correspond à la colonne B. En désignant la colonne dans son intégralité (B:B), nous produirons un calcul évolutif. Si de nouveaux clients étaient ajoutés à la suite du tableau, ils seraient considérés.



Mais cette feuille est conçue de manière à ce que les critères et les plages sur lesquelles ils s'appliquent, soient dynamiques. Comme l'illustre la capture ci-dessus, en H4, les références de la colonne peuvent être changées grâce à une liste déroulante. Il en va de même pour les autres références de colonnes en J4, M4 et P4. Cependant, les références ainsi saisies dans des cellules, sont considérées comme du texte par Excel. Pour qu'elles puissent être interprétées en tant que telles dans un calcul, il faut les désigner grâce à la fonction Indirect : Indirect(H4). Littéralement, cette fonction transforme le texte en références interprétables.

• Sélectionner la cellule pour le calcul du dénombrement, soit H8,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction de dénombrement suivi d'une parenthèse, soit Nb.Si(,
• Saisir la fonction pour interpréter les références, suivie d'une parenthèse, soit Indirect(,
• Sélectionner la cellule H4 pour indiquer la plage de cellules du dénombrement,
• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de la valeur à compter,
• Sélectionner la cellule du critère, soit I4,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, valider le calcul à l'aide de la touche Entrée,

Le calcul retourne la valeur 15. Comme le tableau propose 30 clients, si nous changions la civilité en Mme, le résultat serait le même. Mais la formule est bien dynamique. Pour le constater, il suffit de remplacer une civilité par une autre, dans le tableau source. La formule que nous avons créée est la suivante :

=NB.SI(INDIRECT(H4); I4)

Elle a permis de retourner le nombre de fois que la valeur dynamique en I4, a été repérée dans la plage de cellules dynamique, mentionnée en H4.

Comptabiliser sur des critères recoupés
Il s'agit désormais d'affiner l'étude avec des contraintes plus strictes. En cellule H11, nous souhaitons connaître le nombre de personnes correspondant à deux critères recoupés. Une condition doit être posée sur la civilité et une autre sur l'âge. Quels sont les hommes de plus de 35 ans, par exemple. Le deuxième critère est inscrit en L4. Il est accompagné d'un opérateur de comparaison en K4. Il s'agira de concaténer ces deux informations dynamiques, afin de produire le critère final à dénombrer.

Comme vous l'avez constaté dans l'exemple précédent, la fonction Nb.Si est précieuse mais elle ne permet de dénombrer qu'un seul critère. Pour recouper les critères à compter, Excel propose la fonction Nb.Si.Ens. La formation Excel sur l'analyse décisionnelle multicritère l'avait mise en valeur. Sa syntaxe est la suivante :

=Nb.Si.Ens(Plage1 ; Critère1 ; Plage2 ; Critère2 ; ... ; PlageN ; CritèreN)

Elle permet donc d'énumérer les critères à vérifier et pour chacun d'eux, la plage de cellules correspondante sur laquelle il doit être compté. Autant de plages et de conditions que souhaitées, peuvent être ajoutées, séparées par des points-virgules. Dans le nom de la fonction, l'extension Ens signifie Ensemble.

Avec les niveaux de recoupements que nous allons réaliser, nous allons effectivement dénombrer un ensemble de critères, sur un ensemble de plages de cellules. Tous les critères doivent être vérifiés ensemble, pour que la donnée soit comptabilisée. Il suffit que l'un d'entre eux ne soit pas validé, pour qu'elle ne soit pas intégrée dans le décompte.

• Sélectionner la cellule du calcul, soit H11,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction du dénombrement multicritère, suivie d'une parenthèse, soit Nb.Si.Ens(,
• Saisir la fonction pour interpréter les références, suivie d'une parenthèse, soit Indirect(,
• Sélectionner la cellule H4 pour désigner la première plage de cellules,
• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument du critère à vérifier sur cette plage,
• Sélectionner la cellule I4 pour désigner ce critère,
• Taper un point-virgule (;) pour passer dans l'argument de la prochaine plage de cellules,
• Saisir la fonction pour interpréter les références, suivie d'une parenthèse, soit Indirect(,
• Sélectionner la cellule J4 pour mentionner la seconde plage de cellules à interpréter,
• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument du critère à vérifier sur cette plage,
• Sélectionner la cellule K4 pour désigner l'opérateur de comparaison,
• Enfoncer la touche 1 en haut à gauche du clavier pour le caractère de concaténation (&),
• Sélectionner la cellule L4 pour désigner la valeur du critère,
• Fermer la parenthèse de la fonction Nb.Si.Ens et valider le calcul,

Comme l'illustre la capture ci-dessus, le résultat s'affine en effet. Deux personnes ont été exclues par ce recoupement. Notre étude porte sur les hommes ayant plus de 35 ans. Le second critère sur la seconde plage n'a pas été vérifié pour deux d'entre eux.

En cellule K4, si vous changez l'opérateur >= par l'opérateur <=, vous obtenez le résultat inverse. Cette manipulation prouve que nos calculs sont parfaitement dynamiques, interprétant les opérateurs de comparaison comme des variables, grâce à la concaténation.



Les deux personnes précédemment exclues répondent désormais aux deux conditions à la fois (Homme et âge en dessous de 35 ans).

Pour la suite des opérations, il est préférable de recaler l'opérateur sur sa valeur d'orgine : >=.

La suite est une réplication de la même technique. Pour affiner les résultats avec un niveau de recoupement supplémentaire, il s'agit d'ajouter une plage dans l'énumération de la fonction Nb.Si.Ens, avec son critère associé. Pour adapter le calcul, il est conseillé de le répliquer de barre de formule à barre de formule, en le copiant, pour modifier la syntaxe.

• En cellule H14, adapter le calcul multicritère comme suit :

=NB.SI.ENS(INDIRECT(H4); I4; INDIRECT(J4); K4&L4; INDIRECT(M4); N4&O4)

Nous ajoutons la plage de cellules désignée par la cellule M4. Et sur cette plage, nous souhaitons vérifier le critère traduit par la concaténation des cellules N4 et 04, soit <=55. 3 personnes sortent du panel. Le résultat n'est plus que de 10 hommes sur les 15 de départ. Au fur et à mesure, l'étude s'affine. Nous en concluons donc que 3 hommes sont âgés de plus de 55 ans. Pour le vérifier, comme précédemment, il suffirait d'inverser l'opérateur en N4. Il faut en revanche opter pour une inégalité stricte (>). Certaines personnes sont en effet précisément âgées de 55 ans.

• Enfin en H17, adapter la formule comme suit :

=NB.SI.ENS(INDIRECT(H4); I4; INDIRECT(J4); K4&L4; INDIRECT(M4); N4&O4; Indirect(P4); Q4)

Nous intégrons une nouvelle plage de cellules désignée par la case P4. Sur cette plage nous souhaitons vérifier le critère du département inscrit en Q4. Il n'y a pas d'opérateur ici, donc pas de concaténation. Le résultat affiche la valeur 0. La cellule du département en Q4 est vide en effet. La condition ne peut pas être remplie.

• En Q4, saisir 75 et valider,

Il ne reste plus que 4 hommes, âgés entre 35 et 55 ans, habitant la ville de Paris. Cette démonstration en entonnoir prouve l'intérêt des critères en cascade que peut recouper la fonction Nb.Si.Ens. Pour mieux cibler sa clientèle sur une action commerciale, cette fonction propose d'affiner les résultats sans limites.

Vous avez noté le déclenchement automatique d'une mise en forme conditionnelle sur le tableau des clients. Celle-ci avait été paramétrée à l'origine. Elle permet de recouper les informations de l'analyse statistique multicritère. Elle consiste à déclencher une couleur de fond, lorsque les 4 critères sont satisfaits ensemble.

Zones de critères des bases de données
La fonction Nb.Si.Ens est décidément efficace et puissante. Mais avec les fonctions Excel de bases de données, il est possible non seulement de repousser les limites mais aussi d'augmenter la souplesse. Dans la formation Excel pour rechercher et extraire des données, nous avions présenté les fonctions BdSomme, BdMoyenne et BdNbVal entre autres. Elles permettent de réaliser les mêmes calculs que leurs homologues respectifs (Somme, Moyenne et Nombre de cellules non vides), mais sur la base de critères recoupés.

• Cliquer sur l'onglet BdNb en bas de la fenêtre Excel pour activer sa feuille,

Vous notez la présence du tableau des clients, reproduit à l'identique. L'objectif est de comparer les résultats ainsi que l'efficacité de la nouvelle méthode. A droite de ce tableau, figure une zone de critères. En dessous de chaque champ reproduit à l'identique par rapport aux titres du tableau, sont inscrits des critères respectifs.

Vous comprenez rapidement que la souplesse d'utilisation est intéressante. Les opérateurs peuvent être écrits directement dans la même cellule que les valeurs numériques. Sur un champ de type texte, le symbole de l'étoile (*) permet de considérer n'importe quelle information présente dans la colonne. Pour recouper plusieurs conditions sur un même champ, il suffit de dupliquer l'étiquette de la colonne (Age) pour lui apposer un nouveau critère.

Lorsque les conditions sont énumérées sur une même ligne, elles doivent toutes être vérifiées ensemble (Recoupées). Mais nous pourrions ajouter de nouveaux critères sur les lignes du dessous. Dans ce cas, les fonctions de base de données ajouteraient à l'analyse, les valeurs ayant rempli les conditions de la première ligne avec les valeurs ayant rempli les conditions de la deuxième, et ainsi de suite.

Comme précédemment, nous souhaitons connaître le nombre de personnes correspondant aux 4 critères recoupés. La fonction qui nous intéresse, se nomme BdNbVal. Sa syntaxe est la suivante :

=BdNbVal(Base_de_données ; Champ ; Zone_de_critères)

La base de données n'est autre que le tableau des clients. Le champ est le titre de la colonne sur laquelle le décompte doit s'opérer, en fonction des critères énoncés. Nous pourrons par exemple désigner le champ de la Civilité. Enfin, la zone de critères est le petit tableau préparé à l'avance, dans lequel sont énumérées les conditions à respecter, en dessous de chaque champ répliqué.

• Sélectionner la cellule du calcul, soit la cellule fusionnée en H8,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction de dénombrement dans une base de données, suivi d'une parenthèse, soit BdNbVal(,
• Sélectionner l'intégralité du tableau avec ses titres, soit la plage de cellules B4:F34,
• Taper un point-virgule (;) pour passer dans l'argument du champ sur lequel compter,
• Sélectionner le titre de la colonne Civilité, soit la cellule B4,
• Taper un point-virgule (;) pour passer dans l'argument de la zone de critères,
• Sélectionner le petit tableau des conditions avec ses titres, soit la plage de cellules H4:K5,
• Fermer la parenthèse de la fonction BdNbVal et valider le calcul avec la touche Entrée,

Le résultat tombe et affiche la valeur 30. Or, il y a 30 clients dans le tableau analysé. En effet, les critères tels qu'ils sont définis pour l'instant sont très larges. Aucune contrainte n'est posée sur la civilité à cause du symbole de l'étoile. L'âge doit être compris entre 0 et 100 ans. Donc tout le monde est concerné. Enfin, le département, considéré comme numérique par Excel, doit être supérieur à 0. Donc aucun département n'est ciblé.

• Dans la zone de critères en H5, taper le texte Mr pour ajouter le critère sur la civilité,

Le résultat tombe à 15 personnes et recoupe l'analyse précédente que nous avions effectuée avec la fonction Nb.Si.Ens.

• Dans la zone de critères en I5, taper la condition suivante pour l'âge : >=35,

Le résultat tombe à 13 comme l'analyse précédente sur la feuille Nb.Si.Ens.

• Dans la zone de critères en J5, ajouter la condition de recoupement sur l'âge : <=55,

Il ne reste plus que 10 hommes comme précédemment.

• Enfin dans la zone de critères en K5, taper 75 pour ajouter la condition sur le département,

Le résultat se restreint à 4 personnes recoupant celui de l'analyse précédente. Mais vous en conviendrez, cette zone de critères ouvre de nouvelles perspectives et offre beaucoup de souplesse. De plus, la syntaxe proposée par la fonction BdNbVal est plus simple que celle de la fonction Nb.Si.Ens :

=BDNBVAL(B4:F34;B4;H4:K5)

Cette remarque est d'autant plus vraie que le nombre de critères augmente. La fonction Nb.Si.Ens doit lister toutes les plages et tous les critères. La fonction BdNbVal se contente de désigner une plage de cellules dans laquelle ils sont énumérés. Sa syntaxe ne s'alourdit donc pas.

Quoiqu'il en soit, toutes deux permettent de réaliser des analyses statistiques multicritères avec beaucoup de puissance.