EXCEL 進階應用
壹. 函數之應用 貳. 巨集建置與VBA 介紹

壹.函數之應用

數值函數

sum(引數1,引數2,...引數30)
average(引數1,引數2,...引數30)

round(數值, 指定的位數)

指定的位數 >0,將數值四捨五入至小數點右側第幾位

指定的位數 =0,將數值四捨五入至整數位

指定的位數 <0,將數值四捨五入至小數點左側第幾位

sqrt(數值)
max(引數1,引數2,...引數30)
min(引數1,引數2,...引數30)

int(數值)

無條件捨去小數位數, 只擷取整數位數

mod(被除數, 除數)

sumif(資料範圍,準則條件,實際加總資料的範圍)

將符合條件的部份儲存格作加總的動作

文字函數

left(文字串,指定的字元數)

指定的字元數必須大於或等於 0; 如果省略指定的字元數,則假設其值為 1

leftb(文字串,指定的字元數)

指定的字元數指定您要選錄的字元數(依據位元組)

範例

leftb("台北市 內湖區",4) 等於 "台北"

right(文字串,指定的字元數)

rightb(文字串,指定的字元數)

len(文字串)

value(文字串)

將代表某數值的文字字串轉換成數字資料

concatenate(引數1,引數2,...引數30)

連結數個文字串

日期與時間函數

today() 傳回電腦系統時鐘的現在日期
datevalue(date_text) 將文字形式的日期轉換成序列值serial-number
networkdays((start_date,end_date,holidays)
傳回 start_date(開始日期)與 end_date(結束日期)間的全工作天的數目;工作天不包括週末與任何假日
year(日期資料) 傳回對應於日期的年份
month(日期資料) 傳回日期的月份
day(日期資料) 傳回月份中的日期

統計函數

count(引數1,引數2,...引數30)

如果引數是一個陣列或參照,則只計算這個陣列或參照中的數字。這個陣列或參照中的空白儲存格、邏輯值、文字或錯誤值等,都不予計算。如果您需要計算邏輯值、文字或錯誤值,您可以使用 COUNTA 函數。

counta(引數1,引數2,...引數30)

計算不是空白的儲存格數量; 包含空文字 (""),但是不包含空的儲存格

countif(資料範圍,準則條件)

求出範圍內有多少筆是符合條件的

large(陣列, k)

傳回資料組中第 k 個最大的數值

範例

large({3,4,5,2,3,4,5,6,4,7},3) 等於 5

large({3,4,5,2,3,4,5,6,4,7},7) 等於 4

small(陣列, k)

rank(數值資料,資料範圍,指定的順序)

數值資料是要知道等級的數字

資料範圍是一個數值陣列或數值參照位址,非數值將被忽略

指定的順序為 0或被省略,則 Microsoft Excel 把資料範圍當成從大到小排序來評定指定的順序的等級

指定的順序不是0,則 Microsoft Excel 把資料範圍當成從小到大排序來評定指定的順序的等級

檢視與參照函數

vlookup(搜尋值,陣列,傳回陣列第幾欄,搜尋方式)

搜尋值: 輸入依據的儲存格名稱

陣列: 輸入查詢準則的範圍, 記得要採用絕對位址

陣列的第幾欄: 輸入要查詢的資料是在查詢準則範圍的第幾個欄位

搜尋方式: true尋找最接近的值; false尋找完全一樣的值

match(搜尋值,儲存格範圍或陣列,搜尋型態)

搜尋型態 1(default): 找到等於或小於搜尋值最接近的資料,但資料必須由小到大排列

搜尋型態 0: 找到完全等於搜尋值的資料

搜尋型態 -1: 找到大於或等於搜尋值最接近的資料,但資料必須由大到小排列

財務函數

pmt(rate,nper,pv,fv,type)

傳回每期付款金額及利率固定之年金期付款數額

rate: 為各期的利率

nper: 為年金的總付款期數(rate 和 nper 的衡量單位必須一致)

pv: 為未來各期年金現值的總和, 如貸款總額

fv: 為最後一次付款完成後,所能獲得的現金餘額(年金終值)如果省略 fv 引數,會自動假定為 0 ,也就是說,貸款的年金終值是 0

type: 用以界定各期金額的給付時點; 0 或省略為期末給付 ,1 為期初給付

syd(cost,salvage,life,per)

按年數合計法,傳回資產在指定期間內的折舊金額

cost: 資產原始成本

slavage: 耐用年限結束時之殘餘價值

life: 資產估計可用年限

per: 要計算的期間,必須與life採用相同單位

db(cost,salvage,life,period,month)

傳回以固定餘額遞減法計算之一定期間內資產的折舊

cost 資產的原始成本。

salvage 指資產耐用年限終了時之價值 (亦稱為資產的殘值)。

life 指資產可折舊的年數(亦稱為資產的耐用年限)。

period 指定所要計算折舊的期間。Period 必須與 life 使用相同的衡量單位。

month 第一年的月份數。如果 month 引數被省略,則假定其值為 12

npv( rate, value1, value2, ....)

rate: 貼現率或市場利率

value1, value2,... : 各期的現金流出入量,時間間距必須相同,且於每一期的期末;必須依序輸入且用正負表示收入及虧損

若第一筆現金流量發生於第一段期間的期初,第一個數值不可包含在value 裡, 必須另外將其加入NPV的計算結果

貳. 巨集建置與VBA 介紹

A. 建立巨集範例

1.顯示visual basic工具列

檢視->工具列->visual basic

2.錄製一個指定貨幣格式的巨集

  1. 在工作表中選取儲存格D7到D8
  2. 在visual basic工具列中,點選錄製巨集按鈕
  3. 以formatcurrency的名稱取代原有預設的巨集名稱(macro#)
  4. 為巨集建立一個快速鍵,例如 ctrl+shift+c
  5. 選儲存格格式,按下數字定位格
  6. 在類別(C)清單選貨幣,小數點位數輸入0
  7. 點選停止錄製按鈕
  8. 儲存活頁簿檔案

3. 執行巨集

  1. 在工作表中任選取儲存格範圍A2:D4
  2. 按下快速鍵ctrl+shift+c 執行巨集; 或在visual basic工具列中點選執行巨集按鈕, 在清單中選擇formatcurrency 巨集執行

4. 開啟visual basic編輯器觀看formatcurrency巨集

5. 錄製移除格線的巨集

  1. 點選錄製巨集按鈕
  2. 預設巨集名稱為 rmgrid
  3. 將 工具->選項->檢視->格線的 檢查方塊清除(此格線會被移除)
  4. 點選停止錄製按鈕
  5. 儲存活頁簿檔案

6. 按執行巨集按鈕,選rmgrid巨集, 按編輯檢視巨集的程式碼

7. 自訂函數的建置範例 成績-VBA程式.XLS

8. 如何使用自訂的函數

a.在要使用自訂函數的儲存格內,鍵入=

b.鍵入函數名稱,左括號,引數,右括號, 按Enter鍵

(即在Excel工作表點選一個儲存格,執行 插入->函數,在函數類別選使用者自訂,

右邊會列出我們建立的自訂函數)

上例是在Excel工作表,點一下儲存格J6, 在資料編輯列輸入 =grade(I6) 按Enter鍵

9. 說明

巨集使用VBA語言來設計,所以它是一個程式.

指令程序(Sub Procedure)


指令巨集
是一連串指令的集合,指使excel去完成某些動作,已達到完成一項工作的預定目標.可用巨集錄製器來錄製.

函數程序(Function Procedure)

函數巨集

 

是Excel提供的工作表函數和使用者的計算公式的組合.它可置於工作表的儲存格,在使用者提供的引數下,計算並傳回其結果數值. 無法用巨集錄製器來錄製.

書寫語法:

指令程序(Sub Procedure) Sub 程序名稱()
....
......
End Sub
函數程序(Function Procedure) Function 函數名稱(引數,引數,..)
....
......
End Function

B. visual basic 編輯器視窗含有其他的視窗:

專案總管 屬性 程式碼 區域變數 即時運算 監看

B-1 專案總管視窗

專案總管視窗列出Excel載入的各專案VBA Project的資訊. 在Excel裡,一個專案

代表一個活頁簿 Workbook. 專案裡的物件資料夾,內含活頁簿裡各工作表及活頁簿

本身. 模組資料夾內含活頁簿裡的VBA模組module.

B-2 屬性視窗

屬性視窗隨選取的物件不同而不同. Excel工作表Worksheet物件有12項屬性,

模組只有一項屬性,表單有7大類共35項屬性. 大部分採用員設定值,只有名稱

name及標題Caption二項常用到.

B-3 程式碼視窗

visual basic編輯器最主要的視窗,用來撰寫編輯巨集的程式碼.

B-4 模組(Module)

模組是由儲存為一體的VBA的宣告和程序組成的集合.模組中的程序(巨集的主體)

可以是函數Function程序或是一般Sub程序.在專案總管視窗的模組資料夾裡,含有很多

模組如module1,module2等.

C.

C-1. Excel的物件(Object),屬性(Property),方法(Method),及事件(Event)

Object Property Method Event
  1. Application應用程式
  2. Chart圖表
  3. Dialog內建對話方塊
  4. Filter篩選
  5. Name名稱
  6. PivotTable樞紐分析表
  7. Range範圍
  8. Sheets活頁表群體
  9. Window視窗
  10. Workbook活頁簿
  11. Worksheet工作表 等等
  1. ActiveCell現用儲存格
  2. ActiveSheet現用活頁簿
  3. Bold粗體
  4. Caption標題
  5. Cells
  6. Color顏色
  7. CutCopyMode剪下複製模式
  8. DisplayFullScreen全螢幕展示
  9. Enabled已啟動
  10. EntireColumn整行
  11. Font字型
  12. Formula公式
  13. ThisWorkbook這本活頁簿 等等
  1. Activate
  2. BorderAround
  3. Clear
  4. Copy
  5. Delete
  6. DialogBox
  7. Export
  8. Find
  9. Open
  10. Paste
  11. Quit
  12. Refresh
  13. Select
  14. Show等等

 

 

 

  1. Application的NewWorkbook
  2. SheetActivate
  3. SheetChange
  4. WindowActivate
  5. SelectionChange 

  6. 等等

 

 

 

 

 

 

 

 

 

 

 

 


C-2. 物件(Object),屬性(Property),方法(Method)及事件(Event)的組合語法:

一.  物件.屬性(分為A,B二類型)
A.設定物件屬性值 B.傳回物件屬性值  
物件.屬性.屬性...=設定值 變數=物件.屬性

例:

Selection.Value="Welcome All"

Selection.Font.Size=18

Application.DisplayFullScreen=True

 

diffrow1=ActiveCell.Value

 

 

二.  物件.方法
將物件依此方法來動作 例: Worksheets("SalesTp).Select

C-3. 如何選用物件,屬性,方法或事件

開啟visual basic編輯器,執行 檢視->溜覽物件 出現溜覽物件對話方塊如下圖:

左邊"物件類別''清單裡出現該程式庫內所有物件,而右邊''成員"清單則出現左邊

清單裡選取物件項目的屬性或方法或事件,此時下方的"說明"欄出現選取的物件

或選取的屬性或方法或事件的說明及所在位置.

D. VBA程式碼範例解說


Public Sub Changefont()
Selection.Font.Italic = True '將選取區的字型設為斜體
Selection.Font.Bold = True '將選取區的字型設為粗體
Selection.Font.Name = "標楷體" '將選取區的字型設為標楷體
Selection.Font.Size = 26 '將選取區的字型大小設為 26
Selection.Font.Color = RGB(0, 0, 255) '將選取區的字型設為藍色
End Sub
Public Function Grade(score)
    Select Case score
        Case Is >= 90
            Grade = "優"
        Case Is >= 80
            Grade = "甲"
        Case Is >= 70
            Grade = "乙"
        Case Is >= 60
            Grade = "丙"
        Case Else
            Grade = "丁"
    End Select
End Function
Public Sub ForNext()
  Dim a As Byte, area As String
  For a = 6 To 22
  area = "B" & a
  Range(area).Font.Color = RGB(255, 0, 255)
  Range(area).Font.Name = "標楷體"
  Range(area).Font.Strikethrough = True
  Next a
End Sub
Public Sub DoLoop()
  Dim a As Byte, area As String
  a = 6
  Do
  area = "B" & a
  Range(area).Font.Color = RGB(255, 0, 255)
  Range(area).Font.Name = "標楷體"
  Range(area).Font.Strikethrough = True
  a = a + 1
  If a > 22 Then Exit Do
  Loop
End Sub

Public Sub DoWhile_Loop()
Dim area As String, Number As Byte
area = "B6"
Number = 6
Do While Range(area) <> Empty
Range(area).Font.Color = RGB(255, 0, 255)
Range(area).Font.Name = "標楷體"
Range(area).Font.Strikethrough = True
Number = Number + 1
area = "B" & Number
Loop
End Sub
Public Sub HappyWin()
Dim response As Byte
response = MsgBox("快快樂樂學 Excel 2000", vbAbortRetryIgnore, _
"文淵閣工作室")

Select Case response
Case vbAbort
MsgBox "謝謝您的使用!文淵閣工作室祝您身體健康", , _
"系統回應..."
Application.Quit
Case vbRetry
MsgBox "重新連線中,請稍候。", , "系統回應..."
Case vbIgnore
MsgBox "無法接通,請待會再撥", , "系統回應..."
End Select
End Sub Public Sub InputSample()
InputBox "這是 InputBox 範例"
End Sub
Public Sub SheetRename()
Dim response As String
response = InputBox("請輸入新工作表名稱", _
"重新命名", "快快樂樂學 Office 2000")
ActiveSheet.Name = response
End Sub

E.參考視窗