✔ 最佳答案
B1 儲存格輸入 =A1
B2 輸入 =MAX(OFFSET($A$1,0.5*ROW(A2)*(ROW(A2)-1),0,ROW(A2),1))
再下拉到 B45
---------------------------------------------------------------
說明如下, 先說明數學計算的部份 :
B欄 各儲存格的計算範圍, 可用集合表示,
B1 = { A1 }
B2 = { A2 , A3 }
B3 = { A4 , A5 , A6 }
B4 = { A7 , A8 , A9 , A10 }
.........................
Bn = { Ai , ...... , Aj } , 有 n 個元素
Bn 的起始元素為 Ai , 欲算出 i 與 n 的關係.
以 B4 為例, A7 的足碼 7 的計算式為 :
( 1 + 2 + 3 ) + 1 = 7
因此, 可推導出 Ai 的足碼 i 的計算式為 :
i
= [ 1 + 2 + 3 + ..... + (n-1) ] + 1
= (1/2)(n-1)[ 1 + (n-1) ] + 1
= 1 + 0.5*n*( n - 1 )
其次, 計算 999 個數據資料, 需要多少個 B欄 的儲存格.
1 + 2 + 3 + ...... + n ≧ 999
(1/2)n(n+1) ≧ 999
n^2 + n - 1998 ≧ 0
令 f(n) = n^2 + n - 1998 ,
當 f(n) = 0 , n^2 + n - 1998 = 0
n
= [ - 1 ± √( 1 + 4*1998 ) ] / 2
= - 0.5 ± 0.5*√7993 , 負不合
= - 0.5 + 0.5*89.4
= 44.2
f(44) = 44^2 + 44 - 1998 = - 18
f(45) = 45^2 + 45 - 1998 = 45 ≧ 0
因此, 需要 45 個B欄的儲存格.
---------------------------------------------------------------
將以上計算結果轉為 Excel 函數式如下 :
函數式是在 B2 儲存格開始輸入的, 此時, n = ROW(A2) = 2
因為 i = 1 + 0.5*n*( n - 1 ) , 又 A1 儲存格本身佔據1格 ,
所以 i 可用 $A$1 再往下位移 0.5*ROW(A2)*(ROW(A2)-1) 列來表示.
Bn , 有 n 個元素 , 即 ROW(A2) 個元素.
所以 B2 應該輸入 :
=MAX(OFFSET($A$1,0.5*ROW(A2)*(ROW(A2)-1),0,ROW(A2),1))
各參數的意義為 :
起點 = $A$1 , 用絕對參照來固定
往下位移 = 0.5*ROW(A2)*(ROW(A2)-1) 列
往右位移 = 0 欄
矩陣高度 = ROW(A2)
矩陣寬度 = 1
---------------------------------------------------------------
可用以下簡易的方法, 測試函數式是否正確 :
A1 ~ A999 產生數列 1, 2, 3, ...... , 999
按照上數方法建立函數式, 則 B 欄會計算出
1, 3, 6, 10, 15, ...... , 999
B1 = 1 = max { 1 }
B2 = 3 = max { 2, 3 }
B3 = 6 = max { 4, 5, 6 }
B4 = 10 = max { 7, 8, 9, 10 }
因此, 函數式正確.