excel常用公式大全

1、EXCELEXCEL公式和函數(shù)公式和函數(shù)l公式的使用和公式的調(diào)整公式的使用和公式的調(diào)整l常用函數(shù)常用函數(shù)l工作表的格式化工作表的格式化公式公式ExcelExcel表格中不僅可以輸入數(shù)據(jù)，表格中不僅可以輸入數(shù)據(jù)，也可以使用公式對(duì)工作表中的數(shù)據(jù)也可以使用公式對(duì)工作表中的數(shù)據(jù)進(jìn)行各種數(shù)值計(jì)算和統(tǒng)計(jì)分析等進(jìn)行各種數(shù)值計(jì)算和統(tǒng)計(jì)分析等ExcelExcel的公式是由運(yùn)算符號(hào)，和參的公式是由運(yùn)算符號(hào)，和參與計(jì)算的運(yùn)算數(shù)組成的表達(dá)式與計(jì)算的運(yùn)算數(shù)組成的表達(dá)式公式中的運(yùn)算符l算術(shù)運(yùn)算符：算術(shù)運(yùn)算符：+ - + - * * / % / %l字符運(yùn)算符：字符運(yùn)算符：& & 連接符連接符l比較運(yùn)算符：

2、比較運(yùn)算符：= = = = l邏輯運(yùn)算符：邏輯運(yùn)算符：and or not and or not 以函數(shù)形式出現(xiàn)以函數(shù)形式出現(xiàn)l優(yōu)先級(jí)順序：優(yōu)先級(jí)順序：算術(shù)運(yùn)算符算術(shù)運(yùn)算符字符運(yùn)算符字符運(yùn)算符比較運(yùn)算符比較運(yùn)算符邏輯函數(shù)符邏輯函數(shù)符 ( (使用括號(hào)可確定運(yùn)算順序使用括號(hào)可確定運(yùn)算順序) ) 公式中的數(shù)據(jù)類型公式中的數(shù)據(jù)類型l輸入公式要注意輸入公式要注意公式中的運(yùn)算對(duì)象可以包括：數(shù)值和公式中的運(yùn)算對(duì)象可以包括：數(shù)值和字符、單元格地址、區(qū)域、函數(shù)等字符、單元格地址、區(qū)域、函數(shù)等, ,如：如：=A2+B2+C2=A2+B2+C2不要隨意包含空格不要隨意包含空格公式中的字符要用半角引號(hào)括起來(lái)公式中的字

3、符要用半角引號(hào)括起來(lái)公式中運(yùn)算符兩邊的數(shù)據(jù)類型要相同公式中運(yùn)算符兩邊的數(shù)據(jù)類型要相同 如：如：=ab+25 =ab+25 出錯(cuò)出錯(cuò) #VALUE#VALUE公式的輸入公式的輸入l公式的基本特性：公式的基本特性： 公式的輸入是以公式的輸入是以“ = ”= ”開始開始 公式公式計(jì)算結(jié)果計(jì)算結(jié)果顯示在單元格中顯示在單元格中 公式本身公式本身顯示在編輯欄中顯示在編輯欄中公式示例公式示例=1+2+6 =1+2+6 （數(shù)值計(jì)算）（數(shù)值計(jì)算）=A1+B2 =A1+B2 （引用單元格地址）（引用單元格地址） =SUM(A1:C4) =SUM(A1:C4) （公式計(jì)算）（公式計(jì)算）=IF( =IF( and(A

4、1A2,B1B2),and(A1A2,B1B2),100100* *A1/A2, 100A1/A2, 100* *B1/B2) B1/B2) （函數(shù)計(jì)算）（函數(shù)計(jì)算）=ABC&XYZ =ABC&XYZ ( (字符計(jì)算，結(jié)果字符計(jì)算，結(jié)果:ABCXYZ) :ABCXYZ) 公式中常用單元格的引用來(lái)代替單元格的具體數(shù)據(jù)，當(dāng)公式中公式中常用單元格的引用來(lái)代替單元格的具體數(shù)據(jù)，當(dāng)公式中被引用單元格的數(shù)據(jù)變化時(shí)，公式的計(jì)算結(jié)果會(huì)隨之變化被引用單元格的數(shù)據(jù)變化時(shí)，公式的計(jì)算結(jié)果會(huì)隨之變化公式的復(fù)制和移動(dòng)公式的復(fù)制和移動(dòng)l公式的復(fù)制：公式的復(fù)制：使用填充柄使用填充柄菜單菜單 編輯編輯 復(fù)制復(fù)

5、制/粘貼粘貼 復(fù)制公式時(shí)，公式會(huì)作相對(duì)調(diào)整復(fù)制公式時(shí)，公式會(huì)作相對(duì)調(diào)整相對(duì)引用和絕對(duì)引用相對(duì)引用和絕對(duì)引用相對(duì)引用是針對(duì)當(dāng)前單元格的引用，是隨當(dāng)前單元格變化而變化的絕對(duì)引用是針對(duì)起始單元格的引用，是不隨當(dāng)前單元格的變化而變化的公式的調(diào)整公式的調(diào)整l相對(duì)地址引用相對(duì)地址引用: : 在公式復(fù)制時(shí)將自動(dòng)調(diào)整在公式復(fù)制時(shí)將自動(dòng)調(diào)整u調(diào)整原則是：調(diào)整原則是：新行地址新行地址= =原來(lái)行地址原來(lái)行地址+ +行偏移量行偏移量 新列地址新列地址= =原來(lái)列地址原來(lái)列地址+ +列偏移量列偏移量u例如：將例如：將D2D2單元中的公式單元中的公式 =A2+B2+C2=A2+B2+C2u復(fù)制到復(fù)制到D4D4后，后，D

6、4D4中的公式中的公式 =A4+B4+C4=A4+B4+C4公式的調(diào)整公式的調(diào)整l絕對(duì)地址引用絕對(duì)地址引用 在公式復(fù)制時(shí)不變。在公式復(fù)制時(shí)不變。例如：例如：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$4l混合地址混合地址 在公式復(fù)制時(shí)絕對(duì)地址不變，相對(duì)在公式復(fù)制時(shí)絕對(duì)地址不變，相對(duì)地址按規(guī)則調(diào)整。地址按規(guī)則調(diào)整。公式的調(diào)整公式的調(diào)整l公式移動(dòng)公式移動(dòng) 相對(duì)和絕對(duì)地址都不調(diào)整相對(duì)和絕對(duì)地址都不調(diào)整C3C3單元格的公式單元格的公式=B2+B3

7、+B4=B2+B3+B4移動(dòng)到移動(dòng)到D5D5中，中，D3D3單元格的公式單元格的公式=B2+B3+B4=B2+B3+B4l被公式引用的單元格移動(dòng)，公式跟蹤單元格，值不變被公式引用的單元格移動(dòng)，公式跟蹤單元格，值不變選擇性粘貼選擇性粘貼u菜單菜單 編輯編輯復(fù)制復(fù)制 u菜單菜單 編輯編輯選擇性粘貼選擇性粘貼 u公式、數(shù)值、格式的粘貼公式、數(shù)值、格式的粘貼u運(yùn)算、轉(zhuǎn)置的使用運(yùn)算、轉(zhuǎn)置的使用Sumif函數(shù)函數(shù)根據(jù)指定條件對(duì)若干單元格求和。根據(jù)指定條件對(duì)若干單元格求和。 SUMIF(range,criteria,sum_range) Range 為用于條件判斷的單元格區(qū)域。為用于條件判斷的單元格區(qū)域。

8、Criteria 為確定哪些單元格將被相加求和的為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為例如，條件可以表示為 32、“32”、“32” 或或 “apples”。 Sum_range 為需要求和的實(shí)際單元格。為需要求和的實(shí)際單元格。只有當(dāng)只有當(dāng) Range 中的相應(yīng)單元格滿足條件時(shí)，中的相應(yīng)單元格滿足條件時(shí)，才對(duì)才對(duì) sum_range 中的單元格求和。如果中的單元格求和。如果省略省略 sum_range。則直接對(duì)。則直接對(duì) Range 中的中的單元格求和單元格求和 例如，假設(shè)A1:A4單元格中的內(nèi)容分別為四種

9、產(chǎn)品的屬性值：$100,000，$200,000，$300,000，$400,000。B1:B4 的內(nèi)容為每個(gè)屬性值所對(duì)應(yīng)的銷售傭金；$7,000，$14,000，$21,000，$28,000，那么就可以運(yùn)行公式：SUMIF(A1:A4,160,000,B1:B4) 最后的求和結(jié)果將是$63,000。函數(shù)函數(shù)COUNT用途：它可以統(tǒng)計(jì)數(shù)組或單元格區(qū)域中用途：它可以統(tǒng)計(jì)數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個(gè)數(shù)。含有數(shù)字的單元格個(gè)數(shù)。語(yǔ)法語(yǔ)法：COUNT(value1，value2，.)。參數(shù)參數(shù)：value1，value2，.是包含或引是包含或引用各種類型數(shù)據(jù)的參數(shù)用各種類型數(shù)據(jù)的參數(shù)(130個(gè)

10、個(gè))，其中只，其中只有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計(jì)有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計(jì)。實(shí)例：如果實(shí)例：如果A1=90、A2=人數(shù)、A3=、A4=54、A5=36，則公式“=COUNT(A1:A5)”返回3 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:A7)”的計(jì)算結(jié)果等于3。COUNTBLANK用途：用途：計(jì)算某個(gè)單元格區(qū)域中空白單元格

11、的數(shù)目。語(yǔ)法：語(yǔ)法：COUNTBLANK(range)參數(shù)：參數(shù)：Range為需要計(jì)算其中空白單元格數(shù)目的區(qū)域。實(shí)例：實(shí)例：如果A1=88、A2=55、A3=、A4=72、A5=，則公式“=COUNTBLANK(A1:A5)”返回2。COUNTIF用途：用途：計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。語(yǔ)法：語(yǔ)法：COUNTIF(range，criteria)參數(shù)：參數(shù)：Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本If函數(shù)函數(shù)1IF函數(shù)的語(yǔ)法結(jié)構(gòu)函數(shù)的語(yǔ)法結(jié)構(gòu)IF函數(shù)的語(yǔ)法結(jié)構(gòu)：函數(shù)的語(yǔ)法結(jié)構(gòu)：IF(條件

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

13、執(zhí)行過(guò)程下面以下面以IF(a1+b1+50 b1+c1-50,1,1)函數(shù)式為例來(lái)說(shuō)明函數(shù)式為例來(lái)說(shuō)明IF函數(shù)的執(zhí)行過(guò)程。函數(shù)的執(zhí)行過(guò)程。先計(jì)算條件表達(dá)式先計(jì)算條件表達(dá)式a1+b1+50 b1+c1-50，如果表達(dá)式成立，值為，如果表達(dá)式成立，值為TRUE，并在，并在函數(shù)所在單元格中顯示函數(shù)所在單元格中顯示“1”；如果表達(dá)；如果表達(dá)式不成立，值為式不成立，值為FALSE，并在函數(shù)所在單，并在函數(shù)所在單元格中顯示元格中顯示“2”。IF(E2=85,“優(yōu)”,IF(E2=75,“良”，IF(E2=60,及格，不及格)函數(shù)從左向右執(zhí)行。首先計(jì)算函數(shù)從左向右執(zhí)行。首先計(jì)算E2=85，如果該表達(dá)式成立，則

14、顯示如果該表達(dá)式成立，則顯示“優(yōu)優(yōu)”，如果不，如果不成立就繼續(xù)計(jì)算成立就繼續(xù)計(jì)算E2=75，如果該表達(dá)式，如果該表達(dá)式成立，則顯示成立，則顯示“良良”，否則繼續(xù)計(jì)算，否則繼續(xù)計(jì)算E2=60，如果該表達(dá)式成立，則顯示，如果該表達(dá)式成立，則顯示“及格及格”，否則顯示，否則顯示“不及格不及格”。數(shù)據(jù)排序數(shù)據(jù)排序 使用工具欄上的排序按鈕進(jìn)行排序 單擊數(shù)據(jù)清單需要排序的列中任一單元格； 單擊“常用工具欄”上的升序或降序按鈕.使用“數(shù)據(jù)”菜單中的“排序”命令進(jìn)行排序 1、排序 單擊數(shù)據(jù)清單任一單元格； 單擊數(shù)據(jù)-排序 選擇主要關(guān)鍵字、次要關(guān)鍵字； 選擇升序還是降序，確定。數(shù)據(jù)篩選數(shù)據(jù)篩選 任選一個(gè)單元格數(shù)

15、據(jù)-篩選-自動(dòng)篩選分類匯總分類匯總 分類匯總分類匯總:就是把某一個(gè)關(guān)鍵字段的相同數(shù)就是把某一個(gè)關(guān)鍵字段的相同數(shù)據(jù)匯總在一起。據(jù)匯總在一起。 使用分類匯總命令之前，首先要對(duì)關(guān)使用分類匯總命令之前，首先要對(duì)關(guān)鍵字段進(jìn)行排序，使關(guān)鍵字段相同的數(shù)據(jù)記鍵字段進(jìn)行排序，使關(guān)鍵字段相同的數(shù)據(jù)記錄可以連續(xù)訪問。錄可以連續(xù)訪問。操作步驟：先對(duì)分類字段進(jìn)行排序打開“數(shù)據(jù)”下拉菜單，再單擊“分類匯總”命令在“分類匯總”對(duì)話框中選擇分類的字段（與排序字段相同），匯總的方式，匯總的項(xiàng)（統(tǒng)計(jì)的數(shù)值字段）清除分類匯總清除分類匯總“編輯編輯“里的里的“撤消分類匯總撤消分類匯總”命令（若命令（若已按了已按了1、2或或3分級(jí)顯示

16、，則需多次按分級(jí)顯示，則需多次按“撤消顯示分級(jí)層次撤消顯示分級(jí)層次”至到出現(xiàn)至到出現(xiàn)“撤消分撤消分類匯總類匯總”為止），為止），方法二：在方法二：在“分類匯總分類匯總”對(duì)話框中按對(duì)話框中按“全全部刪除部刪除”按鈕。按鈕。 多工作表或工作簿的操作多工作表或工作簿的操作l同一工作簿中的某一個(gè)公式中引用不同工作同一工作簿中的某一個(gè)公式中引用不同工作表中的數(shù)據(jù)，要引用的單元格地址前加表名表中的數(shù)據(jù)，要引用的單元格地址前加表名字和感嘆號(hào)字和感嘆號(hào)！ 如：如：= =sheet3!sheet3!E3+E3+sheet3!sheet3!F3F3l一個(gè)公式中引用不同工作簿中的數(shù)據(jù)，要在一個(gè)公式中引用不同工作簿中

17、的數(shù)據(jù)，要在引用的單元格地址前加工作簿的名字，并用引用的單元格地址前加工作簿的名字，并用方括號(hào)括起來(lái)方括號(hào)括起來(lái) 如：如：=cjb.xlssheet3!E3+cjb.xlssheet3!F3=cjb.xlssheet3!E3+cjb.xlssheet3!F3工作表的格式化工作表的格式化l單元格格式的設(shè)置單元格格式的設(shè)置: : 選定單元格或區(qū)域選定單元格或區(qū)域u選擇菜單選擇菜單 格式格式 單元格單元格 u設(shè)置數(shù)字格式：兩位小數(shù)、千位分隔設(shè)置數(shù)字格式：兩位小數(shù)、千位分隔u字體、對(duì)齊方式、邊框底紋等，字體、對(duì)齊方式、邊框底紋等，EXCELEXCEL表格要加表格要加邊框線，打印的時(shí)候才有邊線邊框線，打

18、印的時(shí)候才有邊線u合并及居中合并及居中u選擇菜單選擇菜單 格式格式 條件格式條件格式 設(shè)置帶條件的格式，設(shè)置帶條件的格式，如：小于如：小于6060分的成績(jī)顯示紅色粗體，背景為蘭色分的成績(jī)顯示紅色粗體，背景為蘭色u使用使用 格式刷格式刷 或或 選擇性粘貼選擇性粘貼 復(fù)制格式復(fù)制格式工作表的格式化工作表的格式化l調(diào)整行高和列寬調(diào)整行高和列寬鼠標(biāo)拖拽操作鼠標(biāo)拖拽操作菜單操作：菜單操作： 格式格式 行行/列列 隱藏行或列：隱藏行或列： 格式格式 行行/列列 隱藏隱藏 工作表的格式化l工作表的整體顯示工作表的整體顯示工作表的背景設(shè)置工作表的背景設(shè)置 菜單菜單 格式格式 工作表工作表背景背景 工作表的整體設(shè)置工作表的整體設(shè)置 菜單菜單 工具工具 選項(xiàng)選項(xiàng) 的的 視圖視圖 ，工，工作表的網(wǎng)格線的設(shè)置作表的網(wǎng)格線的設(shè)置l自動(dòng)套用格式自動(dòng)套用格式u菜單菜單