用簡單的函式做出排行榜

開始實作前先介紹幾個會用到&常用到的函式。

SMALL函式簡介
SMALL函式的架構為: SMALL(要尋找的範圍，排名)，
舉例來說，SMALL(A1:C3,3)這個函數會呈現A1到C3當中排名第3小的數字。

LARGE函式簡介
LARGE函式的架構為: LARGE(要尋找的範圍，排名)，
舉例來說，LARGE(A1:C3,1)這個函數會呈現A1到C3當中最大的數字。

OFFSET函式簡介
OFFSET函式的架構為: OFFSET(標的欄位，欄數，列數)，
OFFSET(H6, 3, -2)這個函數會show出位於H6下面3欄、左邊2列的格子，也就是F9。

MATCH函式簡介
MATCH函式的架構為: MATCH(目標的值，要尋找的欄或列，搜尋方式)，
若搜尋方式打0的話，該函式會show出目標值位於要尋找的"欄"或"列"中的第幾個位置。

範例實作

上圖左側是台灣上市櫃公司7/4的部份資料，

只要每次有所更新，透過函式，右側的表格也會連動的呈現出最新的資訊，

這是Excel函式簡單又有效率的地方。

以找出外資當日買超張數最多的股票股號為例，邏輯如下:『

使用LARGE
我們先找出C列中外資買超最多的數量為何，

→LARGE(C:C,G1)，

之所以在排名當中輸入G1的原因，

是做好了這個函式後我們只要順著拉下來就會跑出排名1-10的張數了。

使用MATCH
接下來我們要找這個最大值位於C列的第幾個欄位上，

→MATCH(LARGE(C:C,G1),C:C,0)，在這個例子中是660。

使用OFFSET
已經知道是第幾個欄位了，

剩下我們只要用OFFSET往左邊推幾格，就會知道買超排名第一的股票股號了，

→OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -2)

這裡稍微複雜些，我們先講為什麼要C1要變成C$1，

這是因為之後我們把函式往下拉的時候，C1會隨著我們啦下去而變成C2、C3，

寫成C$1之後它就會固定在C1的欄位上不動如山~

而MATCH(LARGE(C:C,G1),C:C,0)之所以要減掉1的原因是，

我們已經知道C欄中最大的數字位於第660個欄位上，

因此從C1算下去，欄位660是數下來的第659個欄位，因此要減掉1，

OFFSET當中最後的-2，就是股號相對於C列上的位置囉（往左邊2列）。』

所以..OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -2)這串式子，

在這個例子中會跑出3231的股號，

如果要跑出股票名稱，改成OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -1)即可，

剩下的2-10名只要把最上面的函式拉下去就可以了。

範例檔案

現在你就擁有一個可以自動化更新的排行表了~

該範例我上傳至: https://goo.gl/I7d56p

有興趣的朋友可以自行下載試做，

如果有問題也歡迎提出來討論！

巨集(Macro)在Excel中的應用

巨集(Macro)簡介

如果把巨集比喻成一首歌，那麼VBA就是穿插其中的音符，
在一張名為Excel的紙上被譜就、被記錄、被演奏。

用非文青的語言來說，巨集是用VBA記錄下來的一連串動作的集合，
並只能在Excel中執行。

巨集可以用錄的，就像錄音一樣你做什麼動作它就保存下什麼動作；
另外也可以透過Excel中的編輯器來撰寫，兩者的優劣在上篇文章已經有提及。

由於錄製的巨集不具有"邏輯判斷"的功能，它只知道你在哪個位置做了什麼事情，
因此就實用性而言是大大輸給了人為撰寫的VBA巨集的。


錄製巨集(Record Macro)

錄製巨集功能被放在Excel視窗的左下角(如下圖)，

點選並按下確定錄製後會開始記錄你的一舉一動，
因此在錄製之前一定要先想好接下來的每一個步驟，
萬一步驟做錯了或者是做了一堆無意義的動作，
那麼可能一切就要從頭來過了。

但幸好錄製巨集時，只有新的動作出現才會再錄製下去，
因此忘記下一步時最好的動作就是不要動作，想清楚之後再繼續Click Click。


巨集錄製範例

以下表(圖)做為一個簡單的範例:

每天更新收盤價後，
"手動由大到小排序現金殖利率，並且篩除扣抵率10%以下的個股"就是機械性工作，
而且沒有使用到較複雜的邏輯判斷，我們就可以用巨集來自動化這個任務。

首先點選左下方的record macro輸入巨集名稱跟其他資訊，
建議巨集名稱一眼就看得懂它的功能，
否則日後試算表日趨複雜時，
你會再也想不起來當初那個叫tt或111的巨集是什麼東西。

注意到左下角的圖示變成一個小正方形，
滑鼠移上去可以看到目前處於巨集錄製中的狀態，
接下來就是把你"機械化"的動作重複一次給電腦看了。

先排序現金股利殖利率所在的列(Column)，
接著再用篩選(filter)掉扣抵稅額低於10%的個股，
最後停止錄製巨集即可。
(點選左下角的那個小正方形即可停止錄製)


巨集錄製完成之後

巨集錄製完成之後，
該份Excel表必須儲存為xlsm格式才能把巨集儲存下來。

日後打開這份Excel文件(WorkBook)，
只要按Ctrl+w或者在開發者欄位中執行該巨集就可以自動化這份報表囉。

什麼是VBA，為什麼要學習VBA

VBA簡介

VBA的全名是Visual Basic for Application，是一種寄生在Excel當中而且只能在Excel裡運行的一種程式語言。VBA能做什麼？ VBA有很多事情做不到，像是開發一套獨立的應用程式、設計網站網頁，或是設計3D模型等等；但在它的專業領域上可以實現令人常常感到驚奇的功能，WOW~原來整理試算表的速度可以這麼快！

舉凡你能用紙筆一步步寫下工作步驟的事務，像是：計算上百家公司的本益比是否低於最近三年的平均評價、稽核公司各員工的KPI後打出考績兼製圖、測試一套新構想的交易邏輯，或甚至化身網路爬蟲抓下你要的資訊，只要一鍵輕鬆搞定；若凡你身邊有那種看似整天偷雞打混卻又按時做完交辦事項的同事，要嘛他人緣特佳，否則大概便是學了VBA。

雖然光靠Excel的內建函式也可以處理大量的運算工作，但只要牽扯到"邏輯"的部分非人力介入不可，像是判斷各家公司的淡旺季、KD黃金交叉後鴻海股票呈現出怎麼樣的走勢、整理完今天公司的收支後用檔名加上今天的日期等等，VBA都可以輕鬆的化解以上問題。

更別提VBA的專長---大量的資料運算，當決心使用VBA實現工作上所有例行事務後，很多需要2~3個小時的工作可以在幾秒鐘之內解決完畢，當"秒殺"成為事實，學習VBA就是你的義務了。


學習VBA的先備條件

學習VBA必須懂英文？學習VBA必須要熟悉Excel？學習VBA必須要先懂VB(Visual Basic)??? 別再相信沒有事實根據的說法了，雖然不可能上了八堂課後就成為coding專家，但學習VBA遠比學習操作Win 8磚塊格來得簡單，而如果Windows的藍屏以及更新提示對你如同家常便飯，學習VBA應該不會讓你另外感受到太大的挫折才是。

只要你知道Excel中的欄位、工作表、工作簿分別是什麼、會排序、會篩選、大概知道Excel可以實現什麼樣的工作，以及有健全的電腦，那麼你就是一個潛在的VBA code了。


如何學習VBA

台灣有關VBA學習的書籍大多為條列示的功能整理，優點是需要實現某個功能的時候可以立即查找；缺點則是沒有教你怎麼全部統整起來使用，常常過了下面這村就忘了前面那店，到頭來還是一場空。

筆者教學VBA的經驗4年有餘，教學範圍除了常見的辦公室應用、資料處理、工作自動化外也包括程式交易及網路爬蟲等等，期間整理出一套快速且容易上手的方法。除了在這裡分享有關Excel、VBA的心得及技巧外，也希望推廣VBA在工作上的運用，讓大家從無腦的庶務中解脫出來。

圖片來源:http://goodmoneying.com/wp-content/uploads/2013/08/freedom.jpg