一、在Excel中批量插入核取方塊Check Box
應該很多人都不陌生
這裡先簡單介紹一下
開發人員>控制項>插入>核取方塊.
(如果沒有見到 [開發人員索引標籤] 的話，可以看這篇開啟，都是巨集的一些基本設置，值得一看的，這裡就不贅述了。)
對的，插入核取方塊就是如此簡單。
接下來就是讀取核取方塊的值，再用於公式上了。
二、儲存格值隨核取方塊聯動
Check Box ->右鍵>控制項格式>控制>儲存格連結
連結特定儲存格後，儲存格就會實時顯示核取方塊的邏輯值 (TRUE: 勾選時/ FALSE: 不勾選時)
當核取方塊的值能顯示在儲存格時，就能做很多操作了
例如:
根據核取方塊的值，顯示或隱藏值 [IF()]
IF早就介紹過了，這裡簡單貼一下公式， =if(判斷條件, 符合條件做這段, 不符合條件做這段)
=IF($F$3 (連結的儲存格) ,"Show","Hide")
結果:
勾選
不勾選
三、批量插入核取方塊 (拖動)
既然一個已經會做了，批量處理也不遠矣
首先來個最簡單直觀的做法: 直接拖動
即和一般的數字/ 公式一樣，控制項也可以通過拖動複製，前提是控制項是在儲存格內
如果只需要記錄，不需要聯動的話，拖動就完全足夠了
而如果要像(二)那樣聯動的話，這樣拖動就不行了
因為多個核取方塊也會互相聯動，即:
也可以再一個個連結至相應的儲存格
但批量的意義就不存在了
此時就可以出動VB了
如果覺得這樣已經滿意、而VB太複雜的話，可以止步於此了
不然的話，可以繼續向下看
四、批量插入核取方塊 (VB程式)
開發人員>程式碼>Visual Basic
雙擊工作表1 (現在正在用的工作表) 會出現以下介面:
(
直接複製以下程式其實就可以了 (記得把多餘字符刪了就可以了)
'批量添加核取方塊Check Box
Sub copyCheckbox()
Const column1 As String = "D" '核取方塊所在的欄
Const column2 As String = "F" '連結儲存格所在的欄
For i = 3 To 12 '需要顯示的列
ActiveSheet.CheckBoxes.Add(Range(column1 & i).Left + 8, Range(column1 & i).Top + 2, Range(column1 & i).Width - 10, Range(column1 & i).Height - 20).Select
'在D3插入核取方塊，如此類推，幾個Range分別是核取方塊的起始點(左起)、起始點(上起)、高度及寬度，可自行調整，為了讓核取方塊完全在儲存格內，建議設置得比儲存格小
With Selection
.Characters.Text = "" '核取方塊顯示文字為空
.LinkedCell = Range(column2 & i).Address '連結相應的儲存格
End With
Next
End Sub
效果:
五、批量插入核取方塊 (VB程式) 之實例--單位手續費+物業費計算 (哈哈，先欠著，有空再補，反正也沒有人催)
假設大富豪King在考慮同時租幾個單位
想知道每個單位的手續費和物業費
逐一計算也是可以的，就是太慢了
上效果
先貼一下源數據和結果
有興趣的可以自己先試做哦
原始資料:
A-K是單位名稱，大廈共三層，有幾個單位並沒有二樓。
其後資料分別是單位不同層數的實用面積及建築面積，分別以平方呎和平方米顯示。
| 實用面積 Saleable area | 建築面積 Gross Leasable Area | |||||||||||||||
| GF | 1F | 2F | All | GF | 1F | 2F | All | |||||||||
| Room No. | Area (sq. m)(GF)SA | Area (sq. ft)(GF)SA | Area (sq. m)(1F)SA | Area (sq. ft)(1F)SA | Area (sq. m)(2F)SA | Area (sq. ft)(2F)SA | Area (sq. m)(All)SA | Area (sq. ft)(All)SA | Area (sq. m)(GF)GLA | Area (sq. ft)(GF)GLA | Area (sq. m)(1F)GLA | Area (sq. ft)(1F)GLA | Area (sq. m)(2F)GLA | Area (sq. ft)(2F)GLA | Area (sq. m)(All)GLA | Area (sq. ft)(All)GLA |
| A | 28.6432 | 308.3125 | 43.8395 | 471.8847 | 50.5173 | 543.7638 | 123.0000 | 1323.9609 | 107.8910 | 1161.3290 | 165.1315 | 1777.4610 | 190.2849 | 2048.2099 | 463.3075 | 4987.0000 |
| B | 76.2832 | 821.1053 | 109.2236 | 1175.6735 | 270.4932 | 2911.5641 | 456.0000 | 4908.3430 | 134.1856 | 1444.3619 | 192.1294 | 2068.0636 | 475.8099 | 5121.5746 | 802.1249 | 8634.0000 |
| C | 333.5844 | 3590.6724 | 455.4156 | 4902.0526 | 0.0000 | 0.0000 | 789.0000 | 8492.7250 | 148.9846 | 1603.6573 | 203.3966 | 2189.3427 | 0.0000 | 0.0000 | 352.3812 | 3793.0000 |
| D | 1011.0000 | 10882.3130 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1011.0000 | 10882.3130 | 94.6682 | 1019.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 94.6682 | 1019.0000 |
| E | 617.3840 | 6645.4662 | 595.6160 | 6411.1566 | 0.0000 | 0.0000 | 1213.0000 | 13056.6228 | 81.8033 | 880.5230 | 78.9190 | 849.4770 | 0.0000 | 0.0000 | 160.7223 | 1730.0000 |
| F | 781.1983 | 8408.7487 | 633.8017 | 6822.1840 | 0.0000 | 0.0000 | 1415.0000 | 15230.9327 | 75.6531 | 814.3234 | 61.3789 | 660.6766 | 0.0000 | 0.0000 | 137.0320 | 1475.0000 |
| G | 808.6863 | 8704.6261 | 808.3137 | 8700.6164 | 0.0000 | 0.0000 | 1617.0000 | 17405.2425 | 65.7440 | 707.6630 | 65.7138 | 707.3370 | 0.0000 | 0.0000 | 131.4578 | 1415.0000 |
| H | 1092.1545 | 11755.8527 | 726.8455 | 7823.6995 | 0.0000 | 0.0000 | 1819.0000 | 19579.5523 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 |
| I | 1024.0282 | 11022.5471 | 996.9718 | 10731.3150 | 0.0000 | 0.0000 | 2021.0000 | 21753.8621 | 51.8749 | 558.3766 | 50.5043 | 543.6234 | 0.0000 | 0.0000 | 102.3792 | 1102.0000 |
| J | 1172.7895 | 12623.8008 | 949.2105 | 10217.2162 | 0.0000 | 0.0000 | 2122.0000 | 22841.0170 | 49.8054 | 536.1008 | 40.3106 | 433.8992 | 0.0000 | 0.0000 | 90.1160 | 970.0000 |
| K | 896.7423 | 9652.4538 | 744.2630 | 8011.1804 | 682.9946 | 7351.6926 | 2324.0000 | 25015.3268 | 77.6103 | 835.3903 | 64.4137 | 693.3431 | 59.1111 | 636.2665 | 201.1351 | 2165.0000 |
需要顯示的結果如下
輸入不同的單位和樓層會顯示相應的面積
勾選核取方塊與否會影響計算結果
之所以要加核取方塊是方便討論
有時客人會想知道多一個單位少一個單位價格會有什麼變化
這時候有核取方塊就直觀得多了
所以以上用到哪些功能了？
燈燈燈燈
就是:
現在梳理一下思路吧
首先客人會說出自己想租哪一個單位的哪一層
即已知C欄的Room No. 和D欄的Floor
如果輸錯信息或表達方式不對就出不到結果
這裡就用[資料驗證]、[下拉選單]來確認，避免被客人狂罵的命運
想得出I欄的Area
正常就是直接用[Vlookup]透過單位A獲取相應的面積
但因為租售時經常涉及到建築面積、實用面積、平方呎、平方米的問題
客人張口一句500平方米是多少呎啊，我習慣看平方米這樣又會是一場單位換算大戰了
所以索性在開始前讓客人先選個夠
直接顯示相應的結果就可以了
但一旦太多選項[Vlookup]這個小孩子就無法處理了
需要用到高年班的[Index-Match]
首先原資料的表頭是這樣的格式: Area (sq. m/ sq. ft) (All/ GF/ 1F /2F) SA/GLA
| Area (sq. m)(GF)SA |
| Area (sq. ft)(GF)SA |
| Area (sq. m)(1F)SA |
| Area (sq. ft)(1F)SA |
| Area (sq. m)(2F)SA |
| Area (sq. ft)(2F)SA |
| Area (sq. m)(All)SA |
| Area (sq. ft)(All)SA |
| Area (sq. m)(GF)GLA |
| Area (sq. ft)(GF)GLA |
| Area (sq. m)(1F)GLA |
| Area (sq. ft)(1F)GLA |
| Area (sq. m)(2F)GLA |
| Area (sq. ft)(2F)GLA |
| Area (sq. m)(All)GLA |
| Area (sq. ft)(All)GLA |
這裡我先用[CONCATENATE()]將所有選項連起來變成E欄Binder的樣子
其中建築面積和實用面積的部分有詳簡寫，所以用到[MID()]、[FIND()]等功能獲取括號內的SA與GLA
有需要的可以看以下公式
=CONCATENATE("Area (",$D$3,")(",D5,")",MID($C$3,FIND("(",$C$3)+1,FIND(")",$C$3)-FIND("(",$C$3)-1))
此處會先用到[MATCH()]
通過Room No.來查找資料在第幾Row
通過Binder來查找資料在第幾Column，
最後用[INDEX()]查找該欄該列的資料是什麼
這樣再加上文章開頭提到的Check Box小知識
就基本完成了
右邊的計算表只是簡單的 單價*面積 的問題
要注意的是 平方呎 和 平方米 的單價是不一樣的
需要用[IF]先判斷一下是在用什麼單位再決定用哪個表
待續
留言列表
