2011年9月19日 星期一

Excel-製作梯形票價表及票價查詢

常見到一些類似下圖梯形票價表的表格,利用某一站到另一站的票價能夠以查詢方式來查票價,該如何使用 Excel 來建立?

首先,根據相鄰間隔站之間的票價,來建立不同站之間的所有可能票價。

(1) 計算票價

儲存格A3:=INDIRECT(ADDRESS(ROW(1:1)+1,COLUMN(A:A),4))+INDIRECT(ADDRESS(ROW(3:3),ROW(1:1)+1,4))

複製儲存格A3,分別貼在每一個票價空格上。

ADDRESS(ROW(1:1)+1,COLUMN(A:A),4):找到儲存格所在欄的票價位址(在該儲存格上一列的儲存格)

ADDRESS(ROW(3:3),ROW(1:1)+1,4):找到儲存格所在列的票價位址(在該儲存格的最右第2個儲存格)

將以上兩個位址透過 INDIRECT 函數,將儲存格位址轉換為儲存格內容。

再將兩個儲存格內容相將即為該儲存格的票價。

(2) 查詢票價

{=INDIRECT(ADDRESS(MAX(IF((A1:J11=A14)+(A1:J11=B14),ROW(A1:J11))),MIN(IF((A1:J11=A14)+(A1:J11=B14),ROW(A1:J11)))))}

這是陣列公式,輸入完成應按 Ctrl+Shift+Enter 鍵。

查詢票價時,起站位於儲存格A14,終站位於儲存格B14。因為查詢時,起站和終站可能為起站:CCC/終站HHH,或是起站:HHH/終站CCC,應該得到相同結果。

如果想要了解詳細公式的執行過程,可以由[評估值公式]對話框來來檢視:(選取[公式/評估值公式])

只要按下[評估值]按鈕或是[逐步執行]按鈕,可以一步一步執行片段的程式。

 

詳細函數說明請參閱微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

ADDRESS:http://office.microsoft.com/zh-tw/excel-help/HP010342163.aspx

在已知指定列和欄號下,取得工作表中儲存格的位址。

語法:ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num:指定要用在儲存格參照中之列號的數值。

column_num:指定要用在儲存格參照中之欄號的數值。

abs_num:可省略。指定要傳回之參照類型的數值。傳回此參照類型如下:

1或省略:絕對儲存格參照;2:列:絕對;欄:相對;3:列:相對;欄:絕對;4:相對參照

沒有留言:

張貼留言

好康東東