récupérer le numéro de la ligne où est insérée la fonction

Y compris Power BI, Power Query et toute autre question en lien avec Excel
s
stef2015
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 11 novembre 2017
Version d'Excel : 2007

Message par stef2015 » 11 novembre 2017, 21:04

Bonjour

Je cherche à faire un calcul dans une cellule d'une ligne qui dépend de 4 données de cette même ligne
J'ai donc fait la fonction ci dessous qui fonctionne si je rentre le numéro de la ligne dans la cellule, par exemple =calculRet(10) si je suis sur la dixième ligne. Mais je voudrais plutôt saisir une fonction générique =calculRet() pour les lignes qui sont dessous donc sans mettre =calculRet(11), =calculRet(12) ...
J'aurai donc besoin à priori de récupérer le numéro de la ligne en fonction de l'endroit ou je saisis la formule. Donc dans le cas ci dessus, si je met ma formule =calculRet() sur la ligne 10 je voudrais récupérer la valeur 10 de cette ligne pour la mettre à la place de i. Comment faire ?
Merci

Function calculRet(i As Integer) As Single
calculRetP22 = Cells(i, 16) * Cells(i, 15) * Cells(i, 10) * 2 / Cells(i, 7)
End Function
Avatar du membre
thev
Membre impliqué
Membre impliqué
Messages : 2'474
Appréciations reçues : 208
Inscrit le : 13 juin 2016
Version d'Excel : 2019 FR 64 bits

Message par thev » 11 novembre 2017, 21:17

Bonsoir,

ci-dessous code
Function calculRet() As Single
    Dim i As Long
    
    Set cell = Application.Caller: i = cell.Row
    calculRet = Cells(i, 16) * Cells(i, 15) * Cells(i, 10) * 2 / Cells(i, 7)
End Function
Modifié en dernier par thev le 11 novembre 2017, 21:18, modifié 1 fois.
Avatar du membre
dhany
Passionné d'Excel
Passionné d'Excel
Messages : 7'081
Appréciations reçues : 698
Inscrit le : 3 octobre 2017
Version d'Excel : 2007 FR

Message par dhany » 11 novembre 2017, 21:17

Bonjour stef2015,

Je te propose d'utiliser cette fonction :
Function calculRet(i As Long) As Single
  If Cells(i, 7) <> 0 Then calculRet = Cells(i, 16) * Cells(i, 15) * Cells(i, 10) * 2 / Cells(i, 7)
End Function
Sur ta feuille Excel, en ligne 10, tu l'appelles ainsi : =calculRet(LIGNE())
et recopie ensuite cette formule vers le bas.

Merci de me donner ton avis.

Cordialement
 
s
stef2015
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 11 novembre 2017
Version d'Excel : 2007

Message par stef2015 » 11 novembre 2017, 21:50

Merci thev et dhany, les 2 fonctionnent

Par contre, la valeur ne s'actualise dans la cellule excel que si on se met dedans et qu'on valide la formule.

Comme c'est un fichier pour des utilisateurs métiers, est-ce qu'il y aurait un moyen pour que la cellule s'actualise dès que les données sont présentes dans les 4 autres cellules ?

Merci encore
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'251
Appréciations reçues : 363
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 12 novembre 2017, 00:12

Bonjour,

c'est parce que tu as mal construit ta fonction personnalisée.
Il faut lui passer les 4 paramètres et elle sera ré-évaluée à chaque changement de l'un d'eux.
Function calculRet(param1 As Double, param2 As Double, param3 As Double, param4 As Double) As Double
  If param1 <> 0 Then calculRet = param2 * param3 * param4 * 2 / param1
End Function
et plus besoin de connaitre le ligne où elle est.

Mais, honnêtement, pour ça il vaut mieux rester sur une formule qui sera largement plus performante.
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
s
stef2015
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 11 novembre 2017
Version d'Excel : 2007

Message par stef2015 » 12 novembre 2017, 00:32

bonjour eriiic et merci

Aujourd'hui j'ai uniquement une formule Exceldans la case, et j'avais commencé comme vous le proposez. Mais les 2 solutions m'obligent sur chaque ligne à indiquer les 4 cellules de valeurs, je trouve ça lourd. C'est pour cela que je cherchais à juste mettre la fonction dans la cellule.
Et globalement j'ai d'autres formules à mettre dans d'autres cellules qui font elles aussi références à d'autres cellules. Donc ca veut dire que à chaque fois il faut que je me souvienne de la formule de calcul pour trouver les bonnes valeurs à mettre (c'est jamais les mêmes colonnes), alors que là je mets uniquement le nom de la formule sans me préoccuper des valeurs.
Je vais réfléchir à votre proposition. Encore merci
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'251
Appréciations reçues : 363
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 12 novembre 2017, 09:45

Bonjour,
les 2 solutions m'obligent sur chaque ligne à indiquer les 4 cellules de valeurs, je trouve ça lourd.
C'est comme ça qu'il faut faire si tu veux que ta fonction se mette à jour toute seule uniquement quand c'est nécessaire, c'est tout.
Si tu préfères forcer le recalcul de ta feuille au risque d'oublier tu peux, ou la rendre volatile.
Pour moi ça serait une mauvaise pratique ici, mais c'est toi qui voit.
Donc ca veut dire que à chaque fois il faut que je me souvienne de la formule de calcul pour trouver les bonnes valeurs à mettre (c'est jamais les mêmes colonnes)
Heuu, ta fonction utilise des colonnes fixes. Tu comptes la modifier à chaque utilisation ? Pour le faire il va falloir que tu réfléchisses...
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
s
stef2015
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 11 novembre 2017
Version d'Excel : 2007

Message par stef2015 » 12 novembre 2017, 10:54

Bonjour eriiic

Oui tu as raison c'est risqué ;)

Pour ta 2eme remarque les colonnes sont fixes mais il y a différentes formules qui ne se basent pas sur les mêmes colonnes.
En fait je fais des calculs ligne après ligne (une personne par ligne) avec une quinzaine de colonnes et en fonction de la date de naissance de la personne et de sa carrière professionnelle, je peux utiliser une dizaine de formules différentes (une ou plusieurs formules d'ailleurs). Donc quand j'arrive dans les colonnes où je dois faire ces calculs il faut soit que je ressaississe la formule ou que j'utilise ta réponse avec les valeurs de la fonction mais ca m'oblige à prendre les règles de calcul papier pour savoir quelle règle appliquer en fonction des conditions de la personne (pour savoir quelles colonnes utilisées). Là l'idée serait de faire plus simple et de me dire par exemple que si l'assuré a une carrière professionnelle entre 1950 et 1960 alors j'utilise la formule dédiée qui aurait uniquement un nom parlant type =retourne50-60. Mais faudrait que je valide la cellule :)

A vivre comme on dit ;)

Merci encore
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'251
Appréciations reçues : 363
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 12 novembre 2017, 11:20

Alors tant qu'à faire, autant complexifier ta fonction pour que ça vaille le coup.
Tu lui passes toutes les colonnes nécessaires pour qu'elle puisse déterminer la règle à appliquer et les éléments pour le faire, et tu lui fais calculer.
Le passage des paramètres peut être fait par une plage : =calculRet(A2:F2) plutôt que 1 par 1.

Exemple de structure possible en supposant la date en A :
Function calculRet(param As Range) As Double
    Select Case Year(param(1, 1))    ' année date
    Case 1960 To 1965
        ' formule1
    Case 1966 To 1973
        ' formule2
    Case Else
        'anomalie
    End Select
End Function
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
s
stef2015
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 11 novembre 2017
Version d'Excel : 2007

Message par stef2015 » 12 novembre 2017, 12:18

Effectivement il vaut peut etre mieux que je fasse comme ca

Merci
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message