Comment transformer une réf en la réf la plus proche de la liste

Bonjour à tous,

Mon problème est le suivant :

J'ai une base de donnée composée de plusieurs colonnes : (A / B / C / D / E / F / G / H)

La concaténation en colonne A des colonnes B / C / D / E / F /G me donne une référence (Key) qui me permet ensuite de faire une rechercheV dans cette base de donnée afin d'extraire l'info de la colonne H.

Dans le cas ou je retrouve bien la référence (Key) dans la feuille que je souhaite remplir, tout fonctionne mais dans les cas où je ne la trouve pas, j'aimerai quand même pouvoir renvoyer la valeur de la colonne H de la référence la plus proche dans ma base de donnée selon la logique suivante :

Valeur Colonne B toujours correcte / Valeur colonne C toujours correcte / Valeur Colonne D correcte ou la plus proche / Valeur colonne E correcte ou la plus proche / Valeur colonne F correcte ou la plus proche / Valeur colonne G correcte ou la plus proche

Vous trouverez ci-joint un fichier Excel pouvant servir d'exemple :

Onglet KPI : la liste de référence pour lesquelles je souhaite l'info présente en colonne H de la base de donnée

Onglet BDD : la base de donnée

Merci d'avance pour votre aide précieuse.

Cordialement,

Anthony

Bonjour,

modification de la formule RECHERCHEV en mettant VRAI au lieu de 0 en 4ième paramètre = recherche approximative, mise en tableau structuré votre source, puis tri de la colonne A du plus petit au plus grand (pas sur que la partie en italique soit nécessaire).

Voir le fichier si cela correspond à votre besoin :

@ bientôt

LouReeD

En fait ça ne marche pas.... Ou pas très bien

@ bientôt

LouReeD

En reprenant une proposition de recherche "à peu près" sur un autre fil, voici votre fichier en .xlsM car il y a utilisation d'une fonction personnalisée :

Cela semble mieux fonctionner, le principe :
grâce à la fonction on recherche la référence se rapprochant le plus de la référence donnée en colonne A par rapport à la liste "source", une fois trouvée, cette référence est utilisée pour retourner la valeur de la colonne H.

@ bientôt

LouReeD

Bonjour LouReeD,

Merci beaucoup pour ton retour rapide.

j'avais également pensé à cette option mais j'ai du mal à comprendre la logique qu'il y a derrière cette formule quand on a plusieurs valeurs numériques à comparer et du coup cela ne fonctionne pas toujours :

Si par exemple je cherche l'info pour la ref : CN-1588-1550-80-1,3-0,8

Si dans ma BDD j'ai ces deux chois :

Avec ta formule j'obtiens en résultat 3 alors que je souhaite avoir 2 car 100 est plus près de 80 que 110.

Si je change ma BDD pour tester d'autres scénarios et que j'ai ces trois choix :

Avec ta formule j'obtiens #NA comme résultat.

Je ne comprends pas bien quelle est la logique derrière cette formule.

Je pense avoir besoin d'un code VBA pour exprimer clairement la logique de calcule mais je n'arrive pas à le faire.

Merci encore pour ton aide.

Cordialement,
Anthony

C'est le constat du premier fichier ou du deuxième ?

@ bientôt

LouReeD

Bonjour,

Je viens de tester avec le fichier contenant la macro et cela ne fonctionne pas non plus car la référence renvoyée n'est pas la plus proche selon ma logique de calcule.

Exemple : pour la référence P-0603-50-10-0,1-0,5

S'il a le choix entre les références suivantes :

J'aimerai qu'il me renvoie la première ligne car 400 est la valeur la plus proche de 50 mais il me renvoie la deuxième ligne.

J'ai du mal à comprendre le code donc je ne sais pas comment le modifier afin qu'il suive ma logique de calcule.

Merci encore pour votre aide.

Cordialement,

Anthony

Bonsoir,

OK, je n'avais pas saisie le côté prioritaire de la chose, je travaillé avec le code fourni sur la globalité de la référence !

Est-ce urgent pour vous ? Le sujet est-il encore d'actualité ? Si urgent, le mieux est d'ouvrir un nouveau fil, sinon je vais tâcher de regarder cela assez vite... mais mes occupations parallèles...

@ bientôt

LouReeD

Bonjour,

Le sujet est toujours d'actualité. C'est assez urgent car j'aimerai pouvoir utiliser mon outil au plus vite et pour cela j'ai besoin de cette fonctionnalité donc si vous pouvez m'aider c'est top.

Je vais continuer à chercher également de mon côté.

Merci encore pour votre aide.
Cordialement,

Anthony

Bonsoir,

un petit test non fini, mais est-ce que les premiers résultats sont ceux voulus ?

Le fichier :

@ bientôt

LouReeD

bonjour Anthony0503, salut LouReed ,

je l'ai fait avec une fonction personnalisée, donc sans ce bouton et avec le tableau comme paramètre. Comme çà, la fonction n'est pas nécessairement volatile.

Bonjour à tous les deux,

Merci beaucoup pour votre aide!

j'ai également avancé de mon côté et je pense finalement n'avoir besoin que de la référence la plus proche (je fais ensuite une recherche depuis cette référence pour obtenir la valeur numérique finale souhaitée).

J'ai une solution qui fonctionne mais je pense pas que ça soit la plus efficace car la macro met environ 5 secondes pour faire 5 lignes et j'ai plusieurs centaines de lignes dans mon fichier.

J'ai essayé de comprendre le code que vous avez écrit mais c'est trop compliqué pour moi.

Dans la solution proposé par Bart, j'ai vu qu'il y avait en colonne E l'information dont j'ai besoin (la référence la plus proche de celle initiale).

Quand j'ai voulu ouvrir le fichier Excel m'a bloqué les macro, je les ai donc copiées-collées dans un nouveau fichier mais ça ne marche pas.

Est-ce que vous pouvez me dire ce que je dois mettre comme code pour obtenir uniquement l'info de la colonne E du fichier de Bart ?

Merci encore pour votre aide précieuse !

Cordialement,

Anthony

re,

1. les données de la feuille "BDD" sont dans un tableau structuré et ne pas dans une plage oridinaire, donc il faut transformer ces données en un tableau structuré.

.2. la formule sera =INDEX(temps(A18;TabDon);2) si vous voulez cette référence au lieu du temps

Les résultats sont correctes ?

Bonjour Bart,

Cela me renvoie le message d'erreur suivant :

Quand je regarde ce qui ne va pas au niveau de la formule, Excel me sélectionne TabDon.Dans la dernière version de mon fichier, je viens chercher uniquement la référence similaire ou la plus proche si pas de valeur exacte au sein d'une BDD composée d'un TCD. Ensuite je fais une recherche depuis cette référence dans le même TCD pour indiquer le temps moyen. Cela me permet d'afficher la référence Bis afin de vérifier qu'elle reste proche de la référence initiale.

Est-ce que tu saurais comment modifier le code afin d'obtenir cette référence Bis ?

Ci-joint la dernière version de mon fichier avec ce que je souhaite obtenir dans la colonne E.

Merci encore pour ton aide !

Cordialement,

Anthony

Bonsoir,

BsAlv : Je suis encore loin de ce type de programmation ! Avec ma vue "séquentielle" je ne savais plus trop où aller !

@ bientôt

LouReeD

Bonjour à tous ,

J'ai trouvé le problème original et donc je m'y suis intéressé.

Les hypothèses:

• j'ai laissé la base de données en plage ordinaire
• j'ai supprimé la colonne 1 de la base de donnée qui ne (me) sert à rien. La base de données ne contient plus que 7 colonnes de A à G (en cas d'une réelle nécessité d'une colonne contenant la concaténation, on peut la remettre mais en colonne H => la macro fonctionnera sans rien n'y changer)

Les paramètres de la fonction TempsValeurProche :

• Le premier paramètre est la référence ( exemple : A2 )
• Le deuxième paramètre est la base de données ( exemple BDD!A:G )
• Ce qui finalement donnerait : =TempsValeurProche(A2;BDD!A:G)

En sortie, la fonction retourne la valeur Temps (7 ème colonne de la base de données) sauf :

• si la référence (SERIE + MODELE) est absent de la base. Dans ce cas on retourne #N/A
• si plusieurs lignes sont des possibles résultats. Dans ce cas, on retourne #REF! (ce dernier cas serait à affiner en fonction de l'égalité ou non de toutes les valeurs de temps trouvées)

Le code est dans Module1 :

Function TempsValeurProche(xref As String, xBDD As Range)
Dim s, t, i&, j&, k&, n&, m&, min@, v@
  s = Split(xref, "-")
  i = xBDD.Parent.UsedRange.Row + xBDD.Parent.UsedRange.Rows.Count - 1
  t = xBDD.EntireColumn.Resize(i)
  For i = 2 To UBound(t)
      If t(i, 1) = s(0) And CStr(t(i, 2)) = s(1) Then n = n + 1: For j = 1 To UBound(t, 2): t(n, j) = t(i, j): Next
  Next i
  If n = 0 Then TempsValeurProche = CVErr(xlErrNA): Exit Function
  For j = 3 To 6
    min = CCur(String(14, "9")): m = 0
    For i = 1 To n: v = Abs(t(i, j) - CSng(s(j - 1))): min = IIf(v < min, v, min): Next
    For i = 1 To n
      If Abs(t(i, j) - CSng(s(j - 1))) = min Then
        m = m + 1: For k = 1 To UBound(t, 2): t(m, k) = t(i, k): Next
      End If
    Next i
    If m = 1 Then TempsValeurProche = t(1, 7): Exit Function
    n = m
  Next j
  If m = 1 Then TempsValeurProche = t(1, 7) Else TempsValeurProche = CVErr(xlErrRef)
End Function

re,

l'exemple de hier n'est pas le même que le problème initial !

Donc, j'ai modifié la fonction. Seulement pour la ligne 43, le résultat est différent, l'écart est le même (400), mais j'ai arrondi en haut.

Voilà, ça c'est fait !

@ bientôt

LouReeD

Merci beaucoup à tous pour votre aide !

Cordialement,

Anthony