Discussion :

[munity]

bonjour le phorum

j'essaie de mettre en place une formule index et equiv sur 2 critères
1 sur un type d'affranchissement
1 sur le poids d'un pli

le souci que je rencontre c'est que le poids n'est pas fixe donc je dois luis indiquer <= en fonction du produit d'affranchissement
mais je n'y parviens pas
si le poids est indiqué, tout est OK par contre s'il n'est pas indiqué, il m'affiche la valeur #N/A

j'ai essayé de créer une formule pour intégrer <= mais là il me mets #VALEUR (matricielle ou non)

ci dessous la copie d'écran du tableau



merci d'avance pour l'aide ou les pistes

david

[Pierre Fauconnier]

Salut.

Tu dois utiliser deux EQUIV dans l'INDEX, un pour le type, et un pour le poids. Index renverra alors la valeur à l'intersection de la ligne trouvée par le premier EQUIV et de la colonne trouvée par le second EQUIV.

[munity]

oui j'ai essayé cette méthode mais le problème c'est que si le poids n'est pas indiqué dans le tableau
par exemple
si le poids est égale à 2999 il mets #N/A
car les poids entre 2001 et 2999 sont à prendre en compte
Voir image ci dessous et c'est là qu'est l'os hélas..

[MarcelG]

Bonsoir,

Si je peux me permettre.
Voir le 3ème argument de la fonction Equiv.

[munity]

merci de ta réponse
mais j'ai essayé également si je mets 1 versus 0
au lieu de prendre le prix égal à 3000 gr (si je mets 2999)
il prend le prix de 2000 gr
c'est à dire 8.67 alors que je veux qu'il prenne pour tout ce qui est strictement supérieur à 2000 le tarif 9.63

et si je mets -1 ou 0 alors j'ai #N/A

[Pierre Fauconnier]

[munity]

merci Pierre mais comme je l'expliquais à MarcelG
je souhaite obtenir le prix compris entre 51 et 300
et donc je devrais avoir
3.744

et non 3.096
de 0à20
de 20à35
et ainsi de suite

si poids = 19 alors tarif 20 g
si poids 21 alors tarif 35
et non l'inverse

logiquement il faudrait pouvoir inclure <= à un endroit de la formule mais je ne vois pas
peut être que c'est du au fait que les plages de comparaison ne sont pas de même taille ? => ligne versus colonne pour les croisements?

[Pierre Fauconnier]

A toi de monter ton tableau selon ce que tu souhaites...

Soit tes poids sont par ordre croissant et

• le plancher est compris;
• le plafond est exclu;
• Equiv renvoie la colonne du plancher (la valeur INFERIEURE la plus proche).

Soit tes poids sont par ordre décroissant et

• ton plafond est inclus;
• ton plancher est exclu;
• Equiv renvoie la colonne du plafond (la valeur SUPERIEURE la plus proche).

Salut Marcel... On s'est croisés

Pour moi, c'est la valeur inférieure la plus proche et tu dois délimiter tes planchers en fonction de cela. Peux-tu donner la grille tarifaire avec un exemple?

[munity]

ci joint le fichier avec la grille tarifaire
grille tarifaire.xlsx

mes poids sont dans l'ordre croissant comme vu sur l'impression ecran
n'y a t'il pas possibilité de lui indiqué <= ou >= sur le poids ?

[Pierre Fauconnier]

Tu n'as pas d'autres possibilités que celles que je t'ai indiquées. Si tes poids sont par ordre croissant, ton plancher sera inclu et ton plafond exclu. C'est en général logique par rapport à la grille tarifaire utilisée.

Dans la grille que tu illustres, le tarif va de 20 gr INCLU à 35gr exclu, puis de 35gr inclu à 50gr exclu. Pour les lettres des lignes 2 à 6, il convient de répéter les prix "20gr" dans la colonne "35gr', et il faut procéder de manière identique pour les cellules qui sont vides dans les autres colonnes. Mais tu ne peux pas exprimer 20gr EXCLU et 35gr INCLU si tu mets tes poids par ordre croissant.

[boisgontierjacques]

Bonsoir,

Pour les poids, il faut utiliser Equiv(valeurCherchée;champLigne/Colonne;1) (positionne sur la valeur inférieure lorsque la valeur cherchée n'est pas trouvée).

Boisgontier

Exemple en PJ

[Pierre Fauconnier]

[...]

Rien de plus que les précédentes réponses fournies (en manque de visibilité sur le net?)...

[munity]

Ok
Merci pour ces reponses
Jai une derniere question
Pourquoi cette formule fonctionne je l utilse sur un autre tableau
Index(d2:e9;equiv(1;(test=a2:a9)*(test2<=b2:b9);0))
C est un exemple ecrit a l arrache donc la valeur de l index est peut etre fausse

[Pierre Fauconnier]

Celle-là travaille sur deux vecteurs colonnes, et pas sur une ligne et une colonne. Elle recherche le premier 1 qui sera renvoyé par le test logique (Excel transtype la valeur VRAI en 1 lors d'une opération arithmétique). Mais dans le cas de ton tableau, elle renverrait la PREMIERE ligne pour laquelle Test est trouvé en A ET Test2<= que B. Elle ne te renverrait donc que rarement le bon résultat. Cette fonction travaille en matricielle.

Si tu l'appliquais à ta matrice Lignes/Colonnes (Type/Poids) sur le second EQUIV, tu récupérerais souvent le plus petit tarif, car le second EQUIV s'arrêtera sur la première colonne qui renvoie VRAI (donc très souvent celle du poids le plut petit).

Ce qui serait intéressant, c'est que tu donnes le tarif tel qu'on te l'a fourni (hors tableau Excel, donc) ainsi que la façon de le lire. Mais je pense qu'il est assez rare de donner le plancher exclu et le plafond inclus, et si c'était le cas, il faudrait simplement exprimer tes poids dans l'ordre décroissant (mais je n'avais vu une grille tarifaire pareille).

[munity]

ok merci Pierre
je me disais bien que le fait d'avoir un tableau à deux entrées (ligne et colonne) ca posait problème
mais l'avis de pro me permet de passer à autre chose
je fais les test avant de valider le post

merci encore pour l'aide apportée par tous

david

[Pierre Fauconnier]

Pour moi, ce n'est pas le tableau à double entrée qui pose problème, mais la façon de lire les tarifs. Même avec un tableau à deux colonnes, tu n'auras pas le bon tarif si tu les classes par ordre croissant des poids à l'intérieur des types (en tout cas pas avec EQUIV, même avec l'exemple que tu as donné)...

C'est pour cela que je serais curieux de voir le tarif (hors Excel) sur lequel tu t'appuies...

[munity]

ci joint un exemple du tarif que j'applique


donc suivant cette grille
pour les poids jusque 20 g le prix est de 0.8 donc entre 1 et 20 alors 0.8
pour les poids de 21 à 100 g le prix est de 1.6 donc entre 21 et 100 alors 1.6
et ainsi de suite

est ce possible de réaliser la formule dans ce sens
en faisant comme suit
20 100
lettre verte 0.8 1.6

comme dans mon tableau ou dois tout inverser ?
un petit exemple me serait bien utile

merci d'avance

david

[Pierre Fauconnier]

Voici une solution pour une orientation "base de données"

[munity]

merci pour ta proposition Pierre mais il y a un problème
tu as ajouté un palier et cela ne va pas (ce qui fait que le poids entre 501 et 3000 grammes n'existe pas)
en fait il faut lire le tableau comme suit
0-20 = 0.8
21-100 = 1.6
101-250 = 3.2
251-500 = 4.8
501-3000 = 6.4

Et avec un sommeprod?
Qui me retournerait la valeur cible ?

[munity]

pour faire suite à ta méthode Pierre
je suis partie d'un système BDD
et me suis inspiré de formules du génial SilkyRoad pour une liste de validation sans doublons
en PJ le résultat
je parviens à ce que j'attends
c'est dommage que je ne puisse pas garder la présentation du tableau ligne/colonne (plus aisé à modifier....)
mais bon je suis toujours preneur d'une solution (macro ? dès changement valeur cellule)
Tarif_DB.xlsx

david