Excel與數據處理(第3版)[杜茂康][電子教桉].ppt

第1章Excel基礎 Excel與數據處理第3版 本章學習目標 認識EXCEL的工作界面 窗口組成元素 了解工作薄 工作表 單元格等基本概念 掌握不同類型的數據輸入方法 理解相對引用和絕對引用 掌握公式輸入方法 掌握工作表的刪除 插入 移動 改名等操作掌握單元格 行 列常用操作 掌握sum count if等簡單函數的應用方法 1 1Excel操作基礎 1 啟動Excel的方法通過Windows任務欄選擇 開始 按鈕通過Excel快捷方式雙擊 xcel文件的名字或圖標 退出 xcel的方法單擊Excel窗口右上角的關閉按鈕選擇 文件 菜單中的 退出 菜單項 按快捷鍵Alt F4 1 1Excel操作基礎 3 Excel界面 功能區(qū) 工作表區(qū) 1 1Excel操作基礎 功能區(qū)的主要控件Excel2007用功能區(qū)取代了早期版本中的菜單 工具欄和大部分任務窗格 包括按鈕 庫和對話框內容 功能區(qū)的主要控件 Office按鈕取代了早期版本中的 文件 菜單 單擊時 將彈出與Excel早期版本 文件 菜單相似的菜單項 它是Excel2007中唯一的菜單 提供了常用的Excel文件操作功能 包括的菜單命令有 新建 打開 保存 另存為 打印 準備 發(fā)送 發(fā)布 關閉 選項卡功能區(qū)由 開始 插入 頁面布局 公式 數據 等選項卡組成 各選項卡是面向任務的 每個選項卡以特定任務或方案為主題組織其中的功能控件 能區(qū)中的選項卡是動態(tài)的 為了減少屏幕混亂 某些選項卡平時是隱藏的 在執(zhí)行相應的操作時 它們會自動顯示出來 組Excel2007將以前版本中那些隱藏在菜單和工具欄中的命令和功能 以面向任務的方式放置在不同的邏輯組中 每個邏輯組能夠完成某種類型的子任務 快速訪問工具欄其中包含一組獨立于當前所顯示的選項卡的命令 無論用戶選擇哪個選項卡 它將一直顯示 為用戶提供操作的便利 用戶可以根據需要設置其中的工具按鈕 1 1Excel操作基礎 工作表區(qū)的主要對象 1 1Excel操作基礎 工作表就是人們常說的電子表格 是Excel中用于存儲和處理數據的主要文檔 由一些橫向和縱向的網格組成 橫向的稱為行 縱向的稱為列 Excel2007的一個工作表最多可有1 048 576行 16 384列數據 而Excel2003的工作表最多有65536行 256列 當前正在使用的工作表稱為活動工作表 工作表標簽每個工作表有一個名稱 體現在工作表標簽上 單擊工作表標簽按鈕可使對應的工作表成為活動工作表 雙擊工作表標簽按鈕可改變它們的名稱 插入工作表標簽按鈕在默認情況下 Excel只打開3個工作表 Sheet1 Sheet2 Sheet 單擊插入新工作表按鈕就會后面插入一個新工作表sheet4 再單擊一次就會插入新工作表sheet5 1 1Excel操作基礎 行號 列標題工作表由1048576行組成 每行的編號稱為行號 工作表的每列用英文字母標志 稱為列標題單元格工作表中的網格 它用列標題和行號定位 如A1就表示 列第 行對應的單元格單元格區(qū)域是指多個連續(xù)單元格的組合 表示形式如下 左上角單元格 右下角單元格例如A2 B3代表一個單元格區(qū)域 包括A2 B2 A3 B3只包括行號或列號的單元格區(qū)域代表整行或整列 如 1 1表示第一行的全部單元格組成的區(qū)域 1 5則表示由第1到5行全部單元格組成的區(qū)域 A A表示第一列全部單元格組成的區(qū)域 A D則表由A B C D四列的全部單元格組成的區(qū)域 1 1Excel操作基礎 編輯欄用于顯示 修改活動單元格的內容全選按鈕用于選定整個工作表所有的單元格 工作表查看方式工作表下邊框的右側提供的三個按鈕用于切換工作表的查看方式工作表縮放 1 2構造第一個工作表 例1 1 某單位的工資數據如下表所示 計算每個職工的總工資 假設個人所得稅的稅率為 收入1600以下 所得稅率為0 收入1600 2500 所得稅率為5 收入2500 3500 所得稅率為8 收入3500 4500 所得稅率為12 收入4500以上 所得稅率為15 計算各位職工應繳納的所得稅 以及繳稅后的實際收入 1 2構造第一個工作表 在EXCEL中可以輕松制作出例1 1的報表 如圖所示 輸入A1 H9中的原始檔案數據 1 在I2中輸入工資計算公式 D2 E2 F2 G2 H22 向下復制此公式到I9 1 在H2中輸入計算公式 I2 K2 向下復制此公式到H9 1 在K2中輸入工資計算公式 I2 J22 向下復制此公式到K9 輸入J列的稅率 1 3工作薄和Excel文件管理 1 3 1工作薄與工作表的關系工作簿在Excel中創(chuàng)建的文件叫做工作簿 擴展名是 xlsx工作薄由工作表組成 新建工作薄只有默認的 個工作表 但可以根據需要增加 個數不限 新建第一個工作薄的默認名 book1 xlsx 工作表由一些橫向和縱向的網格組成的表格 一個工作表最多有1 048 576行 16 384列 工作表由工作表標簽區(qū)別 如sheet1 sheet2 1 3 1工作薄與工作表的關系 工作薄與工作表的關系 1 3 2創(chuàng)建新工作薄 1 工作薄與模板模板模板即模型 樣板 是一種已經建立好的特殊工作薄 已在其中的工作表中設計好了許多功能 如單元格中的字體 字型 工作表的樣式和功能 如財務計算 學生成績統(tǒng)計 會議安排等 工作薄創(chuàng)建Excel總是根據模板創(chuàng)建工作薄 創(chuàng)建的工作薄具有與模板結構 內容和功能 在沒有指定建立工作薄模板的情況下 Excel會據默認模板建立工作薄 這種工作薄在默認情況下只有Sheet1 Sheet2和Sheet3三個工作表 1 3 2創(chuàng)建新工作薄 2 新建默認的空工作薄 三種方法啟動Excel Excel就會自動建立一個名為Boolk1 xlsx 在已啟動Excel的情況下 按Ctrl N 單擊功能區(qū)左上角的Office按鈕 然后從彈出的文件菜單中選擇 新建 1 3 2創(chuàng)建新工作薄 3 根據舊工作薄建立工作薄 1 單擊Office按鈕 從彈出的文件菜單中選擇 新建 Excel將彈出 新建工作薄 對話框 2 單擊 根據已有內容新建 然后在彈出的 根據現有工作薄新建 對話框找到并選擇已有工作薄的名稱 單擊該對話框中的 新建 按鈕 1 3 2創(chuàng)建新工作薄 4 根據模板建立工作薄Excel2007模板類型已安裝的模板 安裝Excel時安裝在本地計算機中的模板 我的模板 用戶自已創(chuàng)建的模板 在線模板 微軟公司網站上的在線工作薄模板 在用這類模板創(chuàng)建新工作薄時 Excel會自動從微軟的網站中下載相關模板到用戶的本地計算機中 并用它創(chuàng)建新工作薄 當然 前提是用戶計算機與Internet網絡相連接 即Online 在線 1 3 2創(chuàng)建新工作薄 5 在線模板示例下面的圖表是用MicrosoftOfficeOnline 中的 預算 模板類中的 商務旅行預算 模板建立的工作薄 在該工作薄中 只需要輸入D列的費用和F列的數字 Excel就會自動計算出H列的費用 這一功能是由 商務旅行模板 提供的 1 3 2創(chuàng)建新工作薄 5 在線模板示例下圖是用 MicrosoftOfficeOnline 中的 庫存控制 模板類中的 設備資產清單 模板建立的工作薄 在該工作薄中預設了許多功能 在L N P Q R列中都預設了計算公式 只要填定了其余列的數據 Excel就會自動計算出這些列的數據 1 3 2創(chuàng)建新工作薄 5 在線模板的建立方法 1 確定計算機已連接上了Internet網絡 2 單擊 Office按鈕 新建 MicrosoftOfficeOnline 將連接到微軟的在線模板網站 3 從微軟網站的在線模板網站中選擇需要的模板 就會從網站中將選擇的模板下載到本機中 同時將應用 1 3 3保存和打開工作薄文件 1 保存文件方法一單擊 快速訪問工具欄 中的按鈕保存文件 方法二單擊Office按鈕 從彈出的文件菜單 選擇 另存為 菜單項 然后在 另存為 對話框中輸入文件名 2 打開文件單擊Office按鈕 從彈出的文件菜單 選擇 打開 菜單項 然后在 打開 對話框中選擇要打開文件的名稱 1 3 4文件格式與兼容性 1 Excel2007的文件格式MicrosoftOffice2007引入了一種基于XML的新文件格式 MicrosoftOfficeOpenXMLFormats 適用于2007版的Word Excel和PowerPoint OfficeXMLFormats有許多優(yōu)點 其一 文件自動壓縮 某些情況下文檔最多可縮小75 其二 OfficeXMLFormats格式以模塊形式組織文件結構 是如果文件中的某個組件 如圖表 受到損壞 文件仍然能夠打開其三 更強的安全性 OfficeXMLFormats格式支持以保密方式共享文檔 可避免用文檔檢查器工具獲取信息 其四 更好的業(yè)務數據集成性和互操作性 在Office中創(chuàng)建的信息容易被其他業(yè)務應用程序所采用 只需一個ZIP工具和XML編輯器就可打開和編輯Office文件 1 3 4文件格式與兼容性 2 Excel2007的文件擴展名和文件兼容性 1 4公式和單元格引用 公式的概念Excel的公式由運算符 數值 字符串 變量和函數組成 公式必須以等號 開頭 即在Excel的單元格中 凡是以等號開頭的輸入數據都被認為是公式 在等號的后面可以跟數值 運算符 變量或函數 在公式中還可以使用括號 例如 10 4 6 2 2 1 50就是公式 可以在任何單元格中輸入此公式 Excel會把公式的計算結果顯示在相應的單元格中 1 4公式 2 運算符在 xcel公式中可以使用運算符 1 4公式 3 說明括號的運算級別最高 在Excel的公式中只能使用小括號 無中括號和大括號 小括號可以嵌套使用 當有多重小括號時 最內層的表達式優(yōu)先運算 同等級別的運算符從左到右依次進行 為字符連接運算 其作用是把前后的兩個字符串連接為一串 例如 ABC DEF 的結果為 ABCDEF 財務 經濟 的結果為 財務經濟 1 4 2引用 1 引用的概念引用即在公式中用到了其他單元格在表格中的位置 引用的作用在于標識工作表中的單元格或單元格區(qū)域 并指明公式中所使用的數據的單元格位置 引用不同工作簿中的單元格稱為鏈接 2 引用的類型A1引用R1C1引用 1 4 2引用 3 相對引用相對引用也稱為相對地址引用 是指在一個公式中直接用單元格的列標與行號來取用某個單元格中的內容 1 4 2引用 相對引用的例子 例1 2 某班某次期末考試成績如圖1 13所示 計算各同學的總分 即F列的數據 在F2計算公式 C2 D2 E2然后向下復制該公式 1 4 2引用 4 絕對引用絕對引用總是在指定位置引用單元格 如果公式所在單元格的位置改變 絕對引用保持不變 絕對引用的形式是在引用單元格的列號與行號前面加 符號 比如 A 1就是對A1單元格的絕對引用 1 4 2引用 絕對引用案例 例1 3 某品牌皮鞋批發(fā)商3月份的銷售數據如圖所示 每雙皮鞋的單價相同 計算各皮鞋代銷商場應支付的總金額 在D6輸入公式 c 3 C6然后向下復制此公式 1 4 2引用 5 混合引用混合引用具有絕對列和相對行 或是絕對行和相對列 比如 A1 B1案例 1 4 2引用 6 三維引用4 三維引用對同一工作薄內不同工作表中相同引用位置的單元格或區(qū)域的引用稱為三維引用 引用形式為 Sheet1 Sheetn 單元格 區(qū)域 例如 Sheet1 sheet8 C5和Sheet1 Sheet8 B2 D6都是三維引用 前者包括Sheet1 sheet8這8個工作表中每個工作表的C5單元格 后者包括此8個工作表中每個工作表的B2 D6區(qū)域 1 4 2引用 6 內部引用與外部引用1 引用相同工作表中的單元格 例 G3 G5 G10 102 引用同一工作簿不同工作表中的單元格 例 Sheet1 G3 Sheet2 G5 Sheet2 E273 引用已打開的不同工作簿中的單元格 例 Book1 Sheet1 IL 4 Book2 Sheet2 E 74 引用未打開的不同工作薄中的單元格 例如 C dk Book1 xlsx Sheet2 B 4 C dk Book1 xlsx Sheet1 C 65 同一公式中存在幾中不同的引用 例 Book1 Sheet1 A 4 Sheet1 G7 F9 1 5函數簡介 1 函數的概念函數是Excel已經定義好了的一些特殊公式 它們可以對一個或多個數據進行計算 然后把計算的結果存放在某個單元格中 2 Excel函數類型工作表函數 財務函數 日期函數 時間函數 數學與三角函數 統(tǒng)計類函數 數據庫管理函數 文本函數及信息類函數等 1 6 1常用工作表函數 1 SUM和Average用法 sum x1 x2 x3 x255 Average x1 x2 x3 x255 其中 x1 x2 x255可以是數字 單元格或單元格區(qū)域的引用 也可以是表達式 功能 sum求所有參數x1 x2 x255的總和 Average求所有參數x1 x2 x255的平均值 1 6 1常用工作表函數 Sum和Average的應用案例某單位的工資數據如下圖所示 已知基本工資 獎金 水費 電費 計算總額 總計與平均數 1 6 1常用工作表函數 max和min用法 max x1 x2 x3 x255 min x1 x2 x3 x255 其中 x1 x2 x255可以是數字 單元格或單元格區(qū)域的引用 也可以是表達式 功能 max求所有參數x1 x2 x255的最大值 min求所有參數x1 x2 x255的最小值 1 6 1常用工作表函數 count用法 count x1 x2 x3 x255 其中 x1 x2 x255可以是數字 單元格或單元格區(qū)域的引用 也可以是表達式 功能 統(tǒng)計x1 x2 x255中數字的個數 4 countif用法 COUNTIF 單元格區(qū)域 條件 其中條件可以是比較式 但必須用雙引號括起來 如 90 60 功能 統(tǒng)計指定單元格區(qū)域中滿足條件的數字的個數 1 6 1常用工作表函數 IF用法IF 條件 表達式1 表達式2 其中表達式可以是數字 函數 單元格或單元格區(qū)域 功能條件成立時 函數結果為表達式 的值 條件不成立時 函數的結果為表達式 的值 1 6 1常用工作表函數 案例某班期末考試成績表如下圖所示 統(tǒng)計每位同學的總分 考試人數 最高和最低總分 以及不及格人數 1 6定制工作環(huán)境 1 選項對話框 單擊Office按鈕 Excel選項 E彈出圖示的Excel選項對話框 通過 工具 自定義 菜單完成 通過此對話框可以定義Excel的工作環(huán)境 定制快整訪問工具條 設置工作表中的字體 字形 色彩 設置受信任的工作目錄 設置新工作薄中默認的工作表個數 設置工作薄文件的保存位置 自動保存時間 1 6定制工作環(huán)境 2 定制快速訪問工具欄快速訪問工具欄相當于以前Excel版本中的工具欄 它會一直顯示在那里 不會因為功能選項卡的切換而隱藏 在默認情況下 快速訪問工具欄顯示在功能區(qū)的左上角 其中只有保存 撤銷和恢復幾個命令按鈕 可定制其中的工具按鈕 方法如下 1 選擇 Excel選項 自定義 從下列位置選擇命令 所有命令 2 從 所有命令 的列表中 選中需要添加到快速訪問工具欄中的命令 然后單擊對話框中的 添加 按鈕 1 6定制工作環(huán)境 2 定制快速訪問工具欄 定制后的快速訪問工具欄 1 7Excel幫助系統(tǒng) 1 關于EXCEL幫助系統(tǒng)Excel的幫助系統(tǒng)非常完善 其中不但有對Excel2007的新增功能和新技術的介紹 而且涉及到了Excel的方方面面 包括工作表數據輸入方法 工作表打印的各類疑難問題 各種Excel函數的功能介紹和應用舉例 各種不同版本的文件格式和兼容性問題 一言概之 Excel的幫助系統(tǒng)詳盡地介紹了Excel的每一個技術問題 小到標點符號的應用說明也能找到 幫助系統(tǒng)是微軟公司提供給Excel用戶的一個不可多得的寶貴資源 或許它比任何一本Excel著作都詳盡和全面 2 訪問幫助系統(tǒng)的方法按F1 謝謝大家 第2章工作表數據輸入 Excel與數據處理第3版 第2章工作表數據輸入 概述數據輸入是很實際的問題 許多工作人員不得不面對它 針對不同規(guī)律的數據 采用不同的輸入方法 不僅能減少數據輸入的工作量 還能保障輸入數據的正確性 如果不了解掌握不同類型數據的輸入方法 可能就要成天座在電腦前 一五一十地輸入數據 輸完了還要檢查 某些人要幾天才能輸入完成的數據 但有的人幾分鐘就完成了 而且數據不會出錯 這就是學習EXCEL數據輸入方法的原因 學習目標 1 了解Excel的數據類型2 掌握不同類型數據的輸入方法3 掌握復制公式輸入數據的方法4 掌握大批量相同數據的輸入方法5 掌握通過自定義格式簡少數據輸入的方法6 掌握用IF Vlookup等函數查找輸入數據的方法7 掌握用Rand和int函數產生大量模擬數據的方法 2 1Excel的數據類型 1 Excel的常見數據類型數字型 日期型 文本型 邏輯型數據數字型表現形式多樣 貨幣 小數 百分數 科學計數法 各種編號 郵政編碼 電話號碼等多種形式 2 1Excel的數據類型 2 Excel2007數值型數據的取值范圍 2 2基本數據的輸入 數據輸入并非易事 請看下面的例子 例2 2 某電信公司有用戶檔案如圖2 2所示 如果不講技巧 一字不誤的輸入 費時費力 本章將介紹一些數據輸入技術 應用這些技術可以減少數據輸入過程的不少麻煩 2 2基本數據的輸入 1 數據輸入的一般過程 1 選定要輸入數據的單元格 2 從鍵盤上輸入數據 3 按Enter鍵后2 輸入數值 1 正數的輸入 234 13 333 333 12E3 2 負數的輸入 234 234 12e2 3 分數的輸入 22 3 03 4 4 貨幣數據的輸入 123 21 2 2基本數據的輸入 3 輸入文本字符文本應逐字輸入數字文本以 開頭輸入 或用 數字 方式輸入 輸入文本32 可輸入 32 或輸入 32文本形式的公式 要通過 插入 選項卡 文本 組中的 對象 命令如 2 2基本數據的輸入 4 輸入日期以yy mm dd形式 或mm dd形式輸入通過格式化得到其它形式的日期 這樣可減少輸入在 開始 選項卡 數字 組卡 單擊其中的箭頭 E彈出圖示的 單元格格式 設置對話框 2 2基本數據的輸入 5 輸入公式公式是對工作表中的數值進行計算的等式 公式要以等號 開始 例如在A3輸入 5 2 3 結果等于2乘3再加5 A3中將顯示11 2 3相同數據的輸入 1 復制相同數據建立具有相同數據的不同工作表 可用復制方法 某班主任建立相同的成績表頭 可用此法 復制一班表頭 2 3相同數據的輸入 2 填充復制相同數據設要建立下圖所示 學生檔案 表 其中入學時間 班級 系都是相同數據 1 在G2中輸入 計算機通信 然后Enter 2 單擊G2 G2右下角會出現黑色小方塊 它就是填充柄 3 向下拖動填充柄 拖過的單元格都被填入了 計算機通信 雙擊此填充柄可自動復制生成G列數據 2 3相同數據的輸入 3 用Ctrl Enter輸入相同數據設要建立下圖所示 學生檔案 表 其中入學時間 班級 系都是相同數據 1 選中F2 F11 2 輸入 JK001 3 按Ctrl Enter 可用同樣方法輸入C G列數據 2 4編號的輸入 1 復制輸入連續(xù)的編號連續(xù)編號 或等差 等比性質的數據 應該采用復制或序列填充的方式進行輸入 比如公職或單位的職工編號 電話號碼 手機號碼 零件編號等 輸入右圖A列的學號 1 在A2輸入1003020101在A3輸入1003020101 2 選中A2 A3 3 向下拖動A3單元格的填充柄 2 4編號的輸入 2 填充產生連續(xù)編號填充序列方式適用于輸入較多的連續(xù)編號數據 比如要輸入電話號碼 663321240 663328000 如右圖所示 方法如下 在第一個單元格中輸入起始號碼在 開始 選項的 編輯 組 單擊該組中填充控件右側的下三角形 彈出 序列 對話框 在Excel彈出的 序列 對話框中指定 列 在步長中輸入1在終止值中輸入最后一個號碼 2 4編號的輸入 3 利用自定義格式產生特殊編號人們常會遇到各種具有一定規(guī)則的特殊格式的編號 如右圖的保險號 1 選中要輸入保險號的區(qū)域2 在 開始 選項卡中 單擊數字組右邊的箭頭 Excel會彈出單元格格式設置對話框 3 選中 自定義 4 輸入自定格式 p00 000 0000 2 4編號的輸入 4 用自定義格式輸入大數字編號當在單元格中輸入一個位數較多的數值時 比如超過15位的整數 Excel會自動將輸入的數據顯示為科學記數法 這可能并不符合我們的需要 產生這種問題的原因是數值精度問題 Excel的默認精度是15位 如果一個數值的長度超過15位 則15位之后的數字都被視為0 這種位數較多的大數字編號 可以用自定義格式進行輸入 或者用文本形式輸入 2 4編號的輸入 4 用自定義格式輸入大數字編號建立右圖所示的商場用戶檔案 其中客戶編號為21位 且前18位全部相同 若直接輸入 不僅數據多 且要出錯 比如在A3輸入 356800993102155129001 Excel會顯示為3 57E20解決方法是定義G列保存身份證號的單元格的自定義格式為 356800993102155129 000 設置自定義格式后 只需要輸入身份證的最后4位編號 2 4編號的輸入 選中A3單元格 單擊 開始 選項卡 數字 組右下功的下箭頭 彈出設置單元格格式對話框 類型 文本框中輸入 356800993102155129 000 2 5用 組合多個單元格數據 1 用 運算符組合數據 為字符連接運算 其作用是把前后的兩個字符串 也可以是數值 連接為一個字符串 如 ADKDKD DKA 的結果為ADKDKDDKA 123 45 678的結果為 12345678 設下圖E列數據由C D列數據組合而成 在E3中輸入公式 C3 D3 然后向下復制 2 5用 組合多個單元格數據 2 用 和文本函數的結合 常和文本函數left right mid等合用 這幾個函數都很簡單 其調用語法如下 left text n right text n mid textn1 n2 其中 left截取text文本左邊的n個字符 比如left 1234 2 12 right截取text文本右邊的n個字符 比如right 1234 2 34 mid從text的第n1字符開始 取n2個字符 比如MID 1234 2 2 23 2 5用 組合多個單元格數據 3 用 和文本函數的結合案例某電話公司的繳費帳號由身份證號的后15位 全長18位 后接電話號碼構成 如下圖所示 用 和文本函數可以很快建立繳費帳號 在I3輸入公式 Right G3 17 H3 然后向下填充復制該公式到I列的其余單元格 2 6采用下拉列表進行數據選擇 1 下拉列表的適用范圍項目個數少而規(guī)范的數據 比如職稱 工種 單位及產品類型等 這類數據適宜采用Excel的 數據有效性 檢驗方式 以下拉列表的方式輸入 例如 某校教師檔案如右圖所示 其中的職稱列數據只能從 講師 教授 副教授 助教 中選擇 這類數據用下拉列表輸入 方便而準確 2 6采用下拉列表進行數據選擇 2 下拉列表建立方法 1 選中要建立下拉列表的單元格區(qū)域 2 然后選擇功能區(qū)中的 數據 選項卡 單擊 數據工具 組中的 數據有效性 控件 3 選擇 設置 標簽 然后從 允許 下拉列表中選擇 序列 選項 4 在 來源 文本框中輸入職稱名字 助教 講師 副教授 教授 2 7利用公式與函數進行查找輸入 1 查找輸入的意義如果表格中的不同數據列之間存在一定的依賴關系 那么用IF公式進行數據的轉換輸入是一種效率較高的輸入方法 如果某些數據已經在其它數據表中建立好了 查詢輸入可以提高效率 且能減少輸入錯誤 2 7利用公式與函數進行查找輸入 2 IF函數的應用IF函數又稱為條件函數 它能夠對給出的條件進行判斷 并根據判斷結果的正誤執(zhí)行不同的運算 IF函數的調用形式如下 IF 條件式 條件正確時函數的取值 條件錯誤時函數的取值 比如 IF 3 2 right error 的結果是 right 而IF 32是正確的 函數的結果就是 條件正確時的取值 與之相對應 而3 2是錯誤的 所以 2 7利用公式與函數進行查找輸入 IF函數可以嵌套使用 即在IF函數中還可以使用IF函數 最多可以嵌套7層 比如 IF A2 89 A IF A2 79 B 如果A2等于93 則該函數的結果是 A 如果A2等于80 則函數的結果是內層IF A2 79 B 的結果 顯然80 79 所以結果為 B 如果A2等于65呢 函數的結果就是IF A2 79 B 不成立時的取值 這時該函數的結果就是一個空值 因為IF A2 79 B 沒有給出A2 79不成立時的取值 在這種情況下 Excel將返回一個空值 即什么結果都沒有 給該函數 2 7利用公式與函數進行查找輸入 3 IF函數查找轉換案例 例2 10 某學校年度獎金如下圖所示 獎金根據職稱確定 教授 2000元 副教授 1500 講師 1000 助教 是500元 在F3輸入公式 IF E3 教授 2000 IF E3 副教授 1500 IF E3 講師 1000 IF E3 助教 500 2 7利用公式與函數進行查找輸入 公式含義分析 2 7利用公式與函數進行查找輸入 4 Vlookup函數查找Vlookup函數能夠從其它工作表 或本工作表 將某數據列中的數據查找出需要的數據 VLOOKUP函數的用法如下 VLOOKUP x table n r 其中 x是要查找的值 table是一個單元格區(qū)域 即由工作表的1列或多列組成 Excel將在table的第一列查找有無x這樣的值 只在table的第1列查找有無x n是table中待返回的匹配值的列序號 r可以取0或1 當r為0時 該函數將進行精確匹配查找 當r 1時 table的第一列應按升序排列 否則找到的結果可能不正確 r 1表示Excel實行的是近似值查找 2 7利用公式與函數進行查找輸入 5 Vlookup函數查找案例 1 問題描述 某移動公司更換了新系統(tǒng) 使原有的部分電話號碼不能使用 同時又新增加了許多電話號碼 系統(tǒng)變化后 大部分用戶仍然使用原來的用戶檔案 電話號碼和繳費合同號都沒有改變 而那些不能使用的電話號碼和新增加的電話號碼則需要重新建立用戶檔案 2 7利用公式與函數進行查找輸入 2 帳號查找過程在需要提取帳號的新號碼工作表的B3輸入查找公式 并向下復制此公式 VLOOKUP D2 A 2 B 18 2 0 E列是從舊帳號的B列找到的數據 N A表示D列的帳號在A列不存在 2 7利用公式與函數進行查找輸入 在實際工作中 不同類型的數據往往存儲在不同的工作表中 這就需要在不同工作表中進行跨表數據查找 例2 12 某超市將其產品單價和產品銷售匯總表分別保存在兩個工作表中 如圖2 21所示 在產品的單價表中包括有該商場大約幾百種產品的單價 銷售匯總表每月統(tǒng)計一次 現在要計算每種產品的銷售總額 2 7利用公式與函數進行查找輸入 在C3單元格中輸入公式 VLOOKUP A3 產品 B 3 D 16 3 0 然后向下復制此公式到C列其他單元格 2 8利用填充序列輸入數據 1 數據序列的含義序列就是一組數據 這組數據有先后順序 內容固定 在應用中常常以一組數據的整體形式出現 2 Excel內置序列對于經常使用的一些數據序列 比如月份 星期 季度等 Excel已經將它們內置在系統(tǒng)中 在輸入這些數據時 只需要輸入第一個數據 其余的可以采用填充復制的方法由Excel自動產生 2 8利用填充序列輸入數據 3 自定義序列用戶可以將經常使用的數據序列添加到Excel系統(tǒng)中 該序列就會擁有與Excel內置序列相同功能 比如 輸入該序列的第一項內容后 其后各項都可以通過填充復制產生 案例 例2 14 某大學有 計算機學院 通信學院 經濟管理學院 法律學院 中藥學院 電子技術學完 等多所學院 學校辦公室經常要用到這些學院名稱 現以此為例說明Excel自定義序列的建立方法 2 8利用填充序列輸入數據 建立方法 1 單擊Office按鈕 Excel選項 常用 編輯自定義列表 彈出圖示的 自定義序列 對話框 2 在 輸入序列 中依次輸入序列內容 3 建立輸入序列后 輸入第一項 即 計算機學院 后 通過填充復制就能產生其它各項內容 2 9行列轉置輸入 某些時候需要將工作表的行數據作為列數據輸入 或將列數據作為新表的行數據 這樣的工作表數據可通過行列轉置快捷生成 例2 15 某品牌電視2008年的銷售統(tǒng)計數據如圖2 25所示 其中的A B兩列數據是以前輸入在工作表中的各省銷售排名 在A列中包括有中國全部省份的名稱 現在要構造D1 AA16的統(tǒng)計表 2 9行列轉置輸入 1 選中A列的省份名稱所在的單元格區(qū)域A4 A34 2 在 開始 選項卡上的 剪貼板 組中 單擊 復制 按鈕 3 單擊E4單元格4 在 開始 選項卡上的 剪貼板 組中 單擊下面的三角形 然后從彈出的快捷菜單中選擇 轉置 這樣就完成了E4 AA4中的省份名稱的輸入 2 9行列轉置輸入 例2 16 矩陣涉及到較多的數據 它由緊密相連的一組單元格區(qū)域構成 矩陣的運算比較復雜 Excel提供了一組矩陣運算的函數 建立圖2 26中A2 D8區(qū)域的行列轉換矩陣 1 選中A2 D8 2 在 開始 選項卡上的 剪貼板 組中 單擊 復制 按鈕 3 單擊G3單元格4 在 開始 選項卡上的 剪貼板 組中 單擊下面的三角形 然后從彈出的快捷菜單中選擇 轉置 這樣就完成了G3 M6中的矩陣輸入 2 9限定輸入數據的長度 顯示提示和出錯信息 對輸入到單元格中的數據進行一定程度的限制 比如限制小數位數 日期和時間的范圍 字符的個數等 當輸入的數據不符合限定規(guī)則時 Excel就顯示一些警告信息 并且不接受輸入的數據 如果工作表的數據是由其他人輸入的 則有必要在別人輸入數據時給出一些簡要的提示信息 或對數據輸入的規(guī)則作些說明 2 10限定輸入數據的長度顯示提示和出錯信息 例2 17 圖2 27左圖是某俱樂部的會員檔案表 A列的用戶編號不超過4個字符 希望對此限制 當用戶輸入此列數據時 為他顯示編號的規(guī)則和限制說明 如圖2 27左圖所示 2 11利用RAND和INT函數產生大批量的仿真數據 1 Rand函數又稱為隨機函數 它產生一個介于0 1之間小數 對Rand函數的結果進行放大 能產生任意范圍內的數據 如 RAND 100 可以產生0 100之間的數據RAND 50 50 可以產生50 100之間的數據 2 Int函數又稱取整函數 將數字向下舍入到最接近的整數 比如 INT 8 9 的結果是8INT 8 9 的結果是 9 2 10用RAND和INT函數產生仿真數據 案例 例2 18 建立如圖2 29所示的仿真成績表 A列數據可以通過填充復制產生 B列數據需要輸入 第1行數據需要輸入 但其中的成績卻不必逐個輸入 可以用INT函數和RAND函數產生 1 選中C2 G11區(qū)域 2 輸入公式 10 int rand 90 3 按Ctrl Enter 說明 用rand產生的數據會隨時發(fā)生變化 可將它貼粘為數據 它就不會發(fā)生變化了 其方法是 1 選中用隨機函數產生的數據區(qū)域 如圖2 28中的C2 J11 然后單擊 開始 選項卡中 剪貼板 組中的復制按鈕 2 在 開始 選項卡上的 剪貼板 組中 單擊下面的三角形 從彈出的快捷菜單中選擇 粘貼值 TheEnd 謝謝大家 第3章工作表的日常操作 Excel與數據處理第3版 本章學習目標 掌握工作表的刪除 插入 移動 改名等操作掌握單元格 行 列的刪除 插入 復制 貼補 剪切 移能及單元格的合并與拆分等操作掌握為單元格添加批注的方法掌握查看工作表數據的不同方式設置打印的標題 頁腳 紙張大小及頁面邊距和工作表打印 3 1最小化功能區(qū) 1 Excel2007功能區(qū)的利與弊好處是直接 功能按鈕操作方便缺點是功能區(qū)占用屏幕空間較大 不便小屏幕電腦操作 功能按鈕分布較散亂 某些選項卡只中特定條件下才顯示出來 易給初學者造成功能不及早期版本完善的錯覺 與當前仍處于主流的菜單操作方式并不完全融洽 2 最小化功能區(qū)小屏幕電腦最佳方式是最小化功能區(qū) 在熟悉功能區(qū)各控件的分布后 最小化后不影響EXCEL的任何操作 當要執(zhí)行某項操作時 單擊任一選項卡就會顯示出正常化的EXCEL操作界面 選擇相能的功能后 又會返回到最小化功能區(qū)狀態(tài) 3 1最小化功能區(qū) 右擊功能區(qū)中的任一選項卡將彈出此快捷菜單 選擇 功能區(qū)域最小化 最小化功能區(qū)后的操作界面 3 2工作表操作 3 2 1工作表常見操作1 工作表的切換2 工作表選擇3 插入工作表4 刪除工作表5 移動工作表6 修改工作表標簽的名字7 復制工作表8 修改新工作簿的默認工作表個數 3 2工作表操作 3 2 2工作表行 列操作選擇行 列選擇單行 用鼠標單擊要選擇的行號連接多行 按住Shift 然后單擊第1和最后兩個行號不連接多行 按住Ctrl 依次單擊要選擇的行號列的選擇方法同行的選擇法 按住Shift單擊行號2 3就選中了 3 2工作表操作 刪除行 列插入行 列調整行高調整列寬 把鼠標移到行號3的下邊線上 當指針變成一個黑色 十字架 時 按下并拖動鼠標左鍵 就可增 減該行的行高 把鼠標移到G列的右邊線上 當指針變成一個黑色 十字架 時 雙擊鼠標左鍵 G列的寬度就會自動進行調整以顯示出那些被遮住的字 3 3單元格操作 1 選擇單元格單個 連續(xù)與不連續(xù)的多個單元格的選擇2 清除單元格內容3 刪除單元格注意單元格清除與刪除的區(qū)別4 插入單元格5 移動單元格6 復制單元格7 單元格合并與取消 3 4編輯工作表數據 3 3 1使用快捷鍵 3 3 1使用快捷鍵見Page57 3 3 2復制 剪切 移動 粘貼 撤消 恢復 例3 1 某商場在工作表中保存每天的銷售記錄 同一工作表中可能保存多天的銷售記錄 如Page58圖3 10所示 圖中A1 E13是2009年4月18日的銷售記錄 H1 I13是2009年4月19日的銷售記錄 這些數據都可以通過復制產生 3 3 2復制 剪切 移動 粘貼 撤消 恢復 1 復制和粘貼2 剪切和移動3 在剪貼板上收集多個復制項目這些操作都在 開始 選項卡的 剪貼板 3 3 2復制 剪切 移動 粘貼 撤消 恢復 4 選擇性粘貼單元格或單元格區(qū)域具有許多內容如字體 字型 邊框 色彩 單元格中的值 公式及有效性驗證等 當對單元格進行復制和粘貼時 操作的是單元格或區(qū)域的全部內容 單元格的各個單項內容可以通過選擇性粘貼進行單項粘貼 這項功能非常實用 也常用到 字型 字體 邊框 色彩 公式 底紋 3 3 2復制 剪切 移動 粘貼 撤消 恢復 1 粘貼值 字體 格式等單項內容 D列的產品單價是用RANDBETWEEN隨機函數生成的20 100之間的隨機數 C列是RANDBETWEEN產生的20 500之間的隨機數 當任一單元格的數據有變化時 隨機數都會發(fā)生變化 通過復制和粘貼值可將區(qū)域C3 D13粘貼為純數字 這些數值就不會變化了 3 3 2復制 剪切 移動 粘貼 撤消 恢復 選擇性粘貼以粘貼上屏C3 D13區(qū)域中的隨機數公式為值的例 方法如下 1 選擇C3 D13區(qū)域 再單擊 開始 剪貼板 復制工具按鈕 2 單擊 開始 剪貼板 下面的三角形 選擇性粘貼 應該注意 本對話框中的內容都是每個單元格具有的 可以將其中的任何一個單項復制粘貼到其它單元格或自身中 3 3 2復制 剪切 移動 粘貼 撤消 恢復 2 粘貼運算 1 復制A2 I2 2 選中A4 I4 3 選擇性粘貼 運算中的 乘 運算后 A4 I4的結果 3 3 2復制 剪切 移動 粘貼 撤消 恢復 3 粘貼鏈接鏈接即連接 它不是將源單元格或區(qū)域中的數據或公式復制到目標單元格或區(qū)域中 而是建立兩者之間的鏈接 并通過鏈接在目標單元格或區(qū)域中顯示源單元格或區(qū)域中的內容 當鏈接源單元格或區(qū)域中的數據發(fā)生變化時 目標單格或區(qū)域中的數據也會發(fā)生變化 在企業(yè)數據的分析過程中 可能會涉及到多個用戶或部門的提交的不同工作簿 需要對這些工作薄中的數據進行匯總 并將相關的數據集成在一個匯總工作簿中 應用鏈接來實現這樣的操作非常有效 因為通過鏈接形成的匯總報表 當源工作簿發(fā)生更改時 鏈接匯總工作簿中的數據會被Excel自動修改 使之與源工作薄中的數據同步 粘貼鏈接 例3 2 某電視機廠將2009年每個季度的銷售數據保存在一個工作表中 如Page61圖3 13 a 所示 在年末時需要制作匯總表 將每個季度的數據匯總在一個表中 并統(tǒng)計出年度銷售總數 如圖3 13 b 所示 鏈接 鏈接 鏈接 鏈接 鏈接 3 3 3編輯欄 編輯欄具可顯示 編輯 修改和輸入數據 實時顯示活動單元格中的內容 當需要在單元格中輸入長公式 修改字符數目較多的單元格內容時 在編輯欄中更方便和直觀 例3 3 某學校按職稱為教師發(fā)放獎金 教授2000元 副教授1500元 講師1000元 助教500元 3 3 4自動更正 單詞翻譯 漢字繁簡轉換 1 自動更正 1 當在單元格中輸入錯誤的符號 單詞時 Excel將按此處列出內容進行更正 2 用戶可將自定義更正方案添加到此對話框中 3 顯示出此對話框的方法 單擊Office按鈕 Excel選項 校對 自動更正選項 3 3 4自動更正 單詞翻譯 漢字繁簡轉換 2 單詞翻譯 Page63 Excel2007可以實現多種不同語言的詞匯翻譯 3 3 4自動更正 單詞翻譯 漢字繁簡轉換 3 漢字簡繁轉換 3 3 5斜線表頭 例3 4 某茶葉公司將2000 2004年的茶葉銷售數據存放在Excel工作表中 為了清晰地達表格數據的含義 需要繪制斜線表頭 如Page64圖3 18所示 3 3 5斜線表頭 1 調整單元格到合適大小 3 選擇Excel2007 插入 形狀 文本框 輸入文本內容并旋轉文本框 4 結過前面操作之后 單元格的大小和直線長短位置常常還會重新調整 比較麻煩 3 3 6單元格批注 批注是為單元格添加注釋 以便說明單元格數據或公式的意義 例3 5 某班某次期末考試的成績表如Page65圖3 21所示 其中有三位同學沒有成績 為了避免時間長了忘記他們無成績的原因 在其對應的單元格中添加了批注 3 5查看工作表數據 3 5 1全屏幕觀察工作表數據 選擇 視圖 選項卡中 工作薄視圖 組中的 全屏顯示 按鈕 就會見到全屏幕視圖 如圖示 3 5查看工作表數據 3 5 2按比例查看工作表數據在Excel2007中可以按任何比例查看工作表 3 5 3按打印的頁面布局查看工作表數據 例3 6 某公司的訂單保存在Excel的工作表中 有幾百行數據 如圖3 24所示 對于這種類型的大型工作表 如果要將其打印在紙質報表中 在打印之前很有必要在頁面布局視圖中調整紙張的邊距 添加頁眉 即每頁的標題 及頁腳 顯示在每頁底部的標識 如頁碼 等內容 如不作這些工作 很有可能打印出一大堆費紙 如將最右邊的一列或最下邊的一行數據打印在一紙新的打印紙上 但通過頁邊距調整就能夠在一張紙上打印出來 3 5 3按打印的頁面布局查看工作表數據 在Excel2007的 頁面布局 視圖中見到的工作表近似于在打印紙中顯示工作表 同時 頁面布局 視圖提供了水平和豎直方向上的標尺 使用標尺可以測量數據的寬度和高度 快速調整工作表內容在打印輸出時的紙張邊距 在頁面布局視圖中還可以方便地更改頁面方向 添加或更改頁眉和頁腳 設置打印邊距以及隱藏或顯示行標題與列標題 更改數據的布局和格式 3 5 3按打印的頁面布局查看工作表數據 1 單擊 視圖 工作薄視圖 組 頁面布局 按鈕 3 4 4按打印的分頁預覽視圖查看工作表數據 分頁預覽能夠顯示要打印的區(qū)域和分頁符 在一個分頁符后面的表格內容將被打印到下一張打印紙上 位置 要打印的區(qū)域顯示為白色 自動分頁符顯示為虛線 手動分頁符顯示為實線 在分頁預覽視圖中可以調整當前工作表的分頁符 還可以調整打印區(qū)域 打印區(qū)域 不需要打印整個工作表時 打印的一個或多個單元格區(qū)域 如果工作表包含打印區(qū)域 則只打印打印區(qū)域中的內容 的大小 選擇 視圖 選項卡中的 分頁預覽 命令 Excel就將以分頁預覽的方式顯示工作表 3 5 4按打印的分頁預覽視圖查看工作表數據 選擇 視圖 分頁預覽 命令打印之前 用這種查看方式能發(fā)現一些打印問題 3 5 6在多窗口查看數據 1 單擊 視圖 窗口 組中 新建窗口 按鈕2 單擊 視圖 窗口 組中 并排查看 按鈕若不滿意 并排查看 可以選擇其它查看方式 3 5 7在拆分窗口中查看數據 1 單擊要進行窗口分隔的單元格位置2 單擊 視圖 窗口 組中 拆分 按鈕若不滿意 拆分 可以拖動分隔線重新定位分隔位置 3 4 7凍結行列標題查看數據 單擊要凍結行下邊 要凍結列右邊的交叉點單元格 單擊 視圖 窗口 組 凍結窗格 然后在彈出的菜單中選擇 凍結拆分窗格 3 5工作表打印 1 打印紙及頁面設置進行正式打印之前要確定的幾件事情 打印紙的大小 打印的份數 是否需要頁眉 是打印指定工作表 還是整個工作簿 或某個工作表的單元格區(qū)域 這些問題可以通過頁面設置解決 3 5工作表打印 2 Excel2007的打印設置相關功能 3 5工作表打印 頁面設置對話框的方法單擊 頁面設置 調整為合適大小 或 工作表選項 組右邊的箭頭 3 5工作表打印 2 打印預覽 1 打印預覽的意義打印預覽是打印稿的屏幕預顯 其效果與最終的打印效果沒有什么區(qū)別 在進行工作表的正式打印之前 有必要進行打印預覽 以便及早發(fā)現問題 進行頁面和紙張的重新設置 避免打印出無用的表格 2 方法單擊Office按鈕 從彈出的 文件 菜單列表中選擇 打印預覽 菜單命令 3 5工作表打印 打印預覽 3 5工作表打印 3 縮放打印比例以適應打印紙大小比較糟糕的打印情況 只有一兩行 列 的內容被打印到了另一頁上 打印內容不足一頁 整張打印紙內容較少 頁面難看 這些情況可通過打印縮放解決 3 5工作表打印 4 打印標題或頁碼進入頁面設置對話框單擊 自定義頁眉 可輸入頁眉或頁腳的文字 也可以選擇頁碼或日期等 3 5工作表打印 5 標題在工作表的首行如果工作表的首行是標題 而且工作表內容較多 可能打印多頁 可以通過頁面設置 將首行指定為標題 3 5工作表打印 6 打印整個工作簿或指定工作表區(qū)域如果需要打印工作簿中的全部工作表 或打印一個工作表的指定區(qū)域 單擊Office按鈕 選擇 文件 打印 菜單命令 彈出 打印內容 對話框 謝謝大家 第4章數據格式化 Excel與數據處理第3版 本章學習目標 通過格式化能夠突出顯示重要的數據 添加提示信息 增加工作表的整體結構和組織性 美化工作表 使工作表數據的含義更加清晰 本章主要掌握以下內容 1 了解Excel格式化的意義2 掌握套用表格式和自定義格式化方法3 掌握應用主題格式化工作表的方法4 掌握文本格式化的方法5 掌握數據格式化的方法6 了解自定義格式化數據的方法和意義7 掌握日期格式化方法8 掌握條件格式的應用和規(guī)則管理方法 4 1工作表的格式化 4 1 1 自動套用表格式表是Excel2007中的一種特殊表格 Excel為表預定義了許多樣式 這些樣式布局美觀 色彩搭配合理 具有不同類型的網