HR進階學習：Excel函數應用教程

Excel函數應用之函數簡介Excel函數應用之數學和三角函數Excel函數應用之邏輯函數Excel函數應用之文本/日期/時間函數Excel函數應用之查詢與引用函數Excel函數應用之統(tǒng)計函數Excel函數應用之工程函數Excel函數應用之財務函數Excel函數應用之信息函數Excel函數應用之數據庫函數（陸元婕）編者語：Excel是辦公室自動化中非常重要的一款軟件，很多巨型國際企業(yè)都是依靠Excel進行數據管理。它不僅僅能夠方便的處理表格和進行圖形分析，其更強大的功能體現在對數據的自動處理和計算，然而很多缺少理工科背景或是對Excel強大數據處理功能不了解的人卻難以進一步深入。編者以為，對Excel函數應用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，然而目前這一部份內容的教學文章卻又很少見，所以特別組織了這一個《Excel函數應用》系列，希望能夠對Excel進階者有所幫助。Excel的數據處理功能在現有的文字處理軟件中可均值等簡單的函數應用上呢？難道Excel術語說明什么是參數？術語說明什么是參數？參數可以是數字、文本、形如TRUE或FALSE的邏輯值、數組、形如#N/A的錯誤值或單元格引用。給定的參數必須能產生有效的值。參數也可以是常量、公式或其它函數。參數不僅僅是常量、公式或函數，還可以是數組、單元格引用等：1.數組--用于建立可產生多個結果或可對存放在行和列中的一組參數進行運算的單個公式。在MicrosoftExcel有兩類數組：區(qū)域數組和常量數組。區(qū)域數組是一個矩形的單元格區(qū)域，該區(qū)域中的單元格共用一個公式；常量數組將一組給定的常量用作某個公式中的2.單元格引用--用于表示單元格在工作表所處位置的坐標值。例如，顯示在第B列和第3行交叉處的單元格，其引用形式為"B3"。3.常量--常量是直接鍵入到單元格或公式中的數字或文本值，或由名稱所代表的10/9/96、數字210和文本"QuarterlyEarnings"都是常量。公式或由公式得出的數值都不是常量。式出現，請在函數名稱前面鍵入等號（=）。在創(chuàng)建包含函數的公式圖2函數的結構圖3公式選項板圖4單元格編輯2.單擊編輯欄中"編輯公式"按鈕，將會在編輯欄下面出現一個"公式選項板"，此時"名稱"框將圖5函數列表框4.當選中所需的函數后，Excel2000將打開"公式選項板"。用戶可以在這個選項板中輸在了解了函數的基本知識及使用方法后，請跟隨筆者一起尋找Excel提供圖6粘貼函數列表售信息的數據清單中，可以計算出所有銷售數值大于1,000且小于2,500個函數均有三個相同的參數：database、field和criteria。這些參數指向數據庫函數所使用的工5在單元格滿足條件時返回TRUE。例如，如果單元格包含一個偶數值，ISEVEN工作表函數返回TRUE。如果需要確定某個單元格區(qū)域中是否存在空白單元格，可以使用COUNTBLANK工作表函數對單元格區(qū)域中的空白單元格進行計數，或者使用用工作表函數。例如，如果需要在表格中查找與第一列中的值相匹配的數值，可以使用VLOOKUP工作表函數。如果需要確10.文本函數--通過文本函數，可以在公式中處理文字串。例如，可以改變大小寫或確定入文字串或連接在文字串上。下面的公式為一個示例，借以說明如何使用函數TODAY和函數TEXT來創(chuàng)建一條信息，該信11.用戶自定義函數--如果要在公式或計算中使用特別復雜的計算，而工作表函數又無法定義函數。這些函數，稱為用戶自定義函數，可以通過使用Visu括SUBTOTAL、SUM、SUMIF、SUM這里筆者將以某單位工資表為例重點介紹SUM（計算一組參數之和）、SUMIF（7=SUM(D3:D12,F3:F12)-SUM(=SUMIF($C$3:$C$12,"銷售部",$F$38是要弧度值，因此必須先將角度值轉為弧度值。具體在Excel提供的"數學與三角函數"中提供了一個名為ROUND(number,nu數，將數字四舍五入。這個函數有兩個參數，分別是num對于數字進行四舍五入,還可以使用INT（取整函數），但由ASIN工作表函數將參數Number沿絕對值增大的方向，舍入為最接近的返回數的階乘，一個數的階乘等于1*2*將參數Number沿絕對值減小的方向去尾舍入，使其等于最接近的significance的倍數返回一個數的自然對數自然對數以常數項e（2.718281在給定的幾組數組中，將數組間對應的元素相乘，返回兩數組中對應數值的平方和之和，平方和加總在統(tǒng)所有參數的邏輯值為真時返回TRUE；只要一個參數的邏語法為AND(logical1,logical2,...)，其中它的語法為IF(logical_test,value_if_true,value_if_falsValue_if_true顯示在logical_test為TRUE時返回的值，Value_if三個參數Value_if_false的結果。IF函則可使用COUNTIF工作表函數。如果要根據單元格=IF(SUM(C5:F5),SUM(C5:說明：為了解釋起來比較方便，我們在這里僅做兩重嵌套的示例，您可以按照實際情況進行=IF(F11>60,IF(AND(F11>90),"優(yōu)秀","合格")如果要計算單元格區(qū)域中某個文本串或數字出現的次數，則可使用COUNTIF工作表函數。如果要根文本串或數字求和，則可使用SUMIF工作COUNTIF可以用來計算給定區(qū)域內滿足特定條件的單元格的數目。比如在成績表語法形式為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通過以上三個函數可以將文本轉換顯示樣式，使得文本變Lower（pLeaseComEHere!）=pleasecomupper（pLeaseComEHere!）=PLEASECOMEproper（pLeaseComEHere!）=PleaseCome您可以使用Mid、Left、Right等函數從MID函數：MID(text,start_num,比如，從字符串"Thisisanapple."分別取出字符從字符串"MynameisMary"中清除空格的函數寫法為：TRI是否完全相同。如果它們完全相同，則返回TRUE；否則，利用函數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)用于求出身份證號碼中代表性別的數字，實際求得的b.VALUE(RIGHT(E4,3)用于將上一步所得的代表數字的字符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ā)現，創(chuàng)建日期：31-05-200工添加的嗎？不是，實際上這個日期還是變化的，它顯示的是系統(tǒng)當前時間。這里是利用函數TODAY和函數具體公式寫法為：="創(chuàng)建日期："&TEXT(TODAY(),"返回對應于數字代碼的字符，函數CHAR可將其他類型刪除文本中不能打印的字符。對從其他應用程序中輸CLEAN函數，將刪除其中含有的當前操作系統(tǒng)無法打印的CONCATENATE(text1,texDOLLAR或RMB(number,deci否則，返回FALSE。函數EXACT能區(qū)分大小寫，但FIND(find_text,within_text,start_nuFIXED(number,decimals,no_coLEFTB基于所指定的字節(jié)數返回文本串中的第一個或前幾個字MID(text,start_num,num_chMIDB(text,start_num,num_bREPLACE使用其他文本串并根據所指定的字符數替換某文本串REPLACEB使用其他文本串并根據所指定的字符數替換某文本REPLACE(old_text,start_num,num_chars,new_teREPLACEB(old_text,start_num,num_bytes,new_te按照給定的次數重復顯示文本?？梢酝ㄟ^函數REPT來SEARCH返回從start_num開始首次找到特定回find_text的起始位置編號。此結果是基于每個字符所使SEARCH(find_text,within_teSEARCHB(find_text,within_text在文字串中用new_text替代old_text。如果需要在某一文的文本，請使用函數SUBSTITUTE；如果需要在某SUBSTITUTE(text,old_text,new_text,insTUPPERUPPER(text)使用￥（日圓）貨幣格式將數字轉換成文DATEDIF(start_date,end_date,uniDAYS360(start_date,end_date,methoEDATE(start_date,mon返回start-date之前或之后指定月份中最后一天的系列數。用函數EEOMONTH(start_date,montNETWORKDAYS返回參數start-data和end-data之間完整的工作日數值。工作日NETWORKDAYS(start_date,end_date,holidays)NOWNOW()TIME(hour,minute,se代表從0:00:00(12:00:00A返回當前日期的系列數，系列數是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]將引用下面特定數值，或者需要查找某一單元格的引用。EHYPERLINK、INDEX、INDIRECT、LOOKUP、MATCH、OFFSET、ROW、ROWS、TRANSPOSE、VLOOKUP16個查詢與引用函數其語法形式為：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ū)域的行數(height必須為正數)。width變量表示LOOKUP函數可以返回向量（單行區(qū)域或單列區(qū)域）或數組其基本語法形式為LOOKUP(lookup_value,lookup_vector,rResult_vector只包含一行或一列的區(qū)域，其大小函數LOOKUP的數組形式在數組的第一行或第一列查找指定的數值，然HLOOKUP(lookup_value,table_array,row_index_num,range_looVLOOKUP(lookup_value,table_array,col_index_num,range_loo（1）精確查找--根據區(qū)域最左列的值，對=VLOOKUP($A21,$A$3:$H$12,2語法解釋：在$A$3:$H$12范圍內（即工資表中）精確找（2）近似的查找--根據定義區(qū)域最左列的值，對其第一步，建立一個項目總額與獎金比例的對照表，如=VLOOKUP(A11,$A$4:$B$8,二是確定一個給定值位于已排序列表中的位置，這不需要準確的匹配.語法結構為：MATCH(lookup_value,lookup_array,mat查找區(qū)域必須為升序排列。"－1"表示搜索大于或等于查找值的最小值，查找區(qū)域必須降序排開。所謂HYPERLINK，也就是創(chuàng)建快捷方式，以打（2）單擊常用工具欄中的"粘貼函數"圖標，將出現"粘貼函數"對話框，在"函（4）在"Link_location"中語法形式為：CHOOSE(index_num,value1,value2,...)Value1,value2,...為1到TRANSPOSE用于返回區(qū)域的轉置。函數TRANSPOSE必須在某個區(qū)域中以數組公式的形式別與array的列數和行數相同。使用們設定轉置后存放的范圍為A9.B14.第二步，單擊常用工具欄中的"粘貼函數"圖標，將出現"粘貼函數"對話框，在"函數分類"框中選擇框中選擇TRANSPOSE，此時在對話框的底部將出現該語法形式為AVERAGE(number1,number2,..其中Number1,number2,...為要計算平均值的1～30個參數。這些參數可以是數字，或者是涉及數用。如果數組或單元格引用參數中有文字、函數TRIMMEAN先從數據集的頭部和尾部除去其中Array為需要進行篩選并求平均值的數語法形式為COUNT(value1,value2），語法形式為FREQUENCY(data_array以計算某公司的員工年齡分布情況為例說明。在工作表里列出了員工的年齡。這些年齡為22和35，并分別輸入到單元格C4:C11。這一列年齡就是data_格后，即以數組公式輸入下面的公式。返回的數組中的元素個數比bi這兩個函數MAX、MIN就是用來求解數據集的極值（即最大值、最小值）。函數的用法非常簡單。MEDIAN函數返回給定數值集合的中位數。所謂中位數是指在一組數語法形式為MEDIAN(number1,number2,...)其中Number1,number2語法形式為MODE(number1,number2,...)其中Number1,number2,...是用于眾數（眾數指如果order不為零，Micros2、求特定數值在一個數據集中的百分比排此PERCENTRANK函數可用于查看特定數據在數據集中所處的=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）在不同的數字系統(tǒng)（如十進制系統(tǒng)、十六進制系統(tǒng)、八又如，將不同進制轉換為其他進制的數值的語法形式為：函數（number,plac通過對幾個DELTA函數求和，可以計算相等數據對的數目。該函數也稱為K語法形式為：GESTEP(number,step)其中Number為待測試的數值。St設其為零。需注意的是，如果任一參數非數值，則函數GESTEP返回錯語法形式為CONVERT(number,fro克米碼`埃年日秒杯升還記得中學時代學過的復數嗎？是不是還記得當時求運算有關的函數，你可以用它來驗證自己的運算結COMPLEX(real_num,i_num,CONVERT(number,from_unit,tERF(lower_limit,upperIMPRODUCT(inumber1,in付額、投資的未來值或凈現值，以及債券或息請參看附表及相關書籍。如果下文中所介紹的函數不可用，返回錯誤值#NAME?，請安裝并加載"分析工具庫4、與復利計算有關的函數--EFFECT語法形式為FV(rate,nper,pmt,p例如：假如某人兩年后需要一筆比較大的學習費用語法形式為：NPV(rate,value1,v意的是：NPV按次序使用value1,value2，來注釋現金流的次序。所以其語法形式為：PMT(rate,nper,pv,fv,type)其），PMT（8%/12,10,10000）計算結PV函數用來計算某項投資的現值。年金現值就是未來各期年款）的付款期總數。Pmt為各期所應支付的金額，其數值在整個年金期間保持不PV(0.08/12,12*20,600,0)計算結果為折舊計算函數主要包括AMORDEGRC、在工作表的B1：B6輸入數據"函數.xls"所示，計算3、在"粘貼函數"對話框中，在"Nper"中輸入償還周期5(年),在"Pmt"中輸入7000(每年的回報額)，在"Pv"中輸入-30000(投資金額)。即公式為=RATE(5,4、確定后計算結果為15.24％。這就是本項投資的每1、計算本金、利息的函數--CUMPRINC、ACCRINT、AC2、與利息支付時間有關的函數--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUP4、與修正期限有關的函數--DURATION、M5、與有價證券有關的函數--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICE6、與證券價格表示有關的函數--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基于一系列復利返回本金的未來值。函數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