錦子老師

小婷婷：「錦子老師，想請教如何將A欄的水果依數量拆解成D欄與E欄這樣，這該如何寫公式呢？」

▍函數公式

步驟1.點取D2儲存格輸入公式：「=A2」。

步驟2.點取D3儲存格輸入公式：「=IF(ROW()<=SUM($B$2:$B$5),IF(COUNTIF($D$1:D2,D2)=VLOOKUP(D2,$A$1:$B$5,2,0),OFFSET($A$1,MATCH (D2,$A$1:$A$5,0),0,1,1),D2),"")」後，向下複製公式到D4:D15儲存格。

▍公式說明

=IF(ROW()<=SUM($B$2:$B$5),IF(COUNTIF($D$1:D2,D2)=VLOOKUP(D2,$A$1:$B$5,2,0),OFFSET($A$1,MATCH(D2,$A$1:$A$5,0),0,1,1),D2), "")

以D3儲存格為例：

如果目前列編號小於B欄水果數量總和(ROW()<=SUM($B$2:$B$5))，則判斷同一欄從第一列到上一列中含有上一儲存格內容是否等於同一水果B欄數量(COUNTIF($D$1:D2,D2)=VLOOKUP(D2, $A$1:$B$5,2,0))，若是則從A1儲存格移動上一儲存格(D2)在A1:A5儲存格範圍中的第幾列(MATCH(D2,$A$1:$A$5,0))的列數0欄，並傳回該儲存格內容(OFFSET($A$1,MATCH(D2, $A$1:$A$5,0),0,1,1))，否則傳回上一儲存格內容。若列號超過水果總和值則不填入資料。

步驟3.點取E2儲存格輸入公式：「=VLOOKUP(D2,$A$2:$B$5,2,0)」後，向下複製公式到E3:E15儲存格。