日常EXCEL函數(shù)應(yīng)用實(shí)例ppt課件

.,日常EXCEL函數(shù)應(yīng)用,EXCEL操作界面及設(shè)置,函數(shù)與公式通用技巧,常用函數(shù),.,Whereisit？,EXCEL操作界面及設(shè)置,在“視圖”選項(xiàng)卡中，通過(guò)勾選或取消勾選復(fù)選框來(lái)設(shè)置界面中顯示或者隱藏編輯欄、行號(hào)列標(biāo)、工作表標(biāo)簽等。,“公式”選項(xiàng)卡中，有“公式審核”、“保護(hù)”等常用函數(shù)公式。,.,Whereisit？,EXCEL操作界面及設(shè)置,右鍵單擊工具欄“自定義快速訪問(wèn)工具欄”彈出自定義快速訪問(wèn)工具欄選項(xiàng)卡，可以設(shè)置自定義訪問(wèn)工具欄。,EXCEL操作界面及設(shè)置,函數(shù)與公式通用技巧,常用函數(shù),.,函數(shù)與公式通用技巧,實(shí)例,.,函數(shù)與公式通用技巧,.,函數(shù)與公式通用技巧,限制：,突破：,.,函數(shù)與公式通用技巧改變工作表的計(jì)算精度,在實(shí)際工作中，許多用戶在計(jì)算數(shù)字時(shí)會(huì)設(shè)置單元格格式為2為小數(shù)的數(shù)值、貨幣格式或者是整數(shù)值。但在使用公式進(jìn)行數(shù)據(jù)處理的過(guò)程中，經(jīng)常會(huì)發(fā)現(xiàn)幾個(gè)單元格之和與合計(jì)單元格顯示的數(shù)據(jù)之和不相等。,因?yàn)閑xcel將數(shù)值按照15位計(jì)算精度帶入計(jì)算所致。如圖，在excel選項(xiàng)中勾選“將精度設(shè)為所顯示的精度”，可以解決上述情況的發(fā)生，但這樣也降低了工作薄中所有工作表的計(jì)算精度，但不影響其他工作薄。此外，也可以用ROUND函數(shù)對(duì)數(shù)值進(jìn)行修約后再求和。,.,函數(shù)與公式通用技巧分布查看公式計(jì)算結(jié)果,方法一：選中包含公式的單元格，單擊“公式”選項(xiàng)卡中的“公式求值”按鈕，彈出“公式求值”對(duì)話框，然后多次單擊“求值”按鈕，“求值”域中將按照公式計(jì)算的順序逐步地顯示公式的計(jì)算過(guò)程。,方法二：功能鍵除了可以重算工作薄外，還可以用于查看公式的運(yùn)算結(jié)果。當(dāng)一個(gè)函數(shù)公式比較長(zhǎng)時(shí)，可以用鍵對(duì)各個(gè)部分進(jìn)行分段計(jì)算，以便公式差錯(cuò)和更正。在分段選擇時(shí)，注意要包含一個(gè)完整的運(yùn)算對(duì)象。,EXCEL操作界面及設(shè)置,函數(shù)與公式通用技巧,常用函數(shù),.,常用函數(shù)IF、IFERROR,根據(jù)對(duì)指定的條件計(jì)算結(jié)果為TRUE或FALSE，返回不同的結(jié)果?？梢允褂肐F對(duì)數(shù)值和公式執(zhí)行條件檢測(cè)。MicrosoftExcel還提供了其他一些函數(shù)，它們可根據(jù)條件來(lái)分析數(shù)據(jù)。例如，如果要計(jì)算某單元格區(qū)域內(nèi)某個(gè)文本字符串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIF和COUNTIFS電子表格函數(shù)。若要計(jì)算基于某區(qū)域內(nèi)一個(gè)文本字符串或一個(gè)數(shù)值的總和，可使用SUMIF和SUMIFS工作表函數(shù)。,.,=IFERROR(value,value_if_error),如果公式計(jì)算出錯(cuò)誤則返回您指定的值；否則返回公式結(jié)果。使用IFERROR函數(shù)來(lái)捕獲和處理公式中的錯(cuò)誤。Value是需要檢查是否存在錯(cuò)誤的參數(shù)。Value_if_error是公式計(jì)算出錯(cuò)誤時(shí)要返回的值。計(jì)算得到的錯(cuò)誤類型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。,常用函數(shù)IF、IFERROR,.,常用函數(shù)AND、OR,所有參數(shù)的計(jì)算結(jié)果為TRUE時(shí)，返回TRUE；只要有一個(gè)參數(shù)的計(jì)算結(jié)果為FALSE，即返回FALSE。通過(guò)AND函數(shù)可以檢驗(yàn)多個(gè)不同的條件，而不僅僅是一個(gè)條件。,=AND(logical1,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!。,.,常用函數(shù)LARGE,=LARGE(array,k),返回?cái)?shù)據(jù)集中第k個(gè)最大值。使用此函數(shù)可以根據(jù)相對(duì)標(biāo)準(zhǔn)來(lái)選擇數(shù)值。Array為需要從中選擇第k個(gè)最大值的數(shù)組或數(shù)據(jù)區(qū)域。K為返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域中的位置（從大到小排）。,注解如果數(shù)組為空，函數(shù)LARGE返回錯(cuò)誤值#NUM!。如果k0或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è)最小值。,.,常用函數(shù)MAX,=MAX(number1,number2,.),返回一組值中的最大值。注解參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。邏輯值和直接鍵入到參數(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)頻率最多（眾數(shù)）的數(shù)值。,.,常用函數(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ù)為錯(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ù)。,.,常用函數(shù)AVERAGEIF,=AVERAGEIF(range,criteria,average_range),注解：忽略區(qū)域中包含TRUE或FALSE的單元格。如果average_range中的單元格為空單元格，AVERAGEIF將忽略它。如果range為空值或文本值，則AVERAGEIF會(huì)返回#DIV0!錯(cuò)誤值。如果條件中的單元格為空單元格，AVERAGEIF就會(huì)將其視為0值。如果區(qū)域中沒(méi)有滿足條件的單元格，則AVERAGEIF會(huì)返回#DIV/0!錯(cuò)誤值。您可以在條件中使用通配符，即問(wèn)號(hào)(?)和星號(hào)(*)。Average_range不必與range的大小和形狀相同。求平均值的實(shí)際單元格是通過(guò)使用average_range中左上方的單元格作為起始單元格，然后加入與range的大小和形狀相對(duì)應(yīng)的單元格確定的。,range是要計(jì)算平均值的一個(gè)或多個(gè)單元格，其中包括數(shù)字或包含數(shù)字的名稱、數(shù)組或引用。criteria是數(shù)字、表達(dá)式、單元格引用或文本形式的條件，用于定義要對(duì)哪些單元格計(jì)算平均值。例如，條件可以表示為32、32、32、蘋果或B4。average_range是要計(jì)算平均值的實(shí)際單元格集。如果忽略，則使用range。,.,常用函數(shù)SUM,=SUM(number1,number2,.),說(shuō)明直接鍵入到參數(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ò)誤。,.,常用函數(shù)COUNT,=COUNT(value1,value2,.),注解如果參數(shù)為數(shù)字、日期或者代表數(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),.,常用函數(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返回文本字符串中從指定位置開(kāi)始的特定數(shù)目的字符，該數(shù)目由用戶指定。,RIGHT根據(jù)所指定的字符數(shù)返回文本字符串中最后一個(gè)或多個(gè)字符。,.,常用函數(shù)CONCATENATE,=CONCATENATE(text1,text2,.),將兩個(gè)或多個(gè)文本字符串合并為一個(gè)文本字符串。,注解您也可使用連接符號(hào)(&)計(jì)算運(yùn)算符代替CONCATENATE函數(shù)來(lái)連接文本項(xiàng)。例如=A1&B1與=CONCATENATE(A1,B1)返回相同的值。,.,常用函數(shù)SUBSTITUTE,=SUBSTITUTE(text,old_text,new_text,instance_num),Text為需要替換其中字符的文本，或?qū)形谋镜膯卧竦囊?。Old_text為需要替換的舊文本。New_text用于替換old_text的文本。Instance_num為一數(shù)值，用來(lái)指定以new_text替換第幾次出現(xiàn)的old_text。如果指定了instance_num，則只有滿足要求的old_text被替換；否則將用new_text替換Text中出現(xiàn)的所有old_text。,在文本字符串中用new_text替代old_text。,.,常用函數(shù)RAND、RANDBETWEEN,=RAND(),注解若要生成a與b之間的隨機(jī)實(shí)數(shù)，請(qǐng)使用：RAND()*(b-a)+a如果要使用函數(shù)RAND生成一隨機(jī)數(shù)，并且使之不隨單元格計(jì)算而改變，可以在編輯欄中輸入“=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ù)。,.,常用函數(shù)RANK,=RANK(number,ref,order),Number為需要找到排位的數(shù)字。Ref為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref中的非數(shù)值型參數(shù)將被忽略。Order為一數(shù)字，指明排位的方式。如果order為0（零）或省略，MicrosoftExcel對(duì)數(shù)字的排位是基于ref為按照降序排列的列表。如果order不為零，MicrosoftExcel對(duì)數(shù)字的排位是基于ref為按照升序排列的列表。注解函數(shù)RANK對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如，在一列按升序排列的整數(shù)中，如果整數(shù)10出現(xiàn)兩次，其排位為5，則11的排位為7（沒(méi)有排位為6的數(shù)值）。,返回一個(gè)數(shù)字在數(shù)字列表中的排位。,.,常用函數(shù)ROUND,=ROUND(number,num_digits),ROUND函數(shù)可將某個(gè)數(shù)字四舍五入為指定的位數(shù)。,說(shuō)明如果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ù)。,.,常用函數(shù)ROW、COLUMN,說(shuō)明：如果省略reference，則假定是對(duì)函數(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),.,常用函數(shù)MATCH,=MATCH(lookup_value,lookup_array,match_type),MATCH函數(shù)可在單元格區(qū)域（區(qū)域:工作表上的兩個(gè)或多個(gè)單元格。區(qū)域中的單元格可以相鄰或不相鄰。）中搜索指定項(xiàng)，然后返回該項(xiàng)在單元格區(qū)域中的相對(duì)位置。,.,常用函數(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)括起來(lái)。如果引用中的每個(gè)區(qū)域只包含一行或一列，則相應(yīng)的參數(shù)row_num或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。,.,常用函數(shù)INDEX,.,常用函數(shù)OFFSET,=OFFSET(reference,rows,cols,height,width),以指定的引用為參照系，通過(guò)給定偏移量得到新的引用。返回的引用可以為一個(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ù)。,.,常用函數(shù)VLOOKUP、HLOOKUP,