Alliance calcul matriciel et fonction DECALER
Bonjour à tous,
Je cherche à comprendre comment faire fonctionner une formule SANS VBA en calcul matriciel du type {=SOMME((C6>=SOMME(C7:DECALER(C7,0,{0\-1\-2})))*1)}
Pour vous donner le contexte de l'utilisation de cette formule :
Je chercher à compter le nombre de mois entier de ventes que j'ai en stock dans mon entreprise en me basant sur les ventes des mois précédents. Ex: si en janvier février j'ai réalisé 20€ et 30€ de ventes et que j'ai 35€ de stock en février, j'ai donc un seul mois entier de ventes en stock.
Cela se fait sans trop de soucis avec la formule suivante
(SOMME((C6>=SOMME(C7:DECALER(C7,0,0)))*1,(C6>=SOMME(C7:DECALER(C7,0,-1)))*1,(C6>=SOMME(C7:DECALER(C7,0,-2)))*1),
la ligne 6 contenant mes stocks et la ligne 7 mes ventes dans l'ordre chronologique. (CF fichier joint) je demande simplement à excel pour un mois donné de tester mon stock vs le cumulé d'1 mois, puis de 2 mois puis de 3 mois de ventes et de me renvoyer le nombre de tests vrais
J'ai mis une version raccourcie en pièce jointe mais dans les faits je teste mes stocks vs une dizaine de mois de ventes. Vous aurez remarqué qu'il n'y a que le chiffre de décalage de colonne qui change dans les 3 parties de ma formule.
J'ai tenté de simplifier tout cela en utilisant une formule matricielle qui me donnerait dans un monde idéal
{=SOMME((C6>=SOMME(C7:DECALER(C7,0,{0\-1\-2})))*1)}
Cependant cela ne fonctionne pas et je m'arrache les cheveux.
Auriez vous une explication / une solution s'il vous plait?
Bonne journée!
Bonjour @ tous,
c'est pas clair
as-tu vérifié bien ta formule classique qui tu dis qu'elle fonctionne bien ?
C6>=SOMME(C7:C7)
C6>=SOMME(B7:C7)
C6>=SOMME(A7:B7)
dans les 3 cas la somme est toujours 9
tu vois ?
pourrais-tu erre clair ?
Bonjour Rachid et merci de t'essayer à l'exercice.
Je t'avoue que c'est ta réponse qui n'est pas claire à mes yeux et je pense que tu n'as pas tout à fait saisi la formule
Si je reprends ta tentative d'écrire un cas particulier de la formule sans fonction décaler (qui je l'admets n'est pas utile en soi mais est bien pratique quand il s'agit de copié coller le tronçon et de ne changer que le chiffre du décalage) cela donne :
Somme((c6<=somme(c7))*1,(c6<=somme(b7:c7))*1,(c6<=somme(a7:c7))*1) ce qui peut être égal uniquement à 0,1,2 ou 3. Les c6<=. Etc sont des testeurs logiques. Cela aurait pu être fait en "si" mais je trouvais cela plus laborieux
Cordialement
Re,
vas sur l'audit de formule et évalue la formule pas à pas.
@ te relire
Rachid,
Je pense que l'on s'éloigne du sujet, le but est de recréer une formule qui fonctionne avec le calcul matriciel comme expliqué dans mon premier message.
Bien sure que dans l'exemple que tu prends les trois sommes à l'intérieur de la formule donnent 9, il n'y a pas de données remplies dans les cases B7 et A7. Du coup C7= somme(A7:C7) = somme(decaler(C7,,-2). C'est un non sujet.
Cordialement
Re,
désolé je passerai le relais à quelqu'un d'autre s'il a compris le sujet.
à titre de rappel : ton sujet a été vu 42 fois.
@ + +
bonsoir
il me semble que tu cherches a verifier le nb de fois que la condition de somme (...) est inferieure à la cel du dessus
on doit pouvoir avec SOMMEPROD . mais .....
1)dans ta pj si on decale de -1 ou -2 pour c7 >>>>on tombe sur du texte
2) question je me demande comment peut marcher
SOMME(C7:DECALER(C7;0;{0.-1.-2}))car( C7 : une val) ?????? jamais vu
un essai quand meme
cordialement
A Rachid : si le sujet était trivial et à la portée de n'importe qui je n'aurais pas besoin d'avoir posté ma question. Je suis moi même assez avancé en excel.
a tulipe :
Bonsoir,
1.Oui sur C7 on tombe sur du texte, mais encore une fois c'est une version simplifiée que j'ai faite, donc qui ne gère pas les cas particuliers. Encore une fois cette partie n'a pas de soucis et fonctionne, elle est présente uniquement dans le but de vous montrer ce que je veux répliquer;
2.C'est tout le sujet de mon post, je n'arrive pas à comprendre pourquoi cela ne me renvoie pas le bon résultat! Quand à la manière de l'écrire, avec les , et les \ cela est lié, j'imagine, au fait que mon excel soit configuré en anglais.
re
je venais d'editer mon post alors regarde des fois que
cordialement
haha j'étais en réédition du mien également, internet m'ayant laché entre deux
La fonction décaler ne te renvoie pas une valeur mais une plage de cellule. Donc quand tu fais C7:decaler(C7,,-2) cela te renvoie la plage A7:C7. Donc si tu fais la somme tu as simplement la somme de A7:C7
J'ai regardé ce que tu m'as proposé, ce n'est pas bête d'avoir tenté de passer par une sommeproduct qui se rapproche des matrices mais ça ne permet pas de traiter le problème de la lourdeur de la formule dès que tu veux le faire sur 10 mois en arrière.
Ce que je souhaiterais savoir est s'il existe un moyen de faire la somme des tests "mes stocks du mois N sont ils supérieurs à la somme des ventes du mois N) + "mes stocks du mois N sont ils supérieurs à la somme combinée des ventes du mois N et du mois N-1) ...
De la même manière que la formule {=SUM((C6<D6:F6)*1)] me teste bien C6 vs D6 puis vs E6 puis vs F6 et me renvoit le nombre de fois que la condition est vraie (cf fichier renvoyé) je voudrais savoir comment faire pour que {SUM((C6>=SUM(C7:OFFSET(C7;0;{0\-1\-2})))*1)} me teste C6 vs C7 puis C6 vs somme(B7:C7) puis C6 vs somme(A7:C7) et me renvoit le nombre de fois où la condition est vraie.
Bonsoir tous,
DECALER ne se matricialise pas, et ne renvoie qu'une valeur.
INDIRECT renvoie bien les matrices mais le calcul ne s'opère pas non plus et aboutit au même résultat.
Une matrice de plages inégales n'a pas l'air d'être bien digérée...
Imagination épuisée pour ce soir
Bonne nuit à tous.
bon alors et celle la
=SOMMEPROD(--(CHOISIR({1.2.3};C7;C7+B7;B7+A7)<C6))tropicalement votre
Bonsoir M Ferrand,
Sauf erreur de ma part il me semble que les DECALER se matricialisent (sauf si l'on ne donne pas le meme sens à cette phrase), comme dans mon fichier joint où j'ai rajouté une ligne où je fais "apparaitre" la matrice qui utilise DECALER sur 3 colonnes.
L'étape qui me manque et qui ne semble pas aboutir c'est la somme de ces "trois colonnes" qui symbolisent ma matrice à l'intérieur d'une même formule.
Il est effectivement grand temps d'aller se coucher, je reviendrai demain en espérant trouver une réponse
tulipe_4 a écrit :bon alors et celle la
=SOMMEPROD(--(CHOISIR({1.2.3};C7;C7+B7;B7+A7)<C6))tropicalement votre
Derniere réponse de la soirée pour ma part : je n'ai aucun doute que la formule fonctionne même sans l'avoir testé, mais cela ne résoud pas le problème d'automatisme si tu veux tester jusqu'à ton mois n-10. Ne serait ce que sur un exemple ou l'on va sur jusqu'au mois à n-5 cela donnerait
SOMMEPROD(--(CHOISIR({1.2.3.4.5};E7;E7+D7;E7+D7+C7;E7+D7+C7+B7;E7+D7+C7+B7+A7)<C6))
(inutile de préciser que plus on veut utiliser la formule sur des mois éloignés et plus elle se doit de commencer à droite dans les colonnes)
Bonne soirée/nuit
Salut Tulipe,
L'insertion de DECALER dans une formule matricielle ne pose pas problème.
Ce que j'ai dit c'est que DECALER ne renvoie pas plusieurs matrices (pas la première fois que je le constate, car j'essaie toujours, et que je dois changer mes batteries), mais une seule, contrairement à INDIRECT qui, au cas particulier, renvoie bien 3 matrices telles que voulues, on le voit parfaitement dans l'évaluateur de formules... Mais ça n'aboutit tout de même pas.
Bonne nuit
re
mais il doit bien y avoir moyen de faire une somme matricielle ;le tout est de differencier les resultats par rapport a C6 par exemple
cordialement
Re,
tout est possible
Le but était une seule formule, certes, mais on peut l'obtenir en 2 temps en faisant les sommes dans une plage :
=SOMME(DECALER(C$7;;1-LIGNE(1:1);;LIGNE(1:1)))Matricielle. A tirer sur 2 autres lignes pour avoir les 3 sommes.
Et là :
=SOMME((C6>=C13:C15)*1)Matricielle. (J'ai fait les sommes en C13:C15)). Plus de problèmes pour obtenir le résultat.
A noter que l'extension du nombre de sommes à comparer allongerait la plage des sommes, mais n'allongerait pas les formules.
Là j'arrête !
Re,
je vais répondre et je souhaite me répondre sur les questions que j'ai mises sur le fichier.
Voir fichier joint
@ + +