|
JSA代码,先进先出法
- function 先进先出法(){
- let arr=Sheets.Item("入库明细表").Range("a1").CurrentRegion.FormulaR1C1;
- let obj=arr.slice(1).reduce((obj,x)=>{
- x[x.length]="=TODAY()-RC[-6]"; //库龄
- let temp=obj[x[3]]=obj[x[3]] || [];
- return (temp.push(x),obj);
- },{});
- Sheets.Item("出库明细表").Activate();
- let res=Range("a1").CurrentRegion.Value2;
- res.forEach((x,i)=>{
- if (i<1) return;
- let temp=obj[x[4]],sy=x[5]
- for (let j=0;j<temp.length;j++){
- let y=temp[j];
- if (sy<=y[4]){
- x[6]=sy*y[5], y[4]-=sy; //计算成本、更新库存
- break;
- }else{
- x[6]=(x[6] || 0) + y[4]*y[5]; //计算成本、更新库存
- sy-=y[4], y[4]=0;
- }
- }
- });
- Range("a1").CurrentRegion.Value2=res;
- arr=arr.slice(1).filter(x=>x[4]!=0);
- with(Sheets.Item("想要的期末库存表")){
- Range("2:"+ Math.max(2,Range("a"+ Rows.Count).End(xlUp).Row)).ClearContents();
- Range("a2").Resize(arr.length-1,arr[0].length).Value2=arr;
- }
- }
复制代码 |
|