Formule de Monique

sonskriverez

XLDnaute Occasionnel
Bonjour le forum,

Je galère toujours sur la formule de Monique, je voudrais calculer les hrs net a partir de cette formule mais en :

1°) mettre la plage horaire dans la formule, toujours de 0:00 à 24:00

2°) en allant chercher la plage de jours fériés en colonne G

merci de votre aide
 

Fichiers joints

wilfried_42

XLDnaute Barbatruc
Re : Formule de Monique

Bonjour Sonskriverez

tu demandes de modifier la formule de monique, j'ai vu la formule et c'est loin d'etre facile

Pour les jours feriés : dans ton fichier tu as un probleme, tous les noms de plage de tes jours feries ne sont pas corrects. Tu y accedes uniquement dans la feuille concernée, quand tu te trouves dans calcul, tu fais menu Insertion/Nom/définir, il te manque des noms : du devras les refaire

Toujours pour les jours feries,
.JOURS.OUVRES(H5;I5;G5)
devient :
NB.JOURS.OUVRES(H5;I5;Indirect(G5))
ton fichier en retour

Les 2 #REF sont dus à tes problemes de noms de plage

Pour la 1ere question, je n'ose pas y toucher, mais ca fonctionne alors masques les colonnes
 

Fichiers joints

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Bonjour Wilfried,

Merci de ton aide je vais refaitre mes plages, oui en drniere solution je vais cacher le tableau mais si je pouvais alléger la formule pour faire plus propre ca seraiit bien
 

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Il est possible que dans le fichier d'origine qu'une plage n'existe pas, comment dans ce cas ne pas prendre en compte les jours de fêtes et de calculer uniquement les jours ouvrées
 

wilfried_42

XLDnaute Barbatruc
Re : Formule de Monique

re:

Tu crées un plage du nom necessaire et ty y affeces une cellule vide, ca devrait faire l'affaire
 

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Non ça ne fonctionne pas, j'ai une liste fixe de pays comme tu peux le voir dans l'exemple, mais il se peux que un nouveau pays soit dans la liste de traitement et disparaisse le lendemain. Il faut que je puisse quand même faire le calcul pour ce pays mais sans les jrs fériés
 

Gael

XLDnaute Barbatruc
Re : Formule de Monique

Bonjour Sonskriverez, bonjour Wilfried,

Je ne suis pas sûr d'avoir bien compris mais voici un essai en simplifiant la formule de Monique (puisse-t-elle me pardonner!) pour ne plus tenir compte du tableau des horaires puisqu'on est sur une plage de 24h.

Je réfléchis en attendant au problème des pays.

@+

Gael
 

Fichiers joints

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Gael, lorsque j'aurais terminé ce truc, il faut que je t'envoie des aspirines :)

bravo la formule à bien diminuée reste le pbl de la plage

Mais merci c'est très formateur. Et je pense que le tps de calcul par rapport à ma fonction qui fait actuellement le même boulot devrait s'améliorer.

Surtout sur plus de 9000 lignes
 

Gael

XLDnaute Barbatruc
Re : Formule de Monique

Re,

Une solution pour les pays et jours fériés dans l'exemple joint:

* Un seul nom "Feries" qui va chercher la plage de jours fériés du pays concerné
* Une modification dans la formule, si le pays n'existe pas on ne met rien dans le paramètre fériés de NB.JOURS.OUVRES sinon on met le nom "Feries" qui fait référence aux JF du pays.

@+

Gael
 

Fichiers joints

Gael

XLDnaute Barbatruc
Re : Formule de Monique

Re,

Petite précision, dans le tableau des JF par pays, j'ai supprimé une ligne blanche en trop (qui génait dans le formule de calcul) et rajouté 2 lignes pour que le dernier pays ne pose pas de problème: "Fin" et "Date".

J'ai créé une plage variable qui contient tous les JF de l'ensemble des pays ce qui te permet de rajouter ou retirer un pays sans avoir rien d'autre à modifier.

modifs dans le fichier joint.

@+

Gael
 

Fichiers joints

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Alors je suis épaté, si tu pouvais m'expliquer la formule qui gère les plages et comment tu arrives à matcher sur le pays, juste pour ma culture

merci
 

Gael

XLDnaute Barbatruc
Re : Formule de Monique

Re,

En faisant insertion nom définir et en sélectionnant feries, tu as accès à la formule que tu peux copier-coller dans une cellule pour l'analyser.

La base est la fonction décaler qui permet de créer des plages variables et est de la forme:

Decaler (Cellule de référence; nb de lignes; nb colonnes; hauteur; largeur)

PaysJF est la plage complète de tous les pays

soit:

=DECALER('European Bank Holidays'!$A$2;EQUIV(Calcul!$G7;PaysJF;0)+1;;EQUIV("Date";DECALER('European Bank Holidays'!$A$2;EQUIV(Calcul!$G7;PaysJF;0)+2;;100);0)-2)

Cellule de référence = 'European Bank Holidays'!$A$2
Nb lignes=EQUIV(Calcul!$G7;PaysJF;0)+1 On cherche la ligne de la table contenant le pays et on se positionne sur la première date du pays.

Hauteur: EQUIV("Date";DECALER('European Bank Holidays'!$A$2;EQUIV(Calcul!$G7;PaysJF;0)+2;;100);0)

On recherche en fait le mot "Date" (qui fait partie du pays suivant) dans une plage qui commence à la ligne du pays +2 pour ne pas trouver le premier mot date et sur une hauteur de 100 (que l'on peut diminuer) pour être sûr d'avoir au moins un pays complet et donc de trouver un mot "Date".

Comme Equiv donne un numéro de ligne relatif dans la table utilisée, si l'on trouve "Date" à la ligne 14 c'est que la hauteur à utiliser est de 14 et le -2 pour ne pas prendre en compte la ligne date et la ligne de séparation à blanc.

Attention, la formule ne marche que pour la colonne G de la feuille calcul, si jamais tu déplace ton tableau, il faut changer la colonne ($G) dans la formule.

@+

Gael
 

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Gael,

Merci pour tes explications, c'est super.

Par contre j'ai transposer la formule dans mon tableau et ca ne fonctionne pas et je comprends pas pourquoi. J'ai jyste modifé en remplaçant ERREUR par 0 car il n'y a pas forcement de pause.

merci de ton aide
 

Fichiers joints

Gael

XLDnaute Barbatruc
Re : Formule de Monique

Re,

En fait, il faut faire attention quand tu copies des formules d'un tableau sur l'autre; si tu fais un simple copier coller, Excel va conserver dans la formule le chemin d'accès complet du fichier de départ car il suppose que tu veux créer un lien.

La solution dans ce cas est d'ouvrir la formule, comme si tu voulais la modifier, de sélectionner tout le texte puis copier et coller dans la cellule de destination.

Sinon j'ai modifié un peu les formules pour 2 raisons:

* Je n'avais pas compris le test initial qui vérifie que les dates saisies ne sont pas des jours fériés, j'ai donc remplacé la référence cellule par le nouveau nom "Feries"
* Dans ce cas, si le pays n'existe pas il se produisait une erreur et j'ai ajouté un test pour ce cas précis.

Voilà vérifie bien si tout est OK et n'hésite pas à réécrire si tu rencontres un autre problème.

@+

Gael
 

Fichiers joints

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Décidement je n'y arrive pas, pourtant je fais bien un copier coller selon tes instructions. Par contre à chaque modification la fenêtre de mise à jour s'affiche !!!

Bon, je te repasse un fichier avec exactement le nombre colonnes d'origine, sur la feuille "Tools" j'ai copié tes formules et les mêmes après copie, je trouve pas le bug.

En plus j'aurai trés certainement besoin de transposer les formules dans d'autres feuilles, et j'y arrive pas, c'est enervant.

Merci de patience
 

Fichiers joints

Gael

XLDnaute Barbatruc
Re : Formule de Monique

Bonjour Sonskriverez, bonjour à tous,

Dans tes formules de calcul de temps, il faut bien faire attention à ce que les colonnes soient bien des colonnes de dates et que la colonne contenant le pays au début de la formule soit correctement définie.

La première chose à vérifier avant de tester les formules est que les noms PaysJF et feries soient bien définis. Ils n'étaient bons ni l'un ni l'autre.

les formules nommées qui contiennent des références relatives sont délicates car elles sont fonction de la cellule active. Lorsque tu définis "Feries" par exemple, avec $S2 pour le pays il faut que la cellule active soit sur la ligne 2; ainsi quand tu sera sur une cellule de la ligne 4, la formule pointera sur $S4 et ainsi de suite.

Pour vérifier les formules nommées, tu sélectionnes une cellule dans une ligne qui contient un pays puis tu fais Insertion - nom - définir, tu sélectionnes "Feries" et tu cliques dans la formule ==> XL doit t'afficher la plage contenant les JF du pays sélectionné. Puis tu fais de même pour Pays JF qui doit t'afficher toute la plage des pays et jours fériés.

Ci-joint la version corrigée.

@+

Gael
 

Fichiers joints

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

bonjour Gael,

ok merci d'avoir corriger ma feuille en // je vais faire un essais en suivant tes instructions, je te tiens au courant

merci
 

mutzik

XLDnaute Barbatruc
Re : Formule de Monique

bonjour,

pour remédier au prob de la fenetre liaison / mise à jour qui s'ouvre, il faut aller dans edition/liaison, selectionner le fichier lié et cliquer sur rompre la liaison
 

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Bertrand,

Merci de te reponse en plus sur une feuille ou le phénomène n'existe pas cela enlève les formules en laissant la valeurs (a moins que ceal soit un hasard sur lma feuille), cela peu-être utile.

merci bcp
 

sonskriverez

XLDnaute Occasionnel
Re : Formule de Monique

Gael,

j'ai trouvé un bug, je m'explique.

Si tu retire les jours de pause colonne T et U que le pays n'est pas dans la plage alors il y a des ####### dans la colonne AK

je suis désolé d'user de ton temps, mais merci de ton aide
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas