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

2、數據、表格格式化

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

5、復制、選擇性粘貼

6、查找、替換的強大功能

7、頁面設置、打印的方式

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

2、函數的應用

3、案例的分析Excel根底及函數的應用函數二、函數§運用根本函數實現對數據、單元格的操作

§運用函數Count、Countif等進行復雜數量統計

§運用函數Sumif、Sumproduct、數組公式等進行有條件的求和

§運用函數Vlookup、Hlookup進行高級數據查詢

§Datedif、Mid等主要日期和文本函數的應用

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

♂重要函數Excel根底及函數的應用If

SUM--SUMIF

Count--counta

COUNTIF

Average〔求平均值〕

Left-Right-Mid

Max-Min

RANK(排位)

FREQUENCY(頻率分布〕Excel根底及函數的應用常用函數表達式

IF(logical_test,value_if_true,value_if_false)

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

說明

邏輯條件:運算結果為True或者False的表達式或值

真值:Logical_test為True時返回的值

假值:Logical_test為False時返回的值

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

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

幾點說明：

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

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

If函數可以嵌套使用七層,利用

True、False構造復雜的關系！IF函數EXCEL函數實例操作If函數Excel根底及函數的應用sum函數語法:

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

number1，number2，...為1到30個需要求和的參數。定義：返回某一單元格區域中數字、邏輯值及數字的文本表達式之和Excel根底及函數的應用說明：

直接鍵入到參數表中的數字、邏輯值及數字的文本表達式將被計算。

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

如果參數為錯誤值或為不能轉換成數字的文本，將會導致錯誤。

Excel根底及函數的應用例如：

SUM(5,3)等于8。

SUM(“6",3,TRUE)等于10，因為文本值被轉換成數字，而邏輯值"TRUE"被轉換成數字1。

不同于前例，如果A1包含"3"，而B1包含TRUE，那么：SUM(A1,B1,2)等于2，因為對非數值型的值的引用不能被轉換成數值。

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

SUMIF(range,criteria,sum_range)

1〕range

為用于條件判斷的單元格區域。

2〕criteria

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

3〕sum_range

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

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

結果=33Excel根底及函數的應用實例操作sumif函數Excel根底及函數的應用Count表達式：

Count(value1,value2,……)

說明:計算連續或不連續區域的數字個數。參數value代表1-30個各種不同類型的數據，或單元格引用，但只對數字型數據進行計算。Excel根底及函數的應用COUNT：A1=0A2=2A3=“”A4=“#REEF”

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

結果

A6=3Excel根底及函數的應用CountA表達式：

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

A5=“總量”

A6=Count(A1,A2,A3,A4,A5,2)結果A6=6Excel根底及函數的應用COUNT與COUNTA的區別

count只統計數字型單元格的個數,對于文字,錯誤值,邏輯值<True>不能統計,而counta可以,但對于空白單元格都不能統計.

Excel根底及函數的應用實例操作Count函數Counta函數Excel根底及函數的應用countif函數語法：

COUNTIF(range,criteria)

說明：

Range為需要計算其中滿足條件的單元格數目的單元格區域，

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

結果=3Excel根底及函數的應用Left函數

表達式：

Left(Text,num_chars)

說明

Text:文本字符串

num_chars:指定要提取的字符數

注意:

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

Excel根底及函數的應用功能:從字符串左端取指定個數字符:A1=012141002A2=Left(A1,2)結果:A2=01

例解一Left函數Excel根底及函數的應用right函數功能:從字符串右端取指定個數字符表達式right(Text,num_chars)

說明

Text:文本字符串

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

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

mid函數功能：

從一個文本字符串的指定位置開始，截取指定數目的字符。

字符截取Excel根底及函數的應用Left-Right-Mid實例操作Excel根底及函數的應用Max-Min函數

表達式：

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

求出一組數中的最大值。

數據計算MIN

求出一組數中的最小值。

數據計算Excel根底及函數的應用：A1=6,A2=4,A3=8如果：A4=Max(A1,A2,A3,2)結果：A4=8Max-Min函數

如果：A4=MIN(A1,A2,A3,2)結果：A4=2Excel根底及函數的應用學會使用嵌套的關系聯結符&對有價值數據的提取和組合實例操作Excel根底及函數的應用Max-Min函數

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

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

=2Excel根底及函數的應用例如：EXCELABCD1分數排名2502326430552實例操作Excel根底及函數的應用Rank函數FREQUENCY函數Excel根底及函數的應用表達式：

FREQUENCY〔Data_array,Bins_array)說明：參數Data_array用于計算頻率的數組，可以是一個單元格區域。參數Bins_array代表分組區間數組，表示頻率計算的分段點。

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

成績介于71-80之間的個數(3)

成績介于81-90之間的個數(1)

成績大于等于91的個數(1)

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

hlookup

Sumif

Sumproduct

Datedif

Weeknum

monthExcel根底及函數的應用vlookup表達式:VLOOKUP〔lookup_value，table_array，col_index_num，range_lookup〕

說明:1:要查找的值;2:查找內容的數組;3:數據后開始的列數;4:TrueFalse

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

Vlookup例如:

Excel根底及函數的應用在B50中得出王勇的總分數?B50=Vlookup(A50,A1:E5,5,0)

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

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

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

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