Faire un SOMME.SI.ENS sur un enssemble de feuilles (3D)
Bonjour,
Après avoir essayé de nombreuses formules et écumé un grand nombre de post et de forum je me suis finalement décidé a poser ma question directement puisque je ne trouve pas la solution a mon problème.
Je vais essayer d’être le plus clair possible ...
Je suis dans la vente, mon fichier comporte une feuille avec mes produits (ref/nom/prix) et quantité vendue
puis suivent toutes les feuilles de mes bons de commandes (BDC) par clients.
Feuille 1 : Tableau récapitulatif
Feuille 10 : BDC_FIN
Je souhaite que lorsque je rentre une référence dans un bon de commande (avec la quantité commandée) mes quantité de cette référence vendue se mettent a jour dans mon tableau récapitulatif
Je sais faire la somme d'une cellule fixe (par exemple A21) sur l’ensemble de mes bons de commande en utilisant les feuilles 2 et 10 (en gras) comme des balises en faisant : =SOMME(BDC_Type:BDC_FIN!A21)
ainsi même si je rajoute une nouvelle feuille bon de commande entre ces deux balises elle sera automatiquement prise en compte et je sais appliquer ma formule SOMME.SI.ENS a une seule feuille mais impossible de compiler les deux fonctions et de "balayer" l'ensemble de mes bons de commandes avec ma fonction SOMME.SI.ENS.
Donc ça donnerais un truc comme ça : =SOMME.SI.ENS(BDC_Type!D21:D36;BDC_Type!A21:A36;"=22")
Mais sur la plage de feuilles BDC_Type:BDC_FIN!
Pouvez vous m'aider svp?
Je vous joint un fichier pour l'exemple
Merci d'avance pour vos réponses !
Bonjour,
Je ne pense pas que ce soit possible ! Faire un calcul 3D sur une zone déterminée, ok, mais en y appliquant aussi une condition, même en matriciel, je ne vois pas !
Par contre, si j'interviens ce n'est pas pour dire que ce n'est pas possible, tu peux compiler tous les onglets dans un seul par VBA et appliquer la formule sur la compilation.
Merci pour ta réponse
Je vais chercher ce qu'est un VBA et voir si j'arrive a l'appliquer
Bonjour
Ou compiler avec PowerQuery sans VBA puisque tu as 2016...
bonjour
je te propose de passer par
SOMMEPROD(SOMME.SI(indirect(.....
http://boisgontierjacques.free.fr/
cordialement
Merci pour ta réponse 78Chris et Merci a toi aussi tulipe_4
Je connais pas PowerQuery, mais je vais me renseigner.
Pour ta proposition tulipe_4 j'ai déja vu ce genre de formule mais j'ai beau essayer je n'arrive pas a trouver comment faire avec la fonction INDIRECT
re
dans 8 cellules tu saisi les coordonneés de chaque plage
astuce : a la première cel tu saisi = et tu vas à la plage de la 1ere feuille ensuite tu effaces le =
tu as ainsi facilement la description (feuille ..! a1:a200)
memechose pour chaque feuille
tu recommences pour les colonnes des resultats à sommer sur chaque feuille
tu va avoir ainsi 2 colonnes d'adresses (1pourle critère et une pour les valeurs )
tu nomme chaque colonne ,exemple Adresse et l'autre Valeur
la formule 3d sera
=sommeprod(somme.si(indirect(Adresse);toncritère;indirect(Valeur)))
et tu valides en matriciel (CTRL Maj Entrée)
attention le nb de ligne de chaque plage doit etre le meme (rectifie au besoin )
cordialement
Ok Tulipe_4
Par contre a chaque ajout de feuille (commande) je devrais refaire mon tableau non?
Et j'aimerais eviter si possible
Oui c'est vrai, il faudra ajouter au tableau le nouvel onglet.
Si je rebondis sur la proposition de Tulipe, et si justement tu as pris la peine de mettre la synthèse en format tableau d'excel, alors les formules des nouvelles lignes seront recopiées automatiquement.
Un exemple ici même si ce n'est pas exactement ton cas !
Bon, j'ai adapté mon bidule à ton cas ...
Quand tu ajoutes une feuille, mets la formule magique en A1 de la feuille, et mets au bas du tableau de l'onglet SOMMEPROD un lien vers la cellule A1 de la nouvelle feuille
Après, tu n'as plus qu'à sommer ...
merci à Tulipe de m'avoir fait penser à cela ... en plus je l'avais déjà fait pour d'autres !!
re a vous 2
il n'est pas interdit de nommer les 2 petits tablo en dynamique ou de les mettre sous forme de tablo automatique (jamais essayé; je n'aime pas )
ps steelson : je n'ai pas ouvert ta PJ
cordialement
@Tulipe, je l'ai adaptée au cas de Wadjo ... cela reprend du reste sous une forme légèrement différente ce que tu proposais.
Bonjour,
Sinon, une simple fonction perso avec les feuilles à éviter dans le Select Case :
Function SOMMEPRODUITS(Produit As String) As Long
Dim FE As Worksheet
Dim Plage As Range
Dim Cel As Range
Dim Total As Long
Application.Volatile
For Each FE In Worksheets
Select Case FE.Name
Case "Tableau recapitulatif", "BDC_Type"
Case Else
With FE: Set Plage = .Range(.Cells(21, 2), .Cells(.Rows.Count, 2).End(xlUp)): End With
For Each Cel In Plage
If Cel.Value = Produit Then Total = Total + Cel.Offset(, 2).Value
Next Cel
End Select
Next FE
SOMMEPRODUITS = Total
End Functionqui est appelée en colonne C à partir de C11 de la feuille "Tableau recapitulatif" et à tirer vers le bas :
=SOMMEPRODUITS(__xlnm._FilterDatabase[[#Cette ligne];[Nom]])
Super ! avec ça ça peux le faire
Il me reste juste a bidouiller un peu pour que ça colle parfaitement mais j'ai tout ce qu'il faut pour y arriver maintenant!
Merci a vous pour votre aide !!!
Je pense pouvoir dire que mon probleme est résolu
Vous etes TOP !!
Encore un grand merci !
waw Theze la je suis pommé
Je sais pas du tout comment utiliser ces lignes de code
J'ai appliqué la solution de tulipe_4 et Steelson.
C'est dommage que ça ne puisse pas etre plus automatique mais bon c'est deja super.
Apres si une autre solution peut inclure les nouvelles feuilles sans plus de manip je suis preuneur
Ta solution fonctionnerais Theze si j'inclu de nouvelles feuilles ?
Ta solution fonctionnerais Theze si j'inclu de nouvelles feuilles ?
Bien sûr car seules les feuilles indiquées dans le Select Case sont évitées, toutes les autres sont prises en compte !
Je sais pas du tout comment utiliser ces lignes de code
Depuis Excel, tu appuis sur la touche Alt que tu maintiens appuyée et tu fais F11 puis I puis M et dans la zone de texte qui vient de s'afficher, tu colles le code ensuite, tu enregistres au format .xlsm (quand tu fais Ctrl+S, un message s'affiche, tu cliques sur le bouton "Non", une fenêtre s'affiche te demandant où tu veux enregistrer la copie du classeur (normal puisqu'il va avoir une autre extension, c'est donc une copie) dans la zone Type (sous "Nom de fichier") ,tu déroules la liste et tu choisis "Classeur Excel (prenant en charge les macros) (*.xlsm)" puis bouton "Enregistrer"
Une fois ceci fait, dans la cellule C11 tu colles la formule :
=SOMMEPRODUITS(__xlnm._FilterDatabase[[#Cette ligne];[Nom]])
que tu tires vers le bas jusqu'au dernier produit
Waw, merci a tous, ça fait plaisir de voir autant de monde pret a aider
Je appliquer la methode de Theze pour voir
je vous dit quoi dans la journée