2015年11月12日 星期四

Excel-資料重組依原順序呈現(SUMPRODUCT,OFFSET,ROW,COUNTIF)

在一個 Excel 中有一個資料表(如下圖左),如果想要在另一個資料表依編號重組資料,而且依原順序呈現,但是因為編號會重覆,所以要如何能依原順序列出資料呢?(參考下圖右)
例如資料清單中編號1者(位於儲存格A2,A3,A7,A10,A13,...),當在重組資料時是置於儲存格G2,G5,G9,G12,G15,...。如下圖中的箭號指示,呈現時必須依原來的順序出現,該如何處理?
Excel-資料重組依原順序呈現(SUMPRODUCT,OFFSET,ROW,COUNTIF)

【公式設計與解析】
為說明方便,先選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。
重組資料時(參考上圖右),『編號』欄位的內容是自行輸入的。
儲存格H2:=OFFSET($B$1,SUMPRODUCT(SMALL((編號=$G2)*ROW(編號),
SUM(--(((編號=G2)*ROW(編號))=0))+COUNTIF($G$2:G2,G2)))-1,,,)
(編號=$G2)*ROW(編號):在 SUMPRODUCT 函數中傳回編號和儲存格G2相同者的『列號』,若是不相同者,傳回 0。本例傳回:2,3,0,0,0,7,0,0,10, ...。
SUM(--(((編號=G2)*ROW(編號))=0)):計算上式傳回 0 者的個數,本例傳回 15。
COUNTIF($G$2:G2,G2):計算由儲存格G2起始至目前儲存格範圍中,和儲存格G2相同者的個數,本例傳回 1。
SMALL((編號=$G2)*ROW(編號),SUM(--(((編號=G2)*ROW(編號))=0))+COUNTIF($G$2:G2,G2)):將以上三式代入 SMALL 函數,可以求得符合條件的最小列號,本例傳回 1。
將上式的傳回值代入 OFFSET 函數,得到:OFFSET($B$1,傳回『列號』-1,,,)。
同理:
儲存格I2:=OFFSET($C$1,SUMPRODUCT(SMALL((編號=$G2)*ROW(編號),
SUM(--(((編號=G2)*ROW(編號))=0))+COUNTIF($G$2:G2,G2)))-1,,,)
存格J2:=OFFSET($D$1,SUMPRODUCT(SMALL((編號=$G2)*ROW(編號),
SUM(--(((編號=G2)*ROW(編號))=0))+COUNTIF($G$2:G2,G2)))-1,,,)
儲存格K2:=OFFSET($E$1,SUMPRODUCT(SMALL((編號=$G2)*ROW(編號),
SUM(--(((編號=G2)*ROW(編號))=0))+COUNTIF($G$2:G2,G2)))-1,,,)

沒有留言:

張貼留言

檢視其他文章

好康東東