XL 2016 Répartition mensuelle sur période glissante

Lolote83

XLDnaute Barbatruc
Bonjour à tous et à toutes,

Encore une fois, j'aurais besoin de vos conseils éclairés pour résoudre mon problème.
Je reçois des factures qui sont le plus souvent à cheval sur 2 voire 3 mois.
J'ai réussi à faire la répartition en jours puis en coûts mais mon tableau doit être systématiquement agrandi pour que les calculs se fassent en fonction de la dernière facture arrivée.
Je souhaiterais au final, avoir un tableau, ou en sélectionnant simplement l'année, je récupère les coûts mensuels.
Peut on arriver au tableau exemple sans avoir à passer par mes tableaux intermédiaires (Jours et Coûts) ?
Par avance, un grand merci à tous et et celles qui prendront du temps pour se creuser la tête comme je le fait depuis pas mal de temps.
Merci encore
Cordialement

@+ Lolote83
 

Pièces jointes

  • Pour Forum - Répartition mensuelle sur période glissante.xlsx
    23.4 KB · Affichages: 13

Lolote83

XLDnaute Barbatruc
Bonsoir Fanfan,

Merci pour ta réponse mais elle ne correspond pas vraiment à ma demande.
Une facture est émise depuis sa date de début et ce jusqu'à sa date de fin.

Prenons par exemple la facture n°6 (ligne7 de la feuil1)
Date début 10/11/2018
Date fin 09/01/2019
Montant 196.92 €

Donc pour cette facture on aura :

21 jours portant sur la période de novembre 2018
31 jours portant sur la période de décembre 2018
09 jours portant sur la période de janvier 20119

cela représente 61 jours en tout et on aura donc

21/61 du montant pour novembre 2018
31/61 du montant pour décembre 2018
09/61 du montant pour janvier 2019

soit

67.79 € pour novembre 2018
100.07 € pour décembre 2018
29.05 € pour janvier 2019

donc 67.79+100.07+29.05 = 196.92 €

C'est ce que j'arrive à faire avec les tableaux intermédiaires mais sans eux !!!! Impossible pour moi pour le moment

En espérant que ces explications sont plus claires pour vous et vous remercie par avance

@+ Lolote83
 

chris

XLDnaute Barbatruc
Bonjour à tous

Sur 2016 on peut utiliser PowerQuery et un TCD avec segment pour choisir l'année

Si la source évolue, Données, Actualiser tout
 

Pièces jointes

  • Répartition mensuelle sur période glissantePQ.xlsx
    33.2 KB · Affichages: 14

Lolote83

XLDnaute Barbatruc
Bonjour Chris,
Bravo monsieur, je savais que quelq'un y arriverait.
Je reconnais là, la force des TCD et PowerQuery.
Par contre, je ne comprends pas encore comment cela fonctionne.
Quelques explications ou tutos m'aiderait bien
Merci beaucoup
Je garde le fichier pour le potasser en attendant quelques explications si possible.
10000 mercis
@+ Lolote83
 

chris

XLDnaute Barbatruc
Bonjour

Edit : se référer au #14 à propos de la couleur verte

J'ai commencé par renommer le tableau de factures pour remplacer le nom automatique Tableau2 par Consommation puis
  • se placer dans une cellule du tableau et Données, A partir d'un tableau ce qui ouvre PowerQuery
  • sélectionner les 3 colonnes de dates et Accueil, Type de données, Date : confirmer le remplacement du typage auto
  • Ajouter une colonne, Colonne personnalisé : nom Cout/J, formule =[TTC]/[Nb jours]
  • Ajouter une colonne, Colonne personnalisé : nom Année, formule ={Number.From([Période Du])..Number.From([Période Au])}
    Cela crée toutes les dates entres les débuts et les fins
  • cliquer sur la double flèche près du titre Année pour Développer sur de nouvelles lignes
  • sélectionner la colonne Année et Accueil, Type de données, Date
  • supprimer toutes les colonnes sauf Année et Cout/J
  • clic droit sur Année, duplication de la colonne et remplacer dans le barre de formule Année - copier par Mois
  • sélectionner Année, Transformer, Date, Année, Année
  • sélectionner Mois, Transformer, Date, Mois, Nom du Mois
  • sélectionner les colonnes Année et Mois, Transformer, Grouper par : en bas
    Nom : Coûts,
    Opération : Somme
    Colonne : Cout/J
  • on crée une seconde requête :
    (on pourrait éventuellement simplifier en prévoyant les dates mini et maxi dans un tableau Excel)
    Accueil, Nouvelle Source, Autres sources, Requête vide et on renomme la requête Couts
  • en ayant cette requête sélectionnée dans la liste à gauche : Accueil, Editeur avancé
    Remplacer Source= par
    Min = #date(Record.Field(Table.Min(Consommation, "Année"),"Année"),1,1),
    Max=#date(Record.Field(Table.Max(Consommation, "Année"),"Année"),12,31),
    Source={Number.From(Min)..Number.From(Max)}
    Valider
  • Transformer (le dernier onglet), Vers la table
  • renommer la colonne M et Accueil, Type de données, Date
  • clic droit sur M, duplication de la colonne et remplacer dans le barre de formule M - copier par Mois
  • clic droit sur M, duplication de la colonne et remplacer dans le barre de formule M - copier par Année
  • sélectionner Mois, Transformer, Date, Mois, Nom du Mois
  • sélectionner M, Transformer, Date, Mois, Mois
  • sélectionner Année, Transformer, Date, Année, Année
  • sélectionner les trois colonnes, clic droit supprimer les doublons
  • Accueil, Fusionner les requêtes, choisir en 2ème requête Consommation, sélectionner Année et Mois dans chacune des tables et tye de jointure Externe gauche
  • cliquer sur la double flèche près du titre Consommation
  • sortir par Fermet et charger dans , Connexion seulement
  • Créer un TCD basé sur la connexion Couts (Consommation)
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @Lolote83 :),

Avec une petite fonction personnalisée: SommeMois(numéro Année, numéro Mois,Tableau)
Le code est dans module1:
VB:
Function SommeMois(Xan As Long, Xmois As Long, xtableau As Range) As Currency
Dim PremDuMois As Date, FinDuMois As Date, TotalTTC As Currency
Dim dateDeb As Date, dateFin As Date, xrow, i&, nj&
   PremDuMois = DateSerial(Xan, Xmois, 1): FinDuMois = DateSerial(Xan, Xmois + 1, 1) - 1
   For Each xrow In xtableau.Rows
      nj = 0
      For i = PremDuMois To FinDuMois: nj = nj - (i >= xrow.Cells(1, 3) And i <= xrow.Cells(1, 4)): Next
      TotalTTC = TotalTTC + nj / (xrow.Cells(1, 4) - xrow.Cells(1, 3) + 1) * xrow.Cells(1, 5)
   Next xrow
   SommeMois = TotalTTC
End Function
 

Pièces jointes

  • Lolote83- Répartition mensuelle sur période glissante- v1.xlsm
    23.6 KB · Affichages: 10

chris

XLDnaute Barbatruc
RE

J'étais lancé sur une réalisation tout PowerQuery sans TCD, d'où la création du calendrier et ai changé d'avis quasi à la fin

Je change dans mon post précédent la couleur de ce qui est utile au TCD

Faire le TCD à partir de la connexion Consommation
 

Pièces jointes

  • Répartition mensuelle sur période glissantePQ2.xlsx
    39.1 KB · Affichages: 13
Dernière édition:

Statistiques des forums

Discussions
311 735
Messages
2 082 023
Membres
101 873
dernier inscrit
excellllll