Trouver une donnée dans un tableau croisé sur condition
Bonjour,
Je dois vérifier la facturation de différents transporteurs utilisés pour l'envoi de colis par un logisticien mais la vérification automatique va delà de mes compétence ; Si je sais le faire sur une simple recherche dans un tableau, je ne sais pas le faire en croisant la donnée à trouver en abscisse et ordonnée mais surtout sur condition du transporteur et du mode utilisé.
Je vous joint le fichier Excel où j'ai retiré toutes les données qui ne servent pas.
Dans le premier onglet "Expéditions", vous trouverez ce qui est facturé par le logisticien avec en colonne B le numéro de la commande, en colonne D le transporteur, en colonne I le poids, en colonne E le mode de transport, en colonne K ce qui a été facturé et en colonne N le code postal.
Dans le second onglet "report-advanced_20181229-6", ce sont les données issues de mon logiciel et auquel je fais appel en colonnes V à AD sur le premier onglet pour comparaison.
Les coûts indiqués en colonne X sont approximatifs car ce sont des moyennes et ne reprennent pas le coût précis de chaque transporteur selon le poids mais aussi le département de destination qui est indiqué en colonne N.
Ces coûts sont indiqués dans l'onglet "GEODIS mes" pour ce transporteur et dans l'onglet "EXAPAQ predict" pour l'autre transporteur qui lui définit un coût uniquement sur le poids du colis (les poids indiqués doivent être lu "jusqu'à XX kg inclus" en ligne 1 de l'onglet "GEODIS mes" et en colonne A de l'onglet "EXAPAQ predict").
J'ai commencé manuellement ce travail en colonne Z et j'aimerai donc qu'il soit automatisé et j'ai réellement besoin de vous.
Il faudrait donc une formule qui vérifierai le nom et le mode de transporteur utilisé (colonnes D et E) puis en fonction du poids (colonne I) et du département uniquement pour GEODIS (colonne N) trouver le coût correct dans les onglets "GEODIS mes" et "EXAPAQ predict".
Je suis sur la dernière version 16.20 Mac.
Un grand merci à vous tous d'avance car je suis réellement ennuyé sachant que j'ai 12 mois à vérifier...
Stéphane.
Bonjour,
Pour GEODIS
=DECALER('GEODIS mes'!$A$1;EQUIV(ENT(CNUM(N2)/1000);'GEODIS mes'!$A:$A;0)-1;EQUIV(I2;'GEODIS mes'!$1:$1;1))Pour EXAPAQ predict
=DECALER('EXAPAQ predict'!$B$1;EQUIV(I2;'EXAPAQ predict'!$A:$A;1);)je te laisse combiner les 2 formules en fonction du transporteur
Bonjour et un grand merci.
J'ai malgré tout 2 petits soucis dont un que je n'arrive pas à résoudre :
- Premier résolu : Cela ne fonctionnait pas pour les poids inférieurs à la première tranche. Par exemple, en ligne 258 du fichier joint, cela ne donnait rien et j'ai donc ajouté une tranche 0,0 pour l'onglet "GEODIS mes".
Y avait-il une solution en modifiant la formule ?
- Non résolu : Les poids indiqués doivent être inclus mais sont exclus actuellement. Par exemple, en ligne 255, le résultat devrait donner 38,29€ et non 42,03€ ; Comment faire sans modifier toute ma grille de tarifs ?
Merci encore.
Stéphane.
- Premier résolu : Cela ne fonctionnait pas pour les poids inférieurs à la première tranche. Par exemple, en ligne 258 du fichier joint, cela ne donnait rien et j'ai donc ajouté une tranche 0,0 pour l'onglet "GEODIS mes".
en effet, j'y ai pensé un peu tard
cette solution est la meilleure
Y avait-il une solution en modifiant la formule ?
oui c'est possible en testant la valeur du poids par rapport à la première tranche
- Non résolu : Les poids indiqués doivent être inclus mais sont exclus actuellement. Par exemple, en ligne 255, le résultat devrait donner 38,29€ et non 42,03€ ; Comment faire sans modifier toute ma grille de tarifs ?
en effet je n'y avais pas pensé !!
bon, je refais les formules en partant du premier fichier ...
Corrections (sans ajouter l'élément 0g)
Pour GEODIS
=DECALER('GEODIS mes'!$A$1;EQUIV(ENT(CNUM(N2)/1000);'GEODIS mes'!$A:$A;0)-1;SI(I2<'GEODIS mes'!$B$1;1;EQUIV(I2;'GEODIS mes'!$1:$1;1)+SI(NB.SI('GEODIS mes'!$1:$1;I2)>0;-1;0)))Pour EXAPAQ predict
=DECALER('EXAPAQ predict'!$B$1;SI(I2<'EXAPAQ predict'!$A$2;1;EQUIV(I2;'EXAPAQ predict'!$A:$A;1)+SI(NB.SI('EXAPAQ predict'!$A:$A;I2)>0;-1;0));)je te laisse combiner les 2 formules en fonction du transporteur
Je suis désolé mais cela ne semble pas fonctionner ou j'ai loupé un truc.
J'ai testé sur les lignes 255 où je devrais avoir 38,29€ et en ligne 200 en mettant 1kg et la valeur change alors qu'elle ne devrait pas.
Désolé car j'abuse peut-être...
Voici le fichier à nouveau en pièce jointe.
en ligne 255 le poids est de 39,002
s'il était de 39,000 ce serait bien 38,29€
en ligne 200 la formule est incorrecte, il y a un #REF!
=SIERREUR(SI(Z200="GEODIS MES";DECALER('GEODIS mes'!$A$1;EQUIV(ENT(CNUM(N200)/1000);'GEODIS mes'!$A:$A;0)-1;SI(I200<'GEODIS mes'!$B$1;1;EQUIV(I200;'GEODIS mes'!$1:$1;1)+SI(NB.SI('GEODIS mes'!$1:$1;I200)>0;-1;0)));SI(Z200="EXAPAQ PREDICT";DECALER('EXAPAQ predict'!$B$1;SI(I200<'EXAPAQ predict'!$A$2;1;EQUIV(I200;'EXAPAQ predict'!$A:$A;1)+SI(NB.SI('EXAPAQ predict'!$A:$A;#REF!)>0;-1;0)););""));"")avec ma formule c'est correct !
il y a beaucoup de poids avec un 3ème chiffre significatif après la virgule ... doit-on l'omettre dans les calculs ?
Merci et désolé de ne pas avoir vu.
Effectivement, je n'avais pas vu et il y a effectivement souvent 0,002 dernière la virgule.
Oui, cela serait bien d'omettre ; Comment faire ?
Stéphane.
Effectivement, je n'avais pas vu et il y a effectivement souvent 0,002 dernière la virgule.
Oui, cela serait bien d'omettre ; Comment faire ?
dans ce cas :
GEODIS
=DECALER('GEODIS mes'!$A$1;EQUIV(ENT(CNUM(N2)/1000);'GEODIS mes'!$A:$A;0)-1;SI(ARRONDI(I2;2)<'GEODIS mes'!$B$1;1;EQUIV(ARRONDI(I2;2);'GEODIS mes'!$1:$1;1)+SI(NB.SI('GEODIS mes'!$1:$1;ARRONDI(I2;2))>0;-1;0)))EXAPAQ
=DECALER('EXAPAQ predict'!$B$1;SI(ARRONDI(I2;2)<'EXAPAQ predict'!$A$2;1;EQUIV(ARRONDI(I2;2);'EXAPAQ predict'!$A:$A;1)+SI(NB.SI('EXAPAQ predict'!$A:$A;ARRONDI(I2;2))>0;-1;0));)