之前已經為外賣記錄表寫了兩篇教程:

1. 

用EXCEL製作外賣記錄表第1步--為每個訂飯人新增一個資料頁[SUM()][根據儲存格資料生成工作表]

2. 

用[HYPERLINK()]函數建立超連結--內含批量建立超連結實例(用EXCEL製作外賣記錄表第2步)

加上這一篇,這個例子基本就完成了,這個外賣記錄表我用了一年多,幾乎不覺得還有需要添加的功能,如果有同樣需求的寶寶可按照三篇教程逐一設置,若和主頁君一樣懶的話,也可以留下郵箱唷。

 

關於巨集,某君很早之前就介紹過了,關於單純錄製動作的巨集也有介紹,不過某君覺得,對於正在學習的人而言,實例是非常重要的,如果腦筋靈活的人,可能一看到功能就知道可以運用到什麼方面,有些人則是有學沒有懂,從不懂該如何把學到的東西運用到實際中。也不是說要看實例的人都是不靈活的!(不要歪曲某君的意思)

多看實例可以學習別人的使用方法,看多了,會更靈活哦!!! (突然情緒到,又寫了一篇碎碎念,有興趣的可以移步看看)

如果對巨集一無所知的話,可以看回這一篇: [VBA]Excel巨集的應用(1)--巨集的認識與初始置 

將會介紹的實例相當於上文提到第一種巨集的使用方法:  單純錄製動作,不需編程

而準備好升級的話,可以往下看了~ 

這次升級了什麼的,照例來一下結果GIF吧:

某君將Excel分成三個區域,分別是: 

  • 每日點單資料區: 下拉選單選擇同事名稱,打入菜式名及數量,下方有一個簡單的統計數字,方便點單後確認數量。
  • 下單區: 按下橙色按鈕,巨集運行,將資料整理並貼到中間,方便下單。
  • 資料儲存區 (按日儲存): 按下第二個橙色按鈕,巨集運行,將資料複製貼上至最右,For Record,同時清空最左,方便下次記錄資料。

(因為巨集沒辦法上一步,所以運行巨集前,一定要思考過,最好是先儲存一下)

除了巨集外,可能有些童鞋還不會下拉選單,可參考這篇: 建立貨物資料表[資料驗證][下拉選單][定義名稱]--以一個偽小店為例

.

巨集部分正式開始

這裡有Order和List兩個巨集,Order是將資料放到下單區,方便落Order,List是將資料放到儲存區。

 

因為是錄製動作的巨集,所以非常靈活,可按自己的需求去做,

這裡只是某君個人的使用習慣,不用照搬,主要是了解怎麼"偷學"代碼即可~

那要怎樣偷學代碼呢?自然是借用excel本身的錄製功能啦。

開發人員-->程序碼-->錄製巨集

開始錄製之後,完全可以照常操作,但你所做的一切都會記錄下來,所以初學時儘量少動,最理想的狀況就是一個動作錄製一個新的巨集,

這樣就可以輕鬆對應代碼與動作了。

例如 選取H至M的代碼是

可以如同字典一般即用即查,運用得當的話,這可比上網查代碼快多了!

-

如果錄製的動作多了,又可以怎樣了解代碼所代表的意思呢?方法大致有三:

一是閱讀,代碼其實都是以英文加上一些規則組合而成的,寫的還需要背背,看的話,連蒙帶猜應該能看出個大概(剛才錄完的動作,應該也不會健忘到完全沒印象吧)

二是網上查(基本上這是懶人君最後的手段了,除非真的完全搞不懂,不然真的不會在網上查,畢竟結果太多,要篩選也是需要時間的)

三是觀察實際的效果,用注釋 (') 的方法可以讓使用者看出每條代碼的作用,自然就不難明白其意思了

基本一加三就可以解決大部分簡單的情況了,實際如何操作?請看:

VB中注釋的標記是 ('),注釋成功後文字會變成綠色,注釋後的代碼就相當於文字,不會被執行。

所以在遇到不理解代碼時,可只反復地注釋該句代碼,尋找有它和沒有它之間的變化,就能猜到它的意思了。

該例子: Selection.EntireColumn.Hidden = True 看英文基本是一目了然

隱藏? 回答: 是/ 否

隱藏什麼? 需要查看前面Columns("I:L").Select,即I:L被選取了,然後將選擇的部分隱藏

也可以寫成Columns("I:L").Hidden=True

通過注釋前後的變化也能看出I:L欄被隱藏了

 

Order

平時向店家下單時,通常都是XXX幾份、YYY幾份、珍奶再幾份這樣,

如果剛才點完一份鹽酥雞、過了十樣單品之後還點鹽酥雞的話,店家大概會很困擾吧,

才四、五樣東西的話,用肉眼就可以輕鬆分辨,但想更省力的懶人君當然是讓電腦直接宣佈答案囉。

 

----以下是使用錄製功能生成代碼的步驟(想直接看代碼的)----

-

錄製開始後,就開始進行操作了: 選取需要的資料,並複製到相應的位置

(在這個案例中,同事的名字不重要,重要的是數量和類型)

框選相應的資料,並[移除重複項] 資料-->資料工具-->移除重複項

(在這個案例中,同事的名字不重要,重要的是數量和類型)

按類型[排序] 資料-->排序與篩選-->排序

把不需要的部分隱藏,令界面更簡潔

代碼及解釋是:

也可直接複製代碼去調試

 

Sub Order()
'
' Order 巨集
'

'
    Columns("H:M").Select
    Range("M1").Activate
    Selection.EntireColumn.Hidden = False '取消隱藏
    
    '將每日點單資料複製至下單區
    Range("C2:G24").Select '選擇資料
    Selection.Copy         '複製資料
    Range("H2").Select     '選擇要貼上的位置
    ActiveSheet.Paste      '貼上
    
    '移除重複項
    ActiveSheet.Range("$H$1:$L$38").RemoveDuplicates Columns:=1, Header:=xlNo '選擇下單區中的資料(有標題),只刪除資料中第一個Column中的重覆項
    
    '排序
    Range("H2:L24").Select                                                           '選擇需要排序的範圍
    ActiveWorkbook.Worksheets("_Daily_").Sort.SortFields.Add Key:=Range("L2:L13") _
       , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("_Daily_").Sort
        .SetRange Range("H2:L24")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With                                                                        '按類型(L欄)按序,其他的是排序的範圍、順序或倒序,有沒有標題等
   
   Columns("I:L").EntireColumn.Hidden = True '隱藏


End Sub

 

計算每樣單品的數量[countif] =countif(range, criteria)

有沒有眼尖的寶寶發現上述的代碼都沒有實現計數的功能呢?

因為excel本身的計算功能很強大、使用很簡單!

自然要把計算類的事情交還給excel處理了 (這也是巨集的好處,結合代碼與office本身的優勢,要不然讓代碼全幹完了,excel要幹什麼?)

因為「下單區」的菜式是由「每日點單資料區」複製過來的,自然是連標點符號都一模一樣

只需要計算「B餐 五谷粉 魚湯」在下單區出現的次數就可以了,

整理一下: 計算一下(在下單區中,「B餐 五谷粉 魚湯」)的次數

翻譯成代碼: =countif(C2:C25下單區,H2B餐 五谷粉 魚湯)

完美主義者的話,連格式都不可放過:=IF(H2="","",COUNTIF($C$2:$C$25,H2))

List

相比起Order,List就簡單得多。

因為Order中需要用到複製、移除重複、排序等多項功能,所以多多少少都要理解一下代碼,而List只需複製一堆資料做Record而已。

具體怎樣做呢?直接去片吧:

 

程式及其解釋:

 

Sub List()
'
' List 巨集
'

'
    '生成當日日期
    Range("P2").Select
    Selection.End(xlDown).Select                                    '跳到最底 (Ctrl+↓)
    ActiveCell.Offset(1, -1).Select                                  '↓ offset(1,0) ← offset(0,-1) 根據x, y軸的原理
    ActiveCell.FormulaR1C1 =Date                                 '借用錄製定好位後,再將=TODAY()改為VBA中的TODAY,即Date,這樣就可以不用自己打日期,又不會每次打開excel時日期都變化了
    
    '複製資料
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select                  '從這裡開始選取到最底 (Ctrl+Shift+↓)
    Range(Selection, Selection.End(xlToRight)).Select               '從這裡開始選取到最右 (Ctrl+Shift+→)
    Selection.Copy                                                        '複製
    
    '再定位後貼上
    Range("P2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    
    '清空資料,方便下次輸入
    Range("B2:E24").Select                                           '選擇
    Selection.ClearContents                                          '清空
End Sub

4ngus 發表在 痞客邦 留言(0) 人氣()