Somme recherchev sur plusieurs feuilles

[Matthieu14]

Bonjour à tous,
Pour faire simple, j'ai créé un petit fichier résumant mon fichier source.
Je souhaite faire la somme sur la feuille Liste de mes différents ingrédients présents dans mes feuilles Menu.
Je serai amené rapidement à avoir de plus en plus d'ingrédients et de plus en plus de menus. Je ne peux donc pas simplement ajouter les feuilles à la suite de ma formule recherchev (comme réalisé dans mon exemple)...
Merci à tous pour votre aide !!

 

[Lone-wolf]

Bonjour Matthieu, le Forum

En A3 de la feuille Liste =SOMME('Menu 0:Menu 2'!C3), et tu tire la formule vers le bas. Tu remplacera Menu 2 par la dernière feuille que tu créera.

 

[Matthieu14]

Bonjour et merci à toi Lone-wolf
Mon problème est que selon les menus les ingrédients ne seront pas toujours les mêmes... J’ai donc besoin d’une formule qui fait le «tri». C’est pour cela que j’avait pensé à recherchev mais sans succès...

 

[job75]

Bonjour Matthieu14, Lone-wolf,

Il s'agit d'un problème classique de consolidation, très souvent traité sur ce forum.

Voyez le fichier joint et cette macro dans le code de la feuille "Liste" :

Private Sub Worksheet_Activate()
Dim d As Object, w As Worksheet, c As Range, a, b, t(), i&
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
On Error Resume Next 'si aucune SpecialCell
For Each w In Worksheets
    If w.Name <> Me.Name Then
        For Each c In w.Columns(2).SpecialCells(xlCellTypeConstants)
            If c <> "Ingrédients" And c <> "Total" And IsNumeric(c(1, 2)) Then d(c.Value) = d(c.Value) + CDbl(c(1, 2))
        Next c
    End If
Next w
'---transposition---
a = d.keys: b = d.items
ReDim t(UBound(a), 1) 'base 0
For i = 0 To UBound(a)
    t(i, 0) = a(i): t(i, 1) = b(i)
Next i
'---restitution---
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
Range("B3:C" & Rows.Count).Delete xlUp 'RAZ
[B3].Resize(i, 2) = t
[B3].Resize(i, 2).Sort [B3], xlAscending, Header:=xlNo 'tri alphabétique sur la colonne B
[B3:C3].Offset(i) = Array("Total", "=SUM(" & [C3].Resize(i).Address(0, 0) & ")")
[B3:C3].Offset(i).Font.Bold = True 'gras
[B3:C3].Offset(i).Font.Color = vbRed 'rouge
[B3].Resize(i + 1, 2).Borders.Weight = xlThin 'bordures
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

A+

 

[Lone-wolf]

Bonjour job75

 

[BOISGONTIER]

Bonjour,

Avec une simple formule:

cas1:Si les codes sont comme dans le fichier:

=SOMME('Menu 0:Menu 2'!C3) (cas1)

cas2:Si les codes sont dispersés dans la colonne B:

=SOMMEPROD(SOMME.SI(INDIRECT("'"&nf&"'!b3:b13");B3;INDIRECT("'"&nf&"'!c3:c13"))) (cas 2)

ou si noms des feuilles génériques (cas 3)

=SOMMEPROD(SOMME.SI(INDIRECT("'menu "&LIGNE($1:$3)-1&"'!b3:b13");B3;INDIRECT("'menu "&LIGNE($1:$3)-1&"'!c3:c13")))

Le nom des feuilles peut être défini par (cas 4)

=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

http://boisgontierjacques.free.fr/pages_site/sommeprod3D.htm#Somme3DChamp

jb

 

[Matthieu14]

Bonjour à tous et merci pour vos réponses !
Mon but est effectivement de trouver d'avantage une formule 3D comme propose Jacques (merci à toi )
La formule fonctionne très bien dans mon exemple simpliste mais je n'arrive pas à la refaire dans mon fichier d'origine...

J'ai des noms de feuille générique et je me suis servi de la formule du cas 3.

Mon problème est que je n'arrive pas à identifier - à cause de mon exemple trop simpliste - à quoi font référence les lignes et les cellules dans ("'menu "&LIGNE($1:$3)-1&"'!b3:b13");B3;INDIRECT("'menu "&LIGNE($1:$3)-1&"'!c3:c13"))).
S'agit il des lignes et cellules de la feuille récapitulative ou de celles des feuilles Menu ?
J'ai bien vu que la B3 toute seule est celle de référence mais pour le reste ...

 

[BOISGONTIER]

Bonjour,

Dans la formule

=SOMMEPROD(SOMME.SI(INDIRECT("'menu "&LIGNE($1:$3)-1&"'!b3:b13");B3;INDIRECT("'menu "&LIGNE($1:$3)-1&"'!c3:c13")))

-b3:b13 représente les codes
-c3:c13 représente les nombres à sommer
-"'menu "&LIGNE($1:$3)-1 génère les noms des feuilles Menu 0, Menu 1, Menu 2

JB

 

[Matthieu14]

Désolé je patauge...

Si joint une partie de mon fichier

 

[BOISGONTIER]

Cf PJ

jb

 

[Matthieu14]

J'ai compris mon problème. L'erreur vient du fait que mes menus ont des noms et ne s'appellent pas 1 2 ou 3... Le boulet...

J'ai donc regardé le cas n°4 et ton site et grâce à toi, tout fonctionne nickel (à priori ) !!! MERCI

Petite question subsidiaire, j'ai essayé de réaliser la même formule mais en indiquant le nombre de fois ou l'ingrédient revient dans les menus en remplaçant somme par NB mais...

 

[job75]

Bonjour Matthieu14, Lone-wolf, JB,

En effet Matthieu ce n'est pas une consolidation que vous désirez.

Alors peut-être que cette fonction VBA vous conviendra, elle est facile à comprendre :

Option Explicit
Option Compare Text 'la casse est ignorée

Function SommeSiFeuilles(nom As String, adresse As String) As Variant
Application.Volatile
If nom <> "" Then
    Dim ncol As Integer, w As Worksheet, t, i As Long
    ncol = Evaluate(adresse).Columns.Count
    For Each w In Worksheets
        If w.Name Like "Menu*" Then
            t = w.Range(adresse) 'matrice, plus rapide
            For i = 1 To UBound(t)
                If t(i, 1) = nom Then _
                    If IsNumeric(t(i, ncol)) Then SommeSiFeuilles = SommeSiFeuilles + CDbl(t(i, ncol))
            Next
        End If
    Next
End If
If SommeSiFeuilles = 0 Then SommeSiFeuilles = "" 'masque les valeurs zéro
End Function

On peut ajouter des feuilles "Menu", il n'y a rien à modifier.

Fichier joint.

A+

 

[job75]

Re,

Petite question subsidiaire, j'ai essayé de réaliser la même formule mais en indiquant le nombre de fois ou l'ingrédient revient dans les menus en remplaçant somme par NB mais...

Pas de problème mais pour gagner du temps de calcul il faut que la fonction renvoie une matrice (vecteur ligne) :

Option Explicit
Option Compare Text 'la casse est ignorée

Function SommeSiFeuilles(nom As String, adresse As String)
Application.Volatile
ReDim a(1 To 2)
If nom <> "" Then
    Dim ncol As Integer, w As Worksheet, t, i As Long
    ncol = Evaluate(adresse).Columns.Count
    For Each w In Worksheets
        If w.Name Like "Menu*" Then
            t = w.Range(adresse) 'matrice, plus rapide
            For i = 1 To UBound(t)
                If t(i, 1) = nom Then
                    If IsNumeric(t(i, ncol)) Then a(1) = a(1) + CDbl(t(i, ncol))
                    a(2) = a(2) + 1
                End If
            Next i
        End If
    Next w
End If
If a(1) = 0 Then a(1) = "" 'masque les valeurs zéro
If a(2) = 0 Then a(2) = "" 'masque les valeurs zéro
SommeSiFeuilles = a 'vecteur ligne
End Function

Fichier (2).

A+

 

[BOISGONTIER]

>Petite question subsidiaire, j'ai essayé de réaliser la même formule mais en indiquant le nombre de fois ou l'ingrédient revient

Pour compter le nb de fois (cas4). La formule devient plus simple.
-on peut ajouter des feuilles -

=SOMMEPROD(NB.SI(INDIRECT("'"&nf&"'!b3:b13");C3))

http://boisgontierjacques.free.fr/pages_site/sommeprod3D.htm#NBsiX

jb

 

[job75]

Re,

Concernant les durées d'exécution des 2 méthodes voyez les 2 fichiers joints.

Chez moi sur Win 10 - Excel 2013 le résultat est pratiquement le même (un peu plus de 2 ms).

Edit : mais si l'on supprime la plage "inutile" I7:J13 la méthode de JB est plus rapide.

A+