Calcul somme excel sur toutes feuilles Fermé

Question

Bonjour,


J'ai créé un fichier excel afin de recenser l'ensemble des dossiers entrant dans mon service.
Ce dossier est divisé à la semaine , j'ai donc 52 pages identiques.
Par contre, je dois délivrer des statistisques mensuels et j'aimerais donc avoir une formule, dans une autre feuille, qui me permette de compter les nombre de fois ou le mois de janvier , février , mars , etc etc... est saisi, mais surtout en recherchant sur toutes les feuilles du classeur.

Actuellement, j'ai cette formule : =SOMMEPROD((MOIS(E1:E256)=12)*1)


Elle me donne le résultat pour le mois correspondant au numéro ( ici décembre) mais que sur la page ou figure mon tableau statistique. Je n'arrive pas à le faire étendre aux pages appelé Semaine1, Semaine2....jusqu'à Semaine52.

J'espère que vous avez compris et que vous pourrez m'aider car je débute avec excel et je n'ai pas toujours les expressions convenables des utilisateurs habituels.

J'ai essayé ça mais sans résultat : =SOMMEPROD((MOIS((Semaine1:Semaine52!E1:E256)=1)*1))

Merci d'avance

gbinforme · Answer

bonjour

Tu te dis débutant mais tu as bien tenté le plus logique.

Cependant, la formule 3D de cumuls ne fonctionne qu'avec des cumuls et ne permet pas de sélection.

En effet les cumuls avec condition sur des matrices fonctionnent en 2 phases, avec création d'une matrice sélectionnée intermédiaire et virtuelle et ils n'autorisent pas autant de matrices virtuelles que d'onglets utilisés dans la formule.

Pour faire tes cumuls, tu devrais essayer avec des tableaux croisés dynamiques qui sont tout à fait adaptés à ton cas et peuvent être utilisés en multi-feuilles.

Pour les utiliser, c'est assez facile et assez bien expliqué dans le lien ci-dessus, une fois la première approche comprise car comme ils permettent à peu près toutes les variations de schémas possibles c'est tout de même un autre monde.

lermite222 · Answer

Bonjour,
La formule pouvant faire la statistique sur plusieur page est possible avec par exemple
=SOMMEPROD(((MOIS(Feuil1!A11:A12)=12)+(MOIS('Feuil2'!A11:A12)=12))*1)
Evidemment j'ai mis des adresses différentes pour pouvoir tester.
Mais le nombre de test possible (avec Excell 2000/2003) est limité à 30 donc pas possible dans ton cas.
Une solution serrais, dans ta feuille statistique faire 52 formules et un totaliseur (Somme) sur les 52 formules

par exemple
D1 .. =SOMMEPROD((Feuil1!MOIS(E1:E256)=E1)*1) 
D2 .. =SOMMEPROD((Feuil2!MOIS(E1:E256)=E1)*1) 
D3 .. =SOMMEPROD((Feuil3!MOIS(E1:E256)=E1)*1) 

......
D53 ..=somme (D1:D52)

et en E1, par exemple 12

A+

Pho3nix_23 · Answer

Merci à vous , 

Lermitte222, je n'arrive pas à utiliser tes formules et à les appliquer sur mes 2 premières feuilles pour essayer.

=SOMMEPROD(('Semaine1'!MOIS(E1:E256)=1)*1) 


En attendant, je vais regarder les tableaux croisés dynamiques conseillés par Gbinforme, mais la je crois que je franchi une marche un peu trop haute pour moi, pour le moment en tout cas .

Si vous avez autre chose, je suis preneur .

Merci encore

lermite222 · Answer

Ce n'est pas le N0M que tu a donner à la feuille mais sont ""nom"" dans Excel, pour voir ce nom va dans l'IDE (Alt+F11) sur la gauche tu a l'explorateur du projet avec les feuille, tu va voir sur chaque feuille il y à 2 noms, par exemple Semaine1:Feuil3 c'est le 2ém nom qu tu doit employé.
Avantage, même si tu modifie le 1er nom (semaineX) sont 2ém nom change jamais.
EDIT: et y faut pas de guillemet

gbinforme · Answer

bonjour

mais la je crois que je franchi une marche un peu trop haute pour moi,


Pas du tout, tu verras qu'en l'essayant tu vas l'adopter !

C'est un peu dur au début, je le concède volontiers, mais une fois le premier pas franchi tu verras que c'est tout à fait adapté à ton utilisation avec des mises à jour faciles, des modifications à l'infini, des regroupements à la demande, l'affichage des détails éventuels, etc.

Commence avec le mode d'emploi que je t'ai donné sur des exemples simples avant de vouloir créer un TCD sur 52 feuilles ce qui un peu plus ardu pour un début mais ensuite tu verras que c'est pas si compliqué et très efficace.


Les formules de lermite222, que je salue, sont très judicieuses mais tes totalisations seront toujours à faire manuellement avec 53 formules pour chaque élément alors qu'avec un TCD tu additionnes à la souris ce que tu veux comme tu veux.

Bon courage et dans quelques semaines, tu regretteras de n'avoir pas connu plus tôt.

Pho3nix_23 · Answer

Merci pour ta réponse rapide,


Il y a tout de même des choses que je ne comprends pas. Pour le nom ok j'ai compris .
Par contre aucune des formules que j'essaie ne marche quand j'essaie de renvoyer sur une autre feuille.

=SOMMEPROD((Feuil2!MOIS(E1:E256)=1)*1) 

Semaine1 est la feuil2 et Semaine2 est la feuil3.

Excel me dit qu'il y a une erreur.
En plus, je ne comprends pas pourquoi en enlevant le renvoi vers une autre feuil tout marche, sauf pour 1 (janvier) car dans ce cas il me donne des résultats surprenants ( 251 au lieu de 3).

Peut-être que mon départ de formule est complètement faux ? Il y a peut-être mieux pour rechercher une somme de date saisie?

Merci encore.

lermite222 · Answer

J'ai portant tester les formules aussi bien avec 1 seul critère que celle avec les 2 critères et le résultat était bon.
Ahhhhh, excuse, j'ai fait une erreur de transcription dans l'exemple avec plusieur ligne...
=SOMMEPROD((Feuil2!MOIS(E1:E256)=1)*1) = PAS BON

=SOMMEPROD((MOIS(Feuil2!E1:E256)=1)*1)  = Y A BON

Pho3nix_23 · Answer

Merci à tous les deux!


ça ne marche toujours pas mais je suis persévérant. 
Quand j'essaie ta formule lermitte (la bonne) , mon excel me demande de mettre à jour les valeur de la feuil2 et m'ouvre l'explorateur windows.
Je choisi mon classeur et il transforme la formule sans me donner le résultat mais en precisant une erreur de valeur ??? je ne comprends pas pourquoi ?

=SOMMEPROD((MOIS([Feuil2]Semaine1!E1:E256)=1)*1) voici la formule une fois que je choisi mon classeur pour mettre à jour.

Pourquoi je dois mettre à jour alors que nous avons convenu tout à l'heure que le nom de feuil ne changé pas?

Pour le TCD , je travaille dessus et je suis sur que c'est un outil très utile, mais, pour le moment,  je comprends mieux ou lermitte veut en venir avec ces calcul , mais je ne parviens pas à les appliquer.

Merci encore

lermite222 · Answer

Huuuum,Huuuum.... cette fois c'est toi  :-D
=SOMMEPROD((MOIS([Feuil2]Semaine1!E1:E256)=1)*1) ???
pourquoi tu modifie !!!

Ne la tape pas, fait un copier/coller sur la cellule D1
=SOMMEPROD((MOIS(Feuil2!$E$1:$E$32)=$E$1)*1)
Tu peu ensuite faire copier... Tirer Jusque ligne 52 et coller, il faudra juste changer les nom feuil12 par Feui3,Feuil4 etc..
les adresse avec $ c'est pour ne pas qu'elle soient relative (qu'elle restent fixes).

Mettre dans cellule E1 = 1
et quand tu change la cellule E1 ton résultat est automatiquement mis à jour.
et ce serrait plus facile et plus lisible si tu met la formule SOMME dans E2
=SOMME(D1:D52)

Fait comme ca, j'ai tout tester.
Un petit bug de Excell avec comparer à 1 prend en compte le cellule vide !!

gbinforme · Answer

bonsoir Pho3nix,

En réfléchissant à ton problème, j'ai trouvé une formule qui fonctionne sur les 52 semaines mais ne sais pas trouver le mois.

Il faudrait créer une colonne sur chaque onglet semaine (possible en sélectionnant simultanément les 52 feuilles) avec l'extraction du mois : =MOIS(E1)

En supposant, que :
- c'est la colonne suivante (F) qui a le mois ($F$1:$F$256)
- que tes feuilles se nomment "semaine1" à "semaine52" sans trous
- que le mois choisi est en E1
avec la formule suivante tu totalises toutes tes feuilles 
=SOMMEPROD(NB.SI(INDIRECT("semaine"&LIGNE(INDIRECT("1:52"))& "!$F$1:$F$256");E1)*1)
avec  LIGNE(INDIRECT("1:52")) on crée un tableau d'entiers consécutifs 1 à 52
qui sont concaténés avec "semaine" et "!$F$1:$F$256" 
pour créer  "semaine1!$F$1:$F$256" à "semaine52!$F$1:$F$256" 
et donner le tableau de références de plage
où NB.SI(...;E1) compte dans chacune de ces plages le nombre de celles contenant le mois
et SOMMEPROD(...)*1) en fait la somme des nombres trouvés. 

En remplaçant E1 par le mois et en créant 12 formules, tu as tous tes mois en permanence.

lermite222 · Answer

Puisqu'il y a une erreur et qu'en plus tu trouve que ca fait beaucoup de formule allons-y pour du VBA.
1°) tu met un bouton sur la feuille Statistique, en mode création tu double clic dessus, tu va voir 2 lignes de code..
Private Sub CommandButton1_Click()

End Sub
Entre ces deux ligne tu ajoute..Call TestSemaine  pour avoir
Private Sub CommandButton1_Click()
    Call TestSemaine
End Sub
Comme tu est dans l'IDE, tu ajoute un module
>> Insertion >> Module
Dans la feuille qui vient de s'ouvrir tu colle ...
Sub TestSemaine()
Dim MoisChercher As Integer
Dim i As Integer, e As Integer, g As Integer
Dim Nom As String, Total As Long
    'Adapter ce nom au nom de la feuille
    Sheets("Statistique").Select
    'mettre le mois a chercher dans H1
    MoisChercher = Cells(1, 8).Value
    'Il faut qu'il y ai des feuille nommée Semaine1 à Semaine 52
    On Error GoTo Sortie
    For i = 1 To 52
        Nom = "Semaine" & i
        For g = 1 To Workbooks.Count
            If Sheets(g).Name = Nom Then
                Sheets(g).Select
                Exit For
            End If
        Next g
        For e = 1 To Range("E1").SpecialCells(xlCellTypeLastCell).Row
            If Month(Cells(e, 5)) = MoisChercher Then
                Total = Total + 1
            End If
        Next e
    Next i
Sortie1:
    Sheets("Statistique").Select
    Cells(1, 5) = Total 'dans cellule E1
Exit Sub
'-----------------------------------------------------------
Sortie:
    MsgBox "La feuille " & Nom & " est introuvable"
    Resume Sortie1
End Sub
Oublie pas de sortir du mode création.
Dans la cellule H1 tu met le mois que tu veux chercher et clic bouton
Ca fonctiionne à tout les coups, même avec 1
A+

Calcul somme excel sur toutes feuilles

11 réponses

Discussions similaires