excel常用公式大全

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

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

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

4、A2,B1B2),and(A1A2,B1B2),100100* *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é)果會隨之變化公式的復(fù)制和移動公式的復(fù)制和移動l公式的復(fù)制：公式的復(fù)制：使用填充柄使用填充柄菜單菜單 編輯編輯 復(fù)制復(fù)制

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

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

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

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

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

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

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

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

13、行過程下面以下面以IF(a1+b1+50 b1+c1-50,1,1)函數(shù)式為例來說明函數(shù)式為例來說明IF函數(shù)的執(zhí)行過程。函數(shù)的執(zhí)行過程。先計算條件表達(dá)式先計算條件表達(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í)行。首先計算函數(shù)從左向右執(zhí)行。首先計算E2=85，如果該表達(dá)式成立，則顯

14、示如果該表達(dá)式成立，則顯示“優(yōu)優(yōu)”，如果，如果不成立就繼續(xù)計算不成立就繼續(xù)計算E2=75，如果該表達(dá)，如果該表達(dá)式成立，則顯示式成立，則顯示“良良”，否則繼續(xù)計算，否則繼續(xù)計算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ù)篩選 任選一個單元格數(shù)據(jù)

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

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

17、數(shù)據(jù)，要在引用的單元格地址前加工作簿的名字，并用引用的單元格地址前加工作簿的名字，并用方括號括起來方括號括起來 如：如：=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字體、對齊方式、邊框底紋等，字體、對齊方式、邊框底紋等，EXCELEXCEL表格要加表格要加邊框線，打印的時候才有邊線邊框線，打印

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