Fonctions SOMME.SI et DROITE sur colonne entière
Bonjour, j'ai un problème que je n'arrive pas à résoudre, et je n'ai rien trouvé qui me permette de le corriger, j'espère que vous pourrez m'aider.
J'ai un classeur avec plusieurs onglets, un pour chaque mois + un total. Sur chaque onglet se trouve une liste de personnes (identifiées par un numéro de matricule unique, un nom, un prénom ainsi que les jours de présence pour chacune d'entre elles.
Exemple :
A B C D
1 000049029 ALLBERT Jean 3
2 000048773 DESPROGES Pierre 0
3 000048972 LABATTE Jessica 19
4 000049048 LAZEREZ Sébastien 19
Sur l'onglet total, je voudrais obtenir donc le total de présence pour chaque personne sur tous les mois réunis, j'utilise donc la formule suivante :
= SOMME.SI (JANVIER!$A;$A ; $A1 ; JANVIER!D:D) + SOMME.SI (FEVRIER!$A:$A ; $A1 ; FEVRIER!D:D) + etc jusqu'à DECEMBRE.
Cette formule parcourt la colonne A dans l'onglet Janvier (argument JANVIER!$A;$A) et regarde si un matricule est identique à celui de la personne concernée dans l'onglet Total (argument $A1). Si tel est le cas, elle somme le nombre de présence contenu en D pour Janvier, et continue ainsi de mois en mois.
Jusqu'à là tout marche bien (même si ma formule n'est peut-être pas optimisée).
Mais là où je rencontre un problème, c'est que le matricule (qui me sert de comparateur entre l'onglet Total et les onglets des mois pour obtenir les jours de présence de la personne concernée et les additionner) peut parfois changer, mais uniquement les deux premiers chiffres.
J'aimerais donc que ma formule ne prenne en compte que les 7 derniers caractères des matricules pour les comparer entre eux. J'essaye donc la formule suivante :
= SOMME.SI (DROITE(JANVIER!$A:$A ; 7) ; DROITE($A1;7) ; JANVIER!D:D) + SOMME.SI (DROITE(FEVRIER!$A:$A ; 7) ; DROITE($A126;7) ; FEVRIER!D:D) etc...
Mais elle ne fonctionne pas :'( Une erreur de syntaxe semble t-il ? Ou bien tout simplement que j'utilise une méthode non appropriée ?
J'espère avoir été suffisament clair, je sais que ce n'est pas forcément évident à comprendre !
Merci !
Edit : fichier exemple : https://www.excel-pratique.com/~files/doc/dvFQ5Exemple.xls
Bonjour,
Afin d'optimiser les réponses, merci de joindre ton fichier (ou un extrait)
Amicalement
Nad
Bonjour,
Si tu joins un fichier, la solution va être plus intéressant. Car, dans ton cas il y a possibilité de faire les formules 3D.
Cdlt,
Je vais essayer de préparer un fichier exemple, car je n'ai pas le droit d'envoyer le fichier sur lequel je travaille, il contient des données confidentielles. Je fais ça au plus vite.
Voici un petit exemple tout simple, avec seulement 3 personnes et 2 mois, janvier et février.
Re,
Question :
1° - Pourquoi rechercher sur le matricule et non pas sur le nom puisque ce dernier est le même ?
2° - Est-ce que chaque tableau est construit de façon identique comme dans ton exemple ?
Si tu retrouves chaque fois les noms dans les même cellules, tu peux faire tout simplement :
=SOMME(JANVIER:FEVRIER!D2)
qui devient
=SOMME(JANVIER:DECEMBRE!D2) pour tous les mois
Amicalement
Nad
Alors, pour répondre à tes questions :
1° - Pourquoi rechercher sur le matricule et non pas sur le nom puisque ce dernier est le même ?
Au début je faisais par le nom, mais dans mon fichier il y a plusieurs centaines de personnes, dont plusieurs ont le même nom, donc mon calcul comptait parfois l'ensemble des présences de deux personnes simultanément, d'où l'intérêt du matricule, qui lui est unique pour chaque personne (je n'ai découvert que par la suite que ce même matricule pouvait changer en cours d'année, même si seulement les 2 premiers chiffres peuvent changer, ce qui me conduit donc à ne vouloir exploiter que la partie finale du matricule, les 7 derniers caractères)
2° - Est-ce que chaque tableau est construit de façon identique comme dans ton exemple ?
Les tableaux ne sont jamais classés de la même façon pour deux raisons :
- tout d'abord le classement se fait par numéro de matricule et non par Nom de famille (et comme le matricule peut changer en cours d'année...)
- ensuite en cours d'année certaines personnes disparaissent et d'autres apparaissent (ce qui impliquent bien évidemment que l'onglet TOTAL se doit de contenir la liste de toutes les personnes qui seront, à un moment ou à un autre, présentes au cours d'année)
De plus, j'ajoute qu'il existe déjà de nombreux fichiers Excel contenant plusieurs centaines de personnes auquels je dois donc ajouter cet onglet Total et cette formule de calcul du total de présence, ce qui implique qu'il est impossible de modifier la structure des autres onglets, cela prendrait trop de temps ! ^^'
Merci de votre attention dans la résolution de ce problème, j'apprécie beaucoup !
Cordialement,
Antony
Re,
Tu peux utiliser cette formule :
=SOMMEPROD(((DROITE(JANVIER!$A$2:$A$4;5)=DROITE(A2;5))*(JANVIER!$D$2:$D$4)+(DROITE(FEVRIER!$A$2:$A$4;5)=DROITE(A2;5))*(FEVRIER!$D$2:$D$4)))
Je vais essayer de la simplifier.
Je te tiens au courant
Amicalement
Nad
Hum oui elle a l'air de bien fonctionner, je vais la tester dans mon fichier d'origine pour voir si elle va aussi bien que dans le petit exemple que j'ai envoyé (ce fichier étant bcp plus grand et contenant toutes les éventualités possibles), merci à toi !
Bonjour,
C'est bon j'ai essayé avec mon fichier d'origine, ça marche niquel, un grand merci à toi !
Bonne journée,
Cordialement,
Antony