提高你的辦公效率excel函數(shù)與宏入門1

1、Excel函數(shù)與宏應(yīng)用入門何藹廷2012年12月3日提綱1.何謂函數(shù).函數(shù)的關(guān)鍵要素.函數(shù)的種類.一般函數(shù)應(yīng)用與函數(shù)套用 2.何謂宏.如何制作宏.錄制宏的應(yīng)用.宏的編寫與應(yīng)用實(shí)例1.函數(shù)Excel函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式任務(wù)的特殊公式。 以常用的求和函數(shù)SUM為例，它的語法是“SUM(number1,number2,.)”。其中“SUM”稱為函數(shù)名稱，一個(gè)函數(shù)只有唯一的一個(gè)名稱，它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號(hào)，接著是用逗號(hào)分隔的稱為參數(shù)的內(nèi)容，最后用一個(gè)右括號(hào)表示函數(shù)結(jié)束。參數(shù)是函數(shù)中最復(fù)雜的組成部分，它

2、規(guī)定了函數(shù)的運(yùn)算對(duì)象、順序或結(jié)構(gòu)等。使得用戶可以對(duì)某個(gè)單元格或區(qū)域進(jìn)行處理1.函數(shù)函數(shù)與公式函數(shù)與公式既有區(qū)別又互相聯(lián)系。 如果說前者是Excel預(yù)先定義好的特殊公式，后者就是由用戶自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的計(jì)算式是由用戶自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的計(jì)算式。以公式“=SUM(E1:H1)*A1+26”為例，它要以等號(hào)“=”開始，其內(nèi)部可以包括函數(shù)、引用、運(yùn)算符和常量。上式中的“SUM(E1:H1)”是函數(shù)，“A1”則是對(duì)單元格A1的引用(使用其中存儲(chǔ)的數(shù)據(jù))，“26”則是常量，“*”和“+”則是算術(shù)運(yùn)算符如果函數(shù)要以公式的形式出現(xiàn)，它必須有兩個(gè)組成部分，一個(gè)是函數(shù)名稱前面的等號(hào)，另一

3、個(gè)則是函數(shù)本身。1.函數(shù)的種類邏輯函數(shù)統(tǒng)計(jì)函數(shù)文本函數(shù)日期和時(shí)間函數(shù)查找和引用函數(shù)加載宏和自動(dòng)化函數(shù)多維數(shù)據(jù)集函數(shù)數(shù)學(xué)和三角函數(shù)數(shù)據(jù)庫函數(shù)工程行函數(shù)信息函數(shù)財(cái)務(wù)函數(shù)函數(shù)邏輯函數(shù)邏輯函數(shù)邏輯函數(shù)中比較有用也比較常用的是 IF 函數(shù)此函數(shù)用于判斷，對(duì)指定的條件計(jì)算結(jié)果為 TRUE 或 FALSE，返回不同的結(jié)果,其結(jié)構(gòu)為：IF(logical_test,value_if_true,value_if_false)簡單例子：IF (X=0，”大于等于0”，”不大于0”) 注意： 1.在編輯函數(shù)時(shí)，需要切換成英文輸入法，excel才能識(shí)別，否則函數(shù)是沒有意義的（沒有大小寫之分） 2.單元格格式不能是文本

4、，否則函數(shù)無法運(yùn)算 3.在輸出的是字符而非數(shù)值或非變量時(shí)，需要用到“” 具體應(yīng)用例子（見附件）函數(shù)邏輯函數(shù)還包含如下函數(shù)函數(shù)統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù)在office 2007 中，統(tǒng)計(jì)函數(shù)多達(dá)83個(gè)。比較常用的有：求和函數(shù)SUM，SUM(number1,number2, .)滿足給定條件的單元格的個(gè)數(shù)函數(shù)COUNTIF，COUNTIF(range,criteria)第N個(gè)最大值最小值LARGE與SMALL，LARGE(array,k)，SMALL(array,k)最大值最小值MAX與MIN，MAX(number1,number2,.)，MIN(number1,number2,.)簡單例子：見附件函數(shù)文本

5、函數(shù)文本函數(shù)在一個(gè)文本值中查找另一個(gè)文本值（區(qū)分大小寫） FINDFIND(find_text,within_text,start_num)在一個(gè)文本值中查找另一個(gè)文本值（不區(qū)分大小寫）SEARCHSEARCH(find_text,within_text,start_num)返回文本中最左邊/右邊字符LEFT/RIGHTLEFT(text,num_chars), RIGHT(text,num_chars)返回中間字符MIDMID(text,start_num,num_chars)替換文本中的字符REPLACEREPLACE(old_text,start_num,num_chars,new_te

6、xt)在文本字符串中用新文本替換舊文本SUBSTITUTESUBSTITUTE(text,old_text,new_text,instance_num)簡單例子：見附件(注意FIND與SEARCH，REPLACE與SUBSTITUTE之間的區(qū)別)函數(shù)日期與時(shí)間函數(shù)日期與時(shí)間函數(shù)比較常用的日期時(shí)間函數(shù)是：NOW()獲取當(dāng)前日期、時(shí)間TODAY()獲取當(dāng)前日期YEAR:YEAR(serial_number)提取年份MONTH: MONTH(serial_number)提取月份提取月份DAY: DAY(serial_number)提取日簡單例子：見附件函數(shù)其他日期時(shí)間函數(shù)函數(shù)查找和引用函數(shù)查找和引用

7、函數(shù)常用的是查找函數(shù)VLOOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)這是我第一個(gè)EXCEL接觸的函數(shù)也是平時(shí)所有函數(shù)中用的比較多的一個(gè)地址函數(shù)ADDRESSADDRESS(row_num,column_num,abs_num,a1,sheet_text)例子詳見附件函數(shù)其他查找引用函數(shù)嵌套函數(shù)異常處理-分解輸入在輸入嵌套函數(shù)時(shí)，很容易出現(xiàn)思維混亂，導(dǎo)致公式錯(cuò)誤，無法完成正常的運(yùn)算。比如輸入： =IF(YEAR(NOW()-YEAR(D2)*12+(MONTH(NOW()-MONTH(D2)=6,可以加工資,)

8、特別是公式長了后，括號(hào)很難把握準(zhǔn)，或者多括號(hào)，或者少括號(hào)上面公式中，第一個(gè)NOW，后面就少了板邊括號(hào)，正常公式應(yīng)該是： =IF(YEAR(NOW()-YEAR(D2)*12+(MONTH(NOW()-MONTH(D2)=6,可以加工資,)措施：2.宏何謂宏宏是VBA語言編出的一段程序，是一系列命令和函數(shù)，存儲(chǔ)于 Visual Basic 模塊中，并且在需要執(zhí)行該項(xiàng)任務(wù)時(shí)可隨時(shí)運(yùn)行。 宏，就是一組指令集，通過執(zhí)行類似批處理的一組命令，目的是讓宏來替我們實(shí)現(xiàn)任務(wù)執(zhí)行的自動(dòng)化。它可以替代人工進(jìn)行一系列費(fèi)時(shí)而重復(fù)的操作，是一個(gè)極為靈活的自定義命令。在Excel中運(yùn)用宏可有兩種途徑，一種是錄制宏，另一種

9、是采用Excel自帶的Visual Basic編輯器來編輯宏命令。前一種使用較多，操作簡練，也易于理解；后一種則能應(yīng)對(duì)紛繁復(fù)雜的運(yùn)算要求，但對(duì)編輯人員要求也更高。2.宏首次與宏的接觸-貼片程序制作2.宏首先看看宏到底能替代我們做什么？2.宏錄制宏1.如何錄制宏2.宏的內(nèi)容是什么3.如何識(shí)別每一步的有效性4.設(shè)置快捷方式5.指定按鈕2.宏錄制宏只能做一些重復(fù)性的、簡單的、既定的工作，一旦碰到需要隨機(jī)應(yīng)變的操作時(shí)，錄制宏就無能為力了。2.宏自編宏1.明確目的，理清思路，確認(rèn)將要使用的語句2.宏2.規(guī)范格式，便于編寫過程中能方便的找到語句的開頭與結(jié)尾 For KQ = 11 To 47 考勤日期(有

10、些日期占用兩列) For BC = 3 To 218 班次中的行數(shù) A1 = Workbooks(系統(tǒng)考勤表.XLS).Sheets(SHEET1).Cells(XT, KQ) A2 = Workbooks(班次.XLSX).Sheets(排班).Cells(BC, 2) If Workbooks(系統(tǒng)考勤表.XLS).Sheets(SHEET1).Cells(XT, KQ) = Workbooks(班次.XLSX).Sheets(排班).Cells(BC, 2) Then RQ = Right(Workbooks(系統(tǒng)考勤表.XLS).Sheets(SHEET1).Cells(11, KQ)

11、, 2) 系統(tǒng)考勤中的日期 B1 = Workbooks(班次.XLSX).Sheets(排班).Cells(BC, BM) B2 = Workbooks(X).Sheets(Y).Cells(i, 2 + RQ) If Workbooks(班次.XLSX).Sheets(排班).Cells(BC, BM) Workbooks(X).Sheets(Y).Cells(i, 2 + RQ) Then Cells(i, 2 + RQ).Select With Selection.Interior .Color = 65535 黃色/紅色為255 End With Range(Cells(i, 2 +

12、 RQ), Cells(i, 2 + RQ).AddComment Range(Cells(i, 2 + RQ), Cells(i, 2 + RQ).Comment.Text Text:=“班次代碼為： ” & Workbooks(“班次.XLSX”).Sheets(“排班 ).Cells(BC, BM) & Chr(10) & 考勤代碼為： & Workbooks(系統(tǒng)考勤表.XLS).Sheets(SHEET1).Cells(XT, KQ) End If Exit For End If Next Next2.宏3.在必要時(shí)進(jìn)行備注，方便以后修改在VB中，“”同行后面的描述不進(jìn)行編譯，可以用英文，也可以用中文進(jìn)行備注或者使用“”將不需要的語句臨時(shí)或長期跳過，以免影響語句的正常編譯2.宏4.自編語句與錄制語句并用其實(shí)很多執(zhí)行層面的語句，都可以用錄制方式獲得，這樣既不用記太多不必要的語句，也因?yàn)槭钦{(diào)用VB自帶的語句，執(zhí)行起來效率也更高一些。2.宏5.快捷鍵與窗口的使用a. F8 逐句調(diào)試b. F5 直接運(yùn)行c. F9 切換斷點(diǎn)d. Ctrl+Break 中斷視圖-本地窗口：觀察運(yùn)行過程中各變量的變化趨勢(shì)，以便發(fā)現(xiàn)問題。2.宏6.VB中的函數(shù)與公式中