XL 2016 Somme matricielle onglets non contigus

[ALE37]

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.

 

[CISCO]

Bonsoir

Tu trouveras peut-être ton bonheur sur le site de J. Boisgontier, là par exemple. Bien sûr, il faut fouiller...

@ plus

 

[job75]

Bonsoir ALE37, CISCO,

Bah il suffit de remplacer =SOMME('OUEST:SUD OUEST'!C11)

par =OUEST!C11+'CENTRE OUEST'!C11+'SUD OUEST'!C11

A+

 

[ALE37]

Bonsoir ALE37, CISCO,

Bah il suffit de remplacer =SOMME('OUEST:SUD OUEST'!C11)

par =OUEST!C11+'CENTRE OUEST'!C11+'SUD OUEST'!C11

A+

Bonsoir, oui mais le but était d'éviter ce type de solution. J'ai regardé le site de J.Boisgontier et il y a des formules approchantes. Je me demandais si on pouvait le faire par VBA.

 

[job75]

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".

 

[CISCO]

Bonsoir

Tu dois pouvoir faire avec ça, non ?

@ plus

 

[ALE37]

Bonsoir

Tu dois pouvoir faire avec ça, non ?

@ plus

Bonjour Cisco, c’est une piste. Je connais Le principe des boucles mais je n’en ai jamais fait. C’est l’occasion d’apprendre!

 

[ALE37]

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]

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]

Bonsoir ALE37,

Il n'y a plus de noms d'onglets avec "OUEST" ? Joignez alors votre fichier.

A priori le plus simple sera d'utiliser une fonction VBA. plutôt qu'une procédure Sub.

A+

 

[ALE37]

Sorry! voilà le fichier. J'ai renommé les onglets sur des comptes de résultats. D'avance merci.

 

[job75]

Voyez le fichier joint et cette fonction VBA :

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()

 

[ALE37]

Voyez le fichier joint et cette fonction VBA :

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]

Bonsoir ALE37,

Vous utiliserez alors cette fonction avec un argument :

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]

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.