2009年8月24日 星期一

Excel-找出一欄中最後一個數值

如果想要抓取某一欄位(例如A欄)中的最後一個數值,可以使用以下的公式:

=LOOKUP(9.99999999999999E+307,A:A)

=LOOKUP(9.9E+307,A:A)

公式的意思是要在A欄中找尋Excel可容許的最大正數(9.99999999999999E+307)。

因為LOOKUP函數是以二分搜尋法方式來找尋資料,例如:

=LOOKUP(10,{1,2,3,4,5,6,7,8,9})

先找到中間值5,判斷後繼續在{6,7,8,9}找尋,

先找到中間值8,判斷後繼續在{9}中找尋,

最後找到最接近的值為9。(注意該陣列已經過排序)

而LOOKUP(9.9E+307,A:A)則是要在A欄中找尋一個找不到的值,不管其數列是否已經過排序,除非真的有一個9.99999999999999E+307的數,否則應傳回一個最接近的數,即是該欄的最後一個數。

而改成LOOKUP(MAX(A:A)+1,A:A),應該也是可以得到相同結果。

---------------------------------------------------------------------------------------

LOOKUP函數有兩種語法形式:向量形式與陣列形式。

  • 向量形式:在單列或單欄範圍 (亦稱為向量) 中搜尋值,並從第二個單列或單欄範圍內的相同位置中傳回值,當有大量值的清單要查詢或當值在不同時間會產生變更時,使用向量形式。
  • 陣列形式:在陣列的第一列或第一欄中搜尋指定的值,然後從陣列最後一列或最後一欄的相同位置中傳回值,當有少量值的清單而且在不同時間值都保持不變時,使用陣列形式。

其中:陣列形式的 LOOKUP 會在陣列的第一列或第一欄中搜尋指定的值,然後從陣列最後一列或最後一欄的相同位置中傳回值。若要比對的值位於陣列第一列或第一欄,請使用這種形式的 LOOKUP 函數。若要指定欄或列的位置時,請使用其他形式的 LOOKUP。

語法
LOOKUP(lookup_value, array)LOOKUP 函數的陣列形式語法具有下列引數

  • lookup_value:LOOKUP 函數在陣列中搜尋的值。
    lookup_value 引數可為數字、文字、邏輯值,或是指向某值的名稱或參照位址。
    如果 LOOKUP 函數找不到 lookup_value,就會使用陣列中小於或等於 lookup_value 的最大值。
    如果 lookup_value 小於第一列或第一欄 (視陣列的尺寸而定) 中的最小值,則 LOOKUP 函數會傳回 #N/A 錯誤值。
  • 陣列:此引數包含文字、數字,或要與 lookup_value 比較的邏輯值之儲存格範圍。 

陣列中的值必須以遞增順序排列: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;否則,LOOKUP 函數可能不會傳回正確的值。

---------------------------------------------------------------------------------------

關於Excel的規格,請參考資料來源:(摘要如下)

http://office.microsoft.com/zh-tw/excel/HP100738491028.aspx

數字精確度:15 位數

最小的可容許負數:-2.2251E-308

最小的可容許正數:2.2251E-308

最大的可容許正數:9.99999999999999E+307

最大的可容許負數:-9.99999999999999E+307

經由公式取得的最大可容許正數:1.7976931348623158e+308

經由公式取得的最大可容許負數:-1.7976931348623158e+308

公式內容的長度:8,192 個字元

公式的內部長度:16,384 個位元組

反複運算:32,767

工作表陣列:受限於可用的記憶體

選取範圍:2,048

在函數中的引數:255

函數的巢狀階層:64

使用者定義的函數類別:255

工作表函數的可用數目:341

運算元堆疊的大小:1,024

跨工作表的相依性:可以有 64,000 張工作表參照其他工作表

跨工作表的陣列公式相依性:受限於可用的記憶體

區域相依性:受限於可用的記憶體

每一張工作表的區域相依性:受限於可用的記憶體

與單一儲存格的相依性:可以有 40 億個公式相依於單一儲存格

從關閉之活頁簿連結的儲存格內容長度:32,767

計算可容許的最早日期:January 1, 1900 (如果使用 1904 的日期系統的話,則為 January 1, 1904)

計算可容許的最晚日期:December 31, 9999

可以輸入的最大時間值:9999:59:59

沒有留言:

張貼留言

好康東東