1、Excel 2010高級(jí)使用技巧,Contents,1、Excel2010的數(shù)據(jù)錄入技巧； 2、自定義數(shù)字格式； 3、Excel2010條件格式設(shè)置； 4、數(shù)據(jù)有效性； 5、EXCEL多工作表鏈接、凍結(jié)與并排； 6、EXCEL工作表的數(shù)據(jù)保護(hù)功能；,一、數(shù)據(jù)錄入和格式化工作表,1.1、數(shù)據(jù)錄入的相關(guān)技巧,數(shù)據(jù)錄入技巧 靈活使用TAB、ALT、ENTER鍵 錄入分?jǐn)?shù)：零+空格+分?jǐn)?shù) 錄入文本格式數(shù)字（郵編、電話等） ：?jiǎn)我?hào)+錄入內(nèi)容 輸入當(dāng)前日期、時(shí)間：Ctrl+; 和 Ctrl+Shift+; 錄入特殊字符（對(duì)號(hào)、平方）; 不連續(xù)區(qū)域的填充：Ctrl+Enter 填充柄錄入（序列填充） 插入

2、批注,實(shí)例01 數(shù)據(jù)錄入,案例分析 （1）在數(shù)字后面自動(dòng)添加單位（如：元、千克等） 將文本字符括在雙引號(hào) ( ) 內(nèi)或在單個(gè)字符前面添加一個(gè)反斜杠 () （2）輸入身份證的前幾位 51102519 （3）輸入條形碼前的某些重復(fù)數(shù)字 “999999999” （4）定義小數(shù)點(diǎn)對(duì)齊、分?jǐn)?shù)對(duì)齊 ?.? # ?/? （5）自定義各種特殊日期格式 mmmm d, yyyy (dddd) December 30,2008 (Tuesday) yyyy“年”m“月”d“日”aaaa 2008年12月30日星期四,1.2、自動(dòng)限定數(shù)據(jù)格式,實(shí)例02 自定義數(shù)字格式,1、EXCEL表格的排版技巧； 2、EXCEL

3、條件格式,1.3、Excel2010條件格式設(shè)置；,1.3.1、單元格格式設(shè)置,文字的對(duì)齊與旋轉(zhuǎn) 合并單元格 使用工具欄上的快捷按鈕 格式單元格對(duì)齊合并單元格 自動(dòng)換行 格式單元格對(duì)齊自動(dòng)換行 強(qiáng)制換行 ALT+ENTER 文字旋轉(zhuǎn) 格式單元格對(duì)齊文字方向 設(shè)定邊框和底紋 使用工具欄上的“邊框”“底紋”快捷按鈕 格式單元格邊框圖案,實(shí)例03 對(duì)齊技巧與邊框底紋,1.3.2、條件格式管理器設(shè)置,什么是條件格式 當(dāng)單元格的值滿足預(yù)先設(shè)定的條件時(shí)，自動(dòng)將單元格設(shè)置成預(yù)先設(shè)定的格式 多條件格式的規(guī)則 按從上到下的順序判斷條件并執(zhí)行 最多指定三個(gè)條件 根據(jù)公式自動(dòng)設(shè)置格式 在條件格式中使用公式和函數(shù) 實(shí)

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

5、+Page Down 移到下一個(gè)工作表 技巧二：右鍵菜單準(zhǔn)確定位工作表 右擊導(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í)比較兩個(gè)工作簿可能很困難,實(shí)例07-1 并排比較數(shù)據(jù)1,實(shí)例07-2 并排比較數(shù)據(jù)2,1.6、數(shù)據(jù)保護(hù)功能,保護(hù)工作表、工作簿 （1）為工作簿加密碼（2）為工作表加密碼（3）設(shè)置不同區(qū)域不同密

6、碼（4）設(shè)置只有部分單元格可編輯,二、公式與函數(shù)的應(yīng)用及案例,雙擊填充柄復(fù)制公式 區(qū)別公式中各種單元格引用方式 絕對(duì)引用、相對(duì)引用、混合引用、三維引用 跨工作表或工作簿的數(shù)據(jù)計(jì)算 不同工作表間數(shù)據(jù)的計(jì)算、不同工作簿間數(shù)據(jù)的計(jì)算 自動(dòng)求和與自動(dòng)計(jì)算功能的使用,2.1 公式的基本用法,實(shí)例08 使用相對(duì)和絕對(duì)引用計(jì)算產(chǎn)品銷售額與利潤(rù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最多支持七

7、層嵌套函數(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)用,2.2 函數(shù)應(yīng)用與常用函數(shù)應(yīng)用舉例,實(shí)例10 函數(shù)綜合實(shí)例,IF ANDOR LEN MID LEFT RIGHT,2.2.1邏輯文本類函數(shù),判斷一個(gè)條件是否滿足，如果滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值,IF函數(shù),語(yǔ)法：IF(logical_test,value_if_true,value_if_false),條件為真時(shí),條件為假時(shí),AND OR 函數(shù),AND函數(shù)的作用：是對(duì)多個(gè)邏輯值進(jìn)行交集計(jì)算，返回邏輯值語(yǔ)法：AND(logical1,logical2) OR函數(shù)的作用：是對(duì)多個(gè)邏

8、輯值進(jìn)行并集計(jì)算，返回邏輯值語(yǔ)法：OR(logical1,logical2,),MIDB函數(shù)作用自文字指定位置開始向右提取指定長(zhǎng)度的字符串，操作對(duì)象均為文本，MID以字符為單位，MIDB以字節(jié)為單位截取語(yǔ)法：MID (text,start_num,num_bytes) LEFT返回文本字符串中的第一個(gè)或前幾個(gè)字符，RIGHT返回文本字符串中的最后一個(gè)或后幾年字符語(yǔ)法：LEFT(text,num_chars)語(yǔ)法：RIGHT(text,num_chars),MID(MIDB) LEFT RIGHT函數(shù),DATE：返回代表特定日期的序列號(hào) 語(yǔ)法：DATE(year,month,day) NOW：返

9、回計(jì)算機(jī)內(nèi)部時(shí)鐘的當(dāng)前日期和時(shí)間 語(yǔ)法：NOW( ) TODAY：返回當(dāng)前日期的序列編號(hào) 語(yǔ)法：TODAY( ) 注意：序列號(hào)是 Microsoft Excel 日期和時(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 天。,2.2.2日期與時(shí)間函數(shù)：,LOOKUP HLOOKUP VLOOKUP,2.2.3查詢和引用類函數(shù),LOOKUP,使用LOOKUP函數(shù)是在單行區(qū)域或單列區(qū)域

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

11、okup),VLOOKUP_實(shí)現(xiàn)豎直查找,在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表首列時(shí),可以使用此函數(shù) 語(yǔ)法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),COUNT COUNTIF SUMIF INT MOD,2.2.4統(tǒng)計(jì)計(jì)數(shù)類函數(shù),COUNT,COUNT函數(shù)的作用：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù)。利用函數(shù) COUNT 可以計(jì)算單元格區(qū)域或數(shù)字?jǐn)?shù)組中數(shù)字字段的輸入項(xiàng)個(gè)數(shù)。 語(yǔ)法：COUNT(value1,value2,.) 參數(shù)：Va

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

13、的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達(dá)式。,SUMIF函數(shù),語(yǔ)法： SUMIF（range,criteria,sum_range),INT,INT函數(shù)的作用：將數(shù)字向下舍入到最接近的整數(shù)。 語(yǔ)法：INT(number) 參數(shù)：number是需要進(jìn)行向下舍入取整的實(shí)數(shù)。,MOD,返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)相同。 語(yǔ)法 MOD(number,divisor)；Number為被除數(shù)；Divisor為除數(shù)。 注解 如果 divisor 為零，函數(shù) MOD 返回錯(cuò)誤值 #DIV/0!。 函數(shù) MOD 可以借用函數(shù) INT 來表示: MOD(n, d) = n - d*INT(n/d)

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

15、12% 的貸款，如果按月支付，rate 應(yīng)為 12%/12，nper 應(yīng)為 4*12；如果按年支付，rate 應(yīng)為 12%，nper 為 4。,2.2.6財(cái)務(wù)函數(shù)及案例應(yīng)用,三、圖表及打印輸出及其它設(shè)置,柱形圖：用來顯示一段時(shí)間內(nèi)數(shù)據(jù)的變化或者描述各個(gè)項(xiàng)目之間數(shù)據(jù)比較圖表 線性圖：主要用于以等時(shí)間間隔顯示數(shù)據(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)系,圖表在數(shù)據(jù)分析中的作用,確定分析目標(biāo) 明確表格數(shù)據(jù)的取值范圍 圖表向?qū)?使用一鍵成圖創(chuàng)建圖表,3.1 創(chuàng)建分析圖表,實(shí)例11 圖表類型匯總,常規(guī)編輯技

16、巧 改變圖表大小 移動(dòng)圖表位置 圖表內(nèi)其他對(duì)象編輯 圖表的特殊編輯 向圖表添加數(shù)據(jù) 刪除圖表內(nèi)數(shù)據(jù),3.2 圖表的編輯、美化技巧,文字?jǐn)?shù)據(jù)修飾 線和陰影修飾 背景修飾 設(shè)置圖表的序列格式 綜合案例分析,頁(yè)面設(shè)置 工作表的頁(yè)眉、頁(yè)腳 分頁(yè)符和打印選項(xiàng) 工作表參數(shù)設(shè)置 設(shè)置打印區(qū)域 自動(dòng)打印統(tǒng)一表頭 打印指定頁(yè)面 打印連續(xù)頁(yè)碼 一次打印多個(gè)工作表 使某個(gè)工作表不能被打印,3.3打印輸出及其它設(shè)置,四、數(shù)據(jù)分析及管理,4.1 排序數(shù)據(jù),排序時(shí)，一般不要選擇數(shù)據(jù)范圍，以防止破壞數(shù)據(jù)的結(jié)構(gòu) 針對(duì)多列進(jìn)行排列使用排序?qū)υ捒蜻M(jìn)行相應(yīng)操作 簡(jiǎn)單排序 多字段排序 按行排序 自定義序列排序,實(shí)例12 數(shù)據(jù)排序,4.2 數(shù)據(jù)的篩選分析,自動(dòng)篩選 高級(jí)篩選 條件區(qū)域的設(shè)置原則 工作表中指定一個(gè)空白區(qū)域作條件區(qū)域 條件區(qū)域第一行是條件名(字段名)行，第二行是輸入的條件 條件區(qū)域中不能包含有空行或空列，單元格中不能包含空格 字符字段