常見到一些類似下圖梯形票價表的表格,利用某一站到另一站的票價能夠以查詢方式來查票價,該如何使用 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,應該得到相同結果。
如果想要了解詳細公式的執行過程,可以由[評估值公式]對話框來來檢視:(選取[公式/評估值公式])
只要按下[評估值]按鈕或是[逐步執行]按鈕,可以一步一步執行片段的程式。
詳細函數說明請參閱微軟網站:
INDIRECT:http://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:相對參照 |