Valeurs maximales conditionnelles 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 :


Valeurs maximales sous conditions

Dans cette formation, nous apportons la solution pour dresser des statistiques particulières, mais aussi particulièrement intéressantes. Il s'agit d'extraire les données numériques les plus grandes selon des contraintes à recouper.



Dans l'exemple illustré par la capture ci-dessus, nous réalisons l'extraction des salaires les plus grands selon la civilité et le service. Mais ces techniques peuvent se décliner à bien d'autres domaines. En outre, elles permettent de dégager les chiffres d'affaires les plus importants pour des produits ou commerciaux, selon des critères à définir.

Source et présentation des informations
Pour réaliser cette étude, nous proposons tout d'abord de réceptionner une source de données existante.

• Télécharger le classeur grandes-valeurs-criteres.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons un tableau énumérant les salariés d'une entreprise. Ils sont détaillés entre les colonnes B et F. Sur la droite, entre les colonnes H et L, un tableau de bord propose de réaliser ces fameuses extractions de salaires seuils.



Elles doivent être faites en fonction de la civilité inscrite sur la ligne de titre et du service inscrit sur la colonne de titre.

Extractions matricielles
Pour fournir ces résultats, le calcul doit être en mesure d'analyser l'ensemble des données sur chaque colonne où la condition est posée. Lorsque les contraintes sont recoupées, la valeur la plus grande pour l'enregistrement correspondant doit être extraite. Ce sont les calculs matriciels qui ont la faculté de construire un tel raisonnement, c'est-à-dire d'analyser globalement des plages de cellules croisées.

La fonction Excel Max est nécessaire. Sa vertu est de déceler la donnée numérique la plus grande dans une plage de cellules mentionnée. Mais la fonction conditionnelle Si doit lui être imbriquée de manière à ce que cette extraction respecte les conditions matricielles, issues du tableau de bord.



De plus, en déployant la liste de la zone Nom, vous pouvez constater que chaque colonne est identifiée par son titre. Nous utiliserons ces noms dans les formules matricielles pour simplifier la syntaxe et la construction.

Nous proposons de débuter par le calcul attendu en cellule I4. Il consiste à extraire le salaire le plus grand pour les femmes travaillant dans le service Comptabilité.

• Sélectionner la cellule I4,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir la fonction donnant la plus grande valeur suivie d'une parenthèse, soit : Max(,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Ouvrir une nouvelle parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la plage des civilités par son nom, soit : Civilité,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Dans le tableau de bord, cliquer sur la civilité associée, soit la cellule I3,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : I$3,

Ainsi, nous la libérons en colonne et la figeons en ligne. De cette manière, pour les lignes du dessous concernant les autres services, nous continuerons d'exploiter cette civilité de référence. En revanche, pour que le calcul puisse se répliquer pour les hommes, soit deux colonnes plus loin, il est nécessaire de la laisser se déplacer dans cette direction.

• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer le nouveau critère à recouper,
• Ouvrir une parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner la plage de cellules des services par son nom, soit : Service,
• Taper le symbole égal (=) pour annoncer la contrainte à honorer,
• Taper les coordonnées du premier service issu du tableau de bord, soit : H4,
• Enfoncer trois fois de suite la touche F4 du clavier, ce qui donne : $H4,

De cette manière, nous figeons la cellule en colonne et la libérons en ligne. Tous les services doivent en effet être passés en revue sur les lignes du dessous pour la réplication du calcul. En revanche, pour le reproduire sur la colonne des hommes, cette donnée ne doit pas changer de rangée.

• Fermer la parenthèse de la seconde matrice conditionnelle,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,

Si les contraintes recoupées sur les matrices mentionnées sont satisfaites, il s'agit d'extraire le salaire le plus grand pour les enregistrements concordants. Il suffit de désigner la plage des salaires.

• Désigner la plage de cellules des salaires par son nom, soit : Salaire,
• Puis, taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Fermer la parenthèse de la fonction Max,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

C'est ainsi que nous la transformons en calcul matriciel, raisonnant sur les lignes respectives des matrices impliquées.

• Double cliquer sur la poignée de la cellule du résultat pour la reproduire sur les autres services,
• Copier la sélection par le raccourci clavier CTRL + C par exemple,
• Sélectionner la première cellule pour les hommes soit : K4,
• Coller la formule par le raccourci clavier CTRL + V par exemple,

Instantanément, tous les meilleurs salaires par civilité et service sont extraits. Le copier-coller agit comme la poignée d'une cellule. Il réplique la logique du calcul en déplaçant les références des cellules non figées.

En scrutant les salaires de la base de données, vous constatez que ces extractions matricielles sont parfaitement cohérentes. La syntaxe de la formule matricielle que nous avons construite est la suivante : {=MAX(SI((Civilité=I$3)*(Service=$H4); Salaire; ''))}. Les accolades situées en préfixe et suffixe ont été générées grâce à cette validation spéciale par le raccourci clavier.

Extraire les salariés les mieux payés
Nous devons désormais extraire les noms des salariés les mieux payés. Pour cela, nous en avons l'habitude, nous devons exploiter les fonctions Index et Equiv.

=Index(Tableau_de_recherche; Indice_de_ligne; Indice_de_colonne)

La fonction Index permet d'extraire d'une plage de données, la cellule située au croisement d'une ligne et d'une colonne. La colonne est connue. Il s'agit de celle des noms. La ligne dépend de la position du salaire mais pas seulement. Plusieurs salaires peuvent être identiques. Il faut extraire le bon, donc recouper aussi les conditions sur la civilité et le service.

=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

La fonction Equiv donne la position d'une valeur cherchée dans une colonne, soit l'indice de ligne manquant pour la fonction Index. Mais pour honorer les trois contraintes à satisfaire, nous devons exploiter une astuce que nous avons déjà mise en oeuvre à maintes reprises. En guise de valeur cherchée, nous allons lui indiquer le chiffre 1. Ce dernier fera office de valeur booléenne pour confirmer les croisements des critères matriciels que nous allons lui passer en second paramètre. Il en résultera la position de l'enregistrement concordant.

• Sélectionner le premier nom à trouver, soit la cellule J4,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des noms par son intitulé, soit : Nom,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne,
• Saisir la fonction donnant la position cherchée, suivie d'une parenthèse, soit : Equiv(,
• Taper le chiffre 1 en guise d'indicateur booléen,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Ouvrir une première parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la colonne des salaires par son nom, soit : Salaire,
• Taper le symbole égal (=) pour annoncer le premier critère à satisfaire,
• Sélectionner le premier salaire extrait en cliquant sur sa cellule I4,
• Fermer la parenthèse de la première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la contrainte à recouper,
• Ouvrir une parenthèse pour accueillir la nouvelle matrice conditionnelle,
• Désigner la colonne des services par son nom, soit : Service,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Sélectionner le premier service du tableau de bord en cliquant sur sa cellule H4,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $H4,

Ainsi, nous la libérons en ligne et la conservons figée en colonne. Pour les mêmes raisons que précédemment, à réplication, nous avons besoin de passer en revue tous les services. Mais dans le même temps, le calcul doit être valable pour les hommes, situés sur une autre colonne.

• Fermer la parenthèse de cette deuxième matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la troisième contrainte à recouper,
• Ouvrir une parenthèse pour accueillir la dernière matrice conditionnelle,
• Désigner la colonne des civilités par son nom, soit : Civilité,
• Taper le symbole égal (=) pour annoncer le dernier critère à vérifier,
• Sélectionner la première civilité du tableau de bord en cliquant sur sa cellule I3,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : I$3,

De cette manière, nous la libérons en colonne pour répliquer le calcul sur la rangée des hommes. Nous la figeons en ligne pour que chaque calcul du dessous utilise cette référence de civilité immuable dans la contrainte.

• Fermer la parenthèse de la dernière matrice conditionnelle,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Saisir le chiffre 1 pour désigner la rangée de la seule colonne mentionnée pour l'extraction,
• Fermer la parenthèse de la fonction Index,
• Valider par le raccourci clavier CTRL + MAJ + Entrée pour la transformer en formule matricielle,
• Double cliquer sur la poignée du résultat pour extraire toutes les femmes par service,
• Copier la sélection par le raccourci clavier CTRL + C par exemple,
• Sélectionner la première cellule pour l'extraction des hommes, soit la cellule L4,
• Puis, coller la formule par le raccourci clavier CTRL + V par exemple,

L'extraction millimétrée est réalisée avec succès. Et pour le prouver, si vous modifiez certains salaires pour créer des redondances, vous constatez que la formule d'extraction, grâce aux conditions recoupées, rapatrie précisément la personne concernée. La formule matricielle d'extraction que nous avons bâtie est la suivante :

{=INDEX(Nom; EQUIV(1; (Salaire=I4)*(Service=$H4)*(Civilité=I$3); 0); 1)}

Repérer les salariés dans le tableau
Pour parachever l'application et renforcer l'interprétation, nous souhaitons faire réagir dynamiquement les lignes des salariés ainsi extraits. Nous devons bâtir une règle de mise en forme conditionnelle. L'astuce la plus simple consiste à exploiter la fonction Nb.Si : =Nb.Si(Plage; Critère). En guise de critère, nous lui passerons chaque nom de salarié issu du tableau source à chercher dans la plage des noms extraits dans le tableau de bord. Lorsque la correspondance est avérée, la ligne doit être repérée.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:F21,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie juste en-dessous pour l'activer,
• Dès lors, saisir la syntaxe suivante pour la règle : =Nb.Si($J$4:$J$7; $B4)>0,

Une mise en forme conditionnelle raisonne chronologiquement. C'est la raison pour laquelle nous débutons l'analyse à partir du premier nom en cellule B4. Nous libérons sa ligne ($B4) pour qu'ils soient tous passer en revue. Sa colonne est conservée figée en revanche. En effet, pour un même enregistrement, la présence du salarié doit forcément être analysée par rapport à cette colonne. Ce nom, nous cherchons à le trouver dans la plage de cellules des femmes ($J$4:$J$7). S'il est trouvé (>0), nous savons qu'il s'agit du meilleur salaire pour le service et la civilité. En conséquence, nous devons associer un format dynamique approprié à cette règle.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange clair se rapprochant du rose,
• Cliquer sur le bouton Ok pour valider ce réglage,

Nous sommes de retour sur la première boîte de dialogue. L'aperçu est explicite. Chaque femme la mieux payée dans son service doit être identifiée sur un fond orange clair.

• Cliquer de nouveau sur le bouton Ok pour valider la création de cette règle de mise en forme,

De retour sur la feuille, vous constatez que les enregistrements des femmes concernées réagissent dynamiquement. Nous devons faire de même pour les hommes en adaptant la plage de recherche ainsi que la couleur de fond. Toutes les données du tableau doivent préalablement être sélectionnées comme précédemment.

La syntaxe de la règle de mise en forme conditionnelle est la suivante : =Nb.Si($L$4:$L$7; $B4).



De plus, il apparaît opportun de lui associer un fond bleu clair.