Discussion :

[Jason91]

Bonjour,

Alors c'est peut-être la fatigue, mais je cherche à trier un tableau d'une liste de nomenclature comprenant les colonnes:
Fabricant/Référence/Désignation/Quantité

Chaque ligne contenant des données étant répétées plusieurs fois dans le tableau. Ex:

Fabricant	Référence	Description commerciale	Quantité
SIEMENS	6ES7131-6BF00-0CA0	SIMATIC ET200SP, DI 8x24VDC HF, MODULE D'ENTREE 8E TOR 24VDC SINK INPUT (PNP)	2
SIEMENS	6ES7131-6BF00-0CA0	SIMATIC ET200SP, DI 8x24VDC HF, MODULE D'ENTREE 8E TOR 24VDC SINK INPUT (PNP)	1
SIEMENS	6ES7131-6BF00-0CA0	SIMATIC ET200SP, DI 8x24VDC HF, MODULE D'ENTREE 8E TOR 24VDC SINK INPUT (PNP)	3

Si je veux le nombre de chaque référence, je n'ai plusieurs solutions, mais le résultat est que je n'obtient qu'un tableau à deux colonnes:
Référence et Quantité (dont on fait la somme):

Référence	Somme de:Quantité
6ES7131-6BF00-0CA0	6

Je voudrais le résultat en tableau avec toutes ses colonnes et avoir la colonne quantité de références:

Fabricant	Référence	Description commerciale	Quantité
SIEMENS	6ES7131-6BF00-0CA0	SIMATIC ET200SP, DI 8x24VDC HF, MODULE D'ENTREE 8E TOR 24VDC SINK INPUT (PNP)	6

Je pense que c'est une fonction basique d'Excel mais là je sèche!

Merci de votre aide

[Malick]

Salut,

Une propostion avec transformation du tableau initiale en tableau structuré et création d'un TCD.

Lorsque des données seront ajoutées, il faudra juste faire un clic-droit sur le TCD et actualiser pour les prendre en compte.

Il faut noter également que cela est possible de le faire directement avec Power Query sans passer par un TCD.

[BENNASR]

Bonjour,

Selon les experts du site et surtout tu as XL-365 powerQuery est dédié pour ce genre de traitement (fortement recommandé)

Si non une solution VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub testttt()
Application.ScreenUpdating = False
Dim F1 As Worksheet
Dim F2 As Worksheet
Set F1 = Sheets("BASE")
Set F2 = Sheets("RESULTAT")
F2.Cells.ClearContents
Dim i As Long
On Error Resume Next
Set d = CreateObject("Scripting.Dictionary")
    TblBD = F1.Range("A2:D" & F1.Range("D" & Rows.Count).End(xlUp).Row)
    For i = 1 To UBound(TblBD)
    clé = TblBD(i, 1) & "|" & TblBD(i, 2) & "|" & TblBD(i, 3)
    d(clé) = d(clé) + TblBD(i, 4)
    Next i
 F2.Cells(1, 1) = "Fabricant"
 F2.Cells(1, 2) = "Référence"
 F2.Cells(1, 3) = "Description commerciale"
 F2.Cells(1, 4) = "Quantité"
 F2.Range("A2").Resize(d.Count) = Application.Transpose(d.keys)
 F2.Range("D2").Resize(d.Count) = Application.Transpose(d.items)
  Application.DisplayAlerts = False
  F2.Range("A2").Resize(d.Count).TextToColumns Other:=1, DataType:=xlDelimited, OtherChar:="|"
Application.ScreenUpdating = True
F2.Select
End Sub

[Pierre Fauconnier]

Salut.

Je plussoie Malick

Power Query réalise cela en quelques clics intuitifs et le résultat est mis à jour par simple clic droit! Un tableau croisé dynamique est tout aussi simple à mettre en place (et ce truc existe depuis plus de 20 ans).

Par rapport à une solution VBA, il n'y a pas photo, à mon avis, puisque cette solution

• utilise VBA et impose donc le xlsm alors que ce n'est pas nécessaire;
• utilise un scripting.dictionary alors que ce n'est pas nécessaire;
• utilise mal le dictionary vu l'absence de test sur l'existence de la clé;
• utilise un On Error Resume Next pour pallier le manque de test sur la clé, camouflant d'autres erreurs éventuelles;
• n'utilise pas les tableaux structurés ce qui verrouille la solution aux cellules utilisées dans le code ();
• utilise des données métier en dur dans le code;
• utilise les noms d'onglets plutôt que les codename (alors que les tableaux structurés évitent de parler des feuilles ^^);
• supprime toutes les données d'une feuille sans prévenir le demandeur alors que rien de tel n'a été demandé;
• recompose par code des "tableaux" alors que cela devrait faire partie de la conception du classeur et, sauf à de très rares exceptions près non rencontrées ici, être réalisé en Excel et pas en VBA;
• utilise Rows.Count sans parentalité, ce qui peut générer une erreur (qui sera masquée à cause du On Error Resume Next ^^);
• impose deux Transpose, toujours coûteux;
• impose un TextToColumns totalement inutile (car la concaténation des clés pour le dictionnaire est inutile, puisque le dico est inutile, et coûteuse);
• sélectionne une feuille alors que ce n'est absolument pas demandé, et change donc l'interface utilisateur inutilement;
• ...,

[Pierre Fauconnier]

J'ose espérer que le moinsseur de mon message aura au moins eu la volonté d'apprendre quelque chose en le lisant et améliorera tant ses techniques Excel que ses techniques de programmation en VBA grâce à cela

[Malick]

Salut à tous,

Je pense qu'on gagnerait à utiliser Power Query associé aux Tableaux croisés Dynamiques pour faire des merveilles, cela tant que c'est possible. Le recours au VBA sera moindre.

Lire sans à volonté :

• Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)
• Power Query Excel : alternative efficace et dynamique aux filtres avancés

[Jason91]

Bonjour Pierre,

Je suis tes posts sur le site depuis quelques années et j'ai beaucoup progressé en VBA et même développer plusieurs applicatifs pour mon travail.
Je remercie aussi tous ceux qui ont répondu à ma question, quelle célérité!

Intuitivement j'ai bien entendu commencé par utiliser un TCD mais ça me donnait le résultat que j'ai indiqué. Ensuite testé aussi Power Query sans plus de succès.
Je voulais éviter de faire une macro VBA pour un truc 'basic', notamment lorsque tu utilises une base de données.
Je vais lire attentivement ta réponse et faire un essai avec ce que tu me propose pour le transposer à mon tableau ensuite.

Je reviens vers vous pour partager mon résultat.

merci

[Jason91]

Bonsoir,

J'ai testé la méthode proposée par Pierre, c'est exactement ce que je cherchais. Le tout était de comprendre comment fonctionne Power Query.

Merci encore pour votre aide