贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到 Excel 的問題:在一個含有類別和數值的清單中(如下圖),如何列出各個類別中最後一個數值?

在下圖中共有「甲、乙、丙、丁、戊、己」共六個類別,如何找出對應類別的最後一個?

Excel-找出數列中每類別的最後一個項目(OFFSET,ROW,陣列公式)

【公式設計與解析】

選取儲存格A1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、數值。

儲存格E2:{=OFFSET($B$1,MAX(IF((類別=D2),ROW(類別),FALSE))-1,0)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter  鍵,Excel 會自動加上「{}」。

(1) IF((類別=D2),ROW(類別),FALSE)

在類別陣列中找到和儲存格D2相同者,傳回其列號(ROW函數用以顯示儲存格列號),否則傳回 FALSE。

(2) MAX(IF((類別=D2),ROW(類別),FALSE))

根據公式(1),傳回列號的最大值(MAX函數),即為各類別的最後一個。

(3) OFFSET($B$1,MAX(IF((類別=D2),ROW(類別),FALSE))-1,0)

將第(2)式傳回的列號,帶入 OFFSET 函數取得對應的儲存格位址內容。


如果你的每個類別資料不是連續排列,原先的公式仍是適用的。

Excel-找出數列中每類別的最後一個項目(OFFSET,ROW,陣列公式)

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • MAX
  • 請問不是找出數列中每類別的最後一個項目,而是要找出倒數第二個項目,要怎麼修改呢?
    EX:
    甲:15
    甲:16
    甲:17
    取出為甲:16

    還麻煩解答了,謝謝。
  • 原公式:
    {=OFFSET($B$1,MAX(IF((類別=D2),ROW(類別),FALSE))-1,0)}
    改為:
    {=OFFSET($B$1,LARGE(IF((類別=D2),ROW(類別),FALSE),2)-1,0)}

    vincent 於 2017/10/22 23:19 回覆

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼