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

 

[Dugenou]

Bonjour,
Un essai en pj : les formules des colonnes B D et P sont matricielles (à valider par ctrl+maj+enter)
Cordialement

PS Attention les N° de produit sont parfois un texte et non un nombre d'où des &"" et des *1 dans certaines formules.

 

[Lo33]

Dugenou, merci beaucoup pour cette réponse ! Je vais voir ça dès demain matin !
Juste une question : quand vous indiquez "les N° de produits sont parfois un texte et non un nombre", vous parlez du fichier Test joint, ou bien des cas possibles en général ?

Bonne soirée !

 

[Dugenou]

Bonjour, c'est dans le fichier test mis en pj : dans la feuille VR les codes produits sont du texte et dans la feuille vente ce sont des chiffres. J'ai utilisé les chiffres tels quels pour afficher les ventes.
A noter aussi que j'ai tenté de faire un truc variable pour rechercher les ventes 2019 ou 2020, mais il semble que les codes produits soient différents chaque année.
A votre disposition si besoin d'explications sur les formules employées.
Cordialement

 

[Lo33]

Bonsoir Dugenou, désolée de revenir aussi tardivement, mais problème d'ordi hier, bref...

j'ai testé les formules (colonne B et H), et l'erreur #NOMBRE! ressort systématiquement. Je ne comprends pas, parce que les formules fonctionnent très bien dans le fichier Test mais pas dans le fichier que je travaille.... c'est à devenir dingue, et je ne peux même pas reproduire l'erreur dans le fichier Test puisque les formules fonctionnent dans ce fichier....

Pour la formule colonne H, j'ai tenté de "contourner" en testant d'autres formules (colonne MNO), feuil2, du fichier Test), avec une formule SIERREUR(INDEX(VR!$C$2:$C$357;EQUIV(LIGNE($A1);VR!$A$2:$A$357));"") en colonne N. Ca se passe plutôt bien jusqu'à ce qu'on arrive en ligne 262 où la dernière valeur se répète sans fin...

Pour la formule située en colonne B, feuil2, c'est idem:

=SIERREUR(RECHERCHEV(INDEX($G$6:$G$403;EQUIV(PETITE.VALEUR(SI(Ventes!$C$3:$C$12=Feuil2!$D$2;Ventes!$B$3:$B$12);NBVAL($B$5:B5))&"";$H$6:$H$403;0));$G$6:$H$403;2;FAUX);"")

Lorsque je la reproduis dans mon fichier, l'erreur #nombre! ressort systématiquement.
J'ai vérifié x fois les formules, et tout correct... mais résultat = #NOMBRE!
C'est fou quand même ! Et là par contre, je n'ai pas réussi à trouver d'autres alternatives.

J'ai vraiment besoin de votre aide. Merci encore !

 

[Dugenou]

Bonjour,
Les formules en B6, D6, et H6 doivent être validées "en matriciel" : si c'est bien fait des {} s'affichent avant le = et après la dernière parenthèse. Pour cela il faut cliquer dans la formule (n'importe ou) et presser simultanément les touches ctrl et Maj (provisoire : pas le cadenas, mais la flèche vers le haut) et enter (techniquement vous laissez crtl et maj enfoncées et vous appuyez une fois sur la touche enter.
Si les accolades apparaissent c'est bon, vous n'avez plus qu'à "tirer" vers le bas pour recopier.

Votre formule en colonne N : =SIERREUR(INDEX(VR!$C$2:$C$357;EQUIV(LIGNE($A1);VR!$A$2:$A$357));"") ne tient pas compte du critère sur l'année (B2) et renvoie en fait n'importe quoi puisque vous cherchez le chiffre1 (ligne(a1) dans une liste de textes sans préciser que vous voulez la correspondance exacte (le 3eme argument du equiv manque dans votre formule). désolé si je suis désagréable


Cordialement

 

[Lo33]

Mais vous n'êtes pas désagréable !
Le copier/coller de la formule que j'ai indiqué, j'ai en effet omis de mettre les {} mais ils y sont bien dans mon fichier, et je confirme que le résultat est #nombre! Je ne comprends pas

Concernant la formule que j'ai mis en colonne N, elle me paraît correcte non ? car elle "pointe" sur VR!$a$2:$a$357, et en colonne A feuille VR, il y a la formule =SI($B2=Feuil2!$B$2;SI(NB.SI.ENS($B$1:B1;B2;$C$1:C1;C2)>0;"";MAX($A$1:A1)+1);"") qui permet de créer "un index". Vous me confirmez ? Ou alors je passe à côté de quelque chose ? Par contre, le hic est que ça se passe bien jusqu'à ce qu'on arrive en ligne 262 où la dernière valeur se répète sans fin...

 

[Dugenou]

Bonjour,
Dans le fichier joint au post#5 la feuille VR colonne A contient des données en dur et non une formule. Et la formule de cette colonne N en elle même est inefficace : votre equi renvoie presque n'importe quoi, d'ou la répétition à partir de la ligne 262
Pour cette erreur #nombre : pouvez vous joindre une copie de votre fichier réel ou vous n'auriez laissé que 3-4 lignes ?
Cordialement

 

[Lo33]

Bonjour,
C'est à ne rien y comprendre.... Je n'ai pas modifié le fichier Test V1 depuis le post 5, et la formule en colonne A feuille VR est bien présente pour moi.... Je remets le fichier Test V1 pour voir si cette fois-ci la formule y apparaît bien?
Pour votre autre demande, je joins un fichier Test V2 qui est mon fichier réel "réduit", où apparaît le résultat #nombre en colonne C, onglet Detalle. Je précise juste que j'ai remplacé petite.valeur par grande.valeur car c'est le tri décroissant qui m'intéresse. Les autres formules figurent en colonnes RST

Merci encore pour votre aide

 

[Lo33]

Re-bonjour,
Vous allez me détester... J'ai omis depuis le début d'indiquer que les produits peuvent apparaître plusieurs fois dans l'onglet VR, pour le même année de fabrication (lorsqu'ils sont entreposés dans des magasins différents). Ce que, bien entendu, je n'ai pas retranscrit dans le fichier que j'avais joint.
Le problème vient sans doute de là ? En effet, dans le pré-tri fait en feuil2, colonne GHI, il faut que le produit n'apparaissent qu'une seule fois en colonne H, et que la colonne I indique la somme totale des tonnes.
Peut-être l'avez-vous remarqué dans le fichier "réel" que j'ai joint à mon post précédent.

Je joins maintenant le 1er fichier que vous m'aviez transmis avec votre proposition de formules, mais que j'ai modifié en y ajoutant les produits "en double" en onglet VR.

Vraiment désolée pour tout ça... Petite padawan je suis.... Je vais devoir changer de pseudo, je pense

 

[Dugenou]

Bonjour,
Deux problèmes dans la formule de la colonne C feuille detalle :
1) vous recherchez un N° material dans la colonne des rangs
2) la donnée de vendeur ne se trouve que dans la feuille ventes et pas dans la feuille VR

c'est un peu plus complexe que votre fichier exemple...

- colonne S : votre formule =SIERREUR(INDEX(VR!$C$2:$C$239;EQUIV(LIGNE($A1);VR!$A$2:$A$239));"") présente un défaut : il manque la dernière partie du Equiv pour demander la correspondance exacte : du coup quand le chiffre recherché n'est pas trouvé il renvoie le dernier
il faut écrire : =SIERREUR(INDEX(VR!$C$2:$C$239;EQUIV(LIGNE($A1);VR!$A$2:$A$239);0);"")
Du coup on n'a plus de répétition en fin de tableau

Je travaille depuis un moment sur votre fichier réel et je viens de lire votre nouveau message. Oui j'avais bien vu qu'il y avait plusieurs lignes pour un même produit dans la feuille vente et j'ai repris la formule pour ne numéroter que les ligne du vendeur et une seule fois.

voir PJ avec, j'espère, quelquechose qui correspond à votre attente

en colonne S on va chercher dans la feuille ventes les N° material du vendeur
en colonne T on totalise les tonnes libres de ce produit (qui viennent de la feuille VR)
en colonne U on affiche l'intitulé pour le N° material
en colonne R on calcule un rang sur les tonnes libres
en colonne C on reprend les N° material en ordre décroissant de tonnes libres
en colonne D les données de la colonne U, en colonne E celles de la colonne T
en colonne F les ventes du vendeur pour l'année choisie (feuille ventes)

Il reste à faire une petite correction pour que le N° material n'apparaisse pas si le vendeur n'a pas fait de ventes sur l'année recherchée (exemple lignes 8 10 et 11)

A très bientôt

 

[Dugenou]

Voici V3 avec affichage seulement des material vendu sur l'année par le vendeur

Cordialement

 

[Lo33]

Franchement, vous êtes SUPER ! c'est exactement ça ! Pour tout ! En plus je ne vous ai pas facilité la tâche...
Je ne sais pas trop quoi dire... Enfin si ! Milles mercis !!!!

je vais décortiquer tout ça pour bien comprendre la syntaxe des formules, c'est ce qui me donne le plus de mal... Je ne connaissais pas la formule NBCAR utilisée en colonne R et je vais m'y pencher.
La formule en colonne F (fichier V3) est géniale (merci pour ce bonus!)

Bref, je bosse sur ce fichier depuis plusieurs mois et j'apprends au fur et à mesure, et avec votre aide. Mais là avec ce que vous m'avez envoyé, je crois bien que je vais bientôt en voir le bout. Encore merci !
Bonne soirée

 

[Dugenou]

Content d'avoir bien compris le problème
Attention à vos liaisons avec les autres fichiers : vous utiliser des formules qui ne sont pas nécessaires, faites une liaison directe (ou mieux : un copier coller valeurs que vous ferez à chaque fois que vos données de base changent)
Je pense par ailleurs qu'il y aura un problème quand vous voudrez changer l'année car les ventes des années précédentes ne sont pas présentes dans le fichier.
N'hésitez pas à revenir en cas de besoin. De mon coté je vais tenter de vous faire un truc un peu plus propre.

A bientôt

 

[Dugenou]

Et pour montrer que je ne vous en veut pas, V4 où j'ai refait le tableau par centre et par client en utilisant des TCD pour avoir des formules moins lourdes.

Cordialement