Discussion :

[cag's]

Bonjour à tous

Je vous sollicite car je bloque depuis 2 jours sur mon fichier EXCEL... Je vous explique la situation:
J'ai un fichier de données (voir le fichier ci-joint) contenant des informations sur le transport de produits de santé. Les champs sont :
-NUM_DOSSIER
-DATE
-HEURE_APPEL
-HEURE_CHARGEMENT
-DEGRE_URGENCE
-TYPE_PRODUIT
-SEM_WEEK qui permet de savoir si le transport s'est effectué un jour de semaine ou de weekend(y compris jours fériés)
-JOUR_NUIT (entre 6h et 21h on considère que c'est la journée et le reste la nuit)

Bon jusque là tout va bien...
Maintenant je crée un tableau croisé dynamique (voir fichier) afin de compter le nombre de produits transportés par mois. Cependant, il m'est demandé d'afficher la quantité de produit par mois mais également la moyenne quotidienne de produits transportés et ceci par semaine et weekend.

Par exemple:
Le mois de février 2015 comprends 28 jours.Grâce à la fonction NB.JOURS.OUVRES je récupère le nombre de jours appartenant à la semaine. Ici je récupère 20 jours, donc pour calculer le nombre de jours correspondant au weekend, il ne reste plus qu'à soustraire 20 de 28 soit 8 jours correspondant à un weekend.
Une fois cette étape de faite, je souhaite diviser la quantité trouvée par semaine par 20 (dans le TCD pour février on ferait 183/20 pour la colonne jour et 125/20 pour la colonne nuit) puis de même pour le weekend, il faut diviser la quantité trouvée par weekend par 8(dans le TCD pour février on ferait 123/8 pour la colonne jour et 72/8 pour la colonne nuit)

Enfin voilà dans l'idée ce que je souhaite faire Maintenant je voudrais intégrer ce principe au tableau croisé dynamique pour que tout soit automatique (et ainsi pouvoir jouer avec les segment comme je veux ) mais je ne vois pas du tout comment!!

J'ai groupé le champ date par mois. Je pensais intégrer un champs calculé et faire une formule qui compte le nombre de jours ouvrés en prenant le min de la date du mois considéré et le max...mais en essayant j'ai vite compris que ça ne marchait pas

Enfin bon je dois présenter ça pour demain et c'est pas gagné^^

J'espère que j'ai été assez clair, et merci à celui ou celle qui aura le courage de me lire

[78chris]

Bonjour

Effectivement le champ calculé ne peut pas marcher.

Il faut soit faire un calcul en amont dans les données, soit faire un tableau en aval qui utilisera le contenu du TCD avec la fonction LIREDONNEESTABCROISDYNAMIQUE ( à mon avis plus facilement gérable que les formules amont dans ce cas)

Dans les 2 cas il te faut créer un tableau de jours fériés et ne pas utiliser tes formules actuelles trop lourdes et non évolutives. Il y a plein d'exemples sur les forums pour créer ce type de tableau par formule sur 2 ou 3 ans.

Le souci sera de bien pondérer les moyennes générales et ne surtout pas faire de moyenne de moyennes.

Sinon c'est également faisable avec PowerPivot sur 2010 et certaines versions de 2013.

[cag's]

Bonjour Chris,

Merci beaucoup pour votre aide. Suite à vos conseils j'ai pu améliorer mon fichier (voir fichier TCD_HELP(2).xlsx ci-joint). Cependant, je rencontre encore un souci...ce ne serait pas drôle sinon^^
En fait je veux faire des stats sur une année sachant que l'année considérée commence en septembre et se termine en août de l'année suivante (par exemple sept 2014 jusqu'à août 2015). On est donc dans le cas où deux années se chevauchent et les jours fériés varient... Donc au lieu de tout mettre dans ma première feuille "Données", j'ai décomposé par années N-1 et N puis par mois (voir fichier test.xlsx). Dans ce fichier j'ai calculé les jours fériés pour l'année N et N-1, l'utilisateur n'a donc plus qu'à saisir les années qu'il souhaite dans la case prévu pour (feuille Jours_Fériés).

Maintenant j'ai repris le même principe que pour le fichier TDC_HELP(2) avec un tableau de jours ouvrés, weekend et jours fériés et nombre de jours dans le mois. Cependant je voudrais que tout se calcule automatiquement en fonction de l'année passée par l'utilisateur. La fonction NB.JOURS.OUVRES requiert nécessairement une date de début de mois et une date de fin (ex: le 01/01/2015 et 31/01/2015). J'ai vu que les gens faisaient un tableau avec les dates de début et de fin pour chaque mois (ce que j'ai fait pour le moment), cependant je ne veux pas que l'année soit fixe puisque c'est l'utilisateur qui va la faire varier en fonction du paramètre passé dans la cellule de la feuille "Jours_fériées" du fichier test.xlsx ... Auriez-vous une idée de comment faire ? J'espère que j'ai été assez précise, sinon je pense que le fichier sera beaucoup plus clair que mes explications pour le coup

[78chris]

Bonjour

Comme je le disais, rien n'empêche de créer un tableau de Jours fériés sur 2 ans ou plus.

Il est parfaitement inutile de nommer chaque jour férié : un simple NB.SI(tableau des JF;[@DATE])>0 permet de savoir si une date est ou non fériée et JOURSEM([@DATE];2)>5 si c'est un week end : de quoi raccourcir ta formule qui devient

Code :

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

=SI(OU(NB.SI(JF;[@DATE])>0;JOURSEM([@DATE];2)>5);"WEEKEND";"SEMAINE")

une fois le tableau des jours fériés sur 2 ans nommé JF

J'ai modifié tes formules de jours fériés pour tout ce qui est liè à Pâques : inutile de compliquer.

Pour le nombre de jour ouvrables on peut aussi facilement les déterminer en utilisant des dates et non des textes en référence : voir toutes les formules dans l'onglet Calcul2.

On peut également dynamiser la formule LIREDONNEESTABCROISDYNAMIQUE (voir formules).

Le changement d'année en haut des jours fériés changera toutes les dates de l'onglet Calcul2. Le Graphique affiche les 12 mois et se remplira au fil du temps.

Je te laisse adapter Calcul1...