Boostez vos compétences Excel avec notre communauté !
Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !
Saboh12617
XLDnaute Nouveau
Bonjour,
Une proposition en passant par PowerQuery, j'ai simplement donné à copilot le tableau d'entrée et sortie voulus.
Remplacez dans la 1e ligne "Table1" par le nom de la table si différent.
Une proposition en passant par PowerQuery, j'ai simplement donné à copilot le tableau d'entrée et sortie voulus.
Remplacez dans la 1e ligne "Table1" par le nom de la table si différent.
PowerQuery:
let
// === Source ===
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// === Types (fr-FR) ===
#"Types modifiés" = Table.TransformColumnTypes(
Source,
{
{"Matricule Salarié", type text},
{"Date de Fin de session de paie", type date}
},
"fr-FR"
),
#"Taux converti" = Table.TransformColumns(
#"Types modifiés",
{{"Taux horaire", each if _ is text then Number.FromText(_, "fr-FR") else _, type number}}
),
// === Colonnes utiles ===
#"Colonnes conservées" = Table.SelectColumns(#"Taux converti", {"Matricule Salarié","Date de Fin de session de paie","Taux horaire"}),
// === Année ===
#"Année ajoutée" = Table.AddColumn(#"Colonnes conservées", "Année", each Date.Year([Date de Fin de session de paie]), Int64.Type),
// === N, N-1, N-2 ===
AnnéeMax = List.Max(#"Année ajoutée"[Année]),
N2 = AnnéeMax - 2,
N1 = AnnéeMax - 1,
N = AnnéeMax,
// === Filtre des 3 années ===
#"Filtré 3 années" = Table.SelectRows(#"Année ajoutée", each List.Contains({N2, N1, N}, [Année])),
// === Groupement + extraction "début/fin" ===
#"Groupé" = Table.Group(
#"Filtré 3 années",
{"Matricule Salarié", "Année"},
{
{"TriAsc", each Table.Sort(_, {{"Date de Fin de session de paie", Order.Ascending}}), type table},
{"TriDesc", each Table.Sort(_, {{"Date de Fin de session de paie", Order.Descending}}), type table}
}
),
#"Taux début" = Table.AddColumn(#"Groupé", "Taux Début", each try Table.FirstN([TriAsc],1){0}[Taux horaire] otherwise null, type number),
#"Taux fin" = Table.AddColumn(#"Taux début", "Taux Fin", each try Table.FirstN([TriDesc],1){0}[Taux horaire] otherwise null, type number),
#"Tables tri supprimées" = Table.RemoveColumns(#"Taux fin", {"TriAsc","TriDesc"}),
// === Libellés N/N-1/N-2 ===
LabelForYear = (y as number) as text =>
if y = N then "N (" & Number.ToText(y) & ")"
else if y = N1 then "N-1 (" & Number.ToText(y) & ")"
else if y = N2 then "N-2 (" & Number.ToText(y) & ")"
else Number.ToText(y),
// === Pivot "Début" (en conservant des nombres) ===
#"Col Début" = Table.AddColumn(#"Tables tri supprimées", "ColNameDébut", each "Taux Horaire Début " & LabelForYear([Année]), type text),
#"Début réduit" = Table.SelectColumns(#"Col Début", {"Matricule Salarié","ColNameDébut","Taux Début"}),
#"Pivot Début" = Table.Pivot(#"Début réduit", List.Distinct(#"Début réduit"[ColNameDébut]), "ColNameDébut", "Taux Début", List.First),
// === Pivot "Fin" (en conservant des nombres) ===
#"Col Fin" = Table.AddColumn(#"Tables tri supprimées", "ColNameFin", each "Taux Horaire Fin " & LabelForYear([Année]), type text),
#"Fin réduit" = Table.SelectColumns(#"Col Fin", {"Matricule Salarié","ColNameFin","Taux Fin"}),
#"Pivot Fin" = Table.Pivot(#"Fin réduit", List.Distinct(#"Fin réduit"[ColNameFin]), "ColNameFin", "Taux Fin", List.First),
// === Fusion dynamique ===
#"Fusionné" = Table.NestedJoin(#"Pivot Début", {"Matricule Salarié"}, #"Pivot Fin", {"Matricule Salarié"}, "Fin", JoinKind.FullOuter),
colsFin = List.RemoveItems(Table.ColumnNames(#"Pivot Fin"), {"Matricule Salarié"}),
#"Fusionné étendu" = Table.ExpandTableColumn(#"Fusionné", "Fin", colsFin, colsFin),
// === Colonnes cibles (garantir les 6) ===
ColonnesCibles = {
"Matricule Salarié",
"Taux Horaire Début N-2 (" & Number.ToText(N2) & ")",
"Taux Horaire Fin N-2 (" & Number.ToText(N2) & ")",
"Taux Horaire Début N-1 (" & Number.ToText(N1) & ")",
"Taux Horaire Fin N-1 (" & Number.ToText(N1) & ")",
"Taux Horaire Début N (" & Number.ToText(N) & ")",
"Taux Horaire Fin N (" & Number.ToText(N) & ")"
},
#"Colonnes ajoutées si manquantes" =
List.Accumulate(
List.RemoveFirstN(ColonnesCibles, 1),
#"Fusionné étendu",
(state, col) => if List.Contains(Table.ColumnNames(state), col) then state else Table.AddColumn(state, col, each null, type number)
),
// === Réordonner (toutes colonnes numériques, null si absent) ===
#"Colonnes réordonnées" = Table.ReorderColumns(#"Colonnes ajoutées si manquantes", ColonnesCibles)
in
#"Colonnes réordonnées"Bonjour et merci de votre retour
ne maitrisant pas power querry je ne vais pas l'intégrer a mon fichier car le jour ou ça bug je serais incapable de réparer et trouver des solutions
Saboh12617
XLDnaute Nouveau
Ok, mais sans vouloir être prétentieux, la complexité des formules nécessaires pour obtenir un tel résultat sans passer par VBA est d'un niveau au moins aussi (si ce n'est plus) élevé. Par conséquent, cela m'étonnerait qu'une solution VBA ou formule vous convienne.
La question est plus complexe qu'un simple "NB.SI", il y a pas mal de cas de bords à gérer.
La question est plus complexe qu'un simple "NB.SI", il y a pas mal de cas de bords à gérer.
au contraire je préfère largement les formules très complexe. je suis de l'ancienne génération qui fait tout en formule mais je ne me suis jamais penché sur le sujet de powerquery
Gégé-45550
XLDnaute Accro
Bonjour à tous,
Une autre proposition en formule unique, à tester et affiner :
Cordialement
Une autre proposition en formule unique, à tester et affiner :
VB:
=PIVOTER.PAR(B6:B80;J6:J80;H6:H80;ASSEMB.H(MIN;MAX);;0;;0;;J6:J80>=2023)merci bcp pour le retour.
merci bcp pour le retour. la formule fonctionne mais le fichier rame complétement. plantage du pc a deux reprises 🙁
nous sommes obligé de passer par une formule matricielle?
as t on la possibilité sur les deux colonnes après "Nbre hres mensuel" de venir mettre un 1 pour la valeur la plus petite et idem pour la valeur la plus grand.
et je pourrais garder ma formule somme.si.ens
Saboh12617
XLDnaute Nouveau
La formule de ALS35 est ce qu'il y a de plus optimisé en formule. Si ça rame c'est que : ou bien vous utilisez des plages de colonnes entières ("A:B") au lieu de la plage réelle (sans doute parce que pour une raison inconnue vous n'utilisez pas de tableau structuré ?), donc vous demandez à Excel beaucoup de calculs inutiles, et sinon c'est que vous avez vraiment beaucoup de data, et auquel cas PowerQuery est intéressant puisque l'évaluation n'est faite qu'une fois et ensuite c'est statique.
Rappellez-vous qu'en fonction du problème il faut utiliser l'outil adapté : parfois macro, parfois fonction, parfois powerquery
Rappellez-vous qu'en fonction du problème il faut utiliser l'outil adapté : parfois macro, parfois fonction, parfois powerquery
merci. oui le fichier fait 52 938ko ....
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
Discussions similaires
- Question
- Question
- Question
- Question
- Question
- Résolu(e)
- Question
- Résolu(e)
