學不完．教不停．用不盡

網友想要將 Excel 工作表中的資料表(如下圖左)取出資料重組成下圖右的樣子，該如何處理？即原本甲、乙、丙分散在多欄，現在要將甲、乙、丙分別組合成一欄，該如何設計公式？

 

【公式設計與解析】

分析上圖，甲、乙、丙有 3 個類別，每個類別有 5 個項目。

儲存格M2：=OFFSET($A$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)

上式中的參數『3』即為 3 個類別，參數『5』即為 5 個項目。

複製儲存格M2，往下各列貼上。

ROW(1:1)：當公式向下複製時 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

MOD(ROW(1:1)-1,5)+1：當公式向下複製時產生『1,2,3,4,5,1,2,4,5,1,2,3,4,5』。

INT((ROW(1:1)-1)/5)*3：當公式向下複製時產生『0,0,0,0,0,3,3,3,3,3,6,6,6,6,6』。

將以上二式代入 OFFSET 函數，求得對應儲存格的內容，即為所求。

同理：

儲存格Ｎ2：=OFFSET($B$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)

儲存格Ｏ2：=OFFSET($C$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)

 

【補充說明】

INT函數：將函數參數取不大於(大於或小於)的最大整數。

MOD函數：求得兩數相除的餘數。