excel公式與常用函數(shù)(+實例)

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

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

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

4、 =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)公式中被引用單元格的數(shù)據(jù)變化時，公式的計算結(jié)果會隨之變化中被引用單元格的數(shù)據(jù)變化時，公式的計算結(jié)果會隨之變化2

5、.32.3公式的復(fù)制公式的復(fù)制1.1.使用填充柄使用填充柄2.2.菜單菜單 編輯編輯 復(fù)制復(fù)制/粘貼粘貼 3.3. 復(fù)制公式時，公式會作相對調(diào)整復(fù)制公式時，公式會作相對調(diào)整3.1公式的調(diào)整1.1. 相對引用相對引用是針對當(dāng)前單元格的引用，是針對當(dāng)前單元格的引用，是隨當(dāng)前單元格變化而變化的。是隨當(dāng)前單元格變化而變化的。2.2. 絕對引用絕對引用是針對起始單元格的引用，是針對起始單元格的引用，是不隨當(dāng)前單元格的變化而變化的。是不隨當(dāng)前單元格的變化而變化的。公式的調(diào)整相對地址引用相對地址引用: : 在公式復(fù)制時將自動調(diào)整在公式復(fù)制時將自動調(diào)整例如：例如：將將D2D2單元中的公式單元中的公式 “=A2

6、+B2+C2“=A2+B2+C2“復(fù)制到復(fù)制到D4D4后，后，D4D4中的公式中的公式 ”=A4+B4+C4=A4+B4+C4”公式的調(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( (在公式復(fù)制時絕對地址不變，相對地址按規(guī)則調(diào)整。在公式復(fù)制時絕對地址不變，相對地址按規(guī)則調(diào)整。) )3.2公式的移動公式移動：相對和絕對地址都不調(diào)整公式移動：相對和絕對地址都不調(diào)整

7、C3C3單元格的公式單元格的公式=B2+B3+B4=B2+B3+B4移動到移動到D5D5中，中，D3D3單元格的公式單元格的公式=B2+B3+B4=B2+B3+B4( (被公式引用的被公式引用的單元格移動單元格移動，公式跟蹤單元格，值不變，公式跟蹤單元格，值不變) )4.14.1常用函數(shù)常用函數(shù)4.1統(tǒng)計函數(shù)1. sum是求參數(shù)的和，就是求指定的所有參數(shù)的和。2. sumif是條件求和，Excel中sumif函數(shù)的用法是根據(jù)指定條件對若干單元格、區(qū)域或引用求和。sumif函數(shù)語法是：SUMIF(range，criteria，sum_range)sumifsumif函數(shù)的參數(shù)如下：函數(shù)的參數(shù)如下

8、：第一個參數(shù)：第一個參數(shù)：RangeRange為條件區(qū)域，用于條件判斷的單元格區(qū)域。為條件區(qū)域，用于條件判斷的單元格區(qū)域。第二個參數(shù)：第二個參數(shù)：CriteriaCriteria是求和條件，由數(shù)字、邏輯表達式等組成的判定條件。是求和條件，由數(shù)字、邏輯表達式等組成的判定條件。第三個參數(shù)：第三個參數(shù)：Sum_rangeSum_range為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實際求和區(qū)域。當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實際求和區(qū)域。注：criteria 參數(shù)中使用通配符（包括問號 (?)和星號 (*)）。問號

9、匹配任意單個字符；星號匹配任意一串字符。如果要查找實際的問號或星號，請在該字符前鍵入波形符 ()。3.COUNT3.COUNT用途用途：它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的：它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)單元格個數(shù)。函數(shù)語法函數(shù)語法：COUNT(value1，value2，.)。參數(shù)參數(shù)：value1，value2，.是包含或引用各種類型數(shù)據(jù)的參數(shù)(130個)，其中只其中只有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計。實例：實例：如果A1=90、A2=人數(shù)、A3=、A4=54、A5=36，則公式“=COUNT(A1:A5) ”返回得3 4.COUNTA4.COUNT

10、A用途用途：返回參數(shù)組中非空值的數(shù)目。語法語法：COUNTA(value1，value2，.)說明說明：參數(shù)可以是任何類型，它們包括空格但不包括空白單元格。如果不需要統(tǒng)計邏輯值、文字或錯誤值，則應(yīng)該使用COUNT函數(shù)。實例實例：如果A1=6.28、A2=3.74，A3=“我們”其余單元格為空，則公式“=COUNTA(A1:A5)”的計算結(jié)果等于3。5.COUNTBLANK5.COUNTBLANK用途：用途：計算某個單元格區(qū)域中空白單元格的數(shù)目。函數(shù)語法：函數(shù)語法：COUNTBLANK(range)參數(shù)：參數(shù)：Range為需要計算其中空白單元格數(shù)目的區(qū)域。實例：實例：如果A1=8888、A2=5

11、5、A3=(空格)、A4=72、A5=(空格)，則公式“=COUNTBLANK(A1:A5)”返回得2。6.COUNTIF6.COUNTIF用途：用途：計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法：語法：COUNTIF(range，criteria)參數(shù)：參數(shù)：Range：需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria：確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。實例：實例：在A1:A7之間，確定數(shù)字55被計算在內(nèi)的條件，返回得27.MAX7.MAX：返回選定數(shù)據(jù)庫項中的最大值返回選定數(shù)據(jù)庫項中的最大值8.MIN8.MIN：返回選定數(shù)據(jù)庫項中的最小值返回選定

12、數(shù)據(jù)庫項中的最小值4.2邏輯（條件）函數(shù)1 1、IFIF1) IF1) IF函數(shù)的語法結(jié)構(gòu)：函數(shù)的語法結(jié)構(gòu)：IF(IF(條件條件, ,結(jié)果結(jié)果1,1,結(jié)果結(jié)果2)2)。2)2) IFIF函數(shù)的功能函數(shù)的功能 對滿足條件的數(shù)據(jù)進行處理，條件滿足則輸出對滿足條件的數(shù)據(jù)進行處理，條件滿足則輸出結(jié)果結(jié)果1 1，不滿足則輸出結(jié)果，不滿足則輸出結(jié)果2 2。可以省略結(jié)果。可以省略結(jié)果1 1或結(jié)果或結(jié)果2 2，但不能同時省略。，但不能同時省略。3)3) 條件表達式條件表達式 把兩個表達式用關(guān)系運算符把兩個表達式用關(guān)系運算符( (主要有主要有= =， ， =，=等等6 6個關(guān)系運算符個關(guān)系運算符) )連接起來就

13、構(gòu)成條件表達式。連接起來就構(gòu)成條件表達式。 例如，在例如，在IF(a1+b1+50 b1+c1-50,IF(a1+b1+50 b1+c1-50,1,2)1,2)函數(shù)式中，函數(shù)式中，條件表達式是條件表達式是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í)行過程。 例例2 2：IF(E2=85,IF(E2=85,“優(yōu)優(yōu)”,IF(E2=75,IF(E2=75,“良良”，IF(E2

14、=60,IF(E2=60,及格及格 ， 不及格不及格)公式理解：公式理解：函數(shù)從左向右執(zhí)行。函數(shù)從左向右執(zhí)行。首先計算首先計算E2=85E2=85，如果該表達式成立，則顯示，如果該表達式成立，則顯示“優(yōu)優(yōu)”，如果不成立就繼續(xù)計算如果不成立就繼續(xù)計算E2=75E2=75，如果該表達式成立，則顯示，如果該表達式成立，則顯示“良良”，否則繼續(xù)計算否則繼續(xù)計算E2=60E2=60，如果該表達式成立，則顯示，如果該表達式成立，則顯示“及格及格”，否則顯，否則顯示示“不及格不及格”。2.AND2.AND1. 說明：所有參數(shù)的計算結(jié)果為 TRUE 時，返回 TRUE；只要有一個參數(shù)的計算結(jié)果為 FALSE，

15、即返回 FALSE。2. 函數(shù)語法：AND(logical1, logical2, .)logical1：必需。要檢驗的第一個條件，其計算結(jié)果可以為 TRUE 或 FALSE。logical2, . ：可選。要檢驗的其他條件，其計算結(jié)果可以為 TRUE或 FALSE，最多可包含 255 個條件。3. 實例： =AND(2+2=4, 2+3=5) 所有參數(shù)的計算結(jié)果均為TRUE =AND(2+2=4, 2+3=6) 所有參數(shù)的計算結(jié)果均為FALSE4.3查找和引用函數(shù)參數(shù)參數(shù)簡單說明簡單說明輸入數(shù)據(jù)類型輸入數(shù)據(jù)類型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查

16、找的區(qū)域數(shù)據(jù)表區(qū)域row_index_num返回數(shù)據(jù)在區(qū)域的第幾行數(shù)正整數(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)實例：用下面這個實例展示Hlookup函數(shù)用法A1:B2表示要在其中查找的區(qū)間，公式中的table_arrayF1到G1中表示我們要查找的對象，公式中

17、的lookup_value現(xiàn)在我們要查找F1到G1分別對應(yīng)的數(shù)值。（按F4鍵，將其變成絕對引用。）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

18、,range_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實例：用下面這個實例展示vlookup函數(shù)用法A1:B7表示要在其中查找的區(qū)間，公式中的table_arrayE1中表示我們要查找的對象，公式中的lookup_value現(xiàn)在我們要查找E1對應(yīng)的數(shù)值。（按（按F4F4鍵，將其變成絕對引用。）鍵，將其變成絕對引用。）4.4排位函數(shù)：RANK

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

20、PLACE(替換區(qū)域替換區(qū)域A2A2，第八個數(shù)字開始，替換，第八個數(shù)字開始，替換4 4個，替換為個，替換為“xxxxxxxx”4.6時間與日期函數(shù) 1.TODAY：用于返回當(dāng)前系統(tǒng)的日期，其完整的格式：=TODAY( ) 2.NOW：用于返回當(dāng)前系統(tǒng)的時間，其完整的格式：=DAY( )DATE函數(shù)：返回指定日期的序列號函數(shù)語法：DATE(year，month，day)4.7數(shù)據(jù)庫函數(shù)一、DAVERAGE1.說明：返回數(shù)據(jù)庫或數(shù)據(jù)清單中滿足指定條件的列中數(shù)值的平均值.2.函數(shù)語法：DAVERAGE(database,field,criteria) 參數(shù)：Database構(gòu)成數(shù)據(jù)表或數(shù)據(jù)庫的單元格區(qū)域。Field指定函數(shù)所使用的數(shù)據(jù)列。Criteria為一組包含給定條件的單元格區(qū)域。3.實例：實例1：利用daverage函數(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，表示品名為電視。二、DSUM1.1.說明：說明：將數(shù)據(jù)庫中符合條件的記錄的字段列中的數(shù)字的和。使用它可以對數(shù)據(jù)進行多條件累加，這種方