XL 2019 Gestion de stock EQUIV, INDEX, ...

[vilcoyotte90]

Bonjour,
Je cherche à gérer un stock dans un fichier excel, dans lequel, j'ai:

• une feuille de récap "bdd" dans laquelle je rentre et visualise toute les références (que j'ai rentré en manuel) que je peux avoir et leur caractéristiques standard et variations ainsi que leur état de stock issu de la feuille "stock"
• une feuille de "stock" ou je rentre l'emplacement (J), la référence, suivant le fabricant (colonnes S, T, U & V) et les variations au produit standard (colonnes AF à AK). Les références internes ("bdd" colonne A) sont alors rapatriées en colonne A de "stock" ainsi que les noms, caractéristiques standard, ...)

J'ai parcouru le forum pour trouver fabriquer la formule de recherche de la colonne A de "stock", ce qui m'a grandement aidé à approcher mon but mais ce n'est toujours pas ça:

• La formule matricielle suivante en stock A11 retourne bien la bonne référence attendue (MOT0000589) en colonne A, à partir de la colonne S (S11=25). Si j'enlève la valeur en S11, la formule retourne 0 car elle n'existe pas, rien d'étonnant. La même formule en stock A20 modifiée pour aller chercher en T plutôt que S me retourne la référence MOT0000568 alors qu'elle n'existe pas (T20 vide). je ne comprends pas ce qui change et pourquoi un résultat différent.

{=SIERREUR(INDEX(soupapes_bdd;EQUIV(S11&AF11&AG11&AH11;'Soupapes bdd'!S:S&'Soupapes bdd'!AF:AF&'Soupapes bdd'!AG:AG&'Soupapes bdd'!AH:AH;0);1);"Non trouvé")}

{=SIERREUR(INDEX(soupapes_bdd;EQUIV(T20&AF20&AG20&AH20;'Soupapes bdd'!T:T&'Soupapes bdd'!AF:AF&'Soupapes bdd'!AG:AG&'Soupapes bdd'!AH:AH;0);1);"Non trouvé")}

• Avant de découvrir le problème au dessus, j’avais des cas qui marchaient et ai donc fait évoluer la formule pour qu'il fasse la recherche pour S T U et V depuis la feuille "stock" (sachant que dans cette feuille, il y a par ligne 1 valeur dans S, ou T ou U ou V mais pas dans plusieurs) pour aller chercher dans "bdd" la dite valeur où il y a par ligne des valeurs multiples dans S, T U et V. on peut retrouver des références en T qui sont déjà utilisé en L pour une autre ligne (bdd S18 à 25 et T10) et une référence peut également être identique à une dimension du produit (bdd S26 et AG20). Le tout doit également être discriminé pour tomber sur 1 seule référence avec les variations (colonnes AF à AK). La ça se gâte, stock A3, A4 et A5 retournent une bonne valeur mais A5 devrait retourner "Non trouvé" car S6=25 avec AF6=8 n'existe pas dans bdd mais il retrourne la ref MOT0000586. Sur les 2 lignes suivante, malgré des références inexitantes, j'ai une référence qui sort...

{=SIERREUR(INDEX(soupapes_bdd;EQUIV(S3&AF3&AG3&AH3;'Soupapes bdd'!S:S&'Soupapes bdd'!AF:AF&'Soupapes bdd'!AG:AG&'Soupapes bdd'!AH:AH;0);1);SIERREUR(INDEX(soupapes_bdd;EQUIV(T3&AF3&AG3&AH3;'Soupapes bdd'!T:T&'Soupapes bdd'!AF:AF&'Soupapes bdd'!AG:AG&'Soupapes bdd'!AH:AH;0);1);SIERREUR(INDEX(soupapes_bdd;EQUIV(U3&AF3&AG3&AH3;'Soupapes bdd'!U:U&'Soupapes bdd'!AF:AF&'Soupapes bdd'!AG:AG&'Soupapes bdd'!AH:AH;0);1);SIERREUR(INDEX(soupapes_bdd;EQUIV(V3&AF3&AG3&AH3;'Soupapes bdd'!V:V&'Soupapes bdd'!AF:AF&'Soupapes bdd'!AG:AG&'Soupapes bdd'!AH:AH;0);1);"Non trouvé"))))}

Je vous joins le fichier avec différents cas d'erreur que j'ai et j'espère avoir été assez clair sur mon besoin.

Merci de votre aide

 

[vgendron]

Hello

je ne comprend pas bien ton fichier
il y a une feuille de BDD ==> c'est celle qui est remplie manuellement pour créer toutes les références.. correct?

la feuille Stock..==> elle sert à quoi?? je ne vois que des formules à rallonge (qui font ramer le fichier)
j'ai l'impression qu'il ne s'agit que d'un extract de la feuille BDD..?

 

[vilcoyotte90]

Salut, Merci de t'intéresser au sujet.

La feuille BDD est bien celle qui contient toutes les références et est rempli manuellement

La feuille STOCK me permet de saisir la quantité de pièces (et surtout leur emplacement car il peut y avoir la même pièce a différents emplacement), leur référence en fonction des fabricants (S, T, U, V) et la/les variations s'il y en a par rapport au produit standard (AF à AK). 1 variation = 1 référence

Je voudrais que la référence en colonne A de STOCK aille chercher la bonne valeur de la colonne A de la feuille BDD, en fonction des critères énumérés précédemment

 

[vilcoyotte90]

{=SIERREUR(SI(S3<>"";INDEX(soupapes_bdd;EQUIV(1;('Soupapes bdd'!$S:$S=S3)*('Soupapes bdd'!$AF:$AF=AF3)*('Soupapes bdd'!$AG:$AG=AG3)*('Soupapes bdd'!$AH:$AH=AH3)*('Soupapes bdd'!$AI:$AI=AI3)*('Soupapes bdd'!$AJ:$AJ=AJ3)*('Soupapes bdd'!$AK:$AK=AK3);0);1);SI(T3<>"";INDEX(soupapes_bdd;EQUIV(1;('Soupapes bdd'!$T:$T=T3)*('Soupapes bdd'!$AF:$AF=AF3)*('Soupapes bdd'!$AG:$AG=AG3)*('Soupapes bdd'!$AH:$AH=AH3)*('Soupapes bdd'!$AI:$AI=AI3)*('Soupapes bdd'!$AJ:$AJ=AJ3)*('Soupapes bdd'!$AK:$AK=AK3);0);1);SI(U3<>"";INDEX(soupapes_bdd;EQUIV(1;('Soupapes bdd'!$U:$U=U3)*('Soupapes bdd'!$AF:$AF=AF3)*('Soupapes bdd'!$AG:$AG=AG3)*('Soupapes bdd'!$AH:$AH=AH3)*('Soupapes bdd'!$AI:$AI=AI3)*('Soupapes bdd'!$AJ:$AJ=AJ3)*('Soupapes bdd'!$AK:$AK=AK3);0);1);SI(V3<>"";INDEX(soupapes_bdd;EQUIV(1;('Soupapes bdd'!$V:$V=V3)*('Soupapes bdd'!$AF:$AF=AF3)*('Soupapes bdd'!$AG:$AG=AG3)*('Soupapes bdd'!$AH:$AH=AH3)*('Soupapes bdd'!$AI:$AI=AI3)*('Soupapes bdd'!$AJ:$AJ=AJ3)*('Soupapes bdd'!$AK:$AK=AK3);0);1);"Pas de REF"))));"Ref inconnue")]

Ne lâchant pas l'affaire, j'ai réussi à modifier la formule de feuille STOCK A3 et arriver à quelque chose de plus satisfaisant:

• je n'ai pas détécté d'erreur pour le moment avec de mauvaises références qui ressortiraient
• ce la rame beaucoup moins...

Par contre, ce n'est peut-être pas très optimisé au niveau du code...

Edit: je pense aussi que la pause de midi m'a aéré la tête...

 

[vgendron]

je ne comprend toujours pas comment tu remplis le tableau (et ce que tu remplis)
mais voici une version de ton fichier avec des tables structurées "t_xxxx" (plus "dynamique" que les plages nommées)
pour simplifier les formules

 

[vilcoyotte90]

Merci beaucoup, en effet, c'est plus "dynamique"
j'ai pas l'habitude d'utiliser les tableaux... je vais m'y mettre ça va sûrement améliorer les performance de mon fichier.
Il faut juste que je rajoute a ta formule les conditions pour ne pas que soient affiché une référence en A si S, T, U et V sont vides ou que la ref n'existe pas

 

[vilcoyotte90]

Petit retour d'utilisation, je suis bluffé par la réactivité qu'apporte l'utilisation des tableaux plutôt que les plages.
J'ai fait l'essai sur la version avec toutes les données, il me fallait désactiver le calcul automatique pour pouvoir saisir de nouvelles données et a chaque enregistrement, c'était 30 secondes...
Avec les tableaux, c'est à peine perceptible...

Merci encore pour la formule et l'amélioration

La formule finale est la suivante:

=SIERREUR(SI(S3<>"";INDEX(t_soup_bdd[Ref];EQUIV(S3&AF3&AG3&AH3&AI3&AJ3&AK3;(t_soup_bdd[Ref FM]&t_soup_bdd[var dia tige]&t_soup_bdd[var dia tête]&t_soup_bdd[var lg tot]&t_soup_bdd[var forme tête]&t_soup_bdd[var comp tête]&t_soup_bdd[var fixation]);0);1);
SI(T3<>"";INDEX(t_soup_bdd[Ref];EQUIV(T3&AF3&AG3&AH3&AI3&AJ3&AK3;(t_soup_bdd[Ref LS]&t_soup_bdd[var dia tige]&t_soup_bdd[var dia tête]&t_soup_bdd[var lg tot]&t_soup_bdd[var forme tête]&t_soup_bdd[var comp tête]&t_soup_bdd[var fixation]);0);1);
SI(U3<>"";INDEX(t_soup_bdd[Ref];EQUIV(U3&AF3&AG3&AH3&AI3&AJ3&AK3;(t_soup_bdd[Ref AE]&t_soup_bdd[var dia tige]&t_soup_bdd[var dia tête]&t_soup_bdd[var lg tot]&t_soup_bdd[var forme tête]&t_soup_bdd[var comp tête]&t_soup_bdd[var fixation]);0);1);
SI(V3<>"";INDEX(t_soup_bdd[Ref];EQUIV(V3&AF3&AG3&AH3&AI3&AJ3&AK3;(t_soup_bdd[Ref constructeur]&t_soup_bdd[var dia tige]&t_soup_bdd[var dia tête]&t_soup_bdd[var lg tot]&t_soup_bdd[var forme tête]&t_soup_bdd[var comp tête]&t_soup_bdd[var fixation]);0);1);"Pas de REF"))));"Ref inconnue")