Recherche d'une formule avancée sur Excel

Résolu/Fermé
Signaler
Messages postés
9
Date d'inscription
jeudi 24 novembre 2016
Statut
Membre
Dernière intervention
20 décembre 2016
-
Messages postés
9
Date d'inscription
jeudi 24 novembre 2016
Statut
Membre
Dernière intervention
20 décembre 2016
-
Bonjour,

Je cherche une aide pour réaliser une formule.
Mon problème est le suivant:

Dans un premier tableau, j'ai renseigné un nombre de contrats signés dans l'année 2017, mois par mois.
Dans un second tableau, j'ai le CA généré par chaque contrat signé, mois par mois.

Dans le troisième tableau, je voudrais renseigner le CA généré par mois, à partir du mois de signature du contrat.

Est-ce que quelqu'un connaît une formule pour réaliser ce type de calcul svp?

J

1 réponse

Messages postés
26305
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 décembre 2021
6 131
Bonjour

=SOMMEPROD((MOIS(champ date)=N° du mois cherché)*(champ des montants))

ce code impose des champs limités avec des n° de ligne (pas de A:A ou B:B donc)
crdlmnt
La qualité de la réponse dépend surtout de la clarté de la question, merci!
Messages postés
9
Date d'inscription
jeudi 24 novembre 2016
Statut
Membre
Dernière intervention
20 décembre 2016
> Utilisateur anonyme
Bonjour,
J'ai trouvé Visual Basic. Je vous avoue que c'est nouveau pour moi...
Pourriez-vous svp m'expliquer rapidement le code que vous avez fait?
En effet, je dois ensuite l'adapter à des calculs plus importants, intégrant plusieurs de calcul. Il faut donc que je comprenne ce que vous avez fait afin de pouvoir l'adapter.
Je vous remercie infiniment!


Option Explicit


Sub Essai()
Application.ScreenUpdating = False
Dim col1 As Integer, col2 As Integer, r As Byte, k As Byte
Dim dcol As Integer: dcol = [B29].CurrentRegion.Columns.Count + 1
Range([C30], Cells(30, dcol)).ClearContents
For col1 = 3 To dcol
k = Cells(24, col1)
If k > 0 Then
r = 3
For col2 = col1 To dcol
Cells(30, col2) = Cells(30, col2) + k * Cells(27, r): r = r + 1
Next col2
End If
Next col1
End Sub
Utilisateur anonyme >
Messages postés
9
Date d'inscription
jeudi 24 novembre 2016
Statut
Membre
Dernière intervention
20 décembre 2016

 
Bonjour Mimichou28,

Tu vas avoir une longue lecture, car tu comprendras vite que pour une bonne
compréhension du code VBA, ce n'est pas possible de l'expliquer rapidement.
Et il y a bien des choses que j'aurais pu détailler encore plus !  ;)


Sub Essai()
  Application.ScreenUpdating = False
  Dim col1 As Integer, col2 As Integer, r As Byte, k As Byte
  Dim dcol As Integer: dcol = [B29].CurrentRegion.Columns.Count + 1
  Range([C30], Cells(30, dcol)).ClearContents
  For col1 = 3 To dcol
    k = Cells(24, col1)
    If k > 0 Then
      r = 3
      For col2 = col1 To dcol
        Cells(30, col2) = Cells(30, col2) + k * Cells(27, r): r = r + 1
      Next col2
    End If
  Next col1
End Sub


Application.ScreenUpdating = False est pour empêcher la mise à jour de l'écran
lors de l'exécution de la macro => plus confortable pour la vue et plus rapide.

Les 2 instructions Dim sont pour déclarer les variables utilisées par la Sub.
col1, col2 : colonne 1 et colonne 2 ; dcol : dernière colonne ; r et k sont
des « variables de travail ».

--------------------------------------------------

dcol = [B29].CurrentRegion.Columns.Count + 1 => dcol = n° de la dernière
colonne utilisée, ici : 14 pour la colonne N ; c'est le nombre de colonnes du
tableau (13) ; +1 car ce tableau commence en colonne B et il y a 1 seule
colonne avant : la colonne A.

Si tu prolonges tes tableaux à droite de par exemple 5 colonnes, dcol = 19
pour colonne S => adaptation automatique.

--------------------------------------------------

L'instruction suivante est pour effacer les résultats de la ligne 30, de C30 à N30 ;
mais comme N est variable : jusqu'à la cellule de ligne 30 et colonne dcol ;
comme dcol = 14, et que 14 est le n° de la colonne N, on efface bien C30:N30 ;
ClearContents signifie : « effacer le contenu » ; c'est comme si tu sélectionnes
une cellule non vide et que tu fais < Suppr > : ça efface la donnée mais pas le
format (bordures, remplissage, alignement, format de nombre et autres...).

Attention : cet effacement est obligatoire pour éviter un mélange entre les
résultats précédents et les nouveaux ; c'est aussi grâce à cette instruction
que j'ai pu écrire :
« Si tu changes tes données de la ligne 24, tu n'as pas besoin d'effacer les
résultats de la ligne 30 avant de faire < Ctrl >< e > de nouveau. ».

--------------------------------------------------

For col1 = 3 To dcol ... Next col1 : tout ce qui est dans cette boucle sera fait
de la colonne 3 (C) à la colonne 14 (N) ; au départ, col1 = 3 ; et c'est quand
Next col1 est exécuté que col1 augmente de 1 => 4, puis 5, puis 6...

--------------------------------------------------

k = Cells(24, col1) => k = contenu de la cellule ligne 24, colonne col1 ;
= le nombre de contrats signés que tu as saisi en ligne 24, colonnes
3 à 14 ; donc de C24 à N24.

If k > 0 Then ... End If : tout ce qui est dans ce bloc sera fait seulement si k > 0,
donc seulement si tu as saisi un nombre de contrats signés => si tu n'as rien
saisi (cellule vide), on ne fait rien et on passera à la colonne suivante.

--------------------------------------------------

r = 3 : en fait, c'est aussi un n° de colonne, qui correspond au rang des revenus
de la ligne 27 ; au départ : colonne 3 = colonne C => ce sera la cellule C27.

--------------------------------------------------

For col2 = col1 To dcol ... Next col 2 : tout ce qui est dans cette boucle sera fait
de la colonne col1 à la colonne 14 (N) ; au départ, col2 = col1 ; et c'est quand
Next col2 est exécuté que col2 augmente de 1.

--------------------------------------------------

Cells(30, col2) = Cells(30, col2) + k * Cells(27, r): r = r + 1

Ce sont ces 2 instructions qui font progressivement sur la ligne 30 les résultats
que tu attends. Rappelles-toi qu'au départ, je l'ai effacée => C30:N30 est vide =>
comme s'il y avait des 0 de C30 à N30 : aucun chiffre d'affaires au début.

C'est primordial puisqu'on va y faire des ajouts : pour la colonne col2, on ajoute
un revenu qui correspond au nombre de contrats signés (1 ; ou plus si tu veux) ×
le revenu de rang r.

Voici un exemple bien concret :

Suppose que col1 = 8 : ça veut dire qu'on a trouvé le nombre de contrats signés
(non nul) de la colonne H => col2 = col1 à 14 : les résultats (= chiffres d'affaires
générés) seront mis des colonnes H à N ; mais attention : le 1er revenu est bien
celui de la colonne 3 (C) en ligne 27, d'où : r = 3 et « Cells(27, r) » ; puis ensuite :
r = r + 1 => r = 4 => revenu suivant = D27, etc...

--------------------------------------------------

Une longue explication, mais difficile de faire autrement !  ;)  J'espère que tu
réussiras à adapter mon code à des calculs plus importants, et te souhaite
de bonnes fêtes de Noël et de fin d'année !  :)

Si mon explication te convient, merci de passer le sujet en résolu ; sinon,
tu peux me demander plus d'infos (pour ce que j'ai déjà fait) ; si tu veux
autre chose, c'est mieux que tu crées un nouveau sujet.

Cordialement.  :)
 
Messages postés
9
Date d'inscription
jeudi 24 novembre 2016
Statut
Membre
Dernière intervention
20 décembre 2016

Bonjour,
Merci beaucoup, les résultats me paraissent corrects!
Par contre, je ne vois pas la formule que vous avez utilisée? Pouvez-vous me dire comment vous avez procéder svp?
De plus, je n'arrive pas à accéder à "Visual Basic"?...
Merci de votre aide!
Utilisateur anonyme >
Messages postés
9
Date d'inscription
jeudi 24 novembre 2016
Statut
Membre
Dernière intervention
20 décembre 2016

 
Bonjour Mimichou28,

Il n'y a aucune formule dans ce que j'ai fait ; c'est uniquement par VBA :
la macro Essai(), que tu peux lancer par < Ctrl >< e >.

J'ai vu dans ton message #9 que tu as trouvé Visual Basic, et je viens d'y
répondre pour expliquer mon code VBA : voir mon message #10.

Cordialement.  :)
 
Messages postés
9
Date d'inscription
jeudi 24 novembre 2016
Statut
Membre
Dernière intervention
20 décembre 2016
> Utilisateur anonyme
Merci infiniment de toute votre aide et de ce petit cours accéléré de VBA!
J'ai posté mon problème comme résolu.
Bonne journée!