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

[Tef_31]

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

 

[chris]

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

 

[Tef_31]

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]

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

 

[Tef_31]

Oh super!

Merci beaucoup!

 

[chris]

RE

Regarde mon edit pour les filtres

 

[Tef_31]

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

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

 

[mapomme]

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":

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

 

[Tef_31]

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

 

[chris]

RE à tous

Attention mettre "" et non " "

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

 

[Tef_31]

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

Merci encore.

 

[chris]

RE

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

D'où le " "

 

[Tef_31]

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.

 

[mapomme]

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

 

[chris]

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