XL 2016 recherche formule

[hou hh]

bonjour

je sollicite votre aide pour déterminer le DPA (dernier prix d'achat) : j'ai des colonnes avec des différentes dates d'achat, articles et prix
mon objectif final est de calculer l'évolution pour chaque période, mais je dois déterminer le DPA
en pj le tableau

 

[njhub]

Bonjour hou hh,

Voyez si la solution proposée dans le fichier joint peut vous aider

😉

 

[hou hh]

bonjour
le résultat s'affiche en #NOM?

 

[chezswan]

bonjour

je sollicite votre aide pour déterminer le DPA (dernier prix d'achat) : j'ai des colonnes avec des différentes dates d'achat, articles et prix
mon objectif final est de calculer l'évolution pour chaque période, mais je dois déterminer le DPA
en pj le tableau

Bonjour hou hh,

un essais en P.J. avec =MAX(SI($A$2:$A$200=[@[GL_CODEARTICLE]];$B$2:$B$200)) puis
Ctrl, MAJ (Shift) et Entrée.
Au besoin dans la colonne F, indiquer qu'il s'agit d'un format date.

Cdlt
Swan

 

[hou hh]

Bonjour hou hh,

un essais en P.J. avec =MAX(SI($A$2:$A$200=[@[GL_CODEARTICLE]];$B$2:$B$200)) puis
Ctrl, MAJ (Shift) et Entrée.
Au besoin dans la colonne F, indiquer qu'il s'agit d'un format date.

Cdlt
Swan

Bonjour hou hh,

un essais en P.J. avec =MAX(SI($A$2:$A$200=[@[GL_CODEARTICLE]];$B$2:$B$200)) puis
Ctrl, MAJ (Shift) et Entrée.
Au besoin dans la colonne F, indiquer qu'il s'agit d'un format date.

Cdlt
Swan

Bonjour Swan,
merci, mais la valeur que je recherche c'est le prix de l'article (dernier prix d'achat) , exemple : si j'ai 3 differents sur 3 périodes, prix de la periode 1, période 2 et période 3 (période 3 c'est la plus récente) je recherche le prix de la période 2 afin de pourvoir calculer après l'evolution entre période 2 et 3

 

[job75]

Bonsoir,

Voyez le fichier joint, cette fonction VBA et la macro de tri Quick sort :

Function DPA(article, colarticle As Range, coldate As Range, colprix As Range, ordre%)
Dim i&, a(), b(), n&
For i = 1 To colarticle.Count
    If colarticle(i) = article Then
        ReDim Preserve a(n): ReDim Preserve b(n)
        a(n) = coldate(i): b(n) = colprix(i)
        n = n + 1
    End If
Next
tri a, b, 0, n - 1 'tri croissant sur les dates
If ordre = -1 Then
    If n > 1 Then DPA = b(n - 2) Else DPA = "n/a"
ElseIf ordre = 0 Then
    DPA = b(n - 1)
End If
End Function

Sub tri(a, b, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      temp = b(g): b(g) = b(d): b(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, b, g, droi)
If gauc < d Then Call tri(a, b, gauc, d)
End Sub

Le code de la fonction doit être placé impérativement dans un module standard.

Elle est utilisée dans les formules des colonnes F et G.

Bonne nuit.

 

[job75]

Bonjour le fil, le forum,

La méthode précédente a un très gros inconvénient.

Quand on ajoute une ligne ou qu'on modifie une cellule des colonnes A B D chaque formule des colonnes F et G est recalculée en étudiant toutes les lignes du tableau.

La durée des calculs peut être rédhibitoire s'il y a beaucoup de lignes.

J'ai testé en recopiant le tableau A2:H142 sur (seulement) 1410 lignes, le recalcul prend 12 secondes.

Dans le post qui suit je présenterai une méthode beaucoup plus rapide.

A+

 

[job75]

Avec ce fichier (2) on utilise maintenant cette macro évènementielle dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tablo, ub&, i&, x, prix1, prix0, j&
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
With ListObjects(1).Range 'tableau structuré
    .AutoFilter: .AutoFilter 'si le tableau est filtré
    .Columns(8).Insert xlToRight 'insère une colonne auxiliaire
    .Cells(2, 8) = "=N(R[-1]C)+1": .Columns(8) = .Columns(8).Value 'numérotation des lignes
    .Sort .Columns(1), xlAscending, .Columns(2), , xlDescending, Header:=xlYes 'tri sur 2 colonnes
    tablo = .Resize(, 8) 'matrice, plus rapide
    ub = UBound(tablo)
    For i = 2 To ub
        x = tablo(i, 1)
        If x <> tablo(i - 1, 1) Then
            prix1 = tablo(i, 4)
            tablo(i, 7) = prix1
            If i = ub Then
                tablo(i, 6) = "n/a"
            Else
                If tablo(i + 1, 1) <> x Then
                    tablo(i, 6) = "n/a"
                Else
                    prix0 = tablo(i + 1, 4)
                    tablo(i, 6) = prix0
                    For j = i + 1 To ub
                        If tablo(j, 1) <> x Then Exit For
                        tablo(j, 6) = prix0
                        tablo(j, 7) = prix1
                    Next j
                    i = j - 1
                End If
            End If
        End If
    Next i
    .Columns(6) = Application.Index(tablo, , 6) 'restitution
    .Columns(7) = Application.Index(tablo, , 7) 'restitution
    .Sort .Columns(8), xlAscending, Header:=xlYes 'tri dans l'ordre initial
    .Columns(8).Delete xlToLeft 'supprime la colonne auxiliaire
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle s'exécute automatiquement quand on modifie ou valide une cellule quelconque.

Sur 1410 lignes l'exécution est quasi immédiate (0,04 seconde).

Sur 14100 lignes l'exécution prend 1,3 seconde.

 

[hou hh]

Bonsoir,

Voyez le fichier joint, cette fonction VBA et la macro de tri Quick sort :

Function DPA(article, colarticle As Range, coldate As Range, colprix As Range, ordre%)
Dim i&, a(), b(), n&
For i = 1 To colarticle.Count
    If colarticle(i) = article Then
        ReDim Preserve a(n): ReDim Preserve b(n)
        a(n) = coldate(i): b(n) = colprix(i)
        n = n + 1
    End If
Next
tri a, b, 0, n - 1 'tri croissant sur les dates
If ordre = -1 Then
    If n > 1 Then DPA = b(n - 2) Else DPA = "n/a"
ElseIf ordre = 0 Then
    DPA = b(n - 1)
End If
End Function

Sub tri(a, b, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      temp = b(g): b(g) = b(d): b(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, b, g, droi)
If gauc < d Then Call tri(a, b, gauc, d)
End Sub

Le code de la fonction doit être placé impérativement dans un module standard.

Elle est utilisée dans les formules des colonnes F et G.

Bonne nuit.

Bonjour,
merci pour votre réponse
ce que je recherche ex

Bonsoir,

Voyez le fichier joint, cette fonction VBA et la macro de tri Quick sort :

Function DPA(article, colarticle As Range, coldate As Range, colprix As Range, ordre%)
Dim i&, a(), b(), n&
For i = 1 To colarticle.Count
    If colarticle(i) = article Then
        ReDim Preserve a(n): ReDim Preserve b(n)
        a(n) = coldate(i): b(n) = colprix(i)
        n = n + 1
    End If
Next
tri a, b, 0, n - 1 'tri croissant sur les dates
If ordre = -1 Then
    If n > 1 Then DPA = b(n - 2) Else DPA = "n/a"
ElseIf ordre = 0 Then
    DPA = b(n - 1)
End If
End Function

Sub tri(a, b, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      temp = b(g): b(g) = b(d): b(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, b, g, droi)
If gauc < d Then Call tri(a, b, gauc, d)
End Sub

Le code de la fonction doit être placé impérativement dans un module standard.

Elle est utilisée dans les formules des colonnes F et G.

Bonne nuit.

Bonjour, merci pour votre réponse
j'ai noté sur le tableau les valeurs que je recherche manuellement
pouvez vous me noter la formule pour les avoir

 

[job75]

pouvez vous me noter la formule pour les avoir

Je vous ai donné 2 solutions, la 2ème étant la bonne, faites un effort pour les comprendre.