Discussion :

[Aulanh]

Bonjour
J'ai un tableau excel de production journalière pour l'année.
Je cherche à sortir des moyennes par journée de chaque mois.
Je m'explique.
J'ai besoin de la moyenne des lundi de janvier, mardi de janvier ainsi de suite pour tous les mois de l'année.
En 2007 j'avais ça mais je ne retrouve pas comment faire.
Ci-joint le fichier de prod journalière avec en dessous le tableau à remplir, mais les formules ne fonctionnent pas.

Merci pour votre aide.

[Aulanh]

J'avais oublié la pièce jointe

[stanca_old]

salut

sur ton premier test SI(ESTNUM($A$5:$A$433) tu ne dis pas ce que tu fais.

Si ESTNUM($A$5:$A$433) est égal, différent ??

[Aulanh]

Merci Stanca
Justement, je ne sais pas. Je veux faire la recherche sur la colonne A qui est composée de dates, mais aussi des intitulés pour les lignes semaines et mois.
J'ai peut-être besoin de filtrer seulement les lignes avec une date. Mais je ne sais pas si c'est nécessaire si la formule est capable de prendre que les lundis et ne pas tenir compte des totaux semaines et mois.
En résumé, la condition SI ESTNUM(... n'est peut-être pas utile.

[ullan]

Bonjour,

Par formule ce type de calcul est faisable par la fonction SOMMEPROD, mais celle-ci n'aime pas des valeurs textes dans la matrice de calcul.

Modifier A11 par un format personnalisé "Total semaine "0 et y saisir =NO.SEMAINE(A10;2), à copier et coller dans toutes les cellules similaires (A19, A25, ...)
Idem pour A40 : "Total "mmmm et =MOIS(A39), à copier et coller dans toutes les cellules similaires (A74, A111, ...).

La formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((JOURSEM($A$4:A433;2)=JOURSEM(B$443;2))*(MOIS($A$4:A433)=MOIS($A446))*(ANNEE($A$4:A433)=A444)*(F$4:F433))

devrait renvoyer le résultat attendu (non testé).

Ensuite il faudrait adapter cette formule de sorte qu'elle puisse également être copiée vers le droite (vers le bas devrait fonctionner).

Adapte ton fichier et dis-nous si cette première approche te convient.

[rjamin]

Bonjour,

Solution avec SOMMEPROD, obligé d'ajouter deux colonnes pour calculé N° du jour de la semaine Dimanche =1 et du N° du mois les 0 sont à la hauteur des lignes qui ne sont pas des dates.
On peut masquer ces colonnes pour l'impression.

Ma solution arrive après une autre similaire.

Désolé.

[Aulanh]

Bonjour et merci pour votre aide.
Pour la solution d'Ullan je n'ai pas réussi à tester. Le changement de format "total semaine " ne fonctionne pas. J'ai bien mis comme format "Total semaine"0 et NO.semaine(A10;2) mais ça me retourne #NOM? par contre, ça fonctionne pour le TOTAL MOIS.
Pour la solution de rjamin, ça fonctionne. Mais je ne voudrais ajouter des colonnes. En fait, le ficher réel contient 17 feuilles avec une moyenne de 30 colonnes. Ma formule recherchée est à mettre sur toutes les feuilles 2009 et aussi sur le fichier de prod 2010.
Sans autres possibilités, je retiendrai cette méthode mais la solution d'Ullan me semble moins lourde si elle fonctionne.
En tout cas, merci à vous deux.

[rjamin]

Re

J'ai essayé la méthode de ullan elle fonctionne mais...

Le format personnalisé "Total semaine "0 rien à dire

Le format personnalisé "Total "mmmm inscrit Total janvier partout, or je reprend chaque fois =Mois(A39) pour janvier =MOIS(A73) pour février mais il inscrit toujours Total janvier, je ne comprends pas.

Je travaille avec Excel 2007.

[Aulanh]

Je viens d'essayer de nouveau.

Moi je suis sur Excel 2003 et =no.semaine() n'est pas connu. Normalement quand j'ouvre une ( le système me guide pendant la saisie. Là rien.

Pour le mois c'est comme toi, ça fonctionne pour janvier mais j'ai toujours Total Janvier tous les mois peu importe le numéro de la ligne même avec =MOIS(A2456).

Allons nous y arriver ???

Merci

[ullan]

Re,

NO.SEMAINE(A10;2)
Il se pourrait que cette fonction fasse partie des macros complémentaires.
Il faut donc cocher l'utilitaire d'analyse sous les macros complémentaires du menu Outils.

MOIS(A73)
Erreur de ma part, à vouloir bien faire et ne pas avoir vérifié !
Le format est bon, mais dans la cellule il suffit de saisir A73

Si il y a encore des difficultés, je mettrai un exemple en pièce jointe.

[rjamin]

Re

Tout est OK maintenant, ullan à raison il faut activer les macros complémentaires dans 2003.

[ullan]

Bonjour,

Par acquit de conscience, j'ai vérifié cette fois les propositions faites.
Heureusement, car j'ai pu corrigé deux erreurs.

En A40, si par le format personnalisé et la valeur saisie (=A39), l'affichage est conforme à ce qui est demandé, la valeur dans cette cellule est la date de A39, ce qui fausse les résultats dans le tableau récapitulatif inférieur.
Il faut donc modifier l'année de cette date en saisissant =A39-366

Dans le tableau récapitulatif la formule proposée renvoie la somme des valeurs répondant aux critères (année, mois et jour semaine), alors qu'il est demandé d'en faire la moyenne.
C'est aussi corrigé.

D'autre part il faudra peut-être remplacer la fonction NO.SEMAINE, qui renvoie ce numéro suivant la norme US, par une formule qui renvoie ce numéro suivant la norme ISO (européenne).
Pour 2010 pas de problème, mais ce ne sera pas le cas pour les années suivantes.

[Aulanh]

Bonjour et Merci encore.
Je viens de compléter le tableau pour les autres jours de la semaine et ça semble fonctionner.
Dernière étape, je met ça dans mes méga tableaux de prod 2008 mais surtout, je regarde si ça fonctionne sur 2009 et sur mes premières données de 2010.
D'ici 2 jours au max je devrais pourvoir comfirmer que tout est OK.
En tout cas, merci rjamin pour tes compléments d'info. Je garde ton systeme avec les colonnes supplémentaires pour autre chose.
Merci Ullan pour ta solution et le temps que tu as dû y passer.

[Aulanh]

Je vais jamais y arriver.
Ullan, J'ai complété 2008 sur ton fichier et tout fonctionne.
J'ai modifier mon fichier 2009 pour les totaux semaine et mois : pas de problèmes.
J'ai copier le tableau 2008 vers 2009 et modifié ce qu'il y avait pour s'adapter à 2009.
Après 1 heure de vérifications mon fichier 2009 semble complètement identique à 2008.
ET....
Que des #VALEUR!
Je comprends rien (Même chose pour 2010).
J'ai mis dans le .rar ton fichier complété et le 2009 qui ne fonctionne pas
Merci

[rjamin]

Bonsoir,

Dans votre fichier il y a une erreur à Total semaine 17 qui est resté au format texte. Il faut y mettre la même formule NO.SEMAINE que pour les autres semaines.

Vicieux comme erreur, mais la patience est une rare vertu.

[Aulanh]

Merci rjamin
Fallait la trouver celle-là.
Mais..... Je vais m'arracher les cheveux. (il en restes encore)
Pour 2010 ça ne fonctionne pas non plus. J'ai passé une bonne partie de la matinée à chercher. Tilt c'est bête mais j'ai des conditions de remplissage.

Le tableau est alimenté journalièrement. Pour ne pas avoir des 0 partout dans les totaux F et G, si A est vide alors F est vide si B est vide alors G est vide.
Problème, les sommeprod ne sont pas conditionnelles et si une cellule en F ou G est vide (à cause de B ou C non renseigné) ça ne fonctionne pas (#VALEUR!).
S'il n'y a pas de solution, je changerai toutes mes fomules en enlevant les :
=SI(B4="";"";somme(b4;d4)) mais ça risque de me poser d'autres problèmes dans d'autres colonnes avec par exemple des divisions par 0 car j'aurai des 0 dans tous mes totaux.

[rjamin]

Bonjour,

Peut-être pour ne pas avoir des zéros dans les cellules non encore utilisées mettre un MFC qui met couleur de police en blanc si la valeur est = à 0.
Mais il faudra alors se protéger contre les #DIV0

Ce n'est qu'une idée donnée pour essayer.

[Aulanh]

Je viens de modifier toutes les formules, car en fait, si j'ai 0 dans les totaux c'est pas trop grave. Mais ça m'a planté tout le reste. Je me retrouve avec des divisions par zéro ou des -100% partout.
Ces données de prod me servent à calculer des taux journaliers, mensuels et pas mal d'autres taux.
Je fais par exemple des comparaisons par rapport à l'année précédente. J'ajoute aussi x% pour prévoir le mois prochain ou l'année prochaine....
Pour ne pas avoir de problèmes avec des divisions par zéro, ou des -100% je vais devoir changer des milliers de formules par par exemple =si(f4=0;"";(f4-z4)/z4)) pour ne pas avoir -100%

(365 jours par colonne à multiplier par 7 colonnes à multiplier par une dizaine de feuille). Je peux faire ça en bloque par colonne mais c'est du boulot de surtout ne rien oublier.

J'attend encore demain pour voir s'il y a possibilité de mettre une condition pour faire le sommeprod de f4 à f435 que sur la partie non vide (f4<>"").

[rjamin]

Re

Ctrl H => Rechercher Remplacer

Vous avez =SI(F4="";"";calcul) et vous Voulez =SI(F4=0;"";calcul)

Dans la zone Rechercher tapez ="" et dans la zone Remplacer tapez =0

Faites deux ou trois remplacements en cliquant sur le bouton Remplacer pour voir si cela fonctionne et si oui Remplacer tout.

Je sais, ce n'est pas miraculeux mais ça peut aider.

[Aulanh]

Bonjour
Je suis habitué à ce genre de gymnastique comme par exemple créer mes fichiers 2010 à partir des 2009 et j'ai commencé à le faire.
Mais comme des si(xx="";"";calcul) à remplacé il y en a partout, mais je ne dois pas tous les remplacer, je suis obligé de le faire par colonne et feuille.
Mais, je n'ai pas le choix.
Je vais marquer comme "résolu", mais peut-être dans les prochains jours, ouvrir une nouvelle discussion en présentant la formule et en demandant s'il y a moyen de mettre une condition ne non vide.
En tout cas merci beaucoup, tu m'as été d'une aide précieuse.
Eric