Formule de comparaison excel
Résolu
vcloclo1
Messages postés
501
Date d'inscription
Statut
Membre
Dernière intervention
-
eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention -
eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention -
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
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
A voir également:
- Formule de comparaison excel
- Formule si et excel - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Formule somme excel colonne - Guide
- Excel mise en forme conditionnelle formule - Guide
- Liste déroulante excel - Guide
20 réponses
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/
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/
bonjour
En fonction de tes données exemples voici le tableau correspondant avec la formule cherchée :
http://www.cijoint.fr/cjlink.php?file=cj200912/cijRJnS4r0.xls
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
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
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
@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
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
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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))}
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))}
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 :
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))
@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
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
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
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
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
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
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 :
Il faut bien commencer à zéro car tout ce qui dépasse le maximum est facturé au maximum.
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.
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
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
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 ?
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 ?
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.
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.
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
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
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 €
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 €
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.
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,
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,
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.
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
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