Excel常用函數(shù)基礎(chǔ)知識

1、第壹部分：excel函數(shù)及公式一、函數(shù)和公式簡述 1. 1公式 在一個單元格輸入等號時，EXCEL就認為你輸入了一個公式。EXCEL單元格接受5種元素的輸入:u運算符例如”+”,”*”u單元格引用例如”A7”u值或字符串例如7.5或”金額”u函數(shù)和參數(shù)例如SUM或IFu括號可以控制公式的運算順序一個公式最多可容納1024個字符，如果要創(chuàng)建超出范圍限制的公式，必須把公式分為多個公式，或創(chuàng)建一個自定義公式創(chuàng)建公式時，盡量不要使用硬編碼值，例如計算7.5%的銷售稅，可以把銷售稅放到一個單元格中，使用單元格引用代替文字引用，一旦稅率發(fā)生改變，只要維護稅率單元格，而不需修改每個單元格 二、函數(shù)和公式基礎(chǔ)

2、 1. 2關(guān)于運算符 運算符對公式中的元素進行特定類型的運算。Microsoft Excel 包含四種類型的運算符：算術(shù)運算符、比較運算符、文本運算符和引用運算符。 算術(shù)運算符+ +加號3+3- -減號3-1* *乘號2*5/ /除號4/3% %百分比20% 求冪32 比較運算符= =等號A2=A1大與B3B2 小于A1=大于等于A1=B1=小于等于A1=B1不等于A1B1 文本運算符& &NORTH&EASTNORTH&EAST 引用運算符：區(qū)域引用(A1:B15)，聯(lián)合引用(A1:B15,C1:D15)空格交叉引用(A1:D15 C1:C15)1. 3引用

3、大多數(shù)公式都會使用單元格或范圍引用一個或多個單元格，單元格的引用有4種類型，分別使用美元符號加以區(qū)別:u相對完全相對，公式被復(fù)制時，單元格引用 掉整到新的位置，如A1,A2u絕對完全絕對，公式被復(fù)制時，單元格引用不會改變，如$A$1u行絕對部分絕對引用，公式被復(fù)制時，列部分調(diào)整，行部分不會改變，如A$1u列絕對部分絕對引用，公式被復(fù)制時，行部分調(diào)整，列部分不會改變，如$A1可以使用F4鍵在引用模式中進行循環(huán)工作表之間的引用，=SHEET2!A1+1工作薄之間的引用，=供應(yīng)商表.XLSSHEET2!A1+1工作薄之間的引用，=銷售表 6月.XLSSHEE2!A1+11. 4公式中的錯誤信息EXC

4、EL2003在錯誤公式邊可以顯示一個智能圖標(biāo)，可以單擊這個圖標(biāo)了解這個錯誤的進一步信息1. 5定義名稱 工作表中引用單元格范圍計算并不直觀，EXCEL可以把單元格，范圍，圖表，圖形命名為有意義的名稱，提供給公式引用。如下圖即是對單元格范圍G2:G13命名為單價u名稱不能包含任何空格，可以使用下劃線或點號代替空格u可以使用字符和數(shù)字組合，但首字符必須是字母或下劃線，不能以數(shù)字開頭u名稱長度限制在255個字符內(nèi)u不能使用符號u名稱不區(qū)分大小寫2.1工作表函數(shù) 工作表函數(shù)就是在公式中使用的一種內(nèi)部工具u可以簡化公式u保證公式運行，否作無法計算u提高編輯任務(wù)的速度u允許”有條件地”運行公式，使之具備基

5、本判斷能力函數(shù)：是預(yù)先錄制好的公式，函數(shù)集是EXCEL中的精華函數(shù)都需要使用括號，括號中的內(nèi)容就是函數(shù)的參數(shù)。函數(shù)的結(jié)果決定于參數(shù)的使用方法，一個函數(shù)可以:u不帶參數(shù)u只有一個參數(shù)u固定數(shù)量的參數(shù)u不確定數(shù)量的參數(shù)u可選參數(shù)文本函數(shù):&:連接連個文本字符串TRIM:刪除數(shù)據(jù)前后所有多余的空格，用一個空格代替多個空格。如:TRIM(“ My Home “)=My homeLEN:返回單元格中字符的數(shù)量。如:LEN(GREENHEAK VIP)=13LEFT:從左起返回確定數(shù)量的字符。如:LEFT(“Beijing”,3)=BeiRIGTH:從右起返回確定數(shù)量的字符。如:RIGHT(“Be

6、ijing”,4)=jingMID:在字符串中任意位置返回確定數(shù)量的字符。如:MID7,8)=19760811UPPER:將文本全部轉(zhuǎn)化為大寫。如:UPPER(“join”)=JOINLOWER:將文本全部轉(zhuǎn)化為小寫。如:LOWER(“JOIN”)=joinPROPER:將文本首字母大寫。如:PROPER(“join me”)=Join Me查找函數(shù):VLOOKUPVLOOKUP:查找表格中的第一列值，返回相應(yīng)的值到指定的表格列種，查找表是垂直放置的，語法如下:VLOOKUP(lookup_value,table_array,col_index_num,

7、range_lookup)uLookup_value:查找值在查找表的第一列uTable_array:包含查找表的范圍uCol_index_num :匹配值在表中的列編號uRange_lookup:可選，為1或空，可以找到一個近似值。為0，找匹配值HLOOKUPHLOOKUP:查找表格中的第一行值，返回相應(yīng)的值到指定的表格行中，語法如下:HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)uLookup_value:查找值在查找表的第一行uTable_array:包含查找表的范圍uCol_index_num :匹配值在表中的行

8、編號uRange_lookup:可選，為1或空，可以找到一個近似值。為0，找匹配值查找函數(shù):MATCHMATCH:MATCH函數(shù)可以返回范圍中的一個單元格相對位置，指出它可以匹配某個指定值，語法如下:MATCH(lookup_value,lookup_array,match_type)INDEXINDEX:返回范圍內(nèi)的單元格，語法如下:INDEX(array,row_num,column_num)信息函數(shù):ISERRORISERROR:值為任何錯誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!)，語法為:ISERROR(VALUE)統(tǒng)計函數(shù)

9、:MAXMAX:返回參數(shù)列表中的最大值，忽略邏輯值和文本MAX(NUMBER1,NUMBER2)MINMIN:返回參數(shù)列表中的最小值，忽略邏輯值和文本MIN(NUMBER1,NUMBER2)邏輯函數(shù):IFIF:IF函數(shù)根據(jù)給定的條件，根據(jù)條件的不同返回不同的結(jié)果。語法為:IF(Logical_test,value_if_true,value_if_false)IF(A190,”IF(A190,”優(yōu)秀優(yōu)秀”,if(A180,”,if(A180,”良好良好”,IF(A160,”,IF(A160,”及格及格”),”),”不及不及格格”) )ANDAND:所有參數(shù)的邏輯值為真時返回真OROR:當(dāng)有一個

10、參數(shù)為真時即返回真COUNTIFCOUNTIF:計算區(qū)域中滿足給定條件的單元格的個數(shù)，語法如下:COUNTIF(range,criteria)COUNTIF(B1:B10,”apples”)COUNTIF(B1:B10,”apples”)SUMIFSUMIF:根據(jù)指定的條件對若干單元格求和，語法如下SUMIF(range,criteria,sum_range)SUMIF(A2:A20,”10000”,B2:B20)SUMIF(A2:A20,”10000”,B2:B20)SUMIF(A2:A20,”0”)SUMIF(A2:A20,”0”)信息函數(shù):SUMPRODUCTSUMPRODUCT:在給定的幾組數(shù)組中，將數(shù)組間元素相乘，并返回乘積之和SUMPRODUCT(array1,array,)OFFSETOFFSET:以指定的引用為參照系，通過給定的偏移量來得到新的引用，OFFSET(reference,rows,cols,height,width)三、數(shù)組基礎(chǔ)知識 1.數(shù)組公式:是用于建立可以產(chǎn)生多個結(jié)果或?qū)梢源娣旁谛泻土兄械囊唤M參數(shù)進行運算的單個公式。數(shù)組公式的特點就是可以執(zhí)行多重計算,它返回的是一