Excel常用函數(shù)基礎(chǔ)知識(shí)PPT學(xué)習(xí)教案

1、會(huì)計(jì)學(xué)1Excel常用函數(shù)基礎(chǔ)知識(shí)常用函數(shù)基礎(chǔ)知識(shí)第1頁/共36頁第2頁/共36頁第壹部分：excel函數(shù)及公式一、函數(shù)和公式簡述 第3頁/共36頁第4頁/共36頁第5頁/共36頁第6頁/共36頁第7頁/共36頁第8頁/共36頁第9頁/共36頁第10頁/共36頁1. 1公式 在一個(gè)單元格輸入等號(hào)時(shí)，EXCEL就認(rèn)為你輸入了一個(gè)公式。EXCEL單元格接受5種元素的輸入:u運(yùn)算符例如”+”,”*”u單元格引用例如”A7”u值或字符串例如7.5或”金額”u函數(shù)和參數(shù)例如SUM或IFu括號(hào)可以控制公式的運(yùn)算順序一個(gè)公式最多可容納1024個(gè)字符，如果要?jiǎng)?chuàng)建超出范圍限制的公式，必須把公式分為多個(gè)公式，或創(chuàng)

2、建一個(gè)自定義公式創(chuàng)建公式時(shí)，盡量不要使用硬編碼值，例如計(jì)算7.5%的銷售稅，可以把銷售稅放到一個(gè)單元格中，使用單元格引用代替文字引用，一旦稅率發(fā)生改變，只要維護(hù)稅率單元格，而不需修改每個(gè)單元格 二、函數(shù)和公式基礎(chǔ) 第11頁/共36頁1. 2關(guān)于運(yùn)算符 運(yùn)算符對(duì)公式中的元素進(jìn)行特定類型的運(yùn)算。Microsoft Excel 包含四種類型的運(yùn)算符：算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符。 算術(shù)運(yùn)算符+ +加號(hào)3+3- -減號(hào)3-1* *乘號(hào)2*5/ /除號(hào)4/3% %百分比20% 求冪32 比較運(yùn)算符= =等號(hào)A2=A1大與B3B2 小于A1=大于等于A1=B1=小于等于A1=B1不等于A

3、1B1 文本運(yùn)算符& &NORTH&EASTNORTH&EAST 引用運(yùn)算符：區(qū)域引用(A1:B15)，聯(lián)合引用(A1:B15,C1:D15)空格交叉引用(A1:D15 C1:C15)第12頁/共36頁1. 3引用 大多數(shù)公式都會(huì)使用單元格或范圍引用一個(gè)或多個(gè)單元格，單元格的引用有4種類型，分別使用美元符號(hào)加以區(qū)別:u相對(duì)完全相對(duì)，公式被復(fù)制時(shí)，單元格引用 掉整到新的位置，如A1,A2u絕對(duì)完全絕對(duì)，公式被復(fù)制時(shí)，單元格引用不會(huì)改變，如$A$1u行絕對(duì)部分絕對(duì)引用，公式被復(fù)制時(shí)，列部分調(diào)整，行部分不會(huì)改變，如A$1u列絕對(duì)部分絕對(duì)引用，公式被復(fù)制時(shí)，行部分調(diào)整，

4、列部分不會(huì)改變，如$A1可以使用F4鍵在引用模式中進(jìn)行循環(huán)工作表之間的引用，=SHEET2!A1+1工作薄之間的引用，=供應(yīng)商表.XLSSHEET2!A1+1工作薄之間的引用，=銷售表 6月.XLSSHEE2!A1+1第13頁/共36頁1. 4公式中的錯(cuò)誤信息EXCEL2003在錯(cuò)誤公式邊可以顯示一個(gè)智能圖標(biāo)，可以單擊這個(gè)圖標(biāo)了解這個(gè)錯(cuò)誤的進(jìn)一步信息第14頁/共36頁1. 5定義名稱 工作表中引用單元格范圍計(jì)算并不直觀，EXCEL可以把單元格，范圍，圖表，圖形命名為有意義的名稱，提供給公式引用。如下圖即是對(duì)單元格范圍G2:G13命名為單價(jià)u名稱不能包含任何空格，可以使用下劃線或點(diǎn)號(hào)代替空格u可

5、以使用字符和數(shù)字組合，但首字符必須是字母或下劃線，不能以數(shù)字開頭u名稱長度限制在255個(gè)字符內(nèi)u不能使用符號(hào)u名稱不區(qū)分大小寫第15頁/共36頁2.1工作表函數(shù) 工作表函數(shù)就是在公式中使用的一種內(nèi)部工具u可以簡化公式u保證公式運(yùn)行，否作無法計(jì)算u提高編輯任務(wù)的速度u允許”有條件地”運(yùn)行公式，使之具備基本判斷能力第16頁/共36頁函數(shù)：是預(yù)先錄制好的公式，函數(shù)集是EXCEL中的精華函數(shù)都需要使用括號(hào)，括號(hào)中的內(nèi)容就是函數(shù)的參數(shù)。函數(shù)的結(jié)果決定于參數(shù)的使用方法，一個(gè)函數(shù)可以:u不帶參數(shù)u只有一個(gè)參數(shù)u固定數(shù)量的參數(shù)u不確定數(shù)量的參數(shù)u可選參數(shù)第17頁/共36頁文本函數(shù):&:連接連個(gè)文本字符

6、串TRIM:刪除數(shù)據(jù)前后所有多余的空格，用一個(gè)空格代替多個(gè)空格。如:TRIM(“ My Home “)=My homeLEN:返回單元格中字符的數(shù)量。如:LEN(GREENHEAK VIP)=13LEFT:從左起返回確定數(shù)量的字符。如:LEFT(“Beijing”,3)=BeiRIGTH:從右起返回確定數(shù)量的字符。如:RIGHT(“Beijing”,4)=jingMID:在字符串中任意位置返回確定數(shù)量的字符。如:MID7,8)=19760811UPPER:將文本全部轉(zhuǎn)化為大寫。如:UPPER(“join”)=JOINLOWER:將文本全部轉(zhuǎn)化為小寫。如:

7、LOWER(“JOIN”)=joinPROPER:將文本首字母大寫。如:PROPER(“join me”)=Join Me第18頁/共36頁查找函數(shù):VLOOKUPVLOOKUP:查找表格中的第一列值，返回相應(yīng)的值到指定的表格列種，查找表是垂直放置的，語法如下:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)uLookup_value:查找值在查找表的第一列uTable_array:包含查找表的范圍uCol_index_num:匹配值在表中的列編號(hào)uRange_lookup:可選，為1或空，可以找到一個(gè)近似值。為0，找匹配

8、值HLOOKUPHLOOKUP:查找表格中的第一行值，返回相應(yīng)的值到指定的表格行中，語法如下:HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)uLookup_value:查找值在查找表的第一行uTable_array:包含查找表的范圍uCol_index_num:匹配值在表中的行編號(hào)uRange_lookup:可選，為1或空，可以找到一個(gè)近似值。為0，找匹配值第19頁/共36頁查找函數(shù):MATCHMATCH:MATCH函數(shù)可以返回范圍中的一個(gè)單元格相對(duì)位置，指出它可以匹配某個(gè)指定值，語法如下:MATCH(lookup_va

9、lue,lookup_array,match_type)INDEXINDEX:返回范圍內(nèi)的單元格，語法如下:INDEX(array,row_num,column_num)第20頁/共36頁信息函數(shù):ISERRORISERROR:值為任何錯(cuò)誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!)，語法為:ISERROR(VALUE)統(tǒng)計(jì)函數(shù):MAXMAX:返回參數(shù)列表中的最大值，忽略邏輯值和文本MAX(NUMBER1,NUMBER2)MINMIN:返回參數(shù)列表中的最小值，忽略邏輯值和文本MIN(NUMBER1,NUMBER2)第21頁/共36頁邏輯

10、函數(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ù)的邏輯值為真時(shí)返回真OROR:當(dāng)有一個(gè)參數(shù)為真時(shí)即返回真COUNTIFCOUNTIF:計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)，語法如下:COUNTIF(range,criteria)COUNTIF(B1:B10,COUNTIF(B1:B10,”a

11、pplesapples”) )SUMIFSUMIF:根據(jù)指定的條件對(duì)若干單元格求和，語法如下SUMIF(range,criteria,sum_range)SUMIF(A2:A20,SUMIF(A2:A20,”1000010000”,B2:B20),B2:B20)SUMIF(A2:A20,SUMIF(A2:A20,”00”) )第22頁/共36頁信息函數(shù):SUMPRODUCTSUMPRODUCT:在給定的幾組數(shù)組中，將數(shù)組間元素相乘，并返回乘積之和SUMPRODUCT(array1,array,)OFFSETOFFSET:以指定的引用為參照系，通過給定的偏移量來得到新的引用，OFFSET(reference,rows,cols,height,width)第23頁/共36頁三、數(shù)組基礎(chǔ)知識(shí) 1.數(shù)組公式:是用于建立可以產(chǎn)生多個(gè)結(jié)果或?qū)梢源娣旁谛泻土兄械囊唤M參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。數(shù)組公式的特點(diǎn)就是可以執(zhí)行多重計(jì)算,它返回的是一組數(shù)據(jù)結(jié)果。由于一個(gè)單元格內(nèi)只能儲(chǔ)存一個(gè)數(shù)值,所以當(dāng)結(jié)果是一組數(shù)據(jù)時(shí),單元格只返回第一個(gè)值；如果你需要用到所有的運(yùn)算結(jié)果時(shí),要么用多個(gè)單元格去分別返回（可以用index（）來返回），要