Formules sur Matrice Multi-Feuilles

Décembre 2016


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

A voir également :

Ce document intitulé «  Formules sur Matrice Multi-Feuilles  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.