Skip to main content
有時候看似隨機產生的結果,也只是為了讓未來存在而產生的必要過去罷了。

前言

不管在什麼時代,下一餐在哪裡?要吃什麼?一直都是一大問題。隨著科技的進步,手指只要在手機上快速彈跳幾下,就像施展了魔法一樣,跑出了大量的美食清單,多少顆星?多少人評論?

但有沒有那種時候是,不想查詢,不想跑大老遠只為了那些網路評論,只想在附近常吃的口袋名單中找尋一間今日特餐,快速且滿足地完成這一餐呢?

相信你我都有相同的經驗,難道就不能簡單地決定今天該吃什麼嗎?難道我就要一直嘗試新的餐廳嗎?如果我已經有附近熟悉的餐廳名單,就只差一個人幫我決定呢?

以上這些都只是美化過的說法,實際上只是在公司遇到了難題,一群人每天中午想訂外食但是又有選擇障礙,訂個餐總要浪費十分鐘討論;我是一個很無法忍受沒效率的人,所以提出了不如把大家想吃的清單都給我,我寫一個隨機產生器來產生每週菜單吧!這才是事實的真相,科技始終來自於惰性阿。

範例

先來預覽一下最後希望達到的效果吧:

隨機外食清單範例

介面很單純,兩個按鈕一個結果;禮拜一就是由負責訂便當的便當長按下重新計算按鈕,更新禮拜一到禮拜日的清單,然後複製貼圖之後寄給大家,讓大家提前點餐,至於【 點餐統計 】 的部分,會專門做一篇如何用EXCEL快速統計並分類清單來跟各位分享。

  1. 首先要先在EXCEL的工作表2建立口袋名單如下:

2. 在工作表一先在A1~E1依序輸入:隨機產生、日、店名、電話、推薦菜單做為表頭

3. 在B2~B3輸入星期一、星期二,然後選取B2:B3點擊儲存格右下角+字往下拉至B8得到星期一~星期日

4. 非常重要的關鍵在於A2~A8必須產生亂數且不重複的整數,容我先跳過,先在A2~A8填入數字1~7

5. 在C2的地方輸入=VLOOKUP(A2,口袋名單!$A$1:$E$18,2,0),這邊表示以A2的數字做為搜尋目標,到口袋名單中的A1~E18找到對應的數字,其中$A$意思是後續複製這個公式時,不隨欄列位置調整相對位置,再來的2,表示找到對應A2的數字時,從A1~E18的儲存格中,帶入對應該找到對應A2列位置的第二欄,最後一個0表示必須完全相等。整個公式表示在口袋名單A欄中找到數字完全等於A2的列,回傳B欄也就是店名的資料回來。

6. D2如法炮製輸入=VLOOKUP(A2,口袋名單!$A$1:$E$18,3,0),回傳電話資料。

7.E2亦是如此,但因為有些餐廳並沒有寫推薦說明,如果回傳資料為空白會顯示0,為了避免這個狀況,將公式修改為=IF(VLOOKUP(A2,口袋名單!$A$1:$E$18,4,0)=0,””,VLOOKUP(A2,口袋名單!$A$1:$E$18,4,0));表示當發現回傳的資料=0時,顯示空白(雙引號內為字串顯示,當沒有輸入時表示為空白””),若不等於0就顯示回傳的資料。

DEMO如下,大家可以試試看,最後一樣選取B2~E2,+號往下複製到E8:

恭喜大家完成了幾乎一半的工作內容,再來便是做出隨機產生而不重複整數

8. 在A2輸入公式=SMALL(IF(COUNTIF(A$1:A1,ROW($1:$18))=0,ROW($1:$18)),INT(RAND()*(18-ROW(A1)))+1)並按下Ctrl+Shift+Enter變為陣列公式,此時可以看到公式變為 {=SMALL(IF(COUNTIF(A$1:A1,ROW($1:$18))=0,ROW($1:$18)),INT(RAND()*(18-ROW(A1)))+1)},將此向下複製到A8,這時可以發現每次按一下Enter都會刷新一次菜單,這是因為自動重算公式的功能還開啟著,必須調整為手動更新。

9. 在撰寫VBA之前,必須前往設定開啟開發功能,可以參考MICROSOFT的教學,開啟後功能表會出現開發人員選項,選擇後點選插入–>表單控制項–>按鈕,此時可以在一個空白處拉出一個按鈕,右鍵選取後編輯文字改為重新計算。

10.這個時候這個按鈕並沒有實質功能,因為還沒有給予功能,按下快捷鍵ALT+F11可以快速進入VBA編輯介面,選取插入–>模組,會產生一個Module 1,並在右邊有一個空白頁,貼 上 以下程式碼:

Sub 重新計算()

Excel.Application.Calculate



Excel.Application.Calculation = xlCalculationAutomatic



Excel.Application.Calculation = xlCalculationManual


End Sub

11. 回到EXCEL頁面,右鍵選取9.創造的按鈕,指定巨集,這個時候可以發現有一個重新計算可以點選,點選後確認。測試一下功能,按下重新計算,可以發現菜單會更新,而且這個時候你做任何的更動,公式都不會重新計算,原因在於上方的程式碼實際上做了三件事情,先強制計算–>開啟自動計算–>開啟手動計算,如此一來每次按下去都會重新計算並將設定調整為手動計算。

12. 回到開發人員,在創建一個按鈕,並且編輯文字為複製貼圖。

13. 進入VBA編輯介面,在End Sub的下方貼上以下程式碼:

Sub copyFIG()

Range("B1:E8").CopyPicture Appearance:=xlScreen, Format:=xlPicture

Excel.Application.Calculation = xlCalculationManual

MsgBox ("已複製至剪貼簿,可直接貼上!")

End Sub

14. 這邊其實是一個錄製巨集得到的一個指令,動作是複製B1:E8的儲存格作為圖面,而且保留透明屬性;關於巨集錄製的部分網路上已經很多教學,以後會開一篇專門來跟大家談談 【 錄製巨集的故事 】 。

15. 點選按鈕複製貼圖,右鍵指定巨集 copyFIG然後按下確認,這時你會發現按下按鈕時,會跳出對話視窗提醒已經複製剪貼簿,這時你就可以貼在MAIL去公告本周菜單了!

完成連結於此,可以來玩看看試看看。

下集預告,這個小工具雖然看似簡單,但實際上必須要有邏輯以及架構的規劃能力,關於如何有計劃的去實現想要的功能,下一篇將用這個小工具當作案例來分享-【概述-如何規劃並撰寫一個EXCEL的小工具】。

THD

Author THD

More posts by THD

Join the discussion 2 Comments

  • stanley表示:

    THD大師您好:
    您寫的這個亂數餵食器真的很讚,我修改了一下以符合我自己需求。
    現在想請您指教一下(在下並非資訊本科請包涵),
    我想要在第一個分頁面建立一個按鈕,
    功能是,按下後可以幫我同時按其他分頁的重新計算,
    然後第一個分頁的內容也就跟著改變了。
    我自己修改的檔案放在如下的google空間,如不嫌棄請看一下
    https://drive.google.com/drive/folders/1m7U_Qa06d7YBdfi_I2ZtRZTjmvbrRY4i?usp=sharing

    您可以寫個文字檔案後上傳,我會照著步驟做,
    也可以直接編輯我的檔案再上傳,
    非常感謝。
    最後請問個小問題,
    有沒有推薦讓新手學習excel的函數與VBA的書或網站呢?
    (我也會來這裡看看的)

    • 個人頭像照片 THD表示:

      Hello, Stanley 你好!
      非資訊本科也沒關係,因為我也不是資訊相關科系歐~^^”
      這個程式做動上的關鍵是開關EXCEL重新計算的功能,
      在原先寫得重新計算的巨集功能之中,有一個會把自動計算改成手動的語法-
      Excel.Application.Calculation = xlCalculationManual
      主要是防止操作過程一直對亂數的結果修改。
      如果是要單次執行重算,你可以插入一個新的模組
      Sub RE_CAL()
      Excel.Application.Calculate ‘下命令給Excel計算所有公式
      End Sub

      或者直接使用原本”重新計算”的巨集裡面的第一行也有這個功能。
      你可先嘗試看看歐~有問題再留言。
      ————–我是分隔線————–
      小問題的部分可以參考為什麼選擇用EXCEL VBA?的文章內容。
      學習EXCEL的書相當的多,但如同一般程式設計的課程一樣,是比較枯燥容易產生挫折的內容,
      我會鼓勵你從解決生活中遇到的問題學習,再回過頭來看這些書籍的內容強化基本功,會更有效率歐!

Leave a Reply