[Résolu] Affichage jour ouvré et jour férié

[David2Coree]

Bonjour à toutes et à tous !

Je créé cette nouvelle discussion car je n'arrive pas à trouver une solution à mon fichier.
En effet, je veux créer un fichier où des personnes remplissent des statistiques.
Dans ce fichier, il y a un onglet par trimestre dans lequel je fais un tableau par semaine ouvrable et enfin par jour ouvrable il y a 2 colonnes Entrée et Sortie. Je présente mes excuses à ceux qui sont déjà perdu dans cette phrase d'introduction
Dans l'onglet "1er Trimestre", en B3, l'agent va sélectionner son nom dans la liste déroulante et automatiquement sur la ligne 3 va s'afficher le numéro de la semaine et en ligne 8 la date des jours ouvrables car les formules font appel à des références dans l'onglet "Liste". Dans cette onglet, en C2, je modifie uniquement l'année.
Mon premier problème est que j'aimerai que lorsque la date est un jour férié, la cellule affiche "Férié" afin de passer à la cellule suivante car avec la formule actuelle, j'ai le premier jour ouvrable qui suit.
Par l'exemple en semaine 22, le 02/06 était férié donc mon tableau devrait avoir une colonne vide mais au lieu de cela le 03/06 s'affiche et le 06/06 (qui fait partie de la semaine 22) vient derrière. Donc j'ai "triché" pour que le 06/06 s'affiche sur le bon tableau de la semaine ce qui va engendrer mon deuxième problème.
Peut-on améliorer cette formule afin la référence du mois change automatiquement et que les jours ouvrables se place correctement dans le tableau de la semaine car actuellement je fais la modification en manuel.
Je ne sais pas si j'ai été assez clair dans mes explications, et je vous encore de m'excuser si ce n'est pas le cas.
Merci à tous ceux qui pourront m'aider.

David

 

[Tibo]

Re : Affichage jour ouvré et jour férié

Bonjour,

Je propose de passer par un SOMME(SI( matriciel, car pas sûr qu'on puisse y parvenir par un SOMMEPROD classique.

en D10 :

=SOMME(SI(ESTNUM('1er Trimestre'!$D$8:$EN$8);SI(MOIS('1er Trimestre'!$D$8:$EN$8)=1;'1er Trimestre'!$D10:$EN10)))

Formule matricielle à valider par CTRL + MAJ +ENTREE

EN E10, sa petite soeur :

=SOMME(SI(ESTNUM('1er Trimestre'!$D$8:$EN$8);SI(MOIS('1er Trimestre'!$D$8:$EN$8)=1;'1er Trimestre'!$E10:$EO10)))

Toujours matricielle

Pour avril, en J10 :

=SOMME(SI(ESTNUM('1er Trimestre'!$D$8:$EN$8);SI(MOIS('1er Trimestre'!$D$8:$EN$8)=4;'1er Trimestre'!$D10:$EN10)))+SOMME(SI(ESTNUM('2nd Trimestre'!$D$8:$EN$8);SI(MOIS('2nd Trimestre'!$D$8:$EN$8)=4;'2nd Trimestre'!$D10:$EN10)))

en K10, sa petite soeur :

=SOMME(SI(ESTNUM('1er Trimestre'!$D$8:$EN$8);SI(MOIS('1er Trimestre'!$D$8:$EN$8)=4;'1er Trimestre'!$E10:$EO10)))+SOMME(SI(ESTNUM('2nd Trimestre'!$D$8:$EN$8);SI(MOIS('2nd Trimestre'!$D$8:$EN$8)=4;'2nd Trimestre'!$E10:$EO10)))

Toutes matricielles, donc validation par CTRL + MAJ + ENTREE

Je te laisse adapter pour les autres mois

Bon app

@+

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Bonjour Tibo

Tout d'abord, j'aimerai sincèrement te remercier car ton aide est très précieuse.

1/ Les formules matricielles fonctionnent correctement sur la première partie du tableau mais dans la seconde, elles ne sont pas valides dans les cellules fusionnées.

2/ Cet après-midi en faisant des tests pour les années futures, je me suis aperçu que la formule en D8 n'est pas correct car quelque soit le jour de la semaine, il se placera en cette cellule alors que le 02/01/2013 tombera un mardi (logiquement il devra être en F8) et en 2014 en H4. Pourrais-tu m'aider à insérer cette condition ?

Je te remercie et je remercie aussi à ceux qui aurait une solution à ces 2 situations.

David

 

[JCGL]

Re : Affichage jour ouvré et jour férié

Bonjour à tous,

Ton année en D2 de la feuille 'Liste' et en C2 de la même feuille et vers le bas sur 12 lignes :

=DATE($D$2;LIGNE()-1;1)+MOD(1-JOURSEM(DATE($D$2;LIGNE()-1;1);2);7)-7

Tu auras tous les lundis.

A + à tous

Édition : Salut Tibo

 

[Tibo]

Re : Affichage jour ouvré et jour férié

re, salut Jean-Claude ,

1/ Les formules matricielles fonctionnent correctement sur la première partie du tableau mais dans la seconde, elles ne sont pas valides dans les cellules fusionnées.

Les cellules fusionnées sont souvent source de problème. C'est le cas avec les formules matricielles.

Dans ce cas, si on ne peut se passer des cellules fusionnées, la solution consiste à défusionner, à mettre la formule à valider matriciellement et à refusionner ensuite. Un peu de boulot certes, mais à faire qu'une seule fois.

@+

 

[Monique]

Re : Affichage jour ouvré et jour férié

Bonjour,

Au lieu de mettre « Férié », on peut mettre 1
avec un format nombre personnalisé [=1]"Férié";jj/mm/aa
Ce qui fait qu’il n’y a plus de texte parmi les dates
A savoir : MOIS(1)= janvier. Les formules en tiennent compte.

Les n° de semaine

=SI(SOMME(O8:W8)<5;"";(ENT(MOD(ENT((MAX(O8:W8)-2)/7)+0,6;52+5/28))+1))

Il n’y a pas 5 fériés à se suivre, il y aura au moins 3, 4 ou 5 vraies dates et le n° de semaine se calculera selon la plus « grande » de ces dates.
Et s’il n’y a pas de dates (si B3 est vide), la cellule du n° de semaine reste vide.
(J’insiste à peine)

Lignes 10 à 34 et 36
Format nombre personnalisé Standard" ";;
Si la cellule contient 0, elle parait vide
et tu n’as plus besoin des formules conditionnelles pour éviter les 0 ; une simple Somme() suffit

En feuille Recap :
Le nom des mois (ligne 8) avec un accent (février, août, décembre)
Les entrées :

=SOMMEPROD((TEXTE('1er Trimestre'!$D$8:$EN$8;"mmmm")=D$8)*('1er Trimestre'!$D$8:$EN$8>1);('1er Trimestre'!$D10:$EN10))+SOMMEPROD((TEXTE('2nd Trimestre'!$D$8:$EN$8;"mmmm")=D$8)*('2nd Trimestre'!$D$8:$EN$8>1);('2nd Trimestre'!$D10:$EN10))

Les sorties :

=SOMMEPROD((TEXTE('1er Trimestre'!$D$8:$EN$8;"mmmm")=D$8)*('1er Trimestre'!$D$8:$EN$8>1);('1er Trimestre'!$E10:$EO10))+SOMMEPROD((TEXTE('2nd Trimestre'!$D$8:$EN$8;"mmmm")=D$8)*('2nd Trimestre'!$D$8:$EN$8>1);('2nd Trimestre'!$E10:$EO10))

J’ai mis une couleur sur les lignes de totaux (plus facile à lire)

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Waouh ! Franchement je suis trop gâté ! 1 MEGA Barbatruc et 2 Barbatruc qui me viennent en aide, je ne pourrai espérer mieux.
Je vais mettre en application vos conseils et je vous tiendrai au courant de l'évolution de mon projet. Mais en tout cas, mille merci à vous 3 !!!
고맙습니다 ! ==> Ko Map Seum Ni Da ! ==> Merci honorifique !

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Au fait, Madame et Messieurs les experts.
Petite question de débutant ... ^^;;
Comment faites vous pour insérer ces bulles ou ces encadrés ?

 

[JCGL]

Re : Affichage jour ouvré et jour férié

Bonjour à tous,

Passe en Mode Avancé et tu auras une barre d’icônes plus importante en haut de tes messages

• 
• Polices
• Tailles
• 
  
  
  
• 
• 
• 
  

• 
  
  
• 
  
  
• 
  
  
  
• 
  
  
  
  
• 
• 
  
  

Voir ICI les exemples de balises BB

A + à tous

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Bonjour Tibo et bonjour tout le monde,

Dans ce cas, si on ne peut se passer des cellules fusionnées, la solution consiste à défusionner, à mettre la formule à valider matriciellement et à refusionner ensuite. Un peu de boulot certes, mais à faire qu'une seule fois.

J'ai suivi ton conseil qui consistait à défusionner, saisir la formule matricielle et la valider et enfin fusionner les cellules à nouveau.
C'est un véritable succès !
Merci Tibo !

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Bonjour Dame Monique,

Lignes 10 à 34 et 36
Format nombre personnalisé Standard" ";;
Si la cellule contient 0, elle parait vide
et tu n’as plus besoin des formules conditionnelles pour éviter les 0 ; une simple Somme() suffit

Cette mise en forme des cellules est tout simplement FORMIDABLE !!!
Merci Monique.

David

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Bonjour à toutes et à tous,

Voici en pièce jointe les différents fichiers que j'ai fait grâce à cette discussion.
1/ Le fichier "forum_agent1.xls" ou "forum_agent2.xls" sont les fichiers qu'un agent devra remplir chaque jour les différentes tâches qu'il aura effectué durant la journée. Ce fichier se compose de 4 onglets (1er Trimestre / 2nd Trimestre / Recap / Liste). Pour le second fichier l'agent fera des tâches particulière.

1. L'agent devra sélectionner son nom B3 afin que les N° de semaine et les dates des jours ouvrable s'affichent.
2. En A10 il devra sélectionner le secteur de travail
3. En D35, il devra choisir dans la liste déroulante OUI ou NON
4. Dans l'onglet, tout sera calculé automatiquement.

2/ Dans le fichier "forum_tab_synth.xls" :
Il s'agit la somme des valeurs de l'onglet "Recap" des agents.

J'ai passé tout l'après-midi sur le forum à essayer de trouver je que je voudrais.
J'aimerai trouver une formule qui consiste à faire par exemple la somme de la cellule 'Recap'!D10 de tous les agents et ainsi de suite sans pour autant que les fichiers soient ouverts et sans passer par VB car je ne suis pas du tout à laisse avec VB.
En tout cas, je vous remercie pour votre aide.
Et je vous souhaite à tous un bon week-end !

David

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Bonjour à tout le forum !

J'aimerai avoir votre avis mesdames et Messieurs les experts.

1/ Est-il possible que pour mon tableau "forum_tab_synth.xls", le message box (voir PJ) ne s'affiche pas et qu'automatiquement il fasse la mise à jour des données ?

2/ Ma seconde question est tout mes fichiers excel se trouve en réseau et avec la même structure de fichier :
\\Lettre lecteur réseau \Nom Entreprise\Nom Service\Statistiques et TBB\service\2011\TBB 2011\1°SEMESTRE 2011\Nom dossier secteur 1 \SEMESTRE_01_2011_nom_agent.xls

Le chemin d'accès réseau est très très très long, vous voyez ?

Et la formule que je mets en place est :

=SOMME('Lettre lecteur réseau \Nom Entreprise\Nom Service\Statistiques et TBB\service\2011\TBB 2011\1°SEMESTRE 2011\Nom dossier secteur 1 \SEMESTRE_01_2011_Nomagent1.xls]Recap'!$D$12;'Lettre lecteur réseau \Nom Entreprise\Nom Service\Statistiques et TBB\service\2011\TBB 2011\1°SEMESTRE 2011\Nom dossier secteur 1 \SEMESTRE_01_2011_Nomagent2.xls]Recap'!$D$12;'Lettre lecteur réseau \Nom Entreprise\Nom Service\Statistiques et TBB\service\2011\TBB 2011\1°SEMESTRE 2011\Nom dossier secteur 1 \SEMESTRE_01_2011_Nomagent3.xls]Recap'!$D$12'...'Lettre lecteur réseau \Nom Entreprise\Nom Service\Statistiques et TBB\service\2011\TBB 2011\1°SEMESTRE 2011\Nom dossier secteur 1 \SEMESTRE_01_2011_Nomagent10.xls]Recap'!$D$12)

N'est-il pas possible de simplifier cette formule ? C'est-à-dire que je saisisse une seule fois le chemin d'accès des fichiers et je saisisse uniquement les nom des fichiers et de la cellule à additionner.

Merci d'avance.
David

 

[JCGL]

Re : Affichage jour ouvré et jour férié

Bonjour à tous,

Nomme une partie du chemin par Insertion / Nom Définir : Chemin_1S

Lettre lecteur réseau \Nom Entreprise\Nom Service\Statistiques et TBB\service\2011\TBB 2011\1°SEMESTRE 2011\Nom dossier secteur 1 \SEMESTRE_01_2011

Ensuite, tu l'utilises :

=SOMME('Chemin_1S_Nomagent1.xls]Recap'!$D$12;'Chemin_1S_Nomagent2.xls]Recap'!$D$12;'Chemin_1S_Nomagent3.xls]Recap'!$D$12'...'Chemin_1S_Nomagent10.xls]Recap'!$D$12)

A + à tous

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Nomme une partie du chemin par Insertion / Nom Définir : Chemin_1S

Bonjour JCGL,

J'ai essayé de définir un nom à mon chemin réseau comme tu me l'indiques dans ton message (PJ def_nom_rzo_00.jpeg) mais dès que je le mets en place dans la formule, j'ai le message d'erreur que la formule est incorrect (PJ def_nom_rzo_01.jpeg).
Donc je modifie le chemin jusqu'au dernier dossier parent avant les fichiers sources (PJ def_nom_rzo_00a.jpeg) et là après validation de la formule, une boîte de dialogue apparaît afin de repointer tous les fichiers excels un par un et après j'ai le bon résultat.
Mais lorsque, je ferme le fichier et je le rouvre, après la demande de la mise à jour des liaisons, j'ai le message d'erreur que des liaisons ne sont pas correct (PJ def_nom_rzo_03.jpeg). Et si je fais la modification des liaisons en repointant à nouveau les fichiers sources, EXCEL me répond que les sources sont invalides.
Ai-je fais un mauvais nommage de chemin réseau ?
Merci pour ton aide.

Cordialement
David

 

[David2Coree]

Re : Affichage jour ouvré et jour férié

Bonjour à tout le forum,

Un petit up pour que vous ne m'oubliez pas ;-)

cordialement