Formules sur feuilles multiples

Le sommaire est le suivant:

Introduction

Seules les Formules Somme, Moyenne, Nb, Nbval permettent de faire des calculs sur des plages discontinues ou Multi-Feuilles (Calcul en 3 Dimensions).
exemple :
=Somme(Feuil1:Feuil3!A1:A10) : effectue la somme de la plage A1:A10 des 3 feuilles.

Somme.si, Nb.si, Sommeprod, Index, Equiv ainsi que les formules matricielles ne le font pas, même avec des plages nommées.

Cette Fonction personnalisée n'a pas pour but de ré-écrire les différentes formules, mais transforme une plage discontinue ou une plage sur un certain nombre de feuilles en une seule matrice.

Syntaxe

=Trf_plage(Ma Plage; "Feuil1:Feuil3")

• Ma plage : La plage à traiter exemple : A1:A10
• "Feuil1:Feuil3" : Les feuilles à traiter, un bloc de feuille (1ère Feuille : Dernière Feuille) ce paramètre est optionnel, pour les plages discontinues ne nécessitant pas de Multi-Feuilles.

Exemples

Je tiens à préciser que les formules nb.si ou somme.si ne fonctionnent toujours pas, mais elles peuvent êtres facilement transformées en formules matricielles.
Index associé à Equiv Fonctionne très bien. (Nous avons maintenant une recherche Multi-Feuilles).

Les formules sur fond roses sont des Formules matricielles.
Elle doivent être validées en appuyant simultanément sur les touches Ctrl + Maj + Entrée. Si la validation est correctement effectuée, des {} encadrent automatiquement la formule.


Téléchargez le fichier d'exemples en cliquant ici

Code

Voici le code de la fonction personnalisée, à placer dans un module standard.

Function M_Charge(plage As Range, Optional feuilles As String = "") As Variant
    Dim cel As Range, i As Long, j As Integer, tablo() As Variant, tablof() As Variant
    Dim f As Integer, feuille1 As String, feuille2 As String
    Application.Volatile ' Permet un recalcul automatique
' Définition de la feuille par défaut si aucune feuille n'est mentionnée
    If feuilles = "" Then feuilles = ActiveSheet.Name & ":" & ActiveSheet.Name
    i = -1
    If InStr(feuilles, ",") > 0 Then ' traitement des feuilles non contigues (séparées par des virgules)
        While InStr(feuilles, ",") > 0
            i = i + 1
            ReDim Preserve tablof(i)
            tablof(i) = Left(feuilles, InStr(feuilles, ",") - 1)
            feuilles = Mid(feuilles, InStr(feuilles, ",") + 1, Len(feuilles) - InStr(feuilles, ","))
        Wend
    End If
    i = i + 1
    ReDim Preserve tablof(i)
    tablof(i) = feuilles
    i = -1
    For f = LBound(tablof) To UBound(tablof) ' traite les différent blocs de feuilles
        feuilles = tablof(f)
        If InStr(feuilles, ":") = 0 Then feuilles = feuilles & ":" & feuilles ' je crée le bloc la feuille est seule
' Récupération de la feuille de début et la feuille de fin
        feuille1 = Left(feuilles, InStr(feuilles, ":") - 1)
        feuille2 = Right(feuilles, Len(feuilles) - InStr(feuilles, ":"))
' Passage en revue de toutes les feuilles entre Feuille1 et Feuille2
        For j = Sheets(feuille1).Index To Sheets(feuille2).Index  ' Each ws In Sheets(feuilles)
            For Each cel In plage ' Pour chacune des cellules de la plage
                i = i + 1
                ReDim Preserve tablo(i) ' J'incrémente la table en cours de création
                tablo(i) = Sheets(j).Cells(cel.Row, cel.Column).Value ' Récupélation de la valeur
            Next
        Next j
    Next f
        M_Charge = tablo ' Affectation du tableau à la fonction (la matrice est créée)
End Function