Automatisation fichier excel

marjorie123

XLDnaute Nouveau
Bonjour à tous,

J'aimerais avoir votre aide sur l'usine à gaz que je souhaiterais créer. Evidemment, il va sans dire que je suis très loin d'être une pro d'excel (je ne viendrai pas vous demander de l'aide sinon !), aussi j'espère que je vais être suffisamment claire dans mes explications... Je vais faire de mon mieux !

Voilà, j'ai un Tableau 1 dans le 1er onglet, avec toutes les informations dont j'ai besoin : agence, nom, date d'entrée, coefficient, taux horaire...

2lqqvb.jpg

Dans un premier temps, je souhaiterais afficher quelques-unes de ces informations dans un Tableau 2, dans le 2ème onglet, mais suivant 2 critères :
- 1er critère : le nom de l'agence
- 2ème critère : si "oui" est inscrit dans la colonne O (Feuil1)

Donc, je veux que s'affiche dans mon tableau toutes les personnes de l'agence "A" qui sont cochées avec un "oui". Dans un autre onglet, les personnes que je veux de l'agence "B", etc.
J'avais démarré avec cette formule :
=SI(LIGNES($A$4:$A4)>NB.SI(Feuil1!$O$1:$O$300;"OUI");"";PETITE.VALEUR(SI(Feuil1!$O$1:$O$300="OUI";LIGNE($O$1:$O$300));LIGNES($A$4:$A4)))

Mais ici, il n'y a qu'un critère sur les 2 de rempli...

2qid1jl.jpg

Ceci est mon premier souci…

Mon second soucis est que j'aimerais que des tableaux, style facture, s'affichent ensuite, pour chaque personne que j'ai sélectionnées de l'agence "A". Une facture de ce type là (avec évidemment, les données du Tableau 1 reportées et les formules qui vont bien):

2vjzbc7.png

Ainsi, si j'ai 3 gars affichés dans mon Tableau 2, il faudrait que 3 "factures" apparaissent. Si j'ai 5 gars, que 5 "factures" apparaissent, etc.
Je pense que ce serait bien plus évident avec Access, mais je ne l'ai pas… Avec des macros peut-être ? Si les cellules de la colonne A du Tableau 2 ne sont pas vides, alors la facture apparaît.

Auriez-vous des idées ?

Je vous joins le fichier excel pour que vous vous rendiez mieux compte.

Merci beaucoup de l'aide et des conseils que vous pourrez m'apporter.
 

Pièces jointes

  • Classeur1.xlsx
    24.2 KB · Affichages: 67
  • Classeur1.xlsx
    24.2 KB · Affichages: 76
  • Classeur1.xlsx
    24.2 KB · Affichages: 53

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

Bonjour Marjorie,

Pour ta première question (avançons un pas à la fois, si tu veux bien! :)), modifie ta formule comme suit:
Code:
=SI(LIGNES($A$4:$A4)>NB.SI(Feuil1!$O$1:$O$300;"OUI");"";PETITE.VALEUR(SI((Feuil1!$O$1:$O$300="OUI")*(Feuil1!$A$1:$A$300=Feuil2!$C$2);LIGNE($O$1:$O$300));LIGNES($A$4:$A4)))
Toujours à valider comme formule matricielle.

Attention: en colonne A de ta feuil1, les "A" sont suivis d'une espace ... la formule n'affichera un résultat que lorsque tu auras supprimé ces caractères superflus
 

marjorie123

XLDnaute Nouveau
Re : Automatisation fichier excel

Merci Modeste ! C'est super ! Nous avançons un pas à la fois, mais nous avançons ! merci !

La formule marche, les lignes pour l'agence A sont parfaites. Par contre, sur le Tableau 2, des "#NOMBRE!" apparaissent sur les lignes suivantes (lorsqu'il y a un "oui" dans le Tableau 1 et que l'agence est une autre que A).
Une idée ?
 

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

Re,

marjorie123 à dit:
Oh ben une idée, ça ne coûte rien :) Si tu travailles avec Excel 2007 et au-delà, tu peux utiliser
Code:
=SIERREUR(PETITE.VALEUR(SI((Feuil1!$O$1:$O$300="OUI")*(Feuil1!$A$1:$A$300=Feuil2!$C$2);LIGNE($O$1:$O$300));LIGNES($A$4:$A4));"")

Mais si tu ouvres le fichier sous une version antérieure, le SIERREUR ne sera pas reconnu! Dis-nous si ça risque d'être le cas!?

P.S.: tu es consciente que des formules matricielles, sur un volume important de données, risquent de ralentir les calculs? Comme on ne sait rien du volume en question, autant se poser la question à ce stade.
 

marjorie123

XLDnaute Nouveau
Re : Automatisation fichier excel

Merci beaucoup, ça marche nickel !

Je ne pense pas que le fichier contiendra une tonne de données, le Tableau 1 risque de s'allonger jusqu'à 80 voire 100 lignes, mais au grand maximum. En sachant qu'il n'y aura à chaque fois qu'une vingtaine de "oui" pour la totalité des Agences. Je pense faire 4 ou 5 onglets (par agence, 1 onglet pour "A", 1 onglet pour "B", etc.) Le Tableau 1 sera la base, qui sera permanente, ensuite le Tableau 2 sera mis à jour chaque semaine.

Ca vous paraît être un volume important ?

Sinon, je ne pense pas que le fichier sera utilisé avec une version antérieure à 2007.

Merci Modeste ! =)
 

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

marjorie123 à dit:
Ca vous paraît être un volume important ?
À première vue, je dirais non ... restera à tester (c'est là que tu auras une réponse à la question)

marjorie123 à dit:
Sinon, je ne pense pas que le fichier sera utilisé avec une version antérieure à 2007
Il vaudrait mieux une certitude :)

Pour tes petits tableaux (de factures?), le nombre exact doit apparaître sous le "Tableau2" de chaque feuille ou alors il peut y en avoir un dizaine de "préparés", qui ne se garnissent qu'en fonction du nombre de personnes concernées dans chaque feuille?
D'autre part, en dehors du nom au-dessus de ces tableaux-factures, quelles données doivent y apparaître? C'est toi qui complèteras "à la main"? Dans ton premier message, tu parlais des "données du Tableau 1 reportées" ... mais dans ton exemple, je ne vois pas le lien entre les 2 feuilles :confused:
 

marjorie123

XLDnaute Nouveau
Re : Automatisation fichier excel

Disons que je suis sûre qu'il n'y aura pas de version antérieure à 2007 ;)

Ensuite, pour les "factures", ce que j'aimerais c'est qu'elles se remplissent automatiquement.
Certaines informations seraient à rechercher dans le Tableau 1 ou le Tableau 2 (je les ai mis en jaunes dans le fichier).
Pour le reste, il y a soit des formules, soit des données fixes.
Je vous renvoie un fichier Excel actualisé (avec les formules pour les factures)
Si vous ne comprenez pas mes commentaires (ce qui est fort possible vu que je m'exprime assez mal.... ><), n'hésitez pas à me redemander !

Pour ce qui est de l'affichage, c'est plus esthétique s'il n'y a que les factures pleines qui apparaissent, mais après, ça n'a pas vraiment d'importance, on pourra jouer sur les zones d'impression. Donc, pour ça, je ne suis pas compliquée.

En tout cas, merci beaucoup Modeste du temps que vous prenez pour m'aider, c'est très gentil !
 

Pièces jointes

  • Classeur1.xlsx
    26 KB · Affichages: 51
  • Classeur1.xlsx
    26 KB · Affichages: 59
  • Classeur1.xlsx
    26 KB · Affichages: 61
Dernière édition:

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

marjorie123 à dit:
pour ça, je ne suis pas compliquée
Mais que voilà une nouvelle qu'elle est bonne :D

Bon, avec tes contraintes (même si tu laisses un soupçon de lattitude), j'ai renoncé à essayer de le faire par formules. Du coup, plutôt que des formules matricielles (puisque je suis de toute manière occupé avec une macro), j'inscris les n° de ligne dans un Tableau au moment où la feuille d'une agence est activée. Dans la foulée, je crée la série des petits "tableaux-factures".

Dans une feuille nommée ModFacture, j'ai sauvegardé un "tableau-facture" vierge. Je ne me suis occupé que des trois premières cellules en jaune (pour la 3e, il faudrait déjà que tu vérifies si ce qui y figure est correct ... après que tu auras activé la feuille "A" qui ne contient rien, à ce stade, sauf les titres du tableau)

En pondant ces quelques lignes de code, j'ai tout de même réfléchi (si, si! ... Mais pas longtemps, hein!). En relisant un de tes messages, j'ai lu "le Tableau 2 sera mis à jour chaque semaine" :eek::eek: Avec mon système, il est actualisé chaque fois que la feuille "A" est activée (avec des formules il l'était en permanence!) ... Quoi qu'il en soit, au moment où il est actualisé, que deviennent les tableaux-factures de la feuille "A" :confused: Si tu as modifié les données du Tableau1, certains tableaux disparaîtront, d'autres s'ajouteront?
Par ailleurs, si je "colle" mon modèle vierge chaque fois que la feuille est actualisée, je vais (forcément) effacer des données que tu aurais encodées "à la main" dans les tableaux-factures ... en foi de quoi, mon système n'est pas valable!

Ce que je te propose c'est de voir si ce qui est fait à la première activation est déjà correct (ou pas!?)
Si oui, on peut utiliser un système de bouton, pour commander la mise à jour des feuilles d'agences. Si je me suis "vautré", pas la peine que je continue de m'agiter inutilement :p

À l'ouverture du fichier, la feuille "A" devrait être visible (et vide, à peu de choses près). Active les macros, sélectionne une des autres feuilles, puis reviens en feuille "A". Elle devrait "se garnir" dans les fractions de secondes qui suivent.
 

Pièces jointes

  • Dispatcher Agences (marjorie123).xlsm
    41.9 KB · Affichages: 53

marjorie123

XLDnaute Nouveau
Re : Automatisation fichier excel

Merci,

Alors pour répondre à ton interrogation, j'ai dit que le fichier serait actualisé toutes les semaines, mais en fait, ça veut seulement dire que la personne qui sera en charge de le remplir viendra sur ce fichier 1 fois par semaine pour le mettre à jour, le reste du temps, il ne sera pas touché.

Logiquement, rien ne devrait être rentré à la main dans les "factures" puisque ce sont soit des formules fixes, soit des données fixes, peu importe la personne. Tout est bon dans le deuxième fichier excel que j'ai joint plus tôt.

J'ai regardé un peu, j'ai activé la macro au démarrage, "A" était vide, j'ai changé de feuille et j'y suis retournée, "A" s'est rempli tout seul comme par magie. Pour l'instant, j'arrive à te suivre. Ensuite, je me suis un peu perdue... Lorsque j'essaye de supprimer des "oui", tous les gars de l'agence A restent dans "A", et lorsque j'ajoute des heures dans le tableau 2 (de l'onglet "A"), les mini factures en bas ne changent pas... J'ai essayé de visualiser la macro, mais elle n'apparaît pas dans "développeur / macro". Pouvez-vous me guider un peu plus ?

Sinon, le principe en lui-même me convient parfaitement !
Merci Modeste de vous pencher sur mon cas, et de m'aider à réaliser ce projet ! =)
 

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

Bonsoir,

Toutes mes confuses :p J'ai effectivement omis de prendre en considération, dans le code, la présence des "oui". La bonne nouvelle c'est que c'est vite corrigé!

Pour voir le code, dans le cas présent, le plus simple est certainement de cliquer droit sur l'onglet de la feuille "A" > Visualiser le code...
Une fois le code affiché, modifie la ligne 10 comme suit:
Code:
If .Cells(lig, 1) = agence And .Cells(lig, 15) = "oui" Then

Re-teste ensuite en passant d'un onglet à l'autre et en modifiant des "oui", pour t'assurer que le contenu de la feuille "A" se met bien à jour comme attendu (y compris le contenu des 3 cellules jaunes ... enfin la 3e, les autres je crois que c'est bon!)

Si c'est ok, j'essaierai de compléter les autres cellules jaunes, sur base des commentaires dans ton dernier fichier et je verrai comment ne lancer la macro que sur demande.

De ton côté, fais-nous savoir si la liste des agences est définie une fois pour toutes ou si cette liste va changer d'une semaine à l'autre. Pas de problème si une agence existe (et donc si une feuille lui est associée), mais que parfois aucun nom n'y est associé ou qu'il n'y a aucun "oui". Par contre, si des agences apparaissent/disparaissent, ça va se compliquer.

Demain, la journée sera "chaude" (et je ne parle pas de la météo :() ... la suite ne viendra peut-être que samedi?

Bonne soirée,
 

marjorie123

XLDnaute Nouveau
Re : Automatisation fichier excel

Bonjour et merci Modeste ! Comme cela, tout marche parfaitement !
Les calculs se mettent à jour dans les factures et les "oui" fonctionnent ! Hehe, à ce stade, je suis vraiment épatée par ton savoir excellien !!

Nous avons 6 agences, même si actuellement nous ne travaillons pas avec toutes, je ne pense pas qu'il y en aura d'autres qui s'ajouteront. Après... au cas où 1 ou 2 agences s'ajouteraient, serait-il possible de créer un ou deux pages avec un nom "bidon" ? Comme cela, si un jour une nouvelle agence arrive, la page sera déjà là, à l'attendre ?

Ensuite, les noms des agences sont fictifs évidemment, personne ne peut s'appeler "A" ou "B" en vrai =P. Il faudra que je change le nom des agences dans les codes ? C'est ça ? Je m'avance un peu mais cette question me turlupine ! =)

Et ne t'inquiète pas, ce n'est pas un projet urgentissime, ce n'est pas à un jour près et tu m'aides tellement (et gratos ! ;)) que je ne me permettrais pas de te mettre une dead-line !!

Encore merci Modeste !
 

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

Bonjour,

cette question me turlupine ! =)
Il n'y a pas de raison :D je prévoyais de t'expliquer comment faire en cas d'ajout ... mais l'explication viendra quand tout sera "goupillé" correctement (si c'est possible :p)

À plus tard pour la suite (sauf si d'ici là une solution a été fournie par d'autres)
 

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

Bonjour Marjorie123, le forum, les adorateurs d'Excel et même ceux qui passeraient ici par erreur,

Voici le fichier mis à jour:
  1. ce n'est plus l'activation d'une feuille agence qui provoque la mise à jour, mais un bouton placé en Feuil1. La macro met d'abord à blanc les différentes feuilles, avant de les "regarnir". Le contenu de chaque tableau "récap par agence" est effacé (sauf les formules de la première ligne) et tous les "tableaux-factures" sont supprimés.
  2. j'ai tâché de tenir compte de tes commentaires pour afficher le contenu attendu des cellules jaunes. J'ai gardé des formules, lorsque ces données sont liées au contenu du tableau de la même feuille. Quand les données proviennent de la Feuil1, c'est une valeur qui est inscrite via la macro. Les formules limitées au tableau-facture lui-même ont été conservées comme dans le modèle (cellules blanches).
    Vérifie (deux fois plutôt qu'une :eek:) que les résultats sont les bons dans les 3 cas!
  3. dans tes tableaux par agence, une colonne sans titre existait ... je l'ai conservée, mais la conversion de la plage en tableau fait que le titre "Colonne 1" lui a été attribué automatiquement. Tu peux modifier ce titre à ta guise. Attention cependant à ce que la suppression de cette colonne nécessiterait d'apporter des modifs au code. Comme je ne connais pas le contenu possible de cette colonne, son contenu n'est pas effacé lors de la mise à blanc de chaque feuille ... à voir, donc!
  4. j'ai commenté un peu le code (mais surtout pour m'y retrouver moi-même! ce n'est pas seulement une usine à gaz, c'est en plus un casse-tête chinois, par moment :p). Comme pour les explications, je complèterai les commentaires quand l'ensemble sera fonctionnel.
  5. j'ai déjà prévu un modèle de feuille pour les agences qui s'ajouteraient par la suite (mais n'en ajoute pas encore: je t'expliquerai, comme promis)


Il me semble utile de repréciser que les modifications apportées par la macro ne peuvent être annulées ... en cas de "boulette", la seule solution est de fermer le fichier sans enregistrer les modifications! Faire systématiquement une copie du fichier serait prudent -dans un premier temps en tout cas- avant de cliquer sur le bouton.

Je file voir si je trouve un bout de rayon de soleil!
 

Pièces jointes

  • Dispatcher Agences (marjorie123) V2.xlsm
    55 KB · Affichages: 43

marjorie123

XLDnaute Nouveau
Re : Automatisation fichier excel

Bonjour Modeste !
Je vois ton message seulement maintenant, as-tu pu profiter d'un bon bout de rayon de soleil ?

Merci pour le fichier, c'est géant de voir ça ! Le bouton marche parfaitement, ça me fait triper de cliquer ;)
Je vais essayer de donner mes remarques et interrogations :

1. Je vais essayer de récapituler pour voir si je comprends bien. Quand tu récupères des info dans le tableau 1 --> la formule est dans la macro, lorsque tu récupères des info ds le tableau 2 ou directement dans la facture --> les formules sont pompées à partir de l'onglet Modfacture. J'ai bon ? Je me demande ça parce que je me rends compte que les formules de la colonne F de la facture ne sont peut-être pas optimales. Mais si je peux les modifier par moi-même, je ne veux pas t'embêter avec ça.

2. Je me rends compte que la formule pour les HS 25% (ligne 12) ne convient pas et je n'arrive pas à la formuler correctement.
Je voudrais que : si J4 est compris entre 36.5 et 43 --> mettre le delta. Donc si quelqu'un a travaillé 40h, 3.5h doivent apparaître, s'il a travaillé 44h, le delta doit être de 6.5h (l'heure restante doit être sur la ligne HS 50%). Mais si il a fait moins de 36.5, rien ne doit apparaître. Et c'est cette dernière partie que je n'arrive pas à formuler.
J'ai ça : =SI(ET(J4>36,5;J4<=43);J4-36,5;43-36,5) --> si le gars fait 44h, c'est okay, s'il fait moins par contre, ça ne va plus, le "43-36.5" s'applique et il y a 6h50 qui se ballade alors que ça devrait être vide.
As-tu une solution ?

3. Je pense que les lignes 12 et 13 (25% et 50%) seraient à inscrire dans la macro pour la colonne "Base nb hrs" car les formules ont pour base le nb total d'heures par semaine du Tableau 2 (J4). Je n'aime pas trop l'idée de gonfler encore plus la macro car j'aurais moins de latitude pour changer les formules si besoin mais... bon, je pense que c'est ce qu'il faut faire tout de même ?
Formule ligne 13 =SI(J4>43;J4-43;"")

4. La ligne 10, colonne "base nb hrs" affiche bien la cellule "nb total d'heures par semaine", mais je souhaiterais qu'il y ait une formule également =SI(J4>=35;35;J4)

5. le coefficient (D10) n'apparaît pas...

6. Pour ce qui est de la colonne vide oui... je ne pense pas qu'elle a lieu d'être. Mais si (par exemple), je souhaite la déplacer et la mettre derrière "nb d'heures semaines" est-ce que ça va tout faire planter ?

7. j'ai regardé la macro mais je ne m'aventurerai pas à la modifier encore, j'attends tes précieuses directives !



Je ne vois pas d'autres remarques (c'est suffisant, déjà, non ? ;)). Je ne sais pas comment tu arrives à me suivre, je m'embrouille moi-même ! Merci encore une fois de m'aider Modeste et de prendre le temps de résoudre mon casse tête chinois !!!
 
Dernière édition:

Modeste

XLDnaute Barbatruc
Re : Automatisation fichier excel

Bonjour,

Je ne sais pas comment tu arrives à me suivre, je m'embrouille moi-même !
Mais c'est très simple, en fait ... je n'y arrive pas ;)

Je ne répondrai pas à tout (toujours un pas à la fois, tu te souviens? ... c'est pour ça que je cherche le soleil: comme les lézards, je bouge une patte toutes les 10 minutes)
- dans le "Tableau 2" des formules existent dans la ligne unique existant au départ. Dans les "tableaux-factures", les infos provenant du "Tableau 1" sont copiées par la macro (sans formule). C'est le cas de la colonne "Taux/Prime horaire" (4 cellules jaunes).
- Dans ces mêmes "tableaux-factures", une formule est insérée dans la colonne "Base Nbr Hrs" qui permet de recopier l'info du "Tableau 2" (2 cellules jaunes).
- Le "coefficient" n'apparaît pas ... parce qu'il n'y avait pas d'explications, dans ton fichier précédent. J'ai donc considéré que c'était une valeur encodée par tes soins.
- Les autres formules sont celles qui figurent dans le ModFacture. Tu peux les modifier à ta guise (en pensant bien que c'est le même tableau qui est recopié toutes les 15 lignes, dans chaque feuille ... à tester, donc) ... Profites-en pour vérifier la formule des "TAUX Facturation", notamment celui des heures sup à 50% :confused:

- je crois avoir compris ce que tu souhaites aux points 3 et 4 ... J'y regarde ... sous peu (ça ne mange pas de pain, comme formulation:))

- Pour la question 2, que donnerait
Code:
=SI(J4<36,5;0;MIN(J4;43)-36,5)
... si j'ai compris tes explications!?


Quand tu parles de lignes (10, 12 ou 13) ce serait bien que tu précises dans quelle feuille ... là j'ai fait des "hypothèses qui me semblaient raisonnables"
 

Discussions similaires

Réponses
4
Affichages
168

Statistiques des forums

Discussions
311 715
Messages
2 081 822
Membres
101 822
dernier inscrit
holale