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

Fichiers joints

Lone-wolf

XLDnaute Barbatruc
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.
 
Dernière édition:

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+
 

Fichiers joints

BOISGONTIER

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

Fichiers joints

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

Fichiers joints

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+
 

Fichiers joints

BOISGONTIER

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

Fichiers joints

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+
 

Fichiers joints

Dernière édition:

Matthieu14

XLDnaute Nouveau
Au top Jacques !!! C'est exactement ce que je cherchais à faire !!!

Merci aussi à vous Job75 et Lone-wolf

Et à bientôt pour de nouvelles aventures... ;)
 

Matthieu14

XLDnaute Nouveau
Bonjour à tous,
Eh oui me revoilà déjà...
J'ai créé une feuille "menu" vierge qui me sert de base pour les réaliser tous sur le même modèle.
J'ai ensuite créé une macro pour copier cette feuille une fois complétée et la renommer par rapport à la cellule J6 (qui est le nom de mon menu).
Mais afin que la formule de Jacques fonctionne, il faut que la feuille créée s'appelle Menu "J6" et non seulement "J6"

Voici ce que j'ai écrit

ActiveSheet.Name = Range("J6")

Merci une nouvelle fois pour votre aide.

Matthieu
 

Frensoa

XLDnaute Nouveau
Bonjour,
je bute sur le sujet évoqué dans ce fil.
j'ai un onglet tableau de bord (dashboard) qui récapitule par numéro de projet les données saisies dans une liste d'onglet qui correspondent à une saisie par semaine. 1 onglet = 1 semaine toujours suivant le même formalisme (01/02/03/04/05 etc..) dans mon détail par semaine j'ai une colonne qui détermine par projet la répartition des jours de déplacement. je cherche à compiler suivant le critère numéro de projet (au format texte) la somme des jours de déplacements. j'ai fait plusieurs essais dont la proposition de @BOISGONTIER mais je n’aboutis qu'à des résultat de type #ref! ou #valeur!

auriez-vous une piste à me proposer?

merci pour vos retours

PS: à noter également que la semaine "00" à l'air de poser problème

François
(ci-joint le fichier de base, j'ai indiqué dans la colonne I le résultat attendu en colonne J)
 

Fichiers joints

job75

XLDnaute Barbatruc
Bonjour Frensoa, bienvenue sur XLD,

Plutôt que squatter ce fil il aurait mieux valu créer votre propre discussion.

Mais bon, voyez le fichier .xlsm joint et ces 2 fonctions VBA, à placer impérativement dans un module standard :
VB:
'---mémorise les constantes---
Const ColAffaire$ = "A"
Const ColJourChantier$ = "N"
Const ColHeure$ = "K"

Function RechJourChantier(Naffaire, Feuilles As Range)
Application.Volatile
Dim w As Worksheet, lig As Variant
For Each w In Worksheets
    If Application.CountIf(Feuilles, w.Name) Then
        lig = Application.Match(Naffaire, w.Columns(ColAffaire), 0)
        If IsNumeric(lig) Then RechJourChantier = RechJourChantier + w.Cells(lig, ColJourChantier)
    End If
Next
End Function

Function RechHeure(Naffaire, Feuille As Range)
Application.Volatile
Dim lig As Variant
On Error Resume Next 'si la feuille n'existe pas
RechHeure = ""
With Worksheets(CStr(Feuille))
    lig = Application.Match(Naffaire, .Columns(ColAffaire), 0)
    If IsNumeric(lig) Then RechHeure = .Cells(lig, ColHeure)
End With
End Function
Elles sont utilisée dans les formules en J25 et L25 à recopier sur les plages concernées.

A+
 

Fichiers joints

Frensoa

XLDnaute Nouveau
Bonjour, merci pour cette réponse, je vais la tester, même si je préfèrerais passer par des formules plutot que le VBA (je ne me suis jamais plongé encore dans le VBA, je suis plus à l'aise avec les formules), mais je vais essayer.
si il y a des propositions en passant par des formules Excel je suis preneur.
pour ce qui est de poser à la suite de ce fil c'était pour éviter d'être redondant avec un sujet similaire dans la mesure où mon problème peut compléter la question originale de @Matthieu14 . mais ce n'est peut être pas l'usage sur ce forum.

merci en tout cas pour cette réponse rapide.
 

Discussions similaires


Haut Bas