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.
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
Cet article est régulièrement mis à jour par des experts sous la direction de Jean-François Pillou, fondateur de CommentCaMarche et directeur délégué au développement numérique du groupe Figaro.
Le sommaire est le suivant:
