Trouve pas la bonne formule

Question

Je souhaite faire un planning de mon personnel avec gestion des absences et la je bloque. Dans mon planning je voudrais pouvoir afficher des mots tels CP; RTT; FO........ lorsque je rempli les infos en feuil 4 puis je mettrai Une mise en forme conditionnelle pour me permettre de différencier sur le planning les raisons d'absences. J'ai essayer une formule qui ressembler à cela ou la j'arrive a marquer CP quelques soit la case remplie en feuil 4 (logique) mais je n'arrive pas à trouver une formules pour marquer au choix selon les critères =Si(ou(et(B$10&gt;=Feuil4!$B3;B$10&lt;=Feuil4!$C3);et(B$10&gt;=Feuil4!$D3........);"CP";"")) Pourriez vous m'aider Merci d avance...

MFerrand · Answer

Tes numéros de semaine sont faux. Je conseillerais cette formule pour les calculer : =ENT((B7-RECHERCHE(B7;DATE(ANNEE(B7)+{-1;0;1};1;3)-JOURSEM(DATE(ANNEE(B7)+{-1;0;1};1;3))+2))/7)+1 (à défaut d'une fonction personnalisée pour le calculer) Je ne vois pas la possibilités de faire une formule de taille raisonnable, si on doit affecter diverses mentions avec la même formule. Aussi j'aurais tendance à préconiser une fonction personnalisée. Il serait bon que tu précises comment doivent être traitées les mentions J M A N et que tu mettes quelques valeurs pour l'on puisse travailler autrement que sur des cellules vides et sur des données typées. Cordialement...

MFerrand · Answer

OK ! Comme tu prévois plusieurs mentions possibles et que la cellule du jour ne contiendra qu'une formule, il faut que cette formule puisse fournir la mention adéquate.....

MFerrand · Answer

Ca a bien changé ! Formule en Récap, à mettre en C5 : =SOMMEPROD(--(DECALER(Planning!$H$7:$NH$7;EQUIV($B5;Planning!$A$8:$A$35;0);)=C$4)) Elle se recopie sur toute la liste et sur les 4 colonnes. Mais elle te donne le résultat annuel. Pour l'avoir par semaine il faut y ajouter une condition, donc savoir où tu mets ça : la condition est que la plage H7:NH7 ait le numéro de semaine cherché donc pouvoir indiquer sa position dans la Récap... Cordialement...

MFerrand · Answer

Je ne sais pas où tu en es. Ton dernier modèle comportait une feuille Récap ébauchée. Je t'ai fourni une formule donnant la récapitulation annuelle. Avec une condition limitant le résultat renvoyé à une semaine, on obtiendra une formule qui récapitule la semaine. Le seul élément qui manquait c'est où la mettre ? Sur la feuille Récap sans doute, mais si tu menais à terme le tableau de recueil des résultats hebdo. on terminerait la formule ! On verrra les alertes ensuite, c'est généralement une MFC à mettre en place... Cordialement...

MFerrand · Answer

Pas un problème de formule mais de disposition du tableau. Je te fais une propostion dans la soirée... A+...

MFerrand · Answer

Re,

Une proposition...

Un petit aménagement pour le calcul du numéro de semaine : les numéros correspondants soit à l'année précédente, soit à l'année suivante, créerait des confusions dans les formules récap les utilisant, donc pour dissocier :

2 formules en F2 et F3 :

=DATE(ANNEE(DateDebut);1;3)-JOURSEM(DATE(ANNEE(DateDebut);1;3))+2

=DATE(ANNEE(DateDebut)+1;1;3)-JOURSEM(DATE(ANNEE(DateDebut)+1;1;3))+2

qui calculent respectivement la date du premier lundi de l'année en cours et celle du premier lundi de l'année suivante.

Ces formules renverront le résultat en fonction de la date que tu saisis en B2...

La formule de calcul du numéro de semaine va donc s'appuyer sur les résultats des deux formules ci-dessus (au lieu d'inclure ce calcul) :

=SI(ET(H6>=$F$2;H6<$F$3);ENT((H6-$F$2)/7)+1;SI(H6<$F$2;0;"na1"))

Elle renvoie ainsi le numéro pour l'année en cours, 0 pour la dernière semaine année préc. et "na1" pour la première semaine de l'année suivante (ce qui ne peut affecter que 6 jours dans une année donnée, 1er au 3 janvier d'une part et 29 au 31 décembre d'autre part).

Pour ce qui est de la Récap, je te propose la récap hebdo sur 4 colonnes en regard de la liste des salariés en B9:B34.

Fausse manoeuvre... la suite arrive.

Donc, récap. hebdo. en colonnes D à G. La cellule D6 fusionnée jusqu'en G affiche le numéro de semaine sélectionné sur liste déroulante (liste établie sur la feuille Fériés et cadrée sur le planning annuel).

Ce numéro est repris ligne 7 (pour que les formules puissent s'y référer directement).

La formule en D9 est donc :

=SOMMEPROD((DECALER(Planning!$H$7:$NH$7;EQUIV($B9;Planning!$A$8:$A$35;0);)=D$8)*(Planning!$H$7:$NH$7=D$7))

C'est donc comme tu le vois la même formule que pour la récap annuelle mais assortie d'une condition relative à la semaine...

La récap annuelle prend alors place à la suite, colonnes H à K.

La mention "Semaine " qui apparaît en D6 est mise en format de cellule.

De même l'affichage des 0 est inhibé par format de cellule.

A voir.

30stephalvi-planning.xlsx (126.32 Ko)

LouReeD · Answer

Bonsoir Maréchal ! Je crois que je vais prendre cette idée de calcul de semaine année passée et année futur pour mon indien !!! En effet je fais dessus des récaps à la semaine et le problème des semaine de fin et début d'année je l'ai contourné avec encore une fois une usine à gaz !!!! Merci pour l'idée, me reste plus qu'à le mettre en application pour la futur version !!!! Comme je l'avais fait avec vos recommandation de numéro de semaine ! @ bientôt LouReeD...

MFerrand · Answer

J'ai mis une fonction de calcul du numéro de semaine sur un post où tu es intervenu aussi... Le principe du calcul est de calculer le premier lundi (ou comme l'a fait Laurent Longre dans une de ses formules le lundi précédent pour éviter d'ajouter 1 au résultat). Le problème est que pour 6 jours de l'année il y a indécision sur le premier lundi de référence, donc quand on détermine en même temps l'année, cela allonge légèrement la formule et on la traine sur les 359 ou 360 autres jours un peu inutilement si on peut faire autrement. Maintenant. quand tu utilises VBA, autant mettre une fonction... Celle que j'ai mis sur le post indiqué n'est pas destinée à être utilisée en feuille de calcul, mais pour l'utiliser en feuille de calcul il suffit de remettre Application.Volatile que j'avais enlevé pour la circonstance, et remettre un nom de fonction en majuscules (pour conserver l'esthétique commune des fonctions Excel). Bonne soirée...

MFerrand · Answer

Ok... J'ai noté que sur le Planning tu avais la première semaine sous liste déroulante mais pas les suivantes... Bonne soirée...

MFerrand · Answer

SOMMEPROD est une fonction qui traite des matrices : dans celle citée, il y en a deux. (DECALER(Planning!$H$7:$NH$7;EQUIV($B9;Planning!$A$8:$A$35;0);)=D$8) Celle-ci renvoie la ligne correspondant au salarié, par décalage de la ligne 7, le décalage étant calculé avec EQUIV sur la colonne salarié. En mettant une condition sous la forme =D8, on vise à ne retenir que l'une des mentions M, AP, N ou FO de cette ligne. La matrice renvoyée sera donc une matrice de valeurs VRAI si la mention correspond à D8, ou FAUX dans le cas contraire. Pour la même matrice figurant dans le calcul récap annuel des mêmes mentions, on avait utilisé l'opérateur -- devant la matrice. Cet opérateur avait pour rôle de transformer les VRAI en 1 et les FAUX en 0, que SOMMEPROD alors additionnait. Pour le même résultat on aurait pu aussi bien faire : (matrice)*1 ou (matrice)+0, les valeurs booléennes se transformant automatiquement en 0 ou 1 lors d'une opération. Ici en présence de deux matrice que l'on multiplie, nul besoin d'ajouter une opération supplémentaire pour avoir le résultat. La seconde matrice : (Planning!$H$7:$NH$7=D$7) est comme on le voit de même taille que la première (sinon on aurait une erreur). Cette matrice est constitué par la ligne 7 qui porte le numéro de semaine et est mise sous condition pour ne retenir qu'une semaine de l'année. Ainsi cette matrice sera également constituée de valeurs VRAI quand la semaine correspond à celle recherchée ou FAUX dans le cas contraire. La multiplication de ces 2 matrices se fait membre à membre, la matrice résultante renverra 1 dans tous les cas où l'on aura VRAI*VRAI, et 0 dans tous les autres cas (VRAI*FAUX, FAUX*VRAI, FAUX*FAUX). SOMMEPROD additionnera les 1 pour renvoyer le nombre de mentions recherchées (M, AP, etc.) sur une semaine donnée pour un salarié donné. De façon générale dans Excel une formule matricielle se valide par la combinaison de touches Ctrl+Maj+Entrée, qui a pour effet d'encadrer la formule d'accolades afin de le signaler. Beaucoup de fonctions peuvent se prêter à une telle utilisation, dans laquelle on remplace des valeurs uniques pour l'utilisation normale de la fonction par des matrices. Malheureusement pas toutes...! La particularité de SOMMEPROD est que la fonction est de conception matricielle sans exiger une validation matricielle. Et le fait qu'elle accepte des conditions (sous la forme que tu as vue) lui donne une grande souplesse dans une diversité d'utilisations. Elle est décrite dans l'aide comme réalisant la somme du produit de deux ou plusieurs matrices, mais tu peux constater qu'elle peut être utilisée pour des dénombrements, également pour une recherche lorsqu'un talbeau à 2 dimensions est assorti de conditions supplémentaires... et évidemment pour des sommes conditionnelles (pour lesquelles elle était largement utilisée avant l'apparition de SOMME.SI, et l'est toujours dans les cas critiques où l'emploi de SOMME.SI(.ENS) est plus malaisé...) J'espère avoir répondu à tes interrogations...