Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2016 Formules matricielles

corsu2a

XLDnaute Occasionnel
Bonjour à tous
j'ai trouve un exemple de tableau qui permet de calculer des conges, un exemple très riche.
Dans le tuto le calcul des jours exclu les Week End et les jours fériés.
Avec cette formules
=SOMMEPROD(($C6:$AG6=GAUCHE(AH$5;3))*(JOURSEM($C$5:$AG$5;2)<5(NB.SI($AK$7:$AK$17;$C$5:$AG$5)=0))

Vous le verrez vous même exemple magnifique.

Je ne vais pas vous détallé la formule dans ce message, elle est expliquée dans le fichier joint.

En fait j'espère que vous allez pouvoir m'aider a modifier cette formule avec d'autres paramètres que les week end et jours féries.

Avec ce même tableau et sur la base de cette formule, je souhaiterais calculer les fréquences de travails suivantes.

A Du lundi au dimanche
B Du Lundi au Vendredi
C Du Vendredi au Dimanche
D Un Samedi
E Un Dimanche ou un jour férié
F Et pour finir, 1 nuit dans la semaine ( avoir car pas évident )

En fait je voudrais modifier la formule pour que :
A les jours soient compris entre jour 2 et 7 inclus
B entre jours 2 et 6
C entre jours 5 et 7
D = jour 6 ( ok =6 )
E = 7 ou Férié
F = 1 jour férié si il y a

Je reste à votre disposition si je n'ai pas été très clair.

Bien à vous
 

Pièces jointes

  • Mariciel compte les fonctions.xlsx
    14.6 KB · Affichages: 17

Wayki

XLDnaute Impliqué
Bonjour,
Il serait gentil la prochaine fois de nous communiquer un fichier non protégé afin que l'on puisse faire le nécessaire.
Ci-joint le fichier non protégé avec les conditions pour nos amis du forum qui veulent tester, je ferais un retour si j'arrive à faire quelque chose.
A +
 

Pièces jointes

  • Mariciel compte les fonctions.xlsx
    15.3 KB · Affichages: 6

corsu2a

XLDnaute Occasionnel
Bonjour @Wayki
Comme j'ai pu l'expliqué a @BrunoM45, je suis désolé je n'ai pas vu ta réponse, et dieu sait si je l'attendais.
Désolé aussi pour le tableau verrouillé, je t'ai remis en PJ un tableau qui explique mieux le projet.
En fait, je souhaiterais que lorsque une semaine complète est validée que le résultat soit 1, si Vendredi à dimanche alors 1 ainsi de suite, de la va s'incrémenter un tableau de rémunération sur la feuille Tableau rému.
Merci infiniment pour ton aide.
 

Pièces jointes

  • Formules et MFC du calendrier OFF.xlsx
    23.5 KB · Affichages: 5

Wayki

XLDnaute Impliqué
Bonjour,
C'est pas grave il arrive que les notification bugs.
Prenons l'exemple du vendredi samedi dimanche travaillé.
Mon fichier va comptabiliser 3, car 3 jours travaillés. Il suffit dans la formule de diviser la sommeprod par 3 pour obtenir 1. Il faudra jouer avec le format pour obtenir un nombre entier, voir rajouter une fonction arrondi inférieur pour que si il y a une absence, ça ne comptabilise pas le WE.
Je vous laisse essayer ça
A +
 

corsu2a

XLDnaute Occasionnel
Bonjour @Wayki bonjour à tous

malheureusement ça ne respect pas le besoin.

Les variables ne peuvent pas être additionnées en bout de tableau. Si l'employé à travaillé une semaine complète il sera rémunéré a 5€, si il à travaillé une semaine complète et la semaine suivante il aura travaillé du lundi au jeudi, alors il sera rémunéré 3€ soit (1x5€ + 1x3€ ).
Il faudrait trouver une formule qui dise "strictement compris entre Lundi et dimanche" ou "strictement compris entre Lundi et Jeudi".
Les seules qui fonctionnent sont celle qui compte un jour

=SOMMEPROD(($C7:$AG7="DIR")*(JOURSEM($C$5:$AG$5;2)=6)*(NB.SI($AO$7:$AO$17;$C$5:$AG$5)=0)) pour samedi
et =SOMMEPROD(($C7:$AG7="DIR")*(JOURSEM($C$5:$AG$5;2)=7)*(NB.SI($AO$7:$AO$17;$C$5:$AG$5)=0)) pour Dimanche.

Pour les acronymes comme DIR, cela veut signifie DIRECTEUR, j'ai comme ça plusieurs autres fonctions dans un groupe de cadre, CDGPIA,CDGPOVO etc. Le calcul devra se faite pour toutes ces fonctions. Je ne peut donc pas utiliser autre chose, T ne fonctionnerait pas, TN non plus...
Je pense que ces calculs sont assez complexes, j'espère que nous allons y arriver.

@chris qui ne me répond plus m'a fait une proposition par PowerQuery + TCD ce qui me semblait parfait mais ça ne fonctionnait pas sur mon PC et mes connaissances en PowerQuery sont très approximatives voir nulles.

J'ai maintenant modifier la présentation du tableau qui en effet n'était pas si simple. Je joins ce fichier que nous pouvons peut être modifier ensemble ??


Si ça intéresse quelqu'un qui souhaite nous aider, cela pourrait être un bon exemple pour d'autres.
Merci
 

Pièces jointes

  • Calcul ast off ete2_PQ (7).xlsx
    46.2 KB · Affichages: 6
Dernière édition:

Wayki

XLDnaute Impliqué
Salut,
C'est bien come je l'ai fait à part qu'il faut le faire fonctionner par ordre de priorité, de façon à ce qu'un dimanche ne se retrouve pas compris dans un vendredi dimanche par exemple. Il faut donc trouver les priorité ou les définir et les soustraire dans les formules qui suivent. je sais pas si je suis clair. Peut-être faudra t il se tourner vers du VBA vu la complexité de ton planning. Je regarde dès que je peux.
A +
 

corsu2a

XLDnaute Occasionnel
Bonjour
tu as bien cerné le problème en tout cas. J'ai tenté autre chose si ça peut aider mais je retrouve le même problème du décompte de jour ou le lundi se compte 2 fois lorsque l'employé fait une semaine complète et 1 Lun - Vend.
J'ai essaye de compter des Mises en forme conditionnel, j'ai trouvé que ça dans mon exemple joint.
 

Pièces jointes

  • Planning cadre 2021-2022 decompte couleur OKKKKKKKKKKKKKK.xlsx
    12.1 KB · Affichages: 3

corsu2a

XLDnaute Occasionnel
Bonjour @Wayki
je post en PJ toute l'explication pour créer ce tableau. le seul soucis c'est que ça ne fonctionnait pas sur mon ordi, je ne sais pas pourquoi. C'est un excellent travail réalisé par @chris mais que je n'arrive tout simplement pas à m'approprier. De plus la présentation du planning a changé donc ses formules ne fonctionnent surement plus. Est ce que @chris pourrait prendre un peu de temps pour me répondre
 

Pièces jointes

  • Planning cadre 2021-2022 decompte couleur OKKKKKKKKKKKKKK.xlsx
    12.1 KB · Affichages: 2
  • Explication calcul ast off 2021.docx
    15.9 KB · Affichages: 5

Wayki

XLDnaute Impliqué
Hello,
Beaucoup plus simple ce tableau.
Ci-joint une proposition.
Je ne comprend pas l"explication par rapport à ton doc partagé, il manque des feuilles non ?
Ca parle de feuil2 etc qui n'y sont pas.
A +
 

Pièces jointes

  • Planning cadre 2021-2022 decompte couleur OKKKKKKKKKKKKKK.xlsx
    12.9 KB · Affichages: 3
Dernière édition:

corsu2a

XLDnaute Occasionnel
Bonjour @Wayki
Merci pour ta réponse, j'essai au fur et a mesure de recentrer le problème afin que ce soit plus simple pour tout le monde. Dans l'ensemble c'est presque résolu hormis quelques bugs.
J'ai tout mis en avant dans le fichier joint sur fond jaune.
merci mille fois pour ton aide, on tient le bon bout.
Bien à toi
 

Pièces jointes

  • Planning cadre 2021-2022 decompte couleur OKKKKKKKKKKKKKK.xlsx
    12.3 KB · Affichages: 3

Wayki

XLDnaute Impliqué
J'ai changé quelques trucs pour gagner en lisibilité et simplifié quelques formules.
Une proposition ci-joint.
 

Pièces jointes

  • Planning cadre 2021-2022 decompte couleur OKKKKKKKKKKKKKK (2).xlsx
    12.7 KB · Affichages: 6

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…