Excel操作方法與技巧課件

1、Excel簡介Excel是Microsoft公司推出的辦公軟件Office中的一個重要組成成員,也是目前最流行的關于電子表格處理的軟件之一。它具有強大的計算、分析和圖表等功能，是公司目前最常用的辦公數(shù)據(jù)表格軟件。在Word中也有表格,在Excel表格與W表格的最大不同在于Excel表格具有強大的數(shù)字運算和數(shù)字分析能力。Excel中內(nèi)置的公式和函數(shù)，可能幫忙用戶進行復雜的計算，由于E在數(shù)據(jù)運算方面有強大的功能，使它成為用戶辦公必不可少的一個常用辦公軟件。第1頁，共44頁。Excel工作界面工作表標簽列標活動單元格行號任務窗格菜單欄繪圖工具欄公式編輯框第2頁，共44頁。Excel的基本元素-工作簿

2、、工作表、單元格工作簿和工作表的特性最大限制開啟工作簿的數(shù)目受限于可用的記憶體和資源系統(tǒng)工作表的大小 最多65536行，256列單元格內(nèi)可接受的文字長度每個單元格最多可包含32000個字符單元格內(nèi)可接受的數(shù)字型數(shù)據(jù)的長度常規(guī)數(shù)字格式的數(shù)據(jù)長度為11位（包括特定字符）單元格內(nèi)數(shù)字精度保留15位；若長度超過15位，多余數(shù)字位顯示為“0”工作簿中可使用工作表頁數(shù)最多255個工作表第3頁，共44頁。Excel主要的四個處理方面數(shù)據(jù)管理（作為一種數(shù)據(jù)管理工具）數(shù)據(jù)運算數(shù)據(jù)分析（通過圖、表及透視分析等）數(shù)據(jù)輸出第4頁，共44頁。數(shù)據(jù)管理第5頁，共44頁。數(shù)據(jù)填充選定相應的單元格并拖動其右下角的填充柄，或者

3、選擇【編輯】【填充】【序列】命令，可在Excel工作表中快速填充多種類型的數(shù)據(jù)序列。在行或列中復制數(shù)據(jù)，拖動單元格填充柄即可；填充一系列數(shù)字、日期等，通過拖動， Excel可自動延續(xù)此系列數(shù)字、日期或時間段等。 技巧 按Ctrl+D組合鍵，可向下填充； 按Ctrl+R組合鍵，可向右填充； “十”運用第6頁，共44頁。通過單擊數(shù)據(jù)菜單下的【排序】菜單命令，彈出“排序”對話框，如圖：復雜排序第7頁，共44頁。自定義篩選：通過單擊數(shù)據(jù)菜單下的【篩選】菜單命令【自動篩選】自定義篩選第8頁，共44頁。高級篩選：通過單擊數(shù)據(jù)菜單下的【篩選】菜單命令 【高級篩選】，通過設定條件進行篩選高級篩選第9頁，共44

4、頁。行列轉(zhuǎn)置姓名數(shù)學語文張三9980李四5570王五7050趙某8885程某6560任某4050劉某7577姓名張三李四王五趙某程某任某劉某數(shù)學99557088654075語文80705085605077選中此復選框時，可將所復制數(shù)據(jù)的列變成行，將行變成列。第10頁，共44頁。選擇性粘貼應用還可以實現(xiàn)如下功能：指定對粘貼的數(shù)值進行數(shù)學運算第11頁，共44頁。特殊數(shù)據(jù)的錄入數(shù)值格式 可以用來錄入一定長度的數(shù)字，0開關數(shù)據(jù)的錄入（方法二：錄入前“”） 在“自定義”選項中均有自己預定的格式信息例：批量添加計量單位 設置單元格格式“0 “XX” ”文本數(shù)據(jù)的錄入：日期數(shù)據(jù)的錄入：輸入3-8或3/8，顯

5、示為3月8日第12頁，共44頁。數(shù)據(jù)錄入的規(guī)范準確-有效性設置（一）在進行數(shù)據(jù)收集時，信息的輸入格式有嚴格要求，比如身份證號碼一欄，需要輸入的是15位還是18位號碼？性別輸入的是男女還是MF?如果信息輸入有錯，會出現(xiàn)怎么的出錯提示？這就需要規(guī)定單元格的數(shù)據(jù)的有效性假如公司只有4個職務，分別為經(jīng)理、主管、職員、臨時工。對在職職務字段進行信息輸入時，如何保證所有人輸入的信息只有以上4中。第13頁，共44頁。數(shù)據(jù)錄入的規(guī)范準確-有效性設置（二）第14頁，共44頁。有效性設置應用自定義Excel下拉菜單 要在Excel實現(xiàn)直接在下拉菜單中（自定義內(nèi)容）選擇輸入的功能，實現(xiàn)如下圖所示的效果步驟操作：1.

6、選中要做下拉列表的單元格。2.在菜單中選擇：數(shù)據(jù)有效性設置（允許）：序列；（來源）：輸入內(nèi)容（可自定義）。 第15頁，共44頁。分列與合并分列：文本分列向?qū)А睂⒑唵蔚膯卧駜?nèi)容拆分到不同的列合并-將兩列文本內(nèi)容進行合并：用&或函數(shù)CONCATENATE()進行連接第16頁，共44頁。分列應用舉例批量轉(zhuǎn)換日期格式有些人Excel中輸入職工出生時間時，為了簡單都輸入成“yymmdd”形式，但規(guī)范的要求輸入成“yyyy-mm-dd”格式，那么如何轉(zhuǎn)化？最快速的方法是：先選定要轉(zhuǎn)化的區(qū)域。點擊【數(shù)據(jù)】【分列】，出現(xiàn)【文本分列向?qū)А繉υ捒颉９催x“固定寬度”，連續(xù)兩次點擊【下一步】按鈕，在步驟三對話框的【

7、列數(shù)據(jù)格式】中，選擇“日期”，并選定“YMD”形式，按下【完成】按鈕，以前的文本即轉(zhuǎn)化成了需要的日期了第17頁，共44頁。拆分與凍結(jié)窗格凍結(jié)窗格以鎖定特定行或列要鎖定行，請選擇其下方要出現(xiàn)拆分的行。 要鎖定列，請選擇其右側(cè)要出現(xiàn)拆分的列。 要同時鎖定行和列，請單擊其下方和右側(cè)要出現(xiàn)拆分的單元格。拆分窗格以鎖定單獨工作表區(qū)域中的行或列要拆分窗格，請指向垂直滾動條頂端或水平滾動條右端的拆分框。 當指針變?yōu)椴鸱种羔?或 時，將拆分框向下或向左拖至所需的位置。 要取消拆分，請雙擊分割窗格的拆分條的任何部分。第18頁，共44頁。工作表復制、移動工作表隱藏工作表多個工作表的同時操作若要選擇相鄰的多張工作表

8、，可以通過shift鍵+單擊實現(xiàn)；若要選擇不相鄰的多張工作表，可以通過ctrl+單擊實現(xiàn)。工作表的保護第19頁，共44頁。實用快捷操作CTRL+C：復制；CTRL+X：剪切；CTRL+V：粘貼CTRL+Y(F4)：重復上一步操作CTRL+Z：取消上一步操作CTRL+F：查找Ctrl+shift+*：快速選擇工作表的數(shù)據(jù)區(qū)域在行或列間雙擊鼠標左鍵，行或列自動寬度自動適應SHIFT+鼠標左鍵：移動列或區(qū)塊Alt+格式刷Ctrl+Enter ：相同數(shù)據(jù)錄入Alt+Enter ：單元格文字隨時換行 第20頁，共44頁。外部數(shù)據(jù)的導入第21頁，共44頁。表格的美化三個方面：格線、表頭、背景添加邊框：選中

9、區(qū)域，添加繪圖邊框背景添加經(jīng)過簡單的合并、顏色、居中等處理快捷方式：自動套用格式突出顯示：條件格式第22頁，共44頁。其他重要功能超鏈接圖表（同PPT，不單獨介紹）選項的使用第23頁，共44頁。綜合小運用讓Excel中人名顯示更美觀的技巧 a 刪除姓名中的空格 選中姓名所在的列，執(zhí)行“編輯替換”命令，在“查找內(nèi)容”欄中輸入一個空格，在“替換為”欄中不填任何內(nèi)容，單擊“全部替換”命令，確定退出后即可. b 讓姓名左右對齊 姓名用字是兩個字與三個字的,左右對齊也有技巧.選中已刪除空格的姓名單元格，單擊“格式單元格”,在打開的“單元格格式”對話框中的水平對齊方式中選擇“分散對齊”選項，確定退出后即可

10、使學生姓名用字左右對齊。第24頁，共44頁。數(shù)據(jù)運算第25頁，共44頁。函數(shù)與公式Excel的主要功能之一是對數(shù)據(jù)進行運算，而公式和函數(shù)是數(shù)據(jù)運算的兩個最重要的工具。利用它們可以完成各種復雜的數(shù)據(jù)運算。掌握好這部分內(nèi)容，是熟練使用Excel進行數(shù)據(jù)管理的先決條件。什么是函數(shù)Excel函數(shù)即是預先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務的特殊公式。什么是公式函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說前者是Excel預先定義好的特殊公式，后者就是由用戶自行設計對工作表進行計算和處理的公式。公式是以“=”號為引導，通過運算符按照一定的順序組合進行數(shù)據(jù)運算處理的等式，函數(shù)則是按特定算法進行計算的產(chǎn)生一個或一組結(jié)果

11、的預定義特殊公式。第26頁，共44頁。示例 Excel中的公式通常都是以“=”開頭的一個運算式或函數(shù)式，可以將“=”引導符看成是Excel公式的標志。例如：G5單元中有如下內(nèi)容=G1+G3+G4*2又如：N9單元中有如下內(nèi)容=SUM（A9:M9） 這些都是Excel中最簡單的公式示例輸入公式： 單擊要輸入公式的單元格； 輸入=，然后輸入公式表達式； 按Enter鍵，或單擊編輯欄左側(cè)的輸入按鈕 ，即可得到運算結(jié)果。第27頁，共44頁。運算前的練習絕對地址：在數(shù)據(jù)變動時，運用公式后的數(shù)據(jù)不會變動相對地址：在數(shù)據(jù)變動時，運用公示后的數(shù)據(jù)會做變動第28頁，共44頁。常用函數(shù)-提取函數(shù)Left：從左側(cè)開

12、始提取需要的字段Right：從右側(cè)開始提取需要的字段Mid：從第幾位開始提取需要位數(shù)的字段語法： Left(text,num_chars) Text是包含要提取的字符的文本字符串。 Num_chars指定要由 LEFT 提取的字符的數(shù)量。舉例：zhang12356 left(zhang12356,4) 得到zhan Right（zhang12356,2) 得到56 Mid(zhang12356,6,3) 得到123第29頁，共44頁。常用函數(shù)-連接函數(shù)1、&2、=CONCATENATE()第30頁，共44頁。常用函數(shù)-統(tǒng)計函數(shù)1、max,min2、sum,average3、count4、tex

13、t,value5、rank6、sumif,countif,sumproduct7、match、index8、trunk.第31頁，共44頁。Now()Today()這兩個函數(shù)均為當前時間的計算，不同是now會出現(xiàn)日期和幾點鐘，而today只是日期常用函數(shù)-常量函數(shù)第32頁，共44頁。常用函數(shù)-條件函數(shù)“=IF()”是條件函數(shù)，最多可套嵌7層語法：=IF(logical_test,value_if_true,value_if_false)舉例：=IF(C4=0,0,IF(C4=1500,C4*0.03,IF(C4=4500,C4*0.1-105,IF(C4=9000,C4*0.2-555,IF(

14、C4=35000,C4*0.25-1005,IF(C4=55000,C4*0.3-2755,IF(C4=80000,C4*0.35-5505)級數(shù)含稅級距稅率（%）速算扣除數(shù)10-15003%021500-450010%10534500-900020%55549000-3500025%1005535000-5500030%2755655000-8000035%5505780000以上45%13505第33頁，共44頁。常用函數(shù)-Vlookup函數(shù)功能：縱向查找進行數(shù)據(jù)的匹配，在表格數(shù)組的首列查找指定的值，并由此返回表格數(shù)組當前行中其他列的值。語法：VLOOKUP(lookup_value,ta

15、ble_array,col_index_num,rangelookup) VLOOKUP（查找值，區(qū)域，列序號，邏輯值）1、選擇需要查找的字段2、選定需要查找類別的區(qū)域3、填寫需要顯示內(nèi)容的字段位于選定區(qū)域的那一列4、確定匹配的程度，0為完全匹配課后作業(yè)：Hlookup,lookup第34頁，共44頁。函數(shù)語法自寫語句-掌握好邏輯關系即可office軟件自帶第35頁，共44頁。思考-如何選擇與使用函數(shù)如果不能確定使用哪一個函數(shù)，您應該怎么辦？or去翻統(tǒng)計課本？or嘗試使用所有函數(shù)，找出適用的函數(shù)？or查看“插入函數(shù)”對話框或“幫助”主題鏈接？學會用不同的辦法解決問題學會用已有的知識解決問題提問：如何比較兩個對同一數(shù)據(jù)的計算結(jié)果（1、分別排序后對比；2、兩列相減看結(jié)果；3、if語句判斷；4、exact函數(shù).）？第36頁，共44頁。數(shù)據(jù)分析第37頁，共44頁。以自己的方式查看數(shù)據(jù)-數(shù)據(jù)透視表不必寫入復雜的公式，您可以使用向?qū)?chuàng)建一個交互式表格來自動提取、組織和匯總您的數(shù)據(jù)。建好數(shù)據(jù)透視表后，可以對數(shù)據(jù)透視表重新安排，以便從不同的角度查看數(shù)據(jù)。合理運用數(shù)據(jù)透視表進行計算與分析，能使許多復雜的問題簡單