Microsoft 365 comment rendre dynamique une feuille synthese qui somme les memes cellules de feuilles differentes

Taristrou

XLDnaute Nouveau
Bonjour,

J'ai besoin de rendre dynamique ma feuille de synthèse qui somme les valeurs des mêmes cellules des feuilles suivantes.

Chaque feuille de données est nommée sur 4 caractères alphanumériques. Il peut y avoir jusqu'à une trentaine de feuilles différentes. Toutes les feuilles ont une structure identique.
La feuille de synthèse somme les valeurs d'une cellule sur toutes les feuilles en utilisant par exemple la formule =SOMME(MORZ:NAPO!B3) pour faire la somme des valeurs en B3 de toutes les feuilles entre la feuille MORZ et la feuille NAPO.

Pour rendre la sélection des feuilles dynamiques, une feuille Select permet de choisir la première feuille (MORZ par exemple) et la dernière feuille de l'analyse (NAPO par exemple).

Je n'arrive plus à obtenir les données quand je tente d'intégrer ces saisies. Je me suis inspirée d'autres posts du forum sur le sujet, mais je n'arrive à rien ...
Dans le fichier joint, j'ai mis uniquement 6 feuilles de données pour tester.

Merci pour votre aide !
 

Pièces jointes

  • Stocks&CA.xlsx
    62 KB · Affichages: 12
Solution
Bonjour à tous,
Une proposition par formule (pas trop simple!!) en B3, avec la liste des feuilles existantes et le choix en Feuille Select. Cette formule peut se recopier vers le bas et vers la droite, à tester.
Cordialement

Taristrou

XLDnaute Nouveau
Bonjour Taristrou, et bienvenu sur XLD,
Après moult essais je suis arrivé à la conclusion que Indirect est incompatible du multipages.
Peut être est ce une erreur d'appréciation et que quelqu'un vous trouvera une solution. :)
Sinon, êtes vous ouvert au VBA ?
Bonjour Sylvanu,

Je ne suis pas contre le VBA, mais mon niveau n'est pas terrible ...
Seule, je préfère utiliser Excel. Cela m'évite une migraine en VBA 🤣
Vous auriez une idée ?

Merci !
 

Taristrou

XLDnaute Nouveau
Bonjour à tous,

Une alternative avec decaler et indirect pour le total des mois.

JHA
Merci, mais cela ne correspond pas à ma demande.
Je ne veux pas que l'utilisateur soit obligé d'aller modifier toutes les colonnes et toutes les lignes de la feuille synthèse ...
J'ai mis juste une sélection dans le tableau, mais il y a normalement beaucoup plus de données à traiter.
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Vous auriez une idée ?
Perso, non.
Alors au cas où, une PJ avec cette macro :
VB:
Sub Worksheet_Activate()
    Dim Début$, Fin$, F, GoDeb%, GoFin%
    Sheets("Rapport").Range("B2:AV32").ClearContents ' on efface le tableau existant
    Application.ScreenUpdating = False
    Début = Sheets("Select").Range("B2")            ' feuille de début
    Fin = Sheets("Select").Range("B3")              ' feuille de fin
    For Each F In Worksheets                        ' on supprime les "-" car texte donc génère des erreurs
        Sheets(F.Name).Range("B2:AV32").Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    Next F
    GoDeb = 0: GoFin = 1
    For Each F In Worksheets
        If F.Name = Début Then GoDeb = 1            ' si feuille debut atteinte
        If GoDeb + GoFin = 2 Then                   ' si les deux flags valent 1 on additionne
            Formule = Formule & "+" & F.Name & "!B2"    ' on construit la formule
        End If
        If F.Name = Fin Then GoFin = 0              ' si feuille fin atteinte
    Next F
    [B2:AV32].FormulaLocal = "=" & Mid(Formule, 2)  ' on colle la formule
    [F2:F32].FormulaLocal = "=D2/C2"                ' on colle la formule calcul %
    [B2:AV32] = [B2:AV32].Value                     ' on remplace par les valeurs
End Sub
1- La macro s'active automatiquement lorsqu'on sélectionne la feuille "Rapport"
2- En colonne F je fais le ration colD/colC
3- Dans les feuilles j'ai supprimé les "-" qui sont du texte et génère des erreurs en VBA dans les formules.
 

Pièces jointes

  • Stocks&CA (4).xlsm
    78.3 KB · Affichages: 6

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Voici une proposition par requête power query et TCD

Lorsque les données ou la sélection De A changent, enregistrer le classeur puis actualiser tout (Données/Actualiser tout)

Comme il n'y a pas de tableau structuré dans le fichier, la requête l'interroge comme un fichier extérieur, c'est pourquoi j'ai rajouté la variable "Fichier" dans le tableau (devenu structuré) de sélections, vérifier sa valeur avant de rafraîchir la requête ou le tcd

Pour certaines colonnes on ne sait pas s'il s'agit de valeurs monétaires ou unitaires à vérifier.
 

Pièces jointes

  • PQ-Rapport.xlsx
    94.2 KB · Affichages: 8

ALS35

XLDnaute Occasionnel
Bonjour à tous,
Une proposition par formule (pas trop simple!!) en B3, avec la liste des feuilles existantes et le choix en Feuille Select. Cette formule peut se recopier vers le bas et vers la droite, à tester.
Cordialement
 

Pièces jointes

  • Stocks&CA Modifié.xlsx
    63 KB · Affichages: 9

Taristrou

XLDnaute Nouveau
Perso, non.
Alors au cas où, une PJ avec cette macro :
VB:
Sub Worksheet_Activate()
    Dim Début$, Fin$, F, GoDeb%, GoFin%
    Sheets("Rapport").Range("B2:AV32").ClearContents ' on efface le tableau existant
    Application.ScreenUpdating = False
    Début = Sheets("Select").Range("B2")            ' feuille de début
    Fin = Sheets("Select").Range("B3")              ' feuille de fin
    For Each F In Worksheets                        ' on supprime les "-" car texte donc génère des erreurs
        Sheets(F.Name).Range("B2:AV32").Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    Next F
    GoDeb = 0: GoFin = 1
    For Each F In Worksheets
        If F.Name = Début Then GoDeb = 1            ' si feuille debut atteinte
        If GoDeb + GoFin = 2 Then                   ' si les deux flags valent 1 on additionne
            Formule = Formule & "+" & F.Name & "!B2"    ' on construit la formule
        End If
        If F.Name = Fin Then GoFin = 0              ' si feuille fin atteinte
    Next F
    [B2:AV32].FormulaLocal = "=" & Mid(Formule, 2)  ' on colle la formule
    [F2:F32].FormulaLocal = "=D2/C2"                ' on colle la formule calcul %
    [B2:AV32] = [B2:AV32].Value                     ' on remplace par les valeurs
End Sub
1- La macro s'active automatiquement lorsqu'on sélectionne la feuille "Rapport"
2- En colonne F je fais le ration colD/colC
3- Dans les feuilles j'ai supprimé les "-" qui sont du texte et génère des erreurs en VBA dans les formules.
Bonjour Sylvanu,

Merci pour le code ! Je vais tester.

Cordialement
 

Taristrou

XLDnaute Nouveau
Bonjour,

Voici une proposition par requête power query et TCD

Lorsque les données ou la sélection De A changent, enregistrer le classeur puis actualiser tout (Données/Actualiser tout)

Comme il n'y a pas de tableau structuré dans le fichier, la requête l'interroge comme un fichier extérieur, c'est pourquoi j'ai rajouté la variable "Fichier" dans le tableau (devenu structuré) de sélections, vérifier sa valeur avant de rafraîchir la requête ou le tcd

Pour certaines colonnes on ne sait pas s'il s'agit de valeurs monétaires ou unitaires à vérifier.
Bonjour Hasco,

Cela fonctionne, et c'est magique pour moi !
J'en demande beaucoup ... mais si vous avez 5 minutes, si vous pouviez m'expliquer rapidement les différentes étapes, en particulier dans Power Query, cela pourra m'aider à réadapter cela à mon fichier final.

Merci beaucoup !
Cordialement,
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour Hasco,

Cela fonctionne, et c'est magique pour moi !
J'en demande beaucoup ... mais si vous avez 5 minutes, si vous pouviez m'expliquer rapidement les différentes étapes, en particulier dans Power Query, cela pourra m'aider à réadapter cela à mon fichier final.

Merci beaucoup !
Cordialement,
Bonjour,
Dès que j'aurai un peu de temps, je vous répondrai plus longuement. Je ne suis ici qu'en "pointillé" ces jours ci
Bonne journée
 

job75

XLDnaute Barbatruc
Bonjour Taristrou, le forum,

Plusieurs remarques sur la formule utilisée par ALS35 au post #13 :

- le principe est celui utilisé par le regretté Jacques Boisgontier sur son site

- mais la formule ne fait aucune somme (chez moi sur Excel 2019), pour cela il faut ajouter la fonction N() qui a été oubliée

- on peut utiliser SOMMEPROD ce qui évite la validation matricielle sur les versions autres que MS 365.

Voyez le fichier joint qui traite les colonnes A à E, pour les colonnes suivantes il faut dans les feuilles :

- remplacer le point par le séparateur décimal (la virgule) pour convertir les textes en nombres

- supprimer les "EUR".

A+
 

Pièces jointes

  • Stocks&CA Modifié V3.xlsx
    63.3 KB · Affichages: 3
Dernière édition:

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Voici une version avec une petite macro qui actualise la requête et le rapport lorsque vous aurez changé les valeurs des paramètres DE, A.

J'ai commenté les lignes de la requête.

Comme c'est un classeur avec macro venant du web (téléchargé) vous aurez peut-être à le débloquer avant de l'ouvrir pour faire tourner la macro : click-droit sur son nom dans l'explorateur de fichiers, puis 'propriétés' et enfin cocher la case 'débloquer' en bas de la fenêtre de propriétés. Validez.

Cordialement
 

Pièces jointes

  • PQ-Rapport.xlsm
    109.7 KB · Affichages: 6

Discussions similaires

Statistiques des forums

Discussions
313 275
Messages
2 096 757
Membres
106 742
dernier inscrit
claudetreguer