2015年10月15日 星期四

Excel-資料重排仍依原順序呈現(SMLL,ROW,COUNTIF,OFFSET,陣列公式)

有網友問到:在 Excel 中有一個資料表(如下圖左),其中的『資料』欄位如果順序被打亂,如何讓『數值』欄位正確的對應(如下圖右)?
觀察下圖中的BBB資料順序雖改變,但是對應的數值,其出現順序仍依原順序呈現。
Excel-資料重排仍依順序呈現(SMLL,ROW,COUNTIF,OFFSET,陣列公式)

【公式設計與解析】
本例為說明和觀察時的方便性,特別使用「輔助」欄位。
儲存格F2:
{=SMALL(IF($A$2:A19=D2,ROW($A$2:A19),FALSE),COUNTIF($D$2:D2,D2))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動加上「{}」。
(第1式) IF($A$2:A19=D2,ROW($A$2:A19),FALSE):比對第一個儲存格A2開始的儲存格陣列,如果與儲存格D2相同者,傳回其列號(ROW函數傳回的數值),否則傳回FALSE
(第2式) COUNTIF($D$2:D2,D2):計算第一個儲存格D2開始的儲存格陣列中和儲存格D2相同者有幾個。
SMALL(第1式,第2式):在陣列公式中,經由第1式傳回的「列號」陣列,找出第幾個較小的列號(例如:儲存格D3的AAA是第2個)。
儲存格E2:=OFFSET($B$1,F2-1,,,)
透過 OFFSET 函數依相對位置取得儲存格內容。
複製儲存格F2:E2,貼至儲存格E2:E19。

註:完整的E欄公式
儲存格E2:{=OFFSET($B$1,SMALL(IF($A$2:A19=D2,ROW($A$2:A19),FALSE),
COUNTIF($D$2:D2,D2))-1,,,)}

沒有留言:

張貼留言

好康東東