Excel基礎 與 重要函數
xlsm副檔名，才可以儲存104萬8576筆紀錄 效率也高
巨集錄製，改程式碼
cell
sheet
workbook
ActiveCell
ActiveSheet
ActiveWorkbook
Dim 宣告 (Declare in Memory)
迴圈
While 判斷式
…
Wend
For x=1 to 10
…
Next
For Each x in 群體元素
…
Next
VLOOKUP
IFERROR
HYPERLINK
TEXT
IF
格式化表格 與 樣板技法
錄製巨集 以「相對位置錄製」 會往下（不會紀錄儲存格）
sheets.count
資料 > 連線 活頁簿連線視窗
activeworkbook.connections(“連線”).delete
沒有刪除連線每次開啟檔案會檢查每個連線會導致開啟速度很慢
股票圖繪製
TEXT(TODAY(),’’’’yyyymmdd)
下載網路的檔案 (Windows API語法）
URLDownloadToFile(0,下載網址,下載後的檔名,0,0)
DoEvents
ActiveWorkbook
ActiveWorkbook.Path
ActiveWorkbook.Name
Format(Date,”yymmdd”)
Application.DisplayAlerts=False （執行完就會自己打開）
Application.DisplayAlerts=Ture
dir(filename) 檢查檔案是否存在
Replace(標的字串,要被替代的字串,替代的字串)
CELL(“filename”) 儲存格顯示Sheet路徑名稱
FIND(“]”,CELL(“filename”))找出”]”在字串的第幾個字
MIN(CELL(“filename”),FIND(“]”,CELL(“filename”))＋1,100) 取字
資料 > Web > 網址 > 選定表格 > 選項 > 整個HTML格式，用來保留原本的超連結HTML格式
ActiveSheet.Copy After:=Sheets(Sheets.Count) 複製的工作表放在最後一張工作表的後方
Sheets(“工作表名稱”).Visible=False 隱藏工作表
VBA內執行EXCEL函數
Application.WorksheetFunction.Excel函數
Application.WorksheetFunction.CountA(Range(L:L))
Application.WorksheetFunction.RandBetween(初值,終值)
公式>立即重算
Replace(原始的字資料,被改的字,要改的字)
Set RngTemp = Selection (Selection是Range物件，設定Range物件要用Set)
msgbox “顯示文字”,vbInformation,”訊息盒”
MATCH 傳回符合條件所在的列數或欄數 MATCH(條件,欄位,Type)
INDIRECT 將文字或其他公司產生的字串，當作範圍名稱或位址
INDIRECT(“SheetName!B”&MATCH(,,))
INDIRECT(“A3:B4”) “ ”內必須是文字
Sheet!Cell 為公式中的表達方式
OFFEST(起始儲存格,左右偏移,上下偏移,選取列數,選取欄數)
陣列的方式顯示 1.資料編輯列 2.Ctrl+Shift+Enter
SUMPRODUCT 加總陣列範圍中的所有資料
應用一 不同比例 不同數值 兩兩相乘後加總
應用二 陣列 依分類並排名次 (概念為比自己分數高加1)
SUMPRODUCT(- -(A1:A2)) A1 A2內為文字 減負運算轉成數子可以公式內相加 不需使用陣列公式的快速鍵才能顯示
減負運算 (兩個減號：乘兩次負1)
將文字轉為數值 ； TURE轉成1 FALSE轉成0
SUM(- -(A1:A2)) A1 A2內為文字 減負運算轉成數子可以公式內相加 需使用陣列公式的快速鍵才能顯示
儲存格編輯狀態下，陣列公式的組合鍵 ctrl + shift(資料才放得下去，資料不能獨立被刪除，被視為一整體)
動態選取儲存格
IF(條件式,成立做的事,不成立做的事)
COUNTA 計算範圍內有資料的儲存格
用INDIRECT去固定儲存格位置，才不會因為插入貼上刪除而改變原參考的儲存格位置(情境：例如已經知道標題列不會跑掉 要固定的使用)
Indirect(5:5)
紅綠燈
設定格式化條件 > 圖示集 > 其他規則 > 預設就是紅綠燈
打勾只顯示圖示
CONCATENATE 串接字串與儲存格，等同於&
日期
TODAY()
year(TODAY()) 取出年份
日期能加減
1901/1/1 是第1天 改儲存格格式就能看到
Date(年,月日,) 組成一個日期
DATE 今天日期
Selection.Delete Shift:=xlUp 刪除 向上縮
VBA中使用工作表函數 Application.WorksheetFunction.函數名稱
Application.WorksheetFunction.CountA(Range(“B:B”))
Selection.Columns.AutoFit 選取的欄位自動調整欄寬
VLOOKUP 垂直方式尋找
HLOOKUP 水平方式尋找
AVERAGEIFS(儲存格範圍,條件的範圍,條件)
按多個條件進行求平均值
函數語法格式語法格式：=averageifs(average_range,criteria_range1,criteria1,crileria_range2,criteria2,…)其中：average_range：表示需要求平均值的區域。criteria_range1，crileria_range2：分別表示條件1所在區域和條件2所在區域。criteria1，criteria2：分別表示條件1和條件2。其中，條件criteria處的形式可以是數字、表達式、文本條件或單元格引用。例如，條件可以是數字1000、表達式”>1000"、文本”研發部” 或 D2。(原文網址：https://kknews.cc/zh-tw/other/om2gkj5.html)
AVERAGE(儲存格範圍)
COUNTIF(儲存格範圍 ,條件)
儲存格G3：=LOOKUP(F3,$I$3:$I$7,$J$3:$J$7)
藉由LOOKUP函數，將儲存格F3的內容，比對儲存格I3:I7，找尋小於或等於的數值，並傳回對應的陣列值。例如：加權平均為60.9，在分數間距中小於或等於的數值為60，對應的陣列值為D。
而你也可以使用以下的陣列寫法，即可不需等第表：
儲存格G3：=LOOKUP(F3,{0,60,70,80,90},{“E”,”D”,”C”,”B”,”A”})
注意：陣列中的值必須以遞增順序排列，否則搜尋結果會錯誤。
IFERROR(沒有錯誤時顯示,有錯誤時顯示)
參數1如果為錯誤值，就返回參數2。如果參數1沒有錯誤，就返回參數1的正常結果
HYPERLINK(“#sheetName!儲存格“ ,”文字”)
新增格式化規則 > 管理規則 > 新增規格 > 新增格式化規則
COUNTIF(儲存格範圍,條件)
COUNT(A:A) 計算Ａ欄數字有幾個 不計算文字
ActiveWorkbook.Path 檔案所在路徑
ActiveWorkbook.Path & “\Stock\Foreign\” 所在路徑之下的資料夾
Application.Wait(Now+TimeValue(“0:00:00”) 延遲時間
才不會被交易所認為是攻擊而斷線兩小時
IntGetSecond=Application.WorkSheetFunction.WaitRandBetween(3,7) 每次延遲3~7秒，不會一直是間隔7秒
Left(儲存格,取幾個)
Mid(儲存格,第幾個,取幾個)
Right(儲存格,取幾個)
Excel開啟檔案
Workbooks.Open Filename:=檔案名稱.副檔名
關閉視窗
ActiveWindows.Close
刪除檔案
Kill Path & 檔案名稱.副檔名
錄製巨集 以相對位置錄製
Option Explicit
Range(“B1048576”).End(xlUp).Row 取得最後一筆資料列號
Sheet(“SheetName”).Visible = True / False 隱藏表格
ActiveSheet.ListObjects(Selection.ListObject.Name).Name=NewName
更改Sheet中的表格名稱 ListObject.Name
命名一個範圍儲存格的表格名稱，之後可以用表格名稱呼叫這個儲存格範圍
設定Range物件儲存格範圍用Set
Set Rng = Range(A2:B6)
VBA 公式取出固定的值是會被當作文字資料，公式＊1就會變成數值資料才可以跟其他的數值做MATCH
命名及更改”表格名稱”
工具列 >設計> 表格名稱，可以命名儲存格ActiveSheet.ListObjects(“Selection.ListObject.Name”).Name=”NEWNAME”
計算列數LngRowCount=Application.WorkSheetFunction.Count(Range(“A:A”))
多欄選取
Range(“A2:B100”,”E2:E100").Select
COUNTIF & COUNTIFS 使用教學
https://blog.gtwang.org/windows/excel-countif-countifs-function-tutorial/
外資買超[證券代號]是指表格名稱外資買超的證券代號欄位全部
[@證券代號]有帶＠是指同一個欄位同一列[證券代號]的值
設定格式化的條件 >管理規則 >編輯規則 >使用公式來決定要格式化哪些儲存格
INDEX + MATCH
https://kknews.cc/zh-tw/other/8l3j9e.html
WorkSheet_SelectionChange 事件的處理 Target是Range物件
Target 傳回儲存格的值
Target.Address 傳回位置
Split函數
Split(“$B$3”,”$”)(1) 傳回結果Ｂ
Split(“$B$3”,”$”)(2) 傳回結果3
InStr函數 取得字串在某字串中的位置
InStr(“abcdef”,”c”) 值傳回3
On Error Resume Next 得到錯誤訊息往下做
EOMONTH函數(日期,整數) 取得指定日期偏移至某月的最後一天
DateDiff(‘’m”,日期一,日期二)
繪製股票圖與均線圖，不使用EXCEL內建的股票圖因為有缺點，無法繪製均線無法取得線圖的原始數據，解決方式是用折線圖
選取日期 選取開高收低 > 插入> 折線圖 > 這時匯出一圖表 > 選工具列圖表工具 > 選工具列 新增圖表項目 > 選 線條 > 高底點連線打開 > 選 漲跌線 > 漲跌線打開
需再完成股票圖開高收低之間的無連線
選擇 格式 > 選擇左上角 數列開盤價 > 圖案外框 改成無外框 (開高收低做四次)
需再完成顏色 上漲紅色 下跌綠色
左上選 上漲橫條圖 >選圖案填滿 >選顏色 >選圖案外框 >選顏色
下跌橫條圖 如是
股票圖加入均線
將均線表格全部選取MA5 MA10 MA20 MA60
COPY PASTE 在股票圖上 出現一個不是要的圖
選變更圖表類型 > 組合式 > 開高收低 都選成折線圖 > MA5 MA10 MA20 MA60 副座標軸 選打勾。之前的設定會不見是個BUG， 需要重設一次開低收高不要有外框 (圖案外框 改成無外框 (開高收低做四次))，並改顏色及變細寬度
建立範圍名稱
日期 107/01/20 轉換成 201801
年=LEFT([@日期],FIND(“/”,[@日期])-1)+1911 ([@日期])-1是少抓一字)
月=MID([@日期],FIND(“/”,[@日期])+1,2)
年月=LEFT([@日期],FIND(“/”,[@日期])-1)+1911 & MID([@日期],FIND(“/”,[@日期])+1,2)
注意這是文字必須轉成數字才能被比對到，因此使用減負運算將文字轉變成數字
年月=- -(LEFT([@日期],FIND(“/”,[@日期])-1)+1911 & MID([@日期],FIND(“/”,[@日期])+1,2))
建立[觀測範圍]，不然日期過長K線會太密，而看不出所以然
建立名稱管理員
公式 >名稱管理員> 新增
用MATCH找日期
用OFFSET動態定位選取儲存格範圍
MATCH(,,0) 完全相同
MATCH(,,1) 最後一筆
Excel圖表ChartObjects(chartname)的選取
ActiveSheet.ChartObjects(chartname).active
ActiveChart.FullSeriesCollection 圖表內線的集合
ActiveChart.FullSeriesCollection.Count 圖表內線的集合的數量
ActiveChart.FullSeriesCollection(i) 指定第i條線
ActiveChart.FullSeriesCollection(i).xValue 第i條線的x軸
xValue是X軸
Value 是Y軸
帶數字的[工作表名稱] 下[公式] 需要帶單引號 ’
Active.FullSeriesCollection(i).Values=”＝‘ “ & ActiveSheet.Name & “ ‘! ” & Split(StrRngName,” , ”)(i-1)
其中 Split(StrRngName,” , ”)(i) i是指分割後的第幾個
