Comptabilser des cellules avec des sigles différents
Bonsoir
J'ai un tableau de périodes de congés annuel. Je cherche un système qui me permette d'avoir une alerte lorsque je dépasse 4 périodes qui ne sont pas composé uniquement de CA.
Toutes les périodes commencent toujours pas un CA ou CAP.
Elles peuvent être composées de CA uniquement, de CA et CAP, mais des lors qu'elles sont constituées de CA et RTT, ou CA et CF, je voudrais qu'elles soient comptabiliser et alerter lorsque le total dépasse 4
Voir tableau dans fichier joint.
Merci pour cette aide pas évidente
Norberlingo
Bonsoir,
Voici une solution qui n'est valable que s'il n'y a qu'une et une seule période de CA par mois.
Je me base sur le fait que toutes les périodes d'absence commence par CA ou CAP (pourquoi y a t il des CA. ? il faut éviter cela pour d'autre tests éventuels). Cela veut dire que les coordonnées CA et CAP ne sont pas significatives et peuvent être ignorées.
Je me contente donc de vérifier en colonne AG qu'il y a bien soit un CF soit un RTT dans le mois auquel cas je mets 1 ou 0.
Ensuite en A1 comme demandé, je fais la somme de tous les mois (j'ai limité à l'année càd que je n'ai pas pris le mois de janvier de l'année suivante car cela ne me semblait pas logique mais je me trompe peut être)
Ensuite grâce à la mise en forme conditionnelle, il ne suffit plus que de tester si la somme est supérieure à 4 auquel cas la cellule est mise en rose et le chiffre en rouge sinon la cellule fera apparaître le nombre de période en noir sur blanc (il y a moyen de faire autrement et de rendre invisible le chiffre).
Si il y a plus d'une période d'absence par mois, je ne vois pas comment résoudre le problème par les fonctions, il faudra passer en VBA.
Préviens moi si c'est le cas
A+
Chris
Bonjour
En fait, oui il peut y avoir plus d'une période par mois de CA+CF ou RTT par exemple du 1 au 8 (CA+ CF) et du 25 au 30(CA+CF). Cela ferait donc deux périodes.
Donc il faut pouvoir comptabiliser plusieurs périodes sur le même mois.
Une période est un ensemble consécutifs de CA ou CA+ CF ou CA+RTT ou CAP+CF ou CAP+RTT
Pas évident je l'avoue
Merci déjà pour les premières esquisses
Norberlingo
Bonjour,
Un beau casse tête ton truc !
Je pense avoir quand même trouvé une solution sans macro.
Contraintes que j'espère, cette fois ci , tu pourras respecter :
1 ne pas avoir de mélange CF RTT dans une période
2 enlever tous ces foutus "." qui sont mis de temps en temps à la fin des CF et des RTT (je les ai enlevés dans le fichier que je renvoie). Les "." à la fin des CA ne me dérangent pas puisque de toutes façons je teste les 2 premiers caractères de CA ou CAP mais je te conseille quand même de les enlever (ce n'est pas "propre" comme travail et cela peut te jouer des tours pour d'autres tests)
En fait la solution consiste à additionner 1 horizontalement de AH à BL pour autant que la cellule considérée ne soit pas = à "" ou que les 2 premiers caractères ne soient pas CA et enfin que la cellule précédente ne soit pas = à la cellule considérée. On obtient ainsi en colonne BL (31) le nombre de plage se terminant par CF ou RTT (SANS ".") pour le mois.
Après il suffit de faire la somme en A1 avec le même raisonnement que dans mon mail précédent.
J'ai ajouté en janvier une période complémentaire et cela à l'air de fonctionner.
A toi de tester pour voir si cela tient bien la route.
Merci de confirmer si cela convient
A+
Chris
Bonsoir
Tout d'abord merci pour le temps consacrer
C'est vrai que c'est un casse tête...mais c'est déjà très bien avancé.
Quelques explications. Les points après les CA, CAP , RTT OU CF représentent les positions des samedi ou dimanche, c'est a dire que lorsqu'il y a un CA. c'est un samedi ou un dimanche, idem pour un CF ou un RTT
Un autre soucis...lorsque la période est à cheval sur deux mois, cela me compte deux périodes alors qu'il y en a qu'une
exemple de 28 avril au 5 mai .... CA CA RTT RTT RTT RTT RTT RTT , l'application me compte deux périodes alors qu'en fait il y en a qu'une.
Bonne fin de soirée
Norbelingo
et encore merci
Bon maintenant que j'ai compris l'utilité des points... il faut donc en tenir compte.
C'est chose faite en ne considérant que les 2 premiers caractères dans la comparaison (CF ou RT).
Reste le problème des chevauchements de mois.
Je pense l'avoir résolu en testant :
Si nous sommes le 1 du mois, j'ajoute un test qui compare la cellule du 1 avec la dernière cellule du mois précédent.
Cela complique bien entendu la chose car la fin de mois n'est pas la même tous les mois (30 et 31 et 28 et 29)
J'ai donc introduit la notion d'année dans le fichier afin de savoir si nous sommes dans une année bissextile ou pas. (mise en B1 et nommée AN)
Ensuite, plutôt que de mettre le mois en A3:A14, j'ai mis la date (1/1/2015...). Grâce au formatage de la cellule, on fait apparaître le mois en littéral.
Enfin dans la colonne AG, je recherche le dernier jour du mois.
Connaissant le dernier jour du mois, il est possible de calculer l'adresse de la dernière cellule du mois précédent et grâce à la fonction INDIRECT on va chercher le contenu de ce qui se trouve dans cette adresse et dont on prend les 2 premiers caractères... ouf !
=AH3+SI(B3="";0;SI(GAUCHE(B3;2)="CA";0;SI(GAUCHE(B3;2)=GAUCHE(A3;2);0;SI(AI$2=1;SI(GAUCHE(INDIRECT(ADRESSE(LIGNE()-1;1+AG2;4;2;));2)=GAUCHE(B3;2);0;1);1))))
Oui je sais ce n'est pas simple mais le problème non plus.
Examines cette formule et si elle te pose problème reviens vers moi avec tes questions.
J'ai remis les points (et cela ne pose pas de problème).
J'ai enlevé le CF qui était dans la cellule du 31 septembre (aux dernières nouvelles il n'y a que 30 jours dans septembre
et j'ai ajouté des CF en début octobre (à la suite de septembre) et cela à l'air de fonctionner. Mais rien ne vaut un test en grandeur nature.
Dernière contrainte quand même : le tableur travaille en année civile : DONC PAS DE CHEVAUCHEMENT D'UNE ANNEE SUR L'AUTRE mais logiquement, en tous cas en Belgique, les compteurs sont remis à 0 au 31/12.
J'en ai profité pour enlever le mois de janvier de l'année suivante qui apparaissait dans le tableau dans la ligne 15.
J'espère que cette fois ci cela te conviendra.
A+
Chris
Bon maintenant que j'ai compris l'utilité des points... il faut donc en tenir compte.
C'est chose faite en ne considérant que les 2 premiers caractères dans la comparaison (CF ou RT).
Reste le problème des chevauchements de mois.
Je pense l'avoir résolu en testant :
Si nous sommes le 1 du mois, j'ajoute un test qui compare la cellule du 1 avec la dernière cellule du mois précédent.
Cela complique bien entendu la chose car la fin de mois n'est pas la même tous les mois (30 et 31 et 28 et 29)
J'ai donc introduit la notion d'année dans le fichier afin de savoir si nous sommes dans une année bissextile ou pas. (mise en B1 et nommée AN)
Ensuite, plutôt que de mettre le mois en A3:A14, j'ai mis la date (1/1/2015...). Grâce au formatage de la cellule, on fait apparaître le mois en littéral.
Enfin dans la colonne AG, je recherche le dernier jour du mois.
Connaissant le dernier jour du mois, il est possible de calculer l'adresse de la dernière cellule du mois précédent et grâce à la fonction INDIRECT on va chercher le contenu de ce qui se trouve dans cette adresse et dont on prend les 2 premiers caractères... ouf !
=AH3+SI(B3="";0;SI(GAUCHE(B3;2)="CA";0;SI(GAUCHE(B3;2)=GAUCHE(A3;2);0;SI(AI$2=1;SI(GAUCHE(INDIRECT(ADRESSE(LIGNE()-1;1+AG2;4;2;));2)=GAUCHE(B3;2);0;1);1))))
Oui je sais ce n'est pas simple mais le problème non plus.
Examines cette formule et si elle te pose problème reviens vers moi avec tes questions.
J'ai remis les points (et cela ne pose pas de problème).
J'ai enlevé le CF qui était dans la cellule du 31 septembre (aux dernières nouvelles il n'y a que 30 jours dans septembre
et j'ai ajouté des CF en début octobre (à la suite de septembre) et cela à l'air de fonctionner. Mais rien ne vaut un test en grandeur nature.
Dernière contrainte quand même : le tableur travaille en année civile : DONC PAS DE CHEVAUCHEMENT D'UNE ANNEE SUR L'AUTRE mais logiquement, en tous cas en Belgique, les compteurs sont remis à 0 au 31/12.
J'en ai profité pour enlever le mois de janvier de l'année suivante qui apparaissait dans le tableau dans la ligne 15.
J'espère que cette fois ci cela te conviendra.
A+
Chris
Bonsoir
Encore merci pour tout...
Je viens de regarder tout ce travail et c'est formidable.
Je ferai des essais ce week end car j'aurai un peu plus de temps et te dirai si tout fonctionne.
Ce qui est super c'est que tu fournis toutes les explications et ça c'est génial.
Vraiment merci beaucoup.
Bonne soirée
@+
Norberlingo
Bonjour,
Une petite réflexion complémentaire qui m'est venue à l'esprit : puisque maintenant nous avons l'année à bord de ton tableur, il était assez facile de mettre en évidence :
1 les WE
2 annuler les jours inutiles en fin de mois
et cela grâce à la mise en forme conditionnelle où j'ai placé 2 tests. (si tu ne connais pas la mise en forme conditionnelle reviens moi)
Sinon va voir dans gérer les règles les 2 tests (et la coche qu'il ne faut pas oublier après le premier test)
A ce propos, j'ai remarqué que les points dans le tableau ne correspondaient pas toujours à un WE.
Il y a peut être une raison autre que je ne connais pas.
Reste à voir si l'usage des points est encore nécessaire (je ne les aime vraiment pas
Une remarque encore : les colonnes à partir de AG peuvent être masquées pour ne pas perturber la vision ainsi que l'impression.
A+
Chris
Bonsoir
Désolé de revenir si tard sur le forum mais suite aux évènements Parisiens, j'étais absent ces derniers jours.
J'ai commencé à regarder plus précisément les données.
J'ai repris mon plan de congés.
J'ai bien indiqué la date de A3 à A14, et formaté la formule. J'obtiens bien les mois de l'année mais je n'arrive pas à insérer la formule:=DATE(AN;LIGNE()-2;1)
D'autre part, si j'intègre l'année "2015" en B1,il n'apparait pas AN en haut à gauche mais le numéro de la cellule, c'est à dire B1.
Dans mon tableau de base où il apparait les congés, toutes les cellules contiennent une formule car elles font références à un autre tableau ( ='Prévisions conges 2016'!B4;='Prévisions conges 2016'!B5, etc etc...). Le soucis c'est que cela,me compte 1 dans les cellules de ton tableau au lieu de 0 et donc cela donne un résultat faux des nombres de périodes.
J'essaye de résoudre tous ces problèmes ...mais pas évident.
Norberlingo
Bonsoir,
J'espère que tu n'as pas eu de conséquences fâcheuses avec les événements parisien.
Pour le tableau :
Si je comprend bien tu es reparti de ton tableur donc la cellule B1 n'est pas nommée : il suffit de se positionner dessus et d'encoder à la place de B1 en haut à gauche AN et de faire entrée. La cellule sera nommée. Cela devrait résoudre ton problème d'insertion de formule qui est probablement du au fait que Excel ne retrouvait pas le nom AN.
Le dernier problème demande plus de réflexion et il est tard : je regarderai cela demain. Ce serait plus facile si tu m'envoyais les fichiers que tu as fait.
Bonne nuit
Chris
Question complémentaire :
Ta formule qui ramène les informations : peux tu au moins me l'envoyer : cela vient probablement de là.
A +
Chris
Bonjour
Encore une journée terrible. sur Paris ...
Concernant le plan de congés.
Je joins en fichier mon tableau avec la connexion .
J'ai essayé de mettre -1 au bout de la formule pour éviter le comptage des cellules faisant référence à une autre feuille.Ca pourrait marcher je crois.
Cependant dans mon tableau, la formule me calcule mes CA ( sans CF ou RTT) comme période.
J'ai du faire une erreur....
Belle après midi
Norberlingo
Bonsoir,
Voici ton fichier en retour et corrigé.
Les commentaires étant assez long, j'ai préféré créer un onglet Commentaires pour t'expliquer tout cela plutôt que d'encombrer le forum.
Dis moi ce que tu en penses (il y a des questions pour améliorer éventuellement) ou bien si tout est en ordre ou encore si tu as des questions complémentaires.
Remarque complémentaire : si tu changes de place une formule : toujours faire attention aux références fixes (avec $). Ces références ne s'adaptent pas si on les déplace (c'est d'ailleurs le but recherché). Il faut donc les adapter à la nouvelle configuration.
Bien à toi
Chris
Bonsoir
Déjà un grand merci pour tout ce travail et le temps consacré....J 'avoue que c'est complexe (très), surtout pour moi et que je n'arrive pas à tout comprendre...surtout la formule.
Désolé pour la MFC des 0 mais je ne m'en rappelais même pas.
J'ai fait des essais ce soir.
En fait je recopie le planning 25 fois .....
Dans un premier temps lorsque je fais un copier coller des cellules AG7/ AG19, une fois sur deux le mois de février bascule en 28 jours au lieu de 29....Etrange, non?
J'ai remarqué aussi la chose suivante. Lorsque les congés sont à cheval sur deux mois, s'il s'agit d'un mois en 30 jours et que par conséquent la cellule du 31 eme jour n'est pas remplie, et que je repars sur l'autre mois cela me compte deux périodes
Exemple avec le mois d'avril : 27/28/29/30 en CF puis 31 vide et 01/02 mai en CF cela me compte deux période au lieu d'une.
Je pense donc que le problème vient qu'une cellule est vide au milieu de la période.....
Enfin comment pourrais je mettre les mois en majuscule dans les cellules A7 à A19 ?
J'oubliais si j'ajoutais le mois de janvier 2017, c'est parce que les congés de 2016, peuvent dépasser jusqu'au 2 janvier 2017. Mais comme il ne peut s'agir que de CA ou CAS, je n'ai pas besoin de comptabiliser cette période.
Quand aux CA et CA. cela sert à les comptabiliser,car il doit y avoir sur l'année 25 CA, et au maximum 35 CA + CA.
Voilà quelques explications que j'avais omis d'indiquer volontairement car cela me semblait compliquer les choses.
Bonne fin de soirée
et encore merci
Noberlingo
Bonsoir,
On va y arriver....
En fait il y avait un petit bug dans la formule pour le calcul des chevauchements d'un mois sur l'autre : dans la formule je teste le dernier du jour du mois précédent : SI(GAUCHE(INDIRECT(ADRESSE(LIGNE()-1;1+$AG6;4;2;));2). En clair il s'agit de trouver les 2 premiers caractères de la cellule se trouvant dans la ligne -1 et dans la colonne 1+AG6 (AG6 étant le nombre du jour du mois précédent). C'est là qu'il y avait le bug : j'avais mis le nombre de jour du mois en cours (AG7) d'où le test pouvait tomber sur le jour inexistant d'un mois. Pour rappel : ce sont des références relatives donc quand je parle de AG6 ou AG7 : il s'agit d'un exemple qui est celle de la première cellule du tableau qui est dupliquée dans toute la matrice et les référence s’adaptent automatiquement.
C'est donc corrigé.
As tu besoin de plus d'explication sur la formule ? Un conseil : décompose là par fonction et tu comprendras mieux (va voir les explications dans l'aide dans les fonctions. Cela te permettra de progresser dans la connaissance d'Excel.
J'ai également aménagé pour avoir les mois en majuscules (et cela à quelque peu compliquer encore les formules car Excel connait les mois en minuscules et pas en majuscules ... c'est comme çà).
J'ai donc été forcé de créer une petite table dans un onglet distinct et d'aménager les formules se trouvant en colonnes A et AG (celle-ci se basait sur la A et comme la A ne donne plus la date mais le mois il fallait recalculer la date dans la colonne AG. Cela n'a l'air de rien de passer de minuscule en majuscule mais ce n'est pas si évident...
Je t'ai remis également le mois de janvier de l'année +1 puisque c'est quanf même plus propre pour ton travail et cela ne devrait pas poser de problème.
J'ai également mis la formule pour faire apparaître les WE en bleu : si cela ne te plaît pas il suffit de l'enlever dans la mise ne forme conditionnelle (c'est la première). A remarquer que même l'année +1 est correctement calculée pour les WE.
Reste le problème du mois de février : es tu certain que les tableaux dans lesquels tu copies on bien la même année de référence en B5 ?? Car si tu as un coup 2015 et un coup 2016, tu vas passer de 28 à 29 jours ET C'EST NORMAL. Ou encore : as tu nommé la cellule B5 (fusionnée avec C5) en l'appelant AN dans tous tes tableaux. Si tu ne l'a pas fait, cela ne peut évidemment pas marché puisque la formule cherche les informations dans une cellule appelée AN. Je pensais t'aider en mettant un nom mais si tu as des difficultés à gérer les noms, je peux remettre la référence de la cellule en dur (B5).
Si tu as bien la même année : il faut m'envoyer les fichiers car je ne vois pas du tout ce qui pourrait entraîner cela.
J'attend de tes nouvelles
A+
Chris
Bonjour
Un peu de temps aujourd'hui alors je me suis remis dans mes tableaux.
Dans ton modèle, si tu fais un copier de AG7 /AG19 pour le coller en AG31, février indique 28 ( voilà mon soucis,car en plus cela se reproduit une fois sur deux dans mon grand tableau)
De même si je fais un copier de B7/AF19 pour le coller en B31, collage spécial / format ( reproduire les WE grisé de ma MFC) cela ne reproduit pas la même chose sur janvier 2017, cela reproduit janvier 2016.
Enfin j'ai remarqué que le 31 avril et 31 septembre était colorié en bleu dans le tableau pour le week end. J'ai donc dans la MFC mis en tête de liste le coloriage en noir de ces cellules et çà à l'air de fonctionner....
Je continue mes recherches...
Bonne journée
Norberlingo
Bonsoir,
Petit à petit l'oiseau fait son nid...
Voici le fichier rectifié. J'ai mis à jour les commentaires avec les explications.
En gros, je ne savais pas que tu allais déplacer en hauteur le tableau et la méthode que j'avais choisie s'appuyait sur le numéro de ligne. Forcément le déplacement en hauteur provoquait des catastrophes dans le calcul de la date.
J'ai changé de méthode de calcul pour ne plus dépendre du numéro de ligne et maintenant tu peux copier la colonne AG. Note qu'une autre solution aurait été de simplement mettre une formule du genre =AG7 pour ne pas avoir de souci mais j'ai quand même préféré rendre la formule universelle.
Tu verras les explications dans les commentaires.
La MEFC a été également revue.
J'espère que ce coup ci on approche...
A+
Chris
Bonjour
J'avais répondu à ton dernier message mais je m'aperçois aujourd'hui qu'il ne figure pas dans les envois et donc qu'il n'apparait pas sur le forum. Etrange, je ne comprends pas pourquoi....j'ai du faire une mauvaise manip
Je suis désolé .....dans un premier temps je te remerciais pour tout le travail accompli.
J'ai passé mon week end à mettre mon tableau à jour avec tes nouvelles formules...et tout fonctionne à merveille...un grand merci car sans toi je n'en serais jamais arrivé là.
J'ai essayé dans un deuxième temps de traduire les formules, pour bien les comprendre et là j'avoue que c'est un peu plus compliqué....mais je ne désespère pas.
Je voulais aussi essayer de mettre une dernière MFC. Mettre une alerte sur les CAS .
En effet ces derniers ne sont acquis que de la manière suivante. Le premier CAS si et seulement si 5 CA au minimum sont positionnés entre début janvier et le 30 avril et/ou le 1er novembre et fin décembre. Le second est obtenu si et seulement si 8CA sont positionnées comme précédemment. Et ces CAS ne peuvent être mis en place que sur les mois de janvier à avril et novembre à décembre.
Je "tatonne".
Voilà j'espère que tout va bien de ton côté et que la situation va redevenir petit à petit normal.
Bonne soirée et encore merci.
Norberlingo