Macro de recherche d'une valeur proche

[Sang1]

Bonjour à tous,

Ayant peu d'expérience en VBA, j'ai du mal à ne partir de rien, je sollicite donc votre aide !

Je dispose d'un fichier dans lequel se trouve des tableaux de données de mesures constitués comme ceci : pour une condition d'expérience donnée (portant sur une tension), je dispose d'une puissance débitée et d'un temps de décharge correspondant.

Dans un autre fichier, je calcule un besoin en puissance débitée, et j'aimerai qu'une macro trouve dans mon fichier de données le temps de décharge correspondant si je débite effectivement cette puissance, sachant qu'il faut chercher la valeur dans la bonne colonne correspondant à la tension appliquée lors de ma décharge.

J'espere avoir été clair ^^'

Je vous joint un fichier excel comme exemple.

Merci d'avance !

 

[job75]

Re : Macro de recherche d'une valeur proche

Bonjour Sang1,

Une macro ne paraît pas s'imposer ici, car elle ferait exactement la même chose que les formules que je vous propose.

1) menu Insertion-Nom-Définir et définir le nom plage par la formule :

=SI(Sheet1!$D$5=Sheet1!$G$2;Sheet1!$H$4:$H$10;SI(Sheet1!$D$5=Sheet1!$I$2;Sheet1!$J$4:$J$10))

Elle définit la plage des puissances utilisées et permet d'alléger considérablement la formule qui suit.

2) En D7, entrer la formule :

=DECALER(INDEX(plage;EQUIV(D6;plage;-1)+(ABS(D6-INDEX(plage;EQUIV(D6;plage;-1)))>ABS(D6-INDEX(plage;EQUIV(D6;plage;-1)+1))));;-1)

Malgré l'utilisation de plage, la formule reste lourde.

Fichier joint.

Edit 1 : dans la 2ème formule on utilise le signe >. Vous pouvez écrire >= suivant le résultat désiré en cas d'égalité des écarts.

Edit 2 : si l'on inverse l'ordre de classement des plages, il suffit de remplacer les 3 premiers -1 (dans EQUIV) par 1.

A+

 

[Sang1]

Re : Macro de recherche d'une valeur proche

Bonjour job75 et merci de ta réponse !

Ta formule fonctionne bien, il ne me reste plus qu'à la décortiquer.

Par contre, est ce qu'avec une formule on peut aller rechercher les données dans un autre fichier, car comme mentionné dans mon premier post, le tableau de valeurs est un fichier indépendant (il y a énormément de valeurs) de celui où le calcul de la puissance nécessaire est réalisé.

Merci pour ton aide !

 

[job75]

Re : Macro de recherche d'une valeur proche

Re,

Là c'est vraiment élémentaire.

Si la puissance mesurée se trouve dans la cellule A1 Feuil1 du fichier toto.xls, il suffit d'entrer en D6 la formule (formule de liaison) :

=[toto.xls]Feuil1!$A$1

Le fichier toto.xls peut être fermé.

A+

 

[Sang1]

Re : Macro de recherche d'une valeur proche

En effet c'était pas très compliqué.

Par contre, je me heurte à un autre problème : la façon de définir plage est "fixe", or j'ai en réalité toute une colonne de "puissances" auxquelles je souhaite affecter un temps de décharge. Ici, la définition de plage est lié à D6, ce qui ne me permet d'en trouver qu'une.

Le mieux ne serait il pas d'écrire une fonction VBA avec deux paramètres d'entrée (tension VpC et puissance) retournant le temps de décharge ?

Je m'y attele.

 

[job75]

Re : Macro de recherche d'une valeur proche

Re,

Il me semble que vous n'y êtes pas.

plage est définie par D5 et pas par D6.

Il suffit de tirer la formule vers le bas comme dans l'exemple ci-joint.

A+

 

[Sang1]

Re : Macro de recherche d'une valeur proche

Euh oui D5 pardon.

Les deux paramètres D5 et D6 sont susceptibles de varier, d'ou le probleme que j'ai avancé ci dessus (enfin je suppose que c'en est un !).

 

[job75]

Re : Macro de recherche d'une valeur proche

Re,

Si D5 varie, il suffit de remplacer $D$5 (référence absolue) par D5 (référence relative) dans la formule de définition de plage.

Mais là on travaille en aveugle. Il faudrait connaître la disposition que vous souhaitez pour vos données.

A+

 

[Sang1]

Re : Macro de recherche d'une valeur proche

Je vous transmet deux fichiers :
Le premier est un exemple du tableau dans lequel je dois effectuer mon calcul : je cherche donc à déterminer le temps de décharge en fonction de VpC et de P. VpC détermine dans quelle colonne je vais chercher P, et P me donnera le temps de décharge correspondant.

Le second est une partie (j'ai enlevé 90% des lignes) du tableau dans lequel j'ai mes données, et qui me permettra de déterminer le temps de décharge.


D'ou mon besoin d'être le plus souple possible.

Merci beaucoup pour votre aide en tout cas !

 

[job75]

Re : Macro de recherche d'une valeur proche

Re,

Bon, c'est vrai que s'il y a beaucoup de lignes, il vaut mieux utiliser une fonction macro, assez facile à écrire d'ailleurs.

Voici la fonction Th, à placer dans un module :

Function Th(V As Variant, P As Single) As Single
Dim plage As Range, pos%, p1 As Range, p2 As Range
With Sheets("Sheet1")
Set plage = IIf(V = .[G2], .[H4:H10], IIf(V = .[I2], .[H4:H10], Nothing))
End With
pos = Application.Match(P, plage, -1)
Set p1 = plage.Cells(pos)
Set p2 = plage.Cells(pos + 1)
Set p1 = IIf(Abs(P - p2) > Abs(P - p1), p1, p2)
Th = p1.Offset(, -1)
End Function

Il suffit d'entrer dans une cellule la formule :

=Th(V;P) où V est une des valeurs ou références G2 ou I2, et P la puissance mesurée.

Je vous laisse mettre tout ceci en place.

A+

 

[job75]

Re : Macro de recherche d'une valeur proche

Re,

Ouh là là, je viens de voir vos 2 fichiers, bien joué

Ce n'est plus le même problème qu'au début, il y a 7 tableaux possibles, et ceci dans un fichier différent de la restitution.

Là, la fonction macro est vraiment indispensable

Je vais reprendre cette macro, je ne devrait pas en avoir pour longtemps.

A+

 

[Sang1]

Re : Macro de recherche d'une valeur proche

D'accord, merci bien.

Je finirai ca demain dès l'aube, il me reste à gérer l'échange entre les deux fichiers (je ne sais jamais ou il faut mettre les .select, .activate dans la macro pour aller chercher le Sheet d'un autre fichier...)

 

[job75]

Re : Macro de recherche d'une valeur proche

Re,

Voici la fonction macro et les 2 fichiers modifiés :

Function Th(V As Variant, P As Single) As Single
Dim col As Byte, plage As Range, pos%, p1 As Range, p2 As Range
With Workbooks("[COLOR="Red"]Exemple tableau[/COLOR]").Sheets("[COLOR="Red"]Sheet1[/COLOR]") 'à adapter
col = Application.Match(V, .[[COLOR="Red"]2:2[/COLOR]], 0) + 1
Set plage = .Range(.Cells([COLOR="Red"]3[/COLOR], col), .Cells(10000, col))
End With
pos = Application.Match(P, plage, -1)
Set p1 = plage.Cells(pos)
Set p2 = plage.Cells(pos + 1)
Set p1 = IIf(Abs(P - p2) > Abs(P - p1), p1, p2)
Th = p1.Offset(, -1)
End Function

En rouge ce qu'il faut adapter au fichier réel.

Bien noter que V doit être exactement une des 7 valeurs du tableau.

Edit : pour répondre à ça :

je ne sais jamais ou il faut mettre les .select, .activate dans la macro pour aller chercher le Sheet d'un autre fichier

il faut savoir qu'en VBA il est presque toujours inutile d'activer des feuilles ou de sélectionner des cellules.

Il suffit de mettre devant la plage Range la feuille et le fichier (comme pour With dans la fonction Th). Utiliser With ou des variables quand c'est trop long.

A+

 

[Sang1]

Re : Macro de recherche d'une valeur proche

Génial, ca marche nickel, merci beaucoup pour le temps que vous avez passé dessus !

Cela m'a bien fait progresser en tout cas !

 

[Sang1]

Re : Macro de recherche d'une valeur proche

ReBonjour !

J'ai encore un petit soucis...

Voici comment j'ai adapté la macro :

Function Tpsdecharge(V As Variant, P As Single, Marque As String, Ref As String) As Single

Dim col As Byte, plage As Range, pos%, p1 As Range, p2 As Range, nom_fich As String

nom_fich = Marque + " " + Ref

With Workbooks(nom_fich).Sheets("Sheet1")
col = Application.Match(V, .[2:2], 0) + 1
Set plage = .Range(.Cells(3, col), .Cells(10000, col))
End With

pos = Application.Match(P, plage, -1)

Set p1 = plage.Cells(pos)
Set p2 = plage.Cells(pos + 1)
Set p1 = IIf(Abs(P - p2) > Abs(P - p1), p1, p2)
Tpsdecharge = p1.Offset(, -1) * 60

End Function

Le problème que j'ai, c'est qu'il faut nécessairement que les fichiers soient ouverts pour que la fonction retourne bien la valeur. Dans le cas contraire, elle retourne #VALUE
Comme les fichiers dans lesquels je vais chercher les données sont des "datasheets", je risque à terme d'en avoir beaucoup, et s'il faut que je les ouvre tous pour que ca marche, ca ne sera pas tres efficace.

Comment modifier la macro pour que je n'ai plus besoin d'ouvrir l'ensemble des fichiers pour qu'elle tourne ?

Merci d'avance !