XL 2016 Extraction de données avec formule matricielle et tri

chris

XLDnaute Barbatruc

• 11 Avril 2021

• #2

Bonjour

Une extraction avec PowerQuery

Les cellules H3 et I3 sont respectivement nommée Debut et Fin et utilisées pour filtrer

Actualisable par une ligne de VBA sur changement de H2...

 

Pièces jointes

• Extraction_PQ.xlsx

  49 KB · Affichages: 10

Vote positif 0 Vote négatif

T

Tef_31

XLDnaute Nouveau

• 11 Avril 2021

• #3

Bonjour Chris,

Merci pour cette proposition très claire.
Je ne savais pas comment faire appel à une cellule externe dans PowerQuery.
Cela répond très bien à mon besoin et me permet de me passer des formules matricielles complexes.

Une autre question cependant, est-il possible de mettre à jour uniquement une requête via VBA, ou serais-je obligé de faire un ThisWorkbook.RefreshAll ?
Pour le coup cela peut-être pénalisant car le fichier original contient déjà pas mal de requêtes.

Merci.

 

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 11 Avril 2021

• #4

RE

Oui on peut rafraichir une seule requête. Voir Ci-joint

J'ai ajouté les chaînes vides dans le filtre (je n'avais filtré que null)

Edit : on a, je pense, intérêt à intervertir les 2 filtres, la date prenant plus de temps que d'éliminer les chaînes vides ou null

 

Pièces jointes

• Extraction_PQ.xlsm

  54.7 KB · Affichages: 13

Dernière édition: 11 Avril 2021

Vote positif 0 Vote négatif

Solution

T

Tef_31

XLDnaute Nouveau

• 11 Avril 2021

• #5

Oh super!

Merci beaucoup!

 

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 11 Avril 2021

• #6

RE

Regarde mon edit pour les filtres

 

Vote positif 0 Vote négatif

T

Tef_31

XLDnaute Nouveau

• 11 Avril 2021

• #7

Je ne pensais pas qu'il y avait une différence entre null et ""

[Assurance] <> null and [Assurance] <> " "

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 11 Avril 2021

• #8

Bonjour à tous,

Une version VBA.
Modifier la cellule H2 (la liste de validation) ou le tableau des valeurs sources.
La mise à jour est automatique quand on change les valeurs du tableau structuré "Tableau1" ou la cellule H2.

Le code est dans le module de la feuille "Feuil1":

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Object, t, date1, date2, i&
   If Intersect(Target, Union(Me.ListObjects(1).Range, Range("H2"))) Is Nothing Then Exit Sub
   On Error GoTo FIN: Application.ScreenUpdating = False
   t = Range("tableau1")
   Set d = CreateObject("scripting.dictionary"): d.comparemode = vbTextCompare
   date1 = Range("h3"): date2 = Range("i3")
   For i = 1 To UBound(t)
      If Trim(t(i, 5)) <> "" And t(i, 2) >= date1 And t(i, 2) <= date2 Then d(t(i, 5)) = d(t(i, 5)) + 1
   Next i
   Application.EnableEvents = False
   Range(Range("g5"), Cells(Rows.Count, "h")).Clear
   Range("g5") = ActiveSheet.ListObjects(1).HeaderRowRange(1, 5): Range("h5") = "Quantité"
   Range("g6").Resize(d.Count) = Application.Transpose(d.keys)
   Range("h6").Resize(d.Count) = Application.Transpose(d.items)
   Range("g5:h5").Resize(d.Count + 1).Sort key1:=Range("h5"), order1:=xlDescending, key2:=Range("g5"), order1:=xlAscending, Header:=xlYes
   Me.ListObjects.Add(xlSrcRange, Range("g5:h5").Resize(d.Count + 1), , xlYes).Name = "Tableau2"
FIN: Application.EnableEvents = True
End Sub

 

Pièces jointes

• Tef_31- Tableau- v1.xlsm

  39.2 KB · Affichages: 14

Dernière édition: 11 Avril 2021

Vote positif 0 Vote négatif

T

Tef_31

XLDnaute Nouveau

• 11 Avril 2021

• #9

Merci mapomme pour cette solution très intéressante également.

Elle a l'avantage d'être plus "instantanée" que la version avec "PowerQuery" mais pour le coup je suis moins à l'aise avec. Il faut que j'analyse le code pour le comprendre en détail.

Merci pour vos réponses qui répondent parfaitement à mon besoin. Comme souvent sur Excel il y a plusieurs façon d'arriver à un même résultat

 

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 11 Avril 2021

• #10

RE à tous

Attention mettre "" et non " "

On peut aussi adapter le code d'actualisation à la mise à jour du tableau source si nécessaire

 

Vote positif 0 Vote négatif

T

Tef_31

XLDnaute Nouveau

• 12 Avril 2021

• #11

Oui pour ça du coup avec ce que tu m'a montré je pense comprendre comment faire.

Merci encore.

 

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 12 Avril 2021

• #12

RE

Vérification faite tu avais bien un espace seul dans ta colonne Assurance.

D'où le " "

 

Vote positif 0 Vote négatif

T

Tef_31

XLDnaute Nouveau

• 12 Avril 2021

• #13

Finalement la mise à jour de la requête est assez longue sur le tableau original qui contient énormément de données. L'étape de filtrage par année prends beaucoup de temps.

@mapomme

Je vais passer par ton code finalement pour cette partie car plus rapide.
Deux questions :
- Est-il possible de lancer la mise à jour à partir du changement d'une zone de liste (voir ci-joint)?
- Comment faire pour afficher uniquement les valeurs supérieures à 10 (ou autre nombre paramétrable) sans utiliser de filtre?

Merci.

 

Pièces jointes

• Tef_31- Tableau- v2.xlsm

  43.9 KB · Affichages: 3

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 12 Avril 2021

• #14

Bonjour @Tef_31,

Un essai dans le fichier joint.
La cellule indiquant le seuil d'affichage a été nommée "limite" (menu Formules / Gestionnaire de noms

 

Pièces jointes

• Tef_31- Tableau- v2.xlsm

  43.9 KB · Affichages: 10

Dernière édition: 12 Avril 2021

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 12 Avril 2021

• #15

Bonjour à tous

Une variante du code de mapomme :

• j'ai considéré que le tableau Résultat existe
• le code est lié à la liste déroulante mais le changement de quantité mini le déclenche également

 

Pièces jointes

• Tef_31- Tableau- v3.xlsm

  44.2 KB · Affichages: 12

Dernière édition: 12 Avril 2021

Vote positif 0 Vote négatif