將發票模板設計成只允許使用者輸入必要欄位，其餘公式自動計算並鎖定，練習資料驗證、查找公式、總額計算與工作表保護。
Star Track Corporation Ltd 使用固定發票模板。使用者只應選擇客戶、產品並輸入數量，其餘資料由公式取得；為避免誤刪公式，完成後須啟用工作表與活頁簿保護。
| Customer Name | Address |
|---|---|
| ABC Retail | 12 Market Road, Taipei |
| Blue Star Co. | 8 Harbor Street, Kaohsiung |
| Central Office | 66 Finance Ave., Taichung |
| Delta Trading | 23 Export Lane, Tainan |
| Product | Rate |
|---|---|
| Laptop Stand | 850 |
| Wireless Mouse | 520 |
| USB-C Hub | 1,200 |
| Keyboard | 980 |
| Monitor | 5,200 |
| Sr.No | Product | Qty |
|---|---|---|
| 1 | Laptop Stand | 3 |
| 2 | Wireless Mouse | 5 |
| 3 | USB-C Hub | 2 |
| 4 | Keyboard | 4 |
| 5 | Monitor | 2 |
| 任務 | 公式／設定 | 用途 |
|---|---|---|
| Customer 下拉 | Data Validation → List | 避免客戶名稱輸入錯誤 |
| Address | =IFERROR(VLOOKUP(B11,$A$4:$B$7,2,FALSE),"") |
依客戶自動帶出地址 |
| Product 下拉 | Data Validation → List | 限制只能選產品主檔 |
| Rate | =IFERROR(VLOOKUP(B15,$D$4:$E$8,2,FALSE),"") |
依產品回傳單價 |
| Amount | =IF(B15="","",C15*D15) |
Qty × Rate |
| Gross Amount | =SUM(E15:E22) |
加總所有明細 |
| VAT | =Gross Amount*14% |
計算 14% VAT |
| Discount | =IF(Gross Amount>15000,Gross Amount*10%,0) |
超過 15,000 給 10% 折扣 |
| Net Amount | =Gross Amount+VAT-Discount |
計算應收淨額 |
| Product | Qty | Rate | Amount |
|---|---|---|---|
| Laptop Stand | 3 | 850 | 2,550 |
| Wireless Mouse | 5 | 520 | 2,600 |
| USB-C Hub | 2 | 1,200 | 2,400 |
| Keyboard | 4 | 980 | 3,920 |
| Monitor | 2 | 5,200 | 10,400 |
| 指標 | 結果 |
|---|---|
| Gross Amount | 21,870 |
| 增值稅（Value Added Tax, VAT）14% | 3,061.80 |
| Discount 10% | 2,187 |
| Net Amount | 22,744.80 |