Excel2010高級使用技巧

1、ContentsContents 二、公式與函數(shù)的應用及案例二、公式與函數(shù)的應用及案例 1 1、Excel2010Excel2010的數(shù)據(jù)錄入技巧；的數(shù)據(jù)錄入技巧； 2 2、自定義數(shù)字格式；、自定義數(shù)字格式； 3 3、Excel2010Excel2010條件格式設置；條件格式設置； 4 4、數(shù)據(jù)有效性；數(shù)據(jù)有效性； 5 5、EXCELEXCEL多工作表鏈接、凍結與并排；多工作表鏈接、凍結與并排； 6 6、EXCELEXCEL工作表的數(shù)據(jù)保護功能；工作表的數(shù)據(jù)保護功能；一、1.1、數(shù)據(jù)錄入的相關技巧說明性，解釋說明性，解釋性的數(shù)據(jù)描述性的數(shù)據(jù)描述，非數(shù)值類型，非數(shù)值類型的。如，的。如， 員工員工

2、編號、姓名、編號、姓名、出生年月、手出生年月、手機號碼機號碼常常用于各種常常用于各種數(shù)學計算的數(shù)數(shù)學計算的數(shù)據(jù)。如：工資據(jù)。如：工資、學生成績、學生成績、銷售額等銷售額等 是以是以“=”號開號開頭，它可以是頭，它可以是簡單的數(shù)學式簡單的數(shù)學式，也可以是包，也可以是包含各種含各種Excel函數(shù)的式子函數(shù)的式子數(shù)值數(shù)值文本文本公式公式 數(shù)據(jù)錄入技巧數(shù)據(jù)錄入技巧 靈活使用靈活使用TABTAB、ALTALT、ENTERENTER鍵鍵 錄入分數(shù)錄入分數(shù)：零：零+ +空格空格+ +分數(shù)分數(shù) 錄入文本格式數(shù)字（郵編、電話等）錄入文本格式數(shù)字（郵編、電話等） ：單引號：單引號+ +錄入內(nèi)錄入內(nèi)容容 輸入當前日

3、期、時間：輸入當前日期、時間：Ctrl+; Ctrl+; 和和 Ctrl+Shift+; Ctrl+Shift+; 錄入特殊字符（對號、平方）錄入特殊字符（對號、平方）; ; 不連續(xù)區(qū)域的填充：不連續(xù)區(qū)域的填充：Ctrl+EnterCtrl+Enter 填充柄錄入（序列填充）填充柄錄入（序列填充） 插入批注插入批注實例實例01 01 數(shù)據(jù)錄入數(shù)據(jù)錄入 （1）在數(shù)字后面自動添加單位（如：元、千克等） 將文本字符括在雙引號將文本字符括在雙引號 ( ) ( ) 內(nèi)或在單個字符前面添加一個反斜杠內(nèi)或在單個字符前面添加一個反斜杠 ()() （2）輸入身份證的前幾位 5110251951102519 （3

4、）輸入條形碼前的某些重復數(shù)字 “999999999”“999999999” （4）定義小數(shù)點對齊、分數(shù)對齊 ?.? ?.? # ?/?# ?/? （5）自定義各種特殊日期格式 mmmmmmmm d, d, yyyyyyyy ( (dddddddd) ) December 30,2008 (Tuesday) December 30,2008 (Tuesday) yyyy“yyyy“年年”m“”m“月月”d“”d“日日”aaaa”aaaa 2008 2008年年1212月月3030日星期四日星期四 1.2、自動限定數(shù)據(jù)格式實例實例02 02 自定義數(shù)字格式自定義數(shù)字格式1、EXCEL表格的排版技巧

5、；2、EXCEL條件格式1.3、Excel2010條件格式設置；v 文字的對齊與旋轉文字的對齊與旋轉 合并單元格合并單元格 使用工具欄上的快捷按鈕使用工具欄上的快捷按鈕 格式格式單元格單元格對齊對齊合并單元格合并單元格 自動換行自動換行 格式格式單元格單元格對齊對齊自動換行自動換行 強制換行強制換行 ALT+ENTER 文字旋轉文字旋轉 格式格式單元格單元格對齊對齊文字方向文字方向v 設定邊框和底紋設定邊框和底紋n使用工具欄上的使用工具欄上的“邊框邊框”“”“底紋底紋”快捷按鈕快捷按鈕n格式格式單元格單元格邊框邊框圖案圖案實例實例03 03 對齊技巧與邊框底紋對齊技巧與邊框底紋什么是條件格式什

6、么是條件格式 當單元格的值滿足預先設定的條件時，自動將單元格設置成預先設定當單元格的值滿足預先設定的條件時，自動將單元格設置成預先設定的格式的格式多條件格式的規(guī)則多條件格式的規(guī)則 按從上到下的順序判斷條件并執(zhí)行按從上到下的順序判斷條件并執(zhí)行 最多指定三個條件最多指定三個條件根據(jù)公式自動設置格式根據(jù)公式自動設置格式 在條件格式中使用公式和函數(shù)在條件格式中使用公式和函數(shù) 實現(xiàn)隔行變色等的特殊格式實現(xiàn)隔行變色等的特殊格式 掌握掌握ROW和和MOD函數(shù)的用法函數(shù)的用法 = =mod(row(),2)實例實例04 04 條件格式條件格式 提高數(shù)據(jù)錄入準確性提高數(shù)據(jù)錄入準確性-有效性檢查。什么是數(shù)據(jù)的有效

7、性？有效性檢查。什么是數(shù)據(jù)的有效性？ 數(shù)據(jù)有效性是一個包含幫助你在工作表中輸入資料提示信息的工具. 它有如下功能： (1) (1) 給用戶提供一個選擇列表給用戶提供一個選擇列表 (2) (2) 限定輸入內(nèi)容的類型或大小限定輸入內(nèi)容的類型或大小 (3) (3) 自定義設置自定義設置 數(shù)據(jù)有效性的設置 提供下拉式菜單的輸入提供下拉式菜單的輸入 設置輸入提示設置輸入提示 設置出錯警告設置出錯警告 自定義有效性條件自定義有效性條件1.4、數(shù)據(jù)的有效性實例實例05 05 數(shù)據(jù)有效性數(shù)據(jù)有效性1.5、如何處理大型工作表 在Excel多個工作表間快速切換 技巧一：用快捷鍵快速導航工作表 Ctrl+Page

8、UpCtrl+Page Up 移到上一個工作表移到上一個工作表 Ctrl+Page Down Ctrl+Page Down 移到下一個工作表移到下一個工作表 技巧二：右鍵菜單準確定位工作表 右擊導航狀態(tài)按鈕選擇所需工作表標簽名右擊導航狀態(tài)按鈕選擇所需工作表標簽名 凍結工作表窗口 大型工作表數(shù)據(jù)的分組、分級顯示 命名單元格或單元格區(qū)域，以便能夠快速返回到這些工作表區(qū)域 組合工作表，以便同時在多張工作表中輸入共用數(shù)據(jù)實例實例06 06 凍結窗格凍結窗格 并排比較數(shù)據(jù)并排比較數(shù)據(jù) 兩個工作簿將在同一個窗口中兩個工作簿將在同一個窗口中打開。當一個工作簿滾動時，打開。當一個工作簿滾動時，Excel Ex

9、cel 將使第二個工作簿自動將使第二個工作簿自動滾動相應數(shù)量的單元格滾動相應數(shù)量的單元格 同時比較兩個工作簿可能很困難同時比較兩個工作簿可能很困難實例實例07-1 07-1 并排比較數(shù)據(jù)并排比較數(shù)據(jù)1 1實例實例07-2 07-2 并排比較數(shù)據(jù)并排比較數(shù)據(jù)2 2保護工作表、工作簿保護工作表、工作簿（1）為工作簿加密碼）為工作簿加密碼（2）為工作表加密碼）為工作表加密碼（3）設置不同區(qū)域不同密碼）設置不同區(qū)域不同密碼（4）設置只有部分單元格可編輯）設置只有部分單元格可編輯二、公式與函數(shù)的應用及案例公式的基本用法公式的基本用法2.1函數(shù)分類與常用函數(shù)應用舉例函數(shù)分類與常用函數(shù)應用舉例2.2 雙擊填

10、充柄復制公式 區(qū)別公式中各種單元格引用方式 絕對引用、相對引用、混合引用、三維引用 跨工作表或工作簿的數(shù)據(jù)計算 不同工作表間數(shù)據(jù)的計算、不同工作簿間數(shù)據(jù)的計算 自動求和與自動計算功能的使用2.1 公式的基本用法實例實例08 08 使用相對和絕對引用計算產(chǎn)品銷售額與利潤使用相對和絕對引用計算產(chǎn)品銷售額與利潤實例實例9-1 9-1 跨工作表數(shù)據(jù)計算跨工作表數(shù)據(jù)計算實例實例9-2 9-2 跨工作簿數(shù)據(jù)計算跨工作簿數(shù)據(jù)計算 函數(shù)：是預定義的公式，通過使用一些稱為參數(shù)的特定值來安特定的順序和結構執(zhí)行計算 函數(shù)的使用方式=函數(shù)名稱（參數(shù)1，參數(shù)2，參數(shù)3) 函數(shù)的嵌套特性 一個函數(shù)可以引用其他函數(shù)的結果作

11、為參數(shù) Excel最多支持七層嵌套函數(shù) 邏輯文本類函數(shù)應用 日期時間函數(shù)應用 查詢引用類函數(shù)應用 統(tǒng)計計數(shù)類函數(shù)應用 財務函數(shù)應用2.2 函數(shù)應用與常用函數(shù)應用舉例實例實例10 10 函數(shù)綜合實例函數(shù)綜合實例 IF ANDOR LEN MID LEFT RIGHT2.2.1邏輯文本類函數(shù) 判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值IF函數(shù)語法：語法：IF(logical_test,value_if_true,value_if_false)條件為真時條件為真時條件為假時條件為假時AND OR 函數(shù) AND函數(shù)的作用：是對多個邏輯值進行交集計算，返回邏輯值語法：AND(lo

12、gical1,logical2) OR函數(shù)的作用：是對多個邏輯值進行并集計算，返回邏輯值語法：OR(logical1,logical2,) MIDB函數(shù)作用自文字指定位置開始向右提取指定長度的字符串，操作對象均為文本，MID以字符為單位，MIDB以字節(jié)為單位截取語法：MID (text,start_num,num_bytes) LEFT返回文本字符串中的第一個或前幾個字符，RIGHT返回文本字符串中的最后一個或后幾年字符語法：LEFT(text,num_chars)語法：RIGHT(text,num_chars)MID(MIDB) LEFT RIGHT函數(shù) DATE：返回代表特定日期的序列號

13、語法：DATE(year,month,day) NOW：返回計算機內(nèi)部時鐘的當前日期和時間 語法：NOW( ) TODAY：返回當前日期的序列編號 語法：TODAY( ) 注意：序列號是 Microsoft Excel 日期和時間計算使用的日期-時間代碼。Microsoft Excel 可將日期存儲為可用于計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1 ，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。2.2.2日期與時間函數(shù)：LOOKUPHLOOKUPVLOOKUP2.2.3查詢和引用類函數(shù)LOOKU

14、P 使用LOOKUP函數(shù)是在單行區(qū)域或單列區(qū)域中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值LOOKUP(lookup_value,lookup_vector,result_vector)說明：lookup_vector為第一個包含一行或一列的區(qū)域；result_vector為第二個包含一行或一列的區(qū)域，其大小必須與參數(shù)lookup_vector相同HLOOKUP實現(xiàn)水平查找 使用HLOOKUP函數(shù)可以在表格的首行查找指定的數(shù)值,并由此返回指定行處與該數(shù)值同一列的數(shù)值，當比較值位于數(shù)據(jù)表首行時，可使用此函數(shù) 語法：HLOOKUP(lookup_value,table_array,

15、row_index_num,range_lookup)VLOOKUP_實現(xiàn)豎直查找 在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。當比較值位于數(shù)據(jù)表首列時,可以使用此函數(shù) 語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) COUNTCOUNT COUNTIFCOUNTIF SUMIFSUMIF INTINT MODMOD2.2.4統(tǒng)計計數(shù)類函數(shù)COUNT COUNT函數(shù)的作用：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個數(shù)。利用函數(shù) COUNT 可以計算單元格區(qū)域或數(shù)字數(shù)組中數(shù)

16、字字段的輸入項個數(shù)。 語法：COUNT(value1,value2,.) 參數(shù)：Value1, value2, . 為包含或引用各種類型數(shù)據(jù)的參數(shù)（1 到 30個），但只有數(shù)字類型的數(shù)據(jù)才被計算。 說明 函數(shù) COUNT 在計數(shù)時，將把數(shù)字、日期、或以文本代表的數(shù)字計算在內(nèi)；但是錯誤值或其他無法轉換成數(shù)字的文字將被忽略。 如果參數(shù)是一個數(shù)組或引用，那么只統(tǒng)計數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文字或錯誤值都將被忽略。 COUNTIF函數(shù)可累計滿足某一條件的對象在單元格區(qū)域中出現(xiàn)的次數(shù)，該條件可以是數(shù)值、文本或表達式，在人事、工資和成績統(tǒng)計中有廣泛用途。 COUNTIF函數(shù)語法

17、：語法： COUNTIF（range,criteria) SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達式。SUMIF函數(shù)語法：語法： SUMIF（range,criteria,sum_range)INT INT函數(shù)的作用：將數(shù)字向下舍入到最接近的整數(shù)。 語法：INT(number) 參數(shù)：number是需要進行向下舍入取整的實數(shù)。返回兩數(shù)相除的余數(shù)。結果的正負號與除數(shù)相同。返回兩數(shù)相除的余數(shù)。結果的正負號與除數(shù)相同。語法語法MOD(number,divisor)；Number 為被除數(shù)；為被除數(shù)；Divisor 為除數(shù)。為除數(shù)。注解注解如果如果 divisor

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

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

20、據(jù)比較圖表 線性圖：主要用于以等時間間隔顯示數(shù)據(jù)的變化趨勢，強調(diào)的是時間性和變動率，而不是變動量 餅形圖：顯示數(shù)據(jù)系列中的項目和該項目數(shù)值總和的比例關系。它只能顯示一個系列的數(shù)據(jù)比例關系圖表在數(shù)據(jù)分析中的作用 確定分析目標 明確表格數(shù)據(jù)的取值范圍 圖表向導 使用一鍵成圖創(chuàng)建圖表3.1 創(chuàng)建分析圖表實例實例11 11 圖表類型匯總圖表類型匯總 常規(guī)編輯技巧 改變圖表大小改變圖表大小 移動圖表位置移動圖表位置 圖表內(nèi)其他對象編輯圖表內(nèi)其他對象編輯 圖表的特殊編輯 向圖表添加數(shù)據(jù)向圖表添加數(shù)據(jù) 刪除圖表內(nèi)數(shù)據(jù)刪除圖表內(nèi)數(shù)據(jù)v 文字數(shù)據(jù)修飾文字數(shù)據(jù)修飾v 線和陰影修飾線和陰影修飾v 背景修飾背景修飾v 設