Total heure et totale visite dans une même cellule
bonjour à tous,
j'espère que vous allez bien.
je viens vers vous pour un nouveau "defi"... :-) j'espère qu'"il n'est pas fou.. :-)
j'aimerais trouver une formule qui d'une part calcul le nombre d'heure par prénom de la colonne ae et mettre en ( ) le nombre de fois ou apparait le prénom mais par jour. j'ai mis dans la ligne y3:azc3 l'exemple mais que j'ai fais manuellement.
j'ai le nombre d'heure qu'on va chez Alice et le nombre de fois ou on va chez Alice le lundi, le mardi, le mercredi....
pour le nombre d'heure, j'ai comme formule
bon, elle devrait fonctionner mais je coince un peu :-)
par contre pour le nombre de fois ou je vais chez une personne sur une journée, là, j'avoue que coince (aussi :-)))
si l'un.e d'entre vous pouvais m'aiguiller, se serait cool... :-)
voici le fichier...
merci à tous et toutes pr vos bons conseils...
ben
Bonjour,
Avec une macro:
Cdlt
Bonsoir Arturo83,
merci pour votre proposition, mais si peux me permettre comme je suis une bulle en macro, je tente d'abord avec une "simple" formule... j'aurais du le préciser et je m'en excuse...mais merci à vous d'essayer de m'aider :-).
bav,
ben
Bonsoir,
Je pensais faire autre chose de ma soirée mais il a fallu que je tombe sur cette question
Pour Y3 ;
=SI($AE3="";"";ENT(SOMMEPROD((JOURSEM($L$3:$L$21;2)=COLONNE()-24)*(SI(GAUCHE($N$3:$V$21;NBCAR($AE3))=$AE3;GAUCHE(DROITE($N$3:$V$21;5);2)+DROITE(DROITE($N$3:$V$21;5);2)/60;0))))&":"&STXT(ARRONDI.SUP(SOMMEPROD((JOURSEM($L$3:$L$21;2)=COLONNE()-24)*(SI(GAUCHE($N$3:$V$21;NBCAR($AE3))=$AE3;GAUCHE(DROITE($N$3:$V$21;5);2)+DROITE(DROITE($N$3:$V$21;5);2)/60;0)))-ENT(SOMMEPROD((JOURSEM($L$3:$L$21;2)=COLONNE()-24)*(SI(GAUCHE($N$3:$V$21;NBCAR($AE3))=$AE3;GAUCHE(DROITE($N$3:$V$21;5);2)+DROITE(DROITE($N$3:$V$21;5);2)/60;0))));2)*0,6&"0000";3;2)&" ("&SOMMEPROD((JOURSEM($L$3:$L$21;2)=COLONNE()-24)*(GAUCHE($N$3:$V$21;NBCAR($AE3))=$AE3))&")")Pour info il y avait des prénoms invisibles parce que leur couleur était le blanc ! Dont gwanaelle en P4.
Et j'ai mis 09:17 pour raymond en P20 juste pour voir si ma formule permettait bien 2 chiffres pour les heures.
Bonjour,
merci pour votre proposition, mais si peux me permettre comme je suis une bulle en macro, je tente d'abord avec une "simple" formule... j'aurais du le préciser et je m'en excuse...mais merci à vous d'essayer de m'aider :-).
Pas de problème, ça vous fera une deuxième possibilité.
D'ailleurs en rouvrant le fichier, je me suis aperçu que dans certains cas, j'avais oublié de remettre à 0 les résultats avant de passer à l'étape suivante, voilà qui est corrigé.
Cdly
Bonjour à tous, bonjour Arturo83, bonjour doux rêveux,
tout d'abord merci à tous les deux pour vos propositions et précieux conseils :-)
Arturo, j'espère que vous ne m'en voudrez pas mais, je préfère rester sur une utilisation "classique" des formules car je ne maitrise pas les macros et en cas de soucis, c'est plus "compliqué" enfin pour moi ...
doux rêveur, j'espère ne pas avoir engendrer des insomnies :-)... merci, à vous pour la proposition je vais voir si j'arrive "seul" à la transposer sur mon fichier...
j'avais une question, sur mon fichier d'origine j'utilise la formule
=SI($AR5="";"";SOMMEPROD((JOURSEM($L$3:$L$42;2)=COLONNES($AJ:AJ))*SI(GAUCHE($N$3:$X$42;NBCAR($AR5))=$AR5;DROITE($N$3:$X$42;5);0)))
qui me donne le nombre d'heure par jour et par prénom.
aurait-il été possible d'ajouter à le suite de cette formule un complément visant à avoir le chiffre du nombre de passage sur chaque jour de la semaine.
le résultat est celui proposé dans le fichier mais votre formule, je n'aurais pas su la faire tellement elle est pour moi impressionnante.
encore merci à tous les deux...
Ben
Bonjour,
doux rêveur, j'espère ne pas avoir engendrer des insomnies :-)
Non non
=SI($AR5="";"";SOMMEPROD((JOURSEM($L$3:$L$42;2)=COLONNES($AJ:AJ))*SI(GAUCHE($N$3:$X$42;NBCAR($AR5))=$AR5;DROITE($N$3:$X$42;5);0)))
qui me donne le nombre d'heure par jour et par prénom.
aurait-il été possible d'ajouter à le suite de cette formule un complément visant à avoir le chiffre du nombre de passage sur chaque jour de la semaine.
Non, c'est ce que j'avais essayé mais votre formule ne fonctionne pas et c'est logique : on ne peut pas additionner du texte. Or, les 5 derniers caractères de vos cellules sont du texte à cause des 2 points.
De plus, on ne peut pas simplement additionner les heures d'un côté et les minutes de l'autre parce que 2 demi heures, ça fait une heure, donc les chiffres à droite des 2 points ne vont pas toujours rester à droite.
D'où la complexité de ma formule pour tenir compte de tous les cas.
bjr à tous, bonjour doux rêveur...
merci pour votre complément d'information... :-)
je vais avoir besoin de vos lumières car je coince...j'ai essayer de refaire ce que vous avez fait sur le fichier d'essai.. mais, je ne comprends pas pq, qq chose coince...voici une copie d'écran..
je devrais avoir en am3 un résultat comme : 02:00 (1) mais j'ai ceci..
auriez-vous une idée d'où pourrait provenir le soucis ?.
merci de votre retour.
ben
Vous l'avez bien sauvée en matriciel (CTRL+MAJ+ENTER) ?
voici la formule..
je ne sais pq c'est comme s'il ne reconnaissait pas le texte...
je vous aide pas bcp..suis désolé... :-(
J'ai trouvé, c'est parce que vous avez tout mis dans d'autres colonnes : il suffit de remplacer les -24 par -35 (il y en a 4). Et sauver encore en matriciel après chaque modif.
Une autre manière de le faire pour ne pas avoir à calculer la position de la colonne si on modifie le tableau, c'est de mettre ceci à la place de COLONNE()-24 dans la 1ère colonne où on met la formule (AJ dans ce cas-ci) ;
COLONNE(A1)Puisque le but est de renvoyer 1, 2, 3 ... au fur et à mesure qu'on étire vers la droite.
GENIAL... ça marche
par contre... la seconde info... gloups...
ça c'est la formule modifiée (avec le colonne()-35:
=SI($AS3="";"";ENT(SOMMEPROD((JOURSEM($L$3:$L$42;2)=COLONNE()-35)*(SI(GAUCHE($N$3:$V$42;NBCAR($AS3))=$AS3;GAUCHE(DROITE($N$3:$V$42;5);2)+DROITE(DROITE($N$3:$V$42;5);2)/60;0))))&":"&STXT(ARRONDI.SUP(SOMMEPROD((JOURSEM($L$3:$L$42;2)=COLONNE()-35)*(SI(GAUCHE($N$3:$V$42;NBCAR($AS3))=$AS3;GAUCHE(DROITE($N$3:$V$42;5);2)+DROITE(DROITE($N$3:$V$42;5);2)/60;0)))-ENT(SOMMEPROD((JOURSEM($L$3:$L$42;2)=COLONNE()-35)*(SI(GAUCHE($N$3:$V$42;NBCAR($AS3))=$AS3;GAUCHE(DROITE($N$3:$V$42;5);2)+DROITE(DROITE($N$3:$V$42;5);2)/60;0))));2)*0,6&"0000";3;2)&" ("&SOMMEPROD((JOURSEM($L$3:$L$42;2)=COLONNE()-35)*(GAUCHE($N$3:$V$42;NBCAR($AS3))=$AS3))&")")
et je devrais changer pour mettre COLONNE(A1).. mis je n'ai pas compris ou mettre cette donnée ?
pouvez-vous m'aider ?
encore mille mercis à vous;
ben
Comme je le disais ci-dessus, à la place de COLONNE()-24 (ou -35), comme ça ;
SI($AE3="";"";ENT(SOMMEPROD((JOURSEM($L$3:$L$21;2)=COLONNE(A1))* ...En l'étirant vers la droite, ça deviendra B1 et ainsi de suite (sachant que COLONNE(B1) renvoie 2) .
Puisque le but de cette partie de la formule est de renvoyer entre 1 et 5 pour trouver la correspondance avec le jour marqué au-dessus (Lundi = 1).
Bonsoir à tous !
Une autre proposition en Y3 :
=TEXTE(SOMMEPROD(ESTNUM(TROUVE($AE3;$N$3:$V$21))*(JOURSEM($L$3:$L$21;2)=COLONNE(A1))*(SIERREUR(DROITE($N$3:$V$21;5)*1;0)));"hh:mm") & " (" & SOMMEPROD(ESTNUM(TROUVE($AE3;$N$3:$V$21))*(JOURSEM($L$3:$L$21;2)=COLONNE(A1))) & ")"Si la casse n'est pas un critère, la fonction TROUVE sera remplacée par CHERCHE.
A étirer vers la droite et vers le bas.
Joli ça JFL !
bonjour à tous, bonjour doux rêveur, bonjour JFL...
merci à tous les deux pour vos retours et propositions... me voilà devant un choix de luxe.... quelle formule choisir
je ne sais si cela va pouvoir aider à "choisir" mais... j'aimerais vous demander si l'une des deux proposition pourrait permettre de faire le total des heures par jours..comme ce qui est en jaune...
ainsi que le total des nombres (colonne orage)...
après ce n'est p-ê pas possible...mais votre avis compte donc j'en tiendrai compte...
je vous suis encore éternellement reconnaissant pour votre aide.
bon début de journée à vous.
Ben
Salut,
je dirais que contrairement à d'autres situations, plus c'est court plus c'est bon
Alors prends celle de JFL.
Pour le total il n'y a plus besoin de discriminer en fonction des jours, donc on garde la première partie de la formule (qu'on modifie à peine) pour la jaune et la 2ème pour l'orange (idem).
Ce qui donne pour la jaune ;
=TEXTE(SOMMEPROD(ESTNUM(CHERCHE($AE3;$N$3:$V$21))*(SIERREUR(DROITE($N$3:$V$21;5)*1;0)));"hh:mm")et pour l'orange ;
=SOMMEPROD(ESTNUM(TROUVE($AE3;$N$3:$V$21))*1)Bonjour à tous !
Bien...
Je vous remercie de ce retour.
bonjour à tous le deux,
encore une fois, mille mercis pour votre aide sans laquelle je n'y arriverais pas...
je reviens seulement vers vous car j'ai eu des difficultés à transposer les formules sur mon fichier ais tt est rentré dans l'ordre :-)
sans vouloir abuser, j'ai deux questions "subsidiaires" :-)
dans la colonne ad, j'ai placé la formule et cela marche très bien.
petite question, est-il possible qu'il fasse le résultat des cellules a3:ac3 ?
je pensais faire avec cette formule : =SIERREUR(gauche($y$3:$ac$3;5)*1;0));"hh:mm") mais ça ne marche pas...
autre question, je cherche à mettre en y22, le total des heures de y3:y20... mais là aussi ça coince..
merci pr votre aide ô combien précieuse..
ben
Alors pour la première, si j'ai bien compris il suffit de reprendre la formule de JFL en supprimant la référence aux jours, donc faire le total des 2 formules que j'ai mises ci-dessus ;
=TEXTE(SOMMEPROD(ESTNUM(CHERCHE($AE3;$N$3:$V$21))*(SIERREUR(DROITE($N$3:$V$21;5)*1;0)));"hh:mm")&" ("&SOMMEPROD(ESTNUM(TROUVE($AE3;$N$3:$V$21))*1)&")"À étirer vers le bas.
Et pour le total par jour ;
=TEXTE(SOMMEPROD(ESTTEXTE($N$3:$V$21)*(JOURSEM($L$3:$L$21;2)=COLONNE(A1))*(SIERREUR(DROITE(SI($N$3:$V$21<>"";$N$3:$V$21);5)*1;0)));"hh:mm") & " (" & SOMMEPROD(ESTTEXTE($N$3:$V$21)*(JOURSEM($L$3:$L$21;2)=COLONNE(A1))*1) & ")"À étirer vers la droite.