XL 2013 Supprimer des sauts de ligne vierge d'une cellule qui contient une formule

[ANTONY34200]

Bonjour,
j'ai créé un planning sous excel, pour ma femme qui reprend une PME en sécurité privée, ou je pense avoir déjà bien bossé dessus.
Je ne suis pas un expert, mais j'ai créé ce planning en consultant pas mal de forum. Le planning est fonctionnel, MAIS, car il y a un mais, j'aimerais y apporter quelques améliorations.
Le fichier joint, comporte 3 onglets (paramètre, général et agt 5)

L'onglet général, est en fait, le planning général, et c'est la que toutes les informations sont saisies.

L'onglet AGT 5, est le planning individuel de l'agent. Des cellules D8 à D39 ont une formule qui appelle le nom du site SI l'agent est planifié dans le planning général, pareil pour les heures de début (F8 à F39) et les heures de fin (G8 à G39).

Mon soucis est que j'aimerais, dans les cellules D8 à D39, F8 à F39, et G8 à G39, quand l'agent est planifié, les sauts de lignes inutiles ou vierges (dans les cellules)soient supprimées, sinon les cellules sont bien trop grande.

J'aimerais aussi savoir s'il est possible de classer par ordre chronologique d'heure de début (dans la ligne) et que les sites et les horaires correspondent.

Je ne suis pas expert, mais je pense toucher un peu, mais la... je bloque. j'ai cherché un peu partout une piste, mais je n'ai rien trouvé.
Si quelqu'un pouvait m'aider un peu ... se serai avec plaisir.
A bientôt, et merci d'avance.

 

[ANTONY34200]

Je vais peut être abuser de votre savoir, mais je vais vous le demander quand même.
1) dans le général j'ai une cellule fonction. j'aimerais, si possible et si l'envie vous dit, de le faire s'incrémenter en face le site comme pour les heures. (le bon site avec la bonne fonction, la bonne heur de début et la bonne heure de fin) j'ai essayé, mais j'ai tout fait buguer. ca fait 1 heure que je suis dessus et je ni arrive pas.
J'ai aussi afficher les colonnes J à R. elles sont pleines de formules.
2) Est ce possible de modifier les formules pour les transformer en VBA
3) Dans la colonne J c'est il doit y avoir le nombre d'heures effectuées en ASCCI en relation avec le général
4) dans la cellule I ,les paniers, est ce possible de passer en VBA, suivant la formule

*dans les formule il y a plein de #ref, car j'ai modifié les site dans le général

Je vous remercie du temps que vous m'avez consacré, et je vous remercie par avance si vous m'aider, ou plutôt me faites, mes codes VBA.

Bonne soirée et encore merci.

 

[sylvanu]

Bonjour,
Alors en vrac :
1- post #1 : les formules sont identiques mais le résultat est différent. Dans : =Antony($D$5;LIGNE()-2;1) il y a Ligne() qui donne le N° de ligne à utiliser.
Donc en D9 Ligne()=9 et en D10 Ligne()=10. Donc le paramètre est différent, ce qui change le résultat.
2- post #16 - 1 : où se trouve cette cellule où il y a la fonction ? je ne trouve pas à part I,N,S ...
3- post #16 - 2 : je suppose que vous parlez des formules dans les cellules I4, N4, S4 ... ?

Je vais passer en VBA les formules en AGT5 colonne H et I.
Et celles de Général colonnes I, N, S , X ....

Pour le reste précisez. ( le mieux est de se référer toujours à Nom de feuille, N° de cellule, c'est plus précis pour la compréhension.)

 

[sylvanu]

Re,
Dans votre formule en H9 vous avez un terme qui est 24/24. Qu'avez vous voulu faire ?
Puis je remplacer GENERAL!I7+24/24-GENERAL!G7 par GENERAL!I7-GENERAL!G7 +1 ou il y a un piège ?

 

[sylvanu]

Re 2,
Pour le panier vous avez la formule =SI(SIERREUR(SI(CHERCHE($D$5;GENERAL!E6);SI(GENERAL!I6>GENERAL!G6;GENERAL!I6-GENERAL!G6;MOD(GENERAL!I6+24/24-GENERAL!G6;1))<$O$1;;1)
Qu'est ce que veut dire <$O$1 et qu'avez vous voulu faire?
O1 contient 05:59. Est ce à dire qu'au delà de 05:59 on doit payer un panier ?

Hé ouais, à un moment il faut rentrer dans les détails.

 

[ANTONY34200]

Bonjour Sylvanu
Pour répondre a vos interrogation.

2-post #16 => dans le l'onglet général, en G4, L4, Q4, ... du fichier que je vous est renvoyé. Les formules de I4 du general, servent à calculer la somme d'heures effectuées sur le site, dans le mois. (un repère pour la facturation en fait)

#18 =>en H9 de AGT5, la formule calcul le temps de travail global de la journée, mais attention, l'agent peu commencer le 03/05 de 17:00 à 18:00 soit 1:00 et aussi faire 20:00 pour se terminer le 04/05 à 03:00, soit 5:00 soit un total de 6:00

#19 => OUI le calcul du panier se fait de la façon suivante : tout temps de travail supérieur à 05:59 et sur le même site donne doit à un panier. dans mon exemple juste avant, le panier ne doit pas se déclencher.

Pas de soucis, je peu rentrer dans les détails, je connais par cœur ce planning, je l'ai créé de toute pièces. j'ai passer plus de 100h à le créer, y apporter des améliorations ... je peux et je me dois d’être précis.

Merci et bon courage.

 

[sylvanu]

Bonjour,
En PJ un essai.
J'ai modifié un peu la syntaxe pour être plus simple :

Feuille AGT 5
D9 F9 G9 : =Antony($D$5;LIGNE();Paramètre) avec Paramètre
=1 Site
=2 Début
=3 Fin
H9 I9 : =hrs($D$5;LIGNE();Paramètre) avec Paramètre
=1 Heures
=2 Panier
Sur ces formules rien à toucher hormis la cellule qui contient le nom de l'agent.

Feuille Général :
I5 : TempsSurSite(COLONNE()) Rien à toucher

A vérifier. Si problème spécifiez feuille et cellule, et fournissez un fichier où est l'erreur.
Documentez l'erreur pour simplifier les choses.
Pour faire simple, dans l'éditeur VBA j'ai créé un module par fonction. ( VBA Ed. ALT + F11 )
Maintenant vous n'avez plus le choix, il va falloir vous y mettre.

 

[ANTONY34200]

Alors, je viens de tester. Pas mal de petites choses ...
1) Concernant l'incrémentation de la fonction, qui se trouve sur GENERAL en G (la fonction APS, SSIAP 1, ASCCI, ...), je suis allé en VBA Fonction_Antony, j'ai bidouillé quelque chose, qui fonctionne a moitié. il y a juste une fonction qui s’incrémente sur AGT 5 en E. je n'arrive pas, a faire s'incrémenter les autres fonctions des différents site du GENERAL.
2) Dans AGT 5, si je planifie sur le GENERAL 2 sites différents le même jour, H et I = #VALEUR
3) Dans AGT 5, pour le calcul de I, j'ai modifié dans VBA, le 0,25 par 0,2499, car dès que le temps de travail ateind ou dépasse 6:00, un panier est acquis.
4) Dans GENERAL, en I, le calcul des heures du site, sitôt que ça dépasse les 24h, ça repart à 0, de plus, j'aimerais que le calcul se fasse en centièmes.
5) Dans AGT 5, la cellule H ne se calcul plus si 2 sites sont planifié le même jour.
6) Dans AGT 5, lacellule I ne calcul plus si l'agent à droit à 2 paniers ( 2 vacations distinctes de 6:00)
7) Dans AGT 5, la cellule J, j'aimerais que dans GENERAL si l'agent est planifié sur un site avec la fonction ASCCI, le nombre d'heures correspondante s'incrémentent et si l'agent est planifié le même jour sur 2 sites ASCCI, les heures s'additionnent.

J’espère ne pas être trop confus dans mes explications, et je te remercie encore du travail, qui est excellent, et qui va grandement aider ma femme.

 

[sylvanu]

Re,
Je me suis aperçu que dans la fonction Antony on avait toutes les infos pour calculer le nombre d'heures et les paniers, donc j'ai mis en paramètre 5 pour les heures et 6 pour les paniers.
Par contre je ne comprends pas le 4, si un agent a travaillé plus de 24h sur un site on ne mets pas le nombre d'heures mais le nombre d'heures modulo 24H ? C'est à dire si le temps est de 23H on met 23H s'il est de 25H on met 1H ?
C'ext quoi quoi calcul qui se fasse en centièmes? on ne met pas 05:30 mais 5,5 ?
Et pour la 7 ??? La cellule J de AGT5 est vide. Ce serait une nouvelle fonction ?
En PJ rectifié pour les Hrs et Panier.
Pas mal la modif pour Parmètre=4 avec déclaration d'array, vous vous y mettez !

 

[ANTONY34200]

En Fait
1) dans GENERAL en I4, N4 ... il faudrait, dans l'absolu, que sa affiche le cumul des heures effectuées sur le site. (exemple : pour le site 1 s'il est planifié tous les jours du mois 2:30 par jour il devra s'afficher 77,50 soit 2,5 h x 31 jours)
2) Dans AGT5 en J9, c'est le calcul des heures effectuées avec un chien (ASCCI). dans le cas en PJ dans la cellule J9 il devait y avoir 12,00 (soit 6:00 du site 1 + 6:00 du site 3) et dans la cellule J10 il devrais y avoir 6,00. et dans J5 c'est la somme de J8 à J39
3) dans AGT5 en H5, j'aimerais avoir la somme de H9 à H39, dans le cas en PJ dans la cellule H5 il devrait y avoir 24:00 (soit 18:00 + 6:00) et s'il y avait 18:00 + 10:00 il devrait y avoir en H5 28:00.

Je suis bien obligé de mis mettre, mon but est de comprendre le système afin de pouvoir modifié si besoin. Avec mon ancien fichier, avec toutes les formules dans chacune des cellules, le fichier devenait trop lourd ... car dans la réalité, il y a plusieurs onglet AGT. Il y en 15 pour être franc.

 

[sylvanu]

Je retire ma question sur les centièmes. C'était débile.
En PJ ce point rectifié. On a bien 77.50 pour 31* 2:30
Pour le reste je regarde.

 

[ANTONY34200]

Ok, super, merci beaucoup

 

[sylvanu]

Re, une nouvelle version.
La prime chien peut être calculée à partir des données déjà existantes. Donc la prime chien prend le paramètre 7.
Plus quelques bugs corrigés pendant les essais, en particulier quand les temps de travail dépassent 12H.

 

[ANTONY34200]

Ca me semble bien.
Par contre dans AGT5 en H5, la somme de H8 à H39 n'apparait pas ...

 

[sylvanu]

Oups,
Comme le contenu sont des fonctions, somme ne marche pas car les formules ne sont pas ré évaluées.
J'ai mis simplement =24*(H8+H9... +H38)

Par contre avec 15 agents, ça risque de ramer sérieux.
Si ce n'est pas supportable, il y a des solutions, comme passer par des macros WorksheetSelect et ne recalculer que la page sélectionnée.

 

[ANTONY34200]

Ok, super ... merci.
qu'est ce que vous entendez par ramer sérieux ?? sa risque de ramer plus qu'avec l'ancien système avec les formules dans chacune des cellules ????