recherche de données selon 2critères qui définissent une colonne et une ligne

[Audrey86]

Bonsoir,

je souhaite créer un outil d'aide à la décision.

j'ai plusieurs grilles tarifaires par fournisseurs qui se présentent sous la forme d'un tableau à deux entrées : un département par ligne (soit 95 lignes) et une tranche de poids par colonne ( nombre de tranche encore indéfini mais au moins 20 ).

une fois ces données renseignées dans un onglet pour chaque fournisseur je souhaite créer un onglet où l'on pourrait indiquer pour chaque département et chaque tranche le prix le moins cher et à quel fournisseur il correspond. j'ai opté pour la fonction MIN pour le tarif et une simple fonction SI pour le nom du fournisseur ( après je me demande comment je ferai si j'ai plus de 7 fournisseurs si quelqu'un pouvait me donner une solution ). Jusque la tout va bien !

je cherche une solution pour mon onglet choix : je souhaite que l'utilisateur n'ai qu'à indiquer un poids et un département pour qu'ensuite la liste des fournisseurs avec le prix associé s'affiche par ordre croissant ( du moins cher au plus cher ) ou tout simplement le moins cher.
j'ai essayé index, equiv.. mais je ne suis pas à l'aise du tout avec ces fonctions ! je ne sais pas du tout quoi faire !

si quelqu'un peut m'aider ça serait vraiment gentil, merci d'avance

ci-joint le fichier (enfin l'ébauche )

 

[Tibo]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonsoir,

Pas certain d'avoir compris.

Peut-être avec ceci en B8 :

=INDEX(comparatif!$B$4:$E$9;EQUIV(C4;comparatif!$A$4:$A$9;0);EQUIV(B4-0.1;comparatif!B3:E3)+1)

et en C8 :

=INDEX(comparatif!$B$4:$E$9;EQUIV(C4;comparatif!$A$4:$A$9;0)+1;EQUIV(B4-0.1;comparatif!B3:E3)+1)

Si pas ça, reviens avec plus de précisions.

@+

Edit : en relisant, j'ai confirmation que je n'ai pas compris.

Audrey, peux-tu joindre un fichier plus complet en nous mettant manuellement le résultat attendu.

@+

 

[CISCO]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonsoir à tous, bonsoir Tibo

En pièce jointe, le tout début du travail, en C8 uniquement, pour le MIN, avec des plages nommées en 3D.

Pour le moment, je ne vois pas comment faire en B8, ni comment obtenir les autres valeurs dans l'ordre croissant.

@ plus

 

[Cousinhub]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonjour,

Salut Tibo et Cisco

On pourrait utiliser un Tableau Croisé Dynamique (TCD), afin d'extraire les tarifs les plus intéressants selon le poids et le département.

Cependant, sous 2007, l'option "Plages de feuilles de calcul avec étiquettes" n'est pas automatiquement présentée dans les rubans.

Il suffit de rajouter un raccourci "Assistant Tableau Croisé Dynamique" dans la barre d'outils accès rapide.

Pour ce faire, tu cliques sur le bouton "Office", en haut à gauche.
Puis sur "Options Excel", en bas à droite
Et enfin sur "Personnaliser"

Dans le ruban de gauche, tu sélectionnes "Commandes non présentes sur le ruban", et tu cliques sur "Assistant Tableau Croisé Dynamique"

Puis sur "Ajouter" et tu peux quitter

Dans ton fichier, tu insères un nouvel onglet

Tu cliques sur le bouton "Assistant TCD"

Tu sélectionnes "Plages de feuilles de calcul avec étiquettes"

Tu sélectionnes à la main les plages qui t'intéressent (dans ton cas, tu cliques sur l'onglet tpt1, tu sélectionnes "A3:E9"), puis tu cliques sur "Ajouter", et pareil pour l'onglet tpt2

Tu cliques sur "Terminer"

Tu obtiens alors un TCD avec le nombre de valeurs

Pour modifier, dans le volet "Liste de champs de TCD", tu cliques sur "Nombre de valeurs", puis sur "Paramètres de champs de valeur"

Tu sélectionnes "Min"

tu obtiens donc un TCD, et en fonction du ou des filtres que tu désires, tu obtiens le tarif le plus avantageux....

Ouf........

ça parait bien long, mais cela a l'avantage de n'utiliser aucune formule...De plus, si tu as des cellules non renseignées, ou avec du texte, celles-ci ne sont pas prises en compte...

regarde le fichier joint, j'y ai mis quelques images pour illustrer mes propos...

Bon courage

 

[Audrey86]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonjour !


Merci à tous pour vos réponse rapide ! Je ne pourrais y regarder plus en détail que ce soir, j'essairai toutes vos propositions.

J'avais déjà tester avec un TDC mais si on veut mettre le nom du fournisseur le tableau devient tout de suite moins exploitable.

j'essairai ce soir de mieux m'expliquer en illustrant dans un fichier, je sais que ce que je souhaite est possible je l'ai déjà vu mais impossible de me souvenir comment ils avaient fait !

En tout cas merci et à ce soir

bonne journée

 

[CISCO]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonjour à tous

Bonne idée, bhbh. Et merci pour les explications.

@ plus

 

[Audrey86]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonjour à tous,

Tibo j'ai essayé mais ça me met " NA " peut être parce que là j'utilise excel 97 ou 2000 !
En tout cas j'ai refait un fichier avec des explications comme tu me l'a demandé j'espère être plus claire.


Cisco merci ça fonctionne ne manque plus que le nom du fournisseur !

bhbh j'ai réessayer avec TBC le seul inconvénient c'est qu'on ne voit pas le nom du fournisseur, je souhaiterai que la grille comparative se présente sous la forme donnée dans le fichier (parce que chose inutile selon moi mais qui sera faite : ils utiliseront une version papier au cas où il n'est pas accès au fichier...)


en tout cas merci à tous pour votre aide !

 

[CISCO]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonjour à tous

Attention Audrey, ma précédente proposition ne tient pas compte du département. C'était juste pour montrer une méthode possible, à adapter à ton besoin...


@ plus

 

[Monique]

Re : recherche de données selon 2critères qui définissent une colonne et une ligne

Bonjour,

Le nom des onglets en G2:G4 (par formule ou non)

Les valeurs min de chaque onglet :

=DECALER(INDIRECT($G2&"!A3");EQUIV(ChxDpt;INDIRECT($G2&"!A4:A20");0);EQUIV(ChxPds;INDIRECT($G2&"!B3:E3");1))+LIGNE()/9^9

Ou bien

=RECHERCHEV(ChxDpt;INDIRECT($G2&"!A4:E17");EQUIV(ChxPds;INDIRECT($G2&"!A3:E3");1);0)+LIGNE()/9^9

Ligne()+9^9 pour départager les ex aequo

L’onglet « Comparatif » ne sert plus à rien

Reste à savoir si les coûts sont fixés pour des tranches allant de 100 inclus à 299,999 ou bien de 100,001 à 300 (dans le fichier, c’est la 1ère solution)

Barres de défilements dans l’onglet de choix, pour faire des essais.
Mise en forme conditionnelle dans tous les onglets pour vérifier plus facilement