Excel高級技巧應(yīng)用專題

1、Microsoft Excel高級技巧應(yīng)用專題高級技巧應(yīng)用專題微軟授權(quán)MLC講師2課程安排數(shù)據(jù)的輸入、編輯及格式化技巧公式、函數(shù)應(yīng)用及編輯技巧圖表的操作數(shù)據(jù)的分析與組織3一 數(shù)據(jù)輸入、格式化表格技巧自動限定數(shù)據(jù)Excel常用快捷鍵處理大型工作表常用技巧表格數(shù)據(jù)的保護(hù)41.自動限定數(shù)據(jù)Custom Number(自定義數(shù)字格式) 靈活使用自定義數(shù)字格式如：輸入身份證前的幾位數(shù)輸入條形碼前的某些重復(fù)數(shù)字在數(shù)字后面添加單位（如元、千克等）自定義各種特殊日期格式定義單元格中的不同位數(shù)小數(shù)點(diǎn)對齊* 操作要點(diǎn)：先選擇基本格式，再使用自定義(Custom)功能設(shè)置相應(yīng)格式5Conditional Forma

2、tting_條件格式根據(jù)單元格數(shù)據(jù)(Cell Value Is)區(qū)域的大小設(shè)置預(yù)警信息根據(jù)公式(Formula Is)自動設(shè)置格式 實(shí)現(xiàn)隔行變色等的特殊格式 掌握ROW和MOD函數(shù)的用法 案例簡析 掌握合理使用IF函數(shù) 條件格式中的條件設(shè)置技巧6Validation_數(shù)據(jù)有效性有效性檢查：可以對單元格內(nèi)的數(shù)據(jù)類型進(jìn)行限制，并能核對輸入單元格的數(shù)據(jù)定義域設(shè)置方法：如下拉式In-cell dropdown菜單設(shè)置輸入提示(Input Message)設(shè)置出錯警告(Error Alert)自定義有效性條件(AllowCustom)提供級聯(lián)下拉菜單(Using Indirect Function)72

3、.高效的Excel快捷鍵數(shù)據(jù)區(qū)域選擇技巧 使用Ctrl+* 選擇連續(xù)數(shù)據(jù)區(qū)域 使用Ctrl+Shift+方向鍵選擇系列數(shù)據(jù) 使用Alt+;選擇可見單元格(忽略隱藏單元格)其他常用Excel的快捷鍵 Ctrl+; Ctrl+Enter Ctrl+方向鍵 Shift+F11 Alt+Enter F3 F48使用鍵盤快捷方式定位到所需位置 快捷鍵功能描述Shift+箭頭鍵通過按箭頭鍵可逐個單元格地擴(kuò)展選擇范圍。Ctrl+箭頭鍵移到數(shù)據(jù)的邊緣。F5打開“定位”對話框。F2編輯活動單元格，并將插入點(diǎn)放到單元格內(nèi)容的末尾。Ctrl+End將插入點(diǎn)移到工作表中最后一個使用的單元格。Ctrl+Home將插入點(diǎn)

4、移動到工作表的開頭。Page Up向上移動一屏。Page Down向下移動一屏。Alt+Page Up向左移動一屏。Alt+Page Down向右移動一屏。Ctrl+Shift+Page Up選中當(dāng)前工作表和上一個工作表。Ctrl+Shift+Page Down選中當(dāng)前工作表和下一個工作表。9*在Excel多個工作表間快速切換技巧一技巧一：用快捷鍵快速導(dǎo)航工作表用快捷鍵快速導(dǎo)航工作表 Ctrl+Page Up移到上一個工作表 Ctrl+Page Down移到下一個工作表 技巧二：右鍵菜單準(zhǔn)確定位工作表技巧二：右鍵菜單準(zhǔn)確定位工作表 右擊導(dǎo)航狀態(tài)按鈕選擇所需工作表標(biāo)簽名103.如何處理大型工作表

5、凍結(jié)窗格(Freeze Panes)命名單元格或單元格區(qū)域，以便能夠快速返回到這些工作表區(qū)域(Insert Name)組合工作表(Group Sheet)，以便同時在多張工作表中輸入公用數(shù)據(jù)工作表間的數(shù)據(jù)鏈接(Insert HyperLink)組及分級顯示(Group and Outline)114. Compare Side by Side With 并排比較數(shù)據(jù)兩個工作簿將在同一個窗口中打開。當(dāng)一個工作簿滾動時，Excel 將使第二個工作簿自動滾動相應(yīng)數(shù)量的單元格 同時比較兩個工作簿可能很困難同時比較兩個工作簿可能很困難125.Protect Data_表格數(shù)據(jù)的安全傳統(tǒng)的數(shù)據(jù)保護(hù)模式(A

6、dd Password)缺點(diǎn)：不太靈活Protect Sheet (Workbook)保護(hù)工作表 將允許用戶更改的單元格區(qū)域設(shè)置為解除鎖定狀態(tài)( ProtectionLocked) 將需要隱藏單元格公式等進(jìn)行隱藏(Hidden) 將工作表進(jìn)行保護(hù)的設(shè)置(Tools-Protection)有針對性設(shè)置保護(hù)的等級(Allow Users to Edit Ranges)優(yōu)點(diǎn)：非常靈活136. *About Print 打印操作使工作表不能打印幾個工作表生成一個打印文件一次打印（打印的頁數(shù)可延續(xù)到下一個“Sheet”)使工作表不能打印:Private Sub Workbook_BeforePrint(

7、Cancel As Boolean)Cancel = TrueEnd Sub14二、公式與函數(shù)應(yīng)用編輯技巧基本公式應(yīng)用常用函數(shù)的應(yīng)用151. Base Functions_公式基本用法雙擊填充柄(Fill Control)復(fù)制公式在公式中合理使用Round函數(shù)區(qū)別公式中各種單元格引用方式跨工作表或工作簿的數(shù)據(jù)計算自動求和與自動計算功能的使用 自動求和(AutoSum)功能的擴(kuò)展 自動計算功能的擴(kuò)展162. 函數(shù)應(yīng)用與高級算法處理邏輯文本類函數(shù)應(yīng)用邏輯文本類函數(shù)應(yīng)用日期類函數(shù)應(yīng)用日期類函數(shù)應(yīng)用查詢引用類函數(shù)應(yīng)用查詢引用類函數(shù)應(yīng)用統(tǒng)計計數(shù)類函數(shù)應(yīng)用統(tǒng)計計數(shù)類函數(shù)應(yīng)用17a.邏輯文本類函數(shù)IFAND

8、ORLENCONCATENATE&MIDLEFTRIGHTPROPERLOWERUPPERTRIM18IF函數(shù)判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值語法：語法：IF(logical_test,value_if_true,value_if_false)條件為真時條件為真時條件為假時條件為假時19ANDORAND函數(shù)的作用是對多個邏輯值進(jìn)行交集計算，返回邏輯值A(chǔ)ND(logical1,logical2)OR函數(shù)的作用是對多個邏輯值進(jìn)行并集計算，返回邏輯值OR(logical1,logical2,)20LENCONCATENATELEN和LENB函數(shù)的作用是返回字

9、符串的長度，LEN以字符為單位，LENB以字節(jié)為單位LEN(text) LENB(text)CONCATENATE函數(shù)的作用是將幾個字符串合并為一個文本字符串CONCATENATE(text1,text2,)也可使用“”運(yùn)算符代替實(shí)現(xiàn)文本的合并21MID(MIDB)LEFTRIGHTMIDB函數(shù)作用自文字指定位置開始向右提取指定長度的字符串，操作對象均為文本，MID以字符為單位，MIDB以字節(jié)為單位截取MID (text,start_num,num_bytes)LEFT返回文本字符串中的第一個或前幾個字符，RIGHT返回文本字符串中的最后一個或后幾年字符LEFT(text,num_chars)

10、RIGHT(text,num_chars)22PROPERTRIMPROPER(text)將單元格中每個單詞的首字母大寫LOWER將文本全部更改為小寫UPPER 將文本全部更改為大寫TRIM刪除文本間多余的空格以及單元格中的前導(dǎo)空格和尾部空格23b.日期計算類函數(shù)DATENOWTODAYYEARMONTHDAYWEEKDAYNETWORKDAYSWORKDAY24DATENOWTODAYDATE用來將日期轉(zhuǎn)換為其序列編號的函數(shù)DATE(year,month,day)NOW()返回計算機(jī)內(nèi)部時鐘的當(dāng)前日期和時間TODAY()返回當(dāng)前日期的序列編號25YEARMONTHDAYYEAR主要用來計算日

11、期所代表的年份，返回值為1900到9999之間的整數(shù)MONTH用來計算日期所代表的月份，它返回值為1到12之間的整數(shù)DAY用來計算日期或者序列數(shù)所代表的日期在當(dāng)前月的天數(shù)，返回值為1到31之間的整數(shù)26WEEKDAYWEEKDAY函數(shù)用來計算某個確定的日期是星期幾WEEKDAY(serial_number,return_type)return_type參數(shù)的取值意義參 數(shù)代表意義1或者省略 返回數(shù)字1表示星期日,數(shù)字7是星期六2返回數(shù)字1表示星期一,數(shù)字7是星期日3返回數(shù)字0表示星期一,數(shù)字6是星期日27NETWORKDAYSWORKDAYNETWORKDAYS返回兩個日期間相隔的工作日天數(shù)N

12、ETWORKDAYS(start_date,end_date,holidays)WORKDAY從某日期開始，經(jīng)過指定數(shù)目的工作日之后的日期WORKDAY(start_date,days,holidays)注：這兩個函數(shù)需要加載后才能使用28c.查詢和引用類函數(shù)LOOKUPHLOOKUPVLOOKUPINDEXMATCH29LOOKUP使用LOOKUP函數(shù)是在單行區(qū)域或單列區(qū)域中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值LOOKUP(lookup_value,lookup_vector,result_vector)說明：lookup_vector為第一個包含一行或一列的區(qū)域；re

13、sult_vector為第二個包含一行或一列的區(qū)域，其大小必須與參數(shù)lookup_vector相同30HLOOKUP_實(shí)現(xiàn)水平查找使用HLOOKUP函數(shù)可以在表格的首行查找指定的數(shù)值,并由此返回指定行處與該數(shù)值同一列的數(shù)值，當(dāng)比較值位于數(shù)據(jù)表首行時，可使用此函數(shù)語法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)31VLOOKUP_實(shí)現(xiàn)豎直查找在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值.當(dāng)比較值位于數(shù)據(jù)表首列時,可以使用此函數(shù)語法：VLOOKUP(lookup_value,table

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

15、:為需要在數(shù)據(jù)表中查找的數(shù)值L_array:表示包含參數(shù)L_value的連續(xù)單元格區(qū)域M_type:指明如何查找值，可取-1、0或1三個值：-1：查找大于或等于Value的最小值，array須降序排列0:查找等于Value的第一個值，array可按任意順序排列1:查找小于或等于Value的最大值，array須按升序排列34d.統(tǒng)計類函數(shù)COUNTCOUNTACOUNTIFSUMIFCOUNTBLANKFREQUENCYMODELARGESMALLRANK35 COUNT/COUNTA函數(shù)COUNT函數(shù)返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個數(shù)COUNTA函數(shù)返回參數(shù)列表中非空值的單元格

16、個數(shù) COUNTBLANK函數(shù)返回包含空單元格的數(shù)目36 COUNTIF函數(shù)COUNTIF函數(shù)可累計滿足某一條件的對象在單元格區(qū)域中出現(xiàn)的次數(shù)，該條件可以是數(shù)值、文本或表達(dá)式，在人事、工資和成績統(tǒng)計中有廣泛用途。多條件時，使用SUM與IF的數(shù)組計算 例如統(tǒng)計考試成績及格的男生人數(shù) 統(tǒng)計工資表中女高工的人數(shù)語法：語法： COUNTIF（range,criteria)37SUMIF函數(shù)SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達(dá)式。多條件時使用“條件求和”向?qū)дZ法：語法： SUMIF（range,criteria,sum_range)38FREQUENCY_頻率分布

17、函數(shù) 以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。例如，使用該函數(shù)可以計算在給定的分?jǐn)?shù)范圍內(nèi)測驗分?jǐn)?shù)的個數(shù)。由于函數(shù)返回一個數(shù)組結(jié)果，所以必須以數(shù)組公式的形式輸入公式語法：FREQUENCY(data_array,bins_array)39LARGESMALLRANK函數(shù)LARGE函數(shù)是返回某個數(shù)據(jù)集中的第K個最大值LARGE(array,k)SMALL函數(shù)是返回某個數(shù)據(jù)集中的第K個最小值SMALL(array,k)RANK函數(shù)返回一個數(shù)字在數(shù)字列表中的排位RANK(number,ref,order)order:為0或省略，返回對ref降序排列的列表40三、Chart_數(shù)據(jù)圖表明確圖表分析的目

18、標(biāo)與正確選擇表格數(shù)據(jù)的關(guān)系合理的確定圖表類型以正確表現(xiàn)分析的目標(biāo)圖表的編輯、修飾及打印411. 圖表在數(shù)據(jù)分析中的作用柱形圖(Column):用來顯示一段時間內(nèi)數(shù)據(jù)的變化或者描述各個項目之間數(shù)據(jù)比較圖表線性圖(Line):主要用于以等時間間隔顯示數(shù)據(jù)的變化趨勢，強(qiáng)調(diào)的是時間性和變動率，而不是變動量餅形圖(Pie):顯示數(shù)據(jù)系列中的項目和該項目數(shù)值總和的比例關(guān)系。它只能顯示一個系列的數(shù)據(jù)比例關(guān)系組合圖422. Create Chart_創(chuàng)建分析圖表確定分析目標(biāo)明確表格數(shù)據(jù)的取值范圍圖表向?qū)?Chart Wizard)使用一鍵成圖創(chuàng)建圖表433.Edit Chart_圖表的編輯技巧常規(guī)編輯技巧 改變圖表大小 移動圖表位置 圖表內(nèi)其他對象編輯圖表的特殊編輯 向圖表添加數(shù)據(jù) 刪除圖表內(nèi)數(shù)據(jù)44*不讓Excel圖表隨源數(shù)據(jù)改變而改變 方法之一是將圖表復(fù)制并粘貼為圖片 選擇圖表按住Shift鍵單擊“編輯”“復(fù)制圖片”命令粘貼到特定區(qū)域方法之二是改