automatisation formule feuille avec tab structuré

Version imprimable

16/03/2021, 12h35
rossemma

2 pièce(s) jointe(s)

automatisation formule feuille avec tab structuré

Bonjour,
D'avance, merci pour votre aide, je suis débutant en VBA.

J'ai une feuille avec un tableau structuré qui ressemble à :

Pièce jointe 593514

J'ai des sous totaux spécifiques liés au tableau structuré.

Je voudrais que lorsque l'on ajoute une colonne, les sous totaux se fassent automatiquement ainsi que le fond gris.

je ne vois pas du tout comment faire

Je mets en annexe une fichier exemple avec les formules de somme.

Pièce jointe 593515

de plus petite question supplémentaire :
on ne sait pas bloquer une colonne comme on fait pour une cellule avec des $
je voudrais bloquer la colonne typ.

=SOMME.SI.ENS(Tableau1[tot1];Tableau1[typ];$B$7)
16/03/2021, 13h38
Pierre Fauconnier

3 pièce(s) jointe(s)

Salut.

Réponse à la seconde question: tu dois doubler la colonne que tu souhaites figer ET effectuer la recopier par un glissement sur la poignée de recopie

Pièce jointe 593521

Perso, dans ce cas-là, j'utilise les références classiques:

Pièce jointe 593524

Attention que l'analyse des données sur la même feuille que la feuille qui contient ces données est rarement une bonne idée. Ici, je partirais plus vite sur un TCD, avec éventuellement un dépivotage des colonnes par Power Query pour rendre l'ajout de la nouvelle colonne automatique dans le TCD

Normalement, ton tableau devrait être construit comme illustré sur la copie d'écran suivante (c'est ce que ferait le dépivotage en Power Query). Dès lors, l'analyse par TCD est ultra-simple.

Pièce jointe 593530

En fait, l'ajout du colonne ici amène à devoir modifier la structure du classeur (ajout de formules par VBA, ajout de colonne dans le TCD, ...) alors qu'en fait, l'intitulé de la nouvelle colonne EST une donnée, et ne devrait donc pas se retrouver en intitulé de colonne, mais dans les données d'une colonne, comme l'illustre la copie d'écran précédente. Si tu ne peux pas obtenir cela nativement, le dépivotage avec Power Query "remettra les données d'équerre" pour te permettre d'utiliser les outils d'analyse d'Excel, tels que les fonctions XXX.SI.ENS et les TCD.

Voici où j'en suis dans mon code, mais la formule somme.si.ens ne veut pas fonctionner

Code:

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
27
28
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim wk As Workbook
Dim ws As Worksheet
Dim tk As ListObject
Dim col1 As ListColumn
Dim col2 As ListColumn
Dim var1, ad1 As Variant
Dim DLign As Long
Dim i As Variant
 
Set wk = ThisWorkbook
Set ws = wk.Worksheets("Feuil1")
Set tk = ws.ListObjects("Tableau1")
Set col1 = tk.ListColumns("typ")
 
DLign = Range("A" & Rows.Count).End(xlUp).Row
 
If Not Application.Intersect(Target, Rows(1)) Is Nothing Then
    var1 = Target.Value
    Set col2 = ws.ListObjects("Tableau1").ListColumns(var1)
    ad1 = Split(Target.Address, "$")(1)
    For i = DLign + 2 To DLign + 3
        ws.Cells(i, ad1).FormulaLocal = "=SOMME.SI.ENS(col2;col1;cells(i,2))"
    Next i
End If
 
End Sub