XL 2013 index pour report de données via liste déroulante

[Lo33]

Bonjour,
Je cherche à créer un numéro d'index via NB.SI ou NB.SI.ENS pour identifier un produit répondant à une plusieurs critères, mais rien ne marche...
Je m'explique : j'ai besoin de faire apparaître la liste de produit en fonction de 2 critères différents sélectionnés via 2 listes déroulantes.
LD1 => Année de fabrication du produit (donnée qui se trouve dans la feuille VR)
LD2 => Commercial (donnée qui se trouve dans la feuille Ventes)

Dans le fichier Test joint, en Feuil2, j'ai besoin de faire apparaître la liste des produits en colonne B, qui répondent au critère de la LD1 et de la LD2, soit la liste des produits fabriqués par exemple en 2019 (Feuille VR) et pour lequel le commercial sélectionné en LD2 a eu des ventes (feuille Ventes). Le but étant ensuite de faire apparaître cette liste classée par ordre croissant des Qté Tonnes Libres (stock existant libre), donnée située en feuille VR.
Je pensais créer d'abord un index pour identifier les produits correspondant : par exemple, avec la formule =SI(SOMMEPROD(VR!$B2:C$400=Feuil2!$B$2)*(Ventes!$C$2:$C$300=Feuil2!$D$2);SI(NB.SI(Ventes!$B$1:B1;B1)>0;"";MAX($G$1:G1)+1);"") indiquée en feuille Ventes colonne G. Pour ensuite utiliser cet index pour récupérer les données de stock en feuille VR, les classer via une formule RANG, puis les reporter classés dans la colonne B de la Feuil2...
Mais je n'arrive même pas à passer l'étape de l'index.... Peut être que la combinaison Sommeprod + NBSI n'est pas la bonne? La syntaxe ? Ou alors j'oublie quelque chose ? Bref suis perdue.... ! Pouvez-vous m'aider ?

Merci et bonne soirée à tous

 

[Lo33]

Hoooo vous êtes pas croyable !! J’avais essayé les TCD mais sans succès parce que je n’arrivais pas à mettre en relation les tables... mais je ne savais pas que je pouvais utiliser des formules avec des TCD... je regarde ça demain matin. Sauf si je craque et que je me lève cette nuit!!
Quoi qu’il en soit je vous fais un feed-back demain !!!

 

[Dugenou]

V5 avec une la colonne S qui se construit à partir d'un TCD, du coup la colonne A peu élégante de la feuille vente disparaît
Cordialement

 

[Lo33]

Bonjour, j'ai regardé vos 2 derniers fichiers, et c'est assez bluffant. Faut que je me penche sur les TCD parce qu'effectivement ça allège grandement les formules, semble-t-il.
Mais je vais d'abord essayer de terminer, sinon je vais me perdre. J'ai peur de tout mélanger.
Le fichier V4 est super intéressant. Surtout la partie Client. C'est l'étape suivante pour moi. Mais il faut qu'apparaissent uniquement les clients du Commercial sélectionné en C4 (detalle). Je pensais tenter de créer un autre "index" en feuille Ventes. Qu'en pensez-vous?

 

[Dugenou]

Bonjour,
Non il n'est pas nécessaire créer un nouvel index, il suffit de rajouter le commercial dans le TCD et une condition dans la formule en I19 :
=SIERREUR(INDEX(Ventes!$N$1:$N$2000;PETITE.VALEUR(SI(Ventes!$O$3:$O$2000=Detalle!$I$2;LIGNE(Ventes!$O$3:$O$2000));LIGNE($A1)));"-")
Cette formule (matricielle) renvoie les N° material si la colonne O contient le produit.
on va ajouter la condition sur le commercial et modifier les colonnes suite à l'ajout de la donnée indiquant le commercial
=SIERREUR(INDEX(Ventes!$N$1:$N$2000;PETITE.VALEUR(SI((Ventes!$P$3:$P$2000=Detalle!$I$2)*(Ventes!$O$3:$O$2000=Detalle!$C$4);LIGNE(Ventes!$O$3:$O$2000));LIGNE($A1)));"-")
dans une formule matricielle les conditions s'ajoutent en les multipliant.
Ensuite on ajoute aussi la condition sur le commercial dans le calcul des quantités colonnes M et N
=SOMME.SI.ENS(Ventes!$I$2:$I$2000;Ventes!$F$2:$F$2000;$I19;Ventes!$B$2:$B$2000;$I$2) devient
=SOMME.SI.ENS(Ventes!$I$2:$I$2000;Ventes!$F$2:$F$2000;$I19;Ventes!$B$2:$B$2000;$I$2;Ventes!$H$2:$H$2000;Detalle!$C$4)

Dans la zone I8 - L14 vous voulez quoi précisément ?

Voir pj

 

[Lo33]

Suis afk, et je réponds depuis mon tel. Je regarde ça ce soir. Vous êtes impressionnant ^^ je crois bien que j’aurai appris ce week-end plus de choses que ces 2 derniers mois !! Je ne savais pas non plus que pour les formules matricielles les conditions s’ajoutaient en les multipliant....
Merci encore !!!

 

[Lo33]

Re,
Bon j'ai tout regardé, et j'ai du boulot !! Je vais suivre votre proposition de travailler avec les TCD.
Je vais donc tout refaire, en suivant mais en comprenant vos formules.
J'ai regardé un peu la formule nbcar indiquée en colonne R, pour le calcul du rang, mais je ne la comprends pas. Pourquoi utiliser nbcar ?

Pour les zone I8-L14, il s'agit de faire remonter les emplacements (centro + almacen) du produit sélectionné en I2 indiquant pour chacun les tonnes libres mais aussi bloquées. Toutes ces infos sont dans l'onglet VR
Bon ! je me mets au boulot !!
Encore milles fois mercis !!

 

[Dugenou]

nbcar (indique le nombre de caractères) : permet de tester si une cellule contient un code : le test estvide ou =0 ne fonctionne pas dans notre cas puisque on a une formule qui peut renvoyer "".
J'ai pensé plus pratique de vérifier qu'on avait plus de 2 caractères dans la cellule. En fait c'est pas très propre, mais ça marche.
Ok la zone I8-L14 n'est pas liée au commercial, donc ce que j'y ai fait est correct.

Au plaisir