[EXCEL]Recherche multi-critères dans un tableau

Signaler
Messages postés
2
Date d'inscription
mercredi 30 novembre 2005
Statut
Membre
Dernière intervention
4 décembre 2005
-
Messages postés
42
Date d'inscription
jeudi 17 juin 2004
Statut
Membre
Dernière intervention
11 septembre 2010
-
Bonjour à tous

Etant en train d'ébaucher un projet sous Excel, je me retrouve confronté à un double-problème, mais dont la racine est la même.

Le contexte du 1er problème:
---------------------------------
Je dispose d'un tableau à plusieurs colonnes. Je cherche dans un premier temps à ce que ne soit pris en compte que les lignes de la dernière colonne avec pour critères ce qu'il y a dans les colonnes 2 et 3.

Par exemple dans la colonne 1, je disposerais des dates sur une année. Dans la colonne 2, j'aurais "Culture" avec pour données "blé", "orge", et "tournesol". Dans la colonne 3, j'aurais "Agriculteur" avec pour données "Michel", "Jacques" et "Pierre". Enfin, dans la dernière colonne, j'aurais les quantités correspondantes.
Ainsi, on peut imaginer qu'à la ligne 2, j'aurais par exemple "10/06/2005" - "blé" - "Michel" - "30".

Maintenant, comment faire pour ne sélectionner que les lignes contenant par exemple "Orge" en colonne 2 et "Michel" en colonne 3? La finalité étant de donner le nb de fois où Michel a eu de l'orge. J'ai tenté le NB.SI, mais cette fonction ne marche qu'avec un seul critère de colonne...

Le contexte du 2nd problème:
----------------------------------
Toujours dans un tableau à plusieurs colonnes, la dernière affichant des données ciblées. Imaginons que dans la colonne 1 j'ai des entreprises "Moulinex", "Seb" et "Braun". Dans la 2ème colonne, j'ai le nom de quelques employés
"Michel", "Jacques" et "Pierre"
qui travaillent pour les 3 entreprises et dans la dernière le nb d'heures mensuelles de ces employés pour chaque entreprise.
Ainsi, on peut imaginer qu'à la ligne 2, j'aurais par exemple "Moulinex" - "Pierre" - "80".

Comment donc faire pour trouver le minimum d'heures travaillées pour une entreprise donnée, sachant que les données ne sont pas triées par entreprise?

Exemple:
Seb --------- Michel ----- 90
Moulinex --- Pierre ------ 80
Seb --------- Jacques --- 45
Braun ------- Pierre ----- 85
Moulinex --- Jacques --- 50

Le résultat de ma fonction, si le critère est "Moulinex" devrait être "50".

Voilà c'est long mais c'est pour être le plus clair possible
Je précise qui ce n'est pas possible avec des fonctions Excel, l'option VBA est tout à fait envisageable.
Merci d'avance

5 réponses

Messages postés
42
Date d'inscription
jeudi 17 juin 2004
Statut
Membre
Dernière intervention
11 septembre 2010

Pour ton 2ème problème la fonction à utiliser est : bdmin

col A Col B Col C

<COLGROUP>
<COL span=2 width=93>
<COL width=65>

----

1 Société ,
,
,
----

2 Moulinex ,
,
,
----

3,
,
,
----

4,
,
,
----

5 Société,
Employé,
Heures,
----

6 Seb
,
Michel
,
90,
----

7 Moulinex
,
Pierre
,
80,
----

8 Seb
,
Jacques
,
45,
----

9 Braun
,
Pierre
,
85,
----

10 Moulinex
,
Jacques
,
50

Cette Phrase est à mettre en A11 mergé jusqu'à C11
"Nombre d'heures minimum de la société " & A2 &" "

En D11 la fonction est : =BDMIN(A5:C10;"Heures";A1:A2)

Il suffit de changer le nom de la société en A2 pour avoir le nb mini d'heure de cette société.

Pour ton premier pb je pense qu'avec un =si(et(critère1;critère2);vrai;faux)
tu devrais le resoudre facilement en 2 étapes. Une première étape ecrirait un 1 dans la colonne complètement à doite, et il te suffira de faire le total de cette colonne pour connaitre le nb de fois ou etc....

Si cela a solutionner tes soucis en partie c'est génial. Bon courage.
Farouk
Messages postés
936
Date d'inscription
lundi 19 janvier 2004
Statut
Membre
Dernière intervention
17 mars 2017
4
Lut,
Pour ton premier prob je t'ai trouver cela sur la toile

'=Count2Ifs(A2:A17;"=";10;B2:B17;">";1
Function Count2Ifs(range1, equality1, value1, range2, equality2, value2)
'equality1 and equality2 could be =,>,<,<>,<=,>=

Const EvalStrings = ".>.<.<>.<=.>=.=."

If range1.Cells.Count <> range2.Cells.Count Then
Count2Ifs = CVErr(xlErrRef)
Exit Function
End If

If InStr(1, EvalStrings, "." & equality1 & ".") = 0 _
Or InStr(1, EvalStrings, "." & equality2 & ".") = 0 Then
Count2Ifs = CVErr(xlErrValue)
Exit Function
End If

Dim i As Long
Dim countt As Long
Dim eval1 As String
Dim eval2 As String

countt = 0
For i = 1 To range1.Cells.Count

eval1 = range1.Cells(i) & equality1 & value1
eval2 = range2.Cells(i) & equality2 & value2
If Evaluate(eval1) And Evaluate(eval2) Then
countt = countt + 1
End If
Next i

Count2Ifs = countt

End Function

Cordialement, Jean-Paul
______________________________________________________________________

Le Savoir n'a de valeur que s'il est partagé
Messages postés
2
Date d'inscription
mercredi 30 novembre 2005
Statut
Membre
Dernière intervention
4 décembre 2005

Merci beaucoup pour vos réponses!

A vrai dire, pour le problème1, j'ai planché dessus hier soir, après avoir tenté d'appliquer la solution de FaroukVazaha. Hélas elle ne me convenait pas, car dans mon tableau, il existe une multitude de variable dans chaque colonne; cela aurait fait beaucoup trop de rajout au tableau.

Donc j'ai fini par passer sur VBA. Mais même si le "codage" de FaroukVazaha ne me convenait pas, j'ai conservé l'idée de faire en 2 étapes; voilà comment j'ai fait:

Sub Analyse_clients()
c = 5
i = 4
' Selection de la première cellule du tableau à considérer, puis détermination de la taille du tableau
a = Cells(9, 2)
Sheets("Historique relations clients").Select
Range("B4").Select
b = ActiveCell.CurrentRegion.Rows.Count
PasFini = True
' Extraction des évènements correspondants à un numéro de client donné, dans un autre tableau, par analyse descendante du tableau "Historique"
While PasFini
d = Cells(i, 2)
If d = a Then
Cells(i, 3).Select
Selection.Copy
Cells(c, 10).Select
ActiveSheet.Paste
Cells(i, 4).Select
Selection.Copy
Cells(c, 9).Select
ActiveSheet.Paste
Cells(i, 5).Select
Selection.Copy
Cells(c, 11).Select
ActiveSheet.Paste
c = c + 1
End If
i = i + 1
If i = b + 2 Then
PasFini = False
End If
Wend
' Copie du tableau dans la feuille d'analyse clientèle, puis tri par date
Range("I5").Select
ActiveCell.CurrentRegion.Select
Selection.Copy
Selection.Clear
Sheets("Analyse clientèle actuelle").Select
Range("E5").Select
ActiveSheet.Paste
Selection.Sort Key1:=Range("E5"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
CutCopyMode = False
End Sub

Ainsi, dans ma première feuille, j'obtiens un tableau où un critère a déjà été utilisé. Il ne me reste plus qu'à utiliser un NB.SI pour le second critère

Je n'ai en revanche pas encore commencer à plancher sur le 2nd problème...

NB: comme vous pouvez le voir dans mon code, je sélectionne chaque cellule à copier séparément, car la fonction Range ne permet que la sélection de 2 Cells. Comment faire pour que je puisse copier une ligne avec 3 Cells?

Merci

[auteurdetail.aspx?ID=294617 ]
Messages postés
936
Date d'inscription
lundi 19 janvier 2004
Statut
Membre
Dernière intervention
17 mars 2017
4
Testes ce que j'ai trouvé sur la toile c'est une fonction NB.SI à deux critères que tu peux mettre dans tes cellules !!!

Cordialement, Jean-Paul
______________________________________________________________________

Le Savoir n'a de valeur que s'il est partagé
Messages postés
42
Date d'inscription
jeudi 17 juin 2004
Statut
Membre
Dernière intervention
11 septembre 2010

<COLGROUP>
<COL span=3 width=80>
<COL width=102>
<COL span=6 width=80>

----

Date ,
Culture,
Agriculteur,
Resultat du test,
Critères :,
maïs,
michel,
A7,
B7,
C7,
----

01/01/2005,
Blé,
Michel,
,
,
,
,
,
,
,
----

01/02/2005,
Orge,
Pierre,
,
,
,
,
,
,
,
----

01/03/2005,
Maïs,
Bernard,
,
,
,
,
,
,
,
----

01/04/2005,
Carotte,
Bernard,
,
,
,
,
,
,
,
----

01/05/2005 ,
Orge,
Michel,
,
,
,
,
,
,
,
----

01/06/2005 ,
Maïs,
Michel,
1,
,
,
,
,
,
,
----

01/07/2005,
Blé,
Pierre,
,
,
,
,
,
,
,
----

,
,
,
,
,
,
,
,
,
,
----

01/06/05 ,
Maïs,
Michel
10 colonnes sont nécessaires.

A1=Date
B1= culture
C1=Agriculteur
D1= Resultat du test
E1=Criteres :
F1=valeur du 1er critère
G1=Valeur du 2ème critère

Pour la bonne regle il faut nommer :
la colonne de A2 à A9 : ColDate
la colonne de B2 à B9 : ColProduit
la colonne de C2 à C9 : ColNom
la colonne de D2 à D9 : ColResultat

Tu dois dépasser la dernière ligne de ta matrice de 1 ligne car si tu veux ajouter des info sup il faudra faire "insertion ligne" sur cette ligne vide sous peine de pas avoir un ajout de ligne dans la colonne nommée. Et donc les infos ajoutées ne seront pas prise en compte.

La cellule F1 : TestProduit
La cellule G1 : TestNom
La cellule H1: ResultatDate
la cellule I1 : ResultatProduit
la cellule J1 : ResultatNom

la formule suivante donne le numero de la ligne de la matrice contenant la valeur cherchée, et non pas le numéro de la ligne du tableau Excel.
Formule de la cellule ResultatDate : ="A" & (EQUIV(1;ColResultat;0))+1
Formule de la cellule ResultatProduit : ="B" & (EQUIV(1;ColResultat;0))+1
Formule de la cellule ResultatNom : ="C" & (EQUIV(1;ColResultat;0))+1
Donc pour retrouver le numéro de la ligne du tableau Excel : le +1 est obligatoire car la matrice démarre en ligne 2. Si tu ne met pas d'entete de colonne tu devras supprimer ce +1.

Formule des cellules D2 à D9 : =SI(ET(ColCulture=TestProduit;ColNom=TestNom);1;0)
La formule est la même car tu fais référence au nom de la ou des colonnes. Tu peux mettre autant de critères que tu veux en les spécifiant dans la formule précédante.
N'oublie pas de rajoute tes valeurs de test dans les cellules Testxxxx que tu rajoutes en parallele.

Formule de la cellule A10 : =INDIRECT(ResultatDate)
Formule de la cellule B10 : =INDIRECT(ResultatProduit)
Formule de la cellule C10 : =INDIRECT(ResultatNom)

Il te suffit ensuite de saisir tes critères en F1= TestProduit et en G1=TestNom pour que sur la ligne 10 apparaissent les info correspondantes.

L'avantage de nommer les cellules et les colonnes c'est que si tu es ammené à deplacer les cellules les noms suivent et aucune formule n'est à changer.

En VBA la solution est plus complexe mais elle existe aussi.

Bon courage