excel經(jīng)典培訓(xùn)(技巧總結(jié)圖表函數(shù)透視表自動(dòng)化)

1、中文電子表格Excel 2 0 0 0的功能和使用1 熟悉Excel 20002 編輯工作表3 格式化工作表4 公式與函數(shù)5 Excel高級(jí)操作6 圖表和圖形學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)l啟動(dòng)和退出 Excel、Excel的基本界面組成、工具欄操作l輸入數(shù)據(jù)、工作簿操作、工作表操作l編輯數(shù)據(jù)的操作、復(fù)制、移動(dòng)和刪除數(shù)據(jù)l改變數(shù)字、文本、單元格、自動(dòng)套用、復(fù)制、條件格式化、改變行寬和列高l公式計(jì)算、函數(shù)計(jì)算l頁(yè)面設(shè)置、打印設(shè)置、打印預(yù)覽、打印工作薄l數(shù)據(jù)排序與篩選、創(chuàng)建圖表1 熟悉Excel 2 0 0 0 1.1 Excel 2000的啟動(dòng)與退出 1.2 Excel 2000的用戶界面與操作 返回目錄1.

2、1 Excel 2 0 0 0的啟動(dòng)與退出1Excel 2000的啟動(dòng)（1）在“開始”中選擇“程序Microsoft Excel”。（2）在桌面上雙擊Excel 2000中文版的快捷圖標(biāo)。2退出Excel 2000（1）在Excel 2000菜單欄上選擇“文件退出”選項(xiàng)。（2）按Alt+ F4鍵，或單擊 Excel2000中文版窗口右上角的關(guān)閉按鈕。圖1 Excel 2000應(yīng)用程序窗口 返回本節(jié)1.2 Excel 2000的用戶界面與操作1工作簿、工作表和單元格（1）工作簿。（2）工作表。（3）單元格。（4）活動(dòng)單元格。2Excel 2000的用戶界面（1）工作區(qū)窗口（2）工作簿窗口（3）標(biāo)

3、題欄（見圖1）（4）菜單欄（5）工具欄（如圖2、3所示）（6）編輯欄（如圖4所示） （7）狀態(tài)欄（8）工作表標(biāo)簽（見圖1）（9）工作表拆分框圖2 “常用”工具欄按鈕功能說明圖3 “格式”工具欄按鈕功能說明 圖4 編輯欄3菜單欄和工具欄的操作（1）菜單欄的操作。 （2）工具欄的操作。（3）移動(dòng)菜單或工具欄（如圖6、7所示） 。圖5 “自定義”對(duì)話框 圖6 工具欄的移動(dòng)柄圖7 工具欄的移動(dòng)柄 返回本節(jié) 2 編輯工作表2.1 復(fù)制和移動(dòng)單元格的內(nèi)容2.2 填充單元格區(qū)域2.3 刪除與清除2.4 查找、替換與定位2.5 插入2.6 工作表的操作2.7 頁(yè)面設(shè)置2.8 打印工作表返回目錄2.1 復(fù)制和移

4、動(dòng)單元格的內(nèi)容（如圖9、10所示）。 1單個(gè)單元格內(nèi)容復(fù)制 2用剪貼板復(fù)制和移動(dòng)單元格內(nèi)容 3用拖動(dòng)鼠標(biāo)的方法復(fù)制和移動(dòng)單元格的內(nèi)容圖9 “選擇性粘貼”對(duì)話框 2.2 填充單元格區(qū)域1填充數(shù)據(jù)2填充公式CTRL+回車/D/R3在當(dāng)前工作表中自動(dòng)填充2.3 刪除與清除 1清除單元格中的信息選定單元格區(qū)域，在菜單欄上選擇“編輯清除”選項(xiàng)，彈出“清除”子菜單。（ DEL）2刪除單元格中的信息（1）刪除整行或整列。（2）刪除單元格或單元格區(qū)域。 返回本節(jié)2.4 查找、替換與定位1查找如圖13所示可通過對(duì)話框上的選項(xiàng)確定查找范圍及查找內(nèi)容。 單元格匹配 按值搜索 查找格式圖13 “查找”對(duì)話框2替換圖1

5、4 “替換”對(duì)話框 搜索和替換的字符可以包括文字、數(shù)字、公式或公式的部分。在菜單欄上選擇“編輯選項(xiàng)”或按Ctrl+H鍵，彈出“替換”對(duì)話框，如圖14所示。 返回本節(jié)3定位定位條件的運(yùn)用：2.5 插入1插入單元格SHIFT鍵和鼠標(biāo)拖曳法2插入行列無法插入（工作表以外的區(qū)域）時(shí)，該如何處理？返回本節(jié)2.6 工作表的操作1工作表的選擇與翻動(dòng)（1）選擇相鄰的一組工作表（SHIFT）（2）選擇不相鄰的一組工作表（CTRL）（3）選擇全部工作表（4）取消工作表組的設(shè)置 2移動(dòng)或復(fù)制工作表用鼠標(biāo)直接移動(dòng)或復(fù)制(CTRL)工作表。3刪除工作表4插入工作表5凍結(jié)與取消凍結(jié)窗格2.7 頁(yè)面設(shè)置圖17 “頁(yè)面設(shè)置”

6、對(duì)話框在菜單欄上選擇“文件頁(yè)面設(shè)置”選項(xiàng)、彈出“頁(yè)面設(shè)置”對(duì)話框，如圖17所示。 1設(shè)置頁(yè)面2設(shè)置頁(yè)邊距（如圖18所示）3設(shè)置頁(yè)眉和頁(yè)腳4設(shè)置工作表的選項(xiàng)（打印標(biāo)題）5插入分頁(yè)符 （1）插入分頁(yè)符。 （2）刪除分頁(yè)符。6設(shè)置打印區(qū)域運(yùn)用：對(duì)不連續(xù)區(qū)域如何打印在同一頁(yè)面上？（照相機(jī)）圖18 “頁(yè)邊距”選項(xiàng)卡圖19 “頁(yè)眉/頁(yè)腳”選項(xiàng)卡 返回本節(jié)2.8 打印工作表1打印預(yù)覽在菜單欄上選擇“文件打印預(yù)覽”選項(xiàng)，在預(yù)覽窗口頂部單擊“關(guān)閉”按鈕，可返回工作表。2設(shè)置打印機(jī)和打印在菜單欄上選擇“文件打印”選項(xiàng)，彈出“打印”對(duì)話框。單擊“確定”按鈕開始打印。返回本節(jié)3 格式化工作表3.1 列寬和行高的調(diào)整3

7、.2 設(shè)置單元格的字體3.3 單元格內(nèi)容的對(duì)齊3.4 表格線與邊框線3.5 設(shè)置單元格的顏色和圖案3.6 設(shè)置單元格的數(shù)字格式3.7 使用條件格式 3.8 使用格式刷3.9 保護(hù)單元格或單元格區(qū)域3.10 自動(dòng)套用格式返回目錄3.1 列寬和行高的調(diào)整1設(shè)置工作表的列寬（1）在菜單欄上選擇“格式列列寬”選項(xiàng)，單擊“確定”按鈕。（2）在菜單欄上選擇“格式列最適合的列寬”選項(xiàng)，單擊“確定”按鈕。（3）將鼠標(biāo)移到所選列標(biāo)的右邊框，拖動(dòng)該邊框。2設(shè)置工作表行高（1）在菜單欄上選擇“格式行行高”選項(xiàng)，單擊“確定”按鈕。（2）在菜單欄上選擇“格式行最適合的行高”選項(xiàng)，單擊“確定”按鈕。（3）將鼠標(biāo)移到所選行

8、標(biāo)的下邊框，該行的高度自動(dòng)設(shè)置為最高項(xiàng)的高度。返回本節(jié)3.2 設(shè)置單元格的字體1用菜單命令設(shè)置字體方法為：選定單元格區(qū)域；在菜單欄上選擇“格式單元格”選項(xiàng)，完成設(shè)置后，可在“預(yù)覽”框預(yù)覽當(dāng)前選定的字體及其格式式樣。2用工具欄上的按鈕設(shè)置字體格式工具欄上有字體、字號(hào)等按鈕，可對(duì)選定的單元格區(qū)域設(shè)置字體。返回本節(jié)3.3 單元格內(nèi)容的對(duì)齊1用菜單命令設(shè)置對(duì)齊方式（1）“水平對(duì)齊”框（2）“垂直對(duì)齊”框 （3）“文本控制”框（4）“方向”框2用“格式”工具欄設(shè)置對(duì)齊方式“格式”工具欄上有四個(gè)常用的單元格對(duì)齊方式按鈕：左對(duì)齊、居中、右對(duì)齊、合并及居中。返回本節(jié)3.4 表格線與邊框線1在單元格區(qū)域周圍加邊

9、框2刪除邊框運(yùn)用：斜線標(biāo)頭的制作返回本節(jié)3.5 設(shè)置單元格的顏色和圖案 選定單元格區(qū)域；在菜單欄上選“格式單元格”選項(xiàng)，彈出“單元格格式”對(duì)話框；在“圖案”選項(xiàng)卡中設(shè)置底紋顏色和圖案，在“示例”顯示框中預(yù)覽設(shè)置的效果；單擊“確定”按鈕。返回本節(jié)3.6 設(shè)置單元格的數(shù)字格式1數(shù)字格式概述Excel提供的數(shù)字格式有：常規(guī)、數(shù)值、貨幣、會(huì)計(jì)專用、日期、時(shí)間、百分比、分?jǐn)?shù)、科學(xué)記數(shù)、文本、自定義等。默認(rèn)的數(shù)字格式是“常規(guī)”格式。輸入時(shí)，系統(tǒng)根據(jù)單元格中輸入的數(shù)值進(jìn)行適當(dāng)?shù)母袷交?2設(shè)置單元格數(shù)字格式圖20 “單元格格式”對(duì)話框3單元格數(shù)字格式的運(yùn)用日期分?jǐn)?shù)身份證號(hào)、銀行卡號(hào)返回本節(jié)3.7 使用條件格

10、式 設(shè)置條件格式(如圖21所示)圖21 “條件格式”對(duì)話框圖22 在“條件格式”對(duì)話框中設(shè)置條件圖23 設(shè)置條件格式示例返回本節(jié)3.8 使用格式刷 選定要復(fù)制的單元格區(qū)域；在“常用”工具欄上單擊“格式刷”按鈕，單元格區(qū)域的選中標(biāo)記由黑實(shí)線框變?yōu)闈L動(dòng)的短虛線框；單擊鼠標(biāo)左鍵，則該單元格區(qū)域設(shè)置為相同的格式，格式刷恢復(fù)原狀。雙擊“格式刷”按鈕，可多次選定單元格進(jìn)行格式刷操作返回本節(jié)3.9 保護(hù)單元格或單元格區(qū)域（1）選定要鎖定、隱藏的單元格區(qū)域。（2）在菜單欄上選擇“格式單元格”選項(xiàng)，彈出“單元格格式”對(duì)話框，在“保護(hù)”選項(xiàng)卡上選擇相應(yīng)的選項(xiàng)。（3）單擊“確定”。（4）在菜單欄上選擇“工具保護(hù)保護(hù)

11、工作表”選項(xiàng)，彈出“保護(hù)工作表”對(duì)話框。（5）輸入保護(hù)口令并設(shè)置相應(yīng)的選項(xiàng)。（6）單擊“確定”，并在“確認(rèn)口令”對(duì)話框中再次輸入口令。另外：打開、修改工作表設(shè)置密碼 返回本節(jié)3.10 自動(dòng)套用格式選定要套用自動(dòng)格式的單元格區(qū)域，在菜單欄上選擇“格式自動(dòng)套用格式”選項(xiàng)，彈出“自動(dòng)套用格式”對(duì)話框；在“格式”列表框中選擇一個(gè)格式。單擊“確定”。返回本節(jié)4 公式與函數(shù)4.1 公式的使用 4.2 函數(shù)的使用返回目錄4.1 公式的使用1、什么是公式 函數(shù)與公式既有區(qū)別又互相聯(lián)系。由用戶自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的公式。以公式“=SUM(E1:H1)*A1+26”為例，它要以等號(hào)“=”開始，其內(nèi)部可以

12、包括函數(shù)、引用、運(yùn)算符和常量。上式中的“SUM(E1:H1)”是函數(shù)，“A1”則是對(duì)單元格A1 的引用(使用其中存儲(chǔ)的數(shù)據(jù))，“26”則是常量，“*”和“+”則是算術(shù)運(yùn)算符。如果函數(shù)要以公式的形式出現(xiàn)，它必須有兩個(gè)組成部分，一個(gè)是函數(shù)名稱前面的等號(hào)，另一個(gè)則是函數(shù)本身。2公式中的運(yùn)算符（1）算術(shù)運(yùn)算符。+ - * / % (乘方)（2）比較運(yùn)算符。= = = （3）文本運(yùn)算符。&（4）引用運(yùn)算符。引用運(yùn)算符可以將單元格區(qū)域合并運(yùn)算，包括冒號(hào)（：）、逗號(hào)（，）和空格。其中： 1）冒號(hào)（：）是區(qū)域運(yùn)算符。 2）逗號(hào)（，）是聯(lián)合運(yùn)算符。 3）空格是交叉運(yùn)算符。3在公式中引用其他單元格（1）引用的類型

13、(圖27所示)。1）相對(duì)引用 2）絕對(duì)引用 3）混合引用（2）引用同一工作簿中其他工作表的單元格。（3）引用其他工作簿的單元格。表1 數(shù)值轉(zhuǎn)換示例公式運(yùn)算結(jié)果說明=“2”+“6”8使用算術(shù)運(yùn)算符時(shí)，雖然“2”和“6”是文本類型的數(shù)據(jù)，但Excel認(rèn)為運(yùn)算是數(shù)字項(xiàng)，自動(dòng)將其轉(zhuǎn)換為數(shù)字=“99/09/08”-“99/09/02”6Excel將具有yy/mm/dd格式的文本當(dāng)作日期，將日期轉(zhuǎn)換成為序列號(hào)后，再進(jìn)行計(jì)算=SUM（“3 * 5”,4）VALUE！Excel不能將文本“3 * 5”轉(zhuǎn)換成數(shù)字3“abcdef”3abcdef當(dāng)公式需要文本型數(shù)值時(shí)，Execl自動(dòng)將數(shù)字轉(zhuǎn)換為文本4.2 函數(shù)的

14、使用1.什么是函數(shù) Excel 函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。以常用的求和函數(shù)SUM 為例，它的語(yǔ)法是“SUM(number1,number2,.)”。其中“SUM”稱為函數(shù)名稱，一個(gè)函數(shù)只有唯一的一個(gè)名稱，它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號(hào)，接著是用逗號(hào)分隔的稱為參數(shù)的內(nèi)容，最后用一個(gè)右括號(hào)表示函數(shù)結(jié)束。 2函數(shù)的語(yǔ)法（1）公式必須以“=”開頭。（2）函數(shù)的參數(shù)用圓括號(hào)“（ ）”括起來。（3）函數(shù)的參數(shù)多于一個(gè)時(shí)，要用“，”號(hào)分隔。（4）參數(shù)的文本要用英文的雙引號(hào)括起來。（5）參數(shù)可以是已定義的單元格名或區(qū)域名。（6）函數(shù)中使用數(shù)組參數(shù)可以由數(shù)值、文本

15、和邏輯值組成。（7）可以混合使用區(qū)域名、單元格引用和數(shù)值作為函數(shù)的參數(shù)。3函數(shù)的參數(shù)類型（1）數(shù)字，如21、-7、37.25等。（2）文字，如“a”、“Word”、“Excel”等。若在文字中使用雙引號(hào)，則在每個(gè)雙引號(hào)處用兩個(gè)雙引號(hào)，如“TEXT”。（3）邏輯值，如TRUE、FALSE或者計(jì)算時(shí)產(chǎn)生邏輯值的語(yǔ)句（如A1035）。（4）錯(cuò)誤值，如#REF!。（5）引用，如D11、C5:C10。3組合函數(shù)組合函數(shù)可以充分利用工作表中的數(shù)據(jù)，還可以充分發(fā)揮 Excel快速計(jì)算和重復(fù)計(jì)算公式的能力。函數(shù)被用作參數(shù)時(shí)，不需要前置“=”號(hào)。4函數(shù)的輸入方法 5使用“自動(dòng)求和”工具按鈕圖29 “粘貼函數(shù)”對(duì)

16、話框圖30 函數(shù)參數(shù)輸入對(duì)話框 圖31 “自動(dòng)求和”功能示例 返回本節(jié)5 Excel高級(jí)操作5.1 數(shù)據(jù)排序5.2 數(shù)據(jù)篩選5.3 分類匯總5.4 數(shù)據(jù)透視表 返回目錄5.1 數(shù)據(jù)的排序(圖35所示操作步驟如下):（1）選擇單元格，或選擇成績(jī)單數(shù)據(jù)中的任一單元格。（2）在菜單欄上選擇“數(shù)據(jù)排序”選項(xiàng)，彈出“排序”對(duì)話框。（3）在“排序”對(duì)話框中指定排序的主要關(guān)鍵字和次要關(guān)鍵字、遞增或遞減排序選項(xiàng)，單擊“遞減”按鈕。（4）選擇排序區(qū)域。（5）單擊“確認(rèn)”按鈕。圖35 “排序”對(duì)話框返回本節(jié)5.2 數(shù)據(jù)篩選（圖36、37所示）1自動(dòng)篩選自定義自動(dòng)篩選圖36 “自定義自動(dòng)篩選方式”對(duì)話框 圖37 自

17、動(dòng)篩選的結(jié)果5.3 分類匯總（1）選擇要進(jìn)行分類匯總的單元格區(qū)域。（2）在菜單欄上選擇“數(shù)據(jù)分類匯總”選項(xiàng)，彈出“分類匯總”對(duì)話框如圖40所示。（3）在“分類匯總”對(duì)話框上進(jìn)行選擇。（4）單擊“確定”（如圖41所示）。 運(yùn)用：分類匯總結(jié)果的復(fù)制多級(jí)分類匯總圖41 分類匯總結(jié)果示意圖圖40 “分類匯總”對(duì)話框返回本節(jié)5.4 數(shù)據(jù)透視表 （1）創(chuàng)建匯總表格。（2）重新組織表格。（3）篩選數(shù)據(jù)透視表數(shù)據(jù)和創(chuàng)建數(shù)據(jù)透視表數(shù)據(jù)組。（4）創(chuàng)建數(shù)據(jù)透視表圖表。1建立數(shù)據(jù)透視表（如圖4247所示） （1）在菜單欄上選擇“數(shù)據(jù)、數(shù)據(jù)透視表和圖表報(bào)告”選項(xiàng)，彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂?duì)話框。（2）指定待分

18、析數(shù)據(jù)的數(shù)據(jù)源類型和需創(chuàng)建的報(bào)表類型，單擊“下一步”。（3）選擇要建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域，單擊“下一步”按鈕。（4）單擊“新建工作表”單選項(xiàng)，再單擊“完成”。（5）根據(jù)需要用鼠標(biāo)分別將“數(shù)據(jù)透視表”工具欄中的字段按鈕拖放到透視表的相應(yīng)位置。 圖42 數(shù)據(jù)透視表示例圖43 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之1”對(duì)話框圖44 “數(shù)據(jù)透視表和透視圖向?qū)?步驟之2”對(duì)話框圖45 “數(shù)據(jù)透視表和透視圖向?qū)?步驟之3”對(duì)話框圖46 “數(shù)據(jù)透視表”工具欄圖47 新建立的數(shù)據(jù)透視表圖48 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)О媸健睂?duì)話框 圖49 “數(shù)據(jù)透視表字段”對(duì)話框2編輯數(shù)據(jù)透視表（1）更改數(shù)據(jù)透視表的布局

19、。 1）行、列字段互換 2）在字段內(nèi)移動(dòng)數(shù)據(jù) 3）隱藏和顯示數(shù)據(jù) 4）添加字段 5）刪除字段 6）設(shè)置數(shù)據(jù)透視表選項(xiàng)圖51 “數(shù)據(jù)透視表選項(xiàng)”對(duì)話框（2）更新數(shù)據(jù)。在菜單欄上選擇“數(shù)據(jù)更新數(shù)據(jù)”選項(xiàng)；在數(shù)據(jù)區(qū)域的快捷菜單中選擇“更新數(shù)據(jù)”選項(xiàng)；在“數(shù)據(jù)透視表”工具欄上單擊“更新數(shù)據(jù)”按鈕，在“數(shù)據(jù)透視表”工具欄上單擊“數(shù)據(jù)透視表”按鈕，在彈出的菜單中選擇“更新數(shù)據(jù)”選項(xiàng)。3數(shù)據(jù)透視圖（如圖52所示）（1）在菜單欄上選擇“數(shù)據(jù)數(shù)據(jù)透視表和圖表報(bào)告”選項(xiàng)。（2）選擇“Microsoft Excel數(shù)據(jù)清單或數(shù)據(jù)庫(kù)”和“數(shù)據(jù)透視圖”，單擊“下一步”按鈕。（3）選擇數(shù)據(jù)源區(qū)域，單擊“下一步”按鈕。（4）根據(jù)需要選擇“新建工作表”或“現(xiàn)有工作表”選項(xiàng)，再單擊“完成”