XL 2016 Alternative à mon tcd

Excellerateur · 16 Septembre 2022

Bonjour chers très membres du forum,

je souhaite une alternative pour calculer les sommes des valeurs de deux colonnes qui sont attachées à d'autres valeurs (confère tableau joint).

En effet comme vous le verrez dans le fichier, j'ai conçu un tcd à partir de la feuille consolide, mais vu que le tcd ralenti énormément la macro par la suite,
je souhaite donc remplacer le tcd par des formules qui feraient le même travail.

J'ai beau essayé des sommes si mais je n'y arrive pas. Peut-être que l'un d'entre vous saurait mieux s'y prendre.

Merci d'avance de votre aide.

max.lander · 16 Septembre 2022

Bonjour à tous,

Un essai en PJ

Excellerateur · 16 Septembre 2022

max.lander à dit:
Bonjour à tous,

Un essai en PJ

Salut et merci beaucoup @max.lander pour ta contribution,

cependant, je crois que ta proposition ne résout que de moitié le problème.

En effet les colonnes qui m'intéressent (en prenant mon fichier comme réf et l'onglet tcd) sont les colonnes somme PF en "D" et en "F". Car elles sont distinctes avec la colonne sou "L" (de l'onglet consolide).

J'aimerais donc avoir une distinction de la somme PF selon que c'est Bill ou Gates.

j'espère que mon explication est claire.

Bien à vous.

cp4 · 16 Septembre 2022

Excellerateur à dit:
Salut et merci beaucoup @max.lander pour ta contribution,

cependant, je crois que ta proposition ne résout que de moitié le problème.

En effet les colonnes qui m'intéressent (en prenant mon fichier comme réf et l'onglet tcd) sont les colonnes somme PF en "D" et en "F". Car elles sont distinctes avec la colonne sou "L" (de l'onglet consolide).

J'aimerais donc avoir une distinction de la somme PF selon que c'est Bill ou Gates.

j'espère que mon explication est claire.

Bien à vous.

Bonjour,

J'ai ouvert le fichier réponse de @max.lander . Pourrais-tu nous données le tableau que tu veux obtenir. Ce qui est clair pour toi, ne l'est pas pour ceux qui veulent aider. Donc aide nous à t'aider. Merci.

Edit: pas très réactif donc un code d'après ce que j'ai compris. N'oublie pas d'adapter la feuille et la cellule de départ de destination.

VB:

Option Explicit

Sub consolider()
   Dim TbE(), TbS(), i As Long, Fc As Worksheet, d As Object, clé, lig As Integer
   Set Fc = ThisWorkbook.Sheets("consolide")
   Set d = CreateObject("Scripting.Dictionary")
   TbE = Fc.UsedRange.Value
   ReDim TbS(1 To UBound(TbE), 1 To 4)

   For i = LBound(TbE) + 1 To UBound(TbE)
      clé = TbE(i, 4) & "|" & TbE(i, 12)
      If d.exists(clé) Then
         lig = d(clé)                 ' Récupération index Tbs()
      Else
         d(clé) = d.Count + 1
         lig = d.Count
         TbS(lig, 1) = TbE(i, 4)
         TbS(lig, 2) = TbE(i, 12)  ' Nouvelle clé
      End If
       
         ' Totalisation colonnes numériques
         TbS(lig, 3) = TbS(lig, 3) + TbE(i, 7)
         TbS(lig, 4) = TbS(lig, 4) + TbE(i, 11)
   Next i

With Feuil3 ' feuille et cellule de départ à adapter
.[F1].CurrentRegion.Offset(1).Delete shift:=xlShiftUp
.[F2].Resize(d.Count, UBound(TbS, 2)) = TbS
.[F2].CurrentRegion.Borders.LineStyle = 1
End With
End Sub

max.lander · 16 Septembre 2022

Peut-être que c'est mieux là (cf.PJ)

Par contre, pour optimiser les temps d'exécution de la macro vous pouvez essayer le code suivant :

VB:

Application.ScreenUpdating = false
'''''''Exécution code
Application.ScreenUpdating = true

Excellerateur · 18 Septembre 2022

max.lander à dit:
Peut-être que c'est mieux là (cf.PJ)

Par contre, pour optimiser les temps d'exécution de la macro vous pouvez essayer le code suivant :

VB:

Application.ScreenUpdating = false '''''''Exécution code Application.ScreenUpdating = true

Salut cher fil,

Merci beaucoup @max.lander pour toutes tes contri.

Dans le même ordre de ce que tu as fait qui est déjà génial, je voudrais également que puisse apparaitre la colonne "B" de ma feuille tcd ( colonne "TxP").
En fait c'est une colonne dont les valeurs sont associées aux valeurs de la colonne "A" (colonne CodE I). En effet, cette configuration me permettra de voir les valeurs de la colonne "A" qui ont deux TxP (confère image jointe).

Si non pour le reste je t'avoue que je suis hyper impressionné par le rendu. Je ne comprends pas trop ta formule et je ne sais d'ailleurs pas comment je vais m'y prendre pour l'adapter à mon fichier (faut-il concevoir le tableau et y insérer la colonne CodE I avant de concevoir les autres??? ), mais je vais me débrouiller...

Excellerateur · 19 Septembre 2022

cp4 à dit:

Bonjour,

J'ai ouvert le fichier réponse de @max.lander . Pourrais-tu nous données le tableau que tu veux obtenir. Ce qui est clair pour toi, ne l'est pas pour ceux qui veulent aider. Donc aide nous à t'aider. Merci.

Edit: pas très réactif donc un code d'après ce que j'ai compris. N'oublie pas d'adapter la feuille et la cellule de départ de destination.

VB:

Option Explicit

Sub consolider()
   Dim TbE(), TbS(), i As Long, Fc As Worksheet, d As Object, clé, lig As Integer
   Set Fc = ThisWorkbook.Sheets("consolide")
   Set d = CreateObject("Scripting.Dictionary")
   TbE = Fc.UsedRange.Value
   ReDim TbS(1 To UBound(TbE), 1 To 4)

   For i = LBound(TbE) + 1 To UBound(TbE)
      clé = TbE(i, 4) & "|" & TbE(i, 12)
      If d.exists(clé) Then
         lig = d(clé)                 ' Récupération index Tbs()
      Else
         d(clé) = d.Count + 1
         lig = d.Count
         TbS(lig, 1) = TbE(i, 4)
         TbS(lig, 2) = TbE(i, 12)  ' Nouvelle clé
      End If
      
         ' Totalisation colonnes numériques
         TbS(lig, 3) = TbS(lig, 3) + TbE(i, 7)
         TbS(lig, 4) = TbS(lig, 4) + TbE(i, 11)
   Next i

With Feuil3 ' feuille et cellule de départ à adapter
.[F1].CurrentRegion.Offset(1).Delete shift:=xlShiftUp
.[F2].Resize(d.Count, UBound(TbS, 2)) = TbS
.[F2].CurrentRegion.Borders.LineStyle = 1
End With
End Sub

Bonjour @cp4

Désolé je n'avais pas vu ta réponse.

Ok je réexplique ce que je veux.

Dans les fichier que j'ai mis en copie on a deux feuilles (consolide et tcd). Le but est d'obtenir à partir de la feuille consolide une feuille qui se rapproche le plus de la feuille tcd (qui est un tableau croisé dynamique; et qui ralenti bcp ma macro). je souhaite donc (un peu comme l'a fait @max.lander une réplique du tcd (les colonnes qui m'intéressent sont les colonnes "A", "B", "C", "D", "E" et "F" tel que dans l'image jointe.

J'espère que ça s'est compris cette fois-ci.

cp4 · 19 Septembre 2022

Bonjour @max.lander , @Excellerateur ,

@Excellerateur : Faut-il faire le distingo entre les lignes Bill et Gates?

max.lander · 19 Septembre 2022

Ci-joint un nouveau test

Excellerateur · 20 Septembre 2022

max.lander à dit:
Ci-joint un nouveau test

Bonjour cher fil, bonjour "très cher" @max.lander

franchement je ne sais pas comment tu t'y prends, mais je sais juste que tu es un magicien hors échelle.

Ta formule est nette ce que je veux (fin je crois vraiment).

Mon souci c'est que je ne sais pas comment m'y prendre pour la réaliser.
Comment t'y prends-tu pour faire ressortir les colonnes "A" et "B"?
Puis après quelle est la procédure pour que la cellule "B1" pour "Bill" (ou "F1" pour "Gates") se rattache aux valeurs de l'onglet "consolide"?

franchement je ne connaissais pas cette formule mais je voudrais la comprendre un minimum.

merci encore tout de même.

max.lander · 20 Septembre 2022

Bonjour à tous,

@Excellerateur, sommeprod est une formule assez simple

Sommeprod((Colonne 1= critère 1) x (Colonne 2= critère 2) ....... x Colonne_à _calculer)

Je t'invite à consulter les différents tutos disponibles.

Dans ton exemple j'ai renommé les différentes colonnes utilisées :

Excellerateur · 20 Septembre 2022

max.lander à dit:
Bonjour à tous,

@Excellerateur, sommeprod est une formule assez simple

Sommeprod((Colonne 1= critère 1) x (Colonne 2= critère 2) ....... x Colonne_à _calculer)

Je t'invite à consulter les différents tutos disponibles.

Dans ton exemple j'ai renommé les différentes colonnes utilisées :

Regarde la pièce jointe 1150096

bonjour cher fil et forum,

Ok merci @max.lander j'ai vu quelques tutos et je vois les enjeux de la formule.

Mon souci est que je cherche à l'adapter en vba à un code existant.

je ne sais pas si je devrais faire un macro dans laquelle je nomme les colonnes utilisées.

Excellerateur · 20 Septembre 2022

max.lander à dit:
Bonjour à tous,

@Excellerateur, sommeprod est une formule assez simple

Sommeprod((Colonne 1= critère 1) x (Colonne 2= critère 2) ....... x Colonne_à _calculer)

Je t'invite à consulter les différents tutos disponibles.

Dans ton exemple j'ai renommé les différentes colonnes utilisées :

Regarde la pièce jointe 1150096

Et ma difficulté quand j'essaye de faire ma macro avec ta solution c'est que je ne sais pas comment définir la colonne de façon générale afin que ça tienne compte d'une situation dans laquelle on pourrait se retrouver avec plus ou moins de ligne que dans le cas actuel.
En gros je voudrais généraliser la définition des zones.

cp4 · 20 Septembre 2022

Bonsoir,
@Excellerateur : Apparemment, mes messages ne t’intéresses pas. J'étais occupé, j'ai pris un peu de mon temps pour modifier mon précédent code que je ne l'éditerais pas mais voici le rendu. Les résultats sont comparativement à ceux de @max.lander identiques.

Bonne soirée.

max.lander · 20 Septembre 2022

Salut @cp4,

@Excellerateur, si vous devez quoi qu'il arrive utiliser VBA, je vous conseille fortement de prendre
en base le code proposé par cp4.

XL 2016 Alternative à mon tcd

Excellerateur

XLDnaute Occasionnel

Pièces jointes

max.lander

XLDnaute Occasionnel

Pièces jointes

Excellerateur

XLDnaute Occasionnel

cp4

XLDnaute Barbatruc

max.lander

XLDnaute Occasionnel

Pièces jointes

Excellerateur

XLDnaute Occasionnel

Excellerateur

XLDnaute Occasionnel

cp4

XLDnaute Barbatruc

max.lander

XLDnaute Occasionnel

Pièces jointes

Excellerateur

XLDnaute Occasionnel

max.lander

XLDnaute Occasionnel

Excellerateur

XLDnaute Occasionnel

Excellerateur

XLDnaute Occasionnel

cp4

XLDnaute Barbatruc

max.lander

XLDnaute Occasionnel

Discussions similaires