2024年度EXCEL常用函數(shù)培訓(xùn)

EXCEL常用函數(shù)培訓(xùn)REPORTING2023WORKSUMMARY2024/3/231目錄CATALOGUEEXCEL函數(shù)基礎(chǔ)文本處理函數(shù)數(shù)學(xué)計(jì)算函數(shù)邏輯判斷函數(shù)日期時(shí)間處理函數(shù)數(shù)據(jù)統(tǒng)計(jì)與分析函數(shù)自定義函數(shù)與宏應(yīng)用2024/3/232PART01EXCEL函數(shù)基礎(chǔ)2024/3/233函數(shù)分類Excel中的函數(shù)可以分為以下幾類函數(shù)定義在Excel中，函數(shù)是一種預(yù)定義的公式，用于執(zhí)行特定的計(jì)算或操作。通過使用函數(shù)，可以簡化復(fù)雜的數(shù)據(jù)處理和分析任務(wù)。數(shù)學(xué)和三角函數(shù)用于執(zhí)行基本的數(shù)學(xué)運(yùn)算，如加法、減法、乘法、除法等，以及更高級的數(shù)學(xué)函數(shù)，如指數(shù)、對數(shù)、三角函數(shù)等。函數(shù)定義與分類2024/3/234用于對數(shù)據(jù)集進(jìn)行統(tǒng)計(jì)分析，如計(jì)算平均值、中位數(shù)、標(biāo)準(zhǔn)差、方差等。統(tǒng)計(jì)函數(shù)文本函數(shù)日期和時(shí)間函數(shù)用于處理和操作文本數(shù)據(jù)，如連接文本、查找和替換文本、提取子字符串等。用于處理日期和時(shí)間數(shù)據(jù)，如計(jì)算兩個日期之間的差異、將日期格式化為特定格式等。030201函數(shù)定義與分類2024/3/235用于在數(shù)據(jù)表中查找特定值或引用其他單元格的值，如VLOOKUP、HLOOKUP等。查找和引用函數(shù)用于與數(shù)據(jù)庫進(jìn)行交互，如查詢數(shù)據(jù)庫中的數(shù)據(jù)、執(zhí)行SQL語句等。數(shù)據(jù)庫函數(shù)用于執(zhí)行財(cái)務(wù)計(jì)算，如計(jì)算貸款還款額、投資回報(bào)率等。財(cái)務(wù)函數(shù)函數(shù)定義與分類2024/3/236每個Excel函數(shù)都有其特定的語法，即函數(shù)的名稱和參數(shù)。語法通常以等號（=）開頭，后跟函數(shù)名稱和括號內(nèi)的參數(shù)。例如，`=SUM(A1:A10)`表示對A1到A10單元格范圍內(nèi)的值進(jìn)行求和。函數(shù)語法函數(shù)的參數(shù)可以是常量、單元格引用、其他函數(shù)或名稱等。不同類型的參數(shù)在函數(shù)中起到不同的作用。例如，在`=SUM(A1:A10,C1:C5)`中，`A1:A10`和`C1:C5`是單元格引用參數(shù)，表示對這兩個范圍內(nèi)的值進(jìn)行求和。參數(shù)類型函數(shù)語法及參數(shù)2024/3/237相對引用默認(rèn)情況下，Excel使用相對引用。當(dāng)復(fù)制包含相對引用的公式時(shí)，引用的單元格地址會相對于公式所在單元格的位置進(jìn)行調(diào)整。例如，在A1單元格中輸入`=B1`，然后將該公式復(fù)制到A2單元格時(shí)，A2單元格中的公式會自動變?yōu)閌=B2`。絕對引用通過在行號和列號前添加美元符號（$），可以將相對引用轉(zhuǎn)換為絕對引用。絕對引用的單元格地址在復(fù)制公式時(shí)不會改變。例如，在A1單元格中輸入`=$B$1`，然后將該公式復(fù)制到其他單元格時(shí)，引用的始終是B1單元格的值。混合引用混合引用是介于相對引用和絕對引用之間的一種引用方式。它允許對行或列進(jìn)行絕對引用，而對另一維度進(jìn)行相對引用。例如，`=B$1`表示列是相對引用而行是絕對引用。單元格引用方式2024/3/238PART02文本處理函數(shù)2024/3/239文本連接使用`&`符號或`CONCATENATE`函數(shù)可以將多個文本字符串連接成一個字符串。例如，`=A1&""&B1`可以將A1和B1單元格的內(nèi)容連接起來，并在中間添加一個空格。文本拆分Excel沒有直接的文本拆分函數(shù)，但可以通過結(jié)合使用`LEFT`、`RIGHT`、`MID`等函數(shù)來實(shí)現(xiàn)文本的拆分。例如，可以使用`=LEFT(A1,5)`來獲取A1單元格中前5個字符。文本連接與拆分2024/3/2310文本查找與替換查找文本使用`FIND`和`SEARCH`函數(shù)可以在文本字符串中查找子字符串的位置。例如，`=FIND("abc",A1)`可以查找A1單元格中"abc"子字符串的起始位置。替換文本使用`SUBSTITUTE`函數(shù)可以在文本字符串中替換指定的子字符串。例如，`=SUBSTITUTE(A1,"old","new")`可以將A1單元格中的"old"子字符串替換為"new"。2024/3/2311使用`UPPER`、`LOWER`和`PROPER`函數(shù)可以分別將文本轉(zhuǎn)換為大寫、小寫和首字母大寫的格式。例如，`=UPPER(A1)`可以將A1單元格的內(nèi)容轉(zhuǎn)換為大寫。大小寫轉(zhuǎn)換使用`VALUE`函數(shù)可以將文本轉(zhuǎn)換為數(shù)值，而`TEXT`函數(shù)則可以將數(shù)值轉(zhuǎn)換為指定格式的文本。例如，`=VALUE(A1)`可以將A1單元格的文本內(nèi)容轉(zhuǎn)換為數(shù)值，而`=TEXT(A1,"0.00")`可以將A1單元格的數(shù)值轉(zhuǎn)換為保留兩位小數(shù)的文本格式。數(shù)值與文本轉(zhuǎn)換文本格式轉(zhuǎn)換2024/3/2312PART03數(shù)學(xué)計(jì)算函數(shù)2024/3/2313乘法使用“*”符號進(jìn)行乘法運(yùn)算。加法使用“+”符號進(jìn)行加法運(yùn)算。減法使用“-”符號進(jìn)行減法運(yùn)算。除法使用“/”符號進(jìn)行除法運(yùn)算。冪運(yùn)算使用“^”符號進(jìn)行冪運(yùn)算，表示將某個數(shù)乘以自身指定的次數(shù)。四則運(yùn)算及冪運(yùn)算2024/3/2314SIN(angle)，返回給定角度的正弦值。三角函數(shù)及反三角函數(shù)正弦函數(shù)COS(angle)，返回給定角度的余弦值。余弦函數(shù)TAN(angle)，返回給定角度的正切值。正切函數(shù)ASIN(value)，返回給定值的反正弦角度。反正弦函數(shù)ACOS(value)，返回給定值的反余弦角度。反余弦函數(shù)ATAN(value)，返回給定值的反正切角度。反正切函數(shù)2024/3/2315數(shù)組公式可以同時(shí)處理多個數(shù)據(jù)，并返回一個或多個結(jié)果。通過使用大括號“{}”來定義數(shù)組范圍或數(shù)組常量。數(shù)組公式介紹SUM(A1:B2*C1:D2)將對應(yīng)單元格相乘后的結(jié)果進(jìn)行求和。數(shù)組公式示例使用F9鍵可以將選定的數(shù)組公式部分轉(zhuǎn)換為值，以便更好地理解和調(diào)試公式。同時(shí)，還可以通過使用TRANSPOSE函數(shù)對數(shù)組進(jìn)行轉(zhuǎn)置操作。數(shù)組公式應(yīng)用技巧數(shù)組公式應(yīng)用2024/3/2316PART04邏輯判斷函數(shù)2024/3/2317示例語法IF(logical_test,value_if_true,value_if_false)CHOOSE函數(shù)根據(jù)索引號從參數(shù)列表中選擇并返回一個值。語法CHOOSE(index_num,value1,[value2],…)根據(jù)指定的條件進(jìn)行邏輯判斷，返回相應(yīng)的結(jié)果。IF函數(shù)示例=IF(A1>B1,"A1大于B1","A1小于等于B1")=CHOOSE(2,"選項(xiàng)1","選項(xiàng)2","選項(xiàng)3")返回"選項(xiàng)2"。條件判斷與選擇2024/3/2318語法AND(logical1,[logical2],…)OR函數(shù)檢查參數(shù)中是否有TRUE，如果有，則返回TRUE。示例=OR(A1>B1,B1=C1)當(dāng)A1大于B1或B1等于C1時(shí)返回TRUE。AND函數(shù)檢查所有參數(shù)是否均為TRUE，如果均為TRUE，則返回TRUE。示例=AND(A1>B1,B1>C1)當(dāng)A1大于B1且B1大于C1時(shí)返回TRUE。語法OR(logical1,[logical2],…)010203040506多條件組合判斷2024/3/2319錯誤值處理IFERROR函數(shù)如果公式的計(jì)算結(jié)果為錯誤，則返回指定的值。語法IFERROR(value,value_if_error)示例=IFERROR(A1/B1,0)當(dāng)B1為0導(dǎo)致除數(shù)為零錯誤時(shí)，返回0。ISERROR函數(shù)檢查一個值是否為錯誤值，如果是，則返回TRUE。語法ISERROR(value)示例=ISERROR(A1/B1)當(dāng)A1除以B1產(chǎn)生錯誤時(shí)返回TRUE。2024/3/2320PART05日期時(shí)間處理函數(shù)2024/3/23210102DATE函數(shù)將年、月、日三個參數(shù)組合成一個日期。TIME函數(shù)將時(shí)、分、秒三個參數(shù)組合成一個時(shí)間。DATEVALUE…將文本格式的日期轉(zhuǎn)換為日期格式。TIMEVALUE…將文本格式的時(shí)間轉(zhuǎn)換為時(shí)間格式。自定義日期時(shí)間格式通過設(shè)置單元格格式，可以自定義日期時(shí)間的顯示方式，如"yyyy-mm-dd"、"hh:mm:ss"等。030405日期時(shí)間格式設(shè)置2024/3/232201TODAY函數(shù)：返回當(dāng)前日期。02NOW函數(shù)：返回當(dāng)前日期和時(shí)間。03DATEDIF函數(shù)：計(jì)算兩個日期之間的天數(shù)、月數(shù)或年數(shù)差異。04YEAR、MONTH、DAY函數(shù)：分別提取日期中的年、月、日信息。05HOUR、MINUTE、SECOND函數(shù)：分別提取時(shí)間中的時(shí)、分、秒信息。06日期時(shí)間比較：可以直接使用比較運(yùn)算符（如=、>、<等）對日期和時(shí)間進(jìn)行比較。日期時(shí)間計(jì)算與比較2024/3/23230102EDATE函數(shù)返回指定月份數(shù)之后的日期。EOMONTH函數(shù)返回指定月份的最后一天的日期。`NETWORKDAY…計(jì)算兩個日期之間的工作日天數(shù)（排除周末和指定假期）。WORKDAY函數(shù)返回指定工作日天數(shù)之后的日期。日期時(shí)間周期性處理結(jié)合使用上述函數(shù)，可以處理諸如“下個月的第一個工作日”、“本季度最后一天的日期”等周期性事件。030405周期性事件處理2024/3/2324PART06數(shù)據(jù)統(tǒng)計(jì)與分析函數(shù)2024/3/2325

數(shù)據(jù)排序與篩選排序功能EXCEL提供了多種排序方式，如升序、降序、自定義排序等，可以方便地對數(shù)據(jù)進(jìn)行排序。篩選功能通過篩選功能，可以快速找出符合特定條件的數(shù)據(jù)，便于進(jìn)行數(shù)據(jù)分析和處理。高級篩選高級篩選功能可以實(shí)現(xiàn)更復(fù)雜的篩選需求，如同時(shí)滿足多個條件的數(shù)據(jù)篩選。2024/3/2326EXCEL的分類匯總功能可以按照指定的字段對數(shù)據(jù)進(jìn)行分類，并計(jì)算各類別的匯總值，如求和、平均值、最大值、最小值等。分類匯總功能可以實(shí)現(xiàn)多級分類匯總，即在已分類的基礎(chǔ)上再進(jìn)行細(xì)分，滿足更復(fù)雜的分析需求。嵌套分類匯總除了系統(tǒng)提供的匯總方式外，還可以通過自定義公式實(shí)現(xiàn)個性化的分類匯總。自定義分類匯總數(shù)據(jù)分類匯總2024/3/232703數(shù)據(jù)透視表分析利用數(shù)據(jù)透視表提供的切片器、篩選器等工具，可以對數(shù)據(jù)進(jìn)行深入分析，發(fā)現(xiàn)數(shù)據(jù)間的關(guān)聯(lián)和趨勢。01數(shù)據(jù)透視表創(chuàng)建通過數(shù)據(jù)透視表功能，可以將大量數(shù)據(jù)轉(zhuǎn)換成易于理解的報(bào)表形式，便于進(jìn)行數(shù)據(jù)分析和決策。02字段設(shè)置與布局在數(shù)據(jù)透視表中，可以通過拖拽字段的方式靈活設(shè)置報(bào)表的布局和顯示內(nèi)容。數(shù)據(jù)透視表應(yīng)用2024/3/2328PART07自定義函數(shù)與宏應(yīng)用2024/3/2329使用VBA編輯器打開Excel，按Alt+F11進(jìn)入VBA編輯器，插入模塊并在模塊中輸入函數(shù)代碼。函數(shù)結(jié)構(gòu)自定義函數(shù)需遵循特定的結(jié)構(gòu)，包括函數(shù)名、參數(shù)、返回值和函數(shù)體。調(diào)試與測試在VBA編輯器中，可以使用調(diào)試工具對自定義函數(shù)進(jìn)行調(diào)試和測試，確保函數(shù)正確無誤。自定義函數(shù)編寫方法2024/3/2330宏錄制01在Excel中，可以通過“開發(fā)者”選項(xiàng)卡中的“錄制宏”功能來錄制宏。在錄制過程中，可以執(zhí)行一系列操作，這些操作將被記錄為宏代碼。宏編輯02錄制的宏可以在VBA編輯器中進(jìn)行編輯和修改。通過編輯宏代碼，可以優(yōu)化宏的性能、添加錯誤處理等功能。宏安全性03在使用宏時(shí)，需要注意宏安