excel公式與常用函數(shù)(+實(shí)例)[高教課堂]

1、EXCELEXCEL公式和函數(shù)公式和函數(shù) 1.1. 公式公式 2.2. 公式的輸入公式的輸入 3.3. 公式的調(diào)整公式的調(diào)整 4.4. 常用函數(shù)常用函數(shù) 1教育教學(xué) Excel Excel表格中不僅可以輸入數(shù)據(jù)，也可表格中不僅可以輸入數(shù)據(jù)，也可 以使用公式對工作表中的數(shù)據(jù)進(jìn)行各種數(shù)值計以使用公式對工作表中的數(shù)據(jù)進(jìn)行各種數(shù)值計 算和統(tǒng)計分析等算和統(tǒng)計分析等 Excel Excel的公式是由運(yùn)算符號和參與計算的公式是由運(yùn)算符號和參與計算 的運(yùn)算數(shù)組成的表達(dá)式的運(yùn)算數(shù)組成的表達(dá)式 1.1公式 2教育教學(xué) 1.2公式中的運(yùn)算符 1.1. 算術(shù)運(yùn)算符：算術(shù)運(yùn)算符：+ - + - * * / % / %

2、2.2. 字符運(yùn)算符：字符運(yùn)算符：& & 連接符連接符 3.3. 比較運(yùn)算符：比較運(yùn)算符：= = = = 4.4. 邏輯運(yùn)算符：邏輯運(yùn)算符：and or not and or not 以函數(shù)形式出現(xiàn)以函數(shù)形式出現(xiàn) 5.5. 優(yōu)先級順序：算術(shù)運(yùn)算符優(yōu)先級順序：算術(shù)運(yùn)算符字符運(yùn)算符字符運(yùn)算符比較運(yùn)算符比較運(yùn)算符邏輯函數(shù)符邏輯函數(shù)符 ( (使用括號可確定運(yùn)算順序使用括號可確定運(yùn)算順序) ) 3教育教學(xué) 1.3公式中的數(shù)據(jù)類型 輸入公式要注意輸入公式要注意 1.1. 公式中的運(yùn)算對象可以包括：數(shù)值和字符、單元格公式中的運(yùn)算對象可以包括：數(shù)值和字符、單元格 地址、區(qū)域、函數(shù)等地址、區(qū)域、函數(shù)等, ,如：

3、如：=A2+B2+C2=A2+B2+C2 2.2. 不要隨意包含空格不要隨意包含空格 3.3. 公式中的字符要用半角引號括起來公式中的字符要用半角引號括起來 4.4. 公式中運(yùn)算符兩邊的數(shù)據(jù)類型要相同公式中運(yùn)算符兩邊的數(shù)據(jù)類型要相同 如：如：=ab+25 =ab+25 出錯出錯 #VALUE#VALUE 4教育教學(xué) 2.1公式的輸入 公式的基本特性：公式的基本特性： 1.1. 公式的輸入是以公式的輸入是以“ = ”= ”開始開始 2.2. 公式計算結(jié)果顯示在單元格中公式計算結(jié)果顯示在單元格中 3.3. 公式本身顯示在編輯欄中公式本身顯示在編輯欄中 5教育教學(xué) 2.2公式示例 =1+2+6 =1

4、+2+6 （數(shù)值計算）（數(shù)值計算） =A1+B2 =A1+B2 （引用單元格地址）（引用單元格地址） =SUM(A1:C4) =SUM(A1:C4) （公式計算）（公式計算） =IF( and(A1A2,B1B2),100=IF( and(A1A2,B1B2),100* *A1/A2, 100A1/A2, 100* *B1/B2) B1/B2) （函數(shù)計算）（函數(shù)計算） =ABC&XYZ (=ABC&XYZ (字符計算，結(jié)果字符計算，結(jié)果:ABCXYZ) :ABCXYZ) 公式中常用單元格的引用來代替單元格的具體數(shù)據(jù)，當(dāng)公式公式中常用單元格的引用來代替單元格的具體數(shù)據(jù)，當(dāng)公式 中被引用單元格的

5、數(shù)據(jù)變化時，公式的計算結(jié)果會隨之變化中被引用單元格的數(shù)據(jù)變化時，公式的計算結(jié)果會隨之變化 6教育教學(xué) 2.32.3公式的復(fù)制公式的復(fù)制 1.1.使用填充柄使用填充柄 2.2.菜單菜單 編輯編輯 復(fù)制復(fù)制/粘貼粘貼 3.3. 復(fù)制公式時，公式會作相對調(diào)整復(fù)制公式時，公式會作相對調(diào)整 7教育教學(xué) 3.1公式的調(diào)整 1.1. 相對引用相對引用是針對當(dāng)前單元格的引用，是針對當(dāng)前單元格的引用， 是隨當(dāng)前單元格變化而變化的。是隨當(dāng)前單元格變化而變化的。 2.2. 絕對引用絕對引用是針對起始單元格的引用，是針對起始單元格的引用， 是不隨當(dāng)前單元格的變化而變化的。是不隨當(dāng)前單元格的變化而變化的。 8教育教學(xué)

6、公式的調(diào)整 相對地址引用相對地址引用: : 在公式復(fù)制時將自動調(diào)整在公式復(fù)制時將自動調(diào)整 例如：例如： 將將D2D2單元中的公式單元中的公式 “=A2+B2+C2“=A2+B2+C2“ 復(fù)制到復(fù)制到D4D4后，后，D4D4中的公式中的公式 ”=A4+B4+C4=A4+B4+C4” 9教育教學(xué) 公式的調(diào)整 絕對地址引用絕對地址引用 ：公式復(fù)制時不變。：公式復(fù)制時不變。 例如：例如： C3C3單元的公式單元的公式=$B$2+$B$3+$B$4=$B$2+$B$3+$B$4 復(fù)制到復(fù)制到D5D5中，中，D5D5單元的公式單元的公式=$B$2+$B$3+$B$4=$B$2+$B$3+$B$4 ( (在

7、公式復(fù)制時絕對地址不變，相對地址按規(guī)則調(diào)整。在公式復(fù)制時絕對地址不變，相對地址按規(guī)則調(diào)整。) ) 10教育教學(xué) 3.2公式的移動 公式移動：相對和絕對地址都不調(diào)整公式移動：相對和絕對地址都不調(diào)整 C3C3單元格的公式單元格的公式=B2+B3+B4=B2+B3+B4移動到移動到D5D5中，中， D3D3單元格的公式單元格的公式=B2+B3+B4=B2+B3+B4 ( (被公式引用的被公式引用的單元格移動單元格移動，公式跟蹤單元格，值不變，公式跟蹤單元格，值不變) ) 11教育教學(xué) 4.14.1常用函數(shù)常用函數(shù) 12教育教學(xué) 4.1統(tǒng)計函數(shù) 1. sum是求參數(shù)的和，就是求指定的所有參數(shù)的和。 2

8、. sumif是條件求和，Excel中sumif函數(shù)的用法是根據(jù)指定條件對若干單元格、 區(qū)域或引用求和。 sumif函數(shù)語法是：SUMIF(range，criteria，sum_range) sumifsumif函數(shù)的參數(shù)如下：函數(shù)的參數(shù)如下： 第一個參數(shù)：第一個參數(shù)：RangeRange為條件區(qū)域，用于條件判斷的單元格區(qū)域。為條件區(qū)域，用于條件判斷的單元格區(qū)域。 第二個參數(shù)：第二個參數(shù)：CriteriaCriteria是求和條件，由數(shù)字、邏輯表達(dá)式等組成的判定條件。是求和條件，由數(shù)字、邏輯表達(dá)式等組成的判定條件。 第三個參數(shù)：第三個參數(shù)：Sum_rangeSum_range 為實(shí)際求和區(qū)域，

9、需要求和的單元格、區(qū)域或引用。為實(shí)際求和區(qū)域，需要求和的單元格、區(qū)域或引用。 當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實(shí)際求和區(qū)域。當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實(shí)際求和區(qū)域。 注：criteria 參數(shù)中使用通配符（包括問號 (?)和星號 (*)）。問號匹配任 意單個字符；星號匹配任意一串字符。如果要查找實(shí)際的問號或星號，請在該 字符前鍵入波形符 ()。 13教育教學(xué) 3.COUNT3.COUNT 用途用途：它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的：它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)單元格個數(shù)。 函數(shù)語法函數(shù)語法：COUNT(value1，value2，.)。 參數(shù)參數(shù)：value

10、1，value2，.是包含或引用各種類型數(shù)據(jù)的參數(shù)(130個)，其中只其中只 有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計。 實(shí)例：實(shí)例：如果A1=90、A2=人數(shù)、A3=、A4=54、A5=36，則公式“=COUNT(A1:A5) ” 返回得3 14教育教學(xué) 4.COUNTA4.COUNTA 用途用途：返回參數(shù)組中非空值的數(shù)目。 語法語法：COUNTA(value1，value2，.) 說明說明：參數(shù)可以是任何類型，它們包括空格但不包括空白單元格。如果不需 要統(tǒng)計邏輯值、文字或錯誤值，則應(yīng)該使用COUNT函數(shù)。 實(shí)例實(shí)例：如果A1=6.28、A2=3.74，A3=“我們”其余單元格為

11、空，則公式 “=COUNTA(A1:A5)”的計算結(jié)果等于3。 15教育教學(xué) 5.COUNTBLANK5.COUNTBLANK 用途：用途：計算某個單元格區(qū)域中空白單元格的數(shù)目。 函數(shù)語法：函數(shù)語法：COUNTBLANK(range) 參數(shù)：參數(shù)：Range為需要計算其中空白單元格數(shù)目的區(qū)域。 實(shí)例：實(shí)例：如果A1=8888、A2=55、A3=(空格)、A4=72、A5=(空格)，則公式 “=COUNTBLANK(A1:A5)”返回得2。 16教育教學(xué) 6.COUNTIF6.COUNTIF 用途：用途：計算區(qū)域中滿足給定條件的單元格的個數(shù)。 語法：語法：COUNTIF(range，criter

12、ia) 參數(shù)：參數(shù)： Range：需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria：確定哪些單元格將被計算在 內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。 實(shí)例：實(shí)例：在A1:A7之間，確定數(shù)字55被計算在內(nèi)的條件，返回得2 17教育教學(xué) 7.MAX7.MAX：返回選定數(shù)據(jù)庫項中的最大值返回選定數(shù)據(jù)庫項中的最大值 8.MIN8.MIN：返回選定數(shù)據(jù)庫項中的最小值返回選定數(shù)據(jù)庫項中的最小值 18教育教學(xué) 4.2邏輯（條件）函數(shù) 1 1、IFIF 1) IF1) IF函數(shù)的語法結(jié)構(gòu)：函數(shù)的語法結(jié)構(gòu)：IF(IF(條件條件, ,結(jié)果結(jié)果1,1,結(jié)果結(jié)果2)2)。 2)2) IFIF函數(shù)的功

13、能函數(shù)的功能 對滿足條件的數(shù)據(jù)進(jìn)行處理，條件滿足則輸出對滿足條件的數(shù)據(jù)進(jìn)行處理，條件滿足則輸出 結(jié)果結(jié)果1 1，不滿足則輸出結(jié)果，不滿足則輸出結(jié)果2 2?？梢允÷越Y(jié)果?？梢允÷越Y(jié)果1 1或結(jié)果或結(jié)果 2 2，但不能同時省略。，但不能同時省略。 19教育教學(xué) 3)3) 條件表達(dá)式條件表達(dá)式 把兩個表達(dá)式用關(guān)系運(yùn)算符把兩個表達(dá)式用關(guān)系運(yùn)算符( (主要有主要有= =， ， =， =等等6 6個關(guān)系運(yùn)算符個關(guān)系運(yùn)算符) )連接起來就構(gòu)成條件表達(dá)式。連接起來就構(gòu)成條件表達(dá)式。 例如，在例如，在IF(a1+b1+50 b1+c1-50,IF(a1+b1+50 b1+c1-50,1,2)1,2)函數(shù)式中，函

14、數(shù)式中， 條件表達(dá)式是條件表達(dá)式是a1+b1+50 b1+c1-50a1+b1+50 b1+c1-50。 4)4) 執(zhí)行過程執(zhí)行過程 下面以下面以IF(a1+b1+50 b1+c1-50,IF(a1+b1+50 b1+c1-50,1,1)1,1)函數(shù)式為例來說明函數(shù)式為例來說明 IFIF函數(shù)的執(zhí)行過程。函數(shù)的執(zhí)行過程。 20教育教學(xué) 21教育教學(xué) 例例2 2： IF(E2=85,IF(E2=85,“優(yōu)優(yōu)”,IF(E2=75,IF(E2=75,“良良”，IF(E2=60,IF(E2=60,及格及格 ， 不及格不及格) 公式理解：公式理解： 函數(shù)從左向右執(zhí)行。函數(shù)從左向右執(zhí)行。 首先計算首先計算E

15、2=85E2=85，如果該表達(dá)式成立，則顯示，如果該表達(dá)式成立，則顯示“優(yōu)優(yōu)”， 如果不成立就繼續(xù)計算如果不成立就繼續(xù)計算E2=75E2=75，如果該表達(dá)式成立，則顯示，如果該表達(dá)式成立，則顯示“良良”， 否則繼續(xù)計算否則繼續(xù)計算E2=60E2=60，如果該表達(dá)式成立，則顯示，如果該表達(dá)式成立，則顯示“及格及格”，否則顯，否則顯 示示“不及格不及格”。 22教育教學(xué) 2.AND2.AND 1. 說明：所有參數(shù)的計算結(jié)果為 TRUE 時，返回 TRUE；只要有一個參數(shù) 的計算結(jié)果為 FALSE，即返回 FALSE。 2. 函數(shù)語法：AND(logical1, logical2, .) logic

16、al1：必需。要檢驗(yàn)的第一個條件，其計算結(jié)果可以為 TRUE 或 FALSE。 logical2, . ：可選。要檢驗(yàn)的其他條件，其計算結(jié)果可以為 TRUE 或 FALSE，最多可包含 255 個條件。 3. 實(shí)例： =AND(2+2=4, 2+3=5) 所有參數(shù)的計算結(jié)果均為TRUE =AND(2+2=4, 2+3=6) 所有參數(shù)的計算結(jié)果均為FALSE 23教育教學(xué) 4.3查找和引用函數(shù) 參數(shù)參數(shù)簡單說明簡單說明輸入數(shù)據(jù)類型輸入數(shù)據(jù)類型 lookup_value要查找的值數(shù)值、引用或文本字符串 table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域 row_index_num返回數(shù)據(jù)在區(qū)域的第幾行數(shù)

17、正整數(shù) range_lookup模糊匹配/精確匹配TRUE /FALSE（或不填） 1 1、HLOOKUPHLOOKUP： 說明：說明： HLOOKUP函數(shù)主要用于在表格或數(shù)值組的首行查找指定的數(shù)值（即在水平查找）， 并返回表格顯示當(dāng)前列中指定行處的值。 函數(shù)語法函數(shù)語法： HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 24教育教學(xué) 實(shí)例： 用下面這個實(shí)例展示Hlookup函數(shù)用法 A1:B2表示要在其中查找的區(qū)間，公式中的table_array F1到G1中表示我們要查找的對象，公式中的lookup_value 現(xiàn)在

18、我們要查找F1到G1分別對應(yīng)的數(shù)值。 （按F4鍵，將其變成絕對引用。） 25教育教學(xué) 2 2、VLOOKUPVLOOKUP 說明：說明： VLOOKUP函數(shù)是Excel中的一個縱向查找函數(shù)，它與LOOKUP函數(shù)和HLOOKUP函 數(shù)屬于一類函數(shù)，在工作中都有廣泛應(yīng)用。功能是按列查找，最終返回該列所需查 詢列序所對應(yīng)的值；與之對應(yīng)的HLOOKUP是按行查找的。 函數(shù)語法：函數(shù)語法： VLOOKUP( VLOOKUP(lookup_value,table_array,col_index_num,range_lookuplookup_value,table_array,col_index_num,r

19、ange_lookup) ) 參數(shù)參數(shù)簡單說明簡單說明輸入數(shù)據(jù)類型輸入數(shù)據(jù)類型 lookup_value要查找的值數(shù)值、引用或文本字符串 table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域 col_index_num返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù)正整數(shù) range_lookup模糊匹配/精確匹配TRUE（或不填）/FALSE 實(shí)例： 用下面這個實(shí)例展示vlookup函數(shù)用法 A1:B7表示要在其中查找的區(qū)間，公式中的table_array E1中表示我們要查找的對象，公式中的lookup_value 現(xiàn)在我們要查找E1對應(yīng)的數(shù)值。 （按（按F4F4鍵，將其變成絕對引用。）鍵，將其變成絕對引用。） 26

20、教育教學(xué) 4.4排位函數(shù)：RANK 說明：說明：rank函數(shù)是排名函數(shù)。rank函數(shù)最常 用的是求某一個數(shù)值在某一區(qū)域內(nèi)的排名。 函數(shù)語法：函數(shù)語法：rank(number,ref,order) 實(shí)例：給下列成績排名實(shí)例：給下列成績排名 27教育教學(xué) 4.5文本函數(shù)：REPLACE 1.1.說明：說明： 用新字符串替換舊字符串，而且替換的位置和數(shù)量都是指定的而且替換的位置和數(shù)量都是指定的。 2. 2. 函數(shù)語法：函數(shù)語法： =Replace(old_text,start_num,num_chars，new_text) =replace（要替換的字符串，開始位置，替換個數(shù)，新的文本） 注意：第四

21、個參數(shù)是文本，要加上引號。 3.3.實(shí)例：實(shí)例： =REPLACE(=REPLACE(替換區(qū)域替換區(qū)域A2A2，第八個數(shù)字開始，替換，第八個數(shù)字開始，替換4 4個，替換為個，替換為“xxxxxxxx” 28教育教學(xué) 4.6時間與日期函數(shù) 1.TODAY：用于返回當(dāng)前系統(tǒng)的日期，其完整的格式：=TODAY( ) 2.NOW：用于返回當(dāng)前系統(tǒng)的時間，其完整的格式：=DAY( ) DATE函數(shù)：返回指定日期的序列號 函數(shù)語法：DATE(year，month，day) 29教育教學(xué) 4.7數(shù)據(jù)庫函數(shù) 一、DAVERAGE 1.說明：返回數(shù)據(jù)庫或數(shù)據(jù)清單中滿足指定條件的列中數(shù)值的平均值. 2.函數(shù)語法：

22、DAVERAGE(database,field,criteria) 參數(shù)： Database構(gòu)成數(shù)據(jù)表或數(shù)據(jù)庫的單元格區(qū)域。 Field指定函數(shù)所使用的數(shù)據(jù)列。 Criteria為一組包含給定條件的單元格區(qū)域。 3.實(shí)例： 實(shí)例1：利用daverage函數(shù)單條件求平均值，求電視的平均數(shù)量。 實(shí)例2：求B分店電視的平均數(shù)量 輸入公式=DAVERAGE(A1:C9,C1,E1:E2) 數(shù)據(jù)區(qū)域數(shù)據(jù)區(qū)域A1:C9（整個查找表） 字段名字段名C1，指定求平均值的數(shù)據(jù)列。 也可以等于3（參數(shù)Field =C1等價于參數(shù)Field =3。即表示返回第三列的值。) 條件區(qū)域條件區(qū)域E1:E2，表示品名為電視。 30教育教學(xué) 二、DSUM 1.1.說明：說明：將數(shù)據(jù)庫中符合條件的記錄的字段列中的數(shù)字的和。使用它可以對數(shù)據(jù) 進(jìn)行多條件累加，這種方式可以使條