2010年5月29日 星期六

Excel-查表以計算總和

在Excel中如果要在以下的表格中,查詢每一列中由1月累計至某一月的總和,該如何設計這個查表工具呢?試著結合利用表單工具的微調按鈕來操作。

由[開發人員]功能表中插入表單控制項-微調按鈕,設定:

(1)目前值:1,最小值:1,最大值10,遞增值:1,儲存格連結:$Q$1。

(2)目前值:1,最小值:1,最大值12,遞增值:1,儲存格連結:$N$2。

image03 image04

輸入公式,儲存格P1:=OFFSET(A1:A11,Q1,0,1,1),使用微調按鈕控制P1顯示甲、乙、丙…

輸入公式,儲存格O2:=OFFSET(A1:M1,0,N2,1,1),使用微調按鈕控制O2顯示1月、2月、3月…

OFFSET():傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:必要項目。計算位移的起始參照位址。

Rows:必要項目。左上角儲存格要往上或往下參照的列數。Rows 可以是正數或負數。

Cols:必要項目。左上角儲存格要往左或往右參照的欄數。Cols 可以是正數或負數。

Height:選用項目。這是所傳回參照位址的高度 (以列數為單位)。Height 必須是正數。

Width:選用項目。這是所傳回參照位址的寬度 (以欄數為單位)。Width 必須是正數。

 

計算累計總和的公式為:

儲存格P2:=SUM(OFFSET($B$1,MATCH(P1,$A$2:$A$11,0),0,1,MATCH(O2,$B$1:$M$1,0)))

其中:

MATCH(P1,$A$2:$A$11,0)為查詢儲存格P1所指的名稱在陣列中的第幾個(表示第幾列)。

MATCH(O2,$B$1:$M$1,0)為查詢儲存格O2所指的名稱在陣列中的第幾個(表示第幾欄)。

MATCH():會搜尋某儲存格範圍內的指定項目,傳回該項目在範圍內的相對位置。

(提示:當需要取得符合搜尋條件的元素之相對位置而非元素本身時,應該使用 MATCH 函數,而不是 LOOKUP 函數)

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:必要項目。在 lookup_array 中尋找比對的值。

lookup_array:必要項。要搜尋儲存格範圍。

match_type:選用項目。有三種可能:-1、0 或 1。

Match_type

行為

1 或省略

MATCH 函數會找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列,例如:...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。

0

MATCH 函數會找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1

MATCH 函數會找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序,例如:TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...,以此類推。

 

以本例來看:(儲存格P1:癸,儲存格O2:12月)

SUM(OFFSET($B$1,MATCH(P1,$A$2:$A$11,0),0,1,MATCH(O2,$B$1:$M$1,0)))

=SUM(OFFSET($B$1,10,0,1,12))

=SUM($B$11:$M$11)

以上做法可以查詢某每一列中由1月累計至指定月份的總和,並且可以使用表單工具的微調按鈕來調整想要查詢的結果。

沒有留言:

張貼留言

檢視其他文章

好康東東