EXCEL-函數(shù)-宏-VBA-入門知識.ppt

1、,Excel 函數(shù),宏-VBA,What can you get from here?,如何設(shè)置EXCEL國際象棋棋盤式底紋 什么是VB，VBS，VBA和宏 如何對一張表所有紅色字體的數(shù)據(jù)求和 Burn-in自動安裝腳本究竟有何訣竅,CONTENTS,EXCEL 函數(shù)概述 EXCEL 宏-VBA介紹 VBS在測試中的應(yīng)用 Q/A,EXCEL 函數(shù)概述,AVERAGE函數(shù) 函數(shù)名稱：AVERAGE 主要功能：求出所有參數(shù)的算術(shù)平均值。 使用格式：AVERAGE(number1,number2,) 參數(shù)說明：number1,number2,：需要求平均值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個

2、。 應(yīng)用舉例：在B8單元格中輸入公式：=AVERAGE(B7:D7,F7:H7,7,8)，確認后，即可求出B7至D7區(qū)域、F7至H7區(qū)域中的數(shù)值和7、8的平均值。 特別提醒：如果引用區(qū)域中包含“0”值單元格，則計算在內(nèi)；如果引用區(qū)域中包含空白或字符單元格，則不計算在內(nèi)。,EXCEL 函數(shù)概述,CONCATENATE函數(shù) 函數(shù)名稱：CONCATENATE 主要功能：將多個字符文本或單元格中的數(shù)據(jù)連接在一起，顯示在一個單元格中。 使用格式：CONCATENATE(Text1，Text) 參數(shù)說明：Text1、Text2為需要連接的字符文本或引用的單元格。 應(yīng)用舉例：在C14單元格中輸入公式：=CO

3、NCATENATE(A14,B14,.com)，確認后，即可將A14單元格中字符、B14單元格中的字符和.com連接成一個整體，顯示在C14單元格中。 特別提醒：如果將上述公式改為：=A14&B14&.com，也能達到相同的目的。,EXCEL 函數(shù)概述,COUNTIF函數(shù) 函數(shù)名稱：COUNTIF 主要功能：統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目。 使用格式：COUNTIF ( Range, Criteria) 參數(shù)說明：Range代表要統(tǒng)計的單元格區(qū)域，Criteria表示指定的條件表達式。 應(yīng)用舉例：在C17單元格中輸入公式：=COUNTIF(B1:B13,=80)，確認后，即可統(tǒng)計

4、出B1至B13單元格區(qū)域中，數(shù)值大于等于80的單元格數(shù)目。 特別提醒：允許引用的單元格區(qū)域中有空白單元格出現(xiàn)。,EXCEL 函數(shù)概述,DATEDIF 函數(shù) 函數(shù)名稱：DATEDIF 主要功能：計算返回兩個日期參數(shù)的差值。 使用格式：=DATEDIF(date1,date2,“y(m ,d)”) 參數(shù)說明：date1代表前面一個日期，date2代表后面一個日期；y（m、d）要求返回兩個日期相差的年（月、天）數(shù)。 應(yīng)用舉例：在C23單元格中輸入公式：=DATEDIF(A23,TODAY(),y) 特別提醒：這是Excel中的一個隱藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降?，可以直接輸入使用，對于計算年齡、工齡

5、等非常有效。,EXCEL 函數(shù)概述,IF 函數(shù) 函數(shù)名稱：IF 主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容。 使用格式：=IF(Logical,Value_if_true,Value_if_false) 參數(shù)說明：Logical代表邏輯判斷表達式； Value_if_true表示當(dāng)判斷條件為邏輯“真（TRUE）”時的顯示內(nèi)容 ，Value_if_false表示當(dāng)判斷條件為邏輯“假（FALSE）”時的顯示內(nèi)容 應(yīng)用舉例：在C29單元格中輸入公式：=IF(C26=18,“符合要求”,“不符合要求”),EXCEL 函數(shù)概述,SUMIF函數(shù) 函數(shù)名稱：SUMIF 主要功能：計算符合

6、指定條件的單元格區(qū)域內(nèi)的數(shù)值和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 參數(shù)說明：Range代表條件判斷的單元格區(qū)域；Criteria為指定條件表達式；Sum_Range代表需要計算的數(shù)值所在的單元格區(qū)域。 應(yīng)用舉例：在D64單元格中輸入公式：=SUMIF(C2:C63,男,D2:D63)，確認后即可求出“男”生的語文成績和。 “=SUM(LARGE(D2:D63,1,2,3,4,5)” 特別提醒：其中“男”和“女”由于是文本型的，需要放在英文狀態(tài)下的雙引號（男、女）中。,EXCEL 函數(shù)概述,RANK函數(shù) 函數(shù)名稱：RANK 主要功能：返回某一數(shù)值在一列數(shù)

7、值中的相對于其他數(shù)值的排位。 使用格式：RANK（Number,ref,order） 參數(shù)說明：Number代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；order代表排序方式參數(shù)（如果為“0”或者忽略，則按降序排名，即數(shù)值越大，排名結(jié)果數(shù)值越??；如果為非“0”值，則按升序排名，即數(shù)值越大，排名結(jié)果數(shù)值越大；）。 應(yīng)用舉例：如在C2單元格中輸入公式：=RANK(B2,$B$2:$B$31,0)，確認后即可得出丁1同學(xué)的語文成績在全班成績中的排名結(jié)果。 特別提醒：我們讓Number參數(shù)采取了相對引用形式，而讓ref參數(shù)采取了絕對引用形式（增加了一個“$”符號），這樣設(shè)置后，選中C2單元

8、格，將鼠標移至該單元格右下角，成細十字線狀時（通常稱之為“填充柄”），按住左鍵向下拖拉，即可將上述公式快速復(fù)制到C列下面的單元格中，完成其他同學(xué)語文成績的排名統(tǒng)計。,EXCEL 函數(shù)概述,SUBTOTAL函數(shù) 函數(shù)名稱：SUBTOTAL 主要功能：返回列表或數(shù)據(jù)庫中的分類匯總。 使用格式：SUBTOTAL(function_num, ref1, ref2, .) 參數(shù)說明：Function_num為1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數(shù)字，用來指定使用什么函數(shù)在列表中進行分類匯總計算（如圖6）；ref1, ref2,代表要進行分類匯總區(qū)域或引用，不超過29個。 應(yīng)用舉例：

9、在B64和C64單元格中分別輸入公式：=SUBTOTAL(3,C2:C63)和=SUBTOTAL103,C2:C63)，并且將61行隱藏起來，確認后，前者顯示為62（包括隱藏的行），后者顯示為61，不包括隱藏的行。,EXCEL 函數(shù)概述,LEFT函數(shù)/RIGHT函數(shù) 函數(shù)名稱：LEFT 主要功能：從一個文本字符串的第一個字符開始，截取指定數(shù)目的字符。 使用格式：LEFT(text,num_chars)， 參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。 應(yīng)用舉例：假定A38單元格中保存了“我喜歡天極網(wǎng)”的字符串，我們在C38單元格中輸入公式：=LEFT(A38,3

10、)，確認后即顯示出“我喜歡”的字符。特別提醒：此函數(shù)名的英文意思為“左”，即從左邊截取，Excel很多函數(shù)都取其英文的意思。,EXCEL 函數(shù)概述,MID函數(shù) 函數(shù)名稱：MID 主要功能：從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。 使用格式：MID(text,start_num,num_chars) 參數(shù)說明：text代表一個文本字符串；start_num表示指定的起始位置；num_chars表示要截取的數(shù)目。 應(yīng)用舉例：假定A47單元格中保存了“我喜歡天極網(wǎng)”的字符串，我們在C47單元格中輸入公式：=MID(A47,4,3)，確認后即顯示出“天極網(wǎng)”的字符。 特別提醒：公式中各參數(shù)

11、間，要用英文狀態(tài)下的逗號“,”隔開。,EXCEL 函數(shù)概述,MOD函數(shù) 函數(shù)名稱：MOD 主要功能：求出兩數(shù)相除的余數(shù)。 使用格式：MOD(number,divisor) 參數(shù)說明：number代表被除數(shù)；divisor代表除數(shù)。 應(yīng)用舉例：輸入公式：=MOD(13,4)，確認后顯示出結(jié)果“1”。 特別提醒：如果divisor參數(shù)為零，則顯示錯誤值“#DIV/0!”；MOD函數(shù)可以借用函數(shù)INT來表示：上述公式可以修改為：=13-4*INT(13/4)。,輕松一刻 -excel,* 在word中分別輸入三個“=”，輸入三個“”或輸入三個“*”或 “-”或 “#”，回車。有驚喜哦 * 如果希望設(shè)

12、置國際象棋棋盤式底紋(白色自定義色)話，應(yīng)該可以的，不妨試試哦,小竅門！,=MOD(ROW()+COLUMN(),2)0， =MOD(ROW()+COLUMN(),2)=0,Excel宏,VBA,挑戰(zhàn)第一關(guān),善用宏簡化工作,什么是宏,宏是一段定義好的操作，它可以是一段程序代碼，也可以是一連串的指令集合。 宏的作用可以使頻繁執(zhí)行的動作自動化 節(jié)省時間，提高工作效率，又能減少失誤,開始錄制宏,操作步驟,完成宏錄制,運行宏,錄制宏 需要經(jīng)常重復(fù)執(zhí)行的某項任務(wù)，就可以把執(zhí)行這些任務(wù)的步驟全部錄制在宏里，把宏變?yōu)榭勺詣訄?zhí)行的任務(wù),執(zhí)行宏的操作 執(zhí)行宏的命令 在工具欄上設(shè)置宏按鈕 使用窗體控件 使用圖形對

13、象執(zhí)行宏,宏的結(jié)構(gòu),Sub 主界面() 主界面 Macro Coren Lee 記錄的宏 2002-11-5 Sheets(主界面).Select End Sub,宏名,批注,宏程序,挑戰(zhàn)第二關(guān),深入淺出VBA,VBA及其特點,什么是VBA ? VBA（Visual Basic for Applications）是Microsoft Office 系列的內(nèi)置編程語言，即應(yīng)用程序中的VB。它“寄生于”O(jiān)ffice應(yīng)用程序，是Office 套裝軟件的一個重要組件。 VBA編程優(yōu)點 1程序只起輔助作用，大部分功能用Office的，因此簡化了程序設(shè)計。 2通過宏錄制，可以部分地實現(xiàn)程序設(shè)計的自動化。

14、3發(fā)行方便，只要發(fā)行含有VBA代碼的文檔或模板即可。 4. Office界面對于廣大計算機應(yīng)用人員來說比較熟悉，符合一般操作人員的使用習(xí)慣，便于軟件推廣應(yīng)用。 5用VBA編程非常簡單。,為何要使用 VBA 宏缺少伸縮的彈性 記錄了許多不需要的步驟 記錄了許多不需要的資料 無法使用較深入的功能 無法完成重復(fù)性的工作 無法完成需要條件判斷的工作,宏,VBA,Visual Basic for Application,VBA與Visual Basic的差異,Visual Basic以界面為中心的開發(fā)方式 重點在使用者如何進行操作 VBA以文件為中心的開發(fā)方式 重點在使用者最后看到什么,Visual B

15、asic編輯器,用來編輯宏程序或制作更高階的整合性應(yīng)用程序 附屬于Office之下，使用較少的系統(tǒng)資源，達到編輯應(yīng)用程序的目的。,什么是工程,工程就是模塊的集合，包括： 用戶窗體 文件，例如Excel中的工作表以及圖表工作表 模塊 類模塊 ActiveX文件 引用的其他工程,VBA程序的基本架構(gòu),對象 面向?qū)ο蟮拈_發(fā)方式 VBA是以操作Office對象為目的的程序語言 屬性 對象所屬的特性或特征 方法 控制對象的內(nèi)建動作或功能，以便可以讓程序順利打開 事件 發(fā)生于對象上的事情,Excel對象結(jié)構(gòu),1.Application對象（代表整個Excel應(yīng)用程序） 2.Workbook（工作簿，即是說

16、Excel文件） 3.Worksheet（ 工作表，即工作簿的“頁”） 4.Range（范圍對象，指一個或者多個單元格）,Excel對象結(jié)構(gòu),使用對象的程序語法,Workbooks(“Mybook.xls”).Worksheets(“Sheet1”).Range(“B5”).Value = “ABCDE”,屬性,Workbooks . Close,集合對象,VBA控制結(jié)構(gòu),分支結(jié)構(gòu),If 條件1 Then 語句塊1 ElseIf 條件2 Then 語句塊2 Else 語句塊n+1 End If,Select Case 測試變量 Case 表達式1 語句塊1 Case 表達式2 語句塊2 Cas

17、e Else 語句塊n+1 End Select,VBA控制結(jié)構(gòu),循環(huán)結(jié)構(gòu),For 變量=初值 To 終值 Step 步長 語句塊 Next 變量,For Each 對象變量 In 對象集 語句塊Next 對象變量,Do While 條件表達式 語句塊 Loop,示例：對紅色字體的數(shù)據(jù)求和,Function SUMIFRAD (Target As range) Dim sum As Integer Dim r As range For Each r In Target If r.Font.Color = vbRed Then RGB(255,0,0) sum = sum + r.Value E

18、nd If Next SUMIFRAD = sum End Function,創(chuàng)建用戶窗體,提示使用者選取、輸入或瀏覽數(shù)據(jù) 使用控件 文本框輸入文字 列表框選取內(nèi)容 選擇框選取一個或多個功能,用戶窗體窗口的操作,建立用戶窗體 配置ActiveX控件 設(shè)置窗口屬性 配置控件屬性及其事件 Load與Unload語句 加載到內(nèi)存或從內(nèi)存中釋放 用Show和Hide方法控制對象的顯示和隱藏,DEMO,VBA幫助你完成從必然王國到自由王國的飛躍,輕松一刻 -excel,*控制特定單元格輸入文本的長度 *給自己的函數(shù)建立新菜單,小竅門！,1.在工具欄空白處右擊鼠標，選“自定義”選項，打開“自定義”對話框。

19、在“命令”標簽中，選中“類別”下的“新菜單”項，再將“命令”下面的“新菜單”拖到菜單欄。 2.再在“類別”下面任選一項（如“插入”選項），在右邊“命令”下面任選一項（如“超鏈接”選項）,單擊“數(shù)據(jù)”菜單的“有效性”選項。在“設(shè)置”卡片“有效性條件”的“允許”下拉菜單中選擇“文本長度”。然后在“數(shù)據(jù)”下拉菜單中選擇“等于”，且“長度”為“4”。,終極突破,Vbs腳本編程,什么是VBS？,Vbs是一種Windows腳本，它的全稱是:Microsoft Visual Basic Script Editon.(微軟公司可視化BASIC腳本版)， VBS是Visual Basic的的一個抽象子集，是系統(tǒng)

20、內(nèi)置的，用它編寫的腳本代碼不能編譯成二進制文件，直接由Windows系統(tǒng)執(zhí)行（實際是一個叫做宿主host的解釋源代碼并執(zhí)行），高效、易學(xué)，但是大部分高級語言能干的事情，它基本上都具備，它可以使各種各樣的任務(wù)自動化，可以使你從重復(fù)瑣碎的工作中解脫出來，極大的提高工作效率。,應(yīng)用領(lǐng)域,Windows操作系統(tǒng) VBScript可以被用來自動地完成重復(fù)性的Windows操作系統(tǒng)任務(wù)。在Windows操作系統(tǒng)中，VBScript可以在Windows Script Host的范圍內(nèi)運行。Windows操作系統(tǒng)可以自動辨認和執(zhí)行 *.VBS和*.WSF兩種文件格式 網(wǎng)頁瀏覽器 網(wǎng)頁服務(wù)器,Sendkeys函

21、數(shù),Sendkeys函數(shù) 函數(shù)名稱：Sendkeys 主要功能：將一個或多個按鍵消息發(fā)送到活動窗口，就如同在鍵盤上進行輸入一樣。 使用格式：SendKeys string, wait 參數(shù)說明：string 必需的。字符串表達式，指定要發(fā)送的按鍵消息。 Wait 可選的。 特別提醒：對 Sendkeys 來說，加號 (+)、插入符 ()、百分比符號 (%)、上劃線 () 及圓括號 ( ) 都具有特殊意義。為了指定上述任何一個字符，要將它放在大括號 () 當(dāng)中。例如，要指定正號，可用 + 表示。為了指定大括號字符，請使用 及 。,Sendkeys函數(shù),為了指定那些與 SHIFT、CTRL 及 A

22、LT 等按鍵結(jié)合的組合鍵，可在這些按鍵碼的前面放置一個或多個代碼，這些代碼列舉如下 ： 按鍵 代碼 SHIFT + CTRL ALT % 特別提醒：為了說明在按下其它按鍵時應(yīng)同時按下 SHIFT、CTRL、及 ALT 的任意組合鍵，請把那些按鍵的碼放在括號當(dāng)中。例如，為了說明按下 E 與 C 的時候同時按下 SHIFT 鍵，請使用 +(EC)。為了說明在按下 E 的時候同時按下 SHIFT 鍵，但接著按 C 而不按 SHIFT，則使用 +EC。,Burn in自動安裝程序,Dim WshShell,fs Set WshShell=WScript.CreateObject(WScript.Shell) Set fs=wscript.createobject(scripting.filesystemobject) If(fs.FileExists(C:Program FilesBurnInTestbit.exe)=true) Then Msgbox Please dont reinstall wscript.quit ElseIf (fs.FolderExists(C:Program Files (x86)=true) Then WshShell.run bitpro_x64.exe