Projet de fiche de pointage. 1er problème : utilisation de sommeprod Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
s
seboseb
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 28 novembre 2018
Version d'Excel : 2016FR

Message par seboseb » 30 novembre 2018, 13:30

Bonjour à tou.te.s,
J'essaie de faire une fiche de pointage pour mes collègues et moi-même. L'idée est de calculer la durée de la journée en entrant les heures de début fin de chaque demi-journée de chantier. Je veux pouvoir compter les heures en fin de mois. Je veux aussi compter les jours de travail, de congés payés (qui sont payés par une caisse et non par l'employeur, dans le bâtiment), de maladie, etc. Je veux enfin affecter la totalité de chaque journée à un ou plusieurs chantiers en cours.

J'ai à peu près la trame pour la collecte de ces données (voir en PJ), mais je ne parviens pas à les recueillir correctement.
J'essaie d'utiliser sommeprod comme suit : (ladate est une matrice comportant... des dates)

=SOMMEPROD(MOIS(ladate)=11) pour avoir le nb de jours en novembre
=SOMMEPROD(MOIS(ladate)=11;heurestravail) pour avoir le nombre d'heures travaillées en novembre
=SOMMEPROD(MOIS(ladate)=11;c1="Duchemolle") pour avoir le nombre d'heures sur le chantier Duchemolle répertoriées en novembre dans la matrice c1 (chantier principal de la journée)

Je n’obtiens rien d'autre que "0" comme résultat... Pourtant, ces formules fonctionnement avec succès dans un autre doc que j'ai fait (un suivi de mes courses à pied).
Par ailleurs, je ne suis pas certain de la syntaxe quand on veut appeler une matrice qui se trouve dans un autre onglet ou dans un autre document.

Bref, je suis paumé : pouvez-vous m'aider ? Merci d'avance (et d'ores et déjà pour avoir lu mon blabla jusqu"au bout)
Pointage v2-expurge.xlsx
(18.63 Kio) Téléchargé 12 fois
Avatar du membre
Florian53
Membre fidèle
Membre fidèle
Messages : 287
Appréciations reçues : 12
Inscrit le : 3 juin 2015
Version d'Excel : Office 365

Message par Florian53 » 30 novembre 2018, 13:39

Bonjour seboseb ,

Voici ton fichier en retour j'ai seulement modifier la formule de la feuille " Mensuel" pour te donner la démarche tu n'as plus qu'a appliquer la même méthode pour les autres.
Pointage v2-expurge.xlsx
(20.05 Kio) Téléchargé 13 fois
Les grandes réussites sont le fruit de l'apprentissage durable. Apprenez à apprendre chaque jour.

:btres:
s
seboseb
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 28 novembre 2018
Version d'Excel : 2016FR

Message par seboseb » 30 novembre 2018, 15:13

Un grand merci ! Mais je ne comprends toujours pas quelle était mon erreur ?
T
Theze
Passionné d'Excel
Passionné d'Excel
Messages : 3'334
Appréciations reçues : 216
Inscrit le : 26 janvier 2011
Version d'Excel : 2007

Message par Theze » 30 novembre 2018, 17:00

Bonjour,

Pour la première formule, il te faut multiplier par 1 :
=SOMMEPROD((MOIS(ladate)=11)*1)
pour la seconde c'est issu de la première :
=SOMMEPROD((MOIS(ladate)=11)*1;heurestravail)
mais pour la troisième il y a un hic, elle doit être comme ceci :
=SOMMEPROD((MOIS(ladate)=11)*(chantier1="Duchemolle"))
mais Sommeprod() demande à ce que les plages aient un nombre de cellules identiques pas forcément à la même hauteur mais le nombre de cellules doit être identique et toi tu as des plages qui ne sont pas égales en nombre de cellules dans tes noms :
ladate = Salarié1!$A$7:$A$64
chantier1 = Salarié1!$M$6:$M$350
heuresc1 = Salarié1!$N$8:$N$350
etc...
donc, équilibre tes plages dans tes noms et après ça ira !
T
Theze
Passionné d'Excel
Passionné d'Excel
Messages : 3'334
Appréciations reçues : 216
Inscrit le : 26 janvier 2011
Version d'Excel : 2007

Message par Theze » 30 novembre 2018, 17:12

Ton classeur avec quelques formules :
Pointage_V3_expurge.xlsx
(21.75 Kio) Téléchargé 7 fois
s
seboseb
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 28 novembre 2018
Version d'Excel : 2016FR

Message par seboseb » 30 novembre 2018, 21:30

D'abord, un grand merci. Ensuite, quelques questions...
1- Pourquoi multiplier par 1 ? Qu'est-ce que ça change ? et d'une manière générale, pourquoi (et quand) employer le signe * au lieu du point-virgule habituel ? Je demande, car la doc sur SOMMEPROD n'en parle pas.

2-Je vais maintenant faire la même chose pour 4 salariés supplémentaires. Il y aura donc 5 onglets "salarié" sur le même modèle. Dois-je définir toutes mes plages pour chaque salarié ? (heurestravail1, heurestravail2, heurestravail3...) ou bien est-ce que heurestravail désignera la même zone sur chaque onglet (j'appellerai alors salarié1!heurestravail, salarié2!heurestravail)

3- Pour des raisons de simplicité, je vais peut-être mettre l'onglet clients dans un autre fichier. Quelle syntaxe me permettra alors d'appeler, à partir de ce nouveau fichier, mes heureschantier1 (et 2 et 3...) dans mes différents onglets salariés ?

Désolé pour cette avalanche de questions, j'ai vraiment essayé mais je manque complètement de bases.
T
Theze
Passionné d'Excel
Passionné d'Excel
Messages : 3'334
Appréciations reçues : 216
Inscrit le : 26 janvier 2011
Version d'Excel : 2007

Message par Theze » 1 décembre 2018, 09:28

Bonjour,
1- Pourquoi multiplier par 1 ? Qu'est-ce que ça change ? et d'une manière générale, pourquoi (et quand) employer le signe * au lieu du point-virgule habituel ? Je demande, car la doc sur SOMMEPROD n'en parle pas
Pour les valeurs non numériques (String), la fonction Sommeprod() leur affecte la valeur 0 donc en multipliant par 1, elle retourne le nombre d'éléments correspondants. Tu multiplie par 1 quand il n'y a qu'un critère à retourner, c'est le cas de :
=SOMMEPROD((MOIS(ladate)=11)*1)
Si tu en as plusieurs, il n'est plus nécessaire de multiplier par 1 car tu multiplies les critères les uns par les autres :
=SOMMEPROD((MOIS(ladate)=11)*(chantier1="Duchemolle"))
2-Je vais maintenant faire la même chose pour 4 salariés supplémentaires. Il y aura donc 5 onglets "salarié" sur le même modèle. Dois-je définir toutes mes plages pour chaque salarié ? (heurestravail1, heurestravail2, heurestravail3...) ou bien est-ce que heurestravail désignera la même zone sur chaque onglet (j'appellerai alors salarié1!heurestravail, salarié2!heurestravail)
Quand tu défini un nom pour une plage, sa référence comporte toujours le nom de la feuille, tu peux le supprimer mais ça ne marchera pas comme il faut.
Je pense que le mieux est de ne pas utiliser de noms et de faire référence aux plages, de cette façon, quand tu as fini ton modèle et que tu fais la copie des feuilles pour tes salariés, elles feront référence à la feuille sur laquelle elles se trouvent.
3- Pour des raisons de simplicité, je vais peut-être mettre l'onglet clients dans un autre fichier. Quelle syntaxe me permettra alors d'appeler, à partir de ce nouveau fichier, mes heureschantier1 (et 2 et 3...) dans mes différents onglets salariés ?
SommePord() fonctionne sur un classeur fermé de cette façon :
=SOMMEPROD(('C:\Dossier\Sous Dossier\[Classeur1.xlsx]Feuil1'!A1:A17="Le truc que tu veux")*1)
s
seboseb
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 28 novembre 2018
Version d'Excel : 2016FR

Message par seboseb » 1 décembre 2018, 14:50

Bon, ça fonctionne en tout cas. Merci pour la syntaxe, je garde ça précieusement.
Par contre, je ne pige toujours pas le coup du *1.
D'abord, parce que (MOIS(ladate)=11) renvoie une valeur numérique donc non nulle
Ensuite, parce que j'ai un autre fichier (je compte mes km de course à pied...) dans lequel la même fonction donne des résultats (si je veux compter mes sorties du mois de novembre, ça marche)
Enfin parce qu'en admettant que SOMMEPROD affecte une valeur 0 à une cellule, pourquoi 0*1 ferait-il 1 ??? Bon, je pense que je n'ai pas compris ce que tu voulais dire ;-)
Avatar du membre
Florian53
Membre fidèle
Membre fidèle
Messages : 287
Appréciations reçues : 12
Inscrit le : 3 juin 2015
Version d'Excel : Office 365

Message par Florian53 » 3 décembre 2018, 09:27

Bonjour à tous,

Voici un lien qui explique le fonctionnement de Somme Prod:
https://forum.openoffice.org/fr/forum/v ... 37#p210937

En retour ton fichier avec les toutes formules.
Pointage_V3_expurge.xlsx
(27.16 Kio) Téléchargé 13 fois
Les grandes réussites sont le fruit de l'apprentissage durable. Apprenez à apprendre chaque jour.

:btres:
s
seboseb
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 28 novembre 2018
Version d'Excel : 2016FR

Message par seboseb » 3 décembre 2018, 20:50

Bien vu, la méthode de comptage des jours ouvrés ! Je n'aurais jamais su faire.
Merci pour le lien ; je l'avais déjà lu mais je le considérais comme caduc, car un membre de ce forum m'avais une fois indiqué que la syntaxe de sommeprod avec "*" était fautive... Ignare que je suis, je crois tout ce qu'on me dit.
Ma feuille fonctionne, merci à tous.
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message