EXCEL函數(shù)培訓(xùn)課件

《Excel函數(shù)的根底及應(yīng)用》第一講2010-04-10EXCEL高手操作函數(shù)圖表分析VBAEXCEL根底知識講解實(shí)例操作Excel根底及函數(shù)的應(yīng)用函數(shù)根底及應(yīng)用1Excel根底及函數(shù)的應(yīng)用EXCEL的根本操作EXCEL工作簿及工作環(huán)境一、操作2EXCEL中表格的美化及數(shù)據(jù)的處理31、工作簿與工作表的關(guān)系2、工具的用途3、工作環(huán)境的設(shè)定1EXCEL工作簿及工作環(huán)境1、數(shù)據(jù)錄入技巧

2、數(shù)據(jù)、表格格式化

3、自動求和自動填充EXCEL的根本操作24、單元格的編輯和置換

5、復(fù)制、選擇性粘貼

6、查找、替換的強(qiáng)大功能

7、頁面設(shè)置、打印的方式

8、學(xué)會盲打和使用快捷鍵1、合理的使用格式工具2、常規(guī)運(yùn)算符：“+”“-”“*”“/”3、比較運(yùn)算符：“<”“>”“=”“<>”4、使用公式對數(shù)值進(jìn)行計(jì)算5、絕對、相對引用EXCEL中表格的美化及數(shù)據(jù)的處理3《Excel函數(shù)的根底及應(yīng)用》第二講2010-051、函數(shù)的定義

2、函數(shù)的應(yīng)用

3、案例的分析Excel根底及函數(shù)的應(yīng)用函數(shù)二、函數(shù)§運(yùn)用根本函數(shù)實(shí)現(xiàn)對數(shù)據(jù)、單元格的操作

§運(yùn)用函數(shù)Count、Countif等進(jìn)行復(fù)雜數(shù)量統(tǒng)計(jì)

§運(yùn)用函數(shù)Sumif、Sumproduct、數(shù)組公式等進(jìn)行有條件的求和

§運(yùn)用函數(shù)Vlookup、Hlookup進(jìn)行高級數(shù)據(jù)查詢

§Datedif、Mid等主要日期和文本函數(shù)的應(yīng)用

§函數(shù)嵌套的計(jì)算、引用函數(shù)與查詢函數(shù)結(jié)合使用學(xué)習(xí)目標(biāo)Excel中所提的函數(shù)其實(shí)是一些預(yù)定義的公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算。用戶可以直接用它們對某個(gè)區(qū)域內(nèi)的數(shù)值進(jìn)行一系列運(yùn)算，如分析和處理日期值和時(shí)間值、確定貸款的支付額、確定單元格中的數(shù)據(jù)類型、計(jì)算平均值、排序顯示和運(yùn)算文本數(shù)據(jù)等等。例如，SUM函數(shù)對單元格或單元格區(qū)域進(jìn)行加法運(yùn)算。什么是函數(shù)？Excel根底及函數(shù)的應(yīng)用函數(shù)的定義邏輯函數(shù):AND、OR、NOT、TRUE、FALSE、IF函數(shù)Excel根底及函數(shù)的應(yīng)用認(rèn)識邏輯函數(shù)這三個(gè)函數(shù)都用來返回參數(shù)邏輯值A(chǔ)ND、OR、NOT表達(dá)式：AND(logical1,logical2,...)OR(logical1,logical2,...)NOT(logical1,logical2,...)EXCEL邏輯函數(shù)AND函數(shù)：所有參數(shù)的邏輯值為真時(shí)返回TRUE；只要一個(gè)參數(shù)的邏輯值為假即返回FALSE。簡言之，就是當(dāng)AND的參數(shù)全部滿足某一條件時(shí)，返回結(jié)果為TRUE，否那么為FALSE。EXCEL邏輯函數(shù)定義OR函數(shù)：OR函數(shù)指在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為TRUE，即返回TRUE,它與AND函數(shù)的區(qū)別在于，AND函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個(gè)為真即可為真EXCEL邏輯函數(shù)定義NOT函數(shù)：NOT函數(shù)用于對參數(shù)值求反。當(dāng)要確保一個(gè)值不等于某一特定值時(shí)，可以使用NOT函數(shù)。簡言之，就是當(dāng)參數(shù)值為TRUE時(shí)，NOT函數(shù)返回的結(jié)果恰與之相反，結(jié)果為FALSEEXCEL邏輯函數(shù)定義TRUE、FALSE這兩個(gè)函數(shù)用來返回參數(shù)的邏輯值，可以直接在單元格或公式中鍵入值TRUE或者FALSE。EXCEL邏輯函數(shù)定義IF函數(shù)IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。它的應(yīng)用很廣泛，可以使用函數(shù)IF對數(shù)值和公式進(jìn)行條件檢測語法：IF(logical_test,value_if_true,value_if_false)。說明：Logical_test表示計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式。本參數(shù)可使用任何比較運(yùn)算符EXCEL邏輯函數(shù)例：Excel根底及函數(shù)的應(yīng)用假設(shè)單元格A1、B1、C1中是各科成績，如果有一科成績不到60分，那么不錄取。公式為？=IF〔OR〔A1<60，B1<60，C1<60)”不錄取”,”錄取“〕♂常用函數(shù)

♂重要函數(shù)Excel根底及函數(shù)的應(yīng)用If

SUM--SUMIF

Count--counta

COUNTIF

Average〔求平均值〕

Left-Right-Mid

Max-Min

RANK(排位)

FREQUENCY(頻率分布〕Excel根底及函數(shù)的應(yīng)用常用函數(shù)表達(dá)式

IF(logical_test,value_if_true,value_if_false)

IF(邏輯條件,真值,假值)

說明

邏輯條件:運(yùn)算結(jié)果為True或者False的表達(dá)式或值

真值:Logical_test為True時(shí)返回的值

假值:Logical_test為False時(shí)返回的值

簡言之，如果第一個(gè)參數(shù)logical_test返回的結(jié)果為真的話，那么執(zhí)行第二個(gè)參數(shù)Value_if_true的結(jié)果，否那么執(zhí)行第三個(gè)參數(shù)Value_if_false的結(jié)果IF函數(shù)Excel根底及函數(shù)的應(yīng)用IF函數(shù)公式F1=IF(SUM(A1:A5),SUM(A1:A5),“”)

解析：如果SUM(A1:E1)不等于零，那么在F1單元格中顯示SUM(A1:E1)的結(jié)果，否那么顯示字符串。

幾點(diǎn)說明：

〔1〕SUM(C5:F5)不等于零的正規(guī)寫法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；

〔2〕""表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。ABCDEF11223344245例解一EXCEL函數(shù)例如IF函數(shù)公式E61=IF(D61<=300,"預(yù)算內(nèi)","超出預(yù)算")例解二EXCEL函數(shù)例如IF函數(shù)方法一：G26=IF(F26>=180,IF(F26>=240,“優(yōu)秀”，“合格”〕,“不合格”〕例解三EXCEL函數(shù)例如IF函數(shù)方法二：G26=IF(F26>=240,“優(yōu)秀”，IF(F26>=180,“合格”，“不合格”))嵌套使用：

If函數(shù)可以嵌套使用七層,利用

True、False構(gòu)造復(fù)雜的關(guān)系！IF函數(shù)EXCEL函數(shù)實(shí)例操作If函數(shù)Excel根底及函數(shù)的應(yīng)用sum函數(shù)語法:

SUM(number1,number2,...)參數(shù):

number1，number2，...為1到30個(gè)需要求和的參數(shù)。定義：返回某一單元格區(qū)域中數(shù)字、邏輯值及數(shù)字的文本表達(dá)式之和Excel根底及函數(shù)的應(yīng)用說明：

直接鍵入到參數(shù)表中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式將被計(jì)算。

如果參數(shù)為數(shù)組或引用，只有其中的數(shù)字將被計(jì)算。數(shù)組或引用中的空白單元格、邏輯值、文本或錯(cuò)誤值將被忽略。

如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換成數(shù)字的文本，將會導(dǎo)致錯(cuò)誤。

Excel根底及函數(shù)的應(yīng)用例如：

SUM(5,3)等于8。

SUM(“6",3,TRUE)等于10，因?yàn)槲谋局当晦D(zhuǎn)換成數(shù)字，而邏輯值"TRUE"被轉(zhuǎn)換成數(shù)字1。

不同于前例，如果A1包含"3"，而B1包含TRUE，那么：SUM(A1,B1,2)等于2，因?yàn)閷Ψ菙?shù)值型的值的引用不能被轉(zhuǎn)換成數(shù)值。

如果單元格A2：E2包含5，15，30，40和50，那么：SUM(A2:C2)等于50；SUM(B2:E2,15)等于150。Excel根底及函數(shù)的應(yīng)用定義：根據(jù)指定條件對假設(shè)干單元格求和sumif函數(shù)語法

SUMIF(range,criteria,sum_range)

1〕range

為用于條件判斷的單元格區(qū)域。

2〕criteria

為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為32、“32”、“>32”或“李先春"。

3〕sum_range

是需要求和的實(shí)際單元格。Excel根底及函數(shù)的應(yīng)用求出上圖中李先春銷量總和？例解一=sumif(B2:E7,”李先春“，C2:E7)

結(jié)果=488Excel根底及函數(shù)的應(yīng)用求出上圖中大于10的數(shù)值總和？例解二sumif(C3:C7,">=10")

結(jié)果=33Excel根底及函數(shù)的應(yīng)用實(shí)例操作sumif函數(shù)Excel根底及函數(shù)的應(yīng)用Count表達(dá)式：

Count(value1,value2,……)

說明:計(jì)算連續(xù)或不連續(xù)區(qū)域的數(shù)字個(gè)數(shù)。參數(shù)value代表1-30個(gè)各種不同類型的數(shù)據(jù)，或單元格引用，但只對數(shù)字型數(shù)據(jù)進(jìn)行計(jì)算。Excel根底及函數(shù)的應(yīng)用COUNT：A1=0A2=2A3=“”A4=“#REEF”

A5=Count(A1,A2,A3,A4,2)

結(jié)果

A6=3Excel根底及函數(shù)的應(yīng)用CountA表達(dá)式：

CountA(value1,value2,……)說明:計(jì)算參數(shù)表中任意類型數(shù)據(jù)的個(gè)數(shù)?；騿卧駞^(qū)域中非空的單元格個(gè)數(shù)。參數(shù)value代表1-30個(gè)各種不同類型的數(shù)據(jù)，或單元格引用。Excel根底及函數(shù)的應(yīng)用COUNTA：A1=0A2=2A3=“”A4=“#REEF”

A5=“總量”

A6=Count(A1,A2,A3,A4,A5,2)結(jié)果A6=6Excel根底及函數(shù)的應(yīng)用COUNT與COUNTA的區(qū)別

count只統(tǒng)計(jì)數(shù)字型單元格的個(gè)數(shù),對于文字,錯(cuò)誤值,邏輯值<True>不能統(tǒng)計(jì),而counta可以,但對于空白單元格都不能統(tǒng)計(jì).

Excel根底及函數(shù)的應(yīng)用實(shí)例操作Count函數(shù)Counta函數(shù)Excel根底及函數(shù)的應(yīng)用countif函數(shù)語法：

COUNTIF(range,criteria)

說明：

Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域，

Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式、單元格引用或文本〔條件可以表示為79、“79”、“>79”、“李先春”或B4〕Excel根底及函數(shù)的應(yīng)用上圖中求出數(shù)據(jù)1中A開頭的個(gè)數(shù)？例解一=countif(B2:B6,”A*”)

結(jié)果=3Excel根底及函數(shù)的應(yīng)用Left函數(shù)

表達(dá)式：

Left(Text,num_chars)

說明

Text:文本字符串

num_chars:指定要提取的字符數(shù)

注意:

num_chars必須大于等于0如果n_cs大于文本長度,返回所有文本如果省略num_chars,默認(rèn)為1

Excel根底及函數(shù)的應(yīng)用功能:從字符串左端取指定個(gè)數(shù)字符:A1=012141002A2=Left(A1,2)結(jié)果:A2=01

例解一Left函數(shù)Excel根底及函數(shù)的應(yīng)用right函數(shù)功能:從字符串右端取指定個(gè)數(shù)字符表達(dá)式right(Text,num_chars)

說明

Text:文本字符串

num_chars:指定要提取的字符數(shù)Excel根底及函數(shù)的應(yīng)用:A1=012141002A2=right(A1,2)結(jié)果:A2=02

例解一right函數(shù)Excel根底及函數(shù)的應(yīng)用Right函數(shù)與Left函數(shù)用法相同,只是意義不同.Excel根底及函數(shù)的應(yīng)用mid函數(shù)表達(dá)式：Mid(Text，start_num，num_chars)Text:文本字符串start_num:提取的第一個(gè)字符的位置num_chars:返回的字符的個(gè)數(shù),不能是負(fù)數(shù)〔注：start_num大于文本總長度,返回空，start_num+num_chars大于文本總長度,返回到文本末的字符串，start_num小于1,返回錯(cuò)誤值#value!〕Excel根底及函數(shù)的應(yīng)用：A1=002141001A2=mid(A1,3,3)結(jié)果A2=214

mid函數(shù)功能：

從一個(gè)文本字符串的指定位置開始，截取指定數(shù)目的字符。

字符截取Excel根底及函數(shù)的應(yīng)用Left-Right-Mid實(shí)例操作Excel根底及函數(shù)的應(yīng)用Max-Min函數(shù)

表達(dá)式：

Max(number1,number2,......)MAX

求出一組數(shù)中的最大值。

數(shù)據(jù)計(jì)算MIN

求出一組數(shù)中的最小值。

數(shù)據(jù)計(jì)算Excel根底及函數(shù)的應(yīng)用：A1=6,A2=4,A3=8如果：A4=Max(A1,A2,A3,2)結(jié)果：A4=8Max-Min函數(shù)

如果：A4=MIN(A1,A2,A3,2)結(jié)果：A4=2Excel根底及函數(shù)的應(yīng)用學(xué)會使用嵌套的關(guān)系聯(lián)結(jié)符&對有價(jià)值數(shù)據(jù)的提取和組合實(shí)例操作Excel根底及函數(shù)的應(yīng)用Max-Min函數(shù)

Rank函數(shù)Excel根底及函數(shù)的應(yīng)用表達(dá)式：rank(Number,ref,order)使用功能：求指定數(shù)字在一列數(shù)字中的排位〔排名次〕說明：Number代表需要求排位的數(shù)據(jù)〔通常使用單元格的相對引用〕，ref代表Number所在的一組數(shù)據(jù)〔通常使用單元格區(qū)域的絕對引用〕Order為一數(shù)字，指明排位的方式〔0或省略為降序，大于0那么為升序〕。求A2在$A$2:$A$5單元格區(qū)域降序排列的名次？

=rank(A2,$A$2:$A$5)

=2Excel根底及函數(shù)的應(yīng)用例如：EXCELABCD1分?jǐn)?shù)排名2502326430552實(shí)例操作Excel根底及函數(shù)的應(yīng)用Rank函數(shù)FREQUENCY函數(shù)Excel根底及函數(shù)的應(yīng)用表達(dá)式：

FREQUENCY〔Data_array,Bins_array)說明：參數(shù)Data_array用于計(jì)算頻率的數(shù)組，可以是一個(gè)單元格區(qū)域。參數(shù)Bins_array代表分組區(qū)間數(shù)組，表示頻率計(jì)算的分段點(diǎn)。

功能：求參數(shù)序列的頻率分布。舉例：Excel根底及函數(shù)的應(yīng)用=FREQUENCY〔B2：B7,C2:C5〕,可以求各個(gè)分?jǐn)?shù)段的人數(shù)。EXCELABCDE1姓名分?jǐn)?shù)分?jǐn)?shù)段說明結(jié)果2李先春7570<=70個(gè)數(shù)13郭俊808071-80個(gè)數(shù)34馬虎909081-90個(gè)數(shù)15文軍67>=90個(gè)數(shù)16高偉917番有才78分?jǐn)?shù)小于等于70的個(gè)數(shù)(1)

成績介于71-80之間的個(gè)數(shù)(3)

成績介于81-90之間的個(gè)數(shù)(1)

成績大于等于91的個(gè)數(shù)(1)

注意:公式必須以數(shù)組公式的形式輸入。選中從公式單元格開始的單元格區(qū)域C2:C5。按F2，再按Ctrl+Shift+Enter。如果公式未以數(shù)組公式的形式輸入，那么返回的結(jié)果為1FREQUENCY函數(shù)實(shí)例操作重點(diǎn)函數(shù)Vlookup

hlookup

Sumif

Sumproduct

Datedif

Weeknum

monthExcel根底及函數(shù)的應(yīng)用vlookup表達(dá)式:VLOOKUP〔lookup_value，table_array，col_index_num，range_lookup〕

說明:1:要查找的值;2:查找內(nèi)容的數(shù)組;3:數(shù)據(jù)后開始的列數(shù);4:TrueFalse

Excel根底及函數(shù)的應(yīng)用VLOOKUP函數(shù)的功能:在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。

Vlookup例如:

Excel根底及函數(shù)的應(yīng)用在B50中得出王勇的總分?jǐn)?shù)?B50=Vlookup(A50,A1:E5,5,0)

=223excelAB50王勇51郭俊實(shí)例操作vlookupExcel根底及函數(shù)的應(yīng)用Excel根底及函數(shù)的應(yīng)用HLOOKUP函數(shù)表達(dá)式:HLOOKUP(lookup_value,table_array,row_index_um,range_lookup)

參數(shù)lookup_value用于設(shè)定要查找的值，可以是數(shù)值，也可以是文本字符串或引用，參數(shù)table_array用于設(shè)置要在其中查找數(shù)據(jù)的數(shù)據(jù)表，這里可以使用區(qū)域或區(qū)域名稱的引用，參數(shù)row_index_um為在查找之后要返回的匹配值的行序號，參數(shù)range_lookup是一個(gè)邏輯值，用于指明函數(shù)在查找時(shí)是精確匹配，還是近似匹配hlookup函數(shù)就是查找函數(shù)，不過它與lookup和vlookup函數(shù)有區(qū)別

hlookup是按行查找的vlookup是按列查找的

=hlookup("要查找的值","待查找的數(shù)據(jù)區(qū)域",行數(shù))Excel根底及函數(shù)的應(yīng)用Hlookup例如:ExcelABCDE1日期姓名123匯總2李鵬