fonction pour chercher une valeur en fonction de 2 conditions

benjy555 · 27 Mars 2013

Bonjour

Je vous explique mon problème sur lequel je m'arrache les cheveux (le mieux étant de voir directement le fichier excel joint) :

Colone A | Colone B | Colone C | Colone D | Colone E
Nom | Performance | date de mesure | ecart date\aujourdhui | Performance a retenir

Le but est de chercher la colonne E performance à retenir mais le problème est que pour un même Nom en colonne A j'ai plusieurs lignes cad plusieurs mesure à des dates différentes.
Et je veux indiquer par une fonction en colonne E la performance (de la colonne B) dont la date de mesure (en colonne C) aura l’écart MINIMAL avec la date d'aujourd'hui (calculée en colone D).

Pour résumer, la fonction doit faire ceci : chercher pour tous les nom en A, le minimum des valeurs en D, et faire figurer la valeur de B correspondante.

Bon j’espère que c'est assez clairement expliqué... merci pour votre aide !

benjy555 · 31 Mars 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

En plus je viens d'utiliser ta base de 60 000 lignes avec la fonction de r@chid , et après env 20 min les résultats sont finalement faux et incohérent ... étrange car sur quelques dizaines de lignes elle marchait très bien ...

Donc je pens que l'idéal serait vraiment de comprendre comment adapter ta macro sur on fichier..

mapomme · 31 Mars 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Bonjour benjy555,

J'ai toujours vu les formules de R@chid fonctionner. As-tu bien trié le tableau ?

Pour le code de la macro. Je le mets ci-dessous. Il faut le copier dans le module de code de la feuille qui contient le tableau. J'ai placé les résultats en colonne J.

Code de la macro:

VB:

Sub Recherche_Perf()
Dim tablo, xrg As Range
Dim i As Long, i0 As Long, i1 As Long
Dim dico As Dictionary, Date_Perf(1 To 2)
Application.ScreenUpdating = False
  Set dico = CreateObject("Scripting.Dictionary")
  
  i = Cells(Rows.Count, "a").End(xlUp).Row
  Set xrg = Range(Range("a2"), Range("I" & i))
  tablo = xrg.Value: i0 = LBound(tablo): i1 = UBound(tablo)
  
  For i = i0 To i1
    If tablo(i, 1) <> "" Then
      If Not dico.exists(tablo(i, 1)) Then
        Date_Perf(1) = tablo(i, 4)
        Date_Perf(2) = tablo(i, 9)
        dico(tablo(i, 1)) = Date_Perf
      Else
        If Abs(tablo(i, 4) - Date) < Abs(dico(tablo(i, 1))(1) - Date) Then
          Date_Perf(1) = tablo(i, 4)
          Date_Perf(2) = tablo(i, 9)
          dico(tablo(i, 1)) = Date_Perf
        End If
      End If
    End If
  Next i
  
  ReDim res(i0 To i1)
  For i = i0 To i1
    If tablo(i, 1) <> "" Then res(i) = dico(tablo(i, 1))(2)
  Next i
  
  Range("j2").Resize(i1 - i0 + 1) = Application.Transpose(res)
  MsgBox "Recherche Performance : Terminée !"
Application.ScreenUpdating = True
End Sub

Rem: il vaut mieux préférer le code de la version v2a à celui de la version v2 ICI.

benjy555 · 31 Mars 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Concernant la formule de r@chid j pense pas avoir fait de mauvaise manip. jai copié collé tes lignes générées dans son fichié puis j'ai tiré la fonction jusqua la 60000 ieme ligne et ensuite en filtrant sur un nom au hasard j'observe les resultat et la ce n'est pas cohérant...

Merci pour le nouveau code, c'est donc directement adapté à mon fichier ?
Et si je veux déplacé le résultat sur une autre colone que la J peux tu me dire comment faire?

Et au passage, peux tu expliquer ce que cela signifie : (i, 1) ou (i, 9) etc je tente de décoder tout ça mais c'est comme du chinois pour moi... en gros je comprend ("colonne", 1) mais le chiffre c'est quoi ??

R@chid · 31 Mars 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Bonsoir @ tous,
Salut mapomme,

R@chid à dit:
Bonsoir,
Oui c'est normal puisque la formule est matricielle, alors tu auras certainement ce problème de ralentissement...
Si ta Base est triée par noms, on peut faire par formule classique...
Voir PJ
@ + +

Comme je l'ai signalé il faut que la base soit triée par "Noms", si c'est déjà le cas ce sera juste un problème de création des champs dynamiques car il faut mettre le curseur dans une cellule quelconque de la ligne 2 par exemple "A2" avant d'ouvrir le Gestionnaire de Noms pour créer le champ Plage_Dates par cette formule,

Code:

=DECALER(Feuil1!$D$1;EQUIV(Feuil1!$A2;Feuil1!$A:$A;0)-1;;NB.SI(Feuil1!$A:$A;Feuil1!$A2))

@ + +

mapomme · 1 Avril 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Bonsoir benjy555, R@chid,

benjy555 à dit:
Merci pour le nouveau code, c'est donc directement adapté à mon fichier ?

Normalement oui. Mais n'ayant pas le fichier réel, je ne peux rien affirmer à 100%.

benjy555 à dit:
Et si je veux déplacé le résultat sur une autre colone que la J peux tu me dire comment faire?

A la fin de la macro, il y a l'instruction: Range("j2").Resize(i1 - i0 + 1) = Application.Transpose(res)

Pour coller le résultat dans une autre colonne que J (par exemple la colonne P), il devrait suffire de remplacer "j2" par "p2"

benjy555 à dit:
Et au passage, peux tu expliquer ce que cela signifie : (i, 1) ou (i, 9) etc je tente de décoder tout ça mais c'est comme du chinois pour moi... en gros je comprend ("colonne", 1) mais le chiffre c'est quoi ??

il faut plutôt comprendre (ligne,colonne). Je vais essayer de commenter mon code. Ce sera pour demain.

mapomme · 1 Avril 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Bonjour benjy555,

benjy555 à dit:
Et au passage, peux tu expliquer ce que cela signifie : (i, 1) ou (i, 9) etc je tente de décoder tout ça mais c'est comme du chinois pour moi... en gros je comprend ("colonne", 1) mais le chiffre c'est quoi ??

En fichiers joints:

Une version v2a dans laquelle j'ai corrigé un bug possible.
Une tentative d'explication du code (moins clair que je ne le souhaitais - moins facile que je ne le pensais!)

Rem: il vaut mieux préférer le code de la version v2a à celui de la version v2.

benjy555 · 1 Avril 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Franchement je suis impressionné ! c'est vraiment sympa de prendre tout ce temps afin d'expliquer aux nul comme moi des choses comme ceci ! Vraiment bravo a toi et aux autres !

J'ai finalement compris l’intérêt de trier à la fin effectivement cela donne le vrai résultat cohérent sur la formule de r@chid et aussi sur la tienne (mapomme) . j'ai d’ailleurs utilisé le code vba de la sub "trier" de ton ancien fichier que j'ai ajouté sur le nouveau fichier !
seul petit détail j'arrive pas à l'intégrer à la fin de la macro et donc je suis obligé de lancé une macro après l'autre ( suis vraiment naz !)

Grace aux explications je devrai réussir a ajuster la macro à ma base, je m'y met dès mardi quand j'arrive au taf. je ne vous ai pas donné directement ma base car mon but et de comprendre pas de vous filer le boulo et recvoir un truc tout fait, pardon si ça vs cause plus de travail au final...

dernier petit détail, ma base est actualisée à partir d'une table externe OBCDE, celà ne pose pas de problème par rapport à la macro ? ( une fois évidement que j'aurai bien vérifié que les info pertinentes se trouvent bien dans les bonnes colonnes)

Je vais même tenter d'integrer l'actualisation de la base à la macro ainsi en clic tout sera fraîchement calculé !

benjy555 · 2 Avril 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Bon testé en situation réel ...... tout marche nikel !!!

Sauf (et oui il y a tjr un mais...) je viens de me rendre compte d'un petit probleme :

Il se trouve qu'il y a quelques "nom" de la colonne A qui ont exactement la date de mesure mais pas la même performance du coup le code fait ressortir des ces cas bien précis la première des valeur qu'il trouve cad 0 dans ma base alors qu'il y a systématiquement pour le meme nom et même date une performance qui est >0

Donc est ce possible d’ajouter au moment de la recherche quelque chose du style :

Si pour toute les ligne avec nom est date identique prendre la perf qui a la date la plus récente et SI LES DATE SONT IDENTIQUE PRENDRE LA PERF QUI EST >0

Voila je pense que ça devrai être facil pour toi de modifier la macro

Merci encor

mapomme · 2 Avril 2013

Re : fonction pour chercher une valeur en fonction de 2 conditions

Bonjour benjy555,

Voir l'adaptation dans le fichier.

fonction pour chercher une valeur en fonction de 2 conditions

benjy555

XLDnaute Junior

Pièces jointes

benjy555

XLDnaute Junior

mapomme

XLDnaute Barbatruc

Pièces jointes

benjy555

XLDnaute Junior

R@chid

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

benjy555

XLDnaute Junior

benjy555

XLDnaute Junior

mapomme

XLDnaute Barbatruc

Pièces jointes

Discussions similaires