Sommer des valeurs suivant des critères imbriqués
Bonjour,
Je me tourne vers vous aujourd'hui car je rencontre un problème sur Excel. J'ai besoin de faire des sommes suivant plusieurs critères. Vous trouverez en PJ, l'exemple de fichier en question.
La feuille 1 correspondant aux données remplies par l'utilisateur quotidiennement pour assurer le pointage des personnes en entrant donc le service de la personne, nom, prénom, nombre d'heure dans la journée et tâche dans la journée.
La feuille 2 est la feuille sur laquelle je veux que l'analyse se trouve. Il me faut sommer le nombre d'heure en fonction du mois en cours, du poste et du service en question (1 poste peut être utiliser pour 2 services...).
Les noms de tâches sont verrouillés grâce à une liste automatique.
J'avais penser à une somme multi critère avec la fonction =SOMME.SI.ENS, mais je n'arrive pas à traiter avec.
Si vous avez des idées mais que vous voulez faire une mise en page différente de la mienne, aucun souci ! la seule chose est que la mise en forme de la feuille 1 ne doit pas changer.
Quelqu'un peut m'éclairer sur le sujet ?
Si vous avez des questions ou que vous voulez que je sois plus claire, n'hésiter pas à me demander !
Merci d'avance et bonne journée
Hugo
Bonjour,
pas simple pour 2 raisons :
- la structure de données n'est pas facile à exploiter ... à l'avenir essaie d'opter pour un tableau
- ensuite : tu donnes des mois en texte sans préciser l'année ... il faudra aussi modifier cela
Bonjour,
Oui, c'est vrai que la structure laisse à désirer, mais comment le mettre en meilleur forme tout en étant fonctionnel ?
Pour ce qui est du format des dates, à quel endroit parlez-vous ? ça ne me pose aucun problème de modifier le format de date, sur n'importe quelle feuille.
... mais j'y arriverai ...
D'abord quelques explications sur le fichier ...
Pour les dates :
Pour ce qui est du format des dates, à quel endroit parlez-vous ? ça ne me pose aucun problème de modifier le format de date, sur n'importe quelle feuille.
Feuil2 : j'ai mis des dates de début de mois en précisant l'année également
Feuil1 : j'ai ajouté une ligne que tu peux masquer avec les dates de début de mois pour faire le lien avec la Feuil2 ... en plus cela résout le problème des cellules fusionnées
=SIERREUR(FIN.MOIS(D3;-1)+1;C4)Pour la somme :
La formule est un peu complexe (encore que ...), elle est surtout matricielle (à valider par Ctrl+Maj+Entrée)
=SOMME((Feuil1!$D$4:$BEA$4=B$2)*(SI(ESTNUM(Feuil1!$D$7:$BEA$23);Feuil1!$D$7:$BEA$23;0))*(Feuil1!$E$7:$BEB$23=$A3))Oui, c'est vrai que la structure laisse à désirer, mais comment le mettre en meilleur forme tout en étant fonctionnel ?
L'idéal serait d'avoir un tableau de ce type :
A ce moment là tous les calculs sont plus simples et plus riches via un ou plusieurs TCD.
Reste à résoudre le problème de "convivialité" de l'application car ce n'est pas forcément confortable de renseigner directement cette liste. Dans ce cas, on peut faire un onglet sur une semaine et par personne pour renseigner cette base de données via une petite macro. Nota : il n'y a qu'un seul onglet de ce type qui s'adaptera à la semaine et à la personne considérées.
Bonjour,
Merci pour ton aide et tes conseils.
Ta formule est vraiment cool, mais comment l'appliquer à chaque service (Recherche, Développement et Informatique) ?
Pour la mise en forme du tableau, c'est sûr que ce serait plus facile comme cela, notamment pour gérer les analyses via TCD.
Le problème est que le fichier que je t'ai transmis n'est qu'un fichier "exemple".
Sur l'original, il y a près de 40 000 heures de renseigner avec près de 100 personnes. La méthode "traditionnelle" et "clair" d'Excel est difficile à mettre en place dans un tel cas ? Je parle surtout d'un point de vue de rapidité dans la saisie. Penses-tu que ça serait aussi rapide que le modèle actuel où l'on peut notamment faire des copier-coller afin de pointer les heures rapidement et par équipe ?
Encore merci pour ton attention.
Bonne journée
S'il faut ajouter une condition cela ne pose pas de problème.Ta formule est vraiment cool, mais comment l'appliquer à chaque service (Recherche, Développement et Informatique) ?
Je regarde et te renvoie la réponse.
Sur l'original, il y a près de 40 000 heures de renseigner avec près de 100 personnes. La méthode "traditionnelle" et "clair" d'Excel est difficile à mettre en place dans un tel cas ? Je parle surtout d'un point de vue de rapidité dans la saisie.
Penses-tu que ça serait aussi rapide que le modèle actuel où l'on peut notamment faire des copier-coller afin de pointer les heures rapidement et par équipe ?
Ce sera aussi rapide ... ou aussi lent ! c'est bien la question. Mais je suis prêt à prendre le pari.
Car la formule matricielle proposée pour coller à ta présentation est très consommatrice de ressources et en continu pendant la saisie ! Un TCD a lui l'avantage de ne pas travailler en continu mais fait le calcul d'un seul coup.
100 personnes c'est encore raisonnable. Cela ferait environ 100 personnes x 213 jours x 2 tâches = environ 40.000 lignes pour une année dans la base de données, c'est tout à fait gérable ! Et on pourrait optimiser les mises à jour par un tri sur la base qui permettrait d'effectuer les recherches pour modifications plus rapidement et donc ne pas ralentir la saisie.
On peut aussi faire des saisie par blocs.
Si tu es intéressé, je peux le faire mais cela va me prendre un jour ou deux.
Si ça ne te dérange pas, je veux bien que tu regardes oui
Si tu penses que c'est faisable, je veux bien voir la variante. Après, quand on a saisi les heures, il y a un traitement sur d'autres feuilles excel. On tri par mois les heures de chaque personne pour envoyer en compta, on différencie les propres à l'entreprise et les intérimaires etc.
Je veux bien voir ce que tu peux proposer
Encore merci
Par service
=SOMME((Feuil1!$A$7:$A$23=$B$1)*(Feuil1!$D$4:$BEA$4=B$2)*(SI(ESTNUM(Feuil1!$D$7:$BEA$23);Feuil1!$D$7:$BEA$23;0))*(Feuil1!$E$7:$BEB$23=$A3))en matriciel
en partant d'une base de données, cela simplifiera beaucoup les interfaces !quand on a saisi les heures, il y a un traitement sur d'autres feuilles excel. On tri par mois les heures de chaque personne pour envoyer en compta, on différencie les propres à l'entreprise et les intérimaires etc.
je vais m'y mettre ...
Question : pour gérer 100 personnes, y a t'il un n° matricule, un code attribué à chacun ?
Super merci !
Actuellement, les personnes sont rentrées par :
- Service
- Nom
- Prénom
- Entreprise
- Arrivé le (date en format jj/mm/aaaa)
- Départ du site le (date en format jj/mm/aaaa)
ok on va faire sans n° matricule !
autre interrogation : les 100 travaillent déjà sur le même fichier ? cela doit toujours bloquer à l'ouverture ...
au moins, la version que je vais te proposer permettra de dispatcher plusieurs fichiers, puis les consolider facilement
Non non, seulement 3 personnes se servent du fichier pour incrémenter les heures et tâches des autres personnes. De ce fait, aucun blocage à l'ouverture. Je t'avoue que je pense qu'il serait mieux que l'on ait qu'un seul fichier, cela nous arrive de l'envoyer par mail par exemple.
Encore merci en tout cas !
Petite question subsidiaire :
J'ai bien réussi à intégrer tes formules dans mon tableau, merci !
Tu as raison, ça ralenti le process.. Est-il possible de créer une sorte de macro permettant de lancer les formules uniquement lorsque l'on se rend sur la feuille où les formules sont présentes ?
Encore merci !
Oui c'est possible en passant en calcul manuel.
Mais attention, cette option reste aussi quand on ferme le fichier, il faut donc réactiver le calcul auto.
Conséquence = en ouverture ce sera très long aussi !!
Autre solution = mettre les formules en macro et n'enregistrer que la valeur. Je vais regarder cette deuxième possibilité !
Je travaille en parallèle sur les 2 formules.
Mais je pense plutôt faire les calculs par macros et non passer en calcul manuel.
A suivre ...
Je réfléchis encore sur la meilleure façon d'accélérer les formules matricielle ... pour l'instant tu peux, avant saisie, faire
Formules > Option de calcul > Manuel ... et ne pas oublier de revenir en automatique !
Il faut le faire aussi avant ouverture du fichier pour ne pas ralentir.
Pour la nouvelle version proposée ...