calcul zile depozitare/tarif

Ce este nou in Microsoft Excel 2010?
Informatii despre cum se utilizeaza Microsoft Excel 2010
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
vio67
Mesaje: 13
Membru din: Sâm Feb 20, 2010 4:57 pm

calcul zile depozitare/tarif

Mesaj de vio67 » Vin Oct 15, 2021 7:03 pm

Buna seara,
Am urmatoarea problema la care va rog cu o sugestie/idee :
-un client care introduce/scoate zilnic cereale depozitate si doreste zilnic o situatie a stocului pe zile cat si o valoare a pretuli de platit cf contact. Atasez si un fisier pe care am incercat ...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: calcul zile depozitare/tarif

Mesaj de IPP » Sâm Oct 16, 2021 6:48 pm

Buna ziua

In primul rand cred ca aveti nevoie de un identificator unic pentru fiecare intrare de lot si implicit o coloana noua in structura de date. Presupunand ca veti depozita concomitent marfa din loturi diferite numai asa veti putea afla stoc si calcule eventual diferentiate pentru zilele de depozitare pentru fiecare intrare in parte.

Asadar, o evidenta rapida a stocului "la zi". Se poate folosi o foaie-raport in care sa folositi introducere manuala pentru fiecare nume de lot si apoi formule de sumifs care v-ar oferi coloanele:
id Lot|Intrare|Iesire|Diferenta (sold)

sau ceva similar folosind alte metode (Pivot Table, macro, depinde si cum evidentiati iesirile ca numar pozitiv sau negativ)

Partea cu calcul costuri in functie de data iesirii mi se pare mai complicat de realizat si m-as gandi la o solutie cu macro, ceva de genul (dupa ce in foaia-baza de date sunt operate deja iesirile pentru, sa spunem Lot01).
-se alege/scrie undeva identificatorul de lot si se ruleaza macro care ar trebui sa aduca in alta parte fiecare iesire facuta impreuna cu data in care a fost efectuata. De acolo calcul zile (si implicit cat este de plata), sold ramas... si tot asa pentru fiecare iesire.

Sau, ca varianta pentru situatie pentru iesirile din ziua curenta (evident dupa ce au fost operate/scrise in foaia baza de date). Macro ar trebui sa identifice fiecare iesire pentru ce lot este si, pentru fiecare lot in parte sa faca operatiunile descrise mai sus.

In fisierul atasat gasiti (in sheet1) doar modelul de aranjare a informatiei respectiv de Raport pentru soldurile la zi.
Crearea unui cod macro (care poate sa devina mai stufos, de exemplu daca va trebui undeva sa se tina cont si de alte variabile, de ex. pentru un lot sunt mai multe iesiri, pentru unele deja s-a platit...) din punctul meu de vedere depaseste un subiect de forum.

IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: calcul zile depozitare/tarif

Mesaj de IPP » Sâm Oct 16, 2021 9:14 pm

Buna ziua

In fisierul atasat (in Sheet1) am incercat o alta abordare, fara macro. Ramane de vazut daca e in regula sau nu pentru dvs.
Am plecat de la ideea ca primele 14 zile de depozitare sunt gratis si incepand cu ziua 15 se taxeaza.
Premise:
-nu se vor face sortari in lista
-exista cate o singura intrare pentru fiecare "lot" in parte

Pe coloana E se calculeaza soldul "la zi" pentru fiecare iesire dintr-un anumit lot
Pe coloana F se calculeaza zilele scurse de la o anumita iesire raportat la intrarea initiala din care am mai scazut cele 14 (echivalentul zilelor gratuite). Da, pot sa apara rezultate cu minus in aceasta forma, deocamdata am lasat asa pentru a se putea urmari mai usor principiul de functionare.
Pe coloana G am calculat cat ar fi de plata dupa urmatoarea formula: cantitate iesita * numar de zile scurse calculat pe coloana F * pret.
Pretul se extrage automat din minilista configurata in zona P2:Q4 in functie de numarul de zile taxabile (adica incepand cu prima zi dupa cele 14 gratis)

IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

vio67
Mesaje: 13
Membru din: Sâm Feb 20, 2010 4:57 pm

Re: calcul zile depozitare/tarif

Mesaj de vio67 » Sâm Oct 16, 2021 9:57 pm

Buna seara,

Multumesc foarte mult pentru timpul acordat problemei.
Incerc sa inteleg formulele si eventual sa revin cu un fisier imbunatatit ( atat cat ma pricep)

O seara frumoasa !

VH

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: calcul zile depozitare/tarif

Mesaj de IPP » Dum Oct 17, 2021 7:55 am

Buna ziua

Cate ceva despre formulele folosite:
-Coloana E (Sold): e o diferenta dintre intrari si iesiri avand criteriu suplimentar id Lot. Ce o face "speciala" este folosirea adreselor mixte care fac dinamica (pe masura ce se copiaza formula in jos) insumarea, in sensul in care soldul se va calcula intodeauna pornind de la E2 pana la randul curent (unde este formula cu ultima informatie completata: se presupune ca adaugati si copiati formula in jos doar pe masura ce aveti de introdus si informatie). Acest lucru permite aflarea soldului curent imediat dupa operarea unei iesiri.
Formula cred ca ar putea fi imbunatatita prin adaugarea unei conditii: daca este intrare sa se ia in calcul toate informatiile existente (ok aici ar fi util de facut un dynamic range in name manager la nivel de coloane). Acest lucru ar permite ca pe fiecare rand de intrari sa existe tot timpul soldul actualizat iar la nivel de iesire, soldul ramas dupa operarea iesirii respective; Printr-o simpla filtrare s-ar obtine o lista atat cu cantitatile intrate cat si cu soldurile existente pentru fiecare intrare in parte, practic la zi

-Coloana F (Zile depozitare...): intern, datele calendaristice in Excel sunt numere intregi; deci se face o diferenta intre numarul corespunzator datei efecturii iesirii si cel al intrarii. Pentru ca am spus deja ca este obligatorie existenta unei singure intrari la nivel de lot, data intrarii pentru lotul respectiv nu a fost extrasa prin formule complicate de cautare ci printr-un simplu sumifs. Evident ca rezultatul ar fi eronat daca ar exista mai multe intrari pentru acelasi lot. Asa, rezultatul unui sumifs nu poate fie decat data corecta pentru ca exista data (in esenta numar) care respecta criteriile.
Apoi am scazut 14 corespunzator zilelor netaxabile. Rezultatele vor fi folosite mai departe pentru extragerea pretului.
Personal eu as opta pentru doua coloane: una in care sa se extraga numarul de zile asa cum este pentru ca rezultatele ar permite sa va faceti o idee despre cat de mult isi tine depozitata marfa fiecare firma in parte, si o coloana calculata (ca sa nu mai apara rezultate negative) pentru a ajunge la zile-criterii de pret

-Coloana G (De Plata): inmulteste cantiatea cu zilele taxabile si cu pretul cf. numarului de zile taxabile. Acest pret se extrage pe baza folosirii functiei vlookup cu argumentul True. Folosirea acestui argument permite returnarea unui rezultat chiar daca item-ul cautat nu este identic cu cel lista de preturi. Sunt mai multe exemple de acest fel pe forum. Cred ca explicatiile din help-ul aplicatiei sunt mai bune. Rezultatul extras ar trebui sa fie de genul: ce este mai mic de 1, returneaza 0, ce este >=1 si <15 returneaza 0,1 si ce este >=15 returneaza 0,2

Altfel, unele lucruri (ma refer la updatari de formule, eventuale reguli de formatare conditionata, data validation) ar putea fi mai usoare daca se foloseste totul intr-un table dar personal evit asta cand exista anumite formule cum sunt cele, de ex. de pe coloana E)

IP

cip.st
Moderator
Moderator
Mesaje: 550
Membru din: Vin Iun 06, 2014 1:43 pm
Localitate: Iași

Re: calcul zile depozitare/tarif

Mesaj de cip.st » Dum Oct 17, 2021 3:21 pm

O mică completare:
Vlookup, cu True ca ultim argument sau fără el, caută pornind de la supoziția că șirul de căutare este ordonat crescător. Probabil folosește căutarea binară ca să se miște mai repede, habar n-am, dar ce e important este că dacă îi furnizăm datele de intrare ordonate crescător, se va comporta ca și cum ar face selecție pe intervale de tip închis la stânga, deschis la dreapta, descrise de coloana de căutat.
ca urmare, formula:

Cod: Selectaţi tot

=VLOOKUP(A1,{0,0;16,0.1;31,0.2},2)
- valabilă pentru setările englezești
sau

Cod: Selectaţi tot

=VLOOKUP(A1;{0\0;16\0,1;31\0,2};2)
- valabilă pentru setările românești

va aduce 0 pentru orice valoare din intervalul [0,16), 0.1 pentru [16,31) și respectiv 0.2 pentru orice valoare mai mare sau egală cu 31.

În privința calculelor... problema este o variantă a problemei valorilor la descărcarea FIFO. Nu știu dacă se poate aborda altfel decât cu VBA sau Power Query, fără a forța utilizatorul să facă el alocarea ieșirilor pe intrări astfel încât să respecte regula FIFO.

vio67
Mesaje: 13
Membru din: Sâm Feb 20, 2010 4:57 pm

Re: calcul zile depozitare/tarif

Mesaj de vio67 » Lun Oct 18, 2021 10:49 pm

Buna seara,
Multumesc pentru raspuns , dar explicatiile dvs sunt prea avansate pentru mine .

VH

vio67
Mesaje: 13
Membru din: Sâm Feb 20, 2010 4:57 pm

Re: calcul zile depozitare/tarif

Mesaj de vio67 » Lun Oct 18, 2021 11:24 pm

Buna seara,

Va multumesc frumos pentru rezolvarea trimisa dar, din pacate, datorita faptului ca nu v-am oferit suficiente detalii, cred ca nu mi-ati inteles problema, de aceea am facut urmatorul rationament de calcul referitor la tabelul atasat de IPP;
Rationamentul ptr prima iesire de 22 tone din data de 23.10.2021 este urmatorul;
Iesirea functioneaza dupa principiul -primul intrat, primul iesit.
Asadar, cele 22 tone se compun din 20+2=22 tone

1) Daca marfa a iesit pe data de 23.10.2021, am 15 zile libere, urmatoarele 15 zile sunt taxate cu 0,1 euro/tona/zi

Deci, avem ptr marfa intrata pe 05.10.2021-15 zile libere, resp.pana pe data de 20 liber(fara taxa), deci, pana pe 23 raman 2 zile taxate cu 0,1 euro/zi--(20x2x0,1=4 euro) ptr marfa intrata pe 06.10.2021-15 zile libere(6+15=21), deci pana pe 21.10.2021 nu avem taxa, ramane o zi, taxata cu 0,1 euro(2x1x0,1=0,2)
Total suma de facturat pentru cantitatea de 22 tone=4 euro+0,2 euro=4,2 euro

VH
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

cip.st
Moderator
Moderator
Mesaje: 550
Membru din: Vin Iun 06, 2014 1:43 pm
Localitate: Iași

Re: calcul zile depozitare/tarif

Mesaj de cip.st » Mar Oct 19, 2021 8:27 am

Pentru a calcula costul depozitării unui lot cu stocul „Stoc” la o dată anume, „DataStoc”, poți folosi formula:

Cod: Selectaţi tot

=Stoc*(DataStoc-DataIntrare-VLOOKUP(DataStoc-DataIntrare,{0,0;16,16;31,31},2))*VLOOKUP(DataStoc-DataIntrare,{0,0;16,0.1;31,0.2},2)
Dacă nu are altcineva timp o să revin diseară cu o soluție simplificată care să nu-mi mănânce prea mult timp.

Actualizare‼
Mi-am dat seama ulterior că am înțeles greșit modul de calcul și ca urmare formula de mai sus este greșită!
În ipotezele:
  • * ziua ieșirii nu este luată în calcul
    * costul pentru 1 tonă cu 60 de zile stoc (ieșire în ziua 61) este compus din 0€ pentru zilele 1-15 + 15*0,1 pentru zilele 16-30 + 30*0,2 pentru zilele 31-60
formula de calcul este:

Cod: Selectaţi tot

=Stoc * SUMPRODUCT({0,0.1,0.2},IF(B1-A1-1-15*{0,1,2}>0,IF(B1-A1-1-15*{0,1,2}>15,15,B1-A1-1-15*{0,1,2}),0))
unde, B1 este data ieșirii din stoc, iar A1 data intrării în stoc

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: calcul zile depozitare/tarif

Mesaj de IPP » Mar Oct 19, 2021 8:31 am

Buna ziua

Am spus de la bun inceput ca propunerea mea se bazeaza pe faptul ca fiecare lot poate sa aiba doar cate o intrare si oricate iesiri, pana ajungeti la sold 0. Dvs. aveti pentru acelasi lot mai multe intrari.
Daca, principial vorbind, acea solutie ar fi fost in regula s-ar fi putut face un workaround: In loc de Lot1 cu sa spunem 3 intrari, se poate introduce sub forma de Lot1_1, Lot1_2, Lot1_3 iar iesirile in consecinta. Iar un raport legat de Lot1 s-ar putea face cu ajutorul unei coloane ajutatoare in baza de date care sa "converteasca" fiecare aparitie de tip Lot1_1, Lot1_2, Lot1_3 in Lot1.

Ce doriti dvs. probabil ar trebui sa faca un soft specializat pe gestiune/contabilitate?
Altfel, revin la primul meu raspuns din acest subiect.

Succes
IP

Scrie răspuns

Înapoi la “Intrebari despre Excel 2010”