如何使用Excel控制項

如果你希望做出一份優秀的Excel,「控制項」是一個必學的項目。最常見到的控制項像是下拉式選單、點選按鈕,無須學過困難的巨集寫法,就可以寫出具有互動功能的Excel試算表。

以下使用的是輔仁大學經濟系的曹維光老師所提供的範例,檔名為Excel-1.xls的練習檔案。分頁當中可以看到房屋貸款房屋貸款練習的兩個分頁,我就先介紹房屋貸款的設計理念,然後我們再來動手作作看。

房屋貸款分區解釋

綠色區域是整個資料的來源,將會被引用到控制項練習(紅色區)與VLOOKUP練習(黃色區)。至於藍色區,由控制項設定時產生,可以作為運算或是索引資料時的利用;這部份是給Excel文件設計者使用,對於Excel使用者來說沒有什麼意義。




在做各個儲存格的公式介紹前,要先說一下。曹老師已經完成儲存格命名,如B6儲存格的名稱預設就是「B6」,但是為了提醒大家那一個儲存格在公式中的運算意義,因此命名為「購屋自備款」。這一點雖然很麻煩,但未來在修改這份文件時會比較輕鬆,知道這個儲存格的意義。


我們來看一下控制項(紅色區的運算流程)

  1. B2:用下拉式方塊來選擇地段名稱,資料的行數放在「藍色區」的「地段名稱序號」
  2. B3:用INDEX或VLOOKUP從資料當中挑出「地段名稱序號」對應的「每坪價格」
  3. B4:用微調按鈕來調整購買坪數
  4. B5:將B3×B4得到「房屋總價款」
  5. B6:這裡老師似乎打錯了,自付款應該為「總價款×C6/100」但老師打成「×C6/10」
    C6:用微調按鈕來調整自付款的比例
  6. B7:銀行貸款就是B5與B6的差額
  7. B8:用微調按鈕將數值傳到藍色區的H3,再將「H3/10000」作成貸款利率
  8. B9:用微調按鈕將數值傳到藍色區的H12,將「H12×24」,即可將月份改為年
  9. B10:用微調按鈕將數值傳到藍色區的H9,將「H9×」10000,得到薪水
  10. B11:用PMT函數算出每月分期付款的數值,數值為負的(費用)
  11. B12:將薪水加上分期付款,就可以得到每月的可支配所得。

從整個設計當中,我們可以察覺所謂的控制項,重點就在表單的運用。因此只要知道怎麼用表單就OK了。


下拉式方塊

下拉式方塊

拖曳出下拉式方塊後,按右鍵→控制項格式,可以在當中設定資料的來源(某一行)。在選取資料範圍時,可以讓你顯示資料所在行數(稱為序號),這可以幫助你在之後的運算,因此設定這個數值出現在那一格。這樣就是下拉式方塊。



微調按鈕

微調按鈕

同樣用右鍵可以修改微調按鈕的功能,要設定的只有四個地方:最小值、最大值、遞增值,與最後數值出現的地方。

重點在於,微調按鈕只能輸入整數值。如果你希望讓使用者可以選擇小數值,就要麻煩一點。

例如:如果你今天希望出現的數值為9.25%,要先將微調按鈕輸出的值放在C1,這時C1=925,但這不是你要的。因此你要在另一格B1輸入公式(B1=C1/10000),這樣才能讓微調按鈕的數值變成小數位數。

留言

熱門文章