學不完．教不停．用不盡

在一個 Excel 中有一個資料表(如下圖左)，如果想要在另一個資料表依編號重組資料，而且依原順序呈現，但是因為編號會重覆，所以要如何能依原順序列出資料呢？(參考下圖右)

例如資料清單中編號1者(位於儲存格A2,A3,A7,A10,A13,...)，當在重組資料時是置於儲存格G2,G5,G9,G12,G15,...。如下圖中的箭號指示，呈現時必須依原來的順序出現，該如何處理？

【公式設計與解析】

為說明方便，先選取儲存格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,,,)