Bonjour,

J'ai désespérément besoin de votre aide en excel svp.

J'ai un fichier de suivi de remplacement de personnel. Anciennement je calculais les heures acceptées sur une base mensuelle et tout allait bien. Désormais, on me demande de faire le calcul sous une base de 28 jours, soit le temps de rotation de mes quatre équipes.

J'ai donc des dates de commencement des plages de 28 jours (ex: 2015-01-11, 2015-02-08, 2013-03-08, etc.) et je dois calculer les heures que tel ou tel personne ont acceptées en temps supplémentaire, le tout référencé dans l'onglet Appel. Avez-vous des pistes de solution pour arriver à calculer le tout ?

Vous pouvez trouver un exemple de ce que je veux faire ici: http://www.cjoint.com/c/EFjuZyggiIR

Un gros merci d'avance pour votre aide précieuse !

Bonsoir,

En B19 : {=SOMMEPROD((Appels!$A:$A>=B$18)*(Appels!$A:$A<C$18)*(Appels!$C:$C=$A19)*SI(ESTNUM(Appels!$M:$M);Appels!$M:$M;0))}

A valider par Ctrl+Maj+Entrée.

NB- Ton classeur est "chargé", le calcul est lent.

Bonjour Ferrand

Un gros merci pour ton aide. Est-ce possible pour toi de m'expliquer svp ? J'aimerais comprendre pour ne pas avoir a reposer la question et pour pouvoir adapter?

Que veux-tu dire par mon classeur est "chargé" ? Oui je remarque qu'il est lent mais je ne sais pas pourquoi...

Un gros merci !

Bonjour,

Vérification faite, c'est ma formule (qui opérait sur l'ensemble des colonnes) qui ralentit le calcul. J'avais fait ainsi pour aller plus vite, mais il vaut donc mieux faire autrement.

1) Nommer la colonne A en champ dynamique. J'ai nommé dapp [pour date appel, mais tu peux donner un autre nom]. Un nom dynamique se fait au moyen de la fonction DECALER. Tu te positionnes sur A2 (première cellule du champ à nommer), puis dans l'onglet Formules tu cliques sur Définir un nom.

La ligne Fait référence à de la boîte de dialogue affiche alors : =Appels!$A$2

Tu complètes pour obtenir la formule suivante : =DECALER(Appels!$A$2;;;NBVAL(Appels!$A:$A)-1;1)

Tu mets le nom dans la rubrique Nom, et tu valides.

Les 3 points-virgules accolés indiquent que tu définis une plage dont le décalage ligne et le décalage colonne sont nuls à partir de A2 [A2 est donc la premère cellule du champ nommé]. L'argument suivant indique le nombre de lignes du champ : on l'ajuste en prenant le nombre de valeurs contenues dans la colonne A, le -1 est pour déduire la valeur de A1 non incluse dans le champ. On limite le champ à 1 colonne pour le dernier argument (donc 1).

Ainsi, en l'état actuel, le nom désigne la plage A2:A100. Si tu ajoutes une valeur en A101, il désignera la plage A2:A101...

Dans la formule, on a besoin 2 fois de la colonne A (donc le champ nommé), puis des colonnes C et M. On les pointera en décalant le champ nommé respectivement de 2 et 12 colonnes.

2) En B19, la formule : =SOMMEPROD((dapp>=B$18)*(dapp<C$18)*(DECALER(dapp;;2)=$A19)*DECALER(dapp;;12))

Plus besoin de valider en matriciel, elle ne contient plus d'élément qui y oblige [la condition relative à M] comme précédemment. (La formule reste matricielle cependant car SOMMEPROD est une fonction matricielle par définition [mais qui se valide de façon ordinaire].

Explication formule -

SOMMEPROD va additionner les valeurs de la colonne M mais après les avoir multiplé par 3 autres facteurs conditionnels qui vont renvoyer 1 ou 0. La valeur de M ne sera prise en compte que si ces 3 facteurs sont simultanément à 1.

Les 2 premiers concernent l'inclusion dans la période de 28 jours, dont la date de début est mentionnée ligne 18. Pour la colonne B on a donc la date de début en B18 et la date de début de la période suivante en C18. Pour la colonne B, on ne doit donc prendre en compte que les dates de Appels!A qui seront >= à B18 et <C18. On formule B$18 et C$18 pour étendre sur les lignes suivantes avec une même référence à 18 et étendre sur les colonnes suivantes avec adaptation (dans la formule de B recopiée en colonne C, le B devient C et le C devient D).

Le 3e concerne le nom de l'agent, mentionné en colonne A. Sur la ligne 19, on ne prendra en compte que les noms en Appels!C identiques à A19. On formule $A19, de façon qu'en recopiant sur les lignes suivantes le 19 passe à 20, 21... et que sur les colonnes suivantes A reste A.

La formule tapée en B19 peut donc être étendue vers le bas (tant qu'il y a des noms en colonne A) et vers la droite (tant qu'il y a des dates en ligne 18, une date de plus que l'extension pour que la fin de période soit prise en compte).

Un gros merci !

Transposé dans mon classeur réel le tout est parfaitement fonctionnel. J'irai me former plus à fond sur les fonctions matricielles.

Par contre si tu peux me renseigner pour les classeurs chargés ?

Merci encore, très très apprécié !

Je n'ai vu qu'après ma réponse, la tienne. Je te suis infiniment reconnaissante du temps que tu as pris pour partager tes connaissances et de la précision avec laquelle tu l'as fait.

Je te souhaite une excellente journée et merci encore !