2010年5月13日 星期四

Excel-使用控制項的選項按鈕來查詢資料

接續上一篇文章,這次要來使用控制項的選項按鈕來查詢資料。

Excel-查表法的應用

http://isvincent.blogspot.com/2010/05/excel_12.html

Excel-使用表單的微調按鈕來查表

http://isvincent.blogspot.com/2010/05/excel_7085.html

先為表格增加一欄輔助欄位,內容為1,2,3…。

現在我們要透過[選項按鈕]控制項,點選學校名稱,即可列出該校所有的資料。

首先,在表單控制項中插入[選項按鈕],本例要新增四個,並輸入如下圖四個學校的名稱。

第一個選項控制項設定為[核取],並連結至儲存格$H$1。其他三個設定為[不核取],全都連結至儲存格$H$1。

當某個選項被選時,會在儲存格H1顯示第幾個被選到,例如:1代表輔仁大學,4代表銘傳大學。

現在要將這個數字轉換成學校名稱,在儲存格H2輸入公式:

儲存格H2:=CHOOSE(H1,"輔仁大學","中原大學","世新大學","銘傳大學")

接著輸入陣列公式:

儲存格H4:{=SMALL(IF($E$2:$E$124=$H$2,$A$2:$A$124),ROW(1:1))}

其中儲存格出現#NUM!錯誤,是因為沒有查到資料。

這個公式的動作原理是找出E欄中和儲存格H2相同的輔助欄位的號碼,顯示在H欄中。再利用SMALL函數,依序的逐列顯示第1個、第2個、第3個…,其中ROW(1:1)在往下複製時會自動調整為ROW(2:2)、ROW(3:3)…。

在儲存格I4中輸入根據H欄的輔助號碼來查詢編號、姓名、招生類別各欄。

儲存格I4:=IF(ISERROR(VLOOKUP($H4,$A$1:$F$124,COLUMN(B:B))),"",VLOOKUP($H4,$A$1:$F$124,COLUMN(B:B)))

將儲存格I4,複製到I4:K24。

並且利用ISEROR函數,將發生錯誤的公式結果不顯示出來。

如果你不想使用輔助欄位(H欄),則可以在儲存格I4輸入以下的公式:

{=IF(ISERROR(VLOOKUP(SMALL(IF($E$2:$E$124=CHOOSE($H$1,"輔仁大學","中原大學","世新大學","銘傳大學"),$A$2:$A$124),ROW(1:1)),$A$1:$F$124,COLUMN(B:B))),"",VLOOKUP(SMALL(IF($E$2:$E$124=CHOOSE($H$1,"輔仁大學","中原大學","世新大學","銘傳大學"),$A$2:$A$124),ROW(1:1)),$A$1:$F$124,COLUMN(B:B)))}

並複製到所有的儲存格即可。

1 則留言:

  1. 請問下我看了你的教學
    但是我OFFICE 2003好像找不核取這個選項
    不知道能否請您指點指點

    回覆刪除

檢視其他文章

好康東東