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

Grizz23 · 28 Février 2023

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 · 28 Février 2023

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 · 28 Février 2023

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 · 28 Février 2023

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 · 28 Février 2023

Bonjour à tous,
Bonjour @Lolote83

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

VB:

=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 · 28 Février 2023

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 · 28 Février 2023

Bonjour à tous,

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

JHA

JHA · 28 Février 2023

Bonjour à tous,

Prise en compte du "Timing" rapide ou normal.

JHA

Grizz23 · 28 Février 2023

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 · 1 Mars 2023

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 · 1 Mars 2023

Bonjour à tous,

A essayer

JHA

Grizz23 · 1 Mars 2023

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 · 2 Mars 2023

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

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

Grizz23

XLDnaute Nouveau

Pièces jointes

Lolote83

XLDnaute Barbatruc

Pièces jointes

Grizz23

XLDnaute Nouveau

Lolote83

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

Pièces jointes

Lolote83

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

Grizz23

XLDnaute Nouveau

Pièces jointes

Lolote83

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

Pièces jointes

Grizz23

XLDnaute Nouveau

Lolote83

XLDnaute Barbatruc

Pièces jointes

Discussions similaires