1、EXCEL常用功能講解蔣芳 2011-10-25第1頁，共37頁。EXCEL水平測試10個EXCEL問題（每個2分） 1、您懂得使用數據透視功能進行靈活、快速的數據匯總2、您很清楚EXCEL2003表格具有256列的數據能力3、您可以區分柱狀圖與餅形圖的數據表述區別4、您可以區分普通圖柱狀圖和百分比堆積柱狀圖數據表述區別5、您經常使用CRTL+1這個快捷鍵6、您善于使用IF函數進行數據條件處理7、您善于使用VLOOKUP函數進行數據處理8、您懂得通過條件格式進行錄入字符的控制9、您懂得通過保護工作表控制單元格錄入限制10、您清楚EXCEL2003文件格式為“.xls”,而EXCEL2007為“

2、.xlsx”第2頁，共37頁。EXCEL水平測試結果測試評定A類：0-3分您可以轉頭走了，就當沒參加過這個測試吧。如果您真想在EXCEL實戰方面有所提高，或者必須面對EXCEL工作，那您可真需要下大功夫了。 B類：4-8分您應該具有一些EXCEL實戰能力，不過經驗尚有不足，當您面對稍有艱巨的EXCEL業務數據處理或PPT商務呈現任務的時候可能會稍許感覺頭疼，胸悶，無從下手，一般得借助別人支持或援助才可渡過難關。 C類：9-12分您應該具備了一定的EXCEL實戰經驗，或者參加過專業培訓學習，具有一的EXCEL商務辦公實戰能力，可以應對日常商務OFFICE工作。 D類：13-16分您應該是企業中的

3、OFFICE高手，同事中的榜樣，您可以較為自如的處理EXCEL日常工作問題，或找到解決建議。經過深入訓練，應該可以達到專業級EXCEL水平。 E類：17-20分E類：高手，絕對的OFFICE高手！第3頁，共37頁。EXCEL高手的必修課第4頁，共37頁。1234特殊輸入及字符處理提高工作效率的快捷方式提高工作效率的快捷鍵操作EXCEL大禮包：Excel提高實例快速輸入技巧第5頁，共37頁。1、直接輸入字符串的方法，先輸入單引號2、輸入分數，0 加空格；分式運算3、巧用字符連接運算符 &（1）給每個數字（字符）前添加相同字符串 abc =abc &A2（2）把每個數字轉換成9位字符，不足者前面添

4、0 =REPT(0,9-LEN(A2) &A24、在相對引用、絕對引用和混合引用間切換 （ $ ）。$A$1絕對列和絕對行 A$1相對列和絕對行$A1絕對列和相對行 A1相對列和相對行一、特殊輸入及字符處理注意：輸入字符串時，請確保是否在英文的半角狀態下輸入。第6頁，共37頁。5、填充，例如：（1）如果您希望使用序列 1、 2、 3、 4，請在前兩個單元格中鍵入 1 和 2；（2）如果您希望使用序列 2、 4、 6、 8，請在前兩個單元格中鍵入 2 和 4；（3）如果您希望使用序列 1、1、1、1 ，請在前兩個單元格中鍵入 1 和 1；（4）如果您希望在一個區域內全部輸入1，請選中該區域，輸入

5、1，,按 Ctrl+Enter 鍵, 此法可對多工作表多區域同時輸入一、特殊輸入及字符處理第7頁，共37頁。1、高效選擇區域（1）當區域較小時，可直接用鼠標拖動選擇（2）區域稍大，但在屏幕范圍內，用鼠標點擊左上角單元格，按住Shift鍵點擊右下角單元格（3）不連續區域選擇：按住 Ctrl鍵用鼠標選擇或在名稱框輸入各區域名稱，區域名稱間用逗號分隔如： A3:B7,A10:D17（4）選定從工作表開始到結尾的方法：選中開始單元格（如A21），同時按下ctrl+shift+end. ctrl+shift+home從工作表結尾到開始.（5）ctrl+shift+（向左小箭頭）， ctrl+shift+

6、（向右小箭頭）， ctrl+shift+（向上小箭頭）， ctrl+shift+（向下小箭頭）二、提高工作效率的快捷方式請記住：（1）CLTR+箭頭表示快速到達；CHTRL+SHIFT+箭頭表示快速選定。（2）用Shift配合鼠標選擇連續區域或連續的工作表，用Ctrl鍵配合鼠標選擇不連續區域或不連續的工作表第8頁，共37頁。2、錄入與公式填充鍵盤：Ctrl+D 復制上一單元格的公式或內容；填充縱向區域（與雙擊黑十字功能相仿）鍵盤：Ctrl+R;復制左邊單元個的公式或內容；填充橫向區域3、批量處理數據（1）給區域每個單元格的值均輸入100000（2）將區域的數字折成千元在區域外的任意單元格（如：

7、A22）輸入1000,并復制該單元格；選中區域，選擇性粘貼-除，確定4、工作表（1）按住Ctrl,用鼠標左鍵拖動屏幕下邊的工作表標簽，可復制一個相同的表格；（2）選中幾個工作表再進行畫表等操作，可對多個工作表進行操作；（3）選中多個工作表設置頁腳頁碼并打印預覽，工作簿頁碼連續；二、提高工作效率的快捷方式第9頁，共37頁。5、如何使空白單元格等于上一個單元格 （1）選中數據區域（2）CTRL+G打開定位窗口，定位條件，空值（3）輸入“+”。（4）選中上一單元格。 即：=+要等于的單元格二、提高工作效率的快捷方式第10頁，共37頁。6、建立分類下拉列表填充項。“數據有效性”(1)、在Sheet2中

8、，將企業建立一個企業名稱數據庫。(2)、選中A列(“工業企業名稱按類別(如“工業企業”、“商業企業”、“個體企業”等)分別輸入不同列中，”名稱所在列)，在“名稱”欄內，輸入“工業企業”字符后，按“回車”鍵進行確認。 仿照上面的操作，將B、C列分別命名為“商業企業”、“個體企業”(3)、切換到Sheet1中，選中需要輸入“企業類別”的列(如C列)，執行“數據有效性”命令，打開“數據有效性” 對話框。在“設置”標簽中，單擊“允許”右側的下拉按鈕，選中“序列”選項，在下面的“來源”方框中，輸入“工業企業”，“商業企業”，“個體企業”序列(各元素之間用英文逗號隔開)，確定退出。 再選中需要輸入企業名稱

9、的列(如D列)，再打開“數據有效性”對 話框，選中“序列”選項后，在“來源”方框中輸入公式：=INDIRECT(C1)，確定退出。二、提高工作效率的快捷方式第11頁，共37頁。6、建立分類下拉列表填充項。“數據有效性”(4)、選中C列任意單元格(如C4)，單擊右側下拉按鈕，選擇相應的“企業類別”填入單元格中。然后選中該單元格對應的D列單元格(如D4)，單擊下拉按鈕，即可從相應類別的企業名稱列表中選擇需要的企業名稱填入該單元格中。 提示：在以后打印報表時，如果不需要打印“企業類別”列，可以選中該列，右擊鼠標，選“隱藏”選項，將該列隱藏起來即可。二、提高工作效率的快捷方式第12頁，共37頁。1、A

10、LT系列快捷鍵（1）Alt+enter拆分同一單元格內多行文字（2）Alt+=，自動求和（3）Alt+0165 = （4）ALT+0178=（5）ALT+0179=（6）ALT+0177=（7）ALT+0137=2、SHIFT系列快捷鍵（1）按住shift拖動單元格=剪切+插入單元格+粘貼（2）選中一個單元格，按shift+F2鍵，可以插入批注。三、提高工作效率的快捷鍵第13頁，共37頁。3、CTRL系列快捷鍵（1）按住ctrl+shift拖動單元格=復制+插入單元格+粘貼（2）Ctrl +9隱藏一行單元格 Ctrl+Shift+9取消隱藏行（3）Ctrl+0隱藏一列單元格 Ctrl+Shif

11、t+0取消隱藏列（4）Ctrl+;輸入今天的日期 Ctrl+Shift+;輸入當前的時間（5）Ctrl+“-”（減號）刪除單元格/行/列 Ctrl+Shift +“+”（加號）插入單元格/行/列（6） Ctrl+1快速打開格式對話框（7）+可以將當前工作表中的公式顯示出來,再按+則恢復.（8）Ctrl+f9最小化窗口（9）Ctrl+f10最大化和還原窗口 (10) Ctrl+Shift+_刪除選定單元格區域的邊框三、提高工作效率的快捷鍵第14頁，共37頁。3、CTRL系列快捷鍵（1）Ctel+Tab 可在程序與程序，表簿與表簿之間轉換打開。（2)使用ctrl+PageDown和ctrl+Pag

12、eUp可以在工作表之間向前向后切換（3)如果你用的是帶滾輪的鼠標，那么先按住“Ctrl”鍵，再撥動滾輪吧！可以快速改變顯示比例喲！注意:有時在中文輸入狀態下會有所沖突,將輸入法轉換為英文即可.三、提高工作效率的快捷鍵第15頁，共37頁。4、功能鍵系列快捷鍵 F2用來修改單元格內容，像改文件名一樣簡單 F3用來打開粘貼名稱對話框 F4重復上一步操作.這招在隔行刪除、多點復制等方面用起來很爽 F5用來打開定位對話框5、工具選項使用技巧（1）工具-選項-視圖中,在“窗口選項”下去掉“零值”后,余額為0就不顯示了。（2）工具選項視圖網格線前面的對勾去掉可以不顯示網格線（3）工具選項批注.三、提高工作效

13、率的快捷鍵第16頁，共37頁。1、快速輸入大量含小數點的數字 單擊“工具”/“選項”/“編輯”標簽，在彈出的對話框中選中“自動設置小數點”復選框，然后在“位數”微調編輯框中鍵入需要顯示在小數點右面的位數就可以了。以后我們再輸入帶有小數點的數字時，直接輸入數字，而小數點將在回車鍵后自動進行定位2、快速輸入大量相同數據 首先同時選中需要填充數據的單元格。若某些單元格不相鄰，可在按住Ctrl鍵的同時，點擊鼠標左鍵，逐個選中；其次輸入要填充的某個數據，按住Ctrl鍵的同時，按回車鍵，則剛才選中的所有單元格同時填入該數據3、快速刪除工作表中空行 4、快速輸入多個重復數據 （錄制宏）5、快速處理多個工作表

14、 四、快速輸入技巧第17頁，共37頁。1234查找和引用函數邏輯運算符日期與時間函數如何利用函數操作文本函數5計數和求和函數EXCEL大禮包：EXCEL函數大全6其它函數第18頁，共37頁。一、Vlookup函數1、用途：VLOOKUP用于在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。2、語法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)3、參數： Lookup_value表示要查找的值，它必須位于自定義查找區域的最左列。Table_array為需要查找的區域，可以使用對區域或區域名

15、稱的引用。Col_index_num為查找區域中待返回的匹配值的列序號。Range_lookup為一邏輯值，指明查找時是精確匹配，還是近似匹配。 一、查找和引用函數第19頁，共37頁。二、 Match函數1、用途：返回在指定方式下與指定數值匹配的數組中元素的相應位置。2、語法：MATCH(lookup_value，lookup_array，match_type)3、參數： lookup_value ：為要搜索的值。 lookup_array：要查找的區域(必須是一行或一列)。 match_type：匹配形式，有0、1和1三種選擇：0表示一個準確的搜索。1表示搜索小于或等于查換值的最大值，查找區

16、域必須為升序排列。1表示搜索大于或等于查找值的最小值，查找區域必須降序排開。以上的搜索，如果沒有匹配值，則返回N/A。 一、查找和引用函數第20頁，共37頁。三、 Transpose函數1、用途：返回區域的轉置。2、語法：TRANSPOSE(array)3、參數：Array是需要轉置的數組或工作表中的單元格區域 注意：必須以數組的公式輸入。請選中以公式單元格開始的區域，按 F2，再按 Ctrl+Shift+Enter一、查找和引用函數第21頁，共37頁。四、 ADDRESS、COLUMN、ROW函數1、 ADDRESS函數 用于按照給定的行號和列標，建立文本類型的單元格地址。 ADDRESS(

17、row_num,column_num,abs_num,a1,sheet_text)如： ADDRESS(4,5,1,FALSE,“Book1Sheet1”)， Book1Sheet1!R4C5 ADDRESS(4,5,2,FALSE,“Book1Sheet1”)， Book1Sheet1!R4C5 ADDRESS(4,5,3,FALSE,“Book1Sheet1”) ，Book1Sheet1!R4C5 ADDRESS(4,5,4,FALSE,“Book1Sheet1”) ，Book1Sheet1!R4C5 ADDRESS(4,5,1,TRUE,“Book1Sheet1”) ，Book1Shee

18、t1!$E$4 2、 COLUMN函數 用于返回給定引用的列標3、 ROW函數 用于返回給定引用的行號一、查找和引用函數第22頁，共37頁。一、 AND、OR、NOT函數AND：所有參數的邏輯值為真時返回 TRUE；OR： 任何一個參數邏輯值為 TRUE，即返回 TRUE；NOT：對參數值求反二、IF函數用途： 執行真假值判斷后，根據邏輯測試的真假值返回不同的結果語法：IF(logical_test，value_if_true，value_if_false)。注意：IF函數可以嵌套七層COUNTIF用途：統計某一區域中符合條件的單元格數目。語法：COUNTIF(range，criteria)二

19、、邏輯運算符第23頁，共37頁。1、日期和時間都僅僅是數字而已 日期：從1900-1-1以1開始;時間：從0:00:00以0開始2、顯示時間 1）Now：顯示當前系統的日期和時間; 如何只顯示當前時間？=now()-today() 2）Time：顯示指定數值的時間 語法：time(hour,minute,second)3、顯示日期 1）Today：顯示當前系統的日期; 2）Date：顯示指定數值的日期； 3）Year：得到日期的年數 4）Month：得到日期的月數 5）Day：得到日期的天數三、日期與時間函數第24頁，共37頁。1、如何刪除Excel不能打印的字符？ Clean函數，語法：cl

20、ean（text）2、如何確定兩單元格是否相同？ 1）用運算符“=”判斷；不區分大小寫 返回結果：“TURE”、“FALSE” 2）用EXACT函數比較；區分大小寫語法：exact（text1,text2） 返回的結果：“TURE”、“FALSE”3、如何連接多個字符串？ 1）用運算符“&”連接； 2）用CONCATENATE函數連接；語法：concatenate（text1,text2text30）四、如何利用函數操作文本第25頁，共37頁。4、如何轉換文本大小寫？ 1）LOWER：所有大寫轉換為小寫；語法：lower（text） 2）UPPER：所有小寫轉換為大寫；語法：upper（tex

21、t） 3）PROPER：首字母及任何非字母后的首字段轉換大寫，其他字母轉換為小寫；語法：proper（text）5、如何取出單元格中的部分字符？ 1）Left：從左邊開始截取指定數目的字符； 語法：left（text,num_chars） 2）Right：從右邊開始截取指定數目的字符；語法：right（text,num_chars） 3）Mid：從文本指定位置開始截取指定數目的字符 語法：mid（text,start_num,num_chars）四、如何利用函數操作文本第26頁，共37頁。1、常用的計數函數 1）Count：統計時不包括空白、文本、 邏輯值和錯誤; 語法：count（value

22、1,value2） 2）Counta：統計時僅不包括空白單元格； 語法： counta（value1,value2） 3）兩者區別2、指定條件的計數函數 1）Countif 語法：countif（range,criteria） 2）舉例五、計數與求和函數第27頁，共37頁。3、常用的求和函數 1）Sum：進行基本數值求和; 語法：sum（number1, number2） 2）Sumif：根據條件求和； 語法：sumif（range,criteria,sum_range）4、分類匯總求和（計算） Subtotal函數 1）語法：subtotal(function_num,ref1,ref2)

23、2）Function_num的常用類型 1 求平均(average) 4 求最大值(max) 2 計數(count) 5 求最小值(min) 3 計數(counta) 9 求和(sum)五、計數與求和函數第28頁，共37頁。1、常用的數學函數 1）Rand:生成隨機數; 2）Int:將數值向下取整為最接近的整數； 3）Trunc:向下舍入（沿絕對值減少的方向） 4）Round：按指定位數四舍五入 5）Roundup：向上舍入（沿絕對值增大的方向） 6）Rounddown：向下舍入（沿絕對值減小的方向） 7）Abs:生成絕對值; 8）Mod:求兩數相除后的余數；2、其他數學函數 1）Averag

24、e:求平均數,注意不包括空白和字符 2）Max：查找一組數的最大值 3）Min：查找一組數的最小值六、其它函數第29頁，共37頁。3、IS類函數用途：其中包括用來檢驗數值或引用類型的九個工作表函數。函數如果為下面的內容，則返回 TRUEISBLANK值為空白單元格。ISERR值為任意錯誤值（除去 #N/A）。ISERROR值為任意錯誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL值為邏輯值。ISNA值為錯誤值 #N/A（值不存在）。ISNONTEXT值為不是文本的任意項（注意此函數在值為空白單元格時返回 TRUE）。ISNUMBER值為數字。ISREF值為引用。ISTEXT值為文本。六、其它函