1、 Excel 2007Excel 2007提升培訓班提升培訓班 Contents 一：數(shù)據(jù)錄入和格式化工作表 二：公式與函數(shù)的應(yīng)用及案例 三：圖表及打印輸出及其它設(shè)置 四：數(shù)據(jù)分析及管理 綜合案例練習 一、數(shù)據(jù)錄入和格式化工作表 ?1、Excel2007的數(shù)據(jù)錄入技巧； 2、自定義數(shù)字格式； 3、Excel2007條件格式設(shè)置； 4、數(shù)據(jù)有效性及案例； 5、EXCEL多工作表鏈接、凍結(jié)與并排； 6、EXCEL工作表的數(shù)據(jù)保護功能； 1.1、數(shù)據(jù)錄入的相關(guān)技巧 數(shù)值 文本 公式 常常用于各種數(shù)學計算的數(shù)據(jù)。如：工資、學生成績、銷售額等 說明性，解釋 性的數(shù)據(jù)描述，非數(shù)值類型的。如， 員工編號、姓

2、名、出生年月、手機號碼 是以是以“=”號號開頭，它可以是簡單的數(shù)學式，也可以是包含各種Excel函數(shù)的式子 ?數(shù)據(jù)錄入技巧 ? 靈活使用TAB、ALT、ENTER鍵 ? 錄入分數(shù)：零+空格+分數(shù) ? 錄入文本格式數(shù)字（郵編、電話等） ：單引號+錄入內(nèi)容 ? 輸入當前日期、時間：Ctrl+; 和 Ctrl+Shift+; ? 錄入特殊字符（對號、平方）; ? 不連續(xù)區(qū)域的填充：Ctrl+Enter ? 填充柄錄入（序列填充） ? 插入批注 ?數(shù)據(jù)錄入技巧 ? 引入動態(tài)數(shù)據(jù)導入WEB資料 ? 導入外部數(shù)據(jù) ? 大型工作表中單元格（區(qū)域）選擇 ? 使用智能標記進行輸入 ? 使用工作表組加快多工作表創(chuàng)

3、建和數(shù)據(jù)輸入速度 ? 數(shù)據(jù)朗讀 高效的Excel快捷鍵 快捷鍵 Ctrl+鼠標滾輪 Ctrl+Shift+方向鍵 Ctrl+A Ctrl+; / Ctrl+Shift+; Shift+F11 Alt+Enter 實現(xiàn)操作 縮放當前視圖 選擇連續(xù)系列數(shù)據(jù) 選擇全部數(shù)據(jù) 輸入當前日期/時間 新建工作表 在單元格內(nèi)強制換行 7 使用鍵盤快捷方式定位到所需位置 快捷鍵 Shift+箭頭鍵 Ctrl+箭頭鍵 F5 F2 Ctrl+End Ctrl+Home Page Up 移到數(shù)據(jù)的邊緣。 打開打開“定位定位”對話框。對話框。 編輯活動單元格，并將插入點放到單元格內(nèi)容末尾。 將插入點移到工作表中最后一個

4、使用的單元格。 將插入點移動到工作表的開頭。 向上移動一屏。 功能描述 通過按箭頭鍵可逐個單元格地擴展選擇范圍。 Page Down Alt+Page Up Alt+Page Down Ctrl+Shift+Page Up 向下移動一屏。 向左移動一屏。 向右移動一屏。 選中當前工作表和上一個工作表。 Ctrl+Shift+Page Down 選中當前工作表和下一個工作表。 1.2、自動限定數(shù)據(jù)格式 ?案例分析 （1）在數(shù)字后面自動添加單位（如：元、千克等） ? 將文本字符括在雙引號 ( ) 內(nèi)或在單個字符前面添加一個反斜杠 () （2）輸入身份證的前幾位 ?51102519 （3）輸入條形碼

5、前的某些重復數(shù)字 ?“999999999” （4）定義小數(shù)點對齊、分數(shù)對齊 ?.? # ?/? （5）自定義各種特殊日期格式 ?mmmm d, yyyy (dddd) December 30,2008 (Tuesday) ?yyyy“ 年年”m“ 月月”d“日日”aaaa 2008年12月30日星期四 選擇性粘貼 格式刷 ?選擇性粘貼 ?公式 ?數(shù)值 ?格式 ?批注 ?轉(zhuǎn)置 ?粘貼鏈接 ?格式刷 1.3、Excel2007條件格式設(shè)置； ?1、EXCEL表格的排版技巧； 2、EXCEL條件格式 1.3.1、單元格格式設(shè)置 ? 文字的對齊與旋轉(zhuǎn) ? 合并單元格 ? 使用工具欄上的快捷按鈕 ? 格

6、式格式單元格單元格對齊對齊合并單元格合并單元格 ? 自動換行 ? 格式格式單元格單元格對齊對齊自動換行自動換行 ? 強制換行 ? ALT+ENTER ? 文字旋轉(zhuǎn) ? 格式格式單元格單元格對齊對齊文字方向文字方向 ? 設(shè)定邊框和底紋 ?使用工具欄上的使用工具欄上的“邊框邊框”“”“底紋底紋”快捷按鈕快捷按鈕 格式格式單元格單元格邊框邊框圖案圖案 http:/ 1.3.2、條件格式管理器設(shè)置 ?什么是條件格式 ? 當單元格的值滿足預先設(shè)定的條件時，自動將單元格設(shè)置成預先設(shè)定的格式 ?多條件格式的規(guī)則 ? 按從上到下的順序判斷條件并執(zhí)行 ? 最多指定三個條件 ?根據(jù)公式自動設(shè)置格式 ? 在條件格式

7、中使用公式和函數(shù) ? 實現(xiàn)隔行變色等的特殊格式 ?掌握ROW和MOD函數(shù)的用法 = =mod(row(),2) ? 案例簡析 1.4、數(shù)據(jù)的有效性 ?提高數(shù)據(jù)錄入準確性 -有效性檢查。什么是數(shù)據(jù)的有效性？ ? 數(shù)據(jù)有效性是一個包含幫助你在工作表中輸入資料提示信息的工具. 它有如下功能： ?(1) 給用戶提供一個選擇列表 ?(2) 限定輸入內(nèi)容的類型或大小 ?(3) 自定義設(shè)置 ? 數(shù)據(jù)有效性的設(shè)置 ? 提供下拉式菜單的輸入 ?設(shè)置輸入提示 ? 設(shè)置出錯警告 ? 自定義有效性條件 1.5、如何處理大型工作表 ?在Excel多個工作表間快速切換 ? 技巧一：用快捷鍵快速導航工作表 ?Ctrl+Pa

8、ge Up 移到上一個工作表 ?Ctrl+Page Down 移到下一個工作表 ? 技巧二：右鍵菜單準確定位工作表 ? 右擊導航狀態(tài)按鈕選擇所需工作表標簽名 ?凍結(jié)工作表窗口 大型工作表數(shù)據(jù)的分組、分級顯示 命名單元格或單元格區(qū)域，以便能夠快速返回到這些工作表區(qū)域 組合工作表，以便同時在多張工作表中輸入共用數(shù)據(jù) 1.5、如何處理大型工作表 ?并排比較數(shù)據(jù) ? 兩個工作簿將在同一個窗口中打開。當一個工作簿滾動時，Excel 將使第二個工作簿自動滾動相應(yīng)數(shù)量的單元格 ?工作表間的數(shù)據(jù)鏈接 ? 創(chuàng)建引導型頁面 ? 創(chuàng)建鏈接按鈕 ? 創(chuàng)建超級鏈接及編輯超級鏈接 ? 設(shè)置工作表及行號列標的隱藏 同時比較

9、兩個工作簿可能很困難同時比較兩個工作簿可能很困難 1.6、數(shù)據(jù)保護功能 ?保護工作表、工作簿 （1）為工作簿加密碼 （2）以隱藏方式保存工作簿 （3）為工作表加密碼 （4）隱藏工作表，并阻止其他人隨意取消隱藏 （5）設(shè)置不同區(qū)域不同密碼 （6）設(shè)置只有部分單元格可編輯 （7）隱藏敏感公式 ?設(shè)置表格的鎖定保護 ?將允許用戶更改的單元格區(qū)域設(shè)置為解除鎖定狀態(tài) ?隱藏單元格的公式 ?先選中全表格式單元格保護-把選定和隱藏上的兩個勾去掉 。再選中你要隱藏的單元格，格式單元格保護-把選定和隱藏前 的兩個勾打上 。再選工具-保護- 保護工作表 ?將工作表進行保護的設(shè)置(加密) 二：圖表及打印輸出及其它設(shè)

10、置 2.1 創(chuàng)建Excel圖表 2.2 編輯Excel圖表 2.3 打印輸出及其它設(shè)置 圖表在數(shù)據(jù)分析中的作用 ?柱形圖：用來顯示一段時間內(nèi)數(shù)據(jù)的變化或者描述各個項目之間數(shù)據(jù)比較圖表 ?線性圖：主要用于以等時間間隔顯示數(shù)據(jù)的 變化趨勢，強調(diào)的是時間性和變動率，而不是 變動量 ?餅形圖：顯示數(shù)據(jù)系列中的項目和該項目數(shù)值總和的比例關(guān)系。它只能顯示一個系列的數(shù)據(jù)比例關(guān)系 ?組合圖 2.1 創(chuàng)建分析圖表 ?確定分析目標 明確表格數(shù)據(jù)的取值范圍 圖表向?qū)??使用一鍵成圖創(chuàng)建圖表 2.2 圖表的編輯、美化技巧 ?常規(guī)編輯技巧 ? 改變圖表大小 ? 移動圖表位置 ? 文字數(shù)據(jù)修飾 ? 線和陰影修飾 ? 背景

11、修飾 ? 圖表內(nèi)其他對象編輯 ? 設(shè)置圖表的序列格式 ? 綜合案例分析 ?圖表的特殊編輯 ? 向圖表添加數(shù)據(jù) ? 刪除圖表內(nèi)數(shù)據(jù) 2.3打印輸出及其它設(shè)置 ?頁面設(shè)置 工作表的頁眉、頁腳 分頁符和打印選項 工作表參數(shù)設(shè)置 ?設(shè)置打印區(qū)域 ? 自動打印統(tǒng)一表頭 ? 打印指定頁面 ? 打印連續(xù)頁碼 ? 一次打印多個工作表 ? 使某個工作表不能被打印 三：公式與函數(shù)的應(yīng)用及案例 3.1 公式的基本用法 3.2 函數(shù)分類與常用函數(shù)應(yīng)用舉例 3.1 公式的基本用法 ?雙擊填充柄復制公式 ?區(qū)別公式中各種單元格引用方式 ? 絕對引用 ? 相對引用 ? 混合引用 ? 三維引用 ?跨工作表或工作簿的數(shù)據(jù)計算

12、? 不同工作表間數(shù)據(jù)的計算 ? 不同工作簿間數(shù)據(jù)的計算 ?自動求和與自動計算功能的使用 3.2 函數(shù)應(yīng)用與常用函數(shù)應(yīng)用舉例 ? 函數(shù)：是預定義的公式，通過使用一些稱為參數(shù)的特定值來安特定的順序和結(jié)構(gòu)執(zhí)行計算 ?函數(shù)的使用方式=函數(shù)名稱（參數(shù) 1，參數(shù)2，參數(shù)3) 函數(shù)的嵌套特性 ? 一個函數(shù)可以引用其他函數(shù)的結(jié)果作為參數(shù) ? Excel最多支持七層嵌套函數(shù) ?邏輯文本類函數(shù)應(yīng)用 日期時間函數(shù)應(yīng)用 查詢引用類函數(shù)應(yīng)用 ?統(tǒng)計計數(shù)類函數(shù)應(yīng)用 其他常用函數(shù)應(yīng)用 財務(wù)函數(shù)應(yīng)用 3.2.1邏輯文本類函數(shù) ?IF ANDOR LEN MID LEFT RIGHT CONCATENATE EXACT IF函

13、數(shù) ?判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值 條件為真時條件為真時 語法：語法： IF(logical_test,value_if_true,value_if_false) 條件為假時條件為假時 AND OR 函數(shù) ?AND函數(shù)的作用：是對多個邏輯值進行交集計算，返回邏輯值 語法：AND(logical1,logical2) ?OR函數(shù)的作用：是對多個邏輯值進行并集計算，返回邏輯值 語法：OR(logical1,logical2,) MID(MIDB) LEFT RIGHT函數(shù) ?MIDB函數(shù)作用自文字指定位置開始向右提取指定長度的字符串，操作對象均為文本， MID

14、以字符為單位， MIDB以字節(jié)為單位截取 語法：MID (text,start_num,num_bytes) ?LEFT返回文本字符串中的第一個或前幾個字符， RIGHT返回文本字符串中的最后一個或后幾年字符 語法：LEFT(text,num_chars) 語法：RIGHT(text,num_chars) CONCATENATE函數(shù) ?CONCATENATE函數(shù)的作用是將幾個字符串合并為一個文本字符串 語法：CONCATENATE(text1,text2,) 也可使用“”運算符代替實現(xiàn)文本的合并 EXACT函數(shù) ?EXACT函數(shù)的作用：測試兩個字符串是否完全相同。如果它們完全相同，則返回TRU

15、E;否則返回FALSE。EXACT函數(shù)能區(qū)分大小寫，但忽略格式上的差異 ?語法：EXACT(text1，text2) 參數(shù)：Text1是待比較的第一個字符串，Text2是待比較的第二個字符串 3.2.2日期與時間函數(shù)： ?DATE：返回代表特定日期的序列號 ? 語法：DATE(year,month,day) ?NOW：返回計算機內(nèi)部時鐘的當前日期和時間 ? 語法：NOW( ) ?TODAY：返回當前日期的序列編號 ? 語法：TODAY( ) ?注意：序列號是 Microsoft Excel 日期和時間計算使用的日期-時間代碼。Microsoft Excel 可將日期存儲為可用于計算的序列號。默

16、認情況下，1900 年 1 月 1 日的序列號是 1 ，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。 3.2.3查詢和引用類函數(shù) ?LOOKUP HLOOKUP ?VLOOKUP INDEX MATCH LOOKUP ?使用LOOKUP函數(shù)是在單行區(qū)域或單列區(qū)域中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值 LOOKUP(lookup_value,lookup_vector,result_vector) 說明：lookup_vector為第一個包含一行或一列的區(qū)域；result_vector為第二個包含一行

17、或一列的區(qū)域，其大小必須與參數(shù)lookup_vector相同 HLOOKUP_實現(xiàn)水平查找 ?使用HLOOKUP函數(shù)可以在表格的首行查找指定的數(shù)值,并由此返回指定行處與該數(shù)值同一列的數(shù)值，當比較值位于數(shù)據(jù)表首行時，可使用此函數(shù) ?語法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) VLOOKUP_實現(xiàn)豎直查找 ?在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。當比較值位于數(shù)據(jù)表首列時,可以使用此函數(shù) ?語法：VLOOKUP(lookup_value,table_array,col_in

18、dex_num,range_lookup) INDEX ?INDEX函數(shù)用于返回表或者區(qū)域中的值或值的引用 語法：INDEX(reference,row_num,column_num,area_num) ?ref:表示對一個或多個單元格區(qū)域的引用，如是多個區(qū)域需用括號括起來；rowcolumn：表示引用中某行列的序號；area：表示選擇引用中的一個區(qū)域（對于多區(qū)域引用） MATCH ?MATCH用來返回在指定方式下與指定數(shù)值相匹配的數(shù)組中元素的相應(yīng)位置(返回的不是數(shù)值本身) ?語法：MATCH(lookup_value,lookup_array,match_type) L_value：為需要在

19、數(shù)據(jù)表中查找的數(shù)值 L_array：表示包含參數(shù)L_value的連續(xù)單元格區(qū)域 M_type：指明如何查找值，可取-1、0或1三個值： -1：查找大于或等于Value的最小值，array須降序排列 0：查找等于Value的第一個值，array可按任意順序排列 1：查找小于或等于Value的最大值，array須按升序排列 3.2.4統(tǒng)計計數(shù)類函數(shù) ?COUNT COUNTIF SUMIF FREQUENCY RANK COUNT ?COUNT函數(shù)的作用：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個數(shù)。利用函數(shù) COUNT 可以計算單元格區(qū)域或數(shù)字數(shù)組中數(shù)字字段的輸入項個數(shù)。 ?語法：COUN

20、T(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ù)組或引用中的空白單元格、邏輯值、文字或錯誤值都將被忽略。 COUNTIF函數(shù) ?COUNTIF函數(shù)可累計滿足某一條件的對象在單元格區(qū)域中出現(xiàn)的次數(shù)，該條件可以是數(shù)值、文本或表達式，在人事、工資和成績統(tǒng)計中有廣泛用途。 語法：語法： COUNT

21、IF（range,criteria) SUMIF函數(shù) ?SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達式。 語法：語法： SUMIF（range,criteria,sum_range) 頻率分布函數(shù) ?以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。例如，使用該函數(shù)可以計算在給定的分數(shù)范圍內(nèi)測驗分數(shù)的個數(shù)。由于函數(shù)返回一個數(shù) 組結(jié)果，所以必須以數(shù)組公式的形式輸入公式。 ?語法：FREQUENCY(data_array,bins_array) RANK 函數(shù) ?RANK函數(shù)返回一個數(shù)字在數(shù)字列表中的排位 語法：RANK(number,ref,order) order：為

22、0或省略，返回對ref降序排列的列表 3.2.5其它常用函數(shù) ?INT MOD ROUND RANK INT ?INT函數(shù)的作用：將數(shù)字向下舍入到最接近的整數(shù)。 ?語法：INT(number) ?參數(shù)：number是需要進行向下舍入取整的實數(shù)。 MOD 返回兩數(shù)相除的余數(shù)。結(jié)果的正負號與除數(shù)相同。 語法 MOD(number,divisor) Number 為被除數(shù)。 Divisor 為除數(shù)。 注解 如果 divisor 為零，函數(shù) MOD 返回錯誤值 #DIV/0!。 函數(shù) MOD 可以借用函數(shù) INT 來表示: MOD(n, d) = n - d*INT(n/d) ROUND ?ROUND

23、函數(shù)的作用：按指定位數(shù)四舍五入某個數(shù)字。 ?語法：ROUND(number，num_digits) 參數(shù)：Number是需要四舍五入的數(shù)字;Num_digits 為指定的位數(shù)，Number按此位數(shù)進行處理 ?注意:如果num_digits大于0，則四舍五入到指定的小數(shù)位 ;如果num_digits等于0，則四舍五入到最接近的整數(shù) ;如果num_digits小于0，則在小數(shù)點左側(cè)按指定位數(shù)四舍五入。 RANK 函數(shù) ?RANK函數(shù)返回一個數(shù)字在數(shù)字列表中的排位 語法：RANK(number,ref,order) order：為0或省略，返回對ref降序排列的列表 3.2.6財務(wù)函數(shù)及案例應(yīng)用 ?

24、PMT（）貸款函數(shù)； 基于固定利率及等額分期付款方式，返回貸款的每期付款額。 語法PMT(rate,nper,pv,fv,type) ?Rate 貸款利率。 Nper 該項貸款的付款總數(shù)。 Pv 現(xiàn)值，或一系列未來付款的當前值的累積和，也稱為本金。 Fv 為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè) 其值為零，也就是一筆貸款的未來值為零。? Type 數(shù)字 0 或 1，用以指定各期的付款時間是在期初還是期末。 ?注解 ? PMT 返回的支付款項包括本金和利息，但不包括稅款、保留支付或某些與貸款有關(guān)的費用。 ? 應(yīng)確認所指定的 rate 和 nper 單位的一致性。例如，同樣是四年期年利率為 12% 的貸款，如果按月支付， rate 應(yīng)為