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

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

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

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

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

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

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

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

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

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

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

1,1)函數(shù)式為例來(lái)說(shuō)明IF函數(shù)的執(zhí)行過(guò)程。2021/6/2820

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

1.

說(shuō)明：所有參數(shù)的計(jì)算結(jié)果為TRUE時(shí)，返回TRUE；只要有一個(gè)參數(shù)的計(jì)算結(jié)果為FALSE，即返回FALSE。

2.

函數(shù)語(yǔ)法：AND(logical1,[logical2],...)

logical1：必需。要檢驗(yàn)的第一個(gè)條件，其計(jì)算結(jié)果可以為TRUE或FALSE。

logical2,...：可選。要檢驗(yàn)的其他條件，其計(jì)算結(jié)果可以為TRUE或

FALSE，最多可包含255個(gè)條件。

3.實(shí)例：

=AND(2+2=4,2+3=5)

所有參數(shù)的計(jì)算結(jié)果均為TRUE

=AND(2+2=4,2+3=6)

所有參數(shù)的計(jì)算結(jié)果均為FALSE

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

說(shuō)明：

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

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

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