XL 2016 Filtrer des données suivant valeur d'une cellule et résumé

luke3300

XLDnaute Impliqué
Bonjour le forum, :)

Je bloque depuis quelques jours sur le fichier ci-joint.
En fait, j'aimerais arriver à filtrer les données de mon tableau (A12:K2121) suivant la valeur de la cellule B2 qui compose les données de la colonne D.
J'aimerais arriver aussi à résumer les chiffres entrés en colonne K, cellule de 13 à 2121 dans les cellules L4 à L11 mais là je ne sais absolument pas comment je dois m'y prendre ...
Merci d'avance et agréable après-midi à toutes et tous.
 

Pièces jointes

  • Classeur1.xlsm
    161.9 KB · Affichages: 39

vgendron

XLDnaute Barbatruc
Hello
avec ce code dans l'évènement change du worksheet

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
   If Target.Address = Range("O13").Address Then
       Range("A12:K2121").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("O12:O13")
   End If
   If Not Intersect(Target, Range("B2")) Is Nothing Then
        Application.ScreenUpdating = False
        Range("Tableau2").Select
        Selection.AutoFilter

        ActiveSheet.ListObjects("Tableau2").Range.AutoFilter Field:=4, Criteria1:="=" & Range("B2"), Operator:=xlAnd
        Application.ScreenUpdating = True
   End If
End Sub
 

luke3300

XLDnaute Impliqué
Bonsoir vgendron et merci de te pencher sur mon souci :D
C'est génial, la solution fonctionne pour le filtre. Merci beaucoup!
Il reste à trouver pour le résumer des chiffres encodés, j'ai remis un détail dans le fichier adapté avec ton code ainsi je pense que ce sera plus simple à comprendre.
Très bonne soirée
 

Pièces jointes

  • Classeur1.xlsm
    163.5 KB · Affichages: 20

job75

XLDnaute Barbatruc
Bonjour luke3300, vgendron, le forum,

On peut tout faire dans la même macro :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B2]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With ListObjects(1).Range
    .AutoFilter
    .AutoFilter 4, [B2] 'filtre automatique
    Intersect(.Offset(1), [I:K]).Copy 'copie les colonnes I J K sans les titres
End With
With Workbooks.Add.Sheets(1)  'document auxiliaire vierge
    .[A1].PasteSpecial xlPasteValues 'collage spécial-valeurs
    With .UsedRange
        .Columns(3).Copy .Columns(4)
        If Application.CountA(.Columns(4)) Then .Columns(4).Sort .Cells(1, 4), xlAscending, Header:=xlNo 'tri
        .Columns(4).RemoveDuplicates 1, Header:=xlNo 'élimine les doublons
        .Columns(5).Resize(8) = "=SUMIF(" & .Columns(3).Address & ",D1," & .Columns(1).Address & ")"
        .Columns(6).Resize(8) = "=SUMIF(" & .Columns(3).Address & ",D1," & .Columns(2).Address & ")"
        [L4:N11] = .Columns(4).Resize(8, 3).Value 'restitution
    End With
    .Parent.Close False 'ferme le document auxiliaire
End With
End Sub
En M4:N11 les sommes des cellules visibles sont calculées.

Fichier joint.

Bonne journée.
 

Pièces jointes

  • Classeur(1).xlsm
    166.2 KB · Affichages: 25
Dernière édition:

job75

XLDnaute Barbatruc
Re,

Une autre solution avec une colonne A auxiliaire qui utilise cette fonction volatile :
Code:
Function Vu() As String
Application.Volatile
If Not Application.Caller.EntireRow.Hidden Then Vu = "X"
End Function
Formule matricielle en M4:M11 et SOMME.SI.ENS en N4:O11.

La macro Worksheet_Change est bien sûr revue.

Fichier (2), le recalcul des formules s'effectue en 0,08 seconde contre 0,28 seconde pour le fichier (1).

A+
 

Pièces jointes

  • Classeur(2).xlsm
    181.5 KB · Affichages: 23

luke3300

XLDnaute Impliqué
Bonjour job75, vgendron et le forum,

La 1ère solution, le filtre sur la valeur fonctionne mais pas je n'arrive pas à avoir le résultat dans la colonne M :(
Dans la 2ème, cela prend un rien plus de temps il me semble et j'ai des attentes après avoir introduit des valeurs en colonne L. Par contre le résultat recherché est obtenu dans les colonnes de M à O.
J'ai donc 2 questions par rapport à cette solution job75:
1. Y-a-t'il moyen d'accélérer le recalcul
2. Peut-on se passer de la colonne A? N'y-a-t'il pas moyen de faire sans?

Déjà un tout grand merci pour votre temps et votre partage ;)

Excellent WE à tous.
 

luke3300

XLDnaute Impliqué
Bonjour job75, le forum,

Je n'en disconviens pas que ça fonctionne chez toi pour la solution 1 mais chez moi ... voici ce que ça donne et tu pourras voir qu'aucune données ne s'ajoute dans la colonne M au fur et à mesure que je les introduis dans la colonne L ...
Démo vidéo visible ici:
Code:
https://1drv.ms/u/s!AqTY9I5DXZW8l_wRMf-0kKpusG3wNw
s!AqTY9I5DXZW8l_wQMf-0kKpusG3wNw


Par contre, dans la 2ème solution, c'est parfait! :D Je me demandais juste si la colonne A était utile, s'il n'y avait pas moyen de passer outre ou de faire sans, voir de faire en sorte qu'elle n'apparaisse pas ...
Démo vidéo visible ici:
Code:
https://1drv.ms/u/s!AqTY9I5DXZW8l_wSlr-oUAnGcbMgog

Il n'empêche que je te remercie vivement pour ton aide et surtout bon dimanche! :D:D:D ainsi qu'au forum ;)
s!AqTY9I5DXZW8l_wSlr-oUAnGcbMgog
 

job75

XLDnaute Barbatruc
Bonjour luke3300, le forum,
pour la solution 1 [...] tu pourras voir qu'aucune données ne s'ajoute dans la colonne M au fur et à mesure que je les introduis dans la colonne L ...
Bah il ne faut rien introduire en colonne L, quand on modifie B2 (filtre) la macro remplit toute seule le tableau L4:N11 :rolleyes:

Pour la 2ème solution la colonne auxiliaire A est indispensable.

Mais la fonction VBA n'est pas nécessaire, il est beaucoup mieux d'entrer cette formule en colonne A :
Code:
=REPT("X";SOUS.TOTAL(3;[@SC]))
Fichier (3), le recalcul des formules est plus rapide, il se fait chez moi en 0,03 seconde.

A+
 

Pièces jointes

  • Classeur(3).xlsm
    180.7 KB · Affichages: 22

luke3300

XLDnaute Impliqué
Re job75,

Bah il ne faut rien introduire en colonne L, quand on modifie B2 (filtre) la macro remplit toute seule le tableau L4:N11 :rolleyes:
Hé bien si job75. :(
Mais je me rend compte que je ne l'ai pas mentionné dans ma description de problème au début du fil ... en fait en colonne L (ligne de 13 à 2121) ce sont des données que j'introduis après avoir fait le filtrage. Et c'est la compilation de ces données introduites après que je recherche dans les cellules M4 à M11. Dans les cellules de N4 à N11, c'est l'addition de valeurs introduites dans d'autres lignes qui se compilent et s'additionnent. Ce que la solution 2 fait parfaitement ceci dit :D
 

job75

XLDnaute Barbatruc
Hé bien si job75. :(
Mais je me rend compte que je ne l'ai pas mentionné dans ma description de problème au début du fil ... en fait en colonne L (ligne de 13 à 2121) ce sont des données que j'introduis après avoir fait le filtrage.
OK je vois que vous mélangez les colonnes, avec le fichier (1) les entrées manuelles se font en K13:K2122.

Ces entrées n'ont bien sûr aucun effet sur le tableau L4:N11 puisque seule la modification de B2 (filtre) entraîne le remplissage de cette plage.
 

Discussions similaires

Réponses
7
Affichages
292