> Tous les forums > Forum Bureautique
 Calcul d'heures sur période de 28 joursSujet résolu
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
Kassie
  Posté le 09/06/2015 @ 23:03 
Aller en bas de la page 
Petite astucienne

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 !

Publicité
ferrand
 Posté le 10/06/2015 à 01:48 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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.

Kassie
 Posté le 10/06/2015 à 14:13 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

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 !

ferrand
 Posté le 10/06/2015 à 15:58 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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).

Kassie
 Posté le 10/06/2015 à 17:07 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

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é !

Kassie
 Posté le 10/06/2015 à 19:34 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

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 !

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour participer à la discussion.
Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !
Recevoir PC Astuces par e-mail


La Lettre quotidienne +226 000 inscrits
Avec l'actu, des logiciels, des applis, des astuces, des bons plans, ...

Les bonnes affaires
Une fois par semaine, un récap des meilleurs offres.

Les fonds d'écran
De jolies photos pour personnaliser votre bureau. Une fois par semaine.

Les nouveaux Bons Plans
Des notifications pour ne pas rater les bons plans publiés sur le site.

Les bons plans du moment PC Astuces

Tous les Bons Plans
9,99 €Carte mémoire microSDXC UHS-I SanDisk A1 Ultra 64 Go à 9,99 €
Valable jusqu'au 07 Décembre

Amazon propose actuellement la carte mémoire microSDXC UHS-I SanDisk A1 Ultra 64 Go à 9,99 €. Cette carte mémoire offre des vitesses jusqu'à 100 Mo/s.


> Voir l'offre
799,99 €PC Portable 15.6 pouces Asus X571 (FHD IPS 120Hz, Core i5, 16Go, 512Go SSD, GTX 1650 Ti) à 799,99 €
Valable jusqu'au 06 Décembre

RueDuCommerce fait une promotion sur l'ordinateur portable Asus Laptop FX571LI-AL246 qui passe à 799,99 € alors qu'on le trouve ailleurs à partir de 900 €. Cet ordinateur portable est équipé d'un écran 15,6 pouces IPS Full HD (1920x1080 pixels) 120 Hz anti-reflet, d'un SSD de 512 Go, d'un processeur Intel Core i5-10300H, de 16 Go de RAM et d'une carte graphique dédiée NVIDIA GeForce GTX 1650 Ti avec 4 Go de RAM.

L'ordinateur est livré dans OS et vous devrez donc installer vous-même Windows 10/11.


> Voir l'offre
GratuitJeu PC Dead by Daylight gratuit
Valable jusqu'au 09 Décembre

Epic Game Store offre actuellement le jeu PC Dead by Daylight. Dead by Daylight est un jeu d'action multi-joueurs (4 contre 1) qui vous plonge dans une ambiance de film d'horreur. Chaque joueur peut jouer le rôle d'un tueur sanguinaire pendant que les quatre autres tentent de lui échapper en évitant d'être attrapé et tué. Jeu et audio en français. PEGI 18


> Voir l'offre

Sujets relatifs
calcul d'heures
Excel 2007/ Calcul de salaire avec nbre d'heures
Calcul heures > 24 heures
Calcul d’heures de nuit
Calcul nombre jours
Feuille de calcul d'heures avec Excel?
calcul heures ,monaie
Formule de calcul pour les heures
calcul heures sup
Calcul heures négatives
Plus de sujets relatifs à Calcul d''heures sur période de 28 jours
 > Tous les forums > Forum Bureautique