(Résolu par Job75) Formules Sommes et Cumul Variables

[sofiacasandra2016]

Bonjour et bonne année à tous,
dans mon fichier ci-joint j'ai 3 onglet.

• dans l'onglet (01-17) je cherche une formule variable dans Q10 comme ceci:

Q10 = la somme de tout les mois de l'année 2017 c'est à dire somme(F10: P10) + F10 de l'onglet (12-17) qui représente janvier de la même année
et cette même formule dans l'onglet (02-17) devient sur P10 :
P10= $E13$ + somme(F10:O10) + F10 (F10 de l'onglet précédent 01-17)
(même chose pour AB10 de l'onglet (01-17) et AA10 de l'onglet (02-17)

• la deuxième formule concerne R10 et AC10 de l'onglet 01-17) :

R10 = $E10$ qui est toujours fixe + la somme de toutes les cellules qui sont avant la colonne R et dont les cellules de la ligne 10 est un mois
c'est a dire R10= $E10$ + somme(F10: P10)

AC10= $E10$ + somme(F10: P10) + somme(S10:AA10) (la formule exclut automatiquement Q10, R10, AB10 car Q9, R9, AB9 n'est pas un mois)

Merci d'avance pour assistance

Cordialement
Sofia

 

[sofiacasandra2016]

Bonjour Dranreb,
merci beaucoup pour votre réponse
concernant la 1er formule elle fonctionne mais sauf si on incrémente de 01-17 vers 02-17, alors que si on commence de 12-16 vers 01-17 la formule renvoi erreur sur l'onglet 01-17 comme vous pouvez constater
de plus la formule doit apparaître aussi dans la colonne EX 2018, et il se peut dans l'avenir ou qur un autre fichier avoir deux autre colonnes EX 2019 et EX 2020 par exemple, donc la formule doit être présente dans tout les cellules des colonnes qui commencent avec EX

Merci encore une fois pour votre assistance

 

[Dranreb]

Non mais là vous avez le modèle qui marche pour le cas présenté. Alors maintenant débrouillez vous pour adapter aux autres cas de figures.

 

[sofiacasandra2016]

Bonjour,
mais dans ce cas la formule n'est pas variable, elle fonctionne juste pour ce cas de figure

 

[job75]

Bonjour sofiacasandra, salut Bernard,

Pourquoi se casser la tête alors qu'il suffit de cette formule en F9 :

="Janvier-"&DROITE(CELLULE("filename";A1);2)+(GAUCHE(DROITE(CELLULE("filename";A1);5);2)="12")

et lors de l'incrémentation de masquer la colonne du mois en cours :

Sub Incremente() 'Macro - 14/01/17
Dim w As Worksheet, dat$, maxi&, i%
For Each w In Worksheets
  dat = "1-" & w.Name
  If IsDate(dat) Then If CDate(dat) > maxi Then maxi = CDate(dat): i = w.Index
Next
If maxi = 0 Then Exit Sub
Sheets(i).Copy After:=Sheets(i)
With Sheets(i + 1)
  .Name = Format(maxi + 31, "mm-yy")
  If Month(maxi + 31) = 12 Then .Columns.Hidden = False _
    Else .Columns(Application.Match(Format(maxi + 31, "mmmm-yy"), .Rows(9), 0)).Hidden = True
End With
End Sub

Fichier joint.

A+

 

[sofiacasandra2016]

Bonsoir Job,

Merci beaucoup pour votre réponse mais masquer la colonne F n'est pas une solution parce que chaque fin de mois j'ai un autre fichier d'ou je fais l'importation de certaine donnée et la première colonne est toujours le mois en cours, donc le mois précédent s'efface.
le code d'incrémentation que j'ai posté fonctionne à merveille mais j'ai juste besoins des formules citées sur mon premier poste pour compléter mon fichier d'origine

Amicalement

 

[job75]

Re,

Je maintiens que le masquage des colonnes est la bonne solution car c'est la plus simple.

Il suffit d'adapter correctement la macro d'importation des données du mois.

C'est difficile bien sûr de changer son fusil d'épaule quand on a une idée en tête !

A+

 

[sofiacasandra2016]

re,
je ne dis pas le contraire c'est vous l'expert qui sait le plus simple, moi ce qui m'intéresse c'est le résultat
sauf que ce fichier je l'envois par la suite à mon supérieur au boulot (avec masquage biensur des codes) après chaque mise à jour, et ce dernier à horreur des colonnes et lignes masquées donc c'est pas une bonne solution pour moi.
en tout cas si ce n'est pas possible de réaliser les formules demandés avec le même code d'incrémentation qui élimine la colonne F ce n'est pas grave
déjà le faite que beaucoup de gens ont répondu sur mon sujet fait preuve de votre bonne assistance, ça n'a pas été le cas dans d'autres forums.

Merci beaucoup encore une fois

Cordialement

 

[job75]

Re,

sauf que ce fichier je l'envois par la suite à mon supérieur au boulot (avec masquage biensur des codes) après chaque mise à jour, et ce dernier à horreur des colonnes et lignes masquées

Ah bon et vous êtes sûre qu'il n'a pas encore plus horreur des usines à gaz ?

Mais pour l'aider à soigner sa claustrophobie ne masquons rien :

Sub Incremente() 'Macro - 14/01/17
Dim w As Worksheet, dat$, maxi&, i%
For Each w In Worksheets
  dat = "1-" & w.Name
  If IsDate(dat) Then If CDate(dat) > maxi Then maxi = CDate(dat): i = w.Index
Next
If maxi = 0 Then Exit Sub
Sheets(i).Copy After:=Sheets(i)
With Sheets(i + 1)
  .Name = Format(maxi + 31, "mm-yy")
  .Visible = xlSheetVisible
  Application.Goto .Cells(1, Application.Match(Format(maxi + 62, "mmmm-yy"), .Rows(9), 0)), True
End With
ActiveCell(9).Select
End Sub

Fichier (2).

Edit : ajouté .Visible = xlSheetVisible au cas où la feuille serait masquée.

A+

 

[sofiacasandra2016]

re Job,

vous avez raison concernant mon sup
pour ma demande je crois que vous n'avez pas bien compris ma problématique
la colonne F doit être supprimé et remplacé par le mois au cour et la formule que j'ai sur F9 répond à ça
dans la colonne EX 2017 c'est la somme de tout les mois de l'année 2017 c'est à dire dans l'onglet 02-17 j'aurai la somme des mois 2017 de cet onglet + la valeur de février 2017 qui se trouve dans F10 de l'onglet 01-12 + la valeur de Janvier-2017 qui se trouve dans F10 de l'onglet 12-16
parce que la base de formule doit être la somme de toutes les valeurs concernant l'année 2017 et à chaque incrémentation la formule prend F10 de l'onglet précédent s'il le mois et de la même année (même chose pour 2018)
Voir fichier ci-joint avec les formules que j'ai inséré manuellement

EDIT: dans votre fichier les données du mois de la colonne F ne s'efface pas alors que ces données doivent être supprimées et remplacé par le mois d'après c'est ce qui fait mon premier code, le problème reste juste dans les formules des sommes

Cordialement
Sofia

 

[job75]

Re,

Pour ma demande je crois que vous n'avez pas bien compris ma problématique

Vous voulez rire ?

Allez respirez un grand coup, c'est à vous de revoir votre manière de penser

PS : j'ai édité mon post précédent avec un petit complément.

A+

 

[Dranreb]

Bonsoir.
Si vous vouliez étudier un tout petit peu la programmation de ma solution, ajoutez des If condition Then pour cerner le cas de figure et mettez une autre formule dans le cas ou la plus fréquente ne convient pas. Par exemple quelque chose dans ce genre (je ne sais pas moi: c'est vous qui comprenez la logique des formules que vous voulez mettre) :

Dim M As Long, A As Long, C As Long, F10Précéd As String, F10Décemb As String
M = Month(.[F9].Value): A = Right$(.[F9].Value, 2)
F10Décemb = "'12-" & Format(A - 1, "00") & "'!RC6"
C = 19 - M
If M > 2 Then
   F10Précéd = "'" & Format(M - 2, "00") & "-" & Format(A, "00") & "'!RC6"
   .Cells(10, C).FormulaR1C1 = "=SUM(RC6:RC[-1]," & F10Précéd & "," & F10Décemb & ")"
Else
   .Cells(10, C).FormulaR1C1 = "=SUM(RC6:RC[-1]," & F10Décemb & ")"
   End If
End Sub

Ensuite pour les autres EX ajoutez un code commençant par Do: C = C + 15: If Left$(.Cells(9, C).Value, 2)<> "EX" Then Exit Do
Puis le code qui installe la formule pour cet EX. Normalement il y faudrait RC[-12] au lieu de RC6 parce qu'il y a toujours les 12 mois je crois.
Terminez par Loop
Vérifiez si c'est bien 15 qu'il faut ajouter pour tomber sur le EX suivant, et s'il y a lieu de changer des choses comme M ou A, voire recalculer F10Précéd ou au contraire ne plus l'utiliser du tout.

 

[sofiacasandra2016]

re,
Job merci pour votre réponse mais ce n'est pas du tout ce que j'ai demandé je ne sais pas comment vous expliquer que le code que j'ai fonctionne super bien j'ai juste besoin d'ajouter les formules en tout cas je vous remercie beaucoup pour votre assistance...

Dranreb votre réponse était vraiment proche à ma demande mais il faut la rendre variable et ce que vous me demandez de faire je n y arriverai vraiment pas à le réaliser


Cordialement
Sofia

 

[Dranreb]

Pas si vous n'essayez pas en tout cas.
Mais tout ce que j'ai vu devant être variable l'est: Anné, mois et position horizontale de la cellule devant porter la formule, et comme c'est de la notation R1C1, aussi les colonnes en notation A1 impliquées, grâce aux références relatives indiquées par un décalage entre crochets par rapport à la cellule qui la porte. RC[-1] désigne toujours la cellule juste à gauche de la formule qui la porte, quelle que soit sa colonne. Tandis que RC6 désigne toujours la colonne F sur la même ligne que la formule.

 

[sofiacasandra2016]

Bonjour dranreb,
Les formules sont variables juste si on commence avec janvier 2017 par contre quand j'ai essayé avec decembre 2016 ca n'a pas marché comme je vous ai montré dans le fichier que j'ai joint..
Je ne s'aurai rien moi si non, je ne suis même pas débutante....
En tout cas merci beaucoup a à vous, Job et tout les membres qui ont essayé de me donner un coup de main

Cordialement
Sofia

 

[job75]

Bonjour sofiacasandra2016, Bernard, le forum,

Ce que femme veut Dieu le veut...

Alors voyez ce fichier joint avec cette fonction VBA :

Function SommePrec(r As Range)
Application.Volatile
Dim dat As Date, i%
dat = CDate("1-" & Application.Caller.Parent.Name)
On Error Resume Next 's'il manque des feuilles
For i = 1 To 12 - r.Count
  SommePrec = SommePrec + Sheets(Format(DateAdd("m", -i, dat), "mm-yy")).[F10]
Next
End Function

Elle est utilisée en R10 et AF10 en complément de la fonction SOMME.

Contrairement à ce que vous pensez votre macro Incremente n'est pas bien fameuse, en particulier quand on passe à l'année suivante.

Celle-ci est aux petits oignons :

Sub Incremente() 'Macro - 15/01/17
Dim w As Worksheet, dat$, maxi&, i%, f$
For Each w In Worksheets
  dat = "1-" & w.Name
  If IsDate(dat) Then If CDate(dat) > maxi Then maxi = CDate(dat): i = w.Index
Next
If maxi = 0 Then Exit Sub
Application.Calculation = xlCalculationManual 'évite les recalculs des formules volatiles
Sheets(i).Copy After:=Sheets(i)
With Sheets(i + 1)
  .Name = Format(maxi + 31, "mm-yy")
  .Visible = xlSheetVisible 'si la feuille est masquée
  f = .[F9].Formula
  If Month(maxi + 31) < 12 Then
    .[F:F].Delete
  Else
    Application.ScreenUpdating = False
    .[F:P].Insert 'insertion de 11 colonnes pour en faire 14
    .[T:AG].Copy .[F1] '14 colonnes
  End If
  .[F9] = f
End With
Application.Calculation = xlCalculationAutomatic 'recalcul de toutes les feuilles
End Sub

Nota : le tableau de la 2ème année doit impérativement avoir 12 mois...

Et voyez aussi la formule en F9, bien plus simple.

Edit : la Workbook_Open évite l'invite à la fermeture due aux formules volatiles.

A+