
原始問題
問題情境
在 A 欄有一串數字資料,例如:
A欄數列
510
830
72
0
(空白)
(空白)
我們的目標是:「抓出最後一個有效數字」,
- 不要抓到 0。
- 不要抓到空白。
也就是,在這個例子裡,我們想要拿到 72
,而不是下面的 0 或空白。
解法公式
這邊使用經典的 LOOKUP 技巧:
=LOOKUP(2, 1/((A2:A200<>0)*(A2:A200<>"")), A2:A200)
公式拆解說明
逐步說明這條公式的運作邏輯:
(A2:A200<>0)
- 判斷每格是不是「不是 0」。
- 是的話 TRUE(1),否的話 FALSE(0)。
(A2:A200<>"")
- 判斷每格是不是「不是空白」。
- 同樣 TRUE(1)或 FALSE(0)。
(A2:A200<>0)*(A2:A200<>"")
- 兩個條件相乘。
- 只有「不是 0 且不是空白」的格子,結果是 1,其他是 0。
1/((A2:A200<>0)*(A2:A200<>""))
- 將剛剛的結果取倒數。
- 符合條件的位置是 1。
- 不符合條件的位置是 #DIV/0!(除以 0 的錯誤)。
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 以後的版本,一直到新版都通用。
Tags
電腦工具