4034Excel如何將水果依數量分解開來 @ 錦子老師

4034

Excel如何將水果依數量分解開來

如果要將A欄中的水果依照B欄的數量分解到D欄及E欄，要怎設定公式呢?

1.點取D2儲存格，輸入公式「=IFERROR(IF(D1="水果",OFFSET($A$1,MATCH(D1,$A$1:$A$5,0),0,1, 1),IF(SUMIF($D$1:D1,D1,$E$1:E1)=VLOOKUP(D1,$A$2:$B$5,2,0),IF(OFFSET($A$1,MATCH(D1,$A$1:$A$5,0),0,1,1)=0,"",OFFSET($A$1,MATCH(D1,$A$1:$A$5,0),0,1,1)),D1)),"")」後，向下複製公式到D3:D17儲存格。

公式說明：

=IFERROR(IF(D1="水果",OFFSET($A$1,MATCH(D1,$A$1:$A$5,0),0,1,1),IF(SUMIF($D$1:D1,D1, $E$1:E1)=VLOOKUP(D1,$A$2:$B$5,2,0),IF(OFFSET($A$1,MATCH(D1,$A$1:$A$5,0),0,1,1)=0,"",OFFSET($A$1,MATCH(D1,$A$1:$A$5,0),0,1,1)),D1)),"")

公式一：MATCH(D1,$A$1:$A$5,0) 傳回D1儲存格內容在A1:A5中的位置。

公式二：OFFSET($A$1,公式一,0,1,1) 傳回從A1儲存格移動N列(公式一傳回結果)，0欄後的儲存格內容。

公式三：VLOOKUP(D1,$A$2:$B$5,2,0) 傳回D1儲存格內容在A2:A5中的位置同列B欄儲存格內容。

公式四：SUMIF($D$1:D1,D1,$E$1:E1) 統計D1到目前儲存格中含有D1儲存格內容的同列E欄數值總和。

公式五：IF(公式二=0,"",公式二) 如果公式二傳回結果為0(表示水果資料結果)，則不填入資料，否則填入公式二傳回結果。

公式六：IF(公式四=公式三,公式五,D1) 如果公式四傳回結果與公式三傳回結果相等，則填入公式五傳回結果，否則填入上一儲存格內容。