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

EXCEL公式和函數(shù)公式的輸入公式的調(diào)整2021/6/281Excel表格中不僅可以輸入數(shù)據(jù)，也可以使用公式對工作表中的數(shù)據(jù)進(jìn)行各種數(shù)值計算和統(tǒng)計分析等Excel的公式是由運算符號和參與計算的運算數(shù)組成的表達(dá)式1.1公式2021/6/2821.2公式中的運算符算術(shù)運算符：+-*/^%字符運算符：&連接符比較運算符：=<><=>=<>邏輯運算符：andornot以函數(shù)形式出現(xiàn)優(yōu)先級順序：算術(shù)運算符→字符運算符→比較運算符→邏輯函數(shù)符(使用括號可確定運算順序)

2021/6/2831.3公式中的數(shù)據(jù)類型輸入公式要注意公式中的運算對象可以包括：數(shù)值和字符、單元格地址、區(qū)域、函數(shù)等,如：=A2+B2+C2不要隨意包含空格公式中的字符要用半角引號括起來公式中運算符兩邊的數(shù)據(jù)類型要相同如：="ab"+25出錯#VALUE2021/6/2842.1公式的輸入公式的基本特性：公式的輸入是以“

=”開始公式計算結(jié)果顯示在單元格中公式本身顯示在編輯欄中2021/6/2852.2公式示例=1+2+6（數(shù)值計算）=A1+B2（引用單元格地址）=SUM(A1:C4)（公式計算）=IF(and(A1>A2,B1>B2),100*A1/A2,100*B1/B2)（函數(shù)計算）="ABC"&"XYZ"(字符計算，結(jié)果:ABCXYZ)公式中常用單元格的引用來代替單元格的具體數(shù)據(jù)，當(dāng)公式中被引用單元格的數(shù)據(jù)變化時，公式的計算結(jié)果會隨之變化2021/6/2862.3公式的復(fù)制使用填充柄菜單[編輯]→[復(fù)制]/[粘貼]

復(fù)制公式時，公式會作相對調(diào)整2021/6/2873.1公式的調(diào)整相對引用是針對當(dāng)前單元格的引用，是隨當(dāng)前單元格變化而變化的。絕對引用是針對起始單元格的引用，是不隨當(dāng)前單元格的變化而變化的。2021/6/288公式的調(diào)整相對地址引用:在公式復(fù)制時將自動調(diào)整例如：將D2單元中的公式“=A2+B2+C2“復(fù)制到D4后，D4中的公式

”=A4+B4+C4”2021/6/289公式的調(diào)整絕對地址引用：公式復(fù)制時不變。例如：C3單元的公式=$B$2+$B$3+$B$4復(fù)制到D5中，D5單元的公式=$B$2+$B$3+$B$4(在公式復(fù)制時絕對地址不變，相對地址按規(guī)則調(diào)整。)2021/6/28103.2公式的移動公式移動：相對和絕對地址都不調(diào)整C3單元格的公式=B2+B3+B4移動到D5中，D3單元格的公式=B2+B3+B4(被公式引用的單元格移動，公式跟蹤單元格，值不變)2021/6/28114.1常用函數(shù)2021/6/28124.1統(tǒng)計函數(shù)sum是求參數(shù)的和，就是求指定的所有參數(shù)的和。sumif是條件求和，Excel中sumif函數(shù)的用法是根據(jù)指定條件對若干單元格、區(qū)域或引用求和。

sumif函數(shù)語法是：SUMIF(range，criteria，sum_range)

sumif函數(shù)的參數(shù)如下：第一個參數(shù)：Range為條件區(qū)域，用于條件判斷的單元格區(qū)域。第二個參數(shù)：Criteria是求和條件，由數(shù)字、邏輯表達(dá)式等組成的判定條件。第三個參數(shù)：Sum_range為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實際求和區(qū)域。注：criteria參數(shù)中使用通配符（包括問號(?)和星號(*)）。問號匹配任意單個字符；星號匹配任意一串字符。如果要查找實際的問號或星號，請在該字符前鍵入波形符(~)。2021/6/28133.COUNT用途：它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)。函數(shù)語法：COUNT(value1，value2，...)。參數(shù)：value1，value2，...是包含或引用各種類型數(shù)據(jù)的參數(shù)(1～30個)，其中只有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計。實例：如果A1=90、A2=人數(shù)、A3=〞〞、A4=54、A5=36，則公式“=COUNT(A1:A5)

”返回得32021/6/28144.COUNTA用途：返回參數(shù)組中非空值的數(shù)目。語法：COUNTA(value1，value2，...)說明：參數(shù)可以是任何類型，它們包括空格但不包括空白單元格。如果不需要統(tǒng)計邏輯值、文字或錯誤值，則應(yīng)該使用COUNT函數(shù)。實例：如果A1=6.28、A2=3.74，A3=“我們”其余單元格為空，則公式“=COUNTA(A1:A5)”的計算結(jié)果等于3。2021/6/28155.COUNTBLANK用途：計算某個單元格區(qū)域中空白單元格的數(shù)目。函數(shù)語法：COUNTBLANK(range)參數(shù)：Range為需要計算其中空白單元格數(shù)目的區(qū)域。實例：如果A1=88、A2=55、A3=(空格)、A4=72、A5=(空格)，則公式“=COUNTBLANK(A1:A5)”返回得2。2021/6/28166.COUNTIF用途：計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法：COUNTIF(range，criteria)參數(shù)：Range：需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria：確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。實例：在A1:A7之間，確定數(shù)字55被計算在內(nèi)的條件，返回得22021/6/28177.MAX：返回選定數(shù)據(jù)庫項中的最大值8.MIN：返回選定數(shù)據(jù)庫項中的最小值2021/6/28184.2邏輯（條件）函數(shù)1、IF1)IF函數(shù)的語法結(jié)構(gòu)：IF(條件,結(jié)果1,結(jié)果2)。2)

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

1,2)函數(shù)式中，條件表達(dá)式是a1+b1+50<>b1+c1-50。4)執(zhí)行過程下面以IF(a1+b1+50<>b1+c1-50,

1,1)函數(shù)式為例來說明IF函數(shù)的執(zhí)行過程。2021/6/2820

2021/6/2821例2：IF(E2>=85,“優(yōu)”,IF(E2>=75,“良”，IF(E2>=60,"及格"，"不及格")))公式理解：函數(shù)從左向右執(zhí)行。首先計算E2>=85，如果該表達(dá)式成立，則顯示“優(yōu)”，如果不成立就繼續(xù)計算E2>=75，如果該表達(dá)式成立，則顯示“良”，否則繼續(xù)計算E2>=60，如果該表達(dá)式成立，則顯示“及格”，否則顯示“不及格”。2021/6/28222.AND

1.

說明：所有參數(shù)的計算結(jié)果為TRUE時，返回TRUE；只要有一個參數(shù)的計算結(jié)果為FALSE，即返回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é)果均為FALSE

2021/6/28234.3查找和引用函數(shù)參數(shù)簡單說明輸入數(shù)據(jù)類型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域row_index_num返回數(shù)據(jù)在區(qū)域的第幾行數(shù)正整數(shù)range_lookup模糊匹配/精確匹配TRUE/FALSE（或不填）1、HLOOKUP：說明：HLOOKUP函數(shù)主要用于在表格或數(shù)值組的首行查找指定的數(shù)值（即在水平查找），并返回表格顯示當(dāng)前列中指定行處的值。。函數(shù)語法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)2021/6/2824實例：用下面這個實例展示Hlookup函數(shù)用法A1:B2表示要在其中查找的區(qū)間，公式中的table_arrayF1到G1中表示我們要查找的對象，公式中的lookup_value現(xiàn)在我們要查找F1到G1分別對應(yīng)的數(shù)值。（按F4鍵，將其變成絕對引用。）2021/6/28252、VLOOKUP

說明：

VLOOKUP函數(shù)是Excel中的一個縱向查找函數(shù)，它與LOOKUP函數(shù)和HLOOKUP函數(shù)屬于一類函數(shù)，在工作中都有廣泛應(yīng)用。功能是按列查找，最終返回該列所需查詢列序所對應(yīng)的值；與之對應(yīng)的HLOOKUP是按行查找的。函數(shù)語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)參數(shù)簡單說明輸入數(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ù)值。（按F4鍵，將其變成絕對引用。）2021/6/28264.4排位函數(shù)：RANK說明：rank函數(shù)是排名函數(shù)。rank函數(shù)最常用的是求某一個數(shù)值在某一區(qū)域內(nèi)的排名。函數(shù)語法：rank(number,ref,[order])實例：給下列成績排名2021/6/28274.5文本函數(shù)：REPLACE1.說明：用新字符串替換舊字符串，而且替換的位置和數(shù)量都是指定的。2.函數(shù)語法：=Replace(old_text,start_num,num_chars，new_text)=replace（要替換的字符串，開始位置，替換個數(shù)，新的文本）注意：第四個參數(shù)是文本，要加上引號。3.實例：=REPLACE(替換區(qū)域A2，第八個數(shù)字開始，替換4個，替換為“xxxx”2021/6/28284.6時間與日期函數(shù)1.TODAY：用于返回當(dāng)前系統(tǒng)的日期，其完整的格式：=TODAY()2.NOW：用于返回當(dāng)前系統(tǒng)的時間，其完整的格式：=DAY()DATE函數(shù)：返回指定日期的序列號函數(shù)語法：DATE(year，month，day)2021/6/28294.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ū)域——A1:C9（整個查找表）字段名——C1，指定求平均值的數(shù)據(jù)列。也可以等于3（參數(shù)Field=C1等價于參數(shù)Field=3。即表示返回第三列的值。)條件區(qū)域——E1:E2，表示品名為電視。2021/6/2830二、DSUM1.說明：將數(shù)據(jù)庫中符合條件的記錄的字段列中的數(shù)字的和。使用它可以對數(shù)據(jù)進(jìn)行多條件累加，這種方式可以使條件的修改變得方便，因此，相對于SUM和SUMIF函數(shù)，DSUM更加靈活。2.函數(shù)語法：DSUM(database,field,criteria)參數(shù)：Database

構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field

指定函數(shù)所使用的數(shù)據(jù)列。列表中的數(shù)據(jù)