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

image

 

【公式設計與解析】

分析上圖,甲、乙、丙有 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 函數,求得對應儲存格的內容,即為所求。

image

同理:

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

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

 

【補充說明】

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

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

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(6) 人氣()