1、 二、公式與函數的應用及案例1、Excel2003的數據錄入技巧；2、自定義數字格式；3、Excel2003條件格式設置；4、數據有效性；5、EXCEL多工作表鏈接、凍結與并排；6、EXCEL工作表的數據保護功能；說明性，解釋性的數據描述，非數值類型的。如， 員工編號、姓名、出生年月、手機號碼常常用于各種數學計算的數據。如：工資、學生成績、銷售額等 是以“=”號開頭，它可以是簡單的數學式，也可以是包含各種Excel函數的式子數值文本公式數據錄入技巧 靈活使用TAB、ALT、ENTER鍵 錄入分數：零+空格+分數 錄入文本格式數字（郵編、電話等） ：單引號+錄入內容 輸入當前日期、時間：Ctrl

2、+; 和 Ctrl+Shift+; 錄入特殊字符（對號、平方）; 不連續區域的填充：Ctrl+Enter 填充柄錄入（序列填充） 插入批注實例01 數據錄入（1）在數字后面自動添加單位（如：元、千克等） 將文本字符括在雙引號將文本字符括在雙引號 ( ) ( ) 內或在單個字符前面添加一個反斜杠內或在單個字符前面添加一個反斜杠 ()()（2）輸入身份證的前幾位 5110251951102519（3）輸入條形碼前的某些重復數字 “999999999”“999999999”（4）定義小數點對齊、分數對齊 ?.? # ?/?.? # ?/?（5）自定義各種特殊日期格式 mmmm d, yyyy (dd

3、dd)mmmm d, yyyy (dddd) December 30,2008 (Tuesday) December 30,2008 (Tuesday) yyyy“yyyy“年年”m“”m“月月”d“”d“日日”aaaa”aaaa 2008 2008年年1212月月3030日星期四日星期四 實例02 自定義數字格式1、EXCEL表格的排版技巧；2、EXCEL條件格式v 文字的對齊與旋轉 合并單元格 使用工具欄上的快捷按鈕 格式單元格對齊合并單元格 自動換行 格式單元格對齊自動換行 強制換行 ALT+ENTER 文字旋轉 格式單元格對齊文字方向v 設定邊框和底紋n使用工具欄上的“邊框”“底紋”快

4、捷按鈕n格式單元格邊框圖案實例03 對齊技巧與邊框底紋什么是條件格式當單元格的值滿足預先設定的條件時，自動將單元格設置成預先設定的格式多條件格式的規則按從上到下的順序判斷條件并執行最多指定三個條件根據公式自動設置格式在條件格式中使用公式和函數實現隔行變色等的特殊格式掌握掌握ROW和和MOD函數的用法函數的用法 = =mod(row(),2)實例04 條件格式提高數據錄入準確性提高數據錄入準確性-有效性檢查。什么是數據的有效性？有效性檢查。什么是數據的有效性？ 數據有效性是一個包含幫助你在工作表中輸入資料提示信息的工具. 它有如下功能： (1) (1) 給用戶提供一個選擇列表給用戶提供一個選擇列

5、表 (2) (2) 限定輸入內容的類型或大小限定輸入內容的類型或大小 (3) (3) 自定義設置自定義設置 數據有效性的設置 提供下拉式菜單的輸入提供下拉式菜單的輸入 設置輸入提示設置輸入提示 設置出錯警告設置出錯警告 自定義有效性條件自定義有效性條件實例05 數據有效性在Excel多個工作表間快速切換 技巧一：用快捷鍵快速導航工作表 Ctrl+Page UpCtrl+Page Up 移到上一個工作表移到上一個工作表 Ctrl+Page Down Ctrl+Page Down 移到下一個工作表移到下一個工作表 技巧二：右鍵菜單準確定位工作表 右擊導航狀態按鈕選擇所需工作表標簽名右擊導航狀態按鈕

6、選擇所需工作表標簽名凍結工作表窗口大型工作表數據的分組、分級顯示命名單元格或單元格區域，以便能夠快速返回到這些工作表區域組合工作表，以便同時在多張工作表中輸入共用數據實例06 凍結窗格并排比較數據 兩個工作簿將在同一個窗口中打開。當一個工作簿滾動時，Excel 將使第二個工作簿自動滾動相應數量的單元格 工作表間的數據鏈接 創建引導型頁面 創建鏈接按鈕 創建超級鏈接及編輯超級鏈接 設置工作表及行號列標的隱藏同時比較兩個工作簿可能很困難同時比較兩個工作簿可能很困難實例07-1 并排比較數據1實例07-2 并排比較數據2保護工作表、工作簿（1）為工作簿加密碼（2）為工作表加密碼（3）設置不同區域不同

7、密碼（4）設置只有部分單元格可編輯公式的基本用法2.1函數分類與常用函數應用舉例2.2雙擊填充柄復制公式區別公式中各種單元格引用方式 絕對引用、相對引用、混合引用、三維引用跨工作表或工作簿的數據計算 不同工作表間數據的計算、不同工作簿間數據的計算自動求和與自動計算功能的使用實例08 使用相對和絕對引用計算產品銷售額與利潤實例9-1 跨工作表數據計算實例9-2 跨工作簿數據計算 函數：是預定義的公式，通過使用一些稱為參數的特定值來安特定的順序和結構執行計算函數的使用方式=函數名稱（參數1，參數2，參數3)函數的嵌套特性 一個函數可以引用其他函數的結果作為參數 Excel最多支持七層嵌套函數邏輯文

8、本類函數應用日期時間函數應用查詢引用類函數應用統計計數類函數應用財務函數應用實例10 函數綜合實例IFANDORLENMID LEFT RIGHT判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值語法：語法：IF(logical_test,value_if_true,value_if_false)條件為真時條件為真時條件為假時條件為假時AND函數的作用：是對多個邏輯值進行交集計算，返回邏輯值語法：AND(logical1,logical2)OR函數的作用：是對多個邏輯值進行并集計算，返回邏輯值語法：OR(logical1,logical2,)MIDB函數作用自文字指定位置開始向

9、右提取指定長度的字符串，操作對象均為文本，MID以字符為單位，MIDB以字節為單位截取語法：MID (text,start_num,num_bytes)LEFT返回文本字符串中的第一個或前幾個字符，RIGHT返回文本字符串中的最后一個或后幾年字符語法：LEFT(text,num_chars)語法：RIGHT(text,num_chars)DATE：返回代表特定日期的序列號 語法：DATE(year,month,day)NOW：返回計算機內部時鐘的當前日期和時間 語法：NOW( )TODAY：返回當前日期的序列編號 語法：TODAY( )注意：序列號是 Microsoft Excel 日期和時間

10、計算使用的日期-時間代碼。Microsoft Excel 可將日期存儲為可用于計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1 ，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。LOOKUPHLOOKUPVLOOKUP使用LOOKUP函數是在單行區域或單列區域中查找數值，然后返回第二個單行區域或單列區域中相同位置的數值LOOKUP(lookup_value,lookup_vector,result_vector)說明：lookup_vector為第一個包含一行或一列的區域；result_vector為第二

11、個包含一行或一列的區域，其大小必須與參數lookup_vector相同使用HLOOKUP函數可以在表格的首行查找指定的數值,并由此返回指定行處與該數值同一列的數值，當比較值位于數據表首行時，可使用此函數語法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。當比較值位于數據表首列時,可以使用此函數語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)COUNTCOUNTCO

12、UNTIFCOUNTIFSUMIFSUMIFINTINTMODMODCOUNT函數的作用：返回包含數字以及包含參數列表中的數字的單元格的個數。利用函數 COUNT 可以計算單元格區域或數字數組中數字字段的輸入項個數。語法：COUNT(value1,value2,.)參數：Value1, value2, . 為包含或引用各種類型數據的參數（1 到 30個），但只有數字類型的數據才被計算。說明 函數 COUNT 在計數時，將把數字、日期、或以文本代表的數字計算在內；但是錯誤值或其他無法轉換成數字的文字將被忽略。 如果參數是一個數組或引用，那么只統計數組或引用中的數字；數組或引用中的空白單元格、邏輯

13、值、文字或錯誤值都將被忽略。COUNTIF函數可累計滿足某一條件的對象在單元格區域中出現的次數，該條件可以是數值、文本或表達式，在人事、工資和成績統計中有廣泛用途。語法：語法： COUNTIF（range,criteria)SUMIF函數可對滿足某一條件的單元格區域求和，該條件可以是數值、文本或表達式。語法：語法： SUMIF（range,criteria,sum_range)INT函數的作用：將數字向下舍入到最接近的整數。語法：INT(number)參數：number是需要進行向下舍入取整的實數。返回兩數相除的余數。結果的正負號與除數相同。語法MOD(number,divisor)；Numb

14、er 為被除數；Divisor 為除數。注解如果 divisor 為零，函數 MOD 返回錯誤值 #DIV/0!。函數 MOD 可以借用函數 INT 來表示:MOD(n, d) = n - d*INT(n/d) PMT（）貸款函數；基于固定利率及等額分期付款方式，返回貸款的每期付款額。語法語法PMTPMT(raterate,npernper,pvpv,fv,type) RateRate貸款利率。 NperNper該項貸款的付款總數。 PvPv現值，或一系列未來付款的當前值的累積和，也稱為本金。 FvFv為未來值，或在最后一次付款后希望得到的現金余額，如果省略 fv，則假設其值為零，也就是一筆貸

15、款的未來值為零。 TypeType數字 0 或 1，用以指定各期的付款時間是在期初還是期末。注解注解 PMT 返回的支付款項包括本金和利息，但不包括稅款、保留支付或某些與貸款有關的費用。 應確認所指定的 rate 和 nper 單位的一致性。例如，同樣是四年期年利率為 12% 的貸款，如果按月支付，rate 應為 12%/12，nper 應為 4*12；如果按年支付，rate 應為 12%，nper 為 4。 創建Excel圖表3.1編輯Excel圖表3.2打印輸出及其它設置3.3柱形圖：用來顯示一段時間內數據的變化或者描述各個項目之間數據比較圖表線性圖：主要用于以等時間間隔顯示數據的變化趨勢

16、，強調的是時間性和變動率，而不是變動量餅形圖：顯示數據系列中的項目和該項目數值總和的比例關系。它只能顯示一個系列的數據比例關系確定分析目標明確表格數據的取值范圍圖表向導使用一鍵成圖創建圖表實例11 圖表類型匯總常規編輯技巧 改變圖表大小改變圖表大小 移動圖表位置移動圖表位置 圖表內其他對象編輯圖表內其他對象編輯圖表的特殊編輯 向圖表添加數據向圖表添加數據 刪除圖表內數據刪除圖表內數據v 文字數據修飾v 線和陰影修飾v 背景修飾v 設置圖表的序列格式v 綜合案例分析頁面設置 工作表的頁眉、頁腳分頁符和打印選項 工作表參數設置 設置打印區域 自動打印統一表頭 打印指定頁面 打印連續頁碼 一次打印多個工作表 使某個工作表不能被打印數據的篩選4.2數據的分類匯總4.3數據透視表4.44.1數據的排序 4.1排序時，一般不要選擇數據范圍，以防止破壞數據的結構針對多列進行排列使用排序對話框進行相應操作 簡單排序 多字段排序 按行排序 自定義序列排序實例12 數據排序自動篩選高級篩選 條件區域的設置原則 工作表中指定一個空白區域作條件區域 條件區域第一行是條件名(字段名)行，第二行是輸入的條件 條件區域中不能包含有空行或空列，單元格中不能包含空格 字符字段的條件可以使用通配符