3704Excel問題? @ 錦子老師

3704

Excel問題?

燕青：錦子老師您好，請問有什麼辦法能讓下圖的左邊表格直接轉成右邊表格的形式。

需求：每筆資料需要根據包裝數量來拆分筆數，並且要更改數量，以產品A為例，總數量為3210，數量分拆成500*6+210。

1.500

2.500

3.500

4.500

5.500

6.500

7.210

共分成7筆，再來因為包裝方式為"塑膠袋"，故要多一筆總是3210，共8筆。

因為每次都要靠複製貼上然加上手動改數量有點麻煩，想到用巨集 不過感覺這應該是需要寫VBA比較可行吧！不知道該怎麼寫?

錦子老師：燕青，關於這個問題可以使用輔助欄與函數功能來解決：

點取F2儲存格，輸入公式：「=IFERROR(LEFT(D2,FIND("*",D2,1)-1),IFERROR (LEFT(D2,FIND("+",D2,1)-1),C2))」。

FIND("*",D2,1) 首先找尋*號在儲存格字串中位置。

LEFT(FIND("*",D2,1)-1) 接著抓取*號前的字串，因為要扣除*號故減1。

IFERROR(LEFT(D2,FIND("*",D2,1)-1),IFERROR (LEFT(D2,FIND("+",D2,1)-1),C2)) 如果找不到*號造成錯誤，改去找+號的位置，抓取+號前的字串，如果找不到+號造成錯誤，則抓取C欄同列儲存格的數值。

點取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儲存格。

VLOOKUP(H2,$A$2:$F$5,6,0) 傳回目前商品的包裝數量(箱/袋)。

VLOOKUP(H2,$A$2:$F$5,3,0) 傳回目前商品的總數量。

VLOOKUP(H2,$A$2:$F$5,5,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) ="塑膠袋",1,0)+ROUNDUP(VLOOKUP(H2,$A$2:$F$5,3,0) /VLOOKUP(H2,$A$2:$F$5,6,0),0) 如果目前商品的包裝方式為塑膠袋，則包裝袋數加1，否則不增加。

COUNTIF($H$2:H2,H2) 統計目前儲存格上方同商品的筆數。

MATCH(H2, $A$1:$A$5,0) 傳回目前商品位在A1:A5儲存格中的第幾列。

OFFSET($A$1,MATCH(H2, $A$1:$A$5,0),0) 傳回從A1儲存格移動N(MATCH函數傳回的值)列0欄的儲存格內容。

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) 如果商品筆數等於需要的包裝箱/袋數，則抓取下一個商品名稱，否則保持原本商品名稱)。

點取I2儲存格，輸入公式：「=IFERROR(VLOOKUP(H2,$A$1:$E$5,2,0),"")」，再將公式複製到I3:I15。

點取J2儲存格，輸入公式：「=IFERROR(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)))),””)」，再將公式複製到J3:J15。。