Calcul somme par page
Résolu
Jeessey
-
pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention -
pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention -
Bonjour,
J'aimerais savoir si en Excel, il y aurait une quelconque formule / Macro qui saurait calculer la somme des lignes de chaque page, sachant que chaque page peut varier.
Voici comment se structure mes donnees ; sur une colonne il y a jusqu'a cent lignes de salaire de differentes personnes. Chaque personne peut appartenir a differents groupes et enfin chaque groupe est variable suivant les mois.
Disant qu'au mois de Janvier, le Groupe A compte 40 personnes non successives sur les lignes. Au meme mois de Janvier, le Groupe B compte 13 personnes non successives sur les lignes. Au mois de Fevrier, le Groupe A compte seulement 3 personnes et le groupe B compte 17, tous non successives.
Mon souhait est de trouver une formule qui saurait calculer automatiquement la somme de ces salaires du Groupe X (A ou B) au mois Y (Janvier ou Fevrier).
J'ai deja trouvee la solution avec un filtre sur une colonne (Groupe) des personnes interessees et sur une autre colonne, des donnees qui compte combien de personnes ont ete selectionnees par le filtre et que les 17 premieres personnes filtrees representent une page et les 17 suivantes une autre page et ainsi de suite.
Ca marche bien sur mon ordi.
Cependant, en envoyant mon fichier a d'autres collegues, la dimension de la mise en page differe et ainsi une page ne represente plus 17 lignes (personnes) mais 16 par exemple.
L'ideal serait alors de connaitre automatiquement par formule jusqu'ou s'arrete la premiere page, puis la deuxieme et ainsi de suite sur une feuille Excel. Sachant cela, je pourrais calculer la somme de chaque page.
Une idee?
Merci par avance
J'aimerais savoir si en Excel, il y aurait une quelconque formule / Macro qui saurait calculer la somme des lignes de chaque page, sachant que chaque page peut varier.
Voici comment se structure mes donnees ; sur une colonne il y a jusqu'a cent lignes de salaire de differentes personnes. Chaque personne peut appartenir a differents groupes et enfin chaque groupe est variable suivant les mois.
Disant qu'au mois de Janvier, le Groupe A compte 40 personnes non successives sur les lignes. Au meme mois de Janvier, le Groupe B compte 13 personnes non successives sur les lignes. Au mois de Fevrier, le Groupe A compte seulement 3 personnes et le groupe B compte 17, tous non successives.
Mon souhait est de trouver une formule qui saurait calculer automatiquement la somme de ces salaires du Groupe X (A ou B) au mois Y (Janvier ou Fevrier).
J'ai deja trouvee la solution avec un filtre sur une colonne (Groupe) des personnes interessees et sur une autre colonne, des donnees qui compte combien de personnes ont ete selectionnees par le filtre et que les 17 premieres personnes filtrees representent une page et les 17 suivantes une autre page et ainsi de suite.
Ca marche bien sur mon ordi.
Cependant, en envoyant mon fichier a d'autres collegues, la dimension de la mise en page differe et ainsi une page ne represente plus 17 lignes (personnes) mais 16 par exemple.
L'ideal serait alors de connaitre automatiquement par formule jusqu'ou s'arrete la premiere page, puis la deuxieme et ainsi de suite sur une feuille Excel. Sachant cela, je pourrais calculer la somme de chaque page.
Une idee?
Merci par avance
A voir également:
- Script shell calcul somme
- Classic shell - Télécharger - Personnalisation
- Script vidéo youtube - Guide
- Formule somme excel colonne - Guide
- Calcul moyenne excel - Guide
- Somme si couleur - Guide
16 réponses
Bonjour,
J'avoues que sans un exemple de ton fichier, sans données confidentielles, ça ne va pas être simple.
J'apporte néanmoins ma contribution par une formule calculant la somme des valeurs d'une colonne, peu importe sa "longueur" :
Somme des valeurs de la colonne A, commençant en A1 (sans entête) :
=SOMME(DECALER(A1;;;NBVAL(A:A);))
Somme des valeurs de la colonne A, commençant en A2 (avec entête) :
=SOMME(DECALER(A2;;;NBVAL(A:A)-1;))
Somme de colonnes sur plusieurs feuilles :
=SOMME(DECALER(Feuil2!E1;;;NBVAL(Feuil2!E:E););DECALER(Feuil3!E1;;;NBVAL(Feuil3!E:E);))
Tu dis... Et si cela ne corresponds pas, envoies une copie de ton fichier.
J'avoues que sans un exemple de ton fichier, sans données confidentielles, ça ne va pas être simple.
J'apporte néanmoins ma contribution par une formule calculant la somme des valeurs d'une colonne, peu importe sa "longueur" :
Somme des valeurs de la colonne A, commençant en A1 (sans entête) :
=SOMME(DECALER(A1;;;NBVAL(A:A);))
Somme des valeurs de la colonne A, commençant en A2 (avec entête) :
=SOMME(DECALER(A2;;;NBVAL(A:A)-1;))
Somme de colonnes sur plusieurs feuilles :
=SOMME(DECALER(Feuil2!E1;;;NBVAL(Feuil2!E:E););DECALER(Feuil3!E1;;;NBVAL(Feuil3!E:E);))
Tu dis... Et si cela ne corresponds pas, envoies une copie de ton fichier.
Bonjour,
selon la présentation de tes données tu peux aussi regarder du coté de la 'consolidation de données'.
Pour le fichier exemple (allégé et anonymisé) : cjoint.com et coller ici le lien fourni.
eric
selon la présentation de tes données tu peux aussi regarder du coté de la 'consolidation de données'.
Pour le fichier exemple (allégé et anonymisé) : cjoint.com et coller ici le lien fourni.
eric
Merci a Pikaju et Eriiic.
Je reformule ma question ; Comment calculer la somme PAR PAGE d'une colonne ayant plusieurs lignes ? Ces lignes sont variables suivant un critere (mois).
Voici l'exemple ;
http://cjoint.com/?BGmkcd1oMj1
J'ai essaye l'astuce de Pikaju mais elle ne prend pas en compte le numero de la page, du moins selon ma connaissance.
Encore merci !
Jeessey
Je reformule ma question ; Comment calculer la somme PAR PAGE d'une colonne ayant plusieurs lignes ? Ces lignes sont variables suivant un critere (mois).
Voici l'exemple ;
http://cjoint.com/?BGmkcd1oMj1
J'ai essaye l'astuce de Pikaju mais elle ne prend pas en compte le numero de la page, du moins selon ma connaissance.
Encore merci !
Jeessey
Si les données dans les feuilles 1, 2, 3 et 4 sont placées dans les mêmes colonens que l'exemple (Col B=salaires, ColC = groupes, colD=mois) alors les formules à utiliser sont :
Groupe A, Feuil1, Janvier :
=SOMMEPROD((Feuil1!C6:C5000="A")*(Feuil1!D6:D5000="Janvier")*Feuil1!B6:B5000)
Somme Groupe A page 2 Janvier
=SOMMEPROD((Feuil2!C6:C5000="A")*(Feuil2!D6:D5000="Janvier")*Feuil2!B6:B5000)
Somme Groupe A page 3 Janvier
=SOMMEPROD((Feuil3!C6:C5000="A")*(Feuil3!D6:D5000="Janvier")*Feuil3!B6:B5000)
Somme Groupe A page 4 Janvier
=SOMMEPROD((Feuil4!C6:C5000="A")*(Feuil4!D6:D5000="Janvier")*Feuil4!B6:B5000)
Somme Groupe A page 1 Février
=SOMMEPROD((Feuil1!C6:C5000="A")*(Feuil1!D6:D5000="Février")*Feuil1!B6:B5000)
...
Somme Groupe B page 3 Février
=SOMMEPROD((Feuil3!C6:C5000="B")*(Feuil3!D6:D5000="Février")*Feuil3!B6:B5000)
Groupe A, Feuil1, Janvier :
=SOMMEPROD((Feuil1!C6:C5000="A")*(Feuil1!D6:D5000="Janvier")*Feuil1!B6:B5000)
Somme Groupe A page 2 Janvier
=SOMMEPROD((Feuil2!C6:C5000="A")*(Feuil2!D6:D5000="Janvier")*Feuil2!B6:B5000)
Somme Groupe A page 3 Janvier
=SOMMEPROD((Feuil3!C6:C5000="A")*(Feuil3!D6:D5000="Janvier")*Feuil3!B6:B5000)
Somme Groupe A page 4 Janvier
=SOMMEPROD((Feuil4!C6:C5000="A")*(Feuil4!D6:D5000="Janvier")*Feuil4!B6:B5000)
Somme Groupe A page 1 Février
=SOMMEPROD((Feuil1!C6:C5000="A")*(Feuil1!D6:D5000="Février")*Feuil1!B6:B5000)
...
Somme Groupe B page 3 Février
=SOMMEPROD((Feuil3!C6:C5000="B")*(Feuil3!D6:D5000="Février")*Feuil3!B6:B5000)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
En fait, il ne s'agit pas de feuille 1, 2, 3 et 4 mais d'une seule feuille dont l'impression peut donner jusqu'a 4 pages (page 1, 2, 3 et 4).
Alors il faut passer par du VBA.
Je t'ai préparé une fonction personnalisée qui ne devrait fonctionner que dans ton cas, si les données sont rigoureusement présentées comme dans ton classeur exemple...
Dans un module (ALT+F11 depuis ta feuille, puis Insertion/Module) copier/coller ce code :
Pour l'utiliser, saisir dans une cellule la formule (par exemple pour Groupe A, Janvier, Page 2) :
=Somme2CritParPage("A";"Janvier";2)
Je t'ai préparé une fonction personnalisée qui ne devrait fonctionner que dans ton cas, si les données sont rigoureusement présentées comme dans ton classeur exemple...
Dans un module (ALT+F11 depuis ta feuille, puis Insertion/Module) copier/coller ce code :
Function Somme2CritParPage(Crit1 As String, Crit2 As String, NumPage As Byte) As Double
Dim Plage As Range
Dim Tabl(), i As Byte
If NumPage = 1 Then
Set Plage = Range("B6:" & ActiveSheet.HPageBreaks.Item(NumPage).Location.Offset(-1, 3).Address)
Else
Set Plage = Range(ActiveSheet.HPageBreaks.Item(NumPage - 1).Location.Address & ":" & ActiveSheet.HPageBreaks.Item(NumPage).Location.Offset(-1, 3).Address)
End If
Tabl = Plage
For i = 1 To UBound(Tabl, 1)
If Tabl(i, 3) = Crit1 And Tabl(i, 4) = Crit2 Then
Somme2CritParPage = Somme2CritParPage + Tabl(i, 2)
End If
Next
Set Plage = Nothing
End Function
Pour l'utiliser, saisir dans une cellule la formule (par exemple pour Groupe A, Janvier, Page 2) :
=Somme2CritParPage("A";"Janvier";2)
A Franck P,
Merci infiniment pour tes reponses. Je ne connaissais pas l'existence de Numpage et de Hpagebreaks en Macro mais vraisemblablement c'est ce qu'il me faut.
Une fois de plus mille mercis ! Il ne me reste plus qu'a assimiler ces fonctions pour m'adapter avec le vrai fichier.
Merci !!!!!!!!!
Joël Claude
Merci infiniment pour tes reponses. Je ne connaissais pas l'existence de Numpage et de Hpagebreaks en Macro mais vraisemblablement c'est ce qu'il me faut.
Une fois de plus mille mercis ! Il ne me reste plus qu'a assimiler ces fonctions pour m'adapter avec le vrai fichier.
Merci !!!!!!!!!
Joël Claude
NumPage n'est qu'une variable, ce n'est pas un objet VBA...
Par contre HPagebreaks oui. Tu as toutes les propriétés sur l'aide VBA.
Le plus important pour toi est de définir la plage de cellules que tu veux traiter. Plage qui correspond donc à 1 page.
La propriété Item(x).Location.Address peut grandement t'aider.
Indique le numéro de la page dans l'Item (Item(1) = page 1 etc...) et place le tout dans un MsgBox pour tester...
Comme ceci :
Cordialement,
Franck P
Par contre HPagebreaks oui. Tu as toutes les propriétés sur l'aide VBA.
Le plus important pour toi est de définir la plage de cellules que tu veux traiter. Plage qui correspond donc à 1 page.
La propriété Item(x).Location.Address peut grandement t'aider.
Indique le numéro de la page dans l'Item (Item(1) = page 1 etc...) et place le tout dans un MsgBox pour tester...
Comme ceci :
Sub Test() 'Sert à indiquer l'adresse de la première cellule page 2 (fin de l'Item(1)) 'de la feuille active MsgBox ActiveSheet.HPageBreaks.Item(1).Location.Address 'donne l'adresse du range représentant la page 3 MsgBox Range(ActiveSheet.HPageBreaks.Item(2).Location.Address & ":" & ActiveSheet.HPageBreaks.Item(3).Location.Offset(-1, 3).Address).Address 'Si tes pages se "propagent" vers la droite et non vers le bas alors : 'MsgBox ActiveSheet.VPageBreaks.Item(1).Location.Address End Sub
Cordialement,
Franck P
Encore plus interessant!
Retenu !
Merci !
En passant, pourquoi mon boucle ne s'arrete pas quand il arrive a la derniere rangee definie par mon U & L Bound ? Un site web qui explique cela ?
Salutations,
Retenu !
Merci !
En passant, pourquoi mon boucle ne s'arrete pas quand il arrive a la derniere rangee definie par mon U & L Bound ? Un site web qui explique cela ?
Salutations,
pourquoi mon boucle ne s'arrete pas quand il arrive a la derniere rangee definie par mon U & L Bound ?
???
Sans le code intégral, j'peux pas te dire...
???
Sans le code intégral, j'peux pas te dire...
Le code integral est trop long, mais voici un extrait assez explicite.
`Etablissement des variables
Dim HoD As Variant 'Hod = Head of Department
Dim i As Long
Dim Rng As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlDown).Row
Set Rng = Range("A10000:A" & Lastrow)
HoD = Rng.Value
`Boucle
For i = LBound(HoD, 1) To UBound(HoD, 1)
Selection.AutoFilter Field:=1, Criteria1:=HoD(i, 1)
Range("A1:A500").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Next i
Sur une feuille, colonne A, premiere ligne cad Ligne 1 = Nom (entete). Ligne 2 = Joel, Ligne 3 = Pikaju, Ligne 4 = Jhon etc jusqu'a la ligne 500.
3 ou 6 ou 2 de ces noms de personnes s'affichent aussi a partir de la ligne 10.000 de la meme colonne A. Ex ; Ligne 10.000 = Pikaju, ligne 10.001 = Cedric, Ligne 10.002 = Eric.
La macro consiste a filtrer la ligne 1 a 500 suivant les noms qui s'affichent a partir de la ligne 10.000. Puis la macro copie le filtre et le colle quelque part.
Lorsque le dernier nom dans la rangee 10.000 soit selectionnee pour filtre, la macro devrait normalement s'arreter cependant ce n'est pas le cas avec l'extrait ci-dessus ; elle continue avec les lignes vides.
Qu'est-ce qui cloche avec mon L&U Bound ?
`Etablissement des variables
Dim HoD As Variant 'Hod = Head of Department
Dim i As Long
Dim Rng As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlDown).Row
Set Rng = Range("A10000:A" & Lastrow)
HoD = Rng.Value
`Boucle
For i = LBound(HoD, 1) To UBound(HoD, 1)
Selection.AutoFilter Field:=1, Criteria1:=HoD(i, 1)
Range("A1:A500").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Next i
Sur une feuille, colonne A, premiere ligne cad Ligne 1 = Nom (entete). Ligne 2 = Joel, Ligne 3 = Pikaju, Ligne 4 = Jhon etc jusqu'a la ligne 500.
3 ou 6 ou 2 de ces noms de personnes s'affichent aussi a partir de la ligne 10.000 de la meme colonne A. Ex ; Ligne 10.000 = Pikaju, ligne 10.001 = Cedric, Ligne 10.002 = Eric.
La macro consiste a filtrer la ligne 1 a 500 suivant les noms qui s'affichent a partir de la ligne 10.000. Puis la macro copie le filtre et le colle quelque part.
Lorsque le dernier nom dans la rangee 10.000 soit selectionnee pour filtre, la macro devrait normalement s'arreter cependant ce n'est pas le cas avec l'extrait ci-dessus ; elle continue avec les lignes vides.
Qu'est-ce qui cloche avec mon L&U Bound ?
Qu'est-ce qui cloche avec mon L&U Bound ? Avec eux? rien ne cloche...
Alors Qu'est-ce qui cloche?
Je sais pas.
A vue de nez?
Ben comme ça, de loin, j'dirais p'tête ben qu'ta colonne C est plus "longue" que ta colonne A...
Essaye comme ceci :
Alors Qu'est-ce qui cloche?
Je sais pas.
A vue de nez?
Ben comme ça, de loin, j'dirais p'tête ben qu'ta colonne C est plus "longue" que ta colonne A...
Lastrow = Cells(Rows.Count, "C").End(xlDown).Row
Set Rng = Range("A10000:A" & Lastrow)
HoD = Rng.Value
Essaye comme ceci :
Dim DernLigne As Long
DernLigne = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A10000:A" & DernLigne)
HoD = Rng.Value