Recherches, sommes, compter selon plusieurs critères (multicritères)

Il arrive souvent de vouloir chercher une valeur dans un tableau d’après plusieurs critères.

Parfois nous souhaitons même chercher une partie d’un champ ou alors chercher sur des dates et heures (par exemple entre deux dates). Il existe des formules nous permettant de réaliser ces recherches, plus ou moins complexes en utilisant parfois des formules matricielles selon nos besoins.

Je vais vous présenter trois recherches dans cet article qui répondent à des besoin que j’ai pu avoir. Afin de bien comprendre, nous utiliserons l’exemple présenté ci-dessous:

Il s’agit là d’un planning qui se base sur une base de données dans un second onglet nommé « BDD »:

J’ai nommé chaque colonne de façon dynamique. Par exemple, pour la colonne employé:
=DECALER(BD!$A$2;1;0;NBVAL(BD!$A:$A)-2)

Si nous revenons dans le premier onglet (nommé « Planning »), nous pouvons voir qu’il s’affiche dynamiquement. En fait chaque fois que je change la date (cf. copie d’écran), le planning s’actualise en partant du principe suivant: la première date du planning correspond à la date choisie et nous affichons ensuite les dates sur 3 mois (ci-dessous la date choisie est le 21/10/2013). Le planning s’est automatiquement mis à jour et la date du jour est affichée en bleu.

Mais comment actualiser le contenu de chaque journée, à savoir Congé ou Maladie ou P1 (pour projet 1) ou P2 sans utiliser de macro ? Nous avons besoin de réaliser une recherche multicritère.

Méthode 1: Recherche d’une valeur unique selon plusieurs critères
Les critères sont donc les suivants dans notre exemple:

  • Le nom de l’employé,
  • la date du jour.

Et nous devons récupérer les deux premiers caractères de la colonne « Type ».
Pour cela, nous allons utiliser une formule matricielle (ci-dessous en C11):
{=SIERREUR(GAUCHE(INDEX(Tableau;EQUIV(1;(Employé=$B11)*(Dates=C$6);0);2);2);0)}

Attention, une telle formule répétée dans de nombreuses cellules comme dans notre exemple peut prendre du temps à être exécutée. C’est pour cette raison que j’ai choisi d’avoir des champs nommés élastiques (dynamiques avec la fonction DECALER, cf. ci-dessuss). Cela permet de réaliser la formule matricielle uniquement sur les données dont nous avons besoin et pas sur toute une colonne qui je le rappelle contient plus d’un million de lignes.

Revenons à présent sur cette formule. La partie importante est celle qui est en gras. Pour le reste, vous pouvez consulter l’article sur les fonctions INDEX / EQUIV.
EQUIV renvoie la position relative d’un élément dans une matrice qui correspond à une valeur spécifique dans un ordre spécifique. Le premier argument correspond à la valeur cherchée, le second à la matrice de recherche et la troisième correspond à la valeur à renvoyer (0 pour valeur exacte).

Donc notre valeur cherchée est 1, ce qui correspond à ‘VRAI’. Nous allons donc cherché dans la matrice que nous définissons par (Employé=$B11)*(Dates=C$6) la valeur VRAI.
La matrice est donc l’ensemble des combinaisons pour lesquelles nous avons le champ nommé « Employé » égal à $B11 (Employé_1) et le champ nommé « Dates » égal à C$6 (21/10/2013). Sans doute que vous voyez écrit 2013 pour la cellule C6 mais il s’agit du format qui a été modifié. Dans la cellule, nous avons bien la date 21/10/2013.

Avec la formule complète, nous devons récupérer la valeur cherchée mais à condition d’utiliser une formule matricielle (CTRL+SHIFT+ENTREE au moment de la validation) car nous appliquons des calculs sur des matrices (Employé et Dates).

Il suffit ensuite de répéter la formule dans les autres cellules et à chaque changement de date, le tableau se mettra automatiquement à jour.

Méthode 2: Nombre de valeurs selon plusieurs critères

A présent, nous souhaitons réaliser une synthèse concernant nos employés pour chaque type de travail et pour une période donnée (entre deux dates). Nous avons plusieurs méthodes pour obtenir ces informations mais je vais vous en proposer une.
Pour l’exemple, nous rechercherons pour Employé_2 du 21/10/2013 au 20/11/2013. Et nous cherchons le nombre de valeurs dans notre BDD qui correspondent aux critères suivants:

  • Employé = « Employé_2 »
  • Gauche(Type;2) = « Co »
  • Dates >= « 21/10/2013 »
  • Dates <= « 20/11/2013 ».
Nous utiliserons la formule suivante que nous répéterons en C24:D28
=SOMMEPROD(–(Employé=$B25);–(GAUCHE(Type;2)=C$23);–(Dates>=$C$20);–(Dates<=$F$20))

En fait il s’agit de la somme des produits. Ici chaque partie est une égalité, ce qui signifie que la valeur est égale à VRAI ou FAUX. De ce fait, nous comptons le nombre de combinaisons qui ont la valeur VRAI. Il faut pour cela que toutes les égalités aient la valeur VRAI. L’avantage d’avoir les — permet tout d’abord d’avoir une meilleure lisibilité de la formule mais cela nous permet également d’utiliser des fonctions à l’intérieur, comme la fonction GAUCHE par exemple.

Méthode 3: Somme d’après plusieurs critères
Enfin, nous souhaitons à présent connaître la charge pour chaque projet sur la période et pour chacun des employés (cellules E24:F28).
Nous appliquerons la même formule que pour les quantités mais nous ajouterons un argument: le champ nommé Charges. Ainsi, à chaque fois que nous trouverons la combinaison complète, la valeur sera VRAI (donc 1). Nous multiplierons donc 1 par la charge correspondante. Et la fonction SOMMEPROD nous permettra donc de faire la somme de tous ces produits.
La formule en E25 est donc la suivante:
=SOMMEPROD(–(Employé=$B25);–(GAUCHE(Type;2)=E$23);–(Dates>=$C$20);–(Dates<=$F$20);Charges)

Nous avons donc montré à travers ces exemples comment réaliser des recherches multicritères pour rechercher une valeur particulière, pour compter le nombre de solutions, ou faire une somme selon plusieurs critères.
Vous trouverez ICI le lien vers le fichier exemple.
REMARQUE: Un problème d’affichage est systématique concernant la sélection de la date, descendre puis remonter dans la feuille Excel pour corriger ce problème.

Leave a reply:

Your email address will not be published.

Site Footer

Sliding Sidebar

Inscrivez-vous et recevez ce livre gratuitement (cliquez sur l'image)