HR進階學習：Excel函數(shù)應用教程

Excel函數(shù)應用之函數(shù)簡介Excel函數(shù)應用之數(shù)學和三角函數(shù)Excel函數(shù)應用之邏輯函數(shù)Excel函數(shù)應用之文本/日期/時間函數(shù)Excel函數(shù)應用之查詢與引用函數(shù)Excel函數(shù)應用之統(tǒng)計函數(shù)Excel函數(shù)應用之工程函數(shù)Excel函數(shù)應用之財務函數(shù)Excel函數(shù)應用之信息函數(shù)Excel函數(shù)應用之數(shù)據(jù)庫函數(shù)（陸元婕）編者語：Excel是辦公室自動化中非常重要的一款軟件，很多巨型國際企業(yè)都是依靠Excel進行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進行圖形分析，其更強大的功能體現(xiàn)在對數(shù)據(jù)的自動處理和計算，然而很多缺少理工科背景或是對Excel強大數(shù)據(jù)處理功能不了解的人卻難以進一步深入。編者以為，對Excel函數(shù)應用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，然而目前這一部份內容的教學文章卻又很少見，所以特別組織了這一個《Excel函數(shù)應用》系列，希望能夠對Excel進階者有所幫助。Excel的數(shù)據(jù)處理功能在現(xiàn)有的文字處理軟件中可均值等簡單的函數(shù)應用上呢？難道Excel術語說明什么是參數(shù)？術語說明什么是參數(shù)？參數(shù)可以是數(shù)字、文本、形如TRUE或FALSE的邏輯值、數(shù)組、形如#N/A的錯誤值或單元格引用。給定的參數(shù)必須能產(chǎn)生有效的值。參數(shù)也可以是常量、公式或其它函數(shù)。參數(shù)不僅僅是常量、公式或函數(shù)，還可以是數(shù)組、單元格引用等：1.數(shù)組--用于建立可產(chǎn)生多個結果或可對存放在行和列中的一組參數(shù)進行運算的單個公式。在MicrosoftExcel有兩類數(shù)組：區(qū)域數(shù)組和常量數(shù)組。區(qū)域數(shù)組是一個矩形的單元格區(qū)域，該區(qū)域中的單元格共用一個公式；常量數(shù)組將一組給定的常量用作某個公式中的2.單元格引用--用于表示單元格在工作表所處位置的坐標值。例如，顯示在第B列和第3行交叉處的單元格，其引用形式為"B3"。3.常量--常量是直接鍵入到單元格或公式中的數(shù)字或文本值，或由名稱所代表的10/9/96、數(shù)字210和文本"QuarterlyEarnings"都是常量。公式或由公式得出的數(shù)值都不是常量。式出現(xiàn)，請在函數(shù)名稱前面鍵入等號（=）。在創(chuàng)建包含函數(shù)的公式圖2函數(shù)的結構圖3公式選項板圖4單元格編輯2.單擊編輯欄中"編輯公式"按鈕，將會在編輯欄下面出現(xiàn)一個"公式選項板"，此時"名稱"框將圖5函數(shù)列表框4.當選中所需的函數(shù)后，Excel2000將打開"公式選項板"。用戶可以在這個選項板中輸在了解了函數(shù)的基本知識及使用方法后，請跟隨筆者一起尋找Excel提供圖6粘貼函數(shù)列表售信息的數(shù)據(jù)清單中，可以計算出所有銷售數(shù)值大于1,000且小于2,500個函數(shù)均有三個相同的參數(shù)：database、field和criteria。這些參數(shù)指向數(shù)據(jù)庫函數(shù)所使用的工5在單元格滿足條件時返回TRUE。例如，如果單元格包含一個偶數(shù)值，ISEVEN工作表函數(shù)返回TRUE。如果需要確定某個單元格區(qū)域中是否存在空白單元格，可以使用COUNTBLANK工作表函數(shù)對單元格區(qū)域中的空白單元格進行計數(shù)，或者使用用工作表函數(shù)。例如，如果需要在表格中查找與第一列中的值相匹配的數(shù)值，可以使用VLOOKUP工作表函數(shù)。如果需要確10.文本函數(shù)--通過文本函數(shù)，可以在公式中處理文字串。例如，可以改變大小寫或確定入文字串或連接在文字串上。下面的公式為一個示例，借以說明如何使用函數(shù)TODAY和函數(shù)TEXT來創(chuàng)建一條信息，該信11.用戶自定義函數(shù)--如果要在公式或計算中使用特別復雜的計算，而工作表函數(shù)又無法定義函數(shù)。這些函數(shù)，稱為用戶自定義函數(shù)，可以通過使用Visu括SUBTOTAL、SUM、SUMIF、SUM這里筆者將以某單位工資表為例重點介紹SUM（計算一組參數(shù)之和）、SUMIF（7=SUM(D3:D12,F3:F12)-SUM(=SUMIF($C$3:$C$12,"銷售部",$F$38是要弧度值，因此必須先將角度值轉為弧度值。具體在Excel提供的"數(shù)學與三角函數(shù)"中提供了一個名為ROUND(number,nu數(shù)，將數(shù)字四舍五入。這個函數(shù)有兩個參數(shù)，分別是num對于數(shù)字進行四舍五入,還可以使用INT（取整函數(shù)），但由ASIN工作表函數(shù)將參數(shù)Number沿絕對值增大的方向，舍入為最接近的返回數(shù)的階乘，一個數(shù)的階乘等于1*2*將參數(shù)Number沿絕對值減小的方向去尾舍入，使其等于最接近的significance的倍數(shù)返回一個數(shù)的自然對數(shù)自然對數(shù)以常數(shù)項e（2.718281在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，返回兩數(shù)組中對應數(shù)值的平方和之和，平方和加總在統(tǒng)所有參數(shù)的邏輯值為真時返回TRUE；只要一個參數(shù)的邏語法為AND(logical1,logical2,...)，其中它的語法為IF(logical_test,value_if_true,value_if_falsValue_if_true顯示在logical_test為TRUE時返回的值，Value_if三個參數(shù)Value_if_false的結果。IF函則可使用COUNTIF工作表函數(shù)。如果要根據(jù)單元格=IF(SUM(C5:F5),SUM(C5:說明：為了解釋起來比較方便，我們在這里僅做兩重嵌套的示例，您可以按照實際情況進行=IF(F11>60,IF(AND(F11>90),"優(yōu)秀","合格")如果要計算單元格區(qū)域中某個文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIF工作表函數(shù)。如果要根文本串或數(shù)字求和，則可使用SUMIF工作COUNTIF可以用來計算給定區(qū)域內滿足特定條件的單元格的數(shù)目。比如在成績表語法形式為COUNTIF(range,criteria)。其中R）：銷售業(yè)績表可能是綜合運用IF、SUMIF、C=SUMIF($C$2:$C$13,A17,$B$2此公式在"銷售人員"清單$C$2:$C$13中檢查單元格A17中的文本（即銷售人員ANNIE），然后計算"訂單金額"列=IF(C17<50000,10%,1通過以上三個函數(shù)可以將文本轉換顯示樣式，使得文本變Lower（pLeaseComEHere!）=pleasecomupper（pLeaseComEHere!）=PLEASECOMEproper（pLeaseComEHere!）=PleaseCome您可以使用Mid、Left、Right等函數(shù)從MID函數(shù)：MID(text,start_num,比如，從字符串"Thisisanapple."分別取出字符從字符串"MynameisMary"中清除空格的函數(shù)寫法為：TRI是否完全相同。如果它們完全相同，則返回TRUE；否則，利用函數(shù)EXACT可以測試輸入文檔內的文字。語法形式為：EXACT(text1,text2EXACT("China","china")語法形式為：IF（logical_test,value_if_true語法形式為：CONCATENATE(text1,語法形式為：MID(text,start_num,nu語法形式為：TODAY()語法形式為：DATEDIF(start_date,end_da語法形式為：RIGHT(text,nu=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"a.RIGHT(E4,3)用于求出身份證號碼中代表性別的數(shù)字，實際求得的b.VALUE(RIGHT(E4,3)用于將上一步所得的代表數(shù)字的字符d.=IF(VALUE(RIGHT(E4,3))/2=CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4a.MID(E4,7,2)為在身份證號碼中獲取表示b.MID(E4,9,2)為在身份證號碼中獲取表示月份的c.MID(E4,11,2)為在身份證號碼中獲d.CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",=CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"b.DATEDIF(F4,TODAY(),"y")用于計算當前系統(tǒng)時間與參加工作時c.DATEDIF(F4,TODAY(),"ym")用于計算當前系統(tǒng)時間與參加工作時間相差的月d.=CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"個月")目在這張人事資料表中我們還發(fā)現(xiàn)，創(chuàng)建日期：31-05-200工添加的嗎？不是，實際上這個日期還是變化的，它顯示的是系統(tǒng)當前時間。這里是利用函數(shù)TODAY和函數(shù)具體公式寫法為：="創(chuàng)建日期："&TEXT(TODAY(),"返回對應于數(shù)字代碼的字符，函數(shù)CHAR可將其他類型刪除文本中不能打印的字符。對從其他應用程序中輸CLEAN函數(shù)，將刪除其中含有的當前操作系統(tǒng)無法打印的CONCATENATE(text1,texDOLLAR或RMB(number,deci否則，返回FALSE。函數(shù)EXACT能區(qū)分大小寫，但FIND(find_text,within_text,start_nuFIXED(number,decimals,no_coLEFTB基于所指定的字節(jié)數(shù)返回文本串中的第一個或前幾個字MID(text,start_num,num_chMIDB(text,start_num,num_bREPLACE使用其他文本串并根據(jù)所指定的字符數(shù)替換某文本串REPLACEB使用其他文本串并根據(jù)所指定的字符數(shù)替換某文本REPLACE(old_text,start_num,num_chars,new_teREPLACEB(old_text,start_num,num_bytes,new_te按照給定的次數(shù)重復顯示文本?？梢酝ㄟ^函數(shù)REPT來SEARCH返回從start_num開始首次找到特定回find_text的起始位置編號。此結果是基于每個字符所使SEARCH(find_text,within_teSEARCHB(find_text,within_text在文字串中用new_text替代old_text。如果需要在某一文的文本，請使用函數(shù)SUBSTITUTE；如果需要在某SUBSTITUTE(text,old_text,new_text,insTUPPERUPPER(text)使用￥（日圓）貨幣格式將數(shù)字轉換成文DATEDIF(start_date,end_date,uniDAYS360(start_date,end_date,methoEDATE(start_date,mon返回start-date之前或之后指定月份中最后一天的系列數(shù)。用函數(shù)EEOMONTH(start_date,montNETWORKDAYS返回參數(shù)start-data和end-data之間完整的工作日數(shù)值。工作日NETWORKDAYS(start_date,end_date,holidays)NOWNOW()TIME(hour,minute,se代表從0:00:00(12:00:00A返回當前日期的系列數(shù)，系列數(shù)是MicrosoftExcel用于日期和時返回某日期為星期幾。默認情況下，其值為1（星期WEEKDAY(serial_number,return_type)WEEKNUM(serial_num,return_type)返回某日期（起始日期）之前或之后相隔指定工作日的某一日期WORKDAY(start_date,days,holidayYEARFRAC(start_date,end_date,basi絕對引用--如果在復制公式時不希望E引用樣式中的絕對引用$A$1等價。如果活動單元格是A1，則單元格相對引用R[1]C[1]將引用下面特定數(shù)值，或者需要查找某一單元格的引用。EHYPERLINK、INDEX、INDIRECT、LOOKUP、MATCH、OFFSET、ROW、ROWS、TRANSPOSE、VLOOKUP16個查詢與引用函數(shù)其語法形式為：ADDRESS(row_num,column_num,abs_num,a1,she比如，ADDRESS(4,5,1,FALSE,"[Book1]Sheet1")需注意的是Row_num、column_num和area_#REF!。如果省略row_num和col其語法形式為：INDIRECT(ref_其基本語法形式為：OFFSET(reference,用區(qū)域的行數(shù)(height必須為正數(shù))。width變量表示LOOKUP函數(shù)可以返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組其基本語法形式為LOOKUP(lookup_value,lookup_vector,rResult_vector只包含一行或一列的區(qū)域，其大小函數(shù)LOOKUP的數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值，然HLOOKUP(lookup_value,table_array,row_index_num,range_looVLOOKUP(lookup_value,table_array,col_index_num,range_loo（1）精確查找--根據(jù)區(qū)域最左列的值，對=VLOOKUP($A21,$A$3:$H$12,2語法解釋：在$A$3:$H$12范圍內（即工資表中）精確找（2）近似的查找--根據(jù)定義區(qū)域最左列的值，對其第一步，建立一個項目總額與獎金比例的對照表，如=VLOOKUP(A11,$A$4:$B$8,二是確定一個給定值位于已排序列表中的位置，這不需要準確的匹配.語法結構為：MATCH(lookup_value,lookup_array,mat查找區(qū)域必須為升序排列。"－1"表示搜索大于或等于查找值的最小值，查找區(qū)域必須降序排開。所謂HYPERLINK，也就是創(chuàng)建快捷方式，以打（2）單擊常用工具欄中的"粘貼函數(shù)"圖標，將出現(xiàn)"粘貼函數(shù)"對話框，在"函（4）在"Link_location"中語法形式為：CHOOSE(index_num,value1,value2,...)Value1,value2,...為1到TRANSPOSE用于返回區(qū)域的轉置。函數(shù)TRANSPOSE必須在某個區(qū)域中以數(shù)組公式的形式別與array的列數(shù)和行數(shù)相同。使用們設定轉置后存放的范圍為A9.B14.第二步，單擊常用工具欄中的"粘貼函數(shù)"圖標，將出現(xiàn)"粘貼函數(shù)"對話框，在"函數(shù)分類"框中選擇框中選擇TRANSPOSE，此時在對話框的底部將出現(xiàn)該語法形式為AVERAGE(number1,number2,..其中Number1,number2,...為要計算平均值的1～30個參數(shù)。這些參數(shù)可以是數(shù)字，或者是涉及數(shù)用。如果數(shù)組或單元格引用參數(shù)中有文字、函數(shù)TRIMMEAN先從數(shù)據(jù)集的頭部和尾部除去其中Array為需要進行篩選并求平均值的數(shù)語法形式為COUNT(value1,value2），語法形式為FREQUENCY(data_array以計算某公司的員工年齡分布情況為例說明。在工作表里列出了員工的年齡。這些年齡為22和35，并分別輸入到單元格C4:C11。這一列年齡就是data_格后，即以數(shù)組公式輸入下面的公式。返回的數(shù)組中的元素個數(shù)比bi這兩個函數(shù)MAX、MIN就是用來求解數(shù)據(jù)集的極值（即最大值、最小值）。函數(shù)的用法非常簡單。MEDIAN函數(shù)返回給定數(shù)值集合的中位數(shù)。所謂中位數(shù)是指在一組數(shù)語法形式為MEDIAN(number1,number2,...)其中Number1,number2語法形式為MODE(number1,number2,...)其中Number1,number2,...是用于眾數(shù)（眾數(shù)指如果order不為零，Micros2、求特定數(shù)值在一個數(shù)據(jù)集中的百分比排此PERCENTRANK函數(shù)可用于查看特定數(shù)據(jù)在數(shù)據(jù)集中所處的=PERCENTRANK($C$3:$CAVERAGEA(value1,value2,..BETADIST(x,alpha,betBETAINV(probability,alpha,betaBINOMDIST(number_s,trials,probability_s,cumulCHIDIST(x,degrees_fCHIINV(probability,degrees_fCHITEST(actual_range,expectedCONFIDENCE(alpha,standard_deCRITBINOM(trials,probability_sDEVSQ(number1,number2,.EXPONDIST(x,lambda,cumulaFDIST(x,degrees_freedom1,degrees_freedFINV(probability,degrees_freedom1,degrees_freedFORECAST(x,known_y's,known_FREQUENCY(data_array,bins_aGAMMADIST(x,alpha,beta,cuGAMMAINV(probability,alpGROWTH(known_y's,known_x's,new_xHYPGEOMDIST(sample_s,number_sapopulation_s,number_population)INTERCEPT(known_y's,knoLINEST(known_y's,known_x's,const,sLOGEST(known_y's,known_x's,const,LOGINV(probability,mean,standarLOGNORMDIST(x,mean,standarMAX(number1,number2,MAXA(value1,value2,.MINA(value1,value2,.NEGBINOMDISTNEGBINOMDIST(number_f,number_s,probability_s)NORMDISTNORMDIST(x,mean,standard_dev,cumulative)NORMINVNORMINV(probability,mean,standard_dev)NORMSDISTNORMSDIST(z)NORMSINVNORMSINV(probability)PERCENTRANK(array,x,significanPERMUT(number,number_choPOISSON(x,mean,cumula事件所對應的概率之和。如果沒有給出PROB(x_range,prob_range,lower_limit,upper_liRSQ(known_y's,knownSTANDARDIZE(x,mean,TDIST(x,degrees_freedomTINV(probability,degrees_freTREND(known_y's,known_x's,new_x'sTTEST(array1,array2,tailsVARVAR(number1,number2,..VARAVARA(value1,value2,..VARPVARP(number1,number2,..VARPA(value1,value2,.WEIBULL(x,alpha,beta,cumulat（2）如果"加載宏"對話框中沒有"分析工具庫"，請單擊"瀏覽"按鈕，定位到"分析工具庫"加載宏文件"所在的驅動器和文件夾（通常位于"MicrosoftOffice（2）在不同的數(shù)字系統(tǒng)（如十進制系統(tǒng)、十六進制系統(tǒng)、八又如，將不同進制轉換為其他進制的數(shù)值的語法形式為：函數(shù)（number,plac通過對幾個DELTA函數(shù)求和，可以計算相等數(shù)據(jù)對的數(shù)目。該函數(shù)也稱為K語法形式為：GESTEP(number,step)其中Number為待測試的數(shù)值。St設其為零。需注意的是，如果任一參數(shù)非數(shù)值，則函數(shù)GESTEP返回錯語法形式為CONVERT(number,fro克米碼`埃年日秒杯升還記得中學時代學過的復數(shù)嗎？是不是還記得當時求運算有關的函數(shù)，你可以用它來驗證自己的運算結COMPLEX(real_num,i_num,CONVERT(number,from_unit,tERF(lower_limit,upperIMPRODUCT(inumber1,in付額、投資的未來值或凈現(xiàn)值，以及債券或息請參看附表及相關書籍。如果下文中所介紹的函數(shù)不可用，返回錯誤值#NAME?，請安裝并加載"分析工具庫4、與復利計算有關的函數(shù)--EFFECT語法形式為FV(rate,nper,pmt,p例如：假如某人兩年后需要一筆比較大的學習費用語法形式為：NPV(rate,value1,v意的是：NPV按次序使用value1,value2，來注釋現(xiàn)金流的次序。所以其語法形式為：PMT(rate,nper,pv,fv,type)其），PMT（8%/12,10,10000）計算結PV函數(shù)用來計算某項投資的現(xiàn)值。年金現(xiàn)值就是未來各期年款）的付款期總數(shù)。Pmt為各期所應支付的金額，其數(shù)值在整個年金期間保持不PV(0.08/12,12*20,600,0)計算結果為折舊計算函數(shù)主要包括AMORDEGRC、在工作表的B1：B6輸入數(shù)據(jù)"函數(shù).xls"所示，計算3、在"粘貼函數(shù)"對話框中，在"Nper"中輸入償還周期5(年),在"Pmt"中輸入7000(每年的回報額)，在"Pv"中輸入-30000(投資金額)。即公式為=RATE(5,4、確定后計算結果為15.24％。這就是本項投資的每1、計算本金、利息的函數(shù)--CUMPRINC、ACCRINT、AC2、與利息支付時間有關的函數(shù)--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUP4、與修正期限有關的函數(shù)--DURATION、M5、與有價證券有關的函數(shù)--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICE6、與證券價格表示有關的函數(shù)--DOLLARD其語法形式為ACCRINT(issue,first_interest,settlement,rate,par,frequen例如，一筆住房抵押貸款的交易情況如下：年利率為9該筆貸款在第一個月償還的本金為：=CUMPRINC(A2/12,A3*1ACCRINT(issue,first_interessettlement,rate,par,freqACCRINTM(issue,maturity,ratAMORDEGRC(cost,date_purchasAMORLINC(cost,date_purchasefirst_period,salvageCOUPDAYBS(settlement,maCOUPDAYS(settlement,maCOUPDAYSNC(settlement,mCOUPNCD(settlement,mCOUPNUM(settlement,mCOUPPCD(settlement,mCUMIPMT(rate,nper,pv,startCUMPRINC(rate,nper,pv,start_DB(cost,salvage,life,period,mDDB(cost,salvage,life,period,faDISC(settlement,maturitDOLLARDE(fractional_doDOLLARFR(decimal_dollar,fracDURATION(settlement,maturity,cEFFECT(nominal_rate,FV(rate,nper,pmt,pv基于一系列復利返回本金的未來值。函數(shù)FVFVSCHEDULE(principal,schMDURATION(settlement,maturiMIRR(values,finance_raNOMINAL(effect_rate,nper），NPV(rate,value1,value2,...)格ODDFPRICE(settlement,matissue,first_coupon,rateODDFYIELD(settlement,mredemption,frequencODDLPRICE(settlement,mODDLYIELD(settlement,matPMT(rate,nper,pv,fvPPMT(rate,per,nper,pv,fvPRICE(settlement,maturate,yld,redemption,frePRICEDISC(settlement,matPRICEMAT(settlement,matuPV(rate,nper,pmt,fvRATE(nper,pmt,pv,fv,typRECEIVED(settlement,maTBILLEQ(settlement,maTBILLPRICE(settlement,maTBILLYIELD