SOMMEPROD en VBA pour plage non vide Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 400
Appréciations reçues : 2
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 18 février 2019, 18:10

Bonjour,

J'ai fait une petite macro pour établir une grille de calcul simple, et il y a deux choses sur lesquelles je me questionne:

1. Est-il possible de coder les formules qui calculent seulement les plages non vide plutôt qu'une plage spécifique?
2. Pourquoi dans le classeur, le calcul ne commence pas à la ligne 2 pour les formules qui concernent Honneywell, SPERIAN et Starfield?
Range("P3").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""Innotex""))"
Range("Q3").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""Innotex""))"
Range("R3").FormulaR1C1 = "=RC[-2]+RC[-1]"
    
Range("P4").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""Honeywell""))"
Range("Q4").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""Honeywell""))"
Range("R4").FormulaR1C1 = "=RC[-2]+RC[-1]"
        
Range("P5").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""SPERIAN""))"
Range("Q5").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""SPERIAN""))"
Range("R5").FormulaR1C1 = "=RC[-2]+RC[-1]"
        
Range("P6").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-7]:R[247]C[-7]=""MANTEAU"")*(R[-1]C[-6]:R[247]C[-6]=""Starfield""))"
Range("Q6").FormulaR1C1 = "=SUMPRODUCT((R[-1]C[-8]:R[247]C[-8]=""PANTALON"")*(R[-1]C[-7]:R[247]C[-7]=""Starfield""))"
Range("R6").FormulaR1C1 = "=RC[-2]+RC[-1]"
 
Range("R7").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Merci pour votre aide.
Avatar du membre
LouReeD
Passionné d'Excel
Passionné d'Excel
Messages : 5'541
Appréciations reçues : 221
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Téléchargements : Mes applications
Contact :

Message par LouReeD » 18 février 2019, 22:39

Bonsoir,

pour ce qui est de la ligne 2, c'est du à l'écriture de votre formule qui est "relative"

Si vous êtes en Range("P3"), vous êtes en ligne 3 et avec R[-1] dans la formule vous "remonter" d'une ligne (le R vaut Row)
Donc les formules suivantes qui cible les lignes 4, 5 et 6 si vous faites R[-1] vous vous "retrouvez" en 3,4 et 5 !
Du coup cela ne commence pas à 2 ! ;-)
Pour commencer à 2 partout il ne faut pas que vos formules soient "relatives" mais "Absolues"

@ bientôt

LouReeD
Quelques règles à lire ICI ;;)
_______________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 400
Appréciations reçues : 2
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 19 février 2019, 16:04

Bonjour LouReed,

du genre:
[P3].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""Innotex""))"
[Q3].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""Innotex""))"
[R3].FormulaR1C1 = "=RC[-2]+RC[-1]"
    
[P4].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""Honeywell""))"
[Q4].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""Honeywell""))"
[R4].FormulaR1C1 = "=RC[-2]+RC[-1]"
        
[P5].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""SPERIAN""))"
[Q5].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""SPERIAN""))"
[R5].FormulaR1C1 = "=RC[-2]+RC[-1]"
        
[P6].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""MANTEAU"")*(R2C10:R247C10=""Starfield""))"
[Q6].FormulaR1C1 = "=SUMPRODUCT((R2C9:R247C9=""PANTALON"")*(R2C10:R247C10=""Starfield""))"
[R6].FormulaR1C1 = "=RC[-2]+RC[-1]"
 
[R7].FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Auriez vous une piste pour adapter la formule pour qu'elle calcul seulement les cellules non vide des colonne I et J?

Merci
Avatar du membre
LouReeD
Passionné d'Excel
Passionné d'Excel
Messages : 5'541
Appréciations reçues : 221
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Téléchargements : Mes applications
Contact :

Message par LouReeD » 19 février 2019, 21:40

Bonsoir,

si on test la cellule égale à Manteau, que faut-il éviter de compter si c'est égale à rien ?
Car Sommeprod fait la somme des produits et donc si une condition "par ligne" n'est pas respectée alors c'est égale à 0 donc cela ne change rien à la somme...

Il doit y avoir un truc qui m'échappe... La fatigue peut être ? ;-)

@ bientôt

LouReeD
Quelques règles à lire ICI ;;)
_______________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 400
Appréciations reçues : 2
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 19 février 2019, 22:15

Bonsoir Maître LouReed,

Serais-je en train de ne pas utiliser la bonne fonction?

En fait, ce calcul n'est qu'une portion du code. Il est intégré à une macro de mise en forme et de gestion de données.

J'extrais un rapport qui requiert un template spécifique, et d'une fois à l'autre le nombre de ligne n'est jamais pareil.

ici, je souhaite calculer le nombre de manteau et de pantalon par compagnie. Le SOMMEPROD m'est apparu comme une bonne solution...

La seule chose, c'est que la formule fonctionne pour une plage spécifique, alors que j'aimerais qu'elle fonctionne pour les lignes non vide des colonne I et J.

Voir onglet test qui donne le résultat de la macro MF_Liste_lundi_GR1
MASTER_Liste lundi_TA HB_2019.xls
(566 Kio) Téléchargé 3 fois
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 400
Appréciations reçues : 2
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 21 février 2019, 20:49

Bonjour LouReed,

Inspirer d'une trouvaille sur net, j'ai tenté ceci, mais ça renvoi à #NOM?
Sub MF_Liste_Lundi_GR1()
Dim i As Long
'Calcul

i = Range("I" & Rows.Count).End(xlUp).Row

'Renvoi à #NOM?
Range("I2").FormulaLocal = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Innotex""))"

'Fonctionne, mais pour une place spécifique
[J2].FormulaR1C1 = "=SUMPRODUCT((R7C9:R400C9=""PANTALON"")*(R7C10:R400C10=""Innotex""))"

End Sub

Merci

A+
Avatar du membre
LouReeD
Passionné d'Excel
Passionné d'Excel
Messages : 5'541
Appréciations reçues : 221
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Téléchargements : Mes applications
Contact :

Message par LouReeD » 22 février 2019, 15:03

Bonjour,

C'est peut être du à votre format locale...
Votre Excel est en français non?
Enlever le "local" de la ligne du dessus. Alors Excel transformera de lui même l'instruction dommprod anglaise en français... Peut être est ce cela...

@bientôt

LouReeD
Quelques règles à lire ICI ;;)
_______________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 400
Appréciations reçues : 2
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 27 février 2019, 14:59

Bonjour LouReed,

Que pensez-vous de ceci? J'imagine qu'il y a place à l'amélioration du code, mais pour moi, c'est le plus loin que je puisse aller!
Sub MF_Liste_Lundi_GR1()
Dim i As Long


' En-tête grille de calcul
[I1] = "Manteau"
[J1] = "Pantalon"
[H2] = "Innotex"
[H3] = "Honeywell"
[H4] = "SPERIAN"
[H5] = "Starfield"
[J6] = "Total"

With Range("I1:J1")
     'Range("J6:K6")
    .Font.Bold = True
    .Interior.ColorIndex = 1
    .Font.ColorIndex = 2
End With
 
With Range("H2:H5")
    .Interior.ColorIndex = 15
End With
 
With Range("I5:K5").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
End With

'Calcul

i = Range("I" & Rows.Count).End(xlUp).Row
i = Range("j" & Rows.Count).End(xlUp).Row

Range("I2").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Innotex""))"
Range("J2").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""Innotex""))"
[K2].FormulaR1C1 = "=RC[-2]+RC[-1]"
    
Range("I3").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Honeywell""))"
Range("J3").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""Honeywell""))"
[K3].FormulaR1C1 = "=RC[-2]+RC[-1]"
        
Range("I4").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""SPERIAN""))"
Range("J4").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""SPERIAN""))"
[K4].FormulaR1C1 = "=RC[-2]+RC[-1]"
       
Range("I5").Formula = "=SUMPRODUCT((I7:I" & i & "=""MANTEAU"")*(J7:J" & i & "=""Starfield""))"
Range("J5").Formula = "=SUMPRODUCT((I7:I" & i & "=""PANTALON"")*(J7:J" & i & "=""Starfield""))"
[K5].FormulaR1C1 = "=RC[-2]+RC[-1]"
 
[K6].FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

End Sub
Merci
Avatar du membre
LouReeD
Passionné d'Excel
Passionné d'Excel
Messages : 5'541
Appréciations reçues : 221
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Téléchargements : Mes applications
Contact :

Message par LouReeD » 27 février 2019, 18:47

Bonsoir,

Je ne comprends pas : le "i" est égal à la dernière cellule de la colonne I ou de la colonne J ?
Vous calculez les deux mais du coup la dernière valeur remplace la première... Ne manque t il pas un test ?

Si je comprend bien le fait de retirer "local" à résolu le problème ?

@bientôt

LouReeD
Quelques règles à lire ICI ;;)
_______________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 400
Appréciations reçues : 2
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 27 février 2019, 19:15

Bonjour LouReed,

ouille, je ne sais pas quoi vous répondre, j'ai mis les deux ne sachant pas ce qu'il fallait mettre. Donc, si je comprends bien, un seul i pour la Collone I devrait suffire à la formule? Et s'il manque un test, je ne sais comment m'y prendre!

Effectivement, le retrait de Local a résout la mauvaise référence.

Merci et à bientôt
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message