1、OFFICE系列培訓(xùn)之系列培訓(xùn)之- ExcelExcel實戰(zhàn)技巧與提高實戰(zhàn)技巧與提高 內(nèi)容提綱一、認識一、認識Excel 二、數(shù)據(jù)處理二、數(shù)據(jù)處理 三、函數(shù)與公式三、函數(shù)與公式 四、高級圖表四、高級圖表2工作表標簽工作表標簽列標列標活動單元格活動單元格行號行號菜單欄菜單欄繪圖工具欄繪圖工具欄認識界面?3工具欄工具欄標題欄標題欄自定義工具欄1、“視圖”-“工具欄”-“自定義” 2、“工具”-“自定義”？4認識選項5認識選項（續(xù)）6認識選項（續(xù)）7認識選項（續(xù)）8啟動Excel程序 單擊【開始】 【程序】 【Microsoft Office】 【Microsoft Office Excel 200

2、3】菜單命令，即可啟動Excel應(yīng)用程序。有三種方法：雙擊xcel文件單擊菜單【文件】 【打開】單擊工具欄上的【打開】按鈕打開文件有兩種方法：單擊菜單【文件】【保存】輸入文件名單擊工具欄上的【保存】按鈕 保存文件有兩種方法：單擊菜單欄右上角【關(guān)閉】按鈕單擊菜單【文件】【關(guān)閉】關(guān)閉文件內(nèi)容提綱一、認識一、認識Excel 二、數(shù)據(jù)處理二、數(shù)據(jù)處理 三、函數(shù)與公式三、函數(shù)與公式 四、高級圖表四、高級圖表 13數(shù)據(jù)處理 數(shù)據(jù)輸入：數(shù)據(jù)輸入：自動填充 單元格的編輯 行、列的基本操作 格式設(shè)置 工作表的基本操作 數(shù)據(jù)的簡單運算 打印工作表14 數(shù)據(jù)有效性 自定義格式 條件格式 合并計算 分類匯總 數(shù)據(jù)透視

3、表 數(shù)據(jù)輸入：數(shù)據(jù)輸入：單擊要輸入數(shù)據(jù)的單元格，利用鍵盤輸入相應(yīng)的內(nèi)容 默認情況下，輸入數(shù)據(jù)后，按nter鍵，將確認輸入并切換到下方的單元格按ab 鍵，可切換到右方的單元格；按鍵盤上的單元格，可靈活選擇下一個數(shù)據(jù)輸入的位置數(shù)據(jù)輸入數(shù)據(jù)輸入修改數(shù)據(jù)： 直接替換數(shù)據(jù)。單擊選中要修改的單元格，輸入新內(nèi)容，會替換原單元格中的內(nèi)容，如需修改整個單元格的內(nèi)容，不用雙擊進入單元格后再修改！ 修改單元格中的部分內(nèi)容。雙擊單元格，單元格變錄入狀態(tài)，光標成“”形，表示文字插入的位置，然后在要修改的文字上拖動鼠標選中要修改的文字，然后輸入新的內(nèi)容。如下圖：數(shù)據(jù)輸入數(shù)據(jù)輸入自動填充： 就是通過簡單的雙擊或拖動操作快速

4、、準確地輸入大量有規(guī)律的數(shù)據(jù)。填充句柄填充句柄選定一個單元格，仔細觀察后，會發(fā)現(xiàn)在單元格右下方有一個小黑方塊，這就是填充句柄。填充句柄填充和自動填充：數(shù)據(jù)輸入數(shù)據(jù)輸入自動填充操作步驟: 當(dāng)鼠標光移動到填充句柄上方時,鼠標會變成“ + + ”字形狀,此時可能通過按住鼠標左鍵拖動實行自動填充操作。數(shù)據(jù)輸入數(shù)據(jù)輸入豎向單擊拖動鼠標,釋放鼠標左鍵后,自動完成填充操作。自動填充操作步驟:數(shù)據(jù)輸入數(shù)據(jù)輸入 在最后一個填充的單元格右下角,會出現(xiàn)一個“自動填充選項”,單擊“自動填充選項”標記,會出現(xiàn)如圖所示的自動填充選項,在這里我們可以重新選擇自動填充的方式,默認填充方式為“以序列方式填充”。自動填充操作步驟

5、:數(shù)據(jù)輸入數(shù)據(jù)輸入單元格的編輯 我們除了對單元格的內(nèi)容進行修改外,還可以對單元格進行移動、復(fù)制、插入、刪除等操作。移動單元格 單擊選擇單元格或多個單元格，按住鼠標左鍵拖動目標位置，即可完成對單元格的移動操作。將鼠標移動到單元格邊框圖上，鼠標變成移動狀態(tài)，然后拖動鼠標到目標位置即可。復(fù)制單元格 傳統(tǒng)方法是通過選擇單元格，單擊編輯-復(fù)制菜單命令鍵（或利用快捷鍵【Ctrl】+C），然后選擇目標單元格，單擊編輯-粘貼菜單命令（或利用快捷鍵【Ctrl】+V），完成對單元格的復(fù)制。 在執(zhí)行移動單元格的過程中，按住【Ctrl】鍵，在箭頭的右上角會出現(xiàn)一個“+”號，這進可以完成對單元格的復(fù)制操作。單元格的編輯

6、清除與刪除清除：只要選中單元格，然后按【Delete】鍵，就可以清除單元格中的內(nèi)容，或在單元格上單擊右鍵，在彈出的快捷菜單中選擇【清除內(nèi)容】命令。單元格的編輯刪除：要刪除單元格，則在單元格上單擊右鍵，在彈出的快捷菜單中選擇【刪除】命令，在“刪除”對話框中選擇一種刪除形式，然后單擊【確定】按鈕。清除與刪除單元格的編輯插入行 在要插入的行的下方，單擊左側(cè)的行號，選擇一行單元格，單擊右鍵彈出快捷菜單，選擇插入命令，如圖行、列的基本操作 單擊要插入位置后面的列標，選擇一列單元格，單擊右鍵彈出快捷菜單，選擇插入命令，如圖插入列行、列的基本操作刪除行、列：在要刪除的行或列上單擊右鍵，在彈出的快捷菜單中選擇

7、【刪除】命令即可。行、列的基本操作行高和列寬調(diào)整行高：將光標移動到行號中間的分隔線上，此時鼠標變成 ,單擊鼠標左鍵向上(或向下)拖動,即可調(diào)整單元格的行高。行、列的基本操作調(diào)整列寬：將光標移動到列標中間的分隔線上，此時鼠標變成 ,單擊鼠標左鍵向左(或向右)拖動,即可調(diào)整單元格的列寬。行高和列寬行、列的基本操作 選擇多列（或多行）單元格，單擊鼠標右鍵，在彈出的快捷菜單中單擊選擇【隱藏】命令，即可隱藏單元格的列（或行）。行、列隱藏行的隱藏與列的隱藏方法相同。行、列的基本操作 要恢復(fù)單元格的顯示，可以通過選擇被隱藏單元格的前后兩列單元格，然后單擊鼠標右鍵，在彈出的快捷菜單中選擇【取消隱藏】命令，即可

8、重新顯示被隱藏的單元格。行、列取消隱藏行、列的基本操作格式設(shè)置選擇單元格后，單擊鼠標右鍵，在彈出的快捷菜單中選擇【設(shè)置單元格格式】菜單選項，如圖：單元格屬性的設(shè)置 Excel中為了方便對數(shù)據(jù)處理，可以通過定義單元格的“數(shù)字”選項卡，來定義不同的單元格數(shù)據(jù)格式。例：輸入電話號碼的區(qū)號“010” 如果直接輸入，“010”中的第一個“0”會被忽略掉。此類數(shù)據(jù)已經(jīng)不是數(shù)字，這是我們需要通過先將單元格的數(shù)據(jù)格式修改成“文本”類型后，再在該單元格處輸入“010”。單元格屬性的設(shè)置格式設(shè)置【對齊】選項卡，可以對單元格的文本進行對齊方式、文本方向等的控制。其中“文本控制”中的“自動換行”選項，在單元格內(nèi)容比較

9、多需要用多行來進行顯示 時，可能使用這個選項達到換行的效果。單元格屬性的設(shè)置（Excel中的字體、邊框、圖案的設(shè)置與word里面的基本相同。）格式設(shè)置工作表的基本操作表間切換單擊表標簽，可以進行表格間的切換。Excel中，當(dāng)前的表格標簽為白色，默認的非當(dāng)前表格為淺灰色。若要選擇相鄰的多張工作表，可以通過shift鍵+單擊實現(xiàn)；若要選擇不相鄰的多張工作表，可以通過ctrl+單擊實現(xiàn)。多表的選擇選擇相鄰的多張工作表選擇不相鄰的多張工作表工作表的基本操作標簽的重命名在表標簽上雙擊鼠標，當(dāng)光標變成“I”狀態(tài)后，即可進行編輯修改狀態(tài)，對表標簽進行重命名。工作表的基本操作更改標簽的顏色在表格的上方，單擊鼠

10、標右鍵，彈出快捷菜單，選擇工作表標簽顏色命令，如圖在彈出的顏色面板中，選擇所需要的顏色，然后單擊 確定按鈕，單擊另外一個工作表后，即可看到效果。如圖工作表的基本操作添加工作表在工作表標簽上單鼠標右鍵，彈出快捷菜單，然后選擇【插入】命令，在彈出的對話框中選擇【工作表】即可工作表的基本操作在工作表標簽上單鼠標右鍵，彈出快捷菜單，然后選擇【刪除】命令刪除工作表工作表的基本操作移動、復(fù)制工作表在表標簽上，單擊鼠標右鍵，在彈出的快捷菜單中選擇移動或復(fù)制工作表命令，彈出對話框單擊“工作簿”下拉列表，可以選擇工作表要移動到的位置即可。如果選擇“建立副本”選項，則表示復(fù)制工作的操作。提醒：如需將該工作表移動到

11、指定的另一個工作薄中，則必須保證目標工作薄時打開狀態(tài)的！工作表的基本操作在工作簿內(nèi)可以進行工作表的位置移動，即單擊單擊一個工作表的表標簽，然后進行拖動，可以調(diào)整工作表的前后排列順序。調(diào)整工作表顯示順序拖動過程中出現(xiàn)的黑色倒立的三角形表示拖動工作表將要插入的位置。工作表的基本操作數(shù)據(jù)的簡單運算求和：選擇要求和的數(shù)據(jù)，并多選一格用于顯示結(jié)果，然后單擊工具欄上的自動求和按鈕。求平均值、計數(shù)等操作如此類推。打印工作表打印工作表之前，需要對工作表的頁面進行設(shè)置，單擊菜單文件下的頁面設(shè)置，如圖頁面選項卡：可以進行方向、縮放、紙張大小的設(shè)定。頁邊距選項卡：可以對打印表格頁邊距進行設(shè)置。打印工作表頁眉/頁腳選

12、項卡：可以對打印文件設(shè)置頁眉、頁腳。我們可以通過單擊自定義頁眉或自定義頁腳來進行自定義頁眉、頁腳。打印工作表工作表選項卡：可以設(shè)置要打印的區(qū)域，默認是全部區(qū)域。如果是多頁打印，可以將表頭部分設(shè)置為“打印標題”，這樣便可以在每頁出現(xiàn)標題內(nèi)容。打印工作表有兩種方法：文件菜單下的打印命令工具欄上的打印按鈕打印工作表常見問題常見問題命名編輯公式換行、自動轉(zhuǎn)行合并單元格撤銷上次操作插入多行過濾數(shù)據(jù)如何使用格式刷如何另存分類匯總可手動創(chuàng)建分級顯示和自動生成分級顯示。 注意：注意：1、一個工作表內(nèi)在同行方向上或列方向上只能創(chuàng)建一個分級顯示；2、在同一個分級顯示中，最多允許有8個層次關(guān)系 50數(shù)據(jù)透視表初步的

13、數(shù)據(jù)統(tǒng)計與分析51內(nèi)容提綱一、認識一、認識Excel 二、數(shù)據(jù)處理二、數(shù)據(jù)處理 三、函數(shù)與公式三、函數(shù)與公式 四、高級圖表四、高級圖表52什么是函數(shù)？什么是公式？ 公式是公式是以以“=”為引導(dǎo)，通過運算符按照一定的順序組合為引導(dǎo)，通過運算符按照一定的順序組合進行數(shù)據(jù)運算處理的進行數(shù)據(jù)運算處理的等式等式。 函數(shù)是函數(shù)是按照特定的算法執(zhí)行計算的產(chǎn)生一個或一組結(jié)果按照特定的算法執(zhí)行計算的產(chǎn)生一個或一組結(jié)果的預(yù)定義的的預(yù)定義的特殊公式特殊公式。序號公式說明1=15*3+20*2包含常量運算的公式2=A1*3+A2*2包含單元格引用的公式3=單價*數(shù)量包含名稱的公式4=SUM(A1*3,A2*2)包含函

14、數(shù)的公式53解讀函數(shù)的參數(shù)=IF(A10,SUM(B1:G1),” ”)函數(shù)的結(jié)構(gòu)嵌套函數(shù)函數(shù)名稱以逗號分隔的3個參數(shù) 思考：哪些函數(shù)不帶參數(shù)？54查看公式計算結(jié)果使用公式審核工具分布求值 以=IF(A10,SUM(B1:G1),” ”)為例，執(zhí)行“公式求值”1255查看公式計算結(jié)果（續(xù)）34556函數(shù)與公式1、文本/數(shù)字/日期函數(shù)講解2、實戰(zhàn)案例綜合應(yīng)用57文本處理函數(shù) 用途：連接“&”前后的字符、字段A A列列B B列列C C列列ABX500120ABX500120-E-E= =A1A1& &B1B1ABX500120-EABX500120-EA A列列B B列列ABX500120ABX5

15、00120= =A1A1& & -E-E ABX500120ABX500120-E思考：如何把零件號后面的-E去掉？1、連接符 &58文本處理函數(shù)2、大小寫字母轉(zhuǎn)換 Lower 、Upper、 Proper所有大寫字母轉(zhuǎn)為小寫字母所有大寫字母轉(zhuǎn)為小寫字母所有小寫字母轉(zhuǎn)為大寫字母所有小寫字母轉(zhuǎn)為大寫字母字符串首字母轉(zhuǎn)為大寫字母字符串首字母轉(zhuǎn)為大寫字母=LOWER (“I LOVE EXCEL”)=“i love excel”=UPPER (“i love excel”)=“I LOVE EXCEL”= Proper (“I 愛 excel”)=“I 愛 Excel”59文本處理函數(shù) 3、提取字符

16、Left 、 Right 、Mid 注意：MID函數(shù)有3個參數(shù)，而LEFT、RIGHT只有2個參數(shù)身份證號身份證號地區(qū)地區(qū)出生日期出生日期性別性別=left(=left(a1,6a1,6) )=mid(=mid(a1,7,6a1,7,6) )=right(=right(a1,1a1,1) )3201027810014923201027810014923201023201027810017810012 2員工代碼員工代碼社保號社保號姓名姓名=left(=left(a1,10a1,10) )=mid(=mid(a1,11,3a1,11,3) )00023370840002337084李虎李虎000

17、23370840002337084李虎李虎00006562460000656246丁世明丁世明00006562460000656246丁世明丁世明為什么是3而不是2？為什么不用Right函數(shù)？60數(shù)字計算函數(shù)1、計算余數(shù) Mod(3,2)=1Mod(15,4)=3Mod(21,7)=0應(yīng)用：身份證號判斷性別？如何判斷數(shù)字的奇偶性？思考：除2的余數(shù)有多少個？除3呢？61數(shù)字計算函數(shù)2、取整函數(shù) Int Trunc=Int(3.2) 返回不大于3.2的最大整數(shù)3=Int(-3.2) 返回不大于-3.2的最大整數(shù)-4=Trunc(3.2) 返回截去小數(shù)部分后的整數(shù)3=Trunc(-3.2) 返回截去

18、小數(shù)部分后的整數(shù)-362數(shù)字計算函數(shù)3、四舍五入Round數(shù)值數(shù)值公式公式顯示結(jié)果顯示結(jié)果123.456123.456=round(A2,0)=round(A2,0)123123123.456123.456=round(A3,2)=round(A3,2)123.46123.461234.561234.56=round(A4,-2)=round(A4,-2)12001200 區(qū)別區(qū)別63數(shù)字計算函數(shù)3、向上取整Roundup、向下取整Rounddown數(shù)值數(shù)值RoundupRoundupRounddownRounddown公式公式顯示結(jié)果顯示結(jié)果公式公式顯示結(jié)果顯示結(jié)果123.456123.45

19、6=rounddown(A3,0)=rounddown(A3,0)123123=roundup(A3,0)=roundup(A3,0)124124123.456123.456=rounddown(A3,2)=rounddown(A3,2)123.45123.45=roundup(A3,2)=roundup(A3,2)123.46123.461234.561234.56=rounddown(A3,-2)=rounddown(A3,-2)12001200=roundup(A3,-2)=roundup(A3,-2)1300130064日期與時間函數(shù)1、4個基礎(chǔ)日期函數(shù)特殊函數(shù)：特殊函數(shù)：Today(

20、)，讀取計算機設(shè)置的今日日期，無參數(shù)日期日期年年月月日日= =YearYear(a1)(a1)= =MonthMonth(a1)(a1)= =DayDay(a1)(a1)2009-6-162009-6-16200920096 61616年年月月日日日期日期= =DATEDATE(A1,B1,C1)(A1,B1,C1)200920096 616162009-6-162009-6-1665日期與時間函數(shù)日期與時間函數(shù)實戰(zhàn)練習(xí)： 快速計算退休日期（假設(shè)：男60退休、女55退休）姓名姓名性別性別出生日期出生日期部門部門退休日期退休日期艾笑艾笑女女1955-6-201955-6-20企管部企管部安博安博

21、男男1961-7-311961-7-31企管部企管部白露白露女女1979-5-131979-5-13企管部企管部謝堅謝堅男男1973-10-191973-10-19物流部物流部卜娟卜娟女女1978-12-291978-12-29物流部物流部蔡媛蔡媛女女1959-7-101959-7-10物流部物流部倉義倉義男男1978-11-201978-11-20生產(chǎn)部生產(chǎn)部曹廷曹廷男男1965-2-141965-2-14生產(chǎn)部生產(chǎn)部66日期與時間函數(shù)日期與時間函數(shù)2、Datedif函數(shù) 用途：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。注：此函數(shù)在Excel幫助文件中沒有。結(jié)構(gòu)：Datedif(start_dat

22、e,end_date,unit)unitunit代碼代碼函數(shù)返回值函數(shù)返回值yy時間段中的整年數(shù)時間段中的整年數(shù)mm時間段中的整月數(shù)時間段中的整月數(shù)dd時間段中的天數(shù)時間段中的天數(shù)mdmd忽略日期中的年和月，計算天數(shù)差忽略日期中的年和月，計算天數(shù)差ymym忽略日期中的年和日，計算月數(shù)差忽略日期中的年和日，計算月數(shù)差ydyd忽略日期中的年，計算天數(shù)差忽略日期中的年，計算天數(shù)差67日期與時間函數(shù)日期與時間函數(shù)2、Datedif函數(shù)練習(xí)(續(xù))開始日期開始日期結(jié)束日期結(jié)束日期公式公式顯示結(jié)果顯示結(jié)果2000-6-152009-6-16=datedif(A2,B2,y)2000-6-172009-6-1

23、6=datedif(A3,B3,y)2008-6-152009-6-16=datedif(A4,B4,m)2008-6-172009-6-16=datedif(A5,B5,m)2008-6-152009-6-16=datedif(A6,B6,d)2008-6-172009-6-16=datedif(A7,B7,d)2008-3-152009-6-16=datedif(A8,B8,md)2008-3-172009-6-16=datedif(A9,B9,md)2008-3-152009-6-16=datedif(A10,B10,ym)2008-6-172009-6-16=datedif(A11,B

24、11,ym)2008-6-152009-6-16=datedif(A12,B12,yd)2008-6-172009-6-16=datedif(A13,B13,yd)68日期與時間函數(shù)日期與時間函數(shù)2、Datedif函數(shù)(續(xù))開始日期開始日期結(jié)束日期結(jié)束日期公式公式顯示結(jié)果顯示結(jié)果2000-6-152009-6-16=datedif(A2,B2,y)92000-6-172009-6-16=datedif(A3,B3,y)82008-6-152009-6-16=datedif(A4,B4,m)122008-6-172009-6-16=datedif(A5,B5,m)112008-6-152009-

25、6-16=datedif(A6,B6,d)3662008-6-172009-6-16=datedif(A7,B7,d)3642008-3-152009-6-16=datedif(A8,B8,md)12008-3-172009-6-16=datedif(A9,B9,md)302008-3-152009-6-16=datedif(A10,B10,ym)32008-6-172009-6-16=datedif(A11,B11,ym)112008-6-152009-6-16=datedif(A12,B12,yd)12008-6-172009-6-16=datedif(A13,B13,yd)36469函數(shù)

26、與公式1、文本/數(shù)字/日期函數(shù)講解2、綜合應(yīng)用、綜合應(yīng)用701、相對單元格與絕對單元格 公式所在單元格與公式所引用單元格的位置關(guān)系位置關(guān)系稱為單元格引用的相對性。A1A1樣式樣式R1C1R1C1樣式樣式特征特征$A$1R1C1絕對引用，向右向下復(fù)制公式不改變引用關(guān)系A(chǔ)$1R1C*行絕對列相對混合引用，向下復(fù)制公式不改變引用關(guān)系$A1R*C1行相對列絕對混合引用，向右復(fù)制公式不改變引用關(guān)系A(chǔ)1R*C*相對引用，向右向下復(fù)制公式均改變引用關(guān)系練習(xí)：練習(xí)：制作九九乘法表712、工作表的引用1、引用其他工作表的數(shù)據(jù)、引用其他工作表的數(shù)據(jù) =工作表名稱工作表名稱！目標單元格目標單元格 例：例：=sheet2!A12、引用已打開的工作簿的數(shù)據(jù)、引用已打開的工作簿的數(shù)據(jù) =工作簿名稱工作簿名稱工作表名稱工作表名稱！目標單元格目標單元格 例：例： =Book2.xlssheet1!$A$13、