Search
錢當然要用在刀口上！用 Excel 的模擬分析功能找出公司的競爭力！Excel 教學 40
► 練習檔下載
載點一 https://tinyurl.com/4jtmcrhs
載點二 https://share.weiyun.com/mxpZOx6f
► 小額贊助 Papaya 電腦教室 ☕️
ECPay： https://p.ecpay.com.tw/A1B71E8
Paypal： https://www.buymeacoffee.com/papayaclass
#分析藍本管理員 #目標搜尋 #運算列表 #規劃求解 #Excel
► 內容綱要
00:00 開場白
00:26 目標搜尋
01:57 使用 PMT 函數計算貸款還款金額
03:22 運算列表
05:07 使用色階來標注列表數值
07:08 分析藍本管理員
08:58 規劃求解
► 練習檔下載
載點一 https://tinyurl.com/4jtmcrhs
載點二 https://share.weiyun.com/mxpZOx6f
► Papaya 電腦教室 - 會員專區
加入頻道會員 https://tinyurl.com/sxt3d2b
會員專區影片連結 https://tinyurl.com/y6mvo38a
► 小額贊助 Papaya 電腦教室 ☕️
ECPay： https://p.ecpay.com.tw/A1B71E8
Paypal： https://www.buymeacoffee.com/papayaclass
► 影片中操作的軟體版本
Microsoft 365 https://tinyurl.com/ya7b4v9o
► Papaya 剪輯影片所使用的軟體
Adobe After Effects https://tinyurl.com/sobj83y
Camtasia Studio https://tinyurl.com/y6thupff
Keynote https://www.apple.com/tw/keynote/
► 影片中用到的多媒體素材來源
Freepik https://www.freepik.com/home
Envato Elements https://elements.envato.com/
Flaticon https://www.flaticon.com
#記得打開影片的CC字幕喔 #分析藍本 #目標搜尋 #運算列表 #規劃求解
內容綱要00:00 開場白00:26 目標搜尋01:57 使用PMT 函數計算貸款還款金額03:22 運算列表 05:07 使用色階來標注列表數值07:08 分析 藍本管理員08:58 規劃 ... ... <看更多>
Excel N25 運算列表 同時二個變數的 模擬分析. 1K views 1 year ago Excel 2021 ... 1K views 1 year ago Excel 2021 試算表教學. 1,040 views • Dec 16, ... ... <看更多>
excel模擬分析運算列表 在 [心得] VBA如何編寫Vlookup公式整理財產目錄- 看板Accounting 的推薦與評價
VBA如何編寫Vlookup公式整理財產目錄
網誌圖文版：
https://www.b88104069.com/archives/4234
Vlookup是《會計人的Excel小教室》主打的兩大招式之一，對於會計工作有很大幫助，在
整理和補充報表時，往往會用到Vlookup函數公式。但，由於它是「比對特定條件查找並
傳回儲存格內容」，聽起來就比較消耗計算資源，有經驗的讀者應該遇到過，在筆數量巨
多、查找範圍寛廣的情況，Excel一定當場卡關給你看，然後CPU使用率瞬間飇昇50%以上
……。我自己工作便有幾個例行性檔案，資料少說幾萬筆，而且系統產生出來的報表有所
不足，必須以Vlookup函數補好補滿，每次把公式往下拉到底，惡夢於焉開始，我的工作
，跟著我的電腦一起被Excel綁架了。在此分享個人在Excel及VBA如何減輕計算負擔的小
技巧（簡單版和複雜版）：
一、極簡版本的資產目錄，只有6筆資料，大企業絶對是這個的好幾N倍。
二、「資產代碼」、「資產類別」、「會計科目」，這些是原報表沒有、但很想要有的資
料，必須以Vlookup帶入。
三、儲存格「G4」的公式：「=LEFT(A4,2)」，儲存格「H4」的公式：「=VLOOKUP(G4,二
!A:C,2,0)」，儲存格「I4」的公式：「=VLOOKUP(G4,二!A:C,3,0)」，有修過《會計人的
Excel小教室》學分，應該都會寫像這樣的函數公式。
四、如同文章一開始所述，如果資料量大，好幾萬個Vlookup同時運作，結果可想而知。
解決辦法之一：上方功能區移到「公式」頁籤、「計算」區塊，將「計算選項」下拉，有
「自動」、「除運算列表外，自動重算」、「手動」三個選項。「自動」是只要儲存格有
任何變動，包括自動儲存時，都會再跑一次幾萬個Vlookup（其實沒必要）。「除運算列
表外，自動重算」主要是搭配Excel一個「模擬分析」的應用，有機會再作分享。「手動
」則是這篇文章推薦的方法之一，設置後Excel不會主動重算，除非操作者按下這個功能
區塊裡的「立即重算」指令。
五、手動重算縱然有其妙用、而且操作簡單，但往往工作中會同時開好幾個Excel檔案好
幾個工作表，手動重算只能在「都不重算」和「一起重算」兩者之間控制，在靈活度上等
於是沒有。最好的解決方案應當是需要時再重算這幾萬筆Vlookup，就這幾萬Vlookup，其
餘不受影響。想達到這樣的效果，只有編寫VBA程式碼了。首先在這個步驟重點介紹VBA的
「UsedRange.Rows.Count」和「For…Next」。
10：建立一個巨集程序，取名為「UsedRange_Left」。
30：很實用的標準程式碼，計算目前工作表的資料行數，設定其值為「R」，如同步驟一
的圖片所示，這裡的R值為10。
40：在儲存格「G1」輸入「R」值。
60：設置一個從「4」到「R-1」的迴圈，變數為「i」，於此範例即為4到9。
80：利用「Left」函數取儲存格文字串的左邊兩個字元，並且設定其值為「C」。例如當i
＝4時，「Left(Cells(4, 1), 2)」即為「OF」。
90：在儲存格「(i,7)」輸入「C」值，例如當i＝4時，亦即於「G4」輸入「OF」。
110：完成一次循環，跳回到「For…」執行下一個「i」。
130：結果此程式。
六、執行結果，如圖所示。「G1」為「10」，表示資料含標題共有10行。「G4」在資料編
輯列為「OF」，即使把它的引用對象儲存格「A4」清除，「OF」仍然在，這代表Left函數
是在VBA執行時計算，把結果值輸入於Excel儲存格中，在Excel並沒有任何函數公式，因
此也不會有公式計算的問題。
七、在先前VBA的基礎上進一步編寫程式碼：
90：在迴圈「For i = 4 To R - 1」中再套一個迴圈「For j = 2 To 4」，第一個「i」
迴圈類似於Excel公式往下拉、好幾個Vlookup的效果，第二個「J」迴圈類似於一個個的
Vlookup，相當於模擬一行一行往下查找的效果。
110~130：白話解釋程式碼，如果「C」(Left(Cells(i, 1), 2))等同於Sheets("二
").Cells(j, 1).Value，那麼將「Sheets("二").Cells(j, 2).Value」輸入在「Sheets("
八").Cells(i, 7).Value」。用心琢磨這一段程式碼，應當能理解其執行效果，便是函數
公式「VLOOKUP(G4,二!A:C,2,0)」。
150~170：另外一組Vlookup，前面查找的是資產類別，這裡查找的是會計科目。嚴格比較
VBA程式碼和Excel函數公式，只有在如果有兩個查找條件同時成立，VBA程式碼取的是成
立的最後一行內容，Excel的Vlookup函數會取成立的第一行內容，這部份文字說明較為抽
象，日後有機會再以適當範例作介紹，總之於實務工作中，很少會遇到因此所產生的困難
。
八、一如預期，以VBA巨集實現Vlookup計算結果，除非再執行一次巨集，否則Excel再怎
麼自動計算或自動儲存，都不會再作這方面的處理，所以當然也不會影響電腦速度。
這節範例用到了一般應用程式中很常見的迴圈語句。如果肯花時間閱讀理解，它其實相當
於高中數學方程式的概念，應該不難理解，它的概念。這裡的VBA程式碼就是單純依照
Vlookup函數的運作邏輯，把工作表上的每一個儲存格當做對象來做計算處理。擴大而言
，在現有的函數公式裡面，只要有涉及到範圍，從哪個儲存格到哪個儲存格的，都可以寫
成VBA的一個迴圈，從這個角度來思考VBA程式碼，會是一個蠻有趣的過程、也會比較有成
就感。
延伸閱讀：
VBA如何自動建立資料夾（下）
https://www.b88104069.com/archives/4193
VBA如何合併報表資料
https://www.b88104069.com/archives/4220
VBA如何保持檔案乾淨
https://www.b88104069.com/archives/4230
--
周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office
會計人的Excel小教室： https://www.facebook.com/acctexcel
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 112.20.97.247
※ 文章網址: https://www.ptt.cc/bbs/Accounting/M.1503711454.A.74E.html
... <看更多>