Discussion :

[Phenicia77]

Bonjour à tous.

J'aurai besoin de savoir s'il est possible d'incrémenter un mois de l'année écrit en lettre? en gros, je veux passer de janvier puis à février,....
Je vous met un extrait de mon fichier.
Il faut que je remplisse l'onglet "Triangulation".
en cellule c3, j'aurai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
For i= 2 to 24
     If worksheets ("triangulation").cells(2,2) = worksheets("primes).cells(i,2)
         If worksheets ("triangulation").cells(1,2) = worksheets("primes).cells(i,3)
             If worksheets ("triangulation").cells(2,2) = worksheets("primes).cells(i,5)
                 If worksheets ("triangulation").cells(1,2) = worksheets("primes).cells(i,6)
                     prime = prime +  worksheets("primes").cells(i,7)

et en colonne D3, c'est le même principe mais pour le 3ème if, il faudrait que j'arrive à avoir octobre et non septembre. Donc en gros "septembre + 1 mois" et ainsi de suite jusqu'à ma dernière colonne.
Va aussi se poser le problème du changement d'année quand on va passer de décembre à janvier. Mais je pensais mettre une condition : si mois = janvier alors année = année +1 mais il y a peut être mieux à faire?
J'aimerai garder les mois en lettres mais si ce n'est pas possible, je les modifierai en chiffre mais je trouve ça moins "convivial".

Je remercie par avance les personnes qui pourront m'éclairer.
Je vous souhaite un excellent après-midi.

[Menhir]

Si ça ne te dérange pas d'avoir le mois en anglais, tu peux utiliser la fonction Format.

En supposant que le numéro du mois soit dans la variable MoisNum :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MoisTxt = Format(DateSerial(2000, MoisNum, 1), "mmmm")

Si c'est pour mettre dans une cellule, il suffit de mettre n'importe quelle date de ce mois et de passer le format à "mmmm".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Range("A1").Value = DateSerial(Year(Now()), MoisNum, 1)
Range("A1").NumberFormat = "mmmm"

[Pierre Fauconnier]

Salut.

Avec une bonne conception de classeur, tu n'as pas besoin de VBA pour réaliser ce genre de tableau. Un peu d'astuce et des fonctions telles que SOMME.SI.ENS suffisent

Tu aurais franchement intérêt à travailler avec des dates partout, plutôt que du texte. Tu simplifieras grandement tes formules et, grâce aux formats de cellule, tu pourras avoir la présentation que tu souhaites.

• En colonne A, modifier le format des cellules et mettre le format personnalisé "AAAA"
• En A3, saisir la date 1/09/2018
• En A4:A30, saisir la formule =mois.decaler(a3;1) pour incrémenter d'un mois et valider avec CTRL+ENTER
• En B3:B30, saisir la formule =a3 et valider avec CTRL+ENTER
• En B3:B30, mettre au format "MMMM"
• En C3:AI30, saisir la formule =MOIS.DECALER($B3;COLONNE()-3) et valider avec CTRL+ENTER (juste pour voir tes dates s'incrémenter).

Voici ce que cela pourrait donner sur le plan visuel:



La rédaction des formules en C3:AI30 sera plus aisée car tu pourras travailler avec SOMME.SI.ENS par exemple, en bornant tes critères sur les dates de début et de fin de mois pour chaque cellule, et tu n'auras plus à te soucier des passages d'année et autres. De plus, tu te passes complètement de VBA (au moins pour cette partie du travail). Il faudrait que tu en dises plus sur ce que tu souhaites obtenir dans les cellules C4 et suivantes pour que l'on puisse t'aider au mieux.


Excel étant un tableur plus à l'aise avec les valeurs numériques (les dates sont des nombres) qu'avec du texte, tes formules seront plus simples à rédiger.

[Phenicia77]

Je vais commencer par changer mes formats et je vais voir si j'arrive à mettre les formules voulues dans mes cellules plutôt que de faire du VBA.
J'étais parti sur du VBA pour que tout soit automatisé mais c'est vrai que des formules sont tout aussi valable.
Je n'hésiterai pas à revenir si j'ai encore besoin d'aide ou pour clore la discussion si j'ai réussi :-)

[Phenicia77]

Bonjour .

J'ai changé les formats comme conseillé ; ce qui devrait m'aider aussi dans d'autres fichiers.
Je n'arrive cependant pas à faire ce que j'aimerai.
J'ai mis une autre version du fichier, avec des données différentes pour une meilleure compréhension de ce que je voudrai faire.
En cellule C3, il faudrait que j'additionne les primes de la colonne G de ma feuille "Primes" quand le mois et l'année de souscription sont les mêmes qu'en A3 et B3 et le mois et l'année de paiement sont les mêmes qu'en A3 et B3.
En D3, j'additionne les primes toujours si le mois et l'année de souscription sont les même qu'en A3 et B3 mais cette fois-ci l'année et le mois de paiement doivent être 2018 et octobre (donc septembre 2018 + 1 mois)
Et donc en D6, par exemple, je dois additionner les primes dont l'année et le mois de souscription sont décembre 2018 et l'année et le mois de paiement janvier 2019.

J'espère que c'est assez claire :-) Ce n'est pas toujours facile d'expliquer.
Je remercie d'avance les personnes qui pourront m'aider.

[Pierre Fauconnier]

C'est un SOMME.SI.ENS qui te permettra cela. Note que tes dates ne sont pas bien saisies pour certaines valeurs dans ton tableau de primes. Veille bien à chaque fois saisir des dates, car c'est cela qui permet à Excel de travailler avec ton tableau tel qu'il est conçu.

La formule est longue mais pas complexe. Elle est longue à cause des intitulés de tes colonnes d'une part et parce que tu as 4 critères pour ta somme (Souscription >= 1er jour du mois en A3 et <= fin du mois de A3, Paiement > fin du mois précédent et <= à la fin du mois actuel). Dans la formule que je te donne, j'ai calculé par rapport à la structure de ton tableau. Tu ne peux donc pas insérer des colonnes sans dommages, mais vu la conception du classeur, ce problème ne devrait pas arriver.

Pense aussi à mettre la plage des primes en tableau structuré (voir mon tuto). De cette façon, lorsque tu y ajouteras des primes, ta "triangulation" sera recalculée.


Voici la formule à mettre en C3:AI30 (avec cette formule, tu peux ajouter des lignes ou des colonnes):
=SOMME.SI.ENS(Tableau1[PRIME HT (en euros)];Tableau1[DATE DE SOUSCRIPTION];">=" &$A3;Tableau1[DATE DE SOUSCRIPTION];"<=" & FIN.MOIS($A3;0);Tableau1[DATE DE PAIEMENT];">" & FIN.MOIS($A3;COLONNE()-4);Tableau1[DATE DE PAIEMENT];"<=" & FIN.MOIS($A3;COLONNE()-3))

Attention, dans mon précédent message, je disais que tu pouvais saisir la formule en A3:A3 et valider avec CTRL+ENTER, mais tu as validé avec SHIFT+CTRL+ENTER. Ce n'est pas une bonne idée. Soit tu saisis en A3 puis tu recopies vers le bas, soit tu sélectionnes A3:A30, puis tu saisis la formule et tu valides avec CRL+ENTER (SANS le SHIFT)...

Si tu saisis la formule ci-dessus en C3, tu ne peux la recopier QUE avec le copier-coller ET PAS avec la poignée de recopie (cfr mon tuto sur les tableaux structurés cités plus haut, au point IV-D-4)

[Phenicia77]

Merci beaucoup de ton aide, même si je n'arrive pas à faire fonctionner la formule sur mon fichier d'origine.

Par contre, je ne comprend pas pourquoi, mais dans la cellule D3 du fichier que tu as joint, il n'y a pas le bon montant : il devrait y avoir 10.8556.

Dans mon fichier d'origine, j'ai donc créé un tableau structuré en suivant la méthode de ton tuto et j'ai mis toutes mes dates de l'onglet "primes" au format "date". mais j'obtiens "0" dans toutes mes cellules.

Même en repartant du fichier que j'avais envoyé, je n'arrive pas aux mêmes résultats que toi (je pense avoir écrit correctement la formule). Peux-tu y jeter un oeil et me dire si quelque chose ne va pas? Je te remercie par avance.
PS : j'ai modifié les montants de primes pour une meilleure lisibilité / visibilité.

[Pierre Fauconnier]

Les dates en A2, A3, D1, etc (celles avec l'année en 4 chiffres) ne sont pas des dates mais du texte. Tu dois les saisir à nouveau (sélectionne la cellule puis F2 puis Enter).

Tu as recopié la formule de C3 avec la poignée de recopie et pas avec un copier-coller normal. Or, recopier latéralement une formule utilisant une colonne structurée avec la poignée de recopie implique qu'Excel considère la colonne comme relative, donc, tu "glisses" de colonne en colonne.

Voici ton fichier corrigé.

[Phenicia77]

super, j'ai réussi sur ce petit extrait. Maintenant je passe à l'étape suivante mon fichier d'origine.
Et là, je constate que j'ai le même problème. J'avais sélectionné toute ma colonne de date, fait un clic droit/ format cells/date mais a priori ça ne met pas vraiment la cellule en format date, c'est bien ça?
Du coup, j'ai vérifié sur quelques lignes de mon fichier d'origine en modifiant la cellule avec F2/entrée et la formule a fonctionné.

Par contre, existe-t'il un moyen différent pour modifier mes cellules? Parce que mon fichier d'origine comporte presque 175000 et je ne me vois pas faire F2/entrée pour toutes mes lignes.

Merci d'avance pour ta réponse.

[Pierre Fauconnier]

• Dans une cellule vide, saisis 1
• Copie cette cellule
• Sélectionne la plage des dates
• Effectue un collage spécial multiplication
• Remets ta plage des dates au format de date souhaité

[Phenicia77]

Merci beaucoup de ton aide, tout marche parfaitement bien maintenant :-)
Je garde cette formule bien en tête parce qu'elle va me servir de nombreuses fois
Excellente journée