XL 2016 Lien entre deux tableaux grâce à la fonction RechercheV

MarineV

XLDnaute Nouveau
Bonjour,

Je ne suis pas experte en Excel et je ne sais pas donc pas si ce que je souhaite est réalisable ou non...

J'ai un fichier qui comporte deux onglets. Le premier onglet « Histo » comporte un tableau avec, en ligne, les références des recettes et les noms des recettes et la date à laquelle les recettes ont été fabriquées. Dans le même tableau, en colonne, on retrouve tous les ingrédients et pour chaque recette je rempli la quantité dans la cellule correspondant à l’ingrédient. Je suis donc amenée à avoir un nombre infini de recette avec un nombre infini d’ingrédient potentiellement utilisable.

Dans le deuxième onglet « fiche recette », j’ai une cellule référence, une cellule avec le nom de la recette et une cellule avec la date puis, en dessous, un tableau avec la liste des ingrédients utilisés ainsi que la quantité. Je souhaiterais que, quand je rentre le numéro de référence de la recette, je retrouve, le nom, la date de fabrication ainsi que tous les ingrédients utilisés à cette date pour faire la recette.

Grâce à la fonction « RECHERCHEV » d’Excel, j’ai réussi à aller chercher les informations correspondantes dans mon premier onglet « Histo ».

Mon problème est que j’aimerais que seuls les ingrédients qui ont effectivement été utilisés pour une recette s’affiche dans le tableau lorsque je tape le numéro de référence. Par exemple, pour la référence numéro 1, la recette 1 comporte 5 ingrédients et ces derniers s’affichent dans le tableau. Mais pour la référence numéro 2, la recette 2 ne comporte que l’ingrédients 1 et 4 et j’aimerais que seuls ces deux ingrédients avec les quantités correspondantes s’affichent… (J’ai mis un deuxième tableau en exemple pour expliquer ce que j’aimerais).

L’objectif est de remplir le tableau « Histo » et de pouvoir retrouver, plusieurs mois/année après, en tapant uniquement le numéro de référence, l’intégralité de la recette avec les ingrédients et quantité utilisés.

J'espère qu'avec le fichier joint mon explication sera assez claire et que quelqu'un pourra m'aider !

Merci d'avance et bonne journée !
 

Pièces jointes

  • Exemple logiciel.xlsx
    12.4 KB · Affichages: 48

Dugenou

XLDnaute Barbatruc
Bonjour,
Une proposition par formule matricielle pour l'ingrédient et index double equiv pour les quantités. A ta disposition pour détailler/expliquer si cette solution te plait plus que la macro.
 

Pièces jointes

  • marineV recettes.xlsx
    14 KB · Affichages: 43

MarineV

XLDnaute Nouveau
Bonjour,

Merci à vous deux pour vos réponses.
Je souhaitais initialement utiliser que des formules et pas de macro (car plus compliqué que les formules) mais la solution de thebenoit59 marche vraiment très bien, c'est exactement ce que je voulais !

La solution par formule matricielle et index double equiv de Dugenou marche également bien mais je n'arrive pas à la faire fonctionner si je rajoute des ingrédients dans mon tableau "Histo"... J'ai essayé de modifier un peu la formule mais je n'arrive pas à comprendre comment elle fonctionne. Peut-être pourriez-vous m'aider ?

Dans tous les cas, un grand merci à vous deux pour le temps que vous avez passé et pour votre aide !
 

Dugenou

XLDnaute Barbatruc
Bonjour,
Pour adapter la formule matricielle si tu as plus de colonnes :
=SIERREUR(INDEX(Tableau1[#En-têtes];PETITE.VALEUR(SI(DECALER(Tableau1[[#En-têtes];[Ingrédient 1]:[Ingrédient 5]];EQUIV('Fiche recette'!$B$1;Tableau1[Ref.];0);)<>"";COLONNE(Tableau1[[#En-têtes];[Ingrédient 1]:[Ingrédient 5]]));LIGNES($6:6)));"")
changer la valeur ingredient5 en ingredientxx (ou xx est ta dernière colonne et valider par ctrl+majprov+enter (autre possibilité : insérer des colonnes avant la dernière et ensuite les renommer)
Pour l'affichage des quantités :
=SIERREUR(INDEX(Tableau1[[Ingrédient 1]:[Ingrédient 5]];EQUIV('Fiche recette'!$B$1;Tableau1[Ref.];0);EQUIV('Fiche recette'!A6;Tableau1[[#En-têtes];[Ingrédient 1]:[Ingrédient 5]];0));"")
même chose : changer le nom de la dernière colonne, pas de validation matricielle.
Cordialement
 

MarineV

XLDnaute Nouveau
J'ai bien essayé de changer la valeur "Ingrédient 5" en "Ingrédient 8" (par exemple) mais Excel me dit qu'il y a une erreur dans la formule dans ce cas et je ne vois pas d'où vient l'erreur vu que j'ai uniquement changé le 5 en 8...
En fait, j'aimerais que la formule prenne toute seule la dernière colonne du tableau pour ne pas avoir à changer la formule à chaque fois que je rajoute un ingrédient : de [Ingrédient 1] à [dernière colonne] en quelque sorte mais je ne sais pas comment faire.
Merci encore !
 

MarineV

XLDnaute Nouveau
Au temps pour moi, j'avais uniquement oublié un crochet (une erreur est vite arrivée avec ces longues formules) et le changement de nom de colonne fonctionne bien. En revanche j'aimerais beaucoup que la formule trouve automatiquement le nom de l'en-tête de la dernière colonne si cela est possible ?
 

Dugenou

XLDnaute Barbatruc
Bonjour,
voir pj avec deux zones nommées (voir formules/gestionnaire de nom) pour éviter d'avoir des formules trop longues et peu lisibles :
nb_ingred qui est un decaler de largeur variable selon le nb d'ingredients (prévu jusque colonne ZZ)
donnees_ingred qui est un decaler de hauteur et largeur variable : prévu pour 2000 lignes de tableau
si tu dépasses ces deux valeurs (ZZ colonnes et 2000 lignes) il faudra modifier les formules dans formules/gestionnaire de nom
Cordialement
 

Pièces jointes

  • marineV recettes2.xlsx
    13.8 KB · Affichages: 30
Dernière édition:

MarineV

XLDnaute Nouveau
Re-bonjour,

Ca marche ! Je pense que jusqu'à la colonne ZZ et la ligne 2000 ce sera amplement suffisant mais effectivement il y aura juste à changer les formules si je dépasse. Merci beaucoup !

J'aurai encore un autre petit problème et je me permets de vous demander encore de l'aide...

Si cette fois je décide de créer une nouvelle recette (avec les ingrédients 2, 3 et 7 dans mon exemple) dans un onglet "nouvelle recette", est-ce qu'il y aurait un moyen pour que les valeurs se rentrent automatiquement dans le tableau Histo (en dernière ligne du tableau) ? Pour l'instant je rentre manuellement et cela ne me pose pas de problème mais quand j'aurai plus d'ingrédients cela deviendra plus contraignant.
 

Pièces jointes

  • marineV recettes2.xlsx
    15.2 KB · Affichages: 27

Dugenou

XLDnaute Barbatruc
Re
et non désolé : je pensais que vous entriez les données dans le tableau Histo.
Si vous voulez saisir dans une sorte de formulaire, il faut faire du VBA et j'en suis incapable. Attendons donc le passage d'un spécialiste ou bien ouvrez un nouveau sujet avec votre fichier en pj et une demande de création d'un formulaire
 

thebenoit59

XLDnaute Accro
Pour la partie en VBA je peux peut-être te proposer une solution.
Soit nous passons directement par un formulaire comme proposé par Dugenou ou en cliquant sur un bouton ça enregistre les valeurs dans le tableau.
Si ça te convient, je ne vérifierai pas la partie formule qui te permet de créer la Fiche recette depuis l'Histo, suite à l'ajout de lignes ou de colonnes, je n'ai pas suivi les différentes modifications.
 

MarineV

XLDnaute Nouveau
D'accord, merci beaucoup pour votre aide Dugenou, je n'y serai jamais arrivée seule !
Pourquoi pas pour la partie en VBA avec un formulaire Thebenoit59 mais je vous avoue que je ne suis pas vraiment douée avec ce genre d'outils. Un formulaire permettrait, simplement en cliquant sur un bouton, d'enregistrer toutes les valeurs de la nouvelle recette dans l'onglet Histo ?
 

thebenoit59

XLDnaute Accro
Une première proposition.
Si pas de nom de recette, un message s'affiche, pareillement si pas d'ingrédient.
Attention, j'ai conservé le style de ta fiche, donc nom de recette en [a1], premier ingrédit en [a4], si tu changes les emplacements ça ne fonctionnera plus.

Tu appuies sur l'icône et la recette s'ajoute dans le fichier.
Je t'ai ajouté un bouton qui nettoie la fiche Nouvelle recette, ça ne se fait pas automatiquement à la fin de l'importation mais on peut l'ajouter.

Attention : Dans Excel Ingrédient 1 <> Ingredient 1 <> ingrédient 1 <> ingredient 1
Il faut faire attention à la casse.
 

Pièces jointes

  • marineV recettes2.xls
    74.5 KB · Affichages: 38

Discussions similaires

Statistiques des forums

Discussions
312 885
Messages
2 093 256
Membres
105 658
dernier inscrit
Mario Richard