Excel 2013教學 01 啟動Excel 2013軟體
Excel 2013教學 08 儲存檔案與申請SkyDrive雲端空間
Excel 2013教學 13 儲存格內容移動與調整儲存格大小
Excel 2013教學 25 資料剖析 分隔符號與日期格式轉換
Excel 2013教學 29 取得外部資料 從Access
Excel 2013教學 30 取得外部資料 從Web 取得匯率與股票資料
Excel 2013教學 46 設定格式化條件 醒目提示儲存格規則
Excel 2013教學 47 設定格式化條件 頂端底端儲存格規則與資料橫條
Excel 2013教學 52 從台灣證劵交易所開啟每日收盤行情CSV檔
Excel 2013教學 53 股票每日收盤行情資料整理與篩選
語法筆記
' ------------------------------------------------------------
' 查看目前開啟excel檔案數量
Dim OpenCnt as Integer
OpenCnt = Application.Workbooks.Count
' ------------------------------------------------------------
' 依序查已開檔名 - 方法一
Dim i As Integer
For i = 1 To Workbooks.Count
MsgBox i & " " & Workbooks(i).Name
Next
' ------------------------------------------------------------
' 依序查已開檔名 - 方法二
Dim my Sheet As WorkSheet
For Each mySheet In Worksheets
MsgBox mySheet.Name
Next mySheet
' ------------------------------------------------------------
' 開啟特定檔案 - 方法一
filename = "C:\VBA\test.xls"
Workbooks.Open filename
' ------------------------------------------------------------
' 開啟特定檔案 - 方法二
Dim filename As String
filename = "C:\ExcelVBA\test.xls"
Dim sn As Object
Set sn = Excel.Application
sn.Workbooks.Open filename
' sn.Workbooks(filename).Close ' 關閉
Set sn = Nothing
' ------------------------------------------------------------
' 關閉指定檔案, 不提示訊息
Dim filename As String
filename = "Test.xls" ' 這裡只可以給短名,給全名會錯
' 假設 Test.xls 已於開啟狀態
Application.DisplayAlerts = False ' 關閉警告訊息
Workbooks(filename).Close
Application.DisplayAlerts = True ' 再打開警告訊息
' ------------------------------------------------------------
' 關閉所有開啟檔案, 但留下主視窗
Workbooks.Close
' ------------------------------------------------------------
' 關閉 excel 程式
Application.Quit
' ------------------------------------------------------------
' 直接進行存檔
Dim filename As String
filename = "a.xls" ' 只可為短檔名
WorkBooks(filename).Save
' ------------------------------------------------------------
' 指定檔名進行另存新檔，並關閉
' 假設要將 "a.xls" 存成 "C:\b.xls"
Application.DisplayAlerts = False ' 關閉警告訊息
Workbooks("a.xls").SaveAs "C:\b.xls" ' 另存新檔
Workbooks("b.xls").Close ' 關閉 b.xls
Application.DisplayAlerts = True ' 開啟警告訊息
' ------------------------------------------------------------
' 指定當前活頁簿
Dim Caption as String
Caption = "a.xls"
Workbooks(Caption).Activate ' 將視窗切到 a.xls
可以使用ActiveSheet.Rows.Count與ActiveSheet.Columns.Count來取得
最後一列
Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
最後一欄
Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
例:
Worksheets("sheet1").Range("H2").Copy
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range("H3:h" & Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row)
Sub test()
For I = 1 To 100 ' 設定要抓到第幾列
Cells(I, 2) = Cells(I, 1).Hyperlinks(1).Address '超連結存放的位置
Next
End Sub![clip_image002[70]](http://lh3.ggpht.com/-Pd580LVCw6o/UVG8Ftyjb7I/AAAAAAAAXno/NZ49-_9Q0mA/s1600-h/clip_image002%25255B70%25255D%25255B2%25255D.png "clip_image002[70]"){kind=small}
![clip_image002[104]](http://lh5.ggpht.com/-6ZjjmTBKcd8/UVG8HDQzguI/AAAAAAAAXn4/5ZHXID_nr50/s1600-h/clip_image002%25255B104%25255D%25255B2%25255D.png "clip_image002[104]"){kind=small}
![clip_image002[158]](http://lh6.ggpht.com/-2X7iov-IBLw/UVG9axbVdcI/AAAAAAAAXuo/kk6LZWp4qhY/s1600-h/clip_image002%25255B158%25255D%25255B2%25255D.png "clip_image002[158]"){kind=small}
| 1-在skydrive建立資料夾與新增文件 |
![clip_image002[159]](http://lh5.ggpht.com/-2bXiMDaly5w/UVG9cXzyErI/AAAAAAAAXu4/JjbAt1o_13M/s1600-h/clip_image002%25255B159%25255D%25255B2%25255D.png "clip_image002[159]"){kind=small}
| 2-儲存檔案與上傳檔案到skydrive |
![clip_image002[160]](http://lh6.ggpht.com/-KUn9exR4kXw/UVG9duSKZLI/AAAAAAAAXvI/EF-HAhn2gQ8/s1600-h/clip_image002%25255B160%25255D%25255B2%25255D.png "clip_image002[160]"){kind=small}
| 3-Skydrive管理刪除與移動 |
![clip_image002[161]](http://lh6.ggpht.com/-_1wY6zv2Qes/UVG9e64pp7I/AAAAAAAAXvY/3RCXlSBPIPo/s1600-h/clip_image002%25255B161%25255D%25255B2%25255D.png "clip_image002[161]"){kind=small}
| 4-安裝SkyDrive 桌面應用程式 |
![clip_image002[162]](http://lh4.ggpht.com/-62wOpyRvxNw/UVG9gewp4jI/AAAAAAAAXvo/_dREd_lmHCo/s1600-h/clip_image002%25255B162%25255D%25255B2%25255D.png "clip_image002[162]"){kind=small}
| 5-使用Skydrive桌面傳資料 |
![clip_image002[163]](http://lh5.ggpht.com/-noQqK2R0QyU/UVG9hmK5yVI/AAAAAAAAXv4/ORiIAOXYDXM/s1600-h/clip_image002%25255B163%25255D%25255B2%25255D.png "clip_image002[163]"){kind=small}
| 6-Word登入skydrive帳號 |
![clip_image002[164]](http://lh3.ggpht.com/-z8dx_O28Els/UVG9jKd9XBI/AAAAAAAAXwI/6eAD6GFi0eU/s1600-h/clip_image002%25255B164%25255D%25255B2%25255D.png "clip_image002[164]"){kind=small}
| 7-Word將檔案儲存到SkyDrive中 |
![clip_image002[165]](http://lh6.ggpht.com/-UrCJFENblIE/UVG9kQcL6KI/AAAAAAAAXwY/vayvrW1cMFQ/s1600-h/clip_image002%25255B165%25255D%25255B2%25255D.png "clip_image002[165]"){kind=small}
| 8-Word套用標題樣式 |
![clip_image002[166]](http://lh5.ggpht.com/-dZIKLEIs3A8/UVG9l6Cc2dI/AAAAAAAAXwo/3njQN2Vr2Mc/s1600-h/clip_image002%25255B166%25255D%25255B2%25255D.png "clip_image002[166]"){kind=small}
| 9-Word文件格式設定 |
![clip_image002[167]](http://lh3.ggpht.com/-EgC8HdGJz64/UVG9nfjcZYI/AAAAAAAAXw4/-YuBczLRZyo/s1600-h/clip_image002%25255B167%25255D%25255B2%25255D.png "clip_image002[167]"){kind=small}
| 10-Word插入線上影片 |
![clip_image002[168]](http://lh4.ggpht.com/-59Iq34rhQrI/UVG9oxxPplI/AAAAAAAAXxI/CCFdv1JfneQ/s1600-h/clip_image002%25255B168%25255D%25255B2%25255D.png "clip_image002[168]"){kind=small}
| 11-Word從影片內嵌程式碼 |
![clip_image002[169]](http://lh3.ggpht.com/-ef3Egdns6uY/UVG9qZFIF0I/AAAAAAAAXxY/O3fySd-UfEs/s1600-h/clip_image002%25255B169%25255D%25255B2%25255D.png "clip_image002[169]"){kind=small}
| 12-Word從影片內嵌程式碼 |
![clip_image002[170]](http://lh5.ggpht.com/-5m3IbHAwrWw/UVG9r1I880I/AAAAAAAAXxo/eYfYRNZDidc/s1600-h/clip_image002%25255B170%25255D%25255B2%25255D.png "clip_image002[170]"){kind=small}
| 13-Word線上插入圖片 |
![clip_image002[171]](http://lh6.ggpht.com/-XUEAs6LyhRQ/UVG9tZnrjRI/AAAAAAAAXx4/fByE_K49s5g/s1600-h/clip_image002%25255B171%25255D%25255B2%25255D.png "clip_image002[171]"){kind=small}
| 14-觸控模式與閱讀版面 |
![clip_image002[172]](http://lh4.ggpht.com/-6B-iYq_byzI/UVG9vgrcWPI/AAAAAAAAXyI/uAJUXlk1Cr8/s1600-h/clip_image002%25255B172%25255D%25255B2%25255D.png "clip_image002[172]"){kind=small}
| 15-展開摺疊與註解文件 |
![clip_image002[173]](http://lh3.ggpht.com/-PeYnDueYWa8/UVG9w6zWU4I/AAAAAAAAXyY/P-PeFlZ0n_Q/s1600-h/clip_image002%25255B173%25255D%25255B2%25255D.png "clip_image002[173]"){kind=small}
| 16-將檔案儲存成PDF與直接編輯PDF檔 |
![clip_image002[174]](http://lh6.ggpht.com/-OrxB5fWkji0/UVG9yf-rBiI/AAAAAAAAXyo/CzL33lDr98Y/s1600-h/clip_image002%25255B174%25255D%25255B2%25255D.png "clip_image002[174]"){kind=small}
| 17-Excel 快速填入 |
![clip_image002[175]](http://lh3.ggpht.com/--DjSJ4dLWn8/UVG9ziunbGI/AAAAAAAAXy4/6jOKwRz1YzM/s1600-h/clip_image002%25255B175%25255D%25255B2%25255D.png "clip_image002[175]"){kind=small}
| 18-Excel 快速填入 |
![clip_image002[176]](http://lh4.ggpht.com/-31i7B9ROEOA/UVG91Nv6huI/AAAAAAAAXzI/w1M2wT8320Y/s1600-h/clip_image002%25255B176%25255D%25255B2%25255D.png "clip_image002[176]"){kind=small}
| 19-Excel快速分析 |
![clip_image002[177]](http://lh6.ggpht.com/-pEn95KwXhKw/UVG9236RuOI/AAAAAAAAXzY/IkbZsnrHih8/s1600-h/clip_image002%25255B177%25255D%25255B2%25255D.png "clip_image002[177]"){kind=small}
| 20-Excel 圖表 |
![clip_image002[178]](http://lh6.ggpht.com/-mY0SgV18Bto/UVG94BFQvYI/AAAAAAAAXzo/5EJZaXnUDKw/s1600-h/clip_image002%25255B178%25255D%25255B2%25255D.png "clip_image002[178]"){kind=small}
| 21-Excel 圖表 |
![clip_image002[179]](http://lh5.ggpht.com/--OCUIzsq9xI/UVG950twPHI/AAAAAAAAXz4/HL5xueUChcQ/s1600-h/clip_image002%25255B179%25255D%25255B2%25255D.png "clip_image002[179]"){kind=small}
| 22-Excel 佈景主題 |
![clip_image002[180]](http://lh4.ggpht.com/-jv6W7PEFdP8/UVG97fEdooI/AAAAAAAAX0I/Rtn9E1o3vpM/s1600-h/clip_image002%25255B180%25255D%25255B2%25255D.png "clip_image002[180]"){kind=small}
| 23-Excel Bing Maps地圖分析 |
![clip_image002[181]](http://lh6.ggpht.com/-wVChBvWI0is/UVG987wtMLI/AAAAAAAAX0Y/qJ4VlaADawU/s1600-h/clip_image002%25255B181%25255D%25255B2%25255D.png "clip_image002[181]"){kind=small}
| 24-Excel PowerView說明 |
![clip_image002[182]](http://lh4.ggpht.com/-P1P4I9RA_d0/UVG9-d1xkEI/AAAAAAAAX0o/8jse_qFAQmI/s1600-h/clip_image002%25255B182%25255D%25255B2%25255D.png "clip_image002[182]"){kind=small}
| 25-Excel 建議的樞紐分析表與時間軸 |
![clip_image002[183]](http://lh6.ggpht.com/-_-cnMyr7xgU/UVG9_p7KKsI/AAAAAAAAX04/fPI_RF-urYM/s1600-h/clip_image002%25255B183%25255D%25255B2%25255D.png "clip_image002[183]"){kind=small}
| 26-Excel-交叉分析篩選器 |
![clip_image002[184]](http://lh4.ggpht.com/-UyrJwviQU10/UVG-BFqyVzI/AAAAAAAAX1I/Cg-jmuEeykw/s1600-h/clip_image002%25255B184%25255D%25255B2%25255D.png "clip_image002[184]"){kind=small}
| 27-PowerPoint 版面與動畫 |
DATEDIF
語法：DATEDIF(start_date,end_date,unit)
語法：DATEDIF(起始日期,終止日期,單位)
Unit說明
"Y"：週期中的整年數
"M"：週期中的整月數
"D"：週期中的天數
"MD"：start_date與end_date間的天數差。(忽略日期中的月和年)
"YM"：start_date與end_date間的月數差。(忽略日期中的日和年)
"YD"：start_date與end_date間的天數差。(忽略日期中的年)
計算年練習
=DATEDIF($H4,TODAY(),"Y")
計算月練習
=DATEDIF($H4,TODAY(),"YM")
計算日練習
=DATEDIF($H4,TODAY(),"MD")
1.選取資料範圍
2.右鍵 / 儲存格格式 / 數值
3.點自訂
4.在G/通用格式前後，輸入需要的單位內容 (前後加上""雙引號)
5.確定
連結 &
1.輸入 =
2.點要連結的儲存格編號
3.確定
函數Concatenate
<符號分割>
1.複製資料
2.將資料貼上到Excel中 / 選取資料分割的範圍
3.資料 / 資料剖析
4.點選分隔符號 / 下一步
5.設定分隔符號 / 下一步
6.設定欄位資料格式 / 完成
底下為分割好的資料結果
<固定寬度分割>
1.選取資料範圍
2.資料 / 資料剖析
3.點固定寬度 / 下一步
4.點選資料要切割的地方加入分欄線 / 下一步
5.點選確定
底下為完成切割的資料內容
1.選取資料範圍
2.資料 / 合併彙算
3.選取要使用統計的函數
4.選取要統計的參照位址 / 新增
5.勾選標籤名稱 / 確定
完成統計的結果
1.點一下要分組計算的欄位名稱
2.資料 / 排序
3.資料 / 小計
4.設定要分組的欄位
5.設定要計算的函數
6.勾選要統計的欄位
7.確定
完成後可以使用群組大綱功能摺疊資料
一、 函數
1.點選要插入函數的儲存格
2.點選 fx貼上函數
3.在「函數類別」內選擇 [全部]
4.點選一下要使用函數的名稱
5.輸入要使用函數開頭的字母
6.選擇要使用的函數
7.設定函數所需要的參數值或範圍
8.[確定]
函數引數對話方塊 : Shift + F3
--------------------------------------------------------------------------------
【Sum】加總
Number1
選取要計算的範圍
可用 Shift 或Ctrl搭配選取
--------------------------------------------------------------------------------
【Average】平均
Number1
選取要計算的範圍
可用Shift 或Ctrl搭配選取
--------------------------------------------------------------------------------
【Max】最大值
Number1
選取要計算的範圍
可用Shift 或Ctrl搭配選取
--------------------------------------------------------------------------------
【Min】最小值
Number1
選取要計算的範圍
可用Shift 或Ctrl搭配選取
--------------------------------------------------------------------------------
【Count】計算數值儲存格的數量
Valus1
選取要計算的範圍
可用Shift 或Ctrl搭配選取
--------------------------------------------------------------------------------
【Counta】計 算非空白儲存格的數量
Valus1
選取要計算的範圍
可用Shift 或Ctrl搭配選取
--------------------------------------------------------------------------------
【Today】取現 在的日期
不用選任何範圍
抓取系統的日期
F9 : 更新
手動取 得方式
日期 : Ctrl + ；
時間 : Ctrl + Shift +；
--------------------------------------------------------------------------------
【Now】取現在的 時間及日期
不用選任何範圍
抓取系統的日期及時間
F9 : 更新
若 格式無法顯示，請更改儲存格的格式
--------------------------------------------------------------------------------
【IF】判斷式
Logical_test 判斷的條件
Value_if_true 條件成立的結果
Value_if_false 條件不成立的結果
TRUE : 真的 成立
FALSE : 假的 不成立
--------------------------------------------------------------------------------
【COUNTIF】 判斷符合條件的儲存格
Range 要判斷的範圍
Criteria 判斷的條件
--------------------------------------------------------------------------------
【RANK】排序 (計算名次)
Number 要比較的值
Ref 比較的範圍 (固定範圍要鎖定)
Order 排序的方式
0 或 空白 : 遞減
非0或空白的數值 : 遞增
--------------------------------------------------------------------------------
【SUMIF】有條件加總
Range 要判斷加總的範圍 (範圍固定絕對參照)
Criteria 判斷的條件
Sum_range 符合條件要加的範圍 (範圍固定絕對參照)
--------------------------------------------------------------------------------
【Month】抓取日期中的月份
Serial_number 選取或輸入日期
--------------------------------------------------------------------------------
二、 設定格式化條件
醒目提示儲存格規則
1.選取要設定的儲存格範圍
2.點選常用功能標籤
3.設定格式化條件
4.醒目提示儲存格規則
5.選擇要判斷的條件方式
6.設定條件值 及 色彩
7.確定
頂端與底端儲存格規則
1.選取要設定的資料範圍
2.點選常用功能標籤
3.設定格式化條件
4.頂端/底端項目規則 / 前10個項目
5.設定項目個數與格式 / 確定
資料橫條
1.選取要設定的資料範圍
2.點選常用功能標籤
3.設定格式化條件
4.資料橫條 / 選擇樣式
底下為設定完成的結果
圖示集
1.選取要設定的資料範圍
2.點選常用功能標籤
3.設定格式化條件
4.圖示集 / 其他規則
5.設定圖示集樣式與規則條件 / 確定
色階
1.先排序要格式化的資料
2.設定格式化條件 / 色階 / 選擇要使用的樣式
--------------------------------------------------------------------------------
三、 插入表格
1點選插入功能標籤
2.點選表格按鈕
3.選取要建立表格的範圍
4.確定
--------------------------------------------------------------------------------
四、達成率計算
公式為 : 達成業績 / 業績目標
--------------------------------------------------------------------------------
五、移除重複資料
1.選取資料範圍
2.資料 / 移除重複
3.點選依照目前的選取範圍排序 / 移除重複
4.點選確定
5.完成後會出現底下訊息
--------------------------------------------------------------------------------
六、資料排序
1.先點選資料
2. 資料 / 排序
3.設定要排序的名稱與規則
4.確定
多鍵值排序
圖示排序
儲存格色彩排序
自訂排序
1.設定要排序的欄位與順序點選自訂清單
2.輸入清單規則 / 新增 / 確定
3.點選確定
七、資料篩選
1.點一下資料
2.資料 / 篩選
3.點選要篩選的欄位 / 勾選要的條件
<日期函數名稱>
今天 TODAY
現在 NOW
年 YEAR
月 MONTH
日 DAY
時 HOUR
分 MINUTE
秒 SECOND
一、插入樞紐分析表
1.點一下要分析的資料
2.插入 / 樞紐分析表
3.設定資料範圍與樞紐分析表的位置
4.確定
1.校閱 / 保護活頁簿
2.輸入密碼
3.再輸入一次 / 確定
4.完成後工作表就無法編修
1.選取要設定的儲存格
2.右鍵 / 儲存格格式 / 保護
3.鎖定 =>若勾選的話，表示無法輸入儲存格資料
隱藏=>若勾選的話會將公式隱藏不顯示
4.確定
5.校閱 / 保護工作表
6.輸入密碼 /確定
7.完成之後若對有保護的儲存格變更，就會出現下列的訊息
