請問有什麼辦法能讓下圖的左表直接轉成右表的形式
需求:
每筆資料需要根據包裝數量來拆分筆數,並且要更改數量
以產品A為例
總數量為3210,數量分拆成500*6+200
1.500
2.500
3.500
4.500
5.500
6.500
7.200
共分成7筆
再來因為包裝方式為"塑膠袋"
故要多一筆總是3210
共8筆
因為每次都要靠複製貼上然加上手動改數量有點麻煩
想到用巨集 不過感覺這應該是需要寫VBA比較可行吧
不知道該怎麼寫?
是否有高人可指點一下
=IFERROR(LEFT(D2,FIND("*",D2,1)-1),IFERROR(LEFT(D2,FIND("+",D2,1)-1),C2))
再將公式複製到F3:F5
H2:
輸入第一個產品:A
H3:
=IF(COUNTIF($H$2:H2,H2)=IF(VLOOKUP(H2,$A$2:$F$5,5,0)="塑膠袋",1,0)+ROUNDUP(VLOOKUP(H2,$A$2:$F$5,3,0)/VLOOKUP(H2,$A$2:$F$5,6,0),0),OFFSET($A$1,MATCH(H2,$A$1:$A$5,0),0),H2)
再將公式複製到H4:H15
I2:
=VLOOKUP(H2,$A$1:$E$5,2,0)
J2:
=IF(COUNTIF($H$2:H2,H2)=IF(VLOOKUP(H2,$A$2:$F$5,5,0)="塑膠袋",1,0)+ROUNDUP(VLOOKUP(H2,$A$2:$F$5,3,0)/VLOOKUP(H2,$A$2:$F$5,6,0),0),IF(VLOOKUP(H2,$A$2:$F$5,5,0)="塑膠袋",VLOOKUP(H2,$A$2:$F$5,3,0),VLOOKUP(H2,$A$2:$F$5,3,0)-SUMIF($H$1:H1,H2,$J$1:J1)),IF(COUNTIF($H$2:H2,H2)=ROUNDUP(VLOOKUP(H2,$A$2:$F$5,3,0)/VLOOKUP(H2,$A$2:$F$5,6,0),0),VLOOKUP(H2,$A$2:$F$5,3,0)-SUMIF($H$1:H1,H2,$J$1:J1),VALUE(VLOOKUP(H2,$A$2:$F$5,6,0))))
K2:
=VLOOKUP(H2,$A$1:$E$5,4,0)
L2:
=VLOOKUP(H2,$A$1:$E$5,5,0)
再將I2:L2公式複製到I3:L15
關於出現的0值,可用儲存格格式設定消除。
錦色如月,子耀光芒。