Microsoft 365 Trouver une somme en fonction d'une date

Itchythemouse · 25 Septembre 2019

Bonjour,
J'ai une base de données qui fait plus de 1 000 lignes et 30 colonnes, ces colonnes indiquent le numéro de situ, la date, le % de facture et le solde.
Je dois synthétiser dans un autre tableau le total financier de la dernière situ par client et par détail, par exemple le client tata a une facture de 6€ pour les pêches achetées avant le 01/09/2016.
Je n'arrive à rien .... Est ce que quelqu'un peut m'aider

job75 · 25 Septembre 2019

Bonjour Itchythemouse, excfl,

Ce n'est pas clair du tout, vous dites :

Itchythemouse à dit:
Je dois synthétiser dans un autre tableau le total financier de la dernière situ par client et par détail

alors sur un exemple créez le tableau que vous voulez obtenir.

A+

excfl · 25 Septembre 2019

Bonjour le forum,

Proposition :

E29 :
=SOMMEPROD((A2:A25=D29)*(F2:F25<=C29)*(B2:B25="peche")*(H2:H25))+SOMMEPROD((A2:A25=D29)*(K2:K25<=C29)*(B2:B25="peche")*(M2:M25))+SOMMEPROD((A2:A25=D29)*(Q2:Q25<=C29)*(B2:B25="peche")*(S2:S25))

Bonjour job75

Itchythemouse · 25 Septembre 2019

job75 à dit:
Bonjour Itchythemouse, excfl,

Ce n'est pas clair du tout, vous dites :

alors sur un exemple créez le tableau que vous voulez obtenir.

A+

Bonjour et merci de votre réponse, je vous joins le document modifié

Itchythemouse · 25 Septembre 2019

excfl à dit:
Bonjour le forum,

Proposition :

E29 :
=SOMMEPROD((A2:A25=D29)*(F2:F25<=C29)*(B2:B25="peche")*(H2:H25))+SOMMEPROD((A2:A25=D29)*(K2:K25<=C29)*(B2:B25="peche")*(M2:M25))+SOMMEPROD((A2:A25=D29)*(Q2:Q25<=C29)*(B2:B25="peche")*(S2:S25))

Bonjour job75

Super ça marche merci de votre retour, mais comme je suis un peu pénible j'ai plus de 30 colonnes sur 1000 lignes est ce qu'il n'y a pas une formule "plus simple" ?

job75 · 25 Septembre 2019

Je ne comprends toujours pas !

1) Vous dites que la formule de excfl convient alors qu'elle fait la somme de 3 SOMMEPROD portant sur les colonnes L M S qui sont elles-mêmes des totaux cumulés. Testez-la avec le mot "POIRE", elle donne 13,50 € alors que le résultat doit être 7,50 €.

2) Dans votre fichier du post #4 Feuil2 vous indiquez le mois "AVRIL" alors qu'auparavant il s'agissait de la date d'observation 01/09/2016.

3) Votre tableau de résultats n'a qu'une ligne : faut-il lister tous les DETAILS du client "TATA" ?

Itchythemouse · 25 Septembre 2019

job75 à dit:
Je ne comprends toujours pas !

1) Vous dites que la formule de excfl convient alors qu'elle fait la somme de 3 SOMMEPROD portant sur les colonnes L M S qui sont elles-mêmes des totaux cumulés. Testez-la avec le mot "POIRE", elle donne 13,50 € alors que le résultat doit être 7,50 €.

2) Dans votre fichier du post #4 Feuil2 vous indiquez le mois "AVRIL" alors qu'auparavant il s'agissait de la date d'observation 01/09/2016.

3) Votre tableau de résultats n'a qu'une ligne : faut-il lister tous les DETAILS du client "TATA" ?

Je l'ai testé à mon tour sur poire et je trouve bien le total des situ pour n'importe quel client et n'importe quel détails je vous joins le tableau

job75 · 25 Septembre 2019

Oui d'accord maintenant vous faites la somme des colonnes H N T, ce n'est plus la formule d'excfl qui utilisait H M S.

Et mes 2 autres questions ?

chris · 25 Septembre 2019

Bonjour à tous

Si les 30 colonnes correspondent à des groupes de situations, cela va faire beaucoup de SOMMEPROD à additionner

Il est possible de normaliser le tableau par une requête PowerQuery, intégré à Excel, puis d'extraire ce qui correspond aux choix.

Comme job75 je m'interroge sur le résultat unique ou la demande initiale "Je dois synthétiser dans un autre tableau le total financier de la dernière situ par client et par détail"

Tu parlais de dernière situ et pas d'un choix de date et "par client et par détail" et non selon des choix...

job75 · 25 Septembre 2019

Bon voyez le fichier joint, les formules matricielles en A5 et B5 et le nom défini lig.

La formule en B5 recherche la position de la date maximum.

Edit : bonjour chère chris

job75 · 25 Septembre 2019

Notez que la solution précédente suppose que les valeurs en euros ne dépassent pas le minimum des dates, soit 41228 pour le 15/11/2012.

chris · 25 Septembre 2019

Waouh, belle formule !

job75 · 26 Septembre 2019

Bonjour Itchythemouse, excfl, chris,

Une solution VBA dans le fichier joint avec ces macros dans le code de la 2ème feuille (clic droit sur l'onglet et Visualiser le code) :

VB:

Private Sub Worksheet_Activate()
Worksheet_Change [A1] 'lance la macro
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dat, client$, ncol%, tablo, resu(), i&, n&, datmax&, j%, x As Variant
dat = [B1]: client = [B2] 'à adapter
If IsDate(dat) And client <> "" Then
    With Sheets("BDD").[A1].CurrentRegion 'à adapter
        ncol = .Columns.Count
        If ncol = 1 Then ncol = 2
        tablo = .Resize(, ncol) 'matrice, plus rapide, au moins 2 éléments
    End With
    ReDim resu(1 To UBound(tablo), 1 To 2)
    For i = 1 To UBound(tablo)
        If tablo(i, 1) = client And tablo(i, 2) <> "" Then
            n = n + 1
            resu(n, 1) = tablo(i, 2)
            datmax = 0
            For j = 2 To ncol
                x = tablo(i, j)
                If IsDate(x) Then If x <= dat Then If x > datmax Then datmax = x: resu(n, 2) = tablo(i, j + 2) 'décalage de 2 colonnes
            Next
        End If
    Next i
End If
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A5] '1ère cellule, à adapter
    Application.EnableEvents = False 'désactive les évènements
    If n Then
        .Resize(n, 2) = resu
        .Resize(n, 2).Interior.ColorIndex = 19 'jaune clair
        .Resize(n, 2).Borders.Weight = xlHairline 'bordures
        .EntireColumn.AutoFit 'ajustement largeur
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 2).Delete xlUp
    Application.EnableEvents = True 'réactive les évènements
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Les macros se déclenchent quand on active la feuille ou quand on modifie une cellule quelconque.

Edit : pour tester j'ai recopié le tableau source sur 50 000 lignes, la durée d'exécution de la macro est de 1,1 (TOTO) à 1,4 (BIBI) seconde.

A+

Microsoft 365 Trouver une somme en fonction d'une date

Itchythemouse

XLDnaute Nouveau

Pièces jointes

job75

XLDnaute Barbatruc

excfl

XLDnaute Barbatruc

Itchythemouse

XLDnaute Nouveau

Pièces jointes

Itchythemouse

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Itchythemouse

XLDnaute Nouveau

Pièces jointes

job75

XLDnaute Barbatruc

chris

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

chris

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

Discussions similaires