我為評委當(dāng)助手-完整精講版課件

1、EXCEL函數(shù)的綜合應(yīng)用一、 認(rèn)識函數(shù)1.函數(shù)格式：=函數(shù)名（參數(shù)1，參數(shù)2，參數(shù)n）注意事項：1.函數(shù)名與其后的括號之間不能有空格；2.當(dāng)有多個參數(shù)時，參數(shù)之間用逗號隔開；3.參數(shù)可以是數(shù)值、文本、邏輯值、單元格地址或 表達(dá)式、函數(shù)。4.函數(shù)中的逗號、引號都是英文狀態(tài)下的半角字符；2.參數(shù)的類型（1）數(shù)值。包括正數(shù)、負(fù)數(shù)、小數(shù)。（2）單元引用。（3）文字。必須用雙引號括住，否則出現(xiàn)錯誤值（#NAME？）（4）邏輯值。邏輯值可以是“TRUE”或“FALSE”，也可以用一表達(dá)式來取代。（5）名稱3.舉例（1）B2=LEN（”ACCO”）正確 B2=LEN（ACCO） 錯誤 注意：若事先利用插入中

2、的名稱命令將某一單元格定義為ACCO，則該函數(shù)可以返回ACCO中的值的長度。 4.引用的類別相對引用：行號和列標(biāo)前面都沒有$,如A1絕對引用：行號和列標(biāo)前面都有$,如$A$1混合引用： -絕對行引用: 如A$1 -絕對列引用： 如$A1求和類函數(shù)邏輯函數(shù)查找應(yīng)用類函數(shù)計數(shù)類函數(shù)財務(wù)函數(shù)求和類函數(shù)SUM:無條件求和，也可用于統(tǒng)計SUMIF：單條件求和，用于分類匯總SUMPRODUCT：多條件統(tǒng)計sum格式：=SUM（參數(shù)）注意：該函數(shù)形式簡單，但用途廣泛例1：計算某商店的累積銷量。 例2：包含文本的求和。例3：多表匯總。例4：條件求和SUMIF目的：用于單條件求和。格式：=SUMIF(range

3、,criteria,sum_range) -Range 為用于條件判斷的單元格區(qū)域。 -Criteria條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 32、“32”、“32” 或 “=60，”及格”，“不及格”) =IF(true，“開”，“關(guān)”) 等同于=IF（1， “開”，“關(guān)” ）注意：1.第三項參數(shù)可以省略。省略后，默認(rèn)為“FALSE”;若省略后，第二項參數(shù)的后面有逗號，則顯示“0”值。2.第一項參數(shù)可以是數(shù)字。1被視為“true”,非0數(shù)字視為“false”。例12：IF嵌套問題。2.AND、OR、NOT（一）AND函數(shù)1.表示邏輯與，當(dāng)所有條件都滿足時，AND函數(shù)返回

4、TRUE，否則返回FALSE。2.格式： =AND（條件1，條件2，條件n）（二）OR函數(shù)1.表示邏輯或，只要有一個條件都滿足時，該函數(shù)返回TRUE，當(dāng)所有條件都不滿足時才返回FALSE。2.格式： =OR（條件1，條件2，條件n）（三）NOT函數(shù)1.只有一個邏輯參數(shù)，可以計算出TRUE或FALSE的邏輯值或邏輯表達(dá)式。2.格式： =NOT（條件）注意：以上三個函數(shù)一般與IF函數(shù)結(jié)合使用例13：列示出既是黨員又是教授的人員的名字-if+and 案例查找與引用函數(shù)LookupVlookupIndexMatch功能：返回向量（單行或單列）或數(shù)組中的數(shù)值。 向量形式：是在單行或區(qū)域（向量）中查找數(shù)值

5、，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值； 格式：=LOOKUP(lookup_value,lookup_vector,result_vector） Lookup_vector 的數(shù)值必須按升序排序 LOOKUP數(shù)組形式： 在數(shù)組的第一行或第一列中查找指定數(shù)值，然后返回最后一行或最后一列中相同位置處的數(shù)值。 =LOOKUP(lookup_value,array) 注意： 1. array的第一行或第一列的數(shù)值必須按升序排序 2.如果數(shù)組區(qū)域中的列數(shù)多于行數(shù)，LOOKUP 在第一行查找 lookup_value。 3.如果數(shù)組為正方形，或者區(qū)域中的行數(shù)多于列數(shù)）則 在第一列查找 loo

6、kup_value。 注意： 1.首列要求升序排列 2.如果找不到 lookup_value，則它與 lookup_vector 中小于或等于 lookup_value 的最大值匹配。 3.如果 lookup_value 小于 lookup_vector 中的最小值，則 返回 #N/A 錯誤值?？梢匀〈鶬F的嵌套問題 例14：根據(jù)學(xué)號查找相關(guān)信息。例16：替代IF函數(shù)的嵌套問題。VLOOKUP 特點：查找值必須位于區(qū)域的第一列。 =VLOOKUP（LOOKUP-VALUE，TABLE-ARRAY，COL-INDEX-NUM， RANGE-LOOKUP） 其中： RANGE-LOOKUP： 邏輯

7、值，F(xiàn)ALSE表示精確匹配 TRUE（省略）近似匹配。 注意：為 TRUE時， table_array 的第一列中的數(shù)值必須按升序排列； 為 FALSE，table_array 不必進(jìn)行排序。 例17：根據(jù)成績單，查找相關(guān)信息。MATCH函數(shù)1.目的：返回在指定方式下與指定數(shù)值匹配 的數(shù)組中元素的相應(yīng)位置。2.格式： =MATCH（lookup-value，lookup-array，match-type） Lookup_value 為需要在數(shù)據(jù)表中查找的數(shù)值。 Lookup_array 可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域。 Match_type 為數(shù)字 -1、0 或 1。其中： 1：查找小

8、于或等于 lookup_value 的最大數(shù)值。Lookup_array 必須按升序排列0： 查找等于 lookup_value 的第一個數(shù)值。Lookup_array 可以按任何順序排列。-1： 查找大于或等于 lookup_value 的最小數(shù)值。Lookup_array 必須按降序排列。如果省略 match_type，則假設(shè)為 1。 INDEX函數(shù)1.目的：返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的 引用。2.格式一（返回值為數(shù)值或數(shù)組） =INDEX（array，row-num,column-num） array：單元區(qū)域或數(shù)組常量； row-num：數(shù)組中某行的行號，函數(shù)從該行返回數(shù) 值；若省

9、略，則必須有column-num column-num：數(shù)組中某列的列號，函數(shù)從該列返回 數(shù)值；若省略，則必須有row-num注意：1.若同時使用row-num和column-num， 該函數(shù)將返回row-num和column-num交叉 處單元格的數(shù)值； 2.若將row-num或column-num設(shè)置為0，則 該函數(shù)返回整個行或列的數(shù)組數(shù)值。但這 時，須按數(shù)組公式的形式輸入。 如： INDEX(1,2;3,4,2,2) 等于 4如果作為數(shù)組公式輸入，則：INDEX(1,2;3,4,0,2) 等于 2;4格式二：（返回值為引用）=INDEX(reference,row_num,column_

10、num,area_num)其中： 1. Reference 對一個或多個單元格區(qū)域的引用；如果為引用輸入一個不連續(xù)的選定區(qū)域，必須用括號括起來。 2.Row_num 行號 3.column_num：列標(biāo) 4.area_num：區(qū)域的標(biāo)號例18：match的用法例19：查找最優(yōu)的項目例17：當(dāng)姓名不在第一列的時候，如何根據(jù)學(xué)號查找姓名。文本函數(shù)LENLEFTRIGHTMIDFIND注意：該類函數(shù)返回值均為文本LEN 功能：返回文本字符串中的字符數(shù) 格式：=LEN (text)LEFT 功能：基于所指定的字符數(shù)返回文本字符串中的第一個或前幾個字符。 格式：=LEFT (text, num_char

11、s) 如果 num_chars 大于文本長度，則 LEFT 返回所有文本。RIGHT函數(shù)則返回最后一個或多個字符MID 功能： 返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。 格式：=MID(text,start_num,num_chars)例20：姓名對齊。例21：-工資票面分解表 -匯總滿足條件的冰箱的個數(shù)例22：提取身份證信息FIND 功能：用于查找指定文本字符串內(nèi)的相應(yīng)文本的位置。找不到，返回#VALUE. 格式：=FIND(find_text,within_text,start_num) 其中：start-num 表示從哪個文本開始查找，省略時，默認(rèn)為1，即首字符

12、。例23：利用文本函數(shù)實現(xiàn)分列統(tǒng)計函數(shù)CountCountif LargeSmalltrimmean計數(shù)函數(shù)-count功能：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個數(shù)。語法： =COUNT(value1,value2,.)其中：Value1, value2, . 為包含或引用各種類型數(shù)據(jù)的參數(shù)（1 到 30個），但只有數(shù)字類型的數(shù)據(jù)才被計算。說明：1、函數(shù) COUNT 在計數(shù)時，將把數(shù)字、日期、或以文本代表的數(shù)字計算在內(nèi)；但是錯誤值或其他無法轉(zhuǎn)換成數(shù)字的文字將被忽略。 2、如果參數(shù)是一個數(shù)組或引用，那么只統(tǒng)計數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文字或錯誤值都將被忽略

13、。如果要統(tǒng)計邏輯值、文字或錯誤值，使用函數(shù) COUNTA。條件計數(shù)-countif格式：= COUNTIF(range,criteria) 其中：Range 為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。 Criteria ：條件，其形式可以為數(shù)字、表達(dá)式、單元格引用或文本。如，條件可以表示為 32、“32”、“32” 、“apples” 或 B4。例24：查找滿足條件的水果。例25：統(tǒng)計有房員工的人數(shù)例26：統(tǒng)計病假及事假天數(shù)large功能：返回數(shù)據(jù)集中第 k 個最大值。使用此函數(shù)可以根據(jù)相對標(biāo)準(zhǔn)來選擇數(shù)值。例如，可以使用函數(shù) LARGE 得到第一名、第二名或第三名的得分。語法：LARGE

14、(array,k)說明：Array 為需要從中選擇第 k 個最大值的數(shù)組或數(shù)據(jù)區(qū)域。 K 為返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域中的位置（從大到小排）。SMALL功能：返回數(shù)據(jù)集中第 k 個最小值。使用此函數(shù)可以返回數(shù)據(jù)集中特定位置上的數(shù)值。語法：=SMALL(array,k)說明：Array 為需要找到第 k 個最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。 K 為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置（從小到大）。例27：large-small-tirm 案例。TRIMMEAN功能：返回數(shù)據(jù)集中的去除極值后的內(nèi)部平均值。該函數(shù) 先從數(shù)據(jù)集的頭部和尾部除去一定百分比的數(shù)據(jù)點，然后再求平均值。語法：=TRIMMEAN(a

15、rray,percent)說明：Array 為需要進(jìn)行整理并求平均值的數(shù)組或數(shù)值區(qū)域。 Percent 為計算時所要除去的數(shù)據(jù)點的比例，通常可用分?jǐn)?shù)表示，如2/7日期函數(shù)YearMonthDayDateDatedifToday例28：提取相應(yīng)的年，月，日。例29：根據(jù)身份證提取出生日期并計算年齡。DATEDIF功能：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。語法：DATEDIF(start_date,end_date,unit) -Start_date 代表時間段內(nèi)的起始日期 -End_date 代表時間段內(nèi)的結(jié)束日期 -Unit 為所需信息的返回類型 “Y”時間段中的整年數(shù)?！癕”時間段中的整月數(shù)

16、?！癉”時間段中的天數(shù)。 “YM”兩個日期中月數(shù)的差；“YD”兩個日期中天數(shù)的差。 這是Excel中的一個隱藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降模梢灾苯虞斎胧褂?例30：計算員工的工齡例31：日期函數(shù)綜合案例財務(wù)函數(shù)資金時間價值：PV FV PMTPPMTIPMT RATENPER投資決策：NPV ,IRR,MIRR折舊函數(shù)：SLN,SYD,DDB,VDB1.pv功能：用于求現(xiàn)值的函數(shù)。格式：=PV（rate，nper，pmt，fv，type）Type:0或省略，表示期末；1，表示期初PMT、FV用負(fù)數(shù)表示2.FV如果存折中已有1萬元，每月末將工資收入中的1500元存銀行，年利率是6%，那么3年后

17、存折上有多少錢？將10000元存入銀行，年利率是6%，按月計息，那么3年后存折上有多少錢？思考：你現(xiàn)有10000元，是存定期三年的利息多，還是存定期一年再自動轉(zhuǎn)存一年，到期再轉(zhuǎn)存一年獲得的利息多？一年期利率3%，3年期利率3.75%。通過計算，得知，存成三年定期，獲取的利息多。但假設(shè)前提是：三年中利率不會發(fā)生變動。如果從個人理財?shù)慕嵌葋砜矗艨紤]利率會隨時變動或者未來有急用的情況，那么，還是建議存款期設(shè)置為1年，以后進(jìn)行自動轉(zhuǎn)存。3.PMT功能：基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。格式：=PMT（ rate，nper，pv，fv，type ）4.PPMT功能：基于固定利

18、率及等額分期付款方式， 返回投資在某一給定期次內(nèi)的本金。格式： =PPMT（ rate，per，nper，pv，fv，type ）其中：per ：計算本金數(shù)額的期次5.IPMT功能：基于固定利率及等額分期付款方式， 返回投資在某一給定期次內(nèi)的利息。格式： =IPMT（ rate，per，nper，pv，fv，type ）其中：per ：計算本金數(shù)額的期次三者之間的關(guān)系： PMT=PPMT+IPMT 例32：某企業(yè)向銀行取得借款400萬元，期限5年，年利率8%，銀行要求該企業(yè)以等額攤還法償還借款，要求企業(yè)編制還款計劃。例34：假設(shè)開一家電器經(jīng)銷店。初期投資￥200,000，而希望未來五年中各年的

19、收入分別為￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的貼現(xiàn)率是8% ，問該項投資是否可行？6.NPV計算項目的凈現(xiàn)值特點： 參數(shù)必須為一組現(xiàn)金流； 流入用正數(shù)，流出為負(fù)數(shù)； 第一年初的數(shù)據(jù)不包含在參數(shù)中。注意：若已知條件中告訴的是年金，那么計算項目凈現(xiàn)值的時候，不能直接用NPV。 特別注意：1、如果參數(shù)是一個數(shù)組或引用，只有其中的數(shù)值部分計算在內(nèi)。忽略數(shù)組或引用中的空白單元格、邏輯值、文字及錯誤值。 例35：保險凈值的計算注意：當(dāng)一組現(xiàn)金流中均有數(shù)字的時候，用NPV計算的結(jié)果和PV計算的結(jié)果一樣；但當(dāng)一組現(xiàn)金流中最初的一段時間內(nèi)的現(xiàn)金流為空時，NPV和PV的結(jié)果有差異，此時，需要將NPV的結(jié)果再次進(jìn)行折現(xiàn)。7.rate函數(shù)功能：計算某項投資的實際贏利（回報率）格式： =RATE（nper，pmt，pv，fv，guess） 注意：（1）guess為估計利率值。 （2）只要知道Pmtpvfv中的任意兩項即可算出相 應(yīng)的利率，且這兩項的符號相反。 例36：某承包人建議你貸給他30000元，用作公共工程建設(shè)資金，并同意每年付給你9000元，共付五年，以此作為這筆貸款的最低回報。那么你如何去