XL 2016 Alternative à mon tcd

E

Excellerateur

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

Pièces jointes

  • Prime test.xlsm
    538.9 KB · Affichages: 10
Trier par date Trier par votes
E

Excellerateur

XLDnaute Occasionnel
max.lander à dit:
Bonjour à tous,

Un essai en PJ
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
cp4

cp4

XLDnaute Barbatruc
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.
Cliquez pour agrandir...
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
 
Dernière édition:
Vote positif 0 Vote négatif
E

Excellerateur

XLDnaute Occasionnel
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
Cliquez pour agrandir...
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).

code I.PNG


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...
 
Vote positif 0 Vote négatif
E

Excellerateur

XLDnaute Occasionnel
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
Cliquez pour agrandir...
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.
colonnes.PNG
 
Vote positif 0 Vote négatif
E

Excellerateur

XLDnaute Occasionnel
max.lander à dit:
Ci-joint un nouveau test
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
E

Excellerateur

XLDnaute Occasionnel
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
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
E

Excellerateur

XLDnaute Occasionnel
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
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif

Discussions similaires

Y
  • Question
XL 2013 Récupérer les bornes d'une chronologie
Réponses
0
Affichages
247
Yvan1
Y
S
  • Question
XL 2013 TCD sur plusieurs onglets
Réponses
6
Affichages
399
WTF
WTF
E
  • Résolu(e)
XL 2016 Code VBA pour créer un TCD
Réponses
11
Affichages
839
Excellerateur
E
E
  • Question
Microsoft 365 associer la valeur d'une cellule à ma macro en sélectionnant la bonne extension année
Réponses
5
Affichages
501
Hasco
Hasco
N
  • Question
Microsoft 365 TCD pour CRA collaborateurs
Réponses
8
Affichages
2 K
nours955i
N
P
  • Question
XL 2019 Rotation automatique entre deux feuilles
Réponses
11
Affichages
1 K
Phil69970
Phil69970
L
  • Résolu(e)
XL 2019 Copier/Coller valeur + couleur d'un classeur à un autre
Réponses
7
Affichages
1 K
Etoto
Etoto
E
  • Question
TCD obtenu par Power Pivot et champ calculé Excel 2016
Réponses
6
Affichages
4 K
chris
chris
R
  • Question
Création d'un message d'alerte à l'approche d'une date Excel 2013
Réponses
6
Affichages
2 K
Ronancoca
R
E
  • Question
XL 2013 Mise en forme d'un graphique lié à un TCD
Réponses
5
Affichages
1 K
chris
chris

Membres actuellement en ligne

11 membres et 63 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 215
Messages
2 086 324
Membres
103 179
dernier inscrit
BERSEB50