FormuleR1C1 très complexe (trop?) avec une suite

Version imprimable

27/09/2016, 03h03
pyriame

FormuleR1C1 très complexe (trop?) avec une suite

Bonjour à tous.
C'est une truc que j'aime bien faire d'ordinaire; me tritouiller le cerveau pour arriver a la formule souhaiter, mais la je sèche un peu.

J'ai une periode de 20 qui me donne la hauteur du nombre de cellule qui vont être utiliser (c'est le truc modifiable de la macro).

j'essaye de faire une formule de type FormuleR1C1 pour obtenir dans ma cellule ceci:

=(ABS(BL21-AT21)+ABS(BL21-AT20)+ABS(BL21-AT19)+ABS(BL21-AT18)+ABS(BL21-AT17)+ABS(BL21-AT16)+ABS(BL21-AT15)+ABS(BL21-AT14)+ABS(BL21-AT13)+ABS(BL21-AT12)+ABS(BL21-AT11)+ABS(BL21-AT10)+ABS(BL21-AT9)+ABS(BL21-AT8)+ABS(BL21-AT7)+ABS(BL21-AT6)+ABS(BL21-AT5)+ABS(BL21-AT4)+ABS(BL21-AT3)+ABS(BL21-AT2))/20

c'est a dire la valeur absolue d'une cellule de reference R[X]C[Y] (ici BL21) toujours la même moins la valeur d'une cellule situé sur la colonne C[Y-1] sur la ligne R[0], puis la même chose avec R[-1] puis R[-2] jusqu'a R[-(Periode -1)] . Periode qui peut être 12, 15, 18 etc... c'est a dire de 12, 15, 18 ligne AU DESSUS.

Voila c'est un peu laborieux a expliquer mais copier/coller dans une cellule c'est beaucoup plus claire et en apparence assez simple a faire mais je bloc. Je pensais utiliser une boucle "for int i" comme en c# mais je crois pas que formuleR1C1 accepte un mini boucle entre les parenthèses :).

Ou une boucle qui calcule juste le Abs( A-B) pour chaque cycle qu'on range dans un dictionnaire (ça existe en VBA?) pour sommation à la fin mais du coup dans la cellule l'affichage des cellule utiliser disparaît et on a juste le résultat ce qui m'arrange pas des masses .

Bref j'aurais besoin d'un peu d'aide :) Alors bon comme pour moi c'est un peu complexe si vous pouviez ne pas répondre en 1 heure que j'ai pas trop l'impression d’être une grosse buze ça serait sympas (on a son petit orgueil...) :)

Encore merci

Bonjour,

Une solution possible avec cette fonction :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Function SommeValeursAbs(ByVal CelluleReference As Range, ByVal CelluleValeurs As Range, ByVal ValeurDuPas As Integer) As Double
 
Dim ColonneReference As Long
Dim ColonneValeurs As Long
Dim I As Integer
 
    Application.Volatile
 
    ColonneReference = CelluleReference.Column
    ColonneValeurs = CelluleValeurs.Column
    SommeValeursAbs = 0#
 
    For I = 1 To ValeurDuPas
        SommeValeursAbs = SommeValeursAbs + Abs(CelluleReference - CelluleReference.Offset(1 - I, ColonneValeurs - ColonneReference))
    Next I
 
    SommeValeursAbs = SommeValeursAbs / ValeurDuPas
 
End Function

Un exemple avec plusieurs valeurs du pas. Les références absolues ne sont pas obligatoires.

Pièce jointe 221260

Cordialement.

27/09/2016, 18h50
pyriame

Ouf ! Comme ça je dirais ... faut que je regarde j'ai pas l'habitude d'avoir une truc aussi pointu dans mes macro .

j'ai tester et ta boucle est tout a fait correct :) Ta fonction est parfaite merci beaucoup.

Maintenant, et c'est en cela que je trouvais cette formule complexe, la valeur est directement calculé dans la macro et quant on clic sur la cellule résultat on a que la valeur du calcul or ce que je souhaiterai afficher dans la cellule c'est vraiment le développement du calcul c'est a dire que en cliquant sur la cellule il s'affiche quelque chose du genre :
=(ABS(BL21-AT21)+ABS(BL21-AT20)+ABS(BL21-AT19)+ABS(BL21-AT18)+ABS(BL21-AT17)+ABS(BL21-AT16)+ABS(BL21-AT15)+ABS(BL21-AT14)+ABS(BL21-AT13)+ABS(BL21-AT12)+ABS(BL21-AT11)+ABS(BL21-AT10)+ABS(BL21-AT9)+ABS(BL21-AT8)+ABS(BL21-AT7)+ABS(BL21-AT6)+ABS(BL21-AT5)+ABS(BL21-AT4)+ABS(BL21-AT3)+ABS(BL21-AT2))/20

et pas simplement le résultat.

Une sorte d'agrégateur de string qui mis bout à bout donne le machin plus haut "=(ABS(BL21-AT21)+ABS(BL21-AT20)+ABS(BL21-AT19)+ABS(BL21-AT1....etc" , envoyer sous cette forme dans la cellule qui se charge du calcul mais qui laisse visible toute les cellule utiliser.

Je crois qu'on peut envoyer des string brut dans une cellule et pour peut qu'elle soit convenablement écrit avec un "=" devant la cellule fait tout de même le calcul (sa fait bidouillage on est d'accord...)

Bonjour,

met la formule en B2 :

Code:

1
2
3
4
5
6
7
Sub formule()
    Dim f As String, i As Long
    For i = 21 To 2 Step -1
        f = f & "+ABS(BL21-AT" & i & ")"
    Next i
    [B2].FormulaLocal = "=(" & Mid(f, 2) & ")/20"
End Sub

eric

Bonsoir Eric.
Ecoute, merci c'est tout à fait ça. Par contre je comprend pas ta formule. Elle marche mais je la comprend pas ... Je voit la boucle, les types mais l'ensemble...

Que signifie le [B2] ? Range("B2") ??? j'ai pas défini cette variable elle viens d'ou ? Et Mid(f,2) c'est quoi ? C'est une fonction ?

Je voudrais pas abuser mais j'aurais une dernière question, voici ma macro (une partie):
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Dim DerniereLigne As Long 'va chercher la periode correspondant a moyenne mobile Dim MM_CCI As Long Dim F10 As Worksheet Set F10 = Worksheets("Download") MM_CCI = F10.Range("P29").Value 'Aller à la dernière remplie [A65536].End(xlUp).Select 'Aller a la cellule 65 , c'est a dire colonne BM " Mean Dev" Cells(ActiveCell.Row, 65).Select 'si periode superieur au nombre de ligne rempli pour l'instand If DerniereLigne < (MM_CCI + 1) Then ActiveCell.FormulaR1C1 = "" ' il y a au moins (periode) ligne + 1 (la 1ere ligne avec les en tête) Else Dim f As String, i As Long For i = (MM_CCI + 1) To 2 Step -1 f = f & "+ABS(BL21-AT" & i & ")" Next i [B2].FormulaLocal = "=(" & Mid(f, 2) & ")/20" ActiveCell.Value = [B2].FormulaLocal End If
Comme on peut s'en douter avec ce code, cette macro sera utiliser sur chaque nouvelle ligne. Je Dl des données sur la page "download" puis j'en extrait une partie que j'envoi sur cette page ou environ 200 formules (colonnes) sont mises en macro. Pour pouvoir jongler entre les différentes formules (certaine sont interdépendantes) j'utilise la méthode R1C1 pour localiser les variables nécessaire sur cette page.

Chaque formule va toujours récupérer des variables sur une même colonne mais a chaque nouvelle enregistrement la formule doit glisser avec la dernière ligne
ex: enregistrement 1 dans cellule C3 => Sum(A1:A3) + B3
enregistrement 2 dans cellule C4 => Sum(A2:A4) + B4
3) dans cellule C5=> Sum(A3:A5) + B5

je fait donc toujours des formule du type: dans active.cell RC => Sum(R[-3]C[-1]:RC[-1] + RC[1]

Est ce qu'il est possible d'écrire la formule que tu m'as montré sous cette forme R1C1 directement ou bien il faut faire une conversion d'adressage RC vers une écriture type A3 ou B28 par exemple pour qu'elle puissent y être intégrer ? La formule que tu m'a fourni est parfaite mais je doit la faire glisser avec la dernière ligne remplie a chaque nouvelle enregistrement.

pour la ligne

Code:

For i = (MM_CCI + 1) To 2 Step -1

je ne vais pas pouvoir la conserver en l'état puisque ici MM_CCI défini juste le nombre de ligne a prendre en compte (ça peut varier et je peut devoir la passer a 23 ou 40 en fonction des besoin) et elle ne représente pas réelement le 2nd terme de l'adresse AT" & i & ".

pour la ligne

Code:

f = f & "+ABS(BL21-AT" & i & ")"

je voyais quelque chose du type f = f & "+ABS("" & Terme1 & "" & - & "" & Terme2 & "" & i & ")"
avec Terme1 = RC[-1] (avec le bon type d'adressage)
mais pour Terme2 qui contiendra successivement R[-" & Periode -1 & "]C puis R[-" & Periode -2 & "]C puis .... jusqu'a R[-" & Periode -Periode & "]C je sèche un peu :)

Je crois que ce que je veut dire c'est si il est possible de rendre ta formule "dynamique" avec un adressage relatif R1C1 dont le nombe de terme "Abs(X-Y)" est une constante défini par l'utilisateur (ici MM_CCI ).

Voila , en tous cas merci pour l'aide apporter je continue mes recherches, j'ai un snickers et du coca je suis paré pour la nuit :)
Bonne nuit a tous

28/09/2016, 09h16
eriiic

Bonjour,

réponse rapide que tu puisses avancer et je dois m'absenter.

Citation:

Que signifie le [B2] ? Range("B2") ???

oui

Citation:

Et Mid(f,2) c'est quoi ? C'est une fonction ?

Faire F1 dessus

Pour le reste je n'ai pas tout lu.
Je n'ai pas voulu t'en parler mais tu sembles faire une confusion entre notation A1 vs R1C1 et adressage relatif/absolu qui existe pour les 2 types de notation. Fais une petite recherche sur ces 2 notions.

Dans la formule BL21 est en fait absolu, il faut donc l'écrire $BL$21. Je ne l'avais pas modifié car tu le demandais ainsi.
Et tu peux remplacer une référence par son nom. Tu testes avec une formule sur la feuille, et ta chaine fabriquée doit être à l'identique.
Utilise les espions de VBE et le pas à pas pour déboguer.
eric

Bonsoir merci pour les précision :)

je poursuit la recherche. C'est vrai que les notations R1C1 , relative, absolu c'estun peu vague chez moi :)

Voila ce que ça donne pour l'instant
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 'Aller à la dernière remplie [A65536].End(xlUp).Select 'Aller a la cellule 65 , c'est a dire colonne BM " Mean Dev CCI" Cells(ActiveCell.Row, 65).Select Dim x As Variant x = Application.ConvertFormula _ (Formula:=ActiveCell.Offset(0, -1).Address, _ fromReferenceStyle:=xlA1, _ toReferenceStyle:=xlA1, _ toAbsolute:=xlRelative) If DerniereLigne < (MM_CCI + 1) Then ActiveCell.FormulaR1C1 = "" Else Dim f As String, i As Long For i = (MM_CCI + 1) To 2 Step -1 f = f & "+ABS(" & x & "-AT" & i & ")" Next i [B2].FormulaLocal = "=(" & Mid(f, 2) & ")/" & MM_CCI & "" ActiveCell.Value = [B2].FormulaLocal End If
Donc a l'aide d'une valeur dans une autre cellule je peut faire varier ma periode (MM_CCI) et le terme de "reference" si on peut dire (la cellule RC[-1] prise comme constante pour le calcul de chaque soustraction) glisse à présent à chaque nouvelle ligne ajouter.

Il me reste a convertir le i sous une forme ou une autre pour que ce paquet de soustraction situé les unes sous les autres glisse lui aussi d'une case vers le bas a chaque nouvelle ligne entré . Bon ça avance , je vous tiend au courant

bonne nuit

VICTOIRE !

formule glissante a chaque nouvelle ligne inséré. C'est pas super propre mais ça fonctionne au poil :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
'Aller à la dernière remplie
[A65536].End(xlUp).Select
'Aller a la cellule 65 , c'est a dire colonne BM " Mean Dev CCI"
Cells(ActiveCell.Row, 65).Select
 
Dim x As Variant
Dim y As Variant
x = Application.ConvertFormula _
                       (Formula:=ActiveCell.Offset(0, -1).Address, _
                       fromReferenceStyle:=xlA1, _
                       toReferenceStyle:=xlA1, _
                       toAbsolute:=xlRelative)
 
y = Left(ActiveCell.Offset(0, -19).Address(ColumnAbsolute:=False), (ActiveCell.Column < 27) + 2)
Z = Right(ActiveCell.Address(ColumnAbsolute:=False), (ActiveCell.Column < 27) + 2)
 
If DerniereLigne < (MM_CCI + 1) Then
 
    ActiveCell.FormulaR1C1 = ""
 
Else
 
    Dim f As String, i As Long
 
      For i = Z To (Z - MM_CCI + 1) Step -1
 
        f = f & "+ABS(" & x & "-" & y & "" & i & ")"
 
      Next i
 
     [B2].FormulaLocal = "=(" & Mid(f, 2) & ")/" & MM_CCI & ""
 
ActiveCell.Value = [B2].FormulaLocal
 
End If

Un grand merci pour l'amorce qui m'a bien aidé et pour les conseils judicieux.

bonne nuit à tous

29/09/2016, 07h37
eriiic
Bonjour,

Tu peux simplifier x, y et z ainsi, et essaie de typer tes variables au plus près de ce qu'elles sont (ne mettre variant que si tu n'as pas le choix) :
Code:

1 2 3 4 5 6 Dim x As String Dim y As String Dim z As Long x = ActiveCell.Offset(, -1).Address(False, False) y = Split(ActiveCell.Offset(0, -19).Address, "$")(1) z = ActiveCell.Row
Rappelle-toi de F1 ;-)
eric