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

Tef_31

XLDnaute Nouveau
Bonjour à tous,

J’aurai besoin de votre aide sur un problème d’extraction de données assez complexe pour moi.
Je cherche à extraire des données d’un tableau avec certains critères. J’ai joint le fichier pour comprendre plus facilement.

Le tableau 1 est la base de données.
Le tableau 2 est celui ou je souhaite extraire mes données.

Ce que j’ai réussi à faire jusqu’à maintenant :
  • Extraire les références d’assurance uniques (colonne assurance)
  • Déterminer le nombre de fois où apparait l’assurance dont la date de réception est située entre la valeur de la cellule H4 et la valeur de la cellule I4.
Je passe par des formules matricielles car j’ai besoin que le tableau soit dynamique (les dates seront dynamiques via une zone de liste).

Je que je souhaiterai faire :
  • Extraire uniquement les données non vides (dans la colonne assurance) et adapter automatiquement la taille du tableau.
  • Réaliser un tri décroissant par quantité
Cela est possible en passant par une requête Power Query mais je ne sais pas rendre cette requête dynamique, c’est pourquoi je fais appel à vous pour essayer de passer par une ou plusieurs formules.

Je travaille sous Excel 2016 et je n’ai pas accès aux nouvelles fonctions (UNIQUE ou TRIER)

Merci d’avance pour votre aide.
Stéphane
 

Pièces jointes

  • Tableau.xlsx
    41.4 KB · Affichages: 25
Solution
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

chris

XLDnaute Barbatruc
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

Tef_31

XLDnaute Nouveau
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.
 

chris

XLDnaute Barbatruc
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:

mapomme

XLDnaute Barbatruc
Supporter XLD
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:

Tef_31

XLDnaute Nouveau
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 :)
 

Tef_31

XLDnaute Nouveau
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

chris

XLDnaute Barbatruc
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:

Discussions similaires

Statistiques des forums

Discussions
311 708
Messages
2 081 750
Membres
101 812
dernier inscrit
trufu