XL 2016 Somme matricielle onglets non contigus

ALE37

XLDnaute Junior
Bonsoir le forum,

Je suis amené à consolider des onglets suivant des périmètres de gestion bien définis.
L'onglet GRAND OUEST dans mon fichier consolide les onglets OUEST, CENTRE OUEST et SUD OUEST.
Quand les onglets sont contiguës rien de plus facile mais si jamais une réorganisation se met en place (et c'est très fréquent)
et que l'un des onglets est déplacé (imaginons SUD OUEST) ou intercalé avec d'autres onglets, la formule de consolidation de base (ex dans le fichier joint) =Somme(OUEST:SUD OUEST!C11) ne marche bien évidemment pas et la conso GRAND OUEST devient fausse.
Existe t il un moyen simple (en VBA ou pas) pour contourner ce problème (les tableaux sont toujours les mêmes). J'ai pas mal regardé sur les forums
mais je n'ai rien trouvé de particulier.

Merci de votre aide.
 

Pièces jointes

  • Somme Matricielle.xlsx
    81.9 KB · Affichages: 13
Solution
Bonjour ALE37, le forum,

L'argument reference peut être un nom défini. représentant des plages variables (dynamiques).

Ce nom ne doit pas être défini dans le classeur mais dans chacune des feuilles listées.

Voyez le fichier joint et la formule en Consolidation!D4 =Sommevba("Montant")

Bonne journée.

job75

XLDnaute Barbatruc
La solution =SOMME('OUEST:SUD OUEST'!C11) ne fonctionne que sur des feuilles qui se suivent, il n'y a vraiment rien d'autre à comprendre.

En VBA on peut faire une boucle sur les feuilles dont le nom se termine par "OUEST".
 

ALE37

XLDnaute Junior
La solution =SOMME('OUEST:SUD OUEST'!C11) ne fonctionne que sur des feuilles qui se suivent, il n'y a vraiment rien d'autre à comprendre.

En VBA on peut faire une boucle sur les feuilles dont le nom se termine par "OUEST".
Bonjour Job, merci pour l’info. J’ai vu autrefois une video Tuto en Anglais sur l’utilisation de SUM et qui repondait à la question. On pouvait déplacer les onglets sans difficultés et conserver le calcul. Je n’ai pas réussi à l’adapter. Il devait manquer un argument.
 

ALE37

XLDnaute Junior
Bonsoir, j'ai retravaillé un peu sur le sujet. J'arrive à obtenir le résultat souhaité mais il ne s'affiche pas directement dans la cellule. Il faut que je crée un bouton (MSGBOX) pour y arriver. Quelle est l'astuce pour que l'affichage du résultat se fasse automatiquement dans la cellule de mon onglet conso? Ici G11 (mon code ci dessous). D'avance merci pour votre aide.

Sub Sommevba()
'somme plage sur onglets non contiguës
'---------------------------------------------------------------------
Set Plage_1 = Sheets("ARBPL").Range("G11")
Set Plage_2 = Sheets("ARATLA").Range("G11")
Range("G11") = Application.WorksheetFunction.Sum(Plage_1, Plage_2)

End Sub
 

job75

XLDnaute Barbatruc
Voyez le fichier joint et cette fonction VBA :
VB:
Function Sommevba() As Double
Application.Volatile
Dim a, adr As String, i As Integer, v As Variant
a = Array("ARBPL", "ARATLA") 'liste des feuilles a traiter, à adapter
adr = Application.Caller.Address
For i = 0 To UBound(a)
    v = Sheets(a(i)).Range(adr)
    If IsNumeric(v) Then Sommevba = Sommevba + v
Next
End Function
Elle est volatile pour qu'elle se recalcule quand une cellule source est modifiée.

Le code doit être placé impérativement dans un module standard, formule en G11 =Sommevba()
 

Pièces jointes

  • SommeVBA(1).xlsm
    107.3 KB · Affichages: 3

ALE37

XLDnaute Junior
Voyez le fichier joint et cette fonction VBA :
VB:
Function Sommevba() As Double
Application.Volatile
Dim a, adr As String, i As Integer, v As Variant
a = Array("ARBPL", "ARATLA") 'liste des feuilles a traiter, à adapter
adr = Application.Caller.Address
For i = 0 To UBound(a)
    v = Sheets(a(i)).Range(adr)
    If IsNumeric(v) Then Sommevba = Sommevba + v
Next
End Function
Elle est volatile pour qu'elle se recalcule quand une cellule source est modifiée.

Le code doit être placé impérativement dans un module standard, formule en G11 =Sommevba()
Bonsoir Job75, merci pour le fichier. ça marche nickel! C'est trés pratique dans le cas des consos d'onglets. Est il possible de sélectionner une plage de cellule et de lui donner l'indication de faire la somme pour la plage en question ?
 

job75

XLDnaute Barbatruc
Bonsoir ALE37,

Vous utiliserez alors cette fonction avec un argument :
VB:
Function Sommevba(reference As String) As Double
Application.Volatile
Dim a, i As Integer
a = Array("ARBPL", "ARATLA") 'liste des feuilles a traiter, à adapter
For i = 0 To UBound(a)
    Sommevba = Application.Sum(Sommevba, Sheets(a(i)).Range(reference))
Next
End Function
Dans la feuille de calcul la formule sera par exemple =Sommevba("B2:G10")

Bonne nuit.
 

job75

XLDnaute Barbatruc
Bonjour ALE37, le forum,

L'argument reference peut être un nom défini. représentant des plages variables (dynamiques).

Ce nom ne doit pas être défini dans le classeur mais dans chacune des feuilles listées.

Voyez le fichier joint et la formule en Consolidation!D4 =Sommevba("Montant")

Bonne journée.
 

Pièces jointes

  • Consolidation(1).xlsm
    20.6 KB · Affichages: 2

Statistiques des forums

Discussions
311 721
Messages
2 081 928
Membres
101 842
dernier inscrit
seb0390