Afisarea denumirii fiecarui produs din factura:
Identificatorul fiecarui produs se gaseste în codul produsului din cadrul facturii, fiind ultimele 4 caractere ale acestuia. Primul caracter al codului de produs din factura reprezinta codul depozitului unde este 10510k102k stocat produsul. Exemplu: primul produs din factura are codul 11002, deci este vorba de produsul cu identificatorul 1002 (din nomenclatorul de produse), si este stocat în depozitul cu codul 1 (din nomenclatorul de depozite).
Pentru a afla care este identificatorul fiecarui produs din factura, e necesar sa se extraga ultimele 4 caractere din cadrul codului, si sa se transforme rezultatul extragerii într-un numar (utilizarea functiei VALUE):
VALUE(RIGHT(A8;4))
Utilizarea functiei VLOOKUP ofera posibilitatea de a afla care este denumirea produsului aferenta identificatorului astfel obtinut. Tabelul de consultat este Nomenclatorul de produse ($A$16:$G$35) iar coloana în care se face cautarea este coloana a doua din acest tabel:
VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35; )
Afisarea pretului produselor din factura:
Pentru o întelegere mai rapida a cerintei, se vor utiliza urmatoarele notatii:
PretEuro=VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35; )
PretLei=PretEuro*$G$4
Pentru a verifica daca pretul în lei este de ordinul miilor, zecilor de mii sau milioanelor, vom utiliza functia LEN de numarare a caracterelor unui text:
=IF(LEN(PretLei)=5;MROUND(PretLei;1000);IF(LEN(PretLei)=6;MROUND(PretLei; 10000); IF(LEN(PretLei)=7;MROUND(PretLei;100000); PretLei)))
Înlocuind pretul în lei cu formula de mai sus, obtinem formula care trebuie utilizata pentru aceasta cerinta:
=IF(LEN(VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;3)*$G$4)=5;MROUND(VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;3)*$G$4;1000);IF(LEN(VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;3)*$G$4)=6;MROUND(VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;3)*$G$4;10000);IF(LEN(VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;3)*$G$4)=7;MROUND(VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;3)*$G$4;100000);VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;3)*$G$4)))
Cheltuielile de transport:
În functie de cantitatea livrata (C8 pentru primul produs) si de destinatia facturii (G5), se va consulta tabelul cu tarifele de transport, citind valoarea situata la intersectia coloanei cu localitatea din factura cu rândul aferent cantitatii livrate. Vom utiliza functia HLOOKUP, pentru preluarea tarifului în EURO. Cunoastem cheia de consultare (G5), cunoastem tabelul de consultat (J15:N36) si trebuie sa aflam si numarul rândului pentru care dorim afisarea tarifului de transport (ultimul argument al functiei HLOOKUP).
Vom utiliza functia Match pentru aflarea pozitiei cantitatii livrate în cadrul listei cu cantitatile transportate (I15:I36)
NrRand=MATCH(C8;$I$15:$I$36;0)
Impunem conditia ca transportul sa fie realizat de furnizor si cantitatea sa fie pozitiva, altfel cheltuiala de transport va fi 0.
Formula finala pentru primul produs din factura este:
=IF(AND($D$4="Da";C8>0);HLOOKUP($G$5;$J$15:$N$36;MATCH(C8;$I$15:$I$36;0))*$G$4; )
Cheltuielile de stocare vor tine cont de depozitul în care se afla produsul (codul depozitului fiind primul caracter din codul produsului). Se va realiza extragerea codului de depozit si apoi consultarea pe verticala a tabelului cu depozitele, în functie de acest cod, pentru aflarea coeficientului de stocaj. Formula finala de calcul a cheltuielilor de stocare, aplicând coeficientul astfel obținut la valoarea din factura, va fi:
=VLOOKUP(VALUE(LEFT(A8;1));$J$8:$K$10; )*D8*C8
Calculul valorii facturii
Formula de calcul este:
Valoare=CantitateLivrata*PretLivrare+CheltuialaTransport+CheltuialaStocare-Discount
Discountul se va calcula, tinând cont de conditiile precizate, astfel:
Discount=CantitateDiscount*ProcentDiscount*PretProdus
Pentru o mai buna întelegere a formulei, vom efectua urmatoarele notatii:
DataInc - Data început promotie produs (preluata din nomenclatorul de produse)
DataSf - Data sfârsit promotie produs (preluata din nomenclatorul de produse)
QDisc - Cantitatea discount produs (preluata din nomenclatorul de produse)
%Disc - Procent discount produs (preluat din nomenclatorul de produse)
DataInc= VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;6)
DataSf= VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;7)
QDisc= VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;4)
%Disc= VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;5)
Pentru ca un produs sa prezinte discount, trebuie sa îndeplinesca simultan conditiile ca data facturii sa fie între data de început si cea de sfârsit promotie, cantitatea livrata pentru acel produs sa fie mai mare decât cantitatea de discount.
Utilizand notatiile de mai sus, discountul se va calcula astfel:
Discount= IF(AND(QDisc<C8;DataInc<=$G$2;DataSf>=$G$2);QDisc*D8*%Disc;0)
Prin urmare, valoarea facturii, tinând cont si de discount va fi:
Valoare=C8*D8+E8+F8-Discount
Facând toate înlocuirile necesare, formula finala de calcul a valorii devine:
=C8*D8+E8+F8-IF(AND(VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;4)<C8;VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;6)<=$G$2;VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;7)>=$G$2);VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;4)*D8*VLOOKUP(VALUE(RIGHT(A8;4));$A$16:$G$35;5);0)
10. Recodificare
=VALUE(REPLACE(A8;2;1;RIGHT(YEAR(TODAY());2)))
Observatie: Explicatia s-a realizat doar pentru primul produs din factura.
|
