Excel 用 LOOKUP 抓最後一個有效數字(排除 0 和空白)

原始問題

問題情境

在 A 欄有一串數字資料,例如:

A欄數列
510
830
72
0
(空白)
(空白)

我們的目標是:「抓出最後一個有效數字」,

  • 不要抓到 0。
  • 不要抓到空白。

也就是,在這個例子裡,我們想要拿到 72,而不是下面的 0 或空白。


解法公式

這邊使用經典的 LOOKUP 技巧:

=LOOKUP(2, 1/((A2:A200<>0)*(A2:A200<>"")), A2:A200)

公式拆解說明

逐步說明這條公式的運作邏輯:

  1. (A2:A200<>0)

    • 判斷每格是不是「不是 0」。
    • 是的話 TRUE(1),否的話 FALSE(0)。
  2. (A2:A200<>"")

    • 判斷每格是不是「不是空白」。
    • 同樣 TRUE(1)或 FALSE(0)。
  3. (A2:A200<>0)*(A2:A200<>"")

    • 兩個條件相乘。
    • 只有「不是 0 且不是空白」的格子,結果是 1,其他是 0。
  4. 1/((A2:A200<>0)*(A2:A200<>""))

    • 將剛剛的結果取倒數。
    • 符合條件的位置是 1。
    • 不符合條件的位置是 #DIV/0!(除以 0 的錯誤)。
  5. LOOKUP(2, ...)

    • LOOKUP 會試著找目標值 2,但陣列裡只會有 1(符合條件)或錯誤。
    • 找不到 2,LOOKUP 自動回傳「小於 2 的最後一個有效資料」,也就是最後一個 1 對應的 A 欄數字。

為什麼這樣可以做到?

  • LOOKUP 的特性是:

    • 遇到錯誤值(例如除以 0)會自動忽略。
    • 會找到「小於搜尋值」的最後一筆有效資料
  • 因此即使資料順序亂掉,只要符合條件的地方有 1,不符合的地方變錯誤,就能正確抓到最後一個有效數字。


實測範例

假設 A2:A7 是這樣:

A
510
830
72
0
 
 

結果:

  • 抓到的是 72
  • 而不是 0,也不是空白。

小提醒

  • 資料不需要升序排列,這種寫法可以隨便亂排沒關係。
  • 如果範圍內有其他錯誤值(例如 #N/A),要小心,可能會干擾。
  • 適合 Excel 2010 以後的版本,一直到新版都通用。

 

小泉

喜愛用文字說明自己眼中所見的一切

較新的 較舊

نموذج الاتصال