Microsoft 365 Somme suivant heures

[nico3869]

Bonjour à tous,
Une fois encore je suis face un mur!
Je souhaiterai obtenir l'addition de 8 colonnes suivant des heures variables.
Plusieurs difficultés :
- Extraire les horaires si un même horaire identique dans les 8 colonnes
- Ensuite, avoir la somme des 8 valeurs à l'horaire identique...
J'espère que mes explications seront assez claires.
Merci d'avance pour votre aide

 

[piga25]

Bonjour,

Peut être comme cela :
1- trouver la valeur temps maxi sur la premiere ligne du tableau (cellule T9)
2- incrémenter d'une seconde cette valeur (cellule T10 et suivantes)
3- si nb valeur temps est égale à 8 alors on additionne les valeurs associées (cellule U9)

=SI(NB.SI(C:C;T9)+NB.SI(E:E;T9)+NB.SI(G:G;T9)+NB.SI(I:I;T9)+NB.SI(K:K;T9)+NB.SI(M:M;T9)+NB.SI(O:O;T9)+NB.SI(Q:Q;T9)=8;INDEX(C:D;EQUIV(T9;C:C;1);2)+INDEX(E:F;EQUIV(T9;E:E;1);2)+INDEX(G:H;EQUIV(T9;G:G;1);2)+INDEX(I:J;EQUIV(T9;I:I;1);2)+INDEX(K:L;EQUIV(T9;K:K;1);2)+INDEX(M:N;EQUIV(T9;M:M;1);2)+INDEX(O:P;EQUIV(T9;O:O;1);2)+INDEX(Q:R;EQUIV(T9;Q:Q;1);2);"")

Voir fichier joint

Une solution VBA devrait mieux fonctionner

 

[djidji59430]

Bonjour à tous,

J'ai pas du comprendre la meme chose !
d'ailleurs dans le tableau, il n'y a aucune ligne avec un horaire identique sur les 8 cellules !

 

[piga25]

Re
sans colonne supplémentaire à mettre en S9 puis à recopier vers le bas.

=SI(NB.SI(C:C;Q9)+NB.SI(E:E;Q9)+NB.SI(G:G;Q9)+NB.SI(I:I;Q9)+NB.SI(K:K;Q9)+NB.SI(M:M;Q9)+NB.SI(O:O;Q9)+NB.SI(Q:Q;Q9)=8;INDEX(C:D;EQUIV(Q9;C:C;1);2)+INDEX(E:F;EQUIV(Q9;E:E;1);2)+INDEX(G:H;EQUIV(Q9;G:G;1);2)+INDEX(I:J;EQUIV(Q9;I:I;1);2)+INDEX(K:L;EQUIV(Q9;K:K;1);2)+INDEX(M:N;EQUIV(Q9;M:M;1);2)+INDEX(O:P;EQUIV(Q9;O:O;1);2)+INDEX(Q:R;EQUIV(Q9;Q:Q;1);2);"")

Edit:
Formule un peu plus courte :

=SI(NB.SI($C$9:$R$512;Q9)=8;INDEX(C:D;EQUIV(Q9;C:C;1);2)+INDEX(E:F;EQUIV(Q9;E:E;1);2)+INDEX(G:H;EQUIV(Q9;G:G;1);2)+INDEX(I:J;EQUIV(Q9;I:I;1);2)+INDEX(K:L;EQUIV(Q9;K:K;1);2)+INDEX(M:N;EQUIV(Q9;M:M;1);2)+INDEX(O:P;EQUIV(Q9;O:O;1);2)+INDEX(Q:R;EQUIV(Q9;Q:Q;1);2);"")

 

[JHA]

Bonjour à tous,

Une MFC pour trouver les mêmes valeurs sur les 8 colonnes et une liste de ces valeurs.

JHA

 

[sylvanu]

Bonsoir Nico, Piga, Djidji, JHA,
J'ai encore compris autre chose.
En PJ on construit la liste de tous les horaires figurant dans les 8 colonnes, on en donne les valeurs et la somme.

 

[Cousinhub]

Bonsoir,
Une solution Power Query
Sylvanu, je ne trouve pas tout à fait les mêmes résultats (pour ma part, 268 lignes répondant au total de 8 occurrences, et quelques résultats finaux non équivalents - ex, 08:36:43, je trouve 489)
Bonne soirée

 

[sylvanu]

Re, Bonsoir Cousinhub,
Merci pour l'info, le bug était sur l'index de ligne.
Ca m'avait échappé.
La PJ #6 a été mise à jour.
Merci encore.

 

[Cousinhub]

Re, Bonsoir Cousinhub,
Merci pour l'info, le bug était sur l'index de ligne.
Ca m'avait échappé.
La PJ #6 a été mise à jour.
Merci encore.

Re-,
Je pense qu'il y a encore des incohérences
pas le même nombre de lignes
et maintenant, à 08:36:42 (que tu avais bon), tu as 10 de trop
Bon courage

 

[sylvanu]

Je regarde.
Re,
J'ai compris. Dans les horaires les "secondes" ne sont jamais identiques.
Elles peuvent avoir plusieurs valeurs comme :

1.15740740741499E-05​

1.15740740740389E-05​

1.15740740740944E-05​

Donc les recherches d'horaires ne collent pas toujours.

Par contre je ne vois pas comme PowerQuerry peut s'en sortir.
Il doit vérifier par rapport aux chaines de caractères et non aux valeurs décimales.
Snif.

 

[TooFatBoy]

Bonjour,

Prop en pj

 

[Cousinhub]

Je regarde.
Re,
J'ai compris. Dans les horaires les "secondes" ne sont jamais identiques.
Elles peuvent avoir plusieurs valeurs comme :

1.15740740741499E-05​

1.15740740740389E-05​

1.15740740740944E-05​

Donc les recherches d'horaires ne collent pas toujours.

Par contre je ne vois pas comme PowerQuerry peut s'en sortir.
Il doit vérifier par rapport aux chaines de caractères et non aux valeurs décimales.
Snif.

Bonjour,
Non, non, PQ traite bien en décimal :
Voici ce qu'il charge, à la première étape :

Et à la transformation en "Heure" :
(étape que j'effectue en toute fin de requête)

Peut-être, en VBA, soit utiliser Range("A1").Text, ou limiter le nombre de décimales (9 pour PQ)
Bon dimanche

 

[alexga78]

Bon Dimanche à tous,
autre essai avec Power query.

Edit: Bonjour à tous,
Excellente remarque de Cousinhub, j'ai oublié cette demande importante.
La question sous-jacente, que fait-on si l'horaire apparait plusieurs fois dans la même colonne ? j'ai pris l'option de ne retenir que la première occurence.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Table.FromList(Table.ToColumns(Source), Splitter.SplitByNothing()), "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Column N°", each  List.Repeat({"Col " & Text.From([Index])},List.Count(_[Column1]))),
    Combined = Table.TransformColumnTypes(Table.FromColumns({List.Combine(Table.SelectRows(#"Added Custom",each Number.IsOdd(_[Index]))[Column1]),List.Combine(Table.SelectRows(#"Added Custom",
    each Number.IsEven(_[Index]))[Column1]), List.Combine(Table.SelectRows(#"Added Custom",each Number.IsOdd(_[Index]))[#"Column N°"])},{"Horaire","Value","Column N°"}),{{"Horaire", type time}}),
    #"Expanded Group" = Table.SelectRows(Table.Group(Table.SelectColumns(Table.SelectRows(Table.ExpandTableColumn(Table.ExpandTableColumn(Table.Group(Combined, {"Horaire"}, {{"Value",
    each  Table.Group(_,{"Column N°"},{{"Group", each Table.AddIndexColumn(_,"Index",1)}})}}), "Value", {"Column N°", "Group"}, {"Column N°", "Group"}), "Group", {"Value", "Index"}, {"Value", "Index"}),
    each [Index] = 1), {"Horaire", "Value"}), {"Horaire"}, {{"Value", each if List.Count(_[Horaire]) = 8 then List.Sum(_[Value]) else null}}), each [Value] <> null),
    #"Sorted Rows" = Table.Sort(#"Expanded Group",{{"Horaire", Order.Ascending}})
in
    #"Sorted Rows"

bonne journée

 

[Cousinhub]

Bon Dimanche à tous,
autre essai avec Power query.

Hello Alex,
Beau code
Tu as juste oublié la condition :

- Extraire les horaires si un même horaire identique dans les 8 colonnes
- Ensuite, avoir la somme des 8 valeurs à l'horaire identique...

Mais aucun souci, dans l'avant-dernière étape de groupage
Bon dimanche

 

[sylvanu]

Bonjour Cousinhub, Alexga,
J'avais bien vu dans le fil que je donne que votre solution par PowerQuerry donnait les bons résultats, ce qui d'ailleurs m'a amené à supprimé ma PJ boguée.
Mais on voit que :
1- PowerQuerry arrondit à 10 chiffres après la virgule, ce qui est la solution car les erreurs dans le fichier étaient de l'ordre de 1e-12.
2- Cela n'explique pas mon souci entre NB.SI et EQUIV. ( voir fil Lien )
J'admettrais volontiers que les deux formules se heurtent au même souci d'arrondi, mais là où je reste perplexe c'est que les deux formules n'ont pas le même comportement, comme si NB.SI arrondissait comme PowerQuerry alors qu' EQUIV travaille sans arrondi.
Ce qui est gênent c'est qu'on peut tomber dans le panneau et chercher longtemps.