有時想要 Excel 即時更新一些重要數據,但又不想要用複雜的函數來完成,該怎麼做才好?拔拔透過開心農場範本,教你用 vlookup 搭配簡易的VBA程式來完成!

Excel-開心農場

【檔案名稱】:Excel教學範本 – 開心農場
【檔案版本】:Ver 1.0 (2016.04.29)
【檔案格式】:Microsoft Excel 2007
【檔案作者】:雙胞胎拔拔(AntonyLiaw)

 

相信經常使用 Excel 的朋友,對於 vlookup 函數並不陌生,不過拔拔相信大家都用在「正途」,也就是一般統計、資料彙整…等工作上,但你應該不知道 Excel 要如何做成「開心農場」吧?讓拔拔一步步教你完成!

●事前規劃:

開心農場版面

1. 農場的版面設計:
‧規劃12個農場土地。
‧每個農場土地要能夠顯示「目前狀態、種植的農作物、成長所需時間、成熟剩下的時間」。
‧是否擁有該土地的狀態表現。
‧種植的農作物,每個成長階段的狀態表現。

2. 帳號資訊的規劃:
‧等級、經驗值、金錢

3. 畫面更新頻率規劃:(避免畫面一直刷新而影響操作)
‧小時、分鐘、秒數

4. 農地記錄資訊規劃
‧農地編號、購買與否、種植與否、土地購入價格、種植的作物、種植的時間

5. 農作物資訊規劃:
‧作物名稱、購入價格、販售價格、作物產量、作物成熟所需時間、種植該作物所需帳號等級

6. 帳號成長經驗值規劃:
‧等級、經驗值

7. 種植農作物的確認視窗
‧選擇農作物
‧判定各項狀態是否符合,顯示對應的提示訊息
‧將所選擇的結果,更新到 filed 資料表。

●流程規劃:

1. 設計報表所顯示的資訊來源(資料表):
‧filed:農地資訊
‧seed:農作物資訊
‧level:等級資訊

2. 將main版面中的農地儲存格放大,作為主要互動儲存格。
‧點擊則呼叫種植作物的視窗
‧農地資訊的「農作物、成長週期、成熟尚餘時間」判定 filed 資料表相對應農地ID編號的狀態。

3. 將每次「種植、收成」的結果,更新到 main 資料表上方。
※也就是說整個農場的互動操作,都必須維持在 main 資料表完成,不應該切換畫面。

●函數說明:

‧使用函式 vlookup (微軟函式說明)

[alert-note]C7 (儲存格-農地狀態)
=IF(AND(VLOOKUP(C6,field!$A$2:$C$13,2,FALSE)=1,VLOOKUP(C6,field!$A$2:$C$13,3,FALSE)=1),IF(C10& lt;0.25,"種子",IF(AND(0.5>C10,C10>=0.25),"發芽",IF(AND(1>C10,C10& gt;=0.5),"成長",IF(C10>=1,"果實")))),IF(VLOOKUP(C6,field!$A$2:$C$13,2,FALSE)=0,"未購買","空地"))[/alert-note]

1. 檢查土地是否為購買(1=購買 ; 0=未購買)的狀態, 且已種植(1=種植 ; 0=未種植)的狀態。
AND(VLOOKUP(C6,field!$A$2:$C$13,2,FALSE)=1,VLOOKUP(C6,field!$A$2:$C$13,3,FALSE)=1)
2. 如果是購買且種植狀態, 則檢查現在種子成長階段,若是「小於25%」顯示為「種子」;25%~50%顯示「發芽」;50%~99%顯示為「成長」;100%時顯示為「果實」。
IF(C10<0.25,"種子",IF(AND(0.5>C10,C10>=0.25),"發芽",IF(AND(1>C10,C10>=0.5),"成長",IF(C10>=1,"果實"))))

※C10的公式是做為轉換公式使用計算用的儲存格,且時間單位是「小時」,但若是C9沒有任何種植資訊時不顯示(不計算),若有種植的話則計算目前農作物的成長進度(百分比)。
IF(OR(C9="待收成",C9=""),"",(D8*3600-HOUR(C9)*3600-MINUTE(C9)*60-SECOND(C9))/(D8*3600))

3. 檢查土地是否為「未購買」狀態,是的話顯示「未購買」,否則表示為購買狀態但未種植,所以顯示為「空地」。
IF(VLOOKUP(C6,field!$A$2:$C$13,2,FALSE)=0,"未購買","空地")

[alert-note]C8 (儲存格-種植的農作物資訊)
=IF(AND(VLOOKUP(C6,field!$A$2:$C$13,2,FALSE)=1,VLOOKUP(C6,field!$A$2:$C$13,3,FALSE)=1),

VLOOKUP(VLOOKUP(C6,field!$A$2:$E$13,5,FALSE),seed!$A$2:$E$11,2,FALSE),"")[/alert-note]

1. 跟C7儲存格一樣, 優先檢查是否該土地為購入且種植狀態, 否則不顯示任何資訊。
2. 若有種植的話, 用 vlookup 去索引C6所對應在field資料表在 E欄 所種下的 農作物編號, 並依照該 農作物ID編號 索引出在 seed 資料表中的 農作物名稱。
VLOOKUP(VLOOKUP(C6,field!$A$2:$E$13,5,FALSE),seed!$A$2:$E$11,2,FALSE)

[alert-note]D8 (儲存格-種植農作物的成長週期資訊)
=IF(AND(VLOOKUP(C6,field!$A$2:$C$13,2,FALSE)=1,VLOOKUP(C6,field!$A$2:$C$13,3,FALSE)=1),

TIME(0,VLOOKUP(VLOOKUP(C6,field!$A$2:$F$13,5,FALSE),seed!$A$2:$F$11,6,FALSE),0),"")[/alert-note]

D8儲存格跟C7差不多,只是這裡因為取回的資訊是「時間」,所以將取回的「分鐘」資訊用 TIME 函數來顯示。
※TIME(hour, minute, second)

其餘的儲存格,都和第一塊農地的概念一樣來完成,當你熟悉農場的運作方式後,也可以將其修改為「股市觀測資訊表、圖書資訊管理表…等」報表來使用唷~那麼廢話不多說,趕緊來下載吧!

 

●下載檔案資訊

  • 檔案名稱:快樂學Excel – 開心農場教學範本
  • 檔案格式:ZIP
  • 版本:v1.0
  • 檔案大小:46.1 KB

點擊下載