第7章Excel公式與函數(shù)

Excel公式與函數(shù)Excel公式名稱的定義與引用Excel函數(shù)1主要內(nèi)容21、公式的輸入與編輯公式的輸入Excel中輸入公式時，首先單擊存放計算結(jié)果的單元格，然后先輸入等號“=”，向系統(tǒng)表示正在輸入公式，接著輸入表示公式的表達式，最后按Enter鍵完成輸入。例如：在A3單元格輸入A1和A2單元格數(shù)據(jù)求和的計算結(jié)果，則在A3單元格中輸入公式“=A1+A2”。注意：在公式中輸入的運算符都必須是西文的半角字符。公式的編輯（1）公式的修改。（2）公式的刪除。Excel公式32、公式的復制與填充公式的復制首先單擊公式所在單元格，然后單擊鼠標右鍵，在快捷菜單中選擇“復制”命令，接著右擊目標單元格，在快捷菜單中選擇“粘貼”命令，完成公式的復制。公式的填充首先單擊公式所在單元格，然后通過拖動單元格的填充柄進行公式的填充。注意：公式的復制與填充，復制與填充的都不是單元格數(shù)據(jù)本身，而是單元格的公式。默認情況下，公式復制或填充時對單元格的引用采用的是相對引用。Excel公式43、單元格引用單元格引用是指在公式中引用指定工作表中的單元格或單元格區(qū)域。相對引用相對引用與包含公式的單元格位置相關(guān)，引用的單元格地址是相對于公式所在單元格的相對位置，不是固定不變的地址。默認情況下，公式中單元格都是使用相對引用。單元格相對引用時，以“列標行號”表示，例如：A3、B1:B2等。公式在填充復制時，公式中相對引用的單元格地址會自動調(diào)整，例如：在A3單元格中輸入公式“=A1+A2”，并使用填充柄向右填充復制公式，B3單元格中顯示的公式就變?yōu)榱恕?B1+B2”。Excel公式5絕對引用絕對引用與包含公式的單元格位置無關(guān)，引用的單元格地址是固定不變的地址。公式在填充復制時，如果希望引用的單元格地址保持不變，那么這時就需要使用絕對引用。單元格絕對引用時，單元格的列標與行號前都必須加上“$”符號，以“$列標$行號”表示，例如：$A$3、$B$1:$B$2等。公式在填充復制時，公式中絕對引用的單元格地址保持不變，例如：在A3單元格中輸入公式“=$A$1+$A$2”，并使用填充柄向右填充復制公式，B3單元格中顯示的公式仍然是“=$A$1+$A$2”。混合引用混合引用是指在單元格引用時希望單元格的行保持不變而列相對變化或者單元格的列保持不變而行相對變化。當單元格的行不變而列變化時，以“列標$行號”表示，例如：A$3；當單元格的列不變而行變化時，以“$列標行號”表示，例如：$A3。Excel公式6跨工作表引用跨工作表引用是指引用其他工作表的單元格數(shù)據(jù)，以“工作表名！單元格地址”表示，例如：Sheet3！A3表示引用Sheet3工作表的A3單元格數(shù)據(jù)?？绻ぷ鞑疽每绻ぷ鞑疽檬侵敢闷渌ぷ鞑局泄ぷ鞅淼膯卧駭?shù)據(jù)，以“[工作簿名]工作表名！單元格地址”表示，例如：[工作簿1]Sheet3！A3表示引用工作簿1中Sheet3工作表的A3單元格數(shù)據(jù)。Excel公式7Excel中名稱是一種特殊的簡略表示法，名稱可以表示單元格、單元格區(qū)域、函數(shù)、常量等。Excel中如果需要反復引用某個單元格區(qū)域，每次引用都重新輸入單元格區(qū)域，操作過程煩瑣且容易出錯，這時候可以通過創(chuàng)建名稱的方式來提高引用的效率。1、名稱的定義名稱的語法規(guī)則（1）唯一性：名稱在其適用范圍內(nèi)必須始終唯一。（2）有效字符：名稱必須以字母、下劃線“-”、或反斜杠“\”

作為第一個字符，其余字符可以是數(shù)字、字母、句點“.”和下劃線。注意：大小寫字母“C”、“c”、“R”、“r”不能使用。（3）空格無效：名稱中不能使用空格。（4）單元格地址不能作為名稱。例如：名稱不能是A3、B$2、$C3等。（5）名稱長度：一個名稱最多不能超過255個西文字符。（6）不區(qū)分大小寫：名稱中可以出現(xiàn)大、小寫字母，但同一個字母的大小寫會被認為是同一個字符。例如：定義了名稱Class，如果在同一工作簿中再定義名稱class、CLASS，則均會被認定為與Class重名而不被允許。名稱的定義與引用8名稱的定義名稱必須先定義后使用。方法一：通過“名稱框”定義名稱。首先選擇需要命名的單元格或單元格區(qū)域，然后單擊編輯欄左側(cè)的“名稱框”并輸入一個名稱，最后按Enter鍵創(chuàng)建名稱。方法二：通過“新建名稱”對話框定義名稱。首先選擇需要命名的單元格區(qū)域，然后單擊“公式”選項卡—>“定義的名稱”組—>“定義名稱”按鈕，彈出“新建名稱”對話框，如圖所示，根據(jù)需要進行相應設(shè)置即可。名稱的定義與引用92、名稱的引用通過“名稱框”引用首先單擊“名稱框”右側(cè)的箭頭，打開下拉列表，列表中將顯示所有已經(jīng)被命名過的單元格或單元格區(qū)域名稱，然后單擊選擇名稱，名稱對應的單元格或單元格區(qū)域即被選中。名稱的定義與引用通過公式和函數(shù)引用首選單擊公式所在的單元格，然后單擊“公式”選項卡—>“定義的名稱”組—>“用于公式”按鈕，接著在下拉列表中選擇需要引用的名稱，引用的名稱在當前單元格的公式中顯示，最后按Enter鍵。103、名稱的編輯和刪除名稱的編輯首先單擊“公式”選項卡—>“定義的名稱”組—>“名稱管理器”按鈕，彈出“名稱管理器”對話框，如圖所示，然后選擇需要修改的名稱，單擊“編輯”按鈕，彈出“編輯名稱”對話框，如圖所示，對名稱、備注、引用位置等項進行修改即可。名稱的刪除先打開“名稱管理器”對話框，然后選擇需要刪除的名稱，單擊“刪除”按鈕即可。名稱的定義與引用11Excel函數(shù)是為了滿足更復雜的計算需求而預置的算法，主要用于處理簡單四則運算無法解決的計算問題。Excel中的函數(shù)是Excel預先編輯好的公式，用戶可以不需定義直接使用。1、函數(shù)的分類Excel中預置了大量不同類型的函數(shù)，為用戶在日常生活和工作中進行數(shù)據(jù)計算提供了強有力的支持。根據(jù)功能的不同，Excel2016的函數(shù)主要分為財務函數(shù)、數(shù)學與三角函數(shù)、日期與時間函數(shù)、統(tǒng)計函數(shù)、查找與引用函數(shù)、數(shù)據(jù)庫函數(shù)、文本函數(shù)等。Excel函數(shù)122、函數(shù)的輸入與編輯函數(shù)的輸入（1）通過單元格直接輸入與輸入公式類似，輸入函數(shù)可以在單元格中直接輸入“=函數(shù)名（參數(shù)列表）”，在輸入的過程中Excel會提示輸入的參數(shù)，如右上圖所示。（2）通過“插入函數(shù)”按鈕插入。首先選擇需要輸入函數(shù)的單元格，然后單擊“公式”選項卡—>“函數(shù)庫”組—>“插入函數(shù)”按鈕或者單擊編輯欄左側(cè)的“插入函數(shù)”按鈕

，彈出“插入函數(shù)”對話框，如右下圖所示，接著選擇需要的函數(shù)，單擊“確定”按鈕，最后在彈出相應函數(shù)的“函數(shù)參數(shù)”對話框，設(shè)置函數(shù)參數(shù)。Excel函數(shù)132、函數(shù)的輸入與編輯函數(shù)的輸入（3）通過“函數(shù)庫”組插入首選選擇需要輸入函數(shù)的單元格，然后單擊“公式”選項卡—>“函數(shù)庫”組—>相應函數(shù)類別按鈕下方的箭頭，彈出該類型的下拉列表，如圖所示，接著在打開的函數(shù)列表中選擇所需的函數(shù)，彈出相應函數(shù)的“函數(shù)參數(shù)”對話框，如圖所示（以LEFT函數(shù)為例），最后在“函數(shù)參數(shù)”對話框中設(shè)置函數(shù)參數(shù)。Excel函數(shù)142、函數(shù)的輸入與編輯函數(shù)的編輯方法一：首先雙擊函數(shù)所在單元格，進入編輯狀態(tài)，然后通過單元格對函數(shù)及其參數(shù)進行修改，最后按Enter鍵。方法二：首先選擇函數(shù)所在單元格，然后單擊編輯欄，在編輯欄中對函數(shù)及其參數(shù)進行修改，最后單擊編輯欄左側(cè)的按鈕

。Excel函數(shù)153、常用函數(shù)條件求和函數(shù)SUMIF格式：SUMIF(range,criteria,[sum_range])功能：返回指定單元格區(qū)域中滿足給定條件的所有數(shù)值的和。例：在如圖所示工作表中的F2單元格計算交通費的總額。Excel函數(shù)16多條件求和函數(shù)SUMIFS格式：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：返回指定區(qū)域內(nèi)滿足一組給定條件的所有單元格的和。例：在如圖所示工作表中的F5單元格計算田華的交通費總額。Excel函數(shù)17條件判斷函數(shù)IF格式：IF(logical_test,[value_if_true],[value_if_false])功能：判斷指定條件是否為TRUE，如果為TRUE，函數(shù)則將返回一個值；如果為FALSE，函數(shù)將返回另一個值。例：如圖所示的工作表中，如果語文成績大等于90，則“等級”顯示“優(yōu)秀”，如果語文成績大等于80且小于90，顯示“良好”，如果語文成績小于80，顯示“及格”。Excel函數(shù)18垂直查詢函數(shù)VLOOKUP格式：VLOOKUP(