Excel2003高級使用技巧

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16、據(jù)的變化趨勢，強(qiáng)調(diào)的是時間性和變動率，而不是變動量餅形圖：顯示數(shù)據(jù)系列中的項目和該項目數(shù)值總和的比例關(guān)系。它只能顯示一個系列的數(shù)據(jù)比例關(guān)系確定分析目標(biāo)明確表格數(shù)據(jù)的取值范圍圖表向?qū)褂靡绘I成圖創(chuàng)建圖表實例11 圖表類型匯總常規(guī)編輯技巧 改變圖表大小改變圖表大小 移動圖表位置移動圖表位置 圖表內(nèi)其他對象編輯圖表內(nèi)其他對象編輯圖表的特殊編輯 向圖表添加數(shù)據(jù)向圖表添加數(shù)據(jù) 刪除圖表內(nèi)數(shù)據(jù)刪除圖表內(nèi)數(shù)據(jù)v 文字?jǐn)?shù)據(jù)修飾v 線和陰影修飾v 背景修飾v 設(shè)置圖表的序列格式v 綜合案例分析頁面設(shè)置 工作表的頁眉、頁腳分頁符和打印選項 工作表參數(shù)設(shè)置 設(shè)置打印區(qū)域 自動打印統(tǒng)一表頭 打印指定頁面 打印連續(xù)頁碼 一次打印多個工作表 使某個工作表不能被打印數(shù)據(jù)的篩選4.2數(shù)據(jù)的分類匯總4.3數(shù)據(jù)透視表4.44.1數(shù)據(jù)的排序 4.1排序時，一般不要選擇數(shù)據(jù)范圍，以防止破壞數(shù)據(jù)的結(jié)構(gòu)針對多列進(jìn)行排列使用排序?qū)υ捒蜻M(jìn)行相應(yīng)操作 簡單排序 多字段排序 按行排序 自定義序列排序?qū)嵗?2 數(shù)據(jù)排序自動篩選高級篩選 條件區(qū)域的設(shè)置原則 工作表中指定一個空白區(qū)域作條件區(qū)域 條件區(qū)域第一行是條件名(字段名)行，第二行是輸入的條件 條件區(qū)域中不能包含有空行或空列，單元格中不能包含空格 字符字段的條件可以使用通配符