Somme recherchev sur plusieurs feuilles

Matthieu14

XLDnaute Nouveau
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 !!
 

Pièces jointes

  • Aide somme recherchev.xlsm
    24.1 KB · Affichages: 107

Matthieu14

XLDnaute Nouveau
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

XLDnaute Barbatruc
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" :
Code:
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+
 

Pièces jointes

  • Aide somme recherchev(1).xlsm
    35.8 KB · Affichages: 65

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
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
 

Pièces jointes

  • Cas1.xlsm
    23.5 KB · Affichages: 61
  • Cas2.xlsm
    23.7 KB · Affichages: 70
  • Cas3.xlsm
    23.6 KB · Affichages: 61
  • Cas4.xlsm
    24.6 KB · Affichages: 81
Dernière édition:

Matthieu14

XLDnaute Nouveau
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

XLDnaute Barbatruc
Repose en paix
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

XLDnaute Nouveau
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

XLDnaute Barbatruc
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 :
Code:
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+
 

Pièces jointes

  • SommeSiFeuilles(1).xlsm
    40.1 KB · Affichages: 42

job75

XLDnaute Barbatruc
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) :
Code:
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+
 

Pièces jointes

  • SommeSiFeuilles(2).xlsm
    41.1 KB · Affichages: 32

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
>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
 

Pièces jointes

  • Copie de Cas4.xlsm
    27.6 KB · Affichages: 43
  • Copie de Copie de Somme si-2.xlsx
    26.6 KB · Affichages: 42
Dernière édition:

job75

XLDnaute Barbatruc
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+
 

Pièces jointes

  • Test job75.xlsm
    44.1 KB · Affichages: 40
  • Test JB.xlsm
    44.8 KB · Affichages: 32
Dernière édition:

Discussions similaires

Réponses
5
Affichages
98
Réponses
3
Affichages
149

Statistiques des forums

Discussions
311 720
Messages
2 081 897
Membres
101 833
dernier inscrit
sandra25