Formule de comparaison excel Résolu/Fermé

Question

Bonjour,

Je cherche une formule pour mon fichier Excel. Je voudrai faire une somme par rapport a un tableau. Je voudrai savoir si c'est possible. Exemple simplifier :

Tableau 1:
---------A-------------------B----
| 1 |  Poids---------------Prix
| 2 |  1 kg-----------------6.70
| 3 |  5 kg---------------10.50
| 4 |  7 kg---------------12.40
| 5 |  10 kg-------------15.25
| 6 |  15 kg-------------17.25
| 7 |  30 kg-------------23.25

Tableau 2
-----------D-------------------E----
| 1 |  Assurance------------Prix
| 2 |  1--------------------+2.30
| 3 |  2--------------------+3.20
| 4 |  3--------------------+4.40
| 5 |  4--------------------+5.60
| 6 |  5--------------------+6.80


Dans une liste ou je mettrai les objets :
-----------A----------------B---------------C-------------D--------
| 10 |  Objet-----------Poids--------Assurance-------Prix
| 11 |  Objet 1-------1.100 kg------------1--------------
| 12 |  Objet 2-------2.500 kg------------3--------------
| 13 |  Objet 3-------1.800 kg------------2--------------
| 14 |  Objet 4-------12.000 kg----------5--------------
| 15 |  etc...
etc..  

Donc, en fonction du poids de l'article A11 indiquer dans B11 par exemple, l'assurance en C1, je voudrai je voudrai que j'ai le résultat automatiquement. Que la formule compare le poids dans le tableau, exemple pour le même article B11 étant > D2 donc prendre le prix supérieure de 10.50€ ainsi de suite avec les autres articles. Et quelle ajoute le prix en fonction de l'assurance choisi, exemple pour le même objet en A11 vu que l'on a assurance 1, ajouté au prix la somme correspondante de 2.30€.

Donc avec les formules on devrait trouver les résultats suivant pour le même tableau :
-----------A----------------B---------------C-------------D--------
| 10 |  Objet-----------Poids--------Assurance-------Prix
| 11 |  Objet 1-------1.100 kg------------1--------- 12.80€ (10.50€ + 2.30€)
| 12 |  Objet 2-------2.500 kg------------3--------- 14.90€ (10.50€ + 4.40€)
| 13 |  Objet 3-------7.000 kg------------2--------- 15.60€ (12.40€ + 3.20€)
| 14 |  Objet 4-------12.000 kg----------5---------- 22.05€ (15.25€ + 6.80€)
| 15 |  etc...
etc..  

La formule doit comparée deux chose. C'est possible ou pas ?

Merci

Mike-31 · Answer

Salut,

Certainement possible, mais les explications sont floues, un exemple de fichier serait le bienvenu, que nous ne soyons pas obligé de le faire pour toi, à joindre avec ce lien

https://www.cjoint.com/

gbinforme · Answer

bonjour

En fonction de tes données exemples voici le tableau correspondant avec la formule cherchée :

=RECHERCHEV(F2;poids;2)+RECHERCHEV(G2;Assurance;2;0)

http://www.cijoint.fr/cjlink.php?file=cj200912/cijRJnS4r0.xls

Mike-31 · Answer

Re,

La réponse de gbinforme que je salut, m'a donné du courage.

D’après tes explications bien que je ne sois pas d’accord avec cette ligne
| 14 | Objet 4-------12.000 kg----------5---------- 22.05€ (15.25€ + 6.80€) vu que dans tes tableau 12,000 kg n’existe pas donc il faut prendre la valeur supérieure comme référence soit 15,00kg ce qui correspond à un prix de 17,25 + 6,80 = 24,05€ 

Formule matricielle à confirmer avec Ctrl+Shift+Entrée
=RECHERCHEV(INDEX($A$2:$A$7;EQUIV(VRAI;$A$2:$A$7>=B11;0));$A$2:$B$7;2;0)+RECHERCHEV(C11;$D$2:$E$7;2;0)

exemple sur ce lien

https://www.cjoint.com/?mqx1JBXmsM

vcloclo1 · Answer

@gbinforme, 
Ton fichier marche bien, c'est bien se que je chercher a faire. 


@Mike-31, 
Le tien marche bien aussi mais quel difference entre une formule normal et matricielle ? 


Par contre pour les deux fichier, si pour un objet il ni a pas d'assurance, j'ai pas de calcule, j'ai "#N/A". 


Un grand merci a vous deux pour votre aide

Mike-31 · Answer

Re,

Explication sur les formules matricielles sur ce lien mieux expliqué que je ne l'aurai fait

http://www.info-3000.com/excel/matrice.php
Comme tu le demandais, mes formules vont chercher les valeurs supérieures dans ton tableau si la valeur exacte n’existe pas.
Pour revenir au message d'erreur "#N/A" des formules proposées. Cela arrive lorsque l'on fait référence à une cellule vide ou la valeur cherchée est inexistante, il suffit de compléter la formule par une conditionnelle SI ou SI(ESTERREUR. Dans le cas présent, il est préférable d’utiliser cette dernière, j’ai donc ajouté la partie en gras à la formule En D11 et incrémenté vers le bas afin de ne rien afficher

{=SI(ESTERREUR(RECHERCHEV(INDEX($A$2:$A$7;EQUIV(VRAI;$A$2:$A$7>=B11;0));$A$2:$B$7;2;0)+RECHERCHEV(C11;$D$2:$E$7;2;0));"";RECHERCHEV(INDEX($A$2:$A$7;EQUIV(VRAI;$A$2:$A$7>=B11;0));$A$2:$B$7;2;0)+RECHERCHEV(C11;$D$2:$E$7;2;0))}

gbinforme · Answer

bonjour

Par contre pour les deux fichier, si pour un objet il ni a pas d'assurance, j'ai pas de calcule, j'ai "#N/A". 
 pour effectuer le contrôle :
=RECHERCHEV(F2;poids;2)+SI(ESTERREUR(RECHERCHEV(G2;Assurance;2;0));0;RECHERCHEV(G2;Assurance;2;0))

vcloclo1 · Answer

@Mike-31,
Dans ton cas a toi, ou l'interré d'avoir une formule matricielle, vu que l'on a quand meme une formule par ligne ?

@gbinforme,
C'est bon, ta formule mache bien

Merci

vcloclo1 · Answer

J'ai un petit souci avec la formule. Si j'ai un poids de 5.100 kg je dois avoir le prix du poids supérieur, les poids son jusqu'à.

Donc pour 5.100 kg sa sera 12.40€ et non pas 10.50€, faut que sa face le calcul maxi a 5 kg et a partir de 5.001 kg le prix supérieur et de meme pour tous les autre poids.

Merci

Mike-31 · Answer

Re,

De mon côté, c'est ce que j'avais compris et essayais de t'expliquer sur le post 6, que ce soit ma formule simple en F ou la matricielle en D dans mon exemple du post 3, vont chercher la valeur supérieure si la valeur exacte n'est pas trouvée

gbinforme · Answer

bonjour

J'ai un petit souci avec la formule.

Ce n'est pas la formule qui est en tord, c'est le barème où j'avais dupliqué la première valeur :
Poids	Prix
0,000 kg	6,70 €
1,000 kg	10,50 €
5,000 kg	12,40 €
7,000 kg	15,25 €
10,000 kg	17,25 €
15,000 kg	23,25 €

Il faut bien commencer à zéro car tout ce qui dépasse le maximum est facturé au maximum.

vcloclo1 · Answer

Bonjour,

Ce n'est pas la formule qui est en tord, c'est le barème où j'avais dupliqué la première valeur :

En faite, des que sa depasse meme d'un gramme faut que sa passe au tarif superieur. Donc 5.000 kg sera facturé 12.40€ mais 5.001 kg sera facturé a 15.25€ jusqu'au poids suivant qui est de 7.000 kg maxi, etc ...

Merci

vcloclo1 · Answer

Petit up 

Merci

eriiic · Answer

Bonjour,

5.000 kg sera facturé 12.40€ mais 5.001 kg sera facturé a 15.25€
Ca ne correspond pas au tableau initial du post 1...
Et si tu es vraiment au gramme près (poids de la bande de timbrage...) rajoute 1 gramme à ton tableau de valeurs.

eric

PS: mais je crois que que tu as une vision un peu stricte des grilles tarifaires. Dans l'absolu ça pourrait être à un dix-millionième de gramme le changement de tarif, donc à 5.0000000001 kg. Autant laisser 5 non ?

Mike-31 · Answer

Salut,

Si tu reprenais mon post 3, (alors que nous sommes tout de même au 15éme) ou je te proposais deux formules dont une matricielle qui fait exactement ce que tu cherches si la valeur est exacte elle affiche le prix de la grille tarifaire et dès que la valeur est supérieure elle affiche la valeur supérieure de la grille tarifaire.

A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

vcloclo1 · Answer

eriiic PS: mais je crois que que tu as une vision un peu stricte des grilles tarifaires. Dans l'absolu ça pourrait être à un dix-millionième de gramme le changement de tarif, donc à 5.0000000001 kg. Autant laisser 5 non ?

Sa serai que de moi oui, mais quand je vais a la poste, eu il sans foute. La derniere fois il m'on fait payé le prix superieur pour le cas expliquer, c'est a dire 5.001 kg. Donc pour ma liste d'areticle, je voudrai avoir le tarif exacte, comme ça je sais reellement se que je vais payé.

Mike-31 Si tu reprenais mon post 3, (alors que nous sommes tout de même au 15éme) ou je te proposais deux formules dont une matricielle qui fait exactement ce que tu cherches si la valeur est exacte elle affiche le prix de la grille tarifaire et dès que la valeur est supérieure elle affiche la valeur supérieure de la grille tarifaire. 

Je vais essayé ton code pour voir se que sa donne, j'avais fait plus l'essai sur le code de "gbinforme" qui ete plus facile pour moi pour adapter a mon tableau final. Mais je vais faire avec le tien si tu me dis que c'est ok pour ce que je veux faire.

Merci

eriiic · Answer

En fait j'ai plutot l'impression que tu as raisonné à l'envers...
avec la formule de gb tu y perds pour 5kg (pile poil !!!!) puisqu'il te retourne le tarif supérieur et tu voudrais le tarif inférieur. 
En faite, des que sa depasse meme d'un gramme faut que sa passe au tarif superieur. Donc 5.000 kg sera facturé 12.40€ mais 5.001 kg sera facturé a 15.25€ jusqu'au poids suivant qui est de 7.000 kg maxi
non, pas avec tes valeurs du post 1, 5kg à 7kg : 12.40 €

vcloclo1 · Answer

C'est tous a fait se que j'ai dis. Pour un poids de 5.000 kg, sa sera 12.40€ et pour un gramme de plus c'est le prix superieur. Pareil pour les autre poids, du moment que l'on depasse les maxi, on passe au tarif suivant.

Mike-31 · Answer

Re,

Non je ne suis pas d'accord, 5.000 kg, sa sera 12.40€ et pour un gramme de plus c'est le prix superieur.

reprends ton premier post

| 1 | Poids---------------Prix 
| 2 | 1 kg-----------------6.70 
| 3 | 5 kg---------------10.50 
| 4 | 7 kg---------------12.40 
| 5 | 10 kg-------------15.25 
| 6 | 15 kg-------------17.25 
| 7 | 30 kg-------------23.25 

5kg c'est 10,50 et dès que l'on dépasse même d'un gramme on passe à 12,40

Si tu changes tout le temps ta grille  tarifaire comment veux tu que nous ayons les mêmes résultats, en qui concerne mes formules, elles respectent cette procédure,

vcloclo1 · Answer

Ok, c'est bon, c'est moi qui me suis mélanger les pinceau lol. Mike-31 a raison. Par contre avec la formule de gbinforme, je n'arrive pas a se resultat. Si je depasse meme d'un gramme il ne fait pas le bon cacule.

eriiic · Answer

Re,

Tu es tétu, mais moi aussi, la formule de gb est parfaitement correcte.
Pour 5.1kg elle te retourne bien 12.40 ce qui est conforme à tes tranches.
Je vais essayer de le dire autrement puisque tu as du mal à l'admettre :
La seule différence c'est que pour 5kg pile-poil (c'est à dire pas pour 5.00000000001) tu voudrais qu'elle te retourne 10.50 comme pour 4.99999999999999 kg.
Un postillon sur ton coli et tu passes de 5kg à 5.00000000001kg, tu crois pas que tu pinailles un peu ?
eric

Formule de comparaison excel

20 réponses

Discussions similaires