Sommeprod, nbval sur plusieurs onglets??? RESOLU

[altinea]

Bonjour, le forum,
Je souhaiterai pouvoir additionner des valeurs qui sont des mots (noms ou autres), ceci dans une feuille synthèse de mon projet.
J'ai un nombre d'onglet correspondant aux nombres de jours du mois traités. Chaque onglet comprends un tableau identique.
Ce tableau a 3 colonnes où sont soit affichés soit en liste déroulante des affectations ou poste de travail,
puis 2 colonnes où via une liste deroulante j'affecte une personne.
Dans ma feuilles synthèse j'ai avec someprod et ou nbval essayé d'additionner tout cela afin d'avoir le nombre de fois qu'une personne a été affectée sur telle ou telle affectation.
Malgré mes divers essais je n'arrive pas à obtenir le résultat escompté pourriez vous m'aider sur cet aspect merci.

J'ai fait quelques annotations sur le feuille nommée synthèse
ci joins une fichier,
Merci de votre aide

 

[ERIC S]

Re : Sommeprod, nbval sur plusieurs onglets???

Re

à tester

=SI(ESTERREUR(EQUIV(B$1;'1'!$B:$B;0));SI(ESTERREUR(EQUIV(B$1;'1'!$F:$F;0));SOMMEPROD(NB.SI(DECALER(INDIRECT(LIGNE(INDIRECT("1:31"))&"!C8");EQUIV(B$1;'1'!$D$9:$D$44;0);0;NB.SI('1'!$D$9:$D$44;B$1));$A2))+SOMMEPROD(NB.SI(DECALER(INDIRECT(LIGNE(INDIRECT("1:31"))&"!H8");EQUIV(B$1;'1'!$D$9:$D$44;0);0;NB.SI('1'!$D$9:$D$44;B$1));$A2));SOMMEPROD(NB.SI(DECALER(INDIRECT(LIGNE(INDIRECT("1:31"))&"!C8");EQUIV(B$1;'1'!$F$9:$F$44;0);0;NB.SI('1'!$F$9:$F$44;B$1));$A2))+SOMMEPROD(NB.SI(DECALER(INDIRECT(LIGNE(INDIRECT("1:31"))&"!H8");EQUIV(B$1;'1'!$F$9:$F$44;0);0;NB.SI('1'!$F$9:$F$44;B$1));$A2)));SOMMEPROD(NB.SI(DECALER(INDIRECT(LIGNE(INDIRECT("1:31"))&"!C8");EQUIV(B$1;'1'!$B$9:$B$44;0);0;NB.SI('1'!$B$9:$B$44;B$1));$A2))+SOMMEPROD(NB.SI(DECALER(INDIRECT(LIGNE(INDIRECT("1:31"))&"!H8");EQUIV(B$1;'1'!$B$9:$B$44;0);0;NB.SI('1'!$B$9:$B$44;B$1));$A2)))

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

Re bonjour ERIC S, concernant ta formule effectivement ca va mieux mais cela ne me donne pas les résultats escomptés, je joins le fichier sur l'onglet synthèse résultat avec formules et ligne inférieure résultat escompté, merci

 

[ERIC S]

Re : Sommeprod, nbval sur plusieurs onglets???

Re

effectivement il y a un pb...que je ne sais pas résoudre pour la colonne D car contrairement aux autres, un nom peut apparaitre plusieurs fois

Avec la structure actuelle du classeur, peut-être Brigitte aura-t'elle une idée ? A ce niveau là je ne rivalise pas avec elle 🙁

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

Ok merci ERIC S, en attendant je vais me contenter de cela, et essayer de farfouiller plus loin, et si Brigitte ou Monique ont une idée et bien j'en serai ravi. Bonne journée

 

[ERIC S]

Re : Sommeprod, nbval sur plusieurs onglets???

Re

sinon, de mon niveau cette fois :

sur 1 feuille puis copie colle sur chaque feuille 1 à 31

en M6 : =Synthèse!A1, tu tires en ligne et en colonne (récupère les titres de ton tableau synthèse)

en N7 :
=SOMMEPROD(($B$9:$B$44=N$6)*($C$9:$C$44=$M7))+SOMMEPROD(($B$9:$B$44=N$6)*($H$9:$H$44=$M7))+ SOMMEPROD(($F$9:$F$44=N$6)*($C$9:$C$44=$M7))+SOMMEPROD(($F$9:$F$44=N$6)*($H$9:$H$44=$M7))+ SOMMEPROD(($D$9:$D$44=N$6)*($C$9:$C$44=$M7))+SOMMEPROD(($D$9:$D$44=N$6)*($H$9:$H$44=$M7))

et tu tires pour remplir le tableau

cela fait ton somme prod par feuille

sur synthèse B2 : =SOMME('1:31'!N7) et tu tires

par contre j'obtiens des différences mais je pense que c'est parce que je n'ai pas compris le fonctionnement de ton tableau, peux-tu expliquer pour la première ligne de 31 :
on rapproche BEAUD de annecy
on rapproche CHAMOT de annecy
on rapproche BEAUD de CABINE
on rapproche BEAUD de TRANSBO ????
on rapproche CHAMOT de TRANSBO
on rapproche CHAMOT de CABINE ?????
[TABLE="width: 776"]
[TR]
[TD]annecy le vieux/6h00/thones/6h20[/TD]
[TD]BEAUD[/TD]
[TD]CABINE[/TD]
[TD] [/TD]
[TD]TRANSBO[/TD]
[TD]CHAMOT[/TD]
[/TR]
[/TABLE]

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

re,
explication sur les affectations

colonne B 1er affectation
colonne c choix de la personne
colonne d choix de la seconde affectation avant 9h
colonne f affectation jusqu'a 13h
choix du nom de la personne

Donc on peux avoir des noms differents sur les lignes et des affectations diverses liées à la plage horaire d'occupation

ta proposition serait de récupérer les infos par un somme pord par feuille, je ne sais pas si c'est un inconvénient mais sur l'onglet modèle j'ai une macro qui me reproduit le nombre d'onglets necessaires en fonction des mois, je vais essayer ta proposition je te tiens au courant, en attendant merci pour toutes tes propositions

 

[ERIC S]

Re : Sommeprod, nbval sur plusieurs onglets???

Re

la formule serait plutôt

=SOMMEPROD(($B$9:$B$44=N$6)*($C$9:$C$44=$M7))+SOMMEPROD(($B$9:$B$44=N$6)*($H$9:$H$44=$M7))+ SOMMEPROD(($F$9:$F$44=N$6)*($H$9:$H$44=$M7))+ SOMMEPROD(($D$9:$D$44=N$6)*($C$9:$C$44=$M7))

ce qui fait qu'on ne compte pas les occurences CF et DH

sinon si tu mets ce tableau sur ta feuille modèle, quand tu la dupliques, il ne doit pas y avoir de pb

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

re ci joins le fichier avec modif telles que proposées. Donc tableau récap par feuille, puis feuille synthèse avec tableau avec la somme de tous les onglets.
J'ai modifié ta formule en fait en faisant un somme prod par type de critères que je voulais, et la ça fonctionne, même avec plusieurs choix identiques.
Par contre c'est vrai que cela alourdi le fichier.
UNe autre idée en partant de la formule qui fonctionne par feuille serait il possible de la travailler de façon à ce que cela fonctionne sur la récap, avec le genre de formule que tu as utilisé plus haut??

je ne peux joindre le fichier il est passée de 500 ko à 2,8 mo

donc la formule qui marche sur chaque feuille est la suivante et correspond à ce que je voudrai mais dna sla feuille synthèse, j'espère que cela ira mieux


=SOMMEPROD(($B$9:$B$44=M$6)*($C$9:$C$44=$L7))+ SOMMEPROD(($D$9:$D$44=M$6)*($C$9:$C$44=$L7))+SOMMEPROD(($F$9:$F$44=M$6)*($G$9:$G$44=$L7))


merci encore

 

[ERIC S]

Re : Sommeprod, nbval sur plusieurs onglets???

Re

une version sans doute plus légère mais toujours avec mes faibles moyens car je ne sais pas gérer les sommeprod sur plusieurs feuilles.......

par contre plus facile à maintenir et adapter je pense que du trop concis

les sommeprod sur synthèse uniquement et simples car font référence à des colonnes intermédiaires sur chaque feuille

pour l'essai colonnes intermédiaires en J,K,L
je fais la concaténation nom & "-" & affectation
le "-" c'est pour limiter les risques en concaténation (es & sai donne essai, mais e & ssai donne aussi essai, donc es & "-" sai donne es-sai)

si tu veux modifier tes conditions, il suffit de revoir les formules en J, K ou L
si tu veux ajouter une condition, tu ajoutes une colonne en M et tu ajoute le sommeprod correspondant dans la synthèse

Par contre je reste preneur d'une explication pas à pas du sommeprod sur plusieurs feuilles, cela ne marche-t'il que parce que les feuilles sont des n°... avec l'évaluation de formule, à un moment on passe par des erreurs alors que la fonction marche (quand on manipule des matrices)

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

bonsoir merci ERIC S j'ai pas eu le temps de regarder tes proposition je regarderai cela ce soir ou demain trop de boulot ces jour, je tge tiens au courant merci ecnore

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

Bonsoir le Forum, le fil
Eric S
J'ai donc utilisé ta dernière proposition, pour faire la somme des onglets.
Laetitia90 m'a aidée sur une demande concernant la macro permettant de choisir le mois et 'lannée pour la creation des feuilles et l’enregistrement du nouveau fichier avec le mois et l'année.
J'ai un big problème c'est que lorsque je lance la creation des onglets ça mouline et ça plante.
Concernant la feuille synthèse j'ai donc repris la formule concernant l'addition des onglet et du fait qu'avant la création les onglets n'existent pas dans les cellules il y a #REF ce qui parait logique.
Je psne qu'il doit y avoir une probleme entre la creation et les formules avec #ref, car quand j'utilise le fichier avec l'un ou l'autre avec les onglet cela fonctionne donc si quelqu'un a une idée,
merci encore

 

[ERIC S]

Re : Sommeprod, nbval sur plusieurs onglets???

Bonjour

il faudrait le fichier avec la macro pour renommer pour que je comprenne

dans les idées brutes de fonderie : faire un fichier avec 31 onglets, et, en fonction du nom que l'on donne au fichier, on masque 29, 30 ou 31 en fonction du mois
de ce fait, comme les feuilles existent tout le temps, pas de #ref

voili voilou ce que je peux te dire pour le moment

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

Bonjour ERIC S, effectivement je pensai avoir posté le fichier avec les infos complètes.
Voici donc le fichier, sur la feuille modèle en cliquant sur la petite image GO il lance l'userform permettant de choisir le mois et l'année. Une fois que l'on valide sur création il devrait créer les feuilles en fonction du mois concerné.
Mais là il rame complètement. Alors que tant que je n'ajoute pas la feuille synthèse avec toutes les formules reproduite sur les cellules concernées la création se fait très rapidement.
UNe fois les feuilles jours créées il renomme le fichier et l'enregistre dans l'emplacement prédéfini.
J'ai essayé par ailleurs de faire en sorte qu'une fois la procédure de création fini, je lance une macro pour reproduire la formule dans la feuille synthèse, mais ce n'est pas mieux.
Voici donc le fichier avec tous les paramètres.
Merci

 

[ERIC S]

Re : Sommeprod, nbval sur plusieurs onglets???

Re

premier point

les sommeprod se calculent à chaque fois, pour l'empêcher pendant la création

Private Sub Création_Click()
Application.ScreenUpdating = 0
Application.Calculation = xlCalculationManual
For i = 1 To nb(DateValue("1/" & C2.ListIndex + 1 & "/" & C1))
Sheets("Modèle").Copy After:=Sheets(i)
ActiveSheet.Name = i
[b2] = DateValue(i & "/" & C2.ListIndex + 1 & "/" & C1)
ActiveSheet.Shapes("Logo_Code").Delete
Next i
Application.Calculation = xlCalculationManual
'Chemin = "C:\Users\Laïla\Desktop\"
'NomFichier = C2.Value & " " & C1.Value & ".xlsm"
'ThisWorkbook.SaveAs Chemin & NomFichier, FileFormat:= _
'xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Unload Me
End Sub

j'ai mis en commentaire l'enregistrement, pour mes essais

 

[altinea]

Re : Sommeprod, nbval sur plusieurs onglets???

re,
effectivement sur ce point c'est super, par contre lors de la création des onglets, la feuille synthèse reste désespérément avec ses #ref, même si je modifie les paramètres des onglets jours. Autre remarque la formule '= b9 & "-" & c9 à priori ne fonctionne pas, j'ai essayé de changer les paramètres sur la feuille modèle et la concaténation ne se fait pas?
à suivre, merci