計算機二級第三章公式和函數(shù)

計算機二級第三章公式和函數(shù)第1頁，共52頁，2023年，2月20日，星期二3.3Excel公式和函數(shù)本節(jié)案例背景：Excel提供大量實用函數(shù)滿足各類計算的需要。通過公式和函數(shù)計算出的結(jié)果不但正確率有保證，而且在原始數(shù)據(jù)發(fā)生改變后，計算結(jié)果能夠自動更新。在人事檔案管理和工資表的創(chuàng)建過程中，公式和函數(shù)給書娟的工作提供了相當(dāng)?shù)谋憷瑯O大地提高了工作的效率和效果。本節(jié)將通過對員工檔案表和員工工資表的處理，幫助書娟實現(xiàn)以下工作目標(biāo)：了解公式和函數(shù)的基本作用，了解在公式中各種引用的含義及方法。掌握公式的構(gòu)成、輸入方法，學(xué)會構(gòu)建公式以幫助計算。了解Excel提供了哪些類型的函數(shù)，掌握函數(shù)的基本輸入方法。掌握Excel中常用、重要函數(shù)的使用方法，并在實際工作中應(yīng)用它們。了解公式及函數(shù)應(yīng)用過程中各種常見問題及解決方法。2第2頁，共52頁，2023年，2月20日，星期二3.3.1使用公式的基本方法1.認(rèn)識公式公式是一組表達(dá)式，由單元格引用、常量、運算符、括號組成，復(fù)雜的公式還可以包括函數(shù)，用于計算生成新的值。在Excel中，公式總是以等號“=”開始。單元格引用：即單元格地址，用于表示單元格在工作表上所處位置的坐標(biāo)。例如，顯示在第B列和第3行交叉處的單元格，其引用形式為“B3”。常量：固定的數(shù)值或文本。例如，數(shù)字“210”和文本“姓名”均為常量。運算符：運算符用于連接常量、單元格引用，從而構(gòu)成完整的表達(dá)式。常用的運算符有：算術(shù)運算符，字符連接符，關(guān)系運算符。33章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第3頁，共52頁，2023年，2月20日，星期二3.3.1使用公式基本方法2.公式的輸入與編輯1）輸入公式=常量/單元格引用/表達(dá)式必須以等號“=”開始，在公式中所使用的運算符都必須是西文的半角字符例如：=30，=B3，=A5*10%，=C8&C943章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第4頁，共52頁，2023年，2月20日，星期二3.3.1使用公式基本方法2）修改公式用鼠標(biāo)雙擊公式進(jìn)入編輯狀態(tài)，在單元格或者編輯欄中均可對公式進(jìn)行修改即可。刪除公式：按Del鍵。3.公式的復(fù)制與填充拖動公式單元格右下角的填充柄“開始”選項卡→“編輯”組→“填充”按鈕。進(jìn)行公式的復(fù)制填充時，填充的實際上是公式而非數(shù)據(jù)本身，填充時公式中對單元格的引用采用的是相對引用。53章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第5頁，共52頁，2023年，2月20日，星期二3.3.1使用公式基本方法4.單位格引用在公式中最常用的是單元格引用。可以在單元格中引用一個單元格、一個單元格區(qū)域、引用另一個工作表或工作簿中的單元格或區(qū)域。單元格引用方式分為以下幾類：相對引用：如“=A1”。絕對引用：如“=$A$1”?；旌弦茫喝纭?A$1”、“=$A1”、“=$A$1”63章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第6頁，共52頁，2023年，2月20日，星期二3.3.2名稱的定義與引用為單元格或區(qū)域指定一個名稱，是實現(xiàn)絕對引用的方法之一。可以定義為名稱的對象包括：常量、單元格或單元格區(qū)域、公式。1.了解名稱的語法規(guī)則唯一性原則：名稱在其適用范圍內(nèi)必須始終唯一，不可重復(fù)。有效字符：名稱中第一個字符必須是字母、下劃線(_)或反斜杠(\)，名稱中不能使用大小寫字母“C”、“c”、“R”或“r”。不能與單元格地址相同：例如，名稱是A1、B$2等。不能使用空格：可選用下劃線(_)和句點(.)作為單詞分隔符。名稱長度有限制：一個名稱最多可以包含255個西文字符。不區(qū)分大小寫：例如，Sales與SALES視為同名。73章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第7頁，共52頁，2023年，2月20日，星期二3.3.2名稱的定義與引用2.為單元格或單元格區(qū)域定義名稱定義好的名稱將會在公式及函數(shù)中被引用。1）快速定義名稱選擇要命名的單元格或單元格區(qū)域，在編輯欄的“名稱框”中輸入名稱后按Enter鍵確認(rèn)。在案例工作簿文檔“3.3員工檔案及工資表”的“員工檔案表”工作表中，將數(shù)據(jù)列表區(qū)域A3:N38的名稱定義為“全體員工資料”。83章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第8頁，共52頁，2023年，2月20日，星期二3.3.2名稱的定義與引用2）將現(xiàn)有行和列標(biāo)題轉(zhuǎn)換為名稱選擇要命名的區(qū)域，必須包括行或列標(biāo)題→“公式”選項卡→“定義的名稱”組→“從所選內(nèi)容創(chuàng)建”按鈕→在對話框中，通過選中“首行”、“左列”、“末行”或“右列”復(fù)選框來指定包含標(biāo)題的位置。在案例工作簿文檔“3.3員工檔案及工資表”的“員工檔案表”中，將“基本工資”和“工齡工資”兩列的首行轉(zhuǎn)換為相應(yīng)列數(shù)據(jù)的名稱。93章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第9頁，共52頁，2023年，2月20日，星期二3.3.2名稱的定義與引用3）使用“新名稱”對話框定義名稱“公式”選項卡→“定義的名稱”組→“定義名稱”按鈕→在“名稱”文本框中輸入名稱→在“范圍”下設(shè)定名稱的適用范圍→在“備注”說明性批注?！耙梦恢谩笨蛑行薷拿麑ο螅梢允菃卧駞^(qū)域、常量、公式），可選擇下列操作之一。在案例工作簿文檔“3.3員工檔案及工資表”的“員工檔案表”中，將工齡工資常量“50”元定義為名稱“工齡工資_每年”。103章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第10頁，共52頁，2023年，2月20日，星期二3.3.2名稱的定義與引用3.引用名稱名稱可直接用來快速選定已命名的區(qū)域，更重要的是可以在公式中引用名稱以實現(xiàn)精確引用。1）通過“名稱框”引用單擊編輯欄中“名稱框”右側(cè)的黑色箭頭，打開“名稱”下拉列表→單擊選擇某一名稱提示：通過名稱框打開的“名稱”下拉列表中不包括常量和公式的名稱。113章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第11頁，共52頁，2023年，2月20日，星期二3.3.2名稱的定義與引用2）在公式中引用“公式”選項卡→“定義的名稱”組→“用于公式”按鈕→選擇名稱4.更改或刪除名稱如果更改了某個已定義的名稱，則工作簿中所有已引用該名稱的位置均會自動隨之更新。更改名稱：“公式”選項卡→“定義的名稱”組→“名稱管理器”按鈕→在名稱列表中選擇要更改的名稱→“編輯”按鈕→在“編輯名稱”對話框中修改名稱屬性。刪除名稱：“公式”選項卡→“定義的名稱”組→“名稱管理器”按鈕→在名稱列表中選擇名稱→“刪除”按鈕。提示：若公式中已引用的某個名稱被刪除，可能導(dǎo)致公式出錯。123章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第12頁，共52頁，2023年，2月20日，星期二3.3.3使用函數(shù)的基本方法1.認(rèn)識函數(shù)函數(shù)實際上特殊的公式，主要是為解決那些復(fù)雜計算需求而提供的一種預(yù)置算法，如求和函數(shù)SUM，平均值函數(shù)EVERAGE，條件函數(shù)IF等。函數(shù)通常表示為：函數(shù)名（[參數(shù)1],

[參數(shù)2],……）函數(shù)中的參數(shù)可以是常量、單元格地址、數(shù)組、已定義的名稱、公式、函數(shù)等。輸入函數(shù)時必須以等號“=”開始。2.

Excel函數(shù)分類Excel提供大量工作表函數(shù)，并按其功能進(jìn)行分類。Excel2010目前默認(rèn)提供的函數(shù)類別共13大類，見表3.3中所列。133章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第13頁，共52頁，2023年，2月20日，星期二表3.3Excel2010函數(shù)類別函數(shù)類別常用函數(shù)示例及說明財務(wù)函數(shù)NPV(rate,value1,[value2],...)返回一項投資的凈現(xiàn)值。日期和時間函數(shù)YEAR(serial_number)返回某日期對應(yīng)的年份數(shù)學(xué)和三角函數(shù)INT(number)將數(shù)字向下舍入到最接近的整數(shù)。統(tǒng)計函數(shù)AVERAGE(number1,[number2],...)返回參數(shù)的算術(shù)平均值查找和引用函數(shù)VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])搜索某個單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格中的值數(shù)據(jù)庫函數(shù)DCOUNTA(database,field,criteria)返回滿足指定條件的非空單元格的個數(shù)。文本函數(shù)MID(text,start_num,num_chars)返回文本字符串中從指定位置開始的特定數(shù)目的字符邏輯函數(shù)IF(logical_test,[value_if_true],[value_if_false])若指定條件的計算結(jié)果為TRUE，將返回某個值；若該條件的計算結(jié)果為FALSE，則返回另一個值。信息函數(shù)ISBLANK(value)檢驗單元格值是否為空，若為空則返回TRUE工程函數(shù)CONVERT(number,from_unit,to_unit)將數(shù)字從一個度量系統(tǒng)轉(zhuǎn)換到另一個度量系統(tǒng)中。兼容性函數(shù)RANK(number,ref,[order])返回一個數(shù)字在數(shù)字列表中的排位多維數(shù)據(jù)集函數(shù)CUBEVALUE(connection,member_expression1,member_expression2…)從多維數(shù)據(jù)集中返回匯總值與加載項一起安裝的用戶自定義函數(shù)如果在系統(tǒng)中安裝了某一包含函數(shù)的應(yīng)用程序，該程序做為Excel的加載項，其所包含的函數(shù)作用自定義函數(shù)顯示在這里以供選用。3.3Excel公式和函數(shù)14第14頁，共52頁，2023年，2月20日，星期二3.3.3使用函數(shù)的基本方法3.函數(shù)的輸入與編輯函數(shù)的輸入方式與公式類似，可以直接在單元格中輸入“=函數(shù)名(所引用的參數(shù))”，但是要想記住每一個函數(shù)名并正確輸入所有參數(shù)是有相當(dāng)困難的。因此，通常情況采用參照的方式輸入一個函數(shù)。1）通過“函數(shù)庫”組插入“公式”選項卡→“函數(shù)庫”組中的某一函數(shù)類別→從函數(shù)列表中單擊函數(shù)→在“函數(shù)參數(shù)”對話框中輸入或選擇參數(shù)153章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第15頁，共52頁，2023年，2月20日，星期二3.3.3使用函數(shù)的基本方法2）通過“插入函數(shù)”按鈕插入“公式”選項卡→“函數(shù)庫”組→“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框→在“選擇類別”下拉表中選擇函數(shù)類別，或者在“搜索函數(shù)”框中輸入函數(shù)的簡單描述后單擊“轉(zhuǎn)到”按鈕→在“選擇函數(shù)”列表中選擇函數(shù)→在“函數(shù)參數(shù)”對話框中輸入?yún)?shù)。3）修改函數(shù)在包含函數(shù)的單元格的雙擊鼠標(biāo)，進(jìn)入編輯狀態(tài)，對函數(shù)參數(shù)進(jìn)行修改后按Enter鍵確認(rèn)。163章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第16頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用1.

Excel中常用函數(shù)簡介1）求和函數(shù)SUM(number1,[number2],...])功能：將指定的參數(shù)number1、number2……相加求和。例如：=SUM(A1:A5)是將單元格A1至A5中的所有數(shù)值相加=SUM(A1,A3,A5)是將單元格A1、A3和A5中的數(shù)字相加。173章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第17頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用2）條件求和函數(shù)SUMIF(range,criteria,[sum_range])功能：對指定單元格區(qū)域中符合指定條件的值求和。提示：在函數(shù)中任何文本條件或任何含有邏輯或數(shù)學(xué)符號的條件都必須使用雙引號(")括起來。如果條件為數(shù)字，則無需使用雙引號。例如：=SUMIF(B2:B25,">5")表示對B2:B25區(qū)域大于5的數(shù)值進(jìn)行相加；=SUMIF(B2:B5,"John",C2:C5)，表示對單元格區(qū)域C2:C5中與單元格區(qū)域B2:B5中等于“John”的單元格對應(yīng)的單元格中的值求和。183章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第18頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用3）多條件求和函數(shù)SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：對指定單元格區(qū)域中滿足多個條件的單元格求和。例如：=SUMIFS(A1:A20,B1:B20,">0",C1:C20,"<10")表示對區(qū)域A1:A20中符合以下條件的單元格的數(shù)值求和：B1:B20中的相應(yīng)數(shù)值大于零、且C1:C20中的相應(yīng)數(shù)值小于10。193章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第19頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用4）絕對值函數(shù)ABS(number)功能：返回數(shù)值number的絕對值例如：=ABS(-2)表示求-2的絕對值；=ABS(A2)，表示對單元格A2中的數(shù)值求取絕對值。5）向下取整函數(shù)INT(number)功能：將數(shù)值number向下舍入到最接近的整數(shù)例如：=INT(8.9)表示將8.9向下舍入到最接近的整數(shù)，結(jié)果為8；=INT(-8.9)表示將-8.9向下舍入到最接近的整數(shù)，結(jié)果為-9。203章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第20頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用6）四舍五入函數(shù)ROUND(number,num_digits)功能：將指定數(shù)值number按指定的位數(shù)num_digits進(jìn)行四舍五入。例如：=ROUND(25.7825,2)，表示將數(shù)值25.7825四舍五入為小數(shù)點后兩位。提示：如果希望始終進(jìn)行向上舍入，可使用ROUNDUP函數(shù)；如果希望始終始終進(jìn)行向下舍入，則應(yīng)使用ROUNDDOWN函數(shù)。213章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第21頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用7）取整函數(shù)TRUNC(number,[num_digits])功能：將指定數(shù)值number的小數(shù)部分截去，返回整數(shù)。num_digits為取整精度，默認(rèn)為0。例如：=TRUNC(8.9)表示取8.9的整數(shù)部分，結(jié)果為8；=TRUNC(-8.9)表示取-8.9的整數(shù)部分，結(jié)果為-8。223章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第22頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用8）垂直查詢函數(shù)VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])功能：搜索指定單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何指定單元格中的值。例如：=VLOOKUP(1,A2:C10,2)要查找的區(qū)域為A2:C10，因此A列為第1列，B列為第2列，C列則為第3列。表示使用近似匹配搜索A列（第1列）中的值1，如果在A列中沒有1，則近似找到A列中與1最接近的值，然后返回同一行中B列（第2列）的值。=VLOOKUP(0.7,A2:C10,3,FALSE)表示使用精確匹配在A列中搜索值0.7。如果A列中沒有0.7這個值，則所以返回一個錯誤#N/A。233章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第23頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用9）邏輯判斷函數(shù)IF(logical_test,[value_if_true],[value_if_false])功能：如果指定條件的計算結(jié)果為TRUE，IF函數(shù)將返回某個值；如果該條件的計算結(jié)果為FALSE，則返回另一個值。提示：在Excel2010中，最多可以使用64個IF函數(shù)進(jìn)行嵌套，以構(gòu)建更復(fù)雜的測試條件。也就是說，IF函數(shù)也可以作為value_if_true和value_if_false參數(shù)包含在另一個IF函數(shù)中。243章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第24頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用9）邏輯判斷函數(shù)（續(xù)）例如：=IF(A2>=60,"及格","不及格")表示，如果單元格A2中的值大于等于60，則顯示“及格”字樣，否則顯示“不及格”字樣；=IF(A2>=90,"優(yōu)秀",IF(A2>=80,"良好",IF(A2>=60,"及格","不及格")))表示下列對應(yīng)關(guān)系：253章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)單元格A2中的值公式單元格顯示的內(nèi)容A2>=90優(yōu)秀90>A2>=80良好80>A2>=60及格A2<60不及格第25頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用10）當(dāng)前日期和時間函數(shù)NOW()功能：返回當(dāng)前計算機系統(tǒng)的日期和時間。當(dāng)將數(shù)據(jù)格式設(shè)置為數(shù)值時，將返回當(dāng)前日期和時間所對應(yīng)的序列號，該序列號的整數(shù)部分表明其與1900年1月1日之間的天數(shù)。11）函數(shù)YEAR(serial_number)

功能：返回指定日期對應(yīng)的年份。返回值為1900到9999之間的整數(shù)例如：=YEAR(A2)當(dāng)在A2單元格中輸入日期2008/12/27時，該函數(shù)返回年份2008。注意：公式所在的單元格不能是日期格式。263章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第26頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用12）當(dāng)前日期函數(shù)

TODAY()功能：返回今天的日期。通過該函數(shù)，可以實現(xiàn)無論何時打開工作簿時工作表上都能顯示當(dāng)前日期；該函數(shù)也可以用于計算時間間隔，可以用來計算一個人的年齡。例如：=YEAR(TODAY())-1963假設(shè)一個人出生在1963年，該公式使用TODAY函數(shù)作為YEAR函數(shù)的參數(shù)來獲取當(dāng)前年份，然后減去1963，最終返回對方的年齡。273章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第27頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用13）平均值函數(shù)AVERAGE(number1,[number2],...)功能：求指定參數(shù)number1、number2……的算術(shù)平均值，最多可包含255個參數(shù)。例如：=AVERAGE(A2:A6)表示對單元格區(qū)域A2到A6中的數(shù)值求平均值=AVERAGE(A2:A6,C6)表示對單元格區(qū)域A2到A6中數(shù)值與C6中的數(shù)值求平均值。283章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第28頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用14）條件平均值函數(shù)AVERAGEIF(range,criteria,[average_range])

功能：對指定區(qū)域中滿足給定條件的所有單元格中的數(shù)值求算術(shù)平均值例如：=AVERAGEIF(A2:A5,"<5000")表示求單元格區(qū)域A2:A5中小于5,000的數(shù)值的平均值；=AVERAGEIF(A2:A5,">5000",B2:B5)表示對單元格區(qū)域B2:B5中與單元格區(qū)域A2:A5中大于5000的單元格所對應(yīng)的單元格中的值求平均值。293章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第29頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用15）多條件平均值函數(shù)AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)

功能：對指定區(qū)域中滿足多個條件的所有單元格中的數(shù)值求算術(shù)平均值例如：=AVERAGEIFS(A1:A20,B1:B20,">70",C1:C20,"<90")表示對區(qū)域A1:A20中符合以下條件的單元格的數(shù)值求平均值：B1:B20中的相應(yīng)數(shù)值大于70、且C1:C20中的相應(yīng)數(shù)值小于90。303章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第30頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用16）計數(shù)函數(shù)COUNT(value1,[value2],...)功能：統(tǒng)計指定區(qū)域中包含數(shù)值的個數(shù)。只對包含數(shù)字的單元格進(jìn)行計數(shù)。例如：=COUNT(A2:A8)表示統(tǒng)計單元格區(qū)域A2到A8中包含數(shù)值的單元格的個數(shù)。17）計數(shù)函數(shù)COUNTA(value1,[value2],...)功能：統(tǒng)計指定區(qū)域中不為空的單元格的個數(shù)?？蓪Π魏晤愋托畔⒌膯卧襁M(jìn)行計數(shù)。例如：=COUNTA(A2:A8)表示統(tǒng)計單元格區(qū)域A2到A8中非空單元格的個數(shù)。313章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第31頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用18）條件計數(shù)函數(shù)COUNTIF(range,criteria)功能：統(tǒng)計指定區(qū)域中滿足單個指定條件的單元格的個數(shù)例如：=COUNTIF(B2:B5,">55")表示統(tǒng)計單元格區(qū)域B2到B5中值大于55的單元格的個數(shù)。323章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第32頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用19）多條件計數(shù)函數(shù)COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)

功能：統(tǒng)計指定區(qū)域內(nèi)符合多個給定條件的單元格的數(shù)量?？梢詫l件應(yīng)用于跨多個區(qū)域的單元格，并計算符合所有條件的次數(shù)。例如：=COUNTIFS(A2:A7,">80",B2:B7,"<100")統(tǒng)計單元格區(qū)域A2到A7中包含大于80的數(shù)，同時在單元格區(qū)域B2到B7中包含小于100的數(shù)的行數(shù)。333章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第33頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用20）最大值函數(shù)MAX(number1,[number2],...)功能：返回一組值或指定區(qū)域中的最大值例如：=MAX(A2:A6)表示從單元格區(qū)域A2:A6中查找并返回最大數(shù)值。21）最小值函數(shù)MIN(number1,[number2],...)功能：返回一組值或指定區(qū)域中的最小值例如：=MIN(A2:A6)表示從單元格區(qū)域A2:A6中查找并返回最小數(shù)值。343章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第34頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用22）排位函數(shù)RANK.EQ(number,ref,[order])

RANK.AVG(number,ref,[order])功能：返回一個數(shù)值在指定數(shù)值列表中的排位；如果多個值具有相同的排位，使用函數(shù)RANK.AVG將返回平均排位；使用函數(shù)RANK.EQ則返回實際排位。例如：=RANK.EQ("3.5",A2:A6,1)表示求取數(shù)值3.5在單元格區(qū)域A2:A6中的數(shù)值列表中的升序排位。353章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第35頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用23）文本合并函數(shù)CONCATENATE(text1,[text2],...)功能：將幾個文本項合并為一個文本項?？蓪⒆疃?55個文本字符串聯(lián)接成一個文本字符串。聯(lián)接項可以是文本、數(shù)字、單元格地址或這些項目的組合。例如：=CONCATENATE(B2,"",C2)表示將單元格B2中的字符串、空格字符以及單元格C2中的值相連接，構(gòu)成一個新的字符串。提示：也可以用文本連結(jié)運算符“&”代替CONCATENATE函數(shù)來聯(lián)接文本項。例如，=A1&B1與=CONCATENATE(A1,B1)返回的值相同。363章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第36頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用24）截取字符串函數(shù)MID(text,start_num,num_chars)功能：從文本字符串中的指定位置開始返回特定個數(shù)的字符。例如：=MID(A2,7,4)表示從單元格A2中的文本字符串中的第7個字符開始提取4個字符。373章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第37頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用25）左側(cè)截取字符串函數(shù)LEFT(text,[num_chars])

功能：從文本字符串最左邊開始返回指定個數(shù)的字符，也就是最前面的一個或幾個字符。例如：=LEFT(A2,4)表示從單元格A2中的文本字符串中提取前四個字符。26）右側(cè)截取字符串函數(shù)RIGHT(text,[num_chars])功能：從文本字符串最右邊開始返回指定個數(shù)的字符，也就是最后面的一個或幾個字符。例如：=RIGHT

(A2,4)表示從單元格A2中的文本字符串中提取后四個字符。383章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第38頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用27）刪除空格函數(shù)TRIM(text)

功能：刪除指定文本或區(qū)域中的空格。在從其他應(yīng)用程序中獲取帶有不規(guī)則空格的文本時，可以使用函數(shù)TRIM。例如：=TRIM("第1季度")表示刪除中文文本的前導(dǎo)空格、尾部空格以及字間空格。28）字符個數(shù)函數(shù)LEN(text)功能：統(tǒng)計并返加指定文本字符串中的字符個數(shù)。例如：=LEN(A2)表示統(tǒng)計位于單元格A2中的字符串的長度。393章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第39頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用2.常用函數(shù)在實際工作中的應(yīng)用在案例工作簿文檔“3.3員工檔案及工資表（1）”中運用公式及函數(shù)。1）運用公式及函數(shù)完善員工檔案表在“員工檔案表”工作表中，需要運用公式和函數(shù)分別提取員工的生日、計算出員工的年齡、工齡以及工齡工資。①提取員工生日：公式1：=CONCATENATE(MID(F4,7,4),"年",MID(F4,11,2),"月",MID(F4,13,2),"日")公式2：=MID(F5,7,4)&"年"&MID(F5,11,2)&"月"&MID(F5,13,2)&"日"403章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第40頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用②計算員工年齡：輸入函數(shù)“=INT((TODAY()-G4)/365)”③計算員工的工齡：輸入函數(shù)“=INT((TODAY()-J4)/365)”④計算工齡工資：每滿一年工齡工資增加50元，用工齡乘以50即可計算工齡工資，可以通過絕對引用或已定義名稱來計算。使用絕對引用：“=K4*基礎(chǔ)數(shù)據(jù)!$B$4”，使用定義名稱：常量“50”已事先被命名為“工齡工資_每年”。輸入公式“=K5*工齡工資_每年”413章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第41頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用⑤計算基礎(chǔ)工資：輸入函數(shù)“=SUM(L4:M4)”。⑥統(tǒng)計全部員工數(shù)量：在“基礎(chǔ)數(shù)據(jù)”工作表的“員工總?cè)藬?shù)”處輸入函數(shù)“=COUNTA(員工檔案表!A4:A38)”。⑦統(tǒng)計女員工的數(shù)量：在“基礎(chǔ)數(shù)據(jù)”工作表的“女性員工”處輸入函數(shù)“=COUNTIF(員工檔案表!C4:C38,"女")”。423章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第42頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用⑧統(tǒng)計工資數(shù)據(jù)在“基礎(chǔ)數(shù)據(jù)”工作表中的相應(yīng)單元格中依次輸入下列函數(shù)以計算相關(guān)數(shù)據(jù)：基本工資總額：=SUM(基本工資)，基本工資列已被定義名稱，所以可直接被求和函數(shù)引用。管理人員工資總額：=SUMIF(員工檔案表!D4:D38,"管理",基本工資)，用條件求和函數(shù)計算“部門”屬于“管理”的所有人員的基本工資總和。平均基本工資：=AVERAGE(基本工資)。本科生平均基本工資：=AVERAGEIF(員工檔案表!I4:I38,"本科",基本工資)，用條件求平均值函數(shù)計算“學(xué)歷”為“本科”的所有人員的平均基本工資。最高基本工資：=MAX(基本工資)最低基本工資：=MIN(基本工資)433章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第43頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用2）運用公式及函數(shù)完善1月工資表在“1月工資表”中，需要利用函數(shù)和公式獲取員工的姓名、所在的部門、員工的基礎(chǔ)工資，并計算應(yīng)付工資、應(yīng)交個人所得稅、實付工資等工資項目。①獲取員工姓名、部門和基礎(chǔ)工資：利用VLOOKUP函數(shù)從員工檔案表中直接獲取相應(yīng)數(shù)據(jù)。獲取姓名：=VLOOKUP(B4,全體員工資料,2,FALSE)獲取部門：=VLOOKUP(B4,全體員工資料,4,FALSE)獲取基礎(chǔ)工資：=VLOOKUP(B4,全體員工資料,14,FALSE)443章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第44頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用②計算應(yīng)付工資應(yīng)付工資合計＝基礎(chǔ)工資+獎金+補貼－扣除病事假。③計算應(yīng)納稅所得額應(yīng)納稅所得額＝應(yīng)付工資合計－社保費用－費用減除標(biāo)準(zhǔn)。應(yīng)納稅所得額必須大于等于零，小于零則不用交稅，需要用IF函數(shù)進(jìn)行判斷。輸入函數(shù)“=IF((I4-J4-3500)>0,I4-J4-3500,0)”；也可以通過絕對引用基礎(chǔ)數(shù)據(jù)表中的減除標(biāo)準(zhǔn)構(gòu)建函數(shù)“=IF((I5-J5-基礎(chǔ)數(shù)據(jù)!$F$12)>0,I5-J5-基礎(chǔ)數(shù)據(jù)!$F$12,0)”453章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第45頁，共52頁，2023年，2月20日，星期二3.3.4Excel中常用函數(shù)的應(yīng)用④計算個人所得稅通過多級IF函數(shù)嵌套，可構(gòu)建出個人所得稅計算公式，并通過ROUND函數(shù)對計算結(jié)果保留2位小數(shù)。個人所得稅稅率表可參見“基礎(chǔ)數(shù)據(jù)”表中所列。=ROUND(IF(K4<=1500,K4*0.03,IF(K4<=4500,K4*0.1-105,IF(K4<=9000,K4*0.2-555,IF(K4<=35000,K4*0.25-1005,IF(K4<=55000,K4*0.3-2755,IF(K4<=80000,K4*0.35-5505,K4*0.45-13505)))))),2)⑤計算實付工資實付工資＝應(yīng)付工資合計-扣除社保-應(yīng)交個人所得稅。463章通過Excel創(chuàng)建并處理電子表格3.3Excel公式和函數(shù)第46頁，共52頁，2023年，2月20日，星期二3.3.5公式與函數(shù)常見問題在輸入公式或函數(shù)的過程中，當(dāng)輸入有誤時，單元格中常常會出現(xiàn)各種不同的錯誤結(jié)果。對這些提示的含義有所了解，有助于更好地發(fā)現(xiàn)并修正公式或函數(shù)中的錯誤。1.常見錯誤值列表473章通過Excel創(chuàng)建并處理電子表格3.3Excel