Microsoft 365 INDEX EQUIV mais selon un critère de quantité ?

[yptsba]

Bonjour à tous,

Pourriez-vous m’aider ? Je vais essayer d'expliquer ma demande mais ce n’est pas évident...
J'ai mis en PJ un fichier Excel en exemple.

Voilà ce que je souhaite, en Feuil1 j'ai en colonne A des références, et en colonne B des qty.

En Feuil2, j'ai des références en colonne A, des qty en colonne B, et en colonne C le nom du préparateur.

Je cherche une formule à mettre en feuil 1 colonne C.
Cette formule doit aller chercher en Feuil2 colonne C le nom du préparateur correspondant à la référence en Feuil1 colonne A mais la formule doit aussi tenir compte des quantités préparé par chaque préparateur en Feuil2 colonne B et toujours utiliser en premier le plus ancien.

C’est-à-dire on voit en Feuil2 que :

• Toto à préparer en tout 5 quantités de référence H
• Michel à préparer en tout 2 quantités de référence Y
• Adil à préparer en tout 3 quantités de référence H

En Feuil1:
en ligne 2 on demande qui à préparer : 3 quantités de H ?
On sait que toto en préparer 5 x H en tout et que c’est le plus ancien donc le résultat attendu est « toto »
en ligne 3 on demande qui à préparer : 2 quantités de H ?
On sait que toto en à préparer 5 x H en tout, et qu’on en a déjà utiliser 3 x H en ligne 2, il en reste donc 2 et comme c’est le plus ancien, le résultat attendu est encore « toto »
En ligne 4 on demande qui à préparer : 2 quantités de H ?
On sait que toto en à préparer 5 x H en tout, et qu’on en a déjà utiliser 3 x H en ligne 2 et 2 x H en ligne 3, on a donc déjà utilisé les 5 quantités de toto.
Donc le résultat attendu est « Adil » car on a déjà utilisé les 5 de toto mais Adil à préparer 3 x H on peut donc en récupérer 2 sur les 3.
Etc…


J'ai trouvé une formule qui utilisie INDEX EQUIV mais elle va uniquement chercher le préparateur correspondant à la référence sans tenir compte des quantités… je l'ai mis en colonne D au cas où ça serve de point de départ à quelqu’un

Merci à tous n'hésitez pas à me poser des questions, je ne suis pas sûr d'avoir été très clair...

 

[Victor21]

Bonjour Tomy @yptsba , bienvenue sur XLD.

Une proposition en pj avec une colonne supplémentaire et une formule matricielle à valider avec les 3 touches simultanées Ctrl+Maj+Entrée :

 

[yptsba]

Bonsoir,


Merci beaucoup, ça à l'air d"être exactement ce que je recherche, mais c'est la 1ère fois que je vois une formule matricielle.

Du coup j'aimerais utiliser cette formule sur un autre fichier excel mais ne n'arrive pas à la reproduire...

Je ne comprends pas car dans la formule il n'y aucune cellule ni feuil d'indiqué, ça parle de tableau1.

Peux-tu m'expliquer un peu comment ça fonctionne ? A quoi correspond ce Tableau1 ? Comment est-il défini ? Comment indique t’on à Excel ou se trouve se tableau1 ?

=INDEX(Tableau1[Nom du préparateur];EQUIV(1;(Tableau1[Référence]=[@Référence])*(Tableau1[Cumul/réf]>=[@[Cumul/réf]]);0))


Sinon j'ai mis un nouveau fichier en PJ, pourrais-tu adapter la formule à ce fichier stp?

J’ai inséré le cumul/ref.

En jaune « Ref Four » l'équivalent de « référence » dans du 1er fichier
En Bleu « Qty » l'équivalent « Quantité » dans l'autre tableau
En vert « Numéro de commande fournisseur » équivalent de « Nom du préparateur »

La formule est à mettre en Q2 de la feuille nommé « Produits Défectueux »


Merci d'avance pour ton aide
Cordialement
Tomy

 

[Victor21]

Bonjour, yptsba.

... c'est la 1ère fois que je vois une formule matricielle.

Les formules et fonctions matricielles servent à effectuer des opérations sur les matrices (zones de cellules), et non plus sur des cellules. SOMMEPROD(), SOMME.SI.ENS() sont des fonctions matricielles incluses dans Excel, et ne nécessitent pas de validation particulière, contrairement aux formules comme celle que je vous ai indiquée.

... ça parle de tableau1.
Peux-tu m'expliquer un peu comment ça fonctionne ? A quoi correspond ce Tableau1 ? Comment est-il défini ? Comment indique t’on à Excel ou se trouve se tableau1 ?
=INDEX(Tableau1[Nom du préparateur];EQUIV(1;(Tableau1[Référence]=[@Référence])*(Tableau1[Cumul/réf]>=[@[Cumul/réf]]);0))
...

Pour mettre une zone sous forme de tableau structuré, après avoir sélectionné la zone (y compris les titres), :

Le nom "Tableau1,...x" est généré automatiquement, et les titres des colonnes génèrent les noms de zones "[Nom du Préparateur]..."
Ces noms seront ensuite utilisés pour plus de lisibilité dans les formules à la place des références colonne/ligne.

Un autre avantage des tableaux structurés est qu'il est inutile de prévoir des lignes vides : à chaque nouvelle entrée, une ligne est automatiquement créée, avec recopie des bonnes formules dans les colonnes qui en contiennent.
De plus, les noms définis sont automatiquement redimensionnés dans les formules qui les utilisent.

 

[yptsba]

Bonjour,

Merci pour tes explications, donc c'est simplification de la mise en forme, c'est pour cela que les feuil et cellule n'apparaissent pas dans la formule?

Suite à tes explications j'ai essayé d'appliquer ta formule à mon nouveau fichier, mais je n'y arrive toujours pas, ça me fait une erreur... Je suis nul...

Pourrais tu insérer ta formule dans ce fichier stp que j'ai un second exemple?

Merci beaucoup de m'aider, c'est très sympa.

Cordialement

 

[Victor21]

Re,

...Pourrais tu insérer ta formule dans ce fichier stp que j'ai un second exemple?

Non, je ne comprends pas l'exemple : 1 stylo commandé, 18 défectueux ...
PS : les sommeprod() n'ont pas besoin de validation matricielle, ainsi que je l'indiquais plus haut.

 

[yptsba]

Pardon j'ai oublié de changé les quantité en feuil2. Je viens de mettre à jour le fichier.

D’ailleurs que se passe t'il si les quantités ne sont pas suffisante? J'ai par exemple laissé volontairement 2 pour la ref SSJ-POP alors qu'il en faudrait 3.

Cela va créer un N/A?

Cordialement
Tomy

 

[Victor21]

Re, @yptsba
J'ai ajouté 4 commandes fictives AAAA,BBBB,CCCC et DDDD)pour que cela soit cohérent. Voir proposition en pj :

Edit : Pas rafraîchi. Je ne vois votre dernier post que maintenant. J'ai ajouté un sierreur() pour le cas de commande manquante.
La pj correspond au précédent fichier.

 

[yptsba]

Bonjour, j'ai pu tester ce week-end ça répond parfaitement à mon besoin, merci beaucoup Victor21