《EXCEL_函數(shù)_宏_VBA 入門知識》由會(huì)員分享，可在線閱讀，更多相關(guān)《EXCEL_函數(shù)_宏_VBA 入門知識（48頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、Excel 函數(shù)函數(shù) 宏宏-VBA-VBA2021/7/11What can you get from here?如何設(shè)置EXCEL國際象棋棋盤式底紋什么是VB，VBS，VBA和宏如何對一張表所有紅色字體的數(shù)據(jù)求和Burn-in自動(dòng)安裝腳本究竟有何訣竅2021/7/12CONTENTSEXCEL 函數(shù)概述EXCEL 宏-VBA介紹VBS在測試中的應(yīng)用Q/A2021/7/13EXCEL 函數(shù)概述AVERAGE函數(shù)函數(shù) 函數(shù)名稱：AVERAGE 主要功能：求出所有參數(shù)的算術(shù)平均值。使用格式：AVERAGE(number1,number2,)參數(shù)說明：number1,number2,：需要求平均值的

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

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

4、OUNTIF(B1:B13,=80)，確認(rèn)后，即可統(tǒng)計(jì)出B1至B13單元格區(qū)域中，數(shù)值大于等于80的單元格數(shù)目。特別提醒：允許引用的單元格區(qū)域中有空白單元格出現(xiàn)。2021/7/16EXCEL 函數(shù)概述DATEDIF函數(shù)函數(shù)函數(shù)名稱：DATEDIF 主要功能：計(jì)算返回兩個(gè)日期參數(shù)的差值。使用格式：=DATEDIF(date1,date2,“y(m,d)”)參數(shù)說明：date1代表前面一個(gè)日期，date2代表后面一個(gè)日期；y（m、d）要求返回兩個(gè)日期相差的年（月、天）數(shù)。應(yīng)用舉例：在C23單元格中輸入公式：=DATEDIF(A23,TODAY(),y)特別提醒：這是Excel中的一個(gè)隱藏函數(shù)，在函

5、數(shù)向?qū)е惺钦也坏降?，可以直接輸入使用，對于?jì)算年齡、工齡等非常有效。2021/7/17EXCEL 函數(shù)概述IF函數(shù)函數(shù)函數(shù)名稱：IF 主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容。使用格式：=IF(Logical,Value_if_true,Value_if_false)參數(shù)說明：Logical代表邏輯判斷表達(dá)式；Value_if_true表示當(dāng)判斷條件為邏輯“真（TRUE）”時(shí)的顯示內(nèi)容，Value_if_false表示當(dāng)判斷條件為邏輯“假（FALSE）”時(shí)的顯示內(nèi)容 應(yīng)用舉例：在C29單元格中輸入公式：=IF(C26=18,“符合要求”,“不符合要求”)2021/7/18

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

7、CEL 函數(shù)概述RANK函數(shù)函數(shù)函數(shù)名稱：RANK主要功能：返回某一數(shù)值在一列數(shù)值中的相對于其他數(shù)值的排位。使用格式：RANK（Number,ref,order）參數(shù)說明：Number代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；order代表排序方式參數(shù)（如果為“0”或者忽略，則按降序排名，即數(shù)值越大，排名結(jié)果數(shù)值越?。蝗绻麨榉恰?”值，則按升序排名，即數(shù)值越大，排名結(jié)果數(shù)值越大；）。應(yīng)用舉例：如在C2單元格中輸入公式：=RANK(B2,$B$2:$B$31,0)，確認(rèn)后即可得出丁1同學(xué)的語文成績在全班成績中的排名結(jié)果。特別提醒：我們讓Number參數(shù)采取了相對引用形式，而讓ref

8、參數(shù)采取了絕對引用形式（增加了一個(gè)“$”符號），這樣設(shè)置后，選中C2單元格，將鼠標(biāo)移至該單元格右下角，成細(xì)十字線狀時(shí)（通常稱之為“填充柄”），按住左鍵向下拖拉，即可將上述公式快速復(fù)制到C列下面的單元格中，完成其他同學(xué)語文成績的排名統(tǒng)計(jì)。2021/7/110EXCEL 函數(shù)概述SUBTOTAL函數(shù)函數(shù)函數(shù)名稱：SUBTOTAL主要功能：返回列表或數(shù)據(jù)庫中的分類匯總。使用格式：SUBTOTAL(function_num,ref1,ref2,.)參數(shù)說明：Function_num為1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數(shù)字，用來指定使用什么函數(shù)在列表中進(jìn)行分類匯總計(jì)算（如圖6）；

9、ref1,ref2,代表要進(jìn)行分類匯總區(qū)域或引用，不超過29個(gè)。應(yīng)用舉例：在B64和C64單元格中分別輸入公式：=SUBTOTAL(3,C2:C63)和=SUBTOTAL103,C2:C63)，并且將61行隱藏起來，確認(rèn)后，前者顯示為62（包括隱藏的行），后者顯示為61，不包括隱藏的行。2021/7/111EXCEL 函數(shù)概述LEFT函數(shù)函數(shù)/RIGHT函數(shù)函數(shù)函數(shù)名稱：LEFT主要功能：從一個(gè)文本字符串的第一個(gè)字符開始，截取指定數(shù)目的字符。使用格式：LEFT(text,num_chars)，參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。應(yīng)用舉例：假定A38單元

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

11、格中輸入公式：=MID(A47,4,3)，確認(rèn)后即顯示出“天極網(wǎng)”的字符。特別提醒：公式中各參數(shù)間，要用英文狀態(tài)下的逗號“,”隔開。2021/7/113EXCEL 函數(shù)概述MOD函數(shù)函數(shù)函數(shù)名稱：MOD 主要功能：求出兩數(shù)相除的余數(shù)。使用格式：MOD(number,divisor)參數(shù)說明：number代表被除數(shù)；divisor代表除數(shù)。應(yīng)用舉例：輸入公式：=MOD(13,4)，確認(rèn)后顯示出結(jié)果“1”。特別提醒：如果divisor參數(shù)為零，則顯示錯(cuò)誤值“#DIV/0!”；MOD函數(shù)可以借用函數(shù)INT來表示：上述公式可以修改為：=13-4*INT(13/4)。2021/7/114輕松一刻 -ex

12、cel*在word中分別輸入三個(gè)“=”，輸入三個(gè)“”或輸入三個(gè)“*”或“-”或“#”，回車。有驚喜哦*如果希望設(shè)置國際象棋棋盤式底紋(白色自定義色)話，應(yīng)該可以的，不妨試試哦=MOD(ROW()+COLUMN(),2)0，=MOD(ROW()+COLUMN(),2)=0 2021/7/115ExcelExcel宏宏2021/7/116挑戰(zhàn)第一關(guān)挑戰(zhàn)第一關(guān)善用宏簡化工作善用宏簡化工作2021/7/117什么是宏宏是一段定義好的操作，它可以是一段程序代碼，也可以是一連串的指令集合。宏的作用可以使頻繁執(zhí)行的動(dòng)作自動(dòng)化 節(jié)省時(shí)間，提高工作效率，又能減少失誤2021/7/118錄制宏錄制宏需要經(jīng)常重復(fù)執(zhí)

13、行的某項(xiàng)任務(wù)，就可需要經(jīng)常重復(fù)執(zhí)行的某項(xiàng)任務(wù)，就可需要經(jīng)常重復(fù)執(zhí)行的某項(xiàng)任務(wù)，就可需要經(jīng)常重復(fù)執(zhí)行的某項(xiàng)任務(wù)，就可以把執(zhí)行這些任務(wù)的步驟全部錄制在以把執(zhí)行這些任務(wù)的步驟全部錄制在以把執(zhí)行這些任務(wù)的步驟全部錄制在以把執(zhí)行這些任務(wù)的步驟全部錄制在宏里，把宏變?yōu)榭勺詣?dòng)執(zhí)行的任務(wù)宏里，把宏變?yōu)榭勺詣?dòng)執(zhí)行的任務(wù)宏里，把宏變?yōu)榭勺詣?dòng)執(zhí)行的任務(wù)宏里，把宏變?yōu)榭勺詣?dòng)執(zhí)行的任務(wù) 執(zhí)行宏的操作執(zhí)行宏的操作 執(zhí)行宏的命令執(zhí)行宏的命令執(zhí)行宏的命令執(zhí)行宏的命令 在工具欄上設(shè)置宏按鈕在工具欄上設(shè)置宏按鈕在工具欄上設(shè)置宏按鈕在工具欄上設(shè)置宏按鈕 使用窗體控件使用窗體控件使用窗體控件使用窗體控件 使用圖形對象執(zhí)行宏使用圖形對

14、象執(zhí)行宏使用圖形對象執(zhí)行宏使用圖形對象執(zhí)行宏 2021/7/119宏的結(jié)構(gòu)Sub主界面主界面()主界面主界面MacroCorenLee記錄的宏記錄的宏2002-11-5Sheets(主界面主界面).SelectEndSub宏名宏名宏名宏名批注批注批注批注宏程序宏程序宏程序宏程序2021/7/120挑戰(zhàn)第二關(guān)挑戰(zhàn)第二關(guān)深入淺出深入淺出VBAVBA2021/7/121VBA及其特點(diǎn) 什么是什么是VBA?VBA（Visual Basic for Applications）是Microsoft Office 系列的內(nèi)置編程語言，即應(yīng)用程序中的VB。它“寄生于”O(jiān)ffice應(yīng)用程序，是Office 套裝

15、軟件的一個(gè)重要組件。VBA編程優(yōu)點(diǎn)編程優(yōu)點(diǎn) 1程序只起輔助作用，大部分功能用Office的，因此簡化了程序設(shè)計(jì)。2通過宏錄制，可以部分地實(shí)現(xiàn)程序設(shè)計(jì)的自動(dòng)化。3發(fā)行方便，只要發(fā)行含有VBA代碼的文檔或模板即可。4.Office界面對于廣大計(jì)算機(jī)應(yīng)用人員來說比較熟悉，符合一般操作人員的使用習(xí)慣，便于軟件推廣應(yīng)用。5用VBA編程非常簡單。2021/7/122為何要使用 VBA宏缺少伸縮的彈性記錄了許多不需要的步驟記錄了許多不需要的資料無法使用較深入的功能無法完成重復(fù)性的工作無法完成需要條件判斷的工作宏宏VBAVBAVisual Basic for Application2021/7/123VBA與

16、Visual Basic的差異Visual Basic以界面為中心的開發(fā)方式重點(diǎn)在使用者如何進(jìn)行操作VBA以文件為中心的開發(fā)方式重點(diǎn)在使用者最后看到什么2021/7/124Visual Basic編輯器用來編輯宏程序或制作更高階的整合性應(yīng)用程序附屬于Office之下，使用較少的系統(tǒng)資源，達(dá)到編輯應(yīng)用程序的目的。2021/7/125什么是工程工程就是模塊的集合，包括：用戶窗體文件，例如Excel中的工作表以及圖表工作表模塊類模塊ActiveX文件引用的其他工程2021/7/126VBA程序的基本架構(gòu)對象面向?qū)ο蟮拈_發(fā)方式VBA是以操作Office對象為目的的程序語言屬性對象所屬的特性或特征方法控

17、制對象的內(nèi)建動(dòng)作或功能，以便可以讓程序順利打開事件發(fā)生于對象上的事情2021/7/127ExcelExcel對象結(jié)構(gòu)對象結(jié)構(gòu)對象結(jié)構(gòu)對象結(jié)構(gòu)2021/7/1281.Application對象（代表整個(gè)Excel應(yīng)用程序）2.Workbook（工作簿，即是說Excel文件）3.Worksheet（工作表，即工作簿的“頁”）4.Range（范圍對象，指一個(gè)或者多個(gè)單元格）Excel對象結(jié)構(gòu)對象結(jié)構(gòu)2021/7/129使用對象的程序語法Workbooks(“Mybook.xls”).Worksheets(“Sheet1”).Range(“B5”).Value=“ABCDE”容器容器容器容器屬性屬性W

18、orkbooks.Close集合對象集合對象2021/7/130VBA控制結(jié)構(gòu)分支結(jié)構(gòu)If條件條件1Then語句塊語句塊1ElseIf條件條件2Then語句塊語句塊2Else語句塊語句塊n+1EndIfSelectCase測試變量測試變量Case表達(dá)式表達(dá)式1語句塊語句塊1Case表達(dá)式表達(dá)式2語句塊語句塊2CaseElse語句塊語句塊n+1EndSelect2021/7/131VBA控制結(jié)構(gòu)循環(huán)結(jié)構(gòu)For變量變量=初值初值To終值終值Step步長步長語句塊語句塊Next變量變量ForEach對象變量對象變量In對象集對象集語句塊語句塊Next對象變量對象變量DoWhile條件表達(dá)式條件表達(dá)式

19、語句塊語句塊Loop2021/7/132示例：對紅色字體的數(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 End If Next SUMIFRAD=sumEnd Function2021/7/133創(chuàng)建用戶窗體提示使用者選取、輸入或?yàn)g覽數(shù)據(jù)使用控件文本框輸入文字列表框選取內(nèi)容選擇框選取一個(gè)或多個(gè)功能2021/7/134用戶窗體窗口的操作建立用戶窗體配

20、置ActiveX控件設(shè)置窗口屬性配置控件屬性及其事件Load與Unload語句加載到內(nèi)存或從內(nèi)存中釋放用Show和Hide方法控制對象的顯示和隱藏2021/7/135VBAVBA幫助你完成從必然王國到自由王國的飛躍幫助你完成從必然王國到自由王國的飛躍幫助你完成從必然王國到自由王國的飛躍幫助你完成從必然王國到自由王國的飛躍2021/7/136輕松一刻 -excel*控制特定單元格輸入文本的長度*給自己的函數(shù)建立新菜單1.在工具欄空白處右擊鼠標(biāo)，選在工具欄空白處右擊鼠標(biāo)，選“自定義自定義”選項(xiàng)，打開選項(xiàng)，打開“自定義自定義”對話框。對話框。在在“命令命令”標(biāo)簽中，選中標(biāo)簽中，選中“類別類別”下的下

21、的“新菜單新菜單”項(xiàng)，再將項(xiàng)，再將“命令命令”下面的下面的“新菜單新菜單”拖到菜單欄。拖到菜單欄。2.再在再在“類別類別”下面任選一項(xiàng)（如下面任選一項(xiàng)（如“插入插入”選項(xiàng)），在右邊選項(xiàng)），在右邊“命令命令”下面任下面任選一項(xiàng)（如選一項(xiàng)（如“超鏈接超鏈接”選項(xiàng)）選項(xiàng)）單擊單擊“數(shù)據(jù)數(shù)據(jù)”菜單的菜單的“有效性有效性”選項(xiàng)。在選項(xiàng)。在“設(shè)置設(shè)置”卡片卡片“有效性條件有效性條件”的的“允許允許”下拉菜單中選擇下拉菜單中選擇“文本長度文本長度”。然后在。然后在“數(shù)據(jù)數(shù)據(jù)”下拉菜單中選擇下拉菜單中選擇“等于等于”，且且“長度長度”為為“4”。2021/7/137終極突破Vbs腳本編程2021/7/138什

22、么是什么是VBSVBS？Vbs是一種Windows腳本，它的全稱是:Microsoft Visual Basic Script Editon.(微軟公司可視化BASIC腳本版)，VBS是Visual Basic的的一個(gè)抽象子集，是系統(tǒng)內(nèi)置的，用它編寫的腳本代碼不能編譯成二進(jìn)制文件，直接由Windows系統(tǒng)執(zhí)行（實(shí)際是一個(gè)叫做宿主host的解釋源代碼并執(zhí)行），高效、易學(xué)，但是大部分高級語言能干的事情，它基本上都具備，它可以使各種各樣的任務(wù)自動(dòng)化，可以使你從重復(fù)瑣碎的工作中解脫出來，極大的提高工作效率。2021/7/139應(yīng)用領(lǐng)域應(yīng)用領(lǐng)域Windows操作系統(tǒng) VBScript可以被用來自動(dòng)地完成

23、重復(fù)性的Windows操作系統(tǒng)任務(wù)。在Windows操作系統(tǒng)中，VBScript可以在Windows Script Host的范圍內(nèi)運(yùn)行。Windows操作系統(tǒng)可以自動(dòng)辨認(rèn)和執(zhí)行*.VBS和*.WSF兩種文件格式網(wǎng)頁瀏覽器網(wǎng)頁服務(wù)器2021/7/140Sendkeys函數(shù)函數(shù)SendkeysSendkeys函函數(shù)數(shù) 函數(shù)名稱：Sendkeys主要功能：將一個(gè)或多個(gè)按鍵消息發(fā)送到活動(dòng)窗口，就如同在鍵盤上進(jìn)行輸入一樣。使用格式：SendKeys string,wait參數(shù)說明：string 必需的。字符串表達(dá)式，指定要發(fā)送的按鍵消息。Wait 可選的。特別提醒：對 Sendkeys 來說，加號(+

24、)、插入符()、百分比符號(%)、上劃線()及圓括號()都具有特殊意義。為了指定上述任何一個(gè)字符，要將它放在大括號()當(dāng)中。例如，要指定正號，可用+表示。為了指定大括號字符，請使用 及。2021/7/141Sendkeys函數(shù)函數(shù)為了指定那些與 SHIFT、CTRL 及 ALT 等按鍵結(jié)合的組合鍵，可在這些按鍵碼的前面放置一個(gè)或多個(gè)代碼，這些代碼列舉如下：按鍵 代碼 SHIFT +CTRL ALT%特別提醒：為了說明在按下其它按鍵時(shí)應(yīng)同時(shí)按下 SHIFT、CTRL、及 ALT 的任意組合鍵，請把那些按鍵的碼放在括號當(dāng)中。例如，為了說明按下 E 與 C 的時(shí)候同時(shí)按下 SHIFT 鍵，請使用+(

25、EC)。為了說明在按下 E 的時(shí)候同時(shí)按下 SHIFT 鍵，但接著按 C 而不按 SHIFT，則使用+EC。2021/7/142Burn in自動(dòng)安裝程序自動(dòng)安裝程序Dim WshShell,fsSet WshShell=WScript.CreateObject(WScript.Shell)Set fs=wscript.createobject(scripting.filesystemobject)If(fs.FileExists(C:Program FilesBurnInTestbit.exe)=true)ThenMsgbox Please dont reinstallwscript.qui

26、tElseIf(fs.FolderExists(C:Program Files(x86)=true)ThenWshShell.run bitpro_x64.exeElseWshShell.Run bitpro.exeEnd If2021/7/143Burn in自動(dòng)安裝程序自動(dòng)安裝程序WScript.Sleep 2000WshShell.AppActivate Setup-BurnInTestWshshell.SendKeys%nWScript.Sleep 1000Wshshell.SendKeys%aWScript.Sleep 1000Wshshell.SendKeys%nWScript.S

27、leep 1500Wshshell.SendKeys%nWScript.Sleep 1500Wshshell.SendKeys%nWScript.Sleep 1000Wshshell.SendKeys%iWScript.Sleep 20002021/7/144定時(shí)關(guān)機(jī)程序定時(shí)關(guān)機(jī)程序代碼開始set ws=CreateObject(wscript.shell)獲得這個(gè)對象的句柄，用于下面的操作do設(shè)置循環(huán)，不停監(jiān)視時(shí)間if abs(DateDiff(s,CDate(11:45:00),time)60 then判斷時(shí)間是不是在要求范圍內(nèi)，即與11點(diǎn)45分接近exit do在時(shí)間范圍內(nèi)就退出循環(huán)end ifwscript.sleep 1000*10暫停腳本執(zhí)行以節(jié)省系統(tǒng)cpu資源loop循環(huán)結(jié)束ws.run shutdown-s-t 600 c”系統(tǒng)將在計(jì)時(shí)結(jié)束關(guān)閉，請注意保存需要的文件,0調(diào)用shutdown命令關(guān)機(jī)代碼結(jié)束2021/7/145利用EXCEL常用函數(shù)來簡化工作了解VB，VBS，VBA和宏是什么關(guān)系利用VBA來創(chuàng)建基本函數(shù)和宏利用VBS來處理簡單重復(fù)性步驟what should we get now?2021/7/146Thanks!HWQA-KYLE 2021/7/147 結(jié)結(jié)束束語語若有不當(dāng)之處，請指正，謝謝！若有不當(dāng)之處，請指正，謝謝！