Microsoft 365 Valeur dans formule selon critères donnés

[Grizz23]

Bonjour à tous,

Je suis en train de créer un tableau permettant la projection de ventes d’un stock donné et je bloque à la toute dernière étape.

Ces ventes se feront selon un % annuel prédéfini, suivant 3 critères :

• Type -> détermine le type de produit et donc le pattern de % de ventes au fil des années,
• Timing -> peut être normal ou accéléré de deux ans,
• Durée -> durée sur laquelle le stock est écoulé.

(A noter que la valeur de ce stock s’apprécie selon un multiple prédéfini et que le décompte des années commence à partir de la validation du budget.)

J’ai trouvé la formule permettant de simuler « à la main » les ventes à venir en fonction du type/timing/durée.

Je souhaiterais cependant automatiser ce process, afin que, pour chaque année de vente, la cellule aille chercher le % correct selon les caractéristiques de la ligne.

J’ai commencé à formuler un SI(CONDITIONS) mais le nombre de facteurs étant particulièrement important, la formule prend rapidement des proportions gigantesques…

Pourriez-vous, svp m’aider ? (si possible sans VBA)

Un Twist sur la fonction « FILTRER » ou un RechercheX pourrait-il être utilisée ici ?

En vous remerciant par avance pour votre aide !

Excellente journée

 

[Lolote83]

Bonjour @Grizz23,
Voici ton fichier en retour.
Les formules en verts (Colonnes A à Y) seulement ont été changées.
Une colonne AL a été rajoutée (concaténation de Type + Durée + Timing)
En AH3 = Concaténation des données E,F et H
En AH4 = Ligne correspondante à la concaténation + MFC pour repérage

Voili voilà
@+ Lolote83

 

[Grizz23]

Bonjour @Lolote83

Merci pour votre retour !
En revanche, il semblerait qu'il y ait un problème dans la formule, en effet, les résultats obtenus ne coïncident pas avec les chiffres visés entrés en exemple dans le document.

Peut-être est-ce dû à un décalage d'année étant donné que l'année "1" du tableau jaune correspond à l'année de définition du budget (ici 2021) ?

Votre méthode peut-elle évoluer pour aboutir sur les chiffres viés initialement ?
Sinon, je pense que "RECHERCHEX" vertical ET horizontal pourrait potentiellement fonctionner mais je n'arrive malheureusement pas à trouver la bonne configuration...

Merci encore par avance !

 

[Lolote83]

Re bonjour,
C'est normal car dans le fichier joint au post#2, j'avais changé la valeur de la cellule E3 en Type2.
Si on met comme dans ton exemple Type1, j'ai les mêmes valeurs que toi.
Voili voilà
@+ Lolote83

 

[JHA]

Bonjour à tous,
Bonjour @Lolote83

Désolé, je n'ai pas la version 365, avec une formule matricielle et une ligne supplémentaire.
En "L5"

=SI(L$9=1;(DECALER($K$3;;EQUIV($I$9;ANNEE($L$2:$AE$2);0))*$I$3)*PETITE.VALEUR(DECALER($AL$2;EQUIV($E$3&$F$3&$H$3;$AI$3:$AI$72&$AK$3:$AK$72&$AJ$3:$AJ$72;0);1;;$H$3);SOMME($L$9:L$9));0)

copier vers la droite

JHA

 

[Lolote83]

Re bonjour,
Salut @JHA .
J'avoue ne pas être allé aussi loin que toi dans la réflexion.
J'ai juste repris ce qui avait été donné au post#1.
Par contre, est-ce normal de trouver #N/A dans tes colonnes de calculs si on choisi par exemple Type 3 ou Type 4 (Cellule E3) ?
@+ Lolote83

 

[JHA]

Bonjour à tous,

Il y a des espaces dans le tableau de droite après "Type 3"

JHA

 

[JHA]

Bonjour à tous,

Prise en compte du "Timing" rapide ou normal.

JHA

 

[Grizz23]

Bonjour à tous,

Merci infiniment pour vos retours,

En étudiant vos propositions, je me suis rendu compte que mon fichier nécessitait une transformation de la matrice pour atteindre l’objectif que je m’étais fixé. (mes excuses pour ne pas m'en être aperçu avant).

En effet, en année N+1 (donc 2024 actuellement), le pourcentage de vente du stock pour un produit budgeté en 2019 par exemple, ne sera pas celui indiqué en année 6 dans le précédent tableau car ce serait omettre une partie des ventes, or, 100% du stock doit être vendu en dernière année.

J’ai donc créé une nouvelle (et dernière) matrice affichant le % de stock vendu par type, par durée et par durée restante.

Il me faut désormais :

• Calculer automatiquement la durée restante en années (durée de vie – année actuelle + année de prise du budget) dans la cellule I3
• Calculer les ventes (je suis désolé mais je ne parviens pas à adapter vos solutions à la nouvelle matrice). J’ai une nouvelle fois tenté une RechercheX combinée mais la formule me renvoie une erreur…
• Prendre en compte que si timing = accéléré, décaler tous les retours de deux ans plus tôt sur la gauche.

Mille mercis par avance pour votre aide,

Excellente soirée !

 

[Lolote83]

Bonjour @Grizz23 ,
Quelques questions avant de me plonger dans un truc ou je risque de faire faux.
Dans l'exemple donné : Type (Type 1), Timing (Normal), Durée (11 ans), Durée rest (6 ans), tu vas chercher les données dans ta matrice en ligne 50. OK
Si on choisi Timing (Rapide), faut il aller chercher les données sur une autre ligne ou c'est dans le tableau (colonne M - AF) que cela se passe ?
Si on choisi Durée restante = 10, cela veut dire que tu auras 10 colonnes de formules (Donc Colonne S à AB) ?
Peux tu donner un exemple plus précis quant au Timing (Normal VS Rapide)
Merci de ton retour
@+ Lolote83

 

[JHA]

Bonjour à tous,

A essayer

JHA

 

[Grizz23]

Bonsoir à tous,

Merci pour vos réponses, effectivement, je comprends que le paramètre "rapide" complique le process et impose des étapes complémentaires.

Dans un soucis de simplification, sortons ce paramètre de l'équation et visons "juste" à obtenir la data correspondant à type / durée / durée restante.
(Effectivement, si on choisi Durée restante = 10, cela veut dire que tu auras 10 colonnes de formules (Donc Colonne S à AB))

Un grand merci par avance pour votre précieuse aide,
Bonne soirée !

 

[Lolote83]

Bonjour à tous,
N'ayant pas vraiment compris le "phénomène RAPIDE", voici ma contribution
Je comptabilise donc à partir de la colonne S (puisque noté PROJECTION)

Je suis en train de créer un tableau permettant la projection de ventes d’un stock donné et je bloque à la toute dernière étape.

@+ Lolote83