日常EXCEL函數(shù)應(yīng)用實(shí)例PPT學(xué)習(xí)課件

1、日常EXCEL函數(shù)應(yīng)用,1,EXCEL操作界面及設(shè)置,函數(shù)與公式通用技巧,常用函數(shù),Where is it？,EXCEL操作界面及設(shè)置,在“視圖”選項(xiàng)卡中，通過勾選或取消勾選復(fù)選框來設(shè)置界面中顯示或者隱藏編輯欄、行號(hào)列標(biāo)、工作表標(biāo)簽等。,“公式”選項(xiàng)卡中，有“公式審核”、“保護(hù)”等常用函數(shù)公式。,3,Where is it？,EXCEL操作界面及設(shè)置,右鍵單擊工具欄“自定義快速訪問工具欄”彈出自定義快速訪問工具欄選項(xiàng)卡，可以設(shè)置自定義訪問工具欄。,4,EXCEL操作界面及設(shè)置,函數(shù)與公式通用技巧,常用函數(shù),函數(shù)與公式通用技巧,實(shí) 例,6,函數(shù)與公式通用技巧,7,函數(shù)與公式通用技巧,限制：,突破

2、：,使用定義名稱。,當(dāng)參數(shù)為單元格區(qū)域引用時(shí)，可使用一對(duì)括號(hào)來包含多個(gè)單元格區(qū)域的引用部分形成合并區(qū)域引用作為函數(shù)參數(shù)。,1、提前將單元格格式設(shè)置為文本后再輸入數(shù)字 2、在輸入數(shù)字時(shí)用半角單引號(hào)開頭。,公式內(nèi)容的長(zhǎng)度不能超過1024個(gè)字符；,公式中函數(shù)的嵌套不能超過7層；,公式中函數(shù)的參數(shù)不能超過30個(gè)；,數(shù)字計(jì)算精度為15位。,8,函數(shù)與公式通用技巧改變工作表的計(jì)算精度,在實(shí)際工作中，許多用戶在計(jì)算數(shù)字時(shí)會(huì)設(shè)置單元格格式為2為小數(shù)的數(shù)值、貨幣格式或者是整數(shù)值。但在使用公式進(jìn)行數(shù)據(jù)處理的過程中，經(jīng)常會(huì)發(fā)現(xiàn)幾個(gè)單元格之和與合計(jì)單元格顯示的數(shù)據(jù)之和不相等。,因?yàn)閑xcel將數(shù)值按照15位計(jì)算精度帶

3、入計(jì)算所致。如圖，在excel選項(xiàng)中勾選“將精度設(shè)為所顯示的精度”，可以解決上述情況的發(fā)生，但這樣也降低了工作薄中所有工作表的計(jì)算精度，但不影響其他工作薄。 此外，也可以用ROUND函數(shù)對(duì)數(shù)值進(jìn)行修約后再求和。,9,函數(shù)與公式通用技巧分布查看公式計(jì)算結(jié)果,方法一： 選中包含公式的單元格，單擊“公式”選項(xiàng)卡中的“公式求值”按鈕，彈出“公式求值”對(duì)話框，然后多次單擊“求值”按鈕，“求值”域中將按照公式計(jì)算的順序逐步地顯示公式的計(jì)算過程。,方法二： 功能鍵除了可以重算工作薄外，還可以用于查看公式的運(yùn)算結(jié)果。 當(dāng)一個(gè)函數(shù)公式比較長(zhǎng)時(shí)，可以用鍵對(duì)各個(gè)部分進(jìn)行分段計(jì)算，以便公式差錯(cuò)和更正。在分段選擇時(shí)，注

4、意要包含一個(gè)完整的運(yùn)算對(duì)象。,10,EXCEL操作界面及設(shè)置,函數(shù)與公式通用技巧,常用函數(shù),常用函數(shù)IF、IFERROR,根據(jù)對(duì)指定的條件計(jì)算結(jié)果為 TRUE 或 FALSE，返回不同的結(jié)果。可以使用 IF 對(duì)數(shù)值和公式執(zhí)行條件檢測(cè)。 Microsoft Excel 還提供了其他一些函數(shù)，它們可根據(jù)條件來分析數(shù)據(jù)。例如，如果要計(jì)算某單元格區(qū)域內(nèi)某個(gè)文本字符串或數(shù)字出現(xiàn)的次數(shù)，則可使用 COUNTIF 和 COUNTIFS 電子表格函數(shù)。若要計(jì)算基于某區(qū)域內(nèi)一個(gè)文本字符串或一個(gè)數(shù)值的總和，可使用 SUMIF 和 SUMIFS 工作表函數(shù)。,12, =IFERROR(value,value_if_

5、error),如果公式計(jì)算出錯(cuò)誤則返回您指定的值；否則返回公式結(jié)果。使用 IFERROR 函數(shù)來捕獲和處理公式中的錯(cuò)誤。 Value 是需要檢查是否存在錯(cuò)誤的參數(shù)。 Value_if_error 是公式計(jì)算出錯(cuò)誤時(shí)要返回的值。計(jì)算得到的錯(cuò)誤類型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!。,常用函數(shù)IF、IFERROR,13,常用函數(shù)AND、OR,所有參數(shù)的計(jì)算結(jié)果為 TRUE 時(shí)，返回 TRUE；只要有一個(gè)參數(shù)的計(jì)算結(jié)果為 FALSE，即返回 FALSE。 通過 AND 函數(shù)可以檢驗(yàn)多個(gè)不同的條件，而不僅僅是一個(gè)條件。,=AND(log

6、ical1, logical2, .),=OR(logical1,logical2,.),在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為 TRUE，即返回 TRUE；任何一個(gè)參數(shù)的邏輯值為 FALSE，即返回 FALSE。,注意： 參數(shù)的計(jì)算結(jié)果必須是邏輯值（如 TRUE 或 FALSE），而參數(shù)必須是包含邏輯值的數(shù)組 如果數(shù)組或引用參數(shù)中包含文本或空白單元格，則這些值將被忽略。 如果指定的單元格區(qū)域未包含邏輯值，則 函數(shù)將返回錯(cuò)誤值 #VALUE!。,14,常用函數(shù)LARGE,=LARGE(array,k),返回?cái)?shù)據(jù)集中第 k 個(gè)最大值。使用此函數(shù)可以根據(jù)相對(duì)標(biāo)準(zhǔn)來選擇數(shù)值。 Array 為需要從中選擇

7、第 k 個(gè)最大值的數(shù)組或數(shù)據(jù)區(qū)域。 K 為返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域中的位置（從大到小排）。,注解 如果數(shù)組為空，函數(shù) LARGE 返回錯(cuò)誤值 #NUM!。 如果 k 0 或 k 大于數(shù)據(jù)點(diǎn)的個(gè)數(shù)，函數(shù) LARGE 返回錯(cuò)誤值 #NUM!。 如果區(qū)域中數(shù)據(jù)點(diǎn)的個(gè)數(shù)為 n，則函數(shù) LARGE(array,1) 返回最大值，函數(shù) LARGE(array,n) 返回最小值。,=SMALL(array,k) 返回?cái)?shù)據(jù)集中第 k 個(gè)最小值。,15,常用函數(shù)MAX,=MAX(number1,number2,.),返回一組值中的最大值。 注解 參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。 邏輯值和直接

8、鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。 如果參數(shù)為數(shù)組或引用，則只使用該數(shù)組或引用中的數(shù)字。數(shù)組 或引用中的空白單元格、邏輯值或文本將被忽略。 如果參數(shù)不包含數(shù)字，函數(shù) MAX 返回 0（零）。 如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。 如果要使計(jì)算包括引用中的邏輯值和代表數(shù)字的文本，請(qǐng)使用 MAXA 函數(shù)。,=MIN(number1,number2,.) 返回一組值中的最小值。 =MEDIAN(number1,number2,.) 返回給定數(shù)值的中值。中值是在一組數(shù)值中居于中間的數(shù)值。 =MODE(number1,number2,.) 返回在某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最

9、多（眾數(shù)）的數(shù)值。,16,常用函數(shù)AVERAGE,=TRIMMEAN(array,percent)返回?cái)?shù)據(jù)集的內(nèi)部平均值。函數(shù) TRIMMEAN 先從數(shù)據(jù)集的頭部和尾部除去一定百分比的數(shù)據(jù)點(diǎn)，然后再求平均值。當(dāng)希望在分析中剔除一部分?jǐn)?shù)據(jù)的計(jì)算時(shí)，可以使用此函數(shù)。,=AVERAGE(number1, number2,.),返回參數(shù)的平均值（算術(shù)平均值）。,注釋： 參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、單元格區(qū)域 或單元格引用。 邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì) 算在內(nèi)。 如果區(qū)域或單元格引用參數(shù)包含文本、邏輯值或空單 元格，則這些值將被忽略；但包含零值的單元格將被計(jì)算在內(nèi)。 如果參數(shù)

10、為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。 若要在計(jì)算中包含引用中的邏輯值和代表數(shù)字的文本，請(qǐng)使用 AVERAGEA 函數(shù)。 若要只對(duì)符合某些條件的值計(jì)算平均值，請(qǐng)使用 AVERAGEIF 函數(shù)或 AVERAGEIFS 函數(shù)。,17,常用函數(shù)AVERAGEIF,=AVERAGEIF(range,criteria,average_range),注解： 忽略區(qū)域中包含 TRUE 或 FALSE 的單元格。 如果 average_range 中的單元格為空單元格，AVERAGEIF 將忽略它。 如果 range 為空值或文本值，則 AVERAGEIF 會(huì)返回 #DIV0! 錯(cuò)誤值。 如果條件中

11、的單元格為空單元格，AVERAGEIF 就會(huì)將其視為 0 值。 如果區(qū)域中沒有滿足條件的單元格，則 AVERAGEIF 會(huì)返回 #DIV/0! 錯(cuò)誤值。 您可以在條件中使用通配符，即問號(hào) (?) 和星號(hào) (*)。 Average_range 不必與 range 的大小和形狀相同。求平均值的實(shí)際單元格是通過使用 average_range 中左上方的單元格作為起始單元格，然后加入與 range 的大小和形狀相對(duì)應(yīng)的單元格確定的。,range是要計(jì)算平均值的一個(gè)或多個(gè)單元格，其中包括數(shù)字或包含數(shù)字的名稱、數(shù)組或引用。 criteria是數(shù)字、表達(dá)式、單元格引用或文本形式的條件，用于定義要對(duì)哪些單元

12、格計(jì)算平均值。例如，條件可以表示為 32、32、32、蘋果 或 B4。 average_range是要計(jì)算平均值的實(shí)際單元格集。如果忽略，則使用 range。,18,常用函數(shù)SUM,=SUM(number1,number2, .),說明 直接鍵入到參數(shù)表中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式將被計(jì)算，請(qǐng)參閱下面的示例一和示例二。 如果參數(shù)是一個(gè)數(shù)組或引用，則只計(jì)算其中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。請(qǐng)參閱下面的第三個(gè)示例。 如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。,19,常用函數(shù)COUNT,=COUNT(value1, value2,.),注解 如果參數(shù)為數(shù)

13、字、日期或者代表數(shù)字的文本（例如，用引號(hào)引起的數(shù)字，如 1），則將被計(jì)算在內(nèi)。 邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。 如果參數(shù)為錯(cuò)誤值或不能轉(zhuǎn)換為數(shù)字的文本，則不會(huì)被計(jì)算在內(nèi)。 如果參數(shù)為數(shù)組或引用，則只計(jì)算數(shù)組或引用中數(shù)字的個(gè)數(shù)。不會(huì)計(jì)算數(shù)組或引用中的空單元格、邏輯值、文本或錯(cuò)誤值。 若要計(jì)算邏輯值、文本值或錯(cuò)誤值的個(gè)數(shù)，請(qǐng)使用 COUNTA 函數(shù)。 若要只計(jì)算符合某一條件的數(shù)字的個(gè)數(shù)，請(qǐng)使用 COUNTIF 函數(shù)或 COUNTIFS 函數(shù)。,=COUNTIF(range,criteria) 計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格數(shù)。例如：=COUNTIF(A2:A7,=20)

14、,20,常用函數(shù)LEFT、MID、RIGHT,=LEFT(text,num_chars) =MID(text,start_num,num_chars) =RIGHT(text,num_chars),Text 是包含要提取字符的文本字符串。 Start_num 是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的 start_num 為 1，以此類推。 Num_chars 指定希望函數(shù)從文本中返回字符的個(gè)數(shù)。,根據(jù)所指定的字符數(shù)，LEFT 返回文本字符串中第一個(gè)字符或前幾個(gè)字符。,MID 返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。,RIGHT 根據(jù)所指定的字符數(shù)返回文本字

15、符串中最后一個(gè)或多個(gè)字符。,21,常用函數(shù)CONCATENATE,=CONCATENATE (text1,text2,.),將兩個(gè)或多個(gè)文本字符串合并為一個(gè)文本字符串。,注解 您也可使用連接符號(hào) (&) 計(jì)算運(yùn)算符代替 CONCATENATE 函數(shù)來連接文本項(xiàng)。例如 =A1&B1 與 =CONCATENATE(A1,B1) 返回相同的值。,22,常用函數(shù)SUBSTITUTE,=SUBSTITUTE(text,old_text,new_text,instance_num),Text 為需要替換其中字符的文本，或?qū)形谋镜膯卧竦囊谩?Old_text 為需要替換的舊文本。 New_text

16、用于替換 old_text 的文本。 Instance_num 為一數(shù)值，用來指定以 new_text 替換第幾次出現(xiàn)的 old_text。如果指定了 instance_num，則只有滿足要求的 old_text 被替換；否則將用 new_text 替換 Text 中出現(xiàn)的所有 old_text。,在文本字符串中用 new_text 替代 old_text。,23,常用函數(shù)RAND、RANDBETWEEN,=RAND( ),注解 若要生成 a 與 b 之間的隨機(jī)實(shí)數(shù)，請(qǐng)使用： RAND()*(b-a)+a 如果要使用函數(shù) RAND 生成一隨機(jī)數(shù)，并且使之不隨單元格計(jì)算而改變，可以在編輯欄中輸入

17、“=RAND()”，保持編輯狀態(tài)，然后按 F9，將公式永久性地改為隨機(jī)數(shù)。,返回大于等于 0 及小于 1 的均勻分布隨機(jī)實(shí)數(shù)，每次計(jì)算工作表時(shí)都將返回一個(gè)新的隨機(jī)實(shí)數(shù)。,=RANDBETWEEN(bottom,top),返回位于指定的兩個(gè)數(shù)之間的一個(gè)隨機(jī)整數(shù)。每次計(jì)算工作表時(shí)都將返回一個(gè)新的隨機(jī)整數(shù)。 Bottom函數(shù) RANDBETWEEN 將返回的最小整數(shù)。 Top函數(shù) RANDBETWEEN 將返回的最大整數(shù)。,24,常用函數(shù)RANK,=RANK(number,ref,order),Number 為需要找到排位的數(shù)字。 Ref 為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref 中的非數(shù)值型參數(shù)將

18、被忽略。 Order 為一數(shù)字，指明排位的方式。 如果 order 為 0（零）或省略，Microsoft Excel 對(duì)數(shù)字的排位是基于 ref 為按照降序排列的列表。 如果 order 不為零，Microsoft Excel 對(duì)數(shù)字的排位是基于 ref 為按照升序排列的列表。 注解 函數(shù) RANK 對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如，在一列按升序排列的整數(shù)中，如果整數(shù) 10 出現(xiàn)兩次，其排位為 5，則 11 的排位為 7（沒有排位為 6 的數(shù)值）。,返回一個(gè)數(shù)字在數(shù)字列表中的排位。,25,常用函數(shù)ROUND,=ROUND(number, num_digits),R

19、OUND 函數(shù)可將某個(gè)數(shù)字四舍五入為指定的位數(shù)。,說明 如果 num_digits 大于 0（零），則將數(shù)字四舍五入到指定的小數(shù)位。 如果 num_digits 等于 0，則將數(shù)字四舍五入到最接近的整數(shù)。 如果 num_digits 小于 0，則在小數(shù)點(diǎn)左側(cè)進(jìn)行四舍五入。 若要始終進(jìn)行向上舍入（遠(yuǎn)離 0），請(qǐng)使用 ROUNDUP 函數(shù)。 若要始終進(jìn)行向下舍入（朝向 0），請(qǐng)使用 ROUNDDOWN 函數(shù)。 若要將某個(gè)數(shù)字四舍五入為指定的倍數(shù)（例如，四舍五入為最接近的 0.5 倍），請(qǐng)使用 MROUND 函數(shù)。,26,常用函數(shù)ROW、COLUMN,說明： 如果省略 reference，則假定是對(duì)

20、函數(shù)所在單元格的引用。 如果 reference 為一個(gè)單元格區(qū)域，并且函數(shù)作為數(shù)組輸入，則函數(shù) 將以數(shù)組的形式返回 reference 的行號(hào)或列號(hào)。 如果參數(shù) reference 為一個(gè)單元格區(qū)域，并且函數(shù)不是以數(shù)組公式的形式輸入的，則 COLUMN 函數(shù)將返回最上側(cè)的行號(hào)或最左側(cè)列的列號(hào)。 Reference 不能引用多個(gè)區(qū)域。,=ROW(reference),=ROWS(array)返回引用或的行數(shù)。 =COLUMNS(array)返回?cái)?shù)組或引用的列數(shù)。,=COLUMN(reference),27,常用函數(shù)MATCH,=MATCH(lookup_value, lookup_array,

21、 match_type),MATCH 函數(shù)可在單元格區(qū)域 （區(qū)域:工作表上的兩個(gè)或多個(gè)單元格。區(qū)域中的單元格可以相鄰或不相鄰。）中搜索指定項(xiàng)，然后返回該項(xiàng)在單元格區(qū)域中的相對(duì)位置。,28,常用函數(shù)INDEX,數(shù)組形式: =INDEX(array,row_num,column_num) 引用形式: =INDEX(reference,row_num,column_num,area_num),Array 為單元格區(qū)域或數(shù)組常量。 Reference 對(duì)一個(gè)或多個(gè)單元格區(qū)域的引用。如果為引用輸入一個(gè)不連續(xù)的區(qū)域，必須將其用括號(hào)括起來。如果引用中的每個(gè)區(qū)域只包含一行或一列，則相應(yīng)的參數(shù) row_num

22、或 column_num 分別為可選項(xiàng)。 Row_num 引用中某行的行號(hào)，函數(shù)從該行返回一個(gè)引用。 Column_num 引用中某列的列標(biāo)，函數(shù)從該列返回一個(gè)引用。 Area_num 選擇引用中的一個(gè)區(qū)域，返回該區(qū)域中 row_num 和 column_num 的交叉區(qū)域。選中或輸入的第一個(gè)區(qū)域序號(hào)為 1，第二個(gè)為 2，以此類推。如果省略 area_num，則函數(shù) INDEX 使用區(qū)域 1。,29,常用函數(shù)INDEX,30,常用函數(shù)OFFSET,=OFFSET(reference,rows,cols,height,width),以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個(gè)單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。,Reference 作為偏移量參照系的引用區(qū)域。 Rows 相對(duì)于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。行數(shù)可為正數(shù)或負(fù)數(shù)。 Cols 相對(duì)于偏移量參照系的左上角單元格，左（右）偏移的列數(shù)。列數(shù)可為正數(shù)或負(fù)數(shù)。 Height 高度，即所要返回的引用區(qū)域的行數(shù)。Height 必須為正數(shù)。 Width 寬度，即所要返回的引用區(qū)域的列數(shù)。Width 必須為正數(shù)。,31,常用函