Excel2003高級(jí)使用技巧

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ù)值類型的。如， 員工編號(hào)、姓名、出生年月、手機(jī)號(hào)碼常常用于各種數(shù)學(xué)計(jì)算的數(shù)據(jù)。如：工資、學(xué)生成績、銷售額等 是以“=”號(hào)開頭，它可以是簡單的數(shù)學(xué)式，也可以是包含各種Excel函數(shù)的式子數(shù)值文本公式數(shù)據(jù)錄入技巧 靈活使用TAB、ALT、ENTER鍵 錄入分?jǐn)?shù)：零+空格+分?jǐn)?shù) 錄入文本格式數(shù)字（郵編、電話等） ：單引號(hào)+錄入內(nèi)容 輸入當(dāng)前日期、時(shí)間：Ctrl

2、+; 和 Ctrl+Shift+; 錄入特殊字符（對(duì)號(hào)、平方）; 不連續(xù)區(qū)域的填充：Ctrl+Enter 填充柄錄入（序列填充） 插入批注實(shí)例01 數(shù)據(jù)錄入（1）在數(shù)字后面自動(dòng)添加單位（如：元、千克等） 將文本字符括在雙引號(hào)將文本字符括在雙引號(hào) ( ) ( ) 內(nèi)或在單個(gè)字符前面添加一個(gè)反斜杠內(nèi)或在單個(gè)字符前面添加一個(gè)反斜杠 ()()（2）輸入身份證的前幾位 5110251951102519（3）輸入條形碼前的某些重復(fù)數(shù)字 “999999999”“999999999”（4）定義小數(shù)點(diǎn)對(duì)齊、分?jǐn)?shù)對(duì)齊 ?.? # ?/?.? # ?/?（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日星期四日星期四 實(shí)例02 自定義數(shù)字格式1、EXCEL表格的排版技巧；2、EXCEL條件格式v 文字的對(duì)齊與旋轉(zhuǎn) 合并單元格 使用工具欄上的快捷按鈕 格式單元格對(duì)齊合并單元格 自動(dòng)換行 格式單元格對(duì)齊自動(dòng)換行 強(qiáng)制換行 ALT+ENTER 文字旋轉(zhuǎn) 格式單元格對(duì)齊文字方向v 設(shè)定邊框和底紋n使用工具欄上的“邊框”“底紋”快

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

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

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

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

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

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

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

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

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

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

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

15、也就是一筆貸款的未來值為零。 TypeType數(shù)字 0 或 1，用以指定各期的付款時(shí)間是在期初還是期末。注解注解 PMT 返回的支付款項(xiàng)包括本金和利息，但不包括稅款、保留支付或某些與貸款有關(guān)的費(fèi)用。 應(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柱形圖：用來顯示一段時(shí)間內(nèi)數(shù)據(jù)的變化或者描述各個(gè)項(xiàng)目之間數(shù)據(jù)比較圖表線性圖：主要用于以等時(shí)間間隔顯示數(shù)

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