高級(jí)文秘及辦公自動(dòng)化教程-電子表格Excel 2003

高級(jí)文秘及辦公自動(dòng)化教程 003 第 6章 電子表格 003 學(xué)習(xí)目的與要求 掌握并能夠熟練操作電子表格的建立及格式化，能夠?qū)Ρ砀竦臄?shù)據(jù)進(jìn)行操作 (編輯、計(jì)算、排序、篩選、記錄單、分類匯總、透視表 )，會(huì)插入圖表，多表操作，能夠?qū)Π踩赃M(jìn)行設(shè)置。 目錄 界面與常用術(shù)語(yǔ) 作表的常規(guī)操作 作表的計(jì)算 作表的數(shù)據(jù)庫(kù)功能 案例：小組成績(jī)分析 界面與常用術(shù)語(yǔ) 編輯欄 位于 口頂部的條形區(qū)域，用于輸入或編輯單元格或圖表中的值或公式。編輯欄的名稱區(qū)與編輯區(qū)中有 “ ”、“ ”、“ 個(gè)按鈕，分別表示取消、確認(rèn)和插入函數(shù)操作。 單元格 工作表中行列交匯的小格子，用于保存數(shù)值、文字、聲音等數(shù)據(jù)，它是 個(gè)單元格最多可容納 32 000個(gè)字符。 單元格地址 (編號(hào) ) 表示單元格在工作表上所處位置的坐標(biāo)。例如，第 C 列和第 3 行交叉處的單元格，其編號(hào)為“ 行號(hào)范圍 1 65 535，列標(biāo)范圍 A Z， Z， ， 56列。 活動(dòng)單元格 當(dāng)移動(dòng)鼠標(biāo)到工作表內(nèi)時(shí)其形狀變?yōu)榭招氖旨?，單擊某單元格，這時(shí)該單元格四周框線變粗，該單元格又稱為當(dāng)前單元格，俗稱激活單元格。用戶只能向活動(dòng)單元格內(nèi)寫入信息，編輯欄的名稱框顯示的是當(dāng)前單元格 (或區(qū)域 )的地址，編輯區(qū)顯示的是當(dāng)前單元格的內(nèi)容 (或公式 )。 工作表 用于存儲(chǔ)和處理數(shù)據(jù)的主要文檔，即電子表格。工作表由排列成行或列的單元格組成，一個(gè)工作表最多含 16 777 216個(gè)單元格 (65 536256)。工作表總是存儲(chǔ)在工作簿中。 且可以對(duì)多張工作表的數(shù)據(jù)進(jìn)行匯總計(jì)算。 區(qū)域 工作表上的兩個(gè)或多個(gè)單元格，這些單元格可以相鄰或不相鄰。通過(guò)對(duì)區(qū)域的引用實(shí)現(xiàn)工作表的主要操作，如計(jì)算、生成圖表等。 工作簿 包含一個(gè)或多個(gè)工作表的文件 (擴(kuò)展名 該文件可用來(lái)組織各種相關(guān)信息，其默認(rèn)文件名為 個(gè)工作簿最多包含 255個(gè)工作表，默認(rèn)情況下一個(gè)工作簿含 3個(gè)工作表 (默認(rèn)名稱 工作表可以重命名、增加、刪除。 據(jù)輸入 1. 單元格寬度有限，怎么輸入長(zhǎng)字符串？ 如在 國(guó)北京清華大學(xué)出版社教材部”。 當(dāng)在活動(dòng)單元格內(nèi)輸入數(shù)據(jù)的寬度超過(guò)了單元格的寬度時(shí)，有三種處理方法：若右側(cè)單元格無(wú)內(nèi)容則當(dāng)前單元格自動(dòng)擴(kuò)寬，成為寬單元格；若右側(cè)單元格有內(nèi)容，則超寬部分自動(dòng)隱藏 (可通過(guò)雙擊列標(biāo)后的豎線或選擇 【 格式 】【 列 】【 最合適的列寬 】 命令，將隱藏內(nèi)容顯示出來(lái) )；用 現(xiàn)在一個(gè)單元格內(nèi)輸入幾行數(shù)據(jù)。 2. 數(shù)值字符串輸入 數(shù)值字串是數(shù)字的組合，不具有數(shù)字的含義 (如編號(hào)、身份證號(hào) )，不能進(jìn)行數(shù)學(xué)運(yùn)算，如求和、求平均等。其輸入方法有兩種：先輸入 單撇號(hào) “ ”，再輸入數(shù)字 (如在 001”，直接輸入 001結(jié)果變成了 1，應(yīng)該輸入 001)；或先設(shè)單元格格式為文本，然后再輸入數(shù)字串。設(shè)置單元格格式的方法是在 【 單元格格式 】 對(duì)話框中的【 數(shù)字 】 選項(xiàng)卡中設(shè)置。 3. 分?jǐn)?shù)的輸入 分?jǐn)?shù)有真分?jǐn)?shù)和假分?jǐn)?shù)兩種：輸入真分?jǐn)?shù)時(shí)，先輸入數(shù)字 0和空格，再輸入內(nèi)容；輸入假分?jǐn)?shù)時(shí)在分?jǐn)?shù)和分?jǐn)?shù)之間還要輸入一個(gè)空格。 4. 大數(shù)值的輸入 比如輸入身份證號(hào)7。 5. 邏輯型數(shù)據(jù)輸入 示，否則用“ 示。如在 =52”，則顯示值為“ 6. 時(shí)間和日期型數(shù)據(jù)的輸入 先試驗(yàn)一下：在某單元格內(nèi)輸入 3示結(jié)果“ 3月 2日”；在某單元格內(nèi)輸入 =3示結(jié)果為 1。 這說(shuō)明當(dāng)輸入的數(shù)據(jù)符合某種格式時(shí)， 以當(dāng)輸入 06/1/1時(shí)，系統(tǒng)認(rèn)為是日期 2006年 1月 1日；當(dāng)輸入 8:30，21:45時(shí)，系統(tǒng)認(rèn)為是時(shí)間；也可以在單元格內(nèi)同時(shí)輸入日期和時(shí)間，二者之間以空格分隔。 7. 智能填充數(shù)據(jù) 填充柄 活動(dòng)單元格右下角的小黑塊，拖動(dòng)它可以實(shí)現(xiàn)智能填充。具體填充方法：在當(dāng)前單元格中輸入數(shù)據(jù)序列中的任意一項(xiàng)，縱向或橫向拖動(dòng)填充柄至某單元格，松開(kāi)鼠標(biāo)。若數(shù)據(jù)序列用完，則從頭開(kāi)始循環(huán)填充。 填充相同數(shù)據(jù)項(xiàng) 對(duì)時(shí)間或日期數(shù)據(jù)，按住 字符串或數(shù)值型數(shù)據(jù)，直接拖動(dòng)填充柄。這種填充其實(shí)是復(fù)制單元格數(shù)據(jù)。 圖 【 序列 】 對(duì)話框 遞增方式填充數(shù)據(jù)項(xiàng) 對(duì)時(shí)間或日期數(shù)據(jù)，直接拖動(dòng)填充控制手柄；對(duì)數(shù)值型數(shù)據(jù)，按住 快速填充 不拖動(dòng)填充柄也能實(shí)現(xiàn)填充，主要是日期、等差或等比序列。例如在 1單元格填充等比數(shù)列 1， 3， 9， 27， 81， 243：在 ；選中 擇 【 編輯 】 丨 【 填充 】 丨 【 序列 】 命令，按圖 擊 【 確定 】 按鈕即可。 選擇區(qū)域 1. 選整行或整列 單擊行號(hào)或列標(biāo) (此時(shí)還可通過(guò) 【 格式 】【 行 】 (或 【 列 】 )設(shè)置行高 (列寬 )，當(dāng)然也可直接拖動(dòng)行號(hào)或列標(biāo)的邊線調(diào)整 )。 2. 選整個(gè)工作表 單擊 【 全選 】 按鈕 (行號(hào) 1的上邊列標(biāo) 或按快捷鍵 。 3. 選區(qū)域 按住鼠標(biāo)左鍵從一個(gè)單元格拖動(dòng)到另一個(gè)單元格，則相應(yīng)的矩形區(qū)域被選中；在名稱框內(nèi)輸入單元格首尾編號(hào)后按 形如 5等 )；也可配合 4. 條件選定 選擇滿足條件的若干單元格，這個(gè)功能非常有趣。選擇 【 編輯 】【 定位 】 命令打開(kāi)【 定位 】 對(duì)話框，在 【 引用位置 】 處輸入?yún)^(qū)域名稱或單擊 【 定位條件 】 按鈕，打開(kāi)【 定位條件 】 對(duì)話框，如圖 區(qū)域數(shù)據(jù)的清除與區(qū)域格式設(shè)置 1. 區(qū)域數(shù)據(jù)清除 選中區(qū)域，選擇 【 編輯 】【 清除 】 命令，彈出級(jí)聯(lián)菜單如圖 據(jù)需要進(jìn)行選擇：欲保留區(qū)域格式只清除其內(nèi)容，選擇 【 內(nèi)容 】 或直接按鍵盤上的清除區(qū)域格式只保留內(nèi)容，選擇 【 格式 】 ；格式和內(nèi)容欲同時(shí)清除選擇 【 全部 】 。當(dāng)然，可用同樣方法清除區(qū)域的批注。 2. 區(qū)域格式設(shè)置 所謂格式是指數(shù)據(jù)的類型、精度、字體、邊框、底紋、對(duì)齊方式等。設(shè)置區(qū)域格式的方法是：右擊選擇區(qū)域，從快捷菜單中選擇 【 設(shè)置單元格格式 】 打開(kāi) 【 單元格格式 】 對(duì)話框 (如圖 根據(jù)需要通過(guò)各個(gè)選項(xiàng)卡進(jìn)行設(shè)置。 刪除與插入 刪除 分刪除整行、整列、單元格、區(qū)域等幾種刪除方式。右擊活動(dòng)區(qū)域，從快捷菜單中選擇 【 刪除 】 ，打開(kāi) 【 刪除 】 對(duì)話框如圖 據(jù)需要作相關(guān)選擇。 插入 右擊活動(dòng)單元格，從快捷菜單中選擇 【 插入 】 ，打開(kāi) 【 插入 】 對(duì)話框如圖 據(jù)需要作相應(yīng)選擇。 圖 6 . 9 【 清 除 】 子 菜單 圖 6 . 1 0 【 刪除 】 對(duì)話框 圖 6 . 1 1 【 插入 】 對(duì)話框 作表的計(jì)算 運(yùn)算符 算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符 引用單元格 絕對(duì)引用 混合引用 三維引用 相對(duì)引用 1. 相對(duì)引用 公式中的相對(duì)單元格引用 (例如 基于包含公式和單元格引用的單元格的相對(duì)位置。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列地復(fù)制公式，引用會(huì)自動(dòng)調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用。 2. 絕對(duì)引用 單元格中的絕對(duì)單元格引用 (例如 $A$1)總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對(duì)引用保持不變。如果多行或多列地復(fù)制公式，絕對(duì)引用將不作調(diào)整。絕對(duì)引用的標(biāo)志是符號(hào) $。 3. 混合引用 混合引用具有絕對(duì)列和相對(duì)行，或是絕對(duì)行和相對(duì)列。絕對(duì)引用列采用 $形式；絕對(duì)引用行采用 A$1， B$1 等形式。如果公式所在單元格的位置改變，則相對(duì)引用改變，而絕對(duì)引用不變。如果多行或多列地復(fù)制公式，相對(duì)引用自動(dòng)調(diào)整，而絕對(duì)引用不作調(diào)整。 4. 三維引用 公式中引用本工作表單元格地址，簡(jiǎn)單而應(yīng)用廣泛。其實(shí)，跨工作簿、跨工作表單元格地址也可以引用，只需在單元格地址前進(jìn)行一些限定。 函數(shù) 1類函數(shù)，分別是數(shù)據(jù)庫(kù)函數(shù)、時(shí)間日期函數(shù)、外部函數(shù)、工程函數(shù)、財(cái)務(wù)函數(shù)、信息函數(shù)、邏輯運(yùn)算符函數(shù)、查找和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、文本和數(shù)據(jù)函數(shù)，每一類又有若干個(gè)函數(shù)，共有數(shù)百個(gè)之多。常用的函數(shù)下表所示。 作表的數(shù)據(jù)庫(kù)功能 有效性設(shè)置、追加記錄、刪除記錄、查找記錄、修改記錄、排序、篩選、分類匯總等。 排序數(shù)據(jù) 1使用排序按鈕排序 2使用 “ 排序 ” 對(duì)話框排序 篩選 選擇 【 數(shù)據(jù) 】【 自動(dòng)篩選 】 命令，則自動(dòng)篩選箭頭顯示于篩選區(qū)域中列標(biāo)題的右側(cè)。單擊該箭頭，彈出下拉列表框，根據(jù)需要設(shè)定條件。 高級(jí)篩選 第 1步：構(gòu)造條件區(qū)域 第 2步：實(shí)施高級(jí)篩選 分類匯總 分類匯總指按某列的類別對(duì)數(shù)據(jù)作進(jìn)一步的匯總，匯總的方式有求和、求平均、統(tǒng)計(jì)個(gè)數(shù)、找出最大值、找出最小值。 有效性設(shè)置 安全性設(shè)置 安全性指對(duì)工作簿、工作表、區(qū)域、單元格的數(shù)據(jù)進(jìn)行保護(hù)，防止別人非法修改，還可把工作簿、工作表、某些行列、單元格公式隱藏起來(lái)，讓別人看不到。 保護(hù)工作簿相當(dāng)于保護(hù)了工作簿中的所有工作表。 方案 1：打開(kāi)或修改工作表時(shí)需要密碼。 方案 2：保護(hù)工作簿的工作表和窗口，禁止對(duì)工作表插入、刪除、移動(dòng)等操作。 圖表 創(chuàng)建圖表圖表 可以分為內(nèi)嵌圖表和獨(dú)立圖表兩種 1用圖表向?qū)?chuàng)建圖表 2用“圖表”工具欄創(chuàng)建簡(jiǎn)單的圖表 學(xué)生成績(jī)單數(shù)據(jù)庫(kù) “圖表向?qū)?4步驟之 1圖表類型”對(duì)話框 “圖表向?qū)?4步驟之 2圖表源數(shù)據(jù)”對(duì)話框 “圖表向?qū)?4步驟之 3圖表選項(xiàng)”對(duì)話框 圖表向?qū)?4步驟之 4 “圖 表位置”對(duì)話框 用圖表向?qū)?chuàng)建的圖表 學(xué)生成績(jī)情況050100150李平程小蕓 郭建國(guó) 江祖明 姜春華張成黎江輝楊麗劉新民 黃小萍計(jì)算機(jī)英語(yǔ)數(shù)學(xué)“圖表”工具欄 圖表對(duì)象 圖例圖表類型圖表區(qū)格式數(shù)據(jù)表按行 向下斜排文字向上斜排文字按列 圖表的編輯 圖表的移動(dòng)、復(fù)制、縮放和刪除 改變圖表的類型 圖表中數(shù)據(jù)和文字的編輯 刪除數(shù)據(jù)系列。 添加數(shù)據(jù)系列。 改變數(shù)據(jù)系列產(chǎn)生方式。 修改和刪除文字。 設(shè)置圖表選項(xiàng) 圖表的格式化 選中圖表 ， 在菜單欄上選擇 “ 編輯 圖表區(qū) ”選項(xiàng)；或在快捷菜單中選擇 “ 圖表區(qū)格式 ” 選項(xiàng)；或雙擊圖表對(duì)象 ， 彈出相應(yīng)的格式設(shè)置對(duì)話框 。 若雙擊圖表區(qū) ， 彈出 “ 圖表區(qū)格式 ” 對(duì)話框 ，可以為整個(gè)圖表區(qū)域設(shè)置圖案 、 字體 、 屬性 。 若雙擊數(shù)值軸或分類軸 ， 彈出 “ 坐標(biāo)軸格式 ” 對(duì)話框 ， 可對(duì)坐標(biāo)軸的圖案 、 刻度 、 字體 、 數(shù)字對(duì)齊方式進(jìn)行設(shè)置 。 若雙擊圖例 ， 彈出 “ 圖例格式 ” 對(duì)話框 ， 可對(duì)圖例的圖案 、 字體和位置進(jìn)行設(shè)置 。 案例：小組成績(jī)分析 通過(guò)本案例，學(xué)會(huì) 和 函數(shù)、條件格式、數(shù)據(jù)透視表 (數(shù)據(jù)透視圖 )、打印輸出等實(shí)用的新功能，以及 【 實(shí)例 某學(xué)習(xí)小組有 10位同學(xué)，其各課程成績(jī)?nèi)绫?對(duì)該小組的成績(jī)進(jìn)行分析處理，具體內(nèi)容為： (1) 計(jì)算每個(gè)同學(xué)的總分、平均分、等級(jí)、名次。 (2) 計(jì)算單科平均分、最高分、最低分。 (3) 分別計(jì)算男生和女生的單科平均成績(jī)。 (4) 將單科不及格的成績(jī)用紅色表示，及格的成績(jī)用藍(lán)色表示。 (5) 生成數(shù)據(jù)透視表和透視圖，以便數(shù)據(jù)分析和匯總。 (6) 格式化表格。 (7) 輸出表格。 習(xí) 題 1. 通過(guò)幫助，學(xué)會(huì)將當(dāng)前工作表移入另外一個(gè)工作簿的方法。 2. 在 后將它導(dǎo)入到 (提示：使用 【 數(shù)據(jù) 】 |【 導(dǎo)入外部數(shù)據(jù) 】 命令。 ) 3. 試將 4. 找出 0個(gè)術(shù)語(yǔ)，并解釋之。 5. 你認(rèn)為 希望 6. 一名教師承擔(dān)多門課程教學(xué)，每門課程周學(xué)時(shí)不同。試在 原始數(shù)據(jù)請(qǐng)假設(shè) )。 7. 閱資料，學(xué)會(huì)相關(guān)操作。 8. 紅旗公司人事檔案表如下： 在 習(xí) 題 (1) 建立工作簿文件“紅旗公司 在“人事檔案”工作表中錄入標(biāo)題，并利用記錄單輸入原始數(shù)據(jù)，并設(shè)表格邊框，凍結(jié)表頭。 (2) 表格數(shù)據(jù)水平、垂直居中，有關(guān)工資兩列整數(shù)部分千分位逗號(hào)分隔，保留兩位小數(shù)，冠人民幣符號(hào)。 (3) 計(jì)算工齡。 (提示：用 、 函數(shù) ) (4) 根據(jù)工齡計(jì)算新基本工資：在原基本工資的基礎(chǔ)上，工齡大于 10年者上浮 500元，否則上浮 200元。 (提示：用 函數(shù) ) (5) 通過(guò)篩選，分別將職稱為技術(shù)員、工程師、高工的職工信息復(fù)制到相應(yīng)名