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

 

[Matthieu14]

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]

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]

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)

 

[job75]

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 :

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

 

[Frensoa]

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.

 

[Frensoa]

l'approche est très intéressante et a l'air terriblement efficace, mais je manque de base en VBA pour comprendre comment on construit ce code et comment il fonctionne exactement (j'arrive à peu près à cerner la démarche mais pas mal d'éléments m'échappent)

 

[job75]

si il y a des propositions en passant par des formules Excel je suis preneur.

Alors voyez le fichier .xlsx joint.

Formule plus simple en L25 à tirer à droite et vers le bas :

=SIERREUR(RECHERCHEV($G25;INDIRECT("'"&L$23&"'!A:K");11;0);"")

Formule matricielle en J25 à valider par Ctrl+Maj+Entrée et tirer vers le bas :

=SOMME(SIERREUR(SOMME.SI(INDIRECT("'"&L$23:BM$23&"'!A:A");G25;INDIRECT("'"&L$23:BM$23&"'!N:N"));0))

 

[Frensoa]

Merci beaucoup ça marche nickel!
donc si je comprend bien:
Le "somme.si" va faire chacune des recherches dans chaque onglet. On rajoute ensuite le "sierreur" pour effacer les résultats faux ou #na et ensuite le "somme" placé au début va additionner chacun des résultats du somme.si en validant le calcul matriciel (je ne suis pas aguerri sur les formules matricielles)
ce qui explique qu'il indique la mention #valeur! si je mets un simple "somme.si" sans validation matricielle
=SOMME.SI(INDIRECT("'"&L$23:BM$23&"'!A:A");G25;INDIRECT(("'"&L$23:BM$23)&"'!N:N"))
parce que plusieurs résultats arrivent en réponse du "somme.si" qui n'en attend qu'un seul.

Et lorsque je valide en fonction matricielle avec juste le "somme.si"
{=SOMME.SI(INDIRECT("'"&L$23:BM$23&"'!A:A");G25;INDIRECT(("'"&L$23:BM$23)&"'!N:N"))}
il va s'arrèter au résultat du premier onglet , d'où le "somme" en début de formule qui permet à la fonction somme.si de réaliser une occurrence à chaque onglet et d'en faire la somme à la fin.

... si j'ai bien compris.

et merci beaucoup pour la simplification de ma formule en L25.

Formule finale de @job75 en J25:
{=SOMME(SIERREUR(SOMME.SI(INDIRECT("'"&L$23:BM$23&"'!A:A");G25;INDIRECT(("'"&L$23:BM$23)&"'!N:N"));0))}
avec sur mon dashboard en G25 mon code projet et L23:BM23 ma liste d'onglet
et sur chacun des onglets en A:A ma liste de projet et en N:N la valeur recherchée

François

 

[job75]

ensuite le "somme" placé au début va additionner chacun des résultats du somme.si en validant le calcul matricie

Oui il faut la fonction SOMME et valider la formule matriciellement, SOMMEPROD sans validation matricielle ne va pas ici.

 

[joon49]

Re,


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+

bonjour, j'ai utiliser votre code pour un fichier excel perso(merci pour le code) seulement j'ai une erreur automation au démarrage du fichier.
savez vous pourquoi merci par avance?

 

[job75]

Bonjour joon49,

seulement j'ai une erreur automation au démarrage du fichier.
savez vous pourquoi merci par avance?

Tout ce que je peux dire c'est qu'il n'y a pas cette erreur avec le fichier du post #13.

A+

 

[joon49]

Moi non plus je ne vois pas pourquoi
Mais ce problème arrive sur Excel 2016 ou 2019 mais pas sur Excel 2021