Je souhaite réaliser l"application suivante, dont l'utilisation pourrait
être très classique parmi les utilisateurs d'excel ayant à traiter des
données de mesures en utilisant une table de conversion a deux entrées.
J'ai une première feuille contenant ma table de conversion a 2 entrées :
- première ligne : valeurs de ma première entrée en B1,C1,D1,....X1
- première colonne : valeurs de ma deuxième entrée en A2, A3, A4, ....Aj
- dans le tableau B2;Xj les valeurs résultantes correspondants aux en-tetes
de ligne et de colonne.
Si le paramètre colonne=25 et le paramlètre ligne = 8, le résultat est 12
On suppose que les paramètres lignes et colonnes sont classés dans l'ordre
croissant.
Je souhaite, à partir de données contenues dans une autre feuille de calcul
pouvoir calculer le résultat, sachant que les valeurs du paramètre ligne et
du paramètre colonne ne sont pas exactement égales aux valeurs des entêtes
dans la table. Par exemple je veux connaitre le résultat pour 27 et 8.2 (et
non pas 25 et 8). Si on fait le calcul manuel, on fait une interpolation
lineaire entre 25 et 35 pour les colonnes et entre 8 et 11 pour les lignes
et on trouve un résultat compris entre les 4 valeurs extrèmes (le barycentre
de 12,13,11,11).
C'est réalisable avec des fonctions simples d'excel (recherche, index et 4
opérations....), mais la formule est tellement longue qu'on ne peut pas la
rentrer dans une seule cellule. Pour que ce soit compréhensible il faut un
certain nombre de cellules intermédiaires et ca rend les choses un peu
lourdes !. Cette application devant être facilement transportable sur
d'autres cellules ou d'autres tables, comment est-il possible de réaliser
une telle fonction : avec une procédure sub ou function qu'on pourrait
appeler de n'importe quelle cellule, en transmettant les deux paramètres
necessaires (adresses des cellules contenant les valeurs d'entrées) ?
L'application classique est par exemple la suivante :
J'ai enregistré dans une feuille la pression de la vapeur en colonne 1, j'ai
sa température en colonne 2, j'ai la table qui donne la masse volumique de
la vapeur en fonction de sa pression et de sa température dans une deuxième
feuille. Je voudrait faire une colonne 3 avec la masse volumique
correspondante pour chaque ligne (sans introduire 4 ou 5 colonnes
supplémentaires de calculs intermédiaires sur cette feuille ou sur une autre
!).
Si quelqu'un peut me donner ici les pistes de démarrage ! Merci !
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
isabelle
bonjour Jean,
si tu me montre la formule je pourrais la transformer en fonction personnalisé.
isabelle
Bonjour
Je souhaite réaliser l"application suivante, dont l'utilisation pourrait être très classique parmi les utilisateurs d'excel ayant à traiter des données de mesures en utilisant une table de conversion a deux entrées.
J'ai une première feuille contenant ma table de conversion a 2 entrées : - première ligne : valeurs de ma première entrée en B1,C1,D1,....X1 - première colonne : valeurs de ma deuxième entrée en A2, A3, A4, ....Aj - dans le tableau B2;Xj les valeurs résultantes correspondants aux en-tetes de ligne et de colonne.
Si le paramètre colonne% et le paramlètre ligne = 8, le résultat est 12 On suppose que les paramètres lignes et colonnes sont classés dans l'ordre croissant.
Je souhaite, à partir de données contenues dans une autre feuille de calcul pouvoir calculer le résultat, sachant que les valeurs du paramètre ligne et du paramètre colonne ne sont pas exactement égales aux valeurs des entêtes dans la table. Par exemple je veux connaitre le résultat pour 27 et 8.2 (et non pas 25 et 8). Si on fait le calcul manuel, on fait une interpolation lineaire entre 25 et 35 pour les colonnes et entre 8 et 11 pour les lignes et on trouve un résultat compris entre les 4 valeurs extrèmes (le barycentre de 12,13,11,11).
C'est réalisable avec des fonctions simples d'excel (recherche, index et 4 opérations....), mais la formule est tellement longue qu'on ne peut pas la rentrer dans une seule cellule. Pour que ce soit compréhensible il faut un certain nombre de cellules intermédiaires et ca rend les choses un peu lourdes !. Cette application devant être facilement transportable sur d'autres cellules ou d'autres tables, comment est-il possible de réaliser une telle fonction : avec une procédure sub ou function qu'on pourrait appeler de n'importe quelle cellule, en transmettant les deux paramètres necessaires (adresses des cellules contenant les valeurs d'entrées) ?
L'application classique est par exemple la suivante : J'ai enregistré dans une feuille la pression de la vapeur en colonne 1, j'ai sa température en colonne 2, j'ai la table qui donne la masse volumique de la vapeur en fonction de sa pression et de sa température dans une deuxième feuille. Je voudrait faire une colonne 3 avec la masse volumique correspondante pour chaque ligne (sans introduire 4 ou 5 colonnes supplémentaires de calculs intermédiaires sur cette feuille ou sur une autre !).
Si quelqu'un peut me donner ici les pistes de démarrage ! Merci !
bonjour Jean,
si tu me montre la formule je pourrais la transformer en fonction
personnalisé.
isabelle
Bonjour
Je souhaite réaliser l"application suivante, dont l'utilisation pourrait
être très classique parmi les utilisateurs d'excel ayant à traiter des
données de mesures en utilisant une table de conversion a deux entrées.
J'ai une première feuille contenant ma table de conversion a 2 entrées :
- première ligne : valeurs de ma première entrée en B1,C1,D1,....X1
- première colonne : valeurs de ma deuxième entrée en A2, A3, A4, ....Aj
- dans le tableau B2;Xj les valeurs résultantes correspondants aux en-tetes
de ligne et de colonne.
Si le paramètre colonne% et le paramlètre ligne = 8, le résultat est 12
On suppose que les paramètres lignes et colonnes sont classés dans l'ordre
croissant.
Je souhaite, à partir de données contenues dans une autre feuille de calcul
pouvoir calculer le résultat, sachant que les valeurs du paramètre ligne et
du paramètre colonne ne sont pas exactement égales aux valeurs des entêtes
dans la table. Par exemple je veux connaitre le résultat pour 27 et 8.2 (et
non pas 25 et 8). Si on fait le calcul manuel, on fait une interpolation
lineaire entre 25 et 35 pour les colonnes et entre 8 et 11 pour les lignes
et on trouve un résultat compris entre les 4 valeurs extrèmes (le barycentre
de 12,13,11,11).
C'est réalisable avec des fonctions simples d'excel (recherche, index et 4
opérations....), mais la formule est tellement longue qu'on ne peut pas la
rentrer dans une seule cellule. Pour que ce soit compréhensible il faut un
certain nombre de cellules intermédiaires et ca rend les choses un peu
lourdes !. Cette application devant être facilement transportable sur
d'autres cellules ou d'autres tables, comment est-il possible de réaliser
une telle fonction : avec une procédure sub ou function qu'on pourrait
appeler de n'importe quelle cellule, en transmettant les deux paramètres
necessaires (adresses des cellules contenant les valeurs d'entrées) ?
L'application classique est par exemple la suivante :
J'ai enregistré dans une feuille la pression de la vapeur en colonne 1, j'ai
sa température en colonne 2, j'ai la table qui donne la masse volumique de
la vapeur en fonction de sa pression et de sa température dans une deuxième
feuille. Je voudrait faire une colonne 3 avec la masse volumique
correspondante pour chaque ligne (sans introduire 4 ou 5 colonnes
supplémentaires de calculs intermédiaires sur cette feuille ou sur une autre
!).
Si quelqu'un peut me donner ici les pistes de démarrage ! Merci !
si tu me montre la formule je pourrais la transformer en fonction personnalisé.
isabelle
Bonjour
Je souhaite réaliser l"application suivante, dont l'utilisation pourrait être très classique parmi les utilisateurs d'excel ayant à traiter des données de mesures en utilisant une table de conversion a deux entrées.
J'ai une première feuille contenant ma table de conversion a 2 entrées : - première ligne : valeurs de ma première entrée en B1,C1,D1,....X1 - première colonne : valeurs de ma deuxième entrée en A2, A3, A4, ....Aj - dans le tableau B2;Xj les valeurs résultantes correspondants aux en-tetes de ligne et de colonne.
Si le paramètre colonne% et le paramlètre ligne = 8, le résultat est 12 On suppose que les paramètres lignes et colonnes sont classés dans l'ordre croissant.
Je souhaite, à partir de données contenues dans une autre feuille de calcul pouvoir calculer le résultat, sachant que les valeurs du paramètre ligne et du paramètre colonne ne sont pas exactement égales aux valeurs des entêtes dans la table. Par exemple je veux connaitre le résultat pour 27 et 8.2 (et non pas 25 et 8). Si on fait le calcul manuel, on fait une interpolation lineaire entre 25 et 35 pour les colonnes et entre 8 et 11 pour les lignes et on trouve un résultat compris entre les 4 valeurs extrèmes (le barycentre de 12,13,11,11).
C'est réalisable avec des fonctions simples d'excel (recherche, index et 4 opérations....), mais la formule est tellement longue qu'on ne peut pas la rentrer dans une seule cellule. Pour que ce soit compréhensible il faut un certain nombre de cellules intermédiaires et ca rend les choses un peu lourdes !. Cette application devant être facilement transportable sur d'autres cellules ou d'autres tables, comment est-il possible de réaliser une telle fonction : avec une procédure sub ou function qu'on pourrait appeler de n'importe quelle cellule, en transmettant les deux paramètres necessaires (adresses des cellules contenant les valeurs d'entrées) ?
L'application classique est par exemple la suivante : J'ai enregistré dans une feuille la pression de la vapeur en colonne 1, j'ai sa température en colonne 2, j'ai la table qui donne la masse volumique de la vapeur en fonction de sa pression et de sa température dans une deuxième feuille. Je voudrait faire une colonne 3 avec la masse volumique correspondante pour chaque ligne (sans introduire 4 ou 5 colonnes supplémentaires de calculs intermédiaires sur cette feuille ou sur une autre !).
Si quelqu'un peut me donner ici les pistes de démarrage ! Merci !
FxM
Bonsoir Jean,
Le problème n'est pas uniquement de faire une interpolation entre les valeurs mais aussi de trouver les bonnes valeurs à utiliser.
J'ai développé cela pour mon boulot (calcul de Z à partir d'une table) sous forme d'une fonction perso. Je te laisse adapter.
Insertion | module | copies le code qui suit : Public Function TableZ999(pres, temp) Application.Volatile For Each elt In Range("GAMP") If elt.Value <= pres And elt.Offset(1, 0).Value > pres Then p1 = elt.Row - Range("GAMP")(1).Row + 1 End If Next elt For Each elt In Range("GAMT") If elt.Value <= temp And elt.Offset(0, 1).Value > temp Then t1 = elt.Column - Range("gamt")(1).Column + 1 End If Next elt O9 = Range("GAMT")(t1): P9 = temp: Q9 = Range("GAMT")(t1 + 1) N10 = Range("GAMP")(p1): N11 = pres: N12 = Range("GAMP")(p1 + 1) zz = Range("tabz") O10 = zz(p1, t1): Q10 = zz(p1, t1 + 1) O12 = zz(p1 + 1, t1): Q12 = zz(p1 + 1, t1 + 1) P10 = (Q10 - O10) / (Q9 - O9) * (P9 - O9) + O10 P12 = (Q12 - O12) / (Q9 - O9) * (P9 - O9) + O12 p11 = (P12 - P10) / (N12 - N10) * (N11 - N10) + P10 TableZ999 = p11 End Function
Attention ! il y a des zones nommées : GAMP est la zone d'entrée de ta seconde valeur (A1:Ax) GAMT est la zone d'entrée de la première valeur (B1:?1) TABZ est la zone contenant les données (A2:?x)
Usage : =tablez999(P;T)
Je peux t'envoyer l'exemple au besoin.
@+ FxM
Bonsoir Jean,
Le problème n'est pas uniquement de faire une interpolation entre les
valeurs mais aussi de trouver les bonnes valeurs à utiliser.
J'ai développé cela pour mon boulot (calcul de Z à partir d'une table)
sous forme d'une fonction perso. Je te laisse adapter.
Insertion | module | copies le code qui suit :
Public Function TableZ999(pres, temp)
Application.Volatile
For Each elt In Range("GAMP")
If elt.Value <= pres And elt.Offset(1, 0).Value > pres Then
p1 = elt.Row - Range("GAMP")(1).Row + 1
End If
Next elt
For Each elt In Range("GAMT")
If elt.Value <= temp And elt.Offset(0, 1).Value > temp Then
t1 = elt.Column - Range("gamt")(1).Column + 1
End If
Next elt
O9 = Range("GAMT")(t1): P9 = temp: Q9 = Range("GAMT")(t1 + 1)
N10 = Range("GAMP")(p1): N11 = pres: N12 = Range("GAMP")(p1 + 1)
zz = Range("tabz")
O10 = zz(p1, t1): Q10 = zz(p1, t1 + 1)
O12 = zz(p1 + 1, t1): Q12 = zz(p1 + 1, t1 + 1)
P10 = (Q10 - O10) / (Q9 - O9) * (P9 - O9) + O10
P12 = (Q12 - O12) / (Q9 - O9) * (P9 - O9) + O12
p11 = (P12 - P10) / (N12 - N10) * (N11 - N10) + P10
TableZ999 = p11
End Function
Attention ! il y a des zones nommées :
GAMP est la zone d'entrée de ta seconde valeur (A1:Ax)
GAMT est la zone d'entrée de la première valeur (B1:?1)
TABZ est la zone contenant les données (A2:?x)
Le problème n'est pas uniquement de faire une interpolation entre les valeurs mais aussi de trouver les bonnes valeurs à utiliser.
J'ai développé cela pour mon boulot (calcul de Z à partir d'une table) sous forme d'une fonction perso. Je te laisse adapter.
Insertion | module | copies le code qui suit : Public Function TableZ999(pres, temp) Application.Volatile For Each elt In Range("GAMP") If elt.Value <= pres And elt.Offset(1, 0).Value > pres Then p1 = elt.Row - Range("GAMP")(1).Row + 1 End If Next elt For Each elt In Range("GAMT") If elt.Value <= temp And elt.Offset(0, 1).Value > temp Then t1 = elt.Column - Range("gamt")(1).Column + 1 End If Next elt O9 = Range("GAMT")(t1): P9 = temp: Q9 = Range("GAMT")(t1 + 1) N10 = Range("GAMP")(p1): N11 = pres: N12 = Range("GAMP")(p1 + 1) zz = Range("tabz") O10 = zz(p1, t1): Q10 = zz(p1, t1 + 1) O12 = zz(p1 + 1, t1): Q12 = zz(p1 + 1, t1 + 1) P10 = (Q10 - O10) / (Q9 - O9) * (P9 - O9) + O10 P12 = (Q12 - O12) / (Q9 - O9) * (P9 - O9) + O12 p11 = (P12 - P10) / (N12 - N10) * (N11 - N10) + P10 TableZ999 = p11 End Function
Attention ! il y a des zones nommées : GAMP est la zone d'entrée de ta seconde valeur (A1:Ax) GAMT est la zone d'entrée de la première valeur (B1:?1) TABZ est la zone contenant les données (A2:?x)
Usage : =tablez999(P;T)
Je peux t'envoyer l'exemple au besoin.
@+ FxM
Jean Laberi
Merci pour ta proposition !
Si on suppose que la table de conversion est sur la feuille "Table "et qu'elle contient 50 colonnes et 50 lignes , on a Table!$A$2:$A$50 représente les entêtes de lignes Table!$B$1:$AX$1 représente les entêtes de colonnes Table!$B$2:$AX$50 représente le coeur de la table (résultats) Si les parametres limites du tableau (50 lignes et 50 colonnes ici) et son nom (ici Table) peuvent être passés comme variables, c'est l'idéal, mais on peut toujours s'y ramener!
A partir de ca, si les cellules d'origine des valeurs d'entrée sont A2 (pour les lignes du tableau) et B2 (pour les colonnes) les calculs à faire sont :
C=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1);EQUIV(Feuil1!B2;Table !$B$1:$AX$1;1)) D=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1);EQUIV(Feuil1!B2;Table !$B$1:$AX$1;1)+1) E=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;EQUIV(Feuil1!B2;Tab le!$B$1:$AX$1;1)) F=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;EQUIV(Feuil1!B2;Tab le!$B$1:$AX$1;1)+1) (ces 4 valeurs précédentes sont les valeurs qui encadrent la valeur cherchée dans la table) G=INDEX(Table!$B$1:$AX$1;1;EQUIV(Feuil1!B2;Table!$B$1:$AX$1;1)) H=INDEX(Table!$B$1:$AX$1;1;EQUIV(Feuil1!B2;Table!$B$1:$AX$1;1)+1) (ces deux valeurs sont les valeurs d'entete de colonnes qui encadrent la valeur passée dans B2) I=INDEX(Table!$A$2:$A$50;EQUIV(A2;Table!$A$2:$A$50;1);1;1) J=INDEX(Table!$A$2:$A$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;1;1) (ces deux valeurs sont les valeurs d'entete de lignes qui encadrent la valeur passée dans A2)
K=(B2-G)/(H-G)*(D-C)+C L=(B2-G)/(H-G)*(F-E)+E (deux premières interpolations sur la valeur passée dans B2)
M =(A2-I)/(J-I)*(L-K)+K M est la valeur finale obtenue par double interpolation à partir de la table Table et des valeurs contenues dans les cellules d'entrée A2 et B2. Les valeurs C,D,E,F,G,H,I,J,K,L sont des variables intermédiaires.
On imaginerait très bien une fonction comme
C2=interpole (Table!$A$1:$AX$50,A2,B2) - en incluant les en-tetes dans le tableau avec cellule $A$1 vide - et ou interpole refait cette série de calculs en evitant la création de toutes les colonnes intermédiaires C,D,E,F...
C'est un calcul simple mais qui implique pas mal les fonctions de recherche ! Est-il possible de créer une fonction de ce type dans Excel ?
si tu me montre la formule je pourrais la transformer en fonction personnalisé.
isabelle
Bonjour
Je souhaite réaliser l"application suivante, dont l'utilisation pourrait être très classique parmi les utilisateurs d'excel ayant à traiter des données de mesures en utilisant une table de conversion a deux entrées.
J'ai une première feuille contenant ma table de conversion a 2 entrées : - première ligne : valeurs de ma première entrée en B1,C1,D1,....X1 - première colonne : valeurs de ma deuxième entrée en A2, A3, A4, ....Aj - dans le tableau B2;Xj les valeurs résultantes correspondants aux en-tetes
Si le paramètre colonne% et le paramlètre ligne = 8, le résultat est 12
On suppose que les paramètres lignes et colonnes sont classés dans l'ordre
croissant.
Je souhaite, à partir de données contenues dans une autre feuille de calcul
pouvoir calculer le résultat, sachant que les valeurs du paramètre ligne et
du paramètre colonne ne sont pas exactement égales aux valeurs des entêtes
dans la table. Par exemple je veux connaitre le résultat pour 27 et 8.2 (et
non pas 25 et 8). Si on fait le calcul manuel, on fait une interpolation lineaire entre 25 et 35 pour les colonnes et entre 8 et 11 pour les lignes
et on trouve un résultat compris entre les 4 valeurs extrèmes (le barycentre
de 12,13,11,11).
C'est réalisable avec des fonctions simples d'excel (recherche, index et 4
opérations....), mais la formule est tellement longue qu'on ne peut pas la
rentrer dans une seule cellule. Pour que ce soit compréhensible il faut un
certain nombre de cellules intermédiaires et ca rend les choses un peu lourdes !. Cette application devant être facilement transportable sur d'autres cellules ou d'autres tables, comment est-il possible de réaliser
une telle fonction : avec une procédure sub ou function qu'on pourrait appeler de n'importe quelle cellule, en transmettant les deux paramètres necessaires (adresses des cellules contenant les valeurs d'entrées) ?
L'application classique est par exemple la suivante : J'ai enregistré dans une feuille la pression de la vapeur en colonne 1, j'ai
sa température en colonne 2, j'ai la table qui donne la masse volumique de
la vapeur en fonction de sa pression et de sa température dans une deuxième
feuille. Je voudrait faire une colonne 3 avec la masse volumique correspondante pour chaque ligne (sans introduire 4 ou 5 colonnes supplémentaires de calculs intermédiaires sur cette feuille ou sur une autre
!).
Si quelqu'un peut me donner ici les pistes de démarrage ! Merci !
Merci pour ta proposition !
Si on suppose que la table de conversion est sur la feuille "Table "et
qu'elle contient 50 colonnes et 50 lignes , on a
Table!$A$2:$A$50 représente les entêtes de lignes
Table!$B$1:$AX$1 représente les entêtes de colonnes
Table!$B$2:$AX$50 représente le coeur de la table (résultats)
Si les parametres limites du tableau (50 lignes et 50 colonnes ici) et son
nom (ici Table) peuvent être passés comme variables, c'est l'idéal, mais on
peut toujours s'y ramener!
A partir de ca, si les cellules d'origine des valeurs d'entrée sont A2 (pour
les lignes du tableau) et B2 (pour les colonnes) les calculs à faire sont :
C=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1);EQUIV(Feuil1!B2;Table
!$B$1:$AX$1;1))
D=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1);EQUIV(Feuil1!B2;Table
!$B$1:$AX$1;1)+1)
E=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;EQUIV(Feuil1!B2;Tab
le!$B$1:$AX$1;1))
F=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;EQUIV(Feuil1!B2;Tab
le!$B$1:$AX$1;1)+1)
(ces 4 valeurs précédentes sont les valeurs qui encadrent la valeur cherchée
dans la table)
G=INDEX(Table!$B$1:$AX$1;1;EQUIV(Feuil1!B2;Table!$B$1:$AX$1;1))
H=INDEX(Table!$B$1:$AX$1;1;EQUIV(Feuil1!B2;Table!$B$1:$AX$1;1)+1)
(ces deux valeurs sont les valeurs d'entete de colonnes qui encadrent la
valeur passée dans B2)
I=INDEX(Table!$A$2:$A$50;EQUIV(A2;Table!$A$2:$A$50;1);1;1)
J=INDEX(Table!$A$2:$A$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;1;1)
(ces deux valeurs sont les valeurs d'entete de lignes qui encadrent la
valeur passée dans A2)
K=(B2-G)/(H-G)*(D-C)+C
L=(B2-G)/(H-G)*(F-E)+E
(deux premières interpolations sur la valeur passée dans B2)
M =(A2-I)/(J-I)*(L-K)+K
M est la valeur finale obtenue par double interpolation à partir de la table
Table et des valeurs contenues dans les cellules d'entrée A2 et B2.
Les valeurs C,D,E,F,G,H,I,J,K,L sont des variables intermédiaires.
On imaginerait très bien une fonction comme
C2=interpole (Table!$A$1:$AX$50,A2,B2)
- en incluant les en-tetes dans le tableau avec cellule $A$1 vide
- et ou interpole refait cette série de calculs en evitant la création de
toutes les colonnes intermédiaires C,D,E,F...
C'est un calcul simple mais qui implique pas mal les fonctions de recherche
! Est-il possible de créer une fonction de ce type dans Excel ?
"isabelle" <as.isabellevIE@videotron.ca> a écrit dans le message de
news:40CC7284.D5145B9F@videotron.ca...
bonjour Jean,
si tu me montre la formule je pourrais la transformer en fonction
personnalisé.
isabelle
Bonjour
Je souhaite réaliser l"application suivante, dont l'utilisation pourrait
être très classique parmi les utilisateurs d'excel ayant à traiter des
données de mesures en utilisant une table de conversion a deux entrées.
J'ai une première feuille contenant ma table de conversion a 2 entrées :
- première ligne : valeurs de ma première entrée en B1,C1,D1,....X1
- première colonne : valeurs de ma deuxième entrée en A2, A3, A4, ....Aj
- dans le tableau B2;Xj les valeurs résultantes correspondants aux
en-tetes
Si le paramètre colonne% et le paramlètre ligne = 8, le résultat est
12
On suppose que les paramètres lignes et colonnes sont classés dans
l'ordre
croissant.
Je souhaite, à partir de données contenues dans une autre feuille de
calcul
pouvoir calculer le résultat, sachant que les valeurs du paramètre ligne
et
du paramètre colonne ne sont pas exactement égales aux valeurs des
entêtes
dans la table. Par exemple je veux connaitre le résultat pour 27 et 8.2
(et
non pas 25 et 8). Si on fait le calcul manuel, on fait une interpolation
lineaire entre 25 et 35 pour les colonnes et entre 8 et 11 pour les
lignes
et on trouve un résultat compris entre les 4 valeurs extrèmes (le
barycentre
de 12,13,11,11).
C'est réalisable avec des fonctions simples d'excel (recherche, index et
4
opérations....), mais la formule est tellement longue qu'on ne peut pas
la
rentrer dans une seule cellule. Pour que ce soit compréhensible il faut
un
certain nombre de cellules intermédiaires et ca rend les choses un peu
lourdes !. Cette application devant être facilement transportable sur
d'autres cellules ou d'autres tables, comment est-il possible de
réaliser
une telle fonction : avec une procédure sub ou function qu'on pourrait
appeler de n'importe quelle cellule, en transmettant les deux paramètres
necessaires (adresses des cellules contenant les valeurs d'entrées) ?
L'application classique est par exemple la suivante :
J'ai enregistré dans une feuille la pression de la vapeur en colonne 1,
j'ai
sa température en colonne 2, j'ai la table qui donne la masse volumique
de
la vapeur en fonction de sa pression et de sa température dans une
deuxième
feuille. Je voudrait faire une colonne 3 avec la masse volumique
correspondante pour chaque ligne (sans introduire 4 ou 5 colonnes
supplémentaires de calculs intermédiaires sur cette feuille ou sur une
autre
!).
Si quelqu'un peut me donner ici les pistes de démarrage ! Merci !
Si on suppose que la table de conversion est sur la feuille "Table "et qu'elle contient 50 colonnes et 50 lignes , on a Table!$A$2:$A$50 représente les entêtes de lignes Table!$B$1:$AX$1 représente les entêtes de colonnes Table!$B$2:$AX$50 représente le coeur de la table (résultats) Si les parametres limites du tableau (50 lignes et 50 colonnes ici) et son nom (ici Table) peuvent être passés comme variables, c'est l'idéal, mais on peut toujours s'y ramener!
A partir de ca, si les cellules d'origine des valeurs d'entrée sont A2 (pour les lignes du tableau) et B2 (pour les colonnes) les calculs à faire sont :
C=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1);EQUIV(Feuil1!B2;Table !$B$1:$AX$1;1)) D=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1);EQUIV(Feuil1!B2;Table !$B$1:$AX$1;1)+1) E=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;EQUIV(Feuil1!B2;Tab le!$B$1:$AX$1;1)) F=INDEX(Table!$B$2:$AX$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;EQUIV(Feuil1!B2;Tab le!$B$1:$AX$1;1)+1) (ces 4 valeurs précédentes sont les valeurs qui encadrent la valeur cherchée dans la table) G=INDEX(Table!$B$1:$AX$1;1;EQUIV(Feuil1!B2;Table!$B$1:$AX$1;1)) H=INDEX(Table!$B$1:$AX$1;1;EQUIV(Feuil1!B2;Table!$B$1:$AX$1;1)+1) (ces deux valeurs sont les valeurs d'entete de colonnes qui encadrent la valeur passée dans B2) I=INDEX(Table!$A$2:$A$50;EQUIV(A2;Table!$A$2:$A$50;1);1;1) J=INDEX(Table!$A$2:$A$50;EQUIV(A2;Table!$A$2:$A$50;1)+1;1;1) (ces deux valeurs sont les valeurs d'entete de lignes qui encadrent la valeur passée dans A2)
K=(B2-G)/(H-G)*(D-C)+C L=(B2-G)/(H-G)*(F-E)+E (deux premières interpolations sur la valeur passée dans B2)
M =(A2-I)/(J-I)*(L-K)+K M est la valeur finale obtenue par double interpolation à partir de la table Table et des valeurs contenues dans les cellules d'entrée A2 et B2. Les valeurs C,D,E,F,G,H,I,J,K,L sont des variables intermédiaires.
On imaginerait très bien une fonction comme
C2=interpole (Table!$A$1:$AX$50,A2,B2) - en incluant les en-tetes dans le tableau avec cellule $A$1 vide - et ou interpole refait cette série de calculs en evitant la création de toutes les colonnes intermédiaires C,D,E,F...
C'est un calcul simple mais qui implique pas mal les fonctions de recherche ! Est-il possible de créer une fonction de ce type dans Excel ?
si tu me montre la formule je pourrais la transformer en fonction personnalisé.
isabelle
Bonjour
Je souhaite réaliser l"application suivante, dont l'utilisation pourrait être très classique parmi les utilisateurs d'excel ayant à traiter des données de mesures en utilisant une table de conversion a deux entrées.
J'ai une première feuille contenant ma table de conversion a 2 entrées : - première ligne : valeurs de ma première entrée en B1,C1,D1,....X1 - première colonne : valeurs de ma deuxième entrée en A2, A3, A4, ....Aj - dans le tableau B2;Xj les valeurs résultantes correspondants aux en-tetes
Si le paramètre colonne% et le paramlètre ligne = 8, le résultat est 12
On suppose que les paramètres lignes et colonnes sont classés dans l'ordre
croissant.
Je souhaite, à partir de données contenues dans une autre feuille de calcul
pouvoir calculer le résultat, sachant que les valeurs du paramètre ligne et
du paramètre colonne ne sont pas exactement égales aux valeurs des entêtes
dans la table. Par exemple je veux connaitre le résultat pour 27 et 8.2 (et
non pas 25 et 8). Si on fait le calcul manuel, on fait une interpolation lineaire entre 25 et 35 pour les colonnes et entre 8 et 11 pour les lignes
et on trouve un résultat compris entre les 4 valeurs extrèmes (le barycentre
de 12,13,11,11).
C'est réalisable avec des fonctions simples d'excel (recherche, index et 4
opérations....), mais la formule est tellement longue qu'on ne peut pas la
rentrer dans une seule cellule. Pour que ce soit compréhensible il faut un
certain nombre de cellules intermédiaires et ca rend les choses un peu lourdes !. Cette application devant être facilement transportable sur d'autres cellules ou d'autres tables, comment est-il possible de réaliser
une telle fonction : avec une procédure sub ou function qu'on pourrait appeler de n'importe quelle cellule, en transmettant les deux paramètres necessaires (adresses des cellules contenant les valeurs d'entrées) ?
L'application classique est par exemple la suivante : J'ai enregistré dans une feuille la pression de la vapeur en colonne 1, j'ai
sa température en colonne 2, j'ai la table qui donne la masse volumique de
la vapeur en fonction de sa pression et de sa température dans une deuxième
feuille. Je voudrait faire une colonne 3 avec la masse volumique correspondante pour chaque ligne (sans introduire 4 ou 5 colonnes supplémentaires de calculs intermédiaires sur cette feuille ou sur une autre
!).
Si quelqu'un peut me donner ici les pistes de démarrage ! Merci !
Jean Laberi
Merci, j'ai testé, ça marche bien ! Est-il impossible de passer en paramètres, le nom du tableau, son nombre de colonnes et son nombre de lignes ? Je vais essayer !
Jean-Francois Bérail jean-francoispointberailatwanadoo.fr (on ne se méfie jamais assez du spam !)
"FxM" a écrit dans le message de news:%
Bonsoir Jean,
Le problème n'est pas uniquement de faire une interpolation entre les valeurs mais aussi de trouver les bonnes valeurs à utiliser.
J'ai développé cela pour mon boulot (calcul de Z à partir d'une table) sous forme d'une fonction perso. Je te laisse adapter.
Insertion | module | copies le code qui suit : Public Function TableZ999(pres, temp) Application.Volatile For Each elt In Range("GAMP") If elt.Value <= pres And elt.Offset(1, 0).Value > pres Then p1 = elt.Row - Range("GAMP")(1).Row + 1 End If Next elt For Each elt In Range("GAMT") If elt.Value <= temp And elt.Offset(0, 1).Value > temp Then t1 = elt.Column - Range("gamt")(1).Column + 1 End If Next elt O9 = Range("GAMT")(t1): P9 = temp: Q9 = Range("GAMT")(t1 + 1) N10 = Range("GAMP")(p1): N11 = pres: N12 = Range("GAMP")(p1 + 1) zz = Range("tabz") O10 = zz(p1, t1): Q10 = zz(p1, t1 + 1) O12 = zz(p1 + 1, t1): Q12 = zz(p1 + 1, t1 + 1) P10 = (Q10 - O10) / (Q9 - O9) * (P9 - O9) + O10 P12 = (Q12 - O12) / (Q9 - O9) * (P9 - O9) + O12 p11 = (P12 - P10) / (N12 - N10) * (N11 - N10) + P10 TableZ999 = p11 End Function
Attention ! il y a des zones nommées : GAMP est la zone d'entrée de ta seconde valeur (A1:Ax) GAMT est la zone d'entrée de la première valeur (B1:?1) TABZ est la zone contenant les données (A2:?x)
Usage : =tablez999(P;T)
Je peux t'envoyer l'exemple au besoin.
@+ FxM
Merci, j'ai testé, ça marche bien ! Est-il impossible de passer en
paramètres, le nom du tableau, son nombre de colonnes et son nombre de
lignes ?
Je vais essayer !
Jean-Francois Bérail
jean-francoispointberailatwanadoo.fr
(on ne se méfie jamais assez du spam !)
"FxM" <fxmanceaux@chello.fr> a écrit dans le message de
news:%23REoGjWUEHA.1020@TK2MSFTNGP11.phx.gbl...
Bonsoir Jean,
Le problème n'est pas uniquement de faire une interpolation entre les
valeurs mais aussi de trouver les bonnes valeurs à utiliser.
J'ai développé cela pour mon boulot (calcul de Z à partir d'une table)
sous forme d'une fonction perso. Je te laisse adapter.
Insertion | module | copies le code qui suit :
Public Function TableZ999(pres, temp)
Application.Volatile
For Each elt In Range("GAMP")
If elt.Value <= pres And elt.Offset(1, 0).Value > pres Then
p1 = elt.Row - Range("GAMP")(1).Row + 1
End If
Next elt
For Each elt In Range("GAMT")
If elt.Value <= temp And elt.Offset(0, 1).Value > temp Then
t1 = elt.Column - Range("gamt")(1).Column + 1
End If
Next elt
O9 = Range("GAMT")(t1): P9 = temp: Q9 = Range("GAMT")(t1 + 1)
N10 = Range("GAMP")(p1): N11 = pres: N12 = Range("GAMP")(p1 + 1)
zz = Range("tabz")
O10 = zz(p1, t1): Q10 = zz(p1, t1 + 1)
O12 = zz(p1 + 1, t1): Q12 = zz(p1 + 1, t1 + 1)
P10 = (Q10 - O10) / (Q9 - O9) * (P9 - O9) + O10
P12 = (Q12 - O12) / (Q9 - O9) * (P9 - O9) + O12
p11 = (P12 - P10) / (N12 - N10) * (N11 - N10) + P10
TableZ999 = p11
End Function
Attention ! il y a des zones nommées :
GAMP est la zone d'entrée de ta seconde valeur (A1:Ax)
GAMT est la zone d'entrée de la première valeur (B1:?1)
TABZ est la zone contenant les données (A2:?x)
Merci, j'ai testé, ça marche bien ! Est-il impossible de passer en paramètres, le nom du tableau, son nombre de colonnes et son nombre de lignes ? Je vais essayer !
Jean-Francois Bérail jean-francoispointberailatwanadoo.fr (on ne se méfie jamais assez du spam !)
"FxM" a écrit dans le message de news:%
Bonsoir Jean,
Le problème n'est pas uniquement de faire une interpolation entre les valeurs mais aussi de trouver les bonnes valeurs à utiliser.
J'ai développé cela pour mon boulot (calcul de Z à partir d'une table) sous forme d'une fonction perso. Je te laisse adapter.
Insertion | module | copies le code qui suit : Public Function TableZ999(pres, temp) Application.Volatile For Each elt In Range("GAMP") If elt.Value <= pres And elt.Offset(1, 0).Value > pres Then p1 = elt.Row - Range("GAMP")(1).Row + 1 End If Next elt For Each elt In Range("GAMT") If elt.Value <= temp And elt.Offset(0, 1).Value > temp Then t1 = elt.Column - Range("gamt")(1).Column + 1 End If Next elt O9 = Range("GAMT")(t1): P9 = temp: Q9 = Range("GAMT")(t1 + 1) N10 = Range("GAMP")(p1): N11 = pres: N12 = Range("GAMP")(p1 + 1) zz = Range("tabz") O10 = zz(p1, t1): Q10 = zz(p1, t1 + 1) O12 = zz(p1 + 1, t1): Q12 = zz(p1 + 1, t1 + 1) P10 = (Q10 - O10) / (Q9 - O9) * (P9 - O9) + O10 P12 = (Q12 - O12) / (Q9 - O9) * (P9 - O9) + O12 p11 = (P12 - P10) / (N12 - N10) * (N11 - N10) + P10 TableZ999 = p11 End Function
Attention ! il y a des zones nommées : GAMP est la zone d'entrée de ta seconde valeur (A1:Ax) GAMT est la zone d'entrée de la première valeur (B1:?1) TABZ est la zone contenant les données (A2:?x)
Usage : =tablez999(P;T)
Je peux t'envoyer l'exemple au besoin.
@+ FxM
Jean Laberi
En cherchant, j'ai trouvé TriLookup ici http://www.trimill.com/ Ca remplit parfaitement la fonction que je cherche et même au-dela. Seul inconvénient, c'est un shareware ! Et faire une commande pour ca, c'est lourd ! Donc je n'utiliserai pas ! Mais je conseille quand même !
En cherchant, j'ai trouvé TriLookup ici http://www.trimill.com/
Ca remplit parfaitement la fonction que je cherche et même au-dela. Seul
inconvénient, c'est un shareware ! Et faire une commande pour ca, c'est
lourd ! Donc je n'utiliserai pas !
Mais je conseille quand même !
En cherchant, j'ai trouvé TriLookup ici http://www.trimill.com/ Ca remplit parfaitement la fonction que je cherche et même au-dela. Seul inconvénient, c'est un shareware ! Et faire une commande pour ca, c'est lourd ! Donc je n'utiliserai pas ! Mais je conseille quand même !
FxM
Bonsoir,
Merci, j'ai testé, ça marche bien ! Est-il impossible de passer en paramètres, le nom du tableau, son nombre de colonnes et son nombre de lignes ?
Tout est passable en paramètre. Du genre : Public Function TableZ999(pres, temp, entetesP, entetesT, donnees) remplacer GAMP par entetesP , GAMT par entetesT et TABZ par donnees.
J'avais mis ces zones en fixes car la recherche et l'amenée des données suivant les gaz se fait par ailleurs et que suffisamment de lignes et colonnes permettent de couvrir tous mes cas.
@+ FxM
Bonsoir,
Merci, j'ai testé, ça marche bien ! Est-il impossible de passer en
paramètres, le nom du tableau, son nombre de colonnes et son nombre de
lignes ?
Tout est passable en paramètre. Du genre :
Public Function TableZ999(pres, temp, entetesP, entetesT, donnees)
remplacer GAMP par entetesP , GAMT par entetesT et TABZ par donnees.
J'avais mis ces zones en fixes car la recherche et l'amenée des données
suivant les gaz se fait par ailleurs et que suffisamment de lignes et
colonnes permettent de couvrir tous mes cas.
Merci, j'ai testé, ça marche bien ! Est-il impossible de passer en paramètres, le nom du tableau, son nombre de colonnes et son nombre de lignes ?
Tout est passable en paramètre. Du genre : Public Function TableZ999(pres, temp, entetesP, entetesT, donnees) remplacer GAMP par entetesP , GAMT par entetesT et TABZ par donnees.
J'avais mis ces zones en fixes car la recherche et l'amenée des données suivant les gaz se fait par ailleurs et que suffisamment de lignes et colonnes permettent de couvrir tous mes cas.
