Formules sur Matrice Multi-Feuilles
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