Excel2007數據分析與處理

1、ExcelExcel數據分析與處理數據分析與處理本章主要內容1 Excel 2007概述2 Excel 2007的基本操作3 單元格的編輯 4 單元格的格式 5 數據處理6 創(chuàng)建數據圖表7 頁面設置與打印輸出21 Excel 2007概述Excel 2007簡介1.1 Excel 2007的啟動與退出1.2 Excel 2007窗口的基本組成1.3 工作薄的基本操作3 Excel 2007簡介4ExcelExcel的中文意思就是超越。的中文意思就是超越。 從從19831983年起，先后推出了年起，先后推出了Excel.0Excel.0、Excel 5.0Excel 5.0、Excel Exce

2、l 6.06.0、Excel 7.0Excel 7.0、Excel 97Excel 97，Excel 2000Excel 2000，Excel 2002Excel 2002，Excel 2003Excel 2003，直到現在的，直到現在的Excel 2007Excel 2007。 ExcelExcel具有十分友好的人機界面和強大的計算功能及出具有十分友好的人機界面和強大的計算功能及出色的圖表功能色的圖表功能，是最，是最出色的電子表格軟件出色的電子表格軟件。主要功能 能同時編輯多張工作表。 提供豐富的函數，對數據作復雜的數學分析和報表統(tǒng)計。 具有豐富的制圖功能，能使圖、文、表有機地結合。 可以從

3、不同角度對數據進行分析，對數據進行重組。 能與indows下運行的其它軟件共享數據資源。1.1 Excel 2007的啟動與退出啟動啟動“開始開始”“程序程序”“Microsoft Office”“Microsoft Office”Microsoft Office Excel 2007Microsoft Office Excel 2007在在“運行運行”對話框中輸入對話框中輸入“excel”excel”（機房里（機房里20032003與與20072007兩個版本同時裝的情況）兩個版本同時裝的情況）退出退出ExcelExcel是多文檔的操作界面，關閉某工作薄與退出是多文檔的操作界面，關閉某工作薄

4、與退出ExcelExcel是有區(qū)別的。是有區(qū)別的。61.2 Excel 2007窗口的基本組成7工作簿工作簿單單元元格格工作表標簽工作表標簽活動單元格地活動單元格地址、名稱框址、名稱框行號行號列號列號活動活動單元格單元格填充填充柄柄編輯欄編輯欄1.3 工作薄的基本操作新建工作表的缺省名Book1，Book2，Book3，保存 xlsx（注意后綴名，2003為xls）打開關閉82 Excel 2007的基本操作2.1 單元格的選擇與活動單元格行、列的選擇單元格的選擇單元格區(qū)域的選擇活動單元格的移動91. 選擇行和列10單擊行號單擊行號選擇整行單擊列號單擊列號選擇整列單擊行號或列號，然后拖動鼠單擊

5、行號或列號，然后拖動鼠標到相鄰的行號或列號；或者標到相鄰的行號或列號；或者按按Shift鍵。鍵。選擇相鄰的行或列單擊第一個行號或列標，按住單擊第一個行號或列標，按住Ctrl，再單擊其他的行號或列，再單擊其他的行號或列號號選擇不相鄰的行或列2. 選定單元格定位一個單元格 直接鼠標單擊某個單元格選定多個相鄰的單元格用鼠標拖動或者Shift鍵選定多個不相鄰的單元格 先選中一個單元格，然后按住Ctrl鍵的同時，點擊別的需要選中的單元格113. 選定單元格區(qū)域單擊選取區(qū)域左上角的單元格，按住Shift鍵，將鼠標指針指向要選取區(qū)域的右下角單元格或者用鼠標從左上角單元格滑動到右下角單元格12選定一個單元格區(qū)

6、域選擇不相鄰的單元格區(qū)域先選中一個單元格區(qū)域，然后按住先選中一個單元格區(qū)域，然后按住CtrlCtrl鍵，再選取其他區(qū)鍵，再選取其他區(qū)域域2 Excel 2007的基本操作2.2 數據的錄入1. 數據類型2. 不同類型數據的錄入3. 序列填充4. 給單元格添加批注131. Excel的數據類型數據類型：文本、數值、日期和時間，還有公式和函數等14輸入文本數據文本輸入指鍵盤上可鍵入的任何符號，文本默認為左對齊。當輸入的字長度超出單元格寬度時，右邊單元格無內容時，則擴展到右邊列；否則截斷顯示。確認輸入：回車或“輸入”按鈕。取消輸入：Esc鍵或者“取消”按鈕。15輸入數值數據數值輸入 數字09和符號+

7、、-、$、E、/、%、小數點(.) 、千分號(,)等數值默認為右對齊。單元格中默認只能顯示11位有效數字，超過長度就用科學計數法來表示如果數字太長無法在單元格中顯示，單元格將以“#”顯示，此時需增加列寬。16如何輸入身份證號？如何輸入身份證號？輸入日期和時間日期日期使用斜線“/”或連字符“-”輸入。例如：“12/3/20”, “2012-3-20”按Ctrl+“;”（分號）鍵輸入當天日期。時間用冒號“:”輸入，一般以24小時格式表示時間，若以12小時格式表示，需在時間后加上A(AM)或P(PM)。A或P與時間之間要空一格。按Ctrl+Shift+“;”（分號）鍵輸入當前時間。在同一單元格中可以

8、同時輸入時間和日期，不過彼此之間要空一格?？墒褂煤瘮?=now()17特殊字符的輸入1.上標和下標的錄入22+232.帶圈數字錄入3.其他符號18設置單元格的格式“開始”- “數字”選項組右下角的“設置單元格格式”按鈕，打開設置格式對話框，來調整類型及相關的設置193. 序列填充當要輸入重復的或者有規(guī)律的數據和序列時，使用自動填充功能來提高工作效率。有規(guī)律的數據指等差、等比、系統(tǒng)預定義的數據填充系列以及用戶自定義的新序列。用填充柄實現快速填充：將鼠標指針指向要復制數據的單元格右下角的填充柄（鼠標指針變成十字），按住左鍵拖過要填充的單元格即可。20鼠標拖拽填充柄當選中單元格內容為純字符、純數字或

9、是公式時，填充相當于復制；當選中單元格內容為文字數值混合時，填充時文字不變，數字遞增；如果有連續(xù)的單元格存在等差、等比關系時，在按相應關系填充，但按Ctrl鍵拖曳時則始終是復制。21 給單元格添加批注批注的作用解釋說明打印時不會輸出223 單元格的編輯3.1 單元格中數據的修改3.2 單元格、行、列的插入與刪除3.3 單元格、行、列的復制與移動233.1 單元格中數據的修改 選擇對象，再單擊“開始”選項卡 下的“編輯”組中的“清除”（1）清除格式和內容（2）清除格式（3）清除內容（4）清除批注243.2 單元格、行、列的插入與刪除插入方法 先選擇位置，即插入的位置 再單擊“開始”選項卡“插入”

10、 25注意 若插入的是行或列，則原來的若插入的是行或列，則原來的行或列順延行或列順延 若插入單元格，則出現若插入單元格，則出現“提示提示對話框對話框”，由用戶選擇：，由用戶選擇：“活活動單元格右移動單元格右移”，“活動單元活動單元格下移格下移”，“整行整行”或是或是“整整列列”3.2 單元格、行、列的插入與刪除刪除方法 選中對象，執(zhí)行“開始” 選項卡“刪除”命令注意 若刪除單元格，會彈出“刪除”對話框，由用戶選擇：“右側單元格左移”，“下方單元格上移”，“整行”或是“整列”263.3單元格、行、列的復制與移動方法1：鼠標拖動 復制：將鼠標移到被選擇對象的邊框變成虛十字箭頭，按住左鍵拖動到目的地

11、 移動：拖動虛十字箭頭的同時按Ctrl鍵方法2：右鍵快捷菜單 復制：先復制，后粘貼 移動：先剪切，后粘貼方法3：快捷鍵27283.3 單元格、行、列的復制與移動選擇性粘貼 復制：先復制，后選擇性粘貼4 單元格的格式4.1 單元格格式設置4.2 工作表的設置294.1 單元格格式的設置1. 行高、列寬2. 字體的設置3. 對齊方式4. 表格線的設置（內邊框與外邊框）5. 圖案的設置6. 條件格式的設置7. 自動套用格式8. 使用與創(chuàng)建單元格樣式9. 合并單元格306. 條件格式的設置設置： “開始”選項卡“樣式”組“條件格式” 按鈕色階關系格式（大于、小于等）編輯與管理條件格式317. 自動套用

12、格式8. 使用與創(chuàng)建單元格樣式3 工作表的設置1. 添加工作表2. 刪除工作表3. 重命名工作表4. 移動、復制工作表5. 設置工作表標簽顏色331. 添加工作表342. 刪除工作表刪除的工作表不能再恢復！353. 重命名工作表 右鍵單擊工作表標簽，選擇“重命名” 雙擊要重命名的工作表標簽364. 移動、復制工作表在同一工作簿中移動工作表，直接拖動表標簽；復制工作表，則用鼠標拖動表標簽時按住ctrl鍵不同或相同工作薄之間功能區(qū)：“開始” “單元格”“格式”右鍵菜單方式375. 設置工作表標簽顏色方法如下工作表標簽，右鍵快捷菜單功能區(qū)命令：“開始”“單元格”“格式”385 數據處理5.1 數據處

13、理的相關概念與方法395.1 數據處理的相關概念與方法1. 公式2. 常用函數3. 排序4. 篩選5. 分類匯總6. 數據透視表401. 公式以“=”開始的單元格數據稱為公式 公式中的運算符 算術運算符 比較運算符 文本運算符 &形成一個長文本，即文本的合并補充：公式的輸入、復制41公式的輸入、復制1) 單擊要輸入公式結果的單元格。2) 輸入等號“=”。3) 輸入公式，所輸入的公式出現在編輯欄中。4) 按回車鍵或單擊“輸入”按鈕。輸入后的公式只能在編輯欄中看到，在單元格中只顯示其結果。421 1、直接輸入、直接輸入公式的輸入、復制1) 單擊要輸入公式結果的單元格。2) 輸入等號“=”。

14、3) 單擊在公式中出現的第一個單元格，其地址將出現在編輯欄中。4) 輸入運算符。5) 按照第3、4步的方法，連續(xù)輸入單元格地址和運算符，直到公式結束。6) 按回車鍵或單擊“輸入”按鈕。 432 2、引用單元格輸入、引用單元格輸入即用鼠標單擊要引用的即用鼠標單擊要引用的單元格來完成輸入單元格來完成輸入公式的輸入、復制如果其他單元格需要用同樣的公式進行數據計算時，可以復制公式。操作步驟如下： 選定被復制公式的單元格，單擊 “復制”。 選定要粘貼公式的單元格，單擊“選擇性粘貼”命令，打開“選擇性粘貼”對話框，然后選擇“公式”單選按鈕。 單擊“確定”按鈕，要復制公式的單元格內顯示按照該公式計算的結果。

15、44還可以用填充柄自動填充還可以用填充柄自動填充功能來實現復制公式。功能來實現復制公式。2. 常用函數函數實際上是一些預定義好的特殊的公式，以完成普通公式所不能完成的功能。函數的語法結構 =函數名（參數1，參數2，）函數名：如Sum()，Average()等，代表函數的功能，在系統(tǒng)中是唯一的。參數：它指明函數所操作的數據，可以為多種類型的數據，還可以是另一個（些）函數，另外，還可以為單元格區(qū)域的引用，如：A1:H3代表從A1到H3的這部分交叉單元格452. 常用函數與手工輸入公式方法一樣，在編輯欄中直接輸入，如“=MAX(B2:D2)”461.1.手工輸入函數手工輸入函數2.2.使用使用 插入

16、函數插入函數 對話框輸入對話框輸入1) 選定要輸入函數的單元格。選定要輸入函數的單元格。2) 單擊單擊“插入函數插入函數”命令或者單擊編輯欄左側的命令或者單擊編輯欄左側的“插入函插入函數數”按鈕按鈕 ，打開，打開“插入函數插入函數”對話框。對話框。2. 常用函數INT ROUND IF(COUNTIF) Sum(SUMIF)averagemaxMinVLOOKUP47演示演示482. 常用函數int向下取整數，取最接近的小于參數向下取整數，取最接近的小于參數的整數的整數 如如=int(3.5)，結果為，結果為3=int(-6), 結果為結果為-5492. 常用函數round按指定位數四舍五入按

17、指定位數四舍五入如如=ROUND(123.4567,0) 結果為結果為123 =ROUND(123.4567,-2) 結果為結果為100 =ROUND(172.4567,-2) 結果為結果為200 =ROUND(123.4567,2) 結果為結果為123.46502. 常用函數if(條件條件,結果結果1,結果結果2)根據條件判斷結果根據條件判斷結果如果條件為真，整個函數的值為結果如果條件為真，整個函數的值為結果1，若條件為假，則函數值為結果若條件為假，則函數值為結果2如如 =if(1010.1, 1,0) 結果為結果為0512. 常用函數countif(單元格區(qū)域單元格區(qū)域,條件條件) 統(tǒng)計單

18、元格統(tǒng)計單元格區(qū)域中符號條件的單元格數量區(qū)域中符號條件的單元格數量如如=countif(A1:A100,1) 統(tǒng)計統(tǒng)計A1到到A100中，值大于中，值大于1的單元格個數的單元格個數 =countif(B2:B100,男男)統(tǒng)計統(tǒng)計B2到到B100中，值為中，值為 男男 的單元格個數的單元格個數 =countif(C2:C100,60)統(tǒng)計統(tǒng)計C2到到C100中，中，值為值為 60 的單元格個數的單元格個數表達式表達式文本文本數字數字演示演示3. 排序52排序種類排序種類簡單排序簡單排序多多關鍵字排序關鍵字排序自定義序列排序自定義序列排序3. 排序簡單排序選中要排序的單元格區(qū)域“開始”- “編輯

19、”-“排序與篩選”-“升序”或者“降序”533. 排序多關鍵字排序選中要排序的單元格區(qū)域“開始”- “編輯”-“排序與篩選”-“自定義排序”54主要關鍵字主要關鍵字次要關鍵字次要關鍵字3. 排序自定義序列排序選中要排序的單元格區(qū)域還是選擇“自定義排序”55自定義序列自定義序列 4.篩選只顯示用戶關心的數據，隱藏不滿足條件的記錄類似“淘”選中要排序的單元格區(qū)域中的任意一個單元格“開始”- “編輯”-“排序與篩選”-“篩選”56 4.篩選57簡單篩選簡單篩選自定義自定義篩選篩選 4.篩選58清除篩選清除篩選5. 分類匯總分類匯總是數據分析的一種方法步驟 先對分類匯總的字段 在要進行分類匯總的數據清

20、單里，選取一個單元格?！皵祿?“分級顯示” “分類匯總”，打開“分類匯總”對話框 在該對話框中，選擇分類的字段，匯總方式（求和、平均等）以及選定匯總項，單擊“確定”按鈕。注意：可嵌套匯總596.數據透視表60數據透視表是一種可以快速匯總、分析大量數據表數據透視表是一種可以快速匯總、分析大量數據表格的交互式工具。格的交互式工具。 若要創(chuàng)建數據透視表，要求數據源必須是比較規(guī)則的數據, 如：數據記錄中最好不要有空白單元格或各并單元格；每個字段中數據的數據類型必須一致. 數據越規(guī)則，數據透視表使用起來越方便.6162報表生成報表生成區(qū)域區(qū)域數據數據透視表字透視表字段列表段列表可以根據此字段對報表實可

21、以根據此字段對報表實現篩選，可以顯示每類項現篩選，可以顯示每類項目相關的報表目相關的報表此字段種的每類項目會成為列此字段中的每類項目會成為一行會自動計算此字段的匯總信息會自動計算此字段的匯總信息（如求和、計數、平均值、方（如求和、計數、平均值、方差等等差等等) )數據透視表字段的使用數據透視表字段的使用使用數據透視表查看摘要與明細信息63使用數據透視表組合數據64對日期分類組合對數值數據分類組合對文本字段分類組合注意：要將字段進行組合，要求此字段里所有數據必須為同一格式，并且不能有空值，任何一個單元格為空也將導致不能組合。數據透視表的數據匯總方式65在匯總方式中有一共有在匯總方式中有一共有 1

22、1 11 種函數，包括：求和、計數、數值計數、平種函數，包括：求和、計數、數值計數、平均值、最大值、最小值、乘積、標準偏差、總體標準偏差、方差、總體均值、最大值、最小值、乘積、標準偏差、總體標準偏差、方差、總體方差。方差?！皵抵禂抵怠眳^(qū)域中的數據默認情況下通過以下方法對數據透視表中的基礎區(qū)域中的數據默認情況下通過以下方法對數據透視表中的基礎源數據進行匯總：對于數值使用源數據進行匯總：對于數值使用“求和求和”匯總方式，對于文本值使用匯總方式，對于文本值使用“計數計數”匯總方式。匯總方式。但是，我們可以手動更改匯總方式還可以自定義計算方式。但是，我們可以手動更改匯總方式還可以自定義計算方式。改變數

23、據透視表的值顯示方式66 前面我們改變的數據透視表的匯總方式其實就相當于使用工作表函數對數據的統(tǒng)計匯總。 其實通過改變數據透視表的值顯示方式，還可以對數據按照不同字段做相對比較。方法：將光標選中數據透視表中的數值字段，然后在“選項”工具面板的“活動字段”組中單擊“字段設置”命令，在彈出的“值字段設置”對話框的選擇“值顯示方式”選項卡，然后在下拉列表中選擇相應的值顯示方式即可。如果我們希望同時查看計算字段的百分比和數量合計能不能做到呢？如果我們希望同時查看計算字段的百分比和數量合計能不能做到呢？在數據透視表中使用計算字段 如，需要按照金額的 6%計算稅金?？梢允褂糜嬎阕侄?，向數據透視表添加一個新

24、的字段“稅金”。67怎樣刪除添加字段？怎樣刪除添加字段？數據透視表的數據源控制與刷新 當我們使用數據透視表生成報告后，如果數據源變化了，數據透視表報告的信息會不會自動跟著變化呢？ 1.數據源的值更改了 2.數據源增加了新的記錄，范圍變化了 默認情況下數據透視表并不會自動更新。 1）我們可以通過數據透視表的“選項”工具面板里的“數據”組的“刷新”命令來刷新數據透視表。這樣數據透視表報告就變成最新的了。 2）可以通過數據透視表的“選項”工具面板里的“數據”組的“更改數據源”命令來重新選擇數據透視表所引用的數據范圍。68建立數據透視圖 使用數據透視表生成數據透視圖696 編輯圖表與數據分析處理圖表是

25、將表格中的數據用圖形表示出來，即數據的可視化。通過圖表，就可以很容易看出Excel表格中數據的宏觀走向，總結數據的規(guī)律或從圖表中很快得出自己需要的結論圖表是相互聯(lián)系的，當數據發(fā)生變化，圖表也相應地發(fā)生變化。Excel還提供多種圖表類型，并支持用戶的自定義類型。707.編輯圖表與數據分析處理7.1 創(chuàng)建圖表7.2 編輯圖表71Excel中使用的圖表類型圖表的使用：（1）嵌入式圖表嵌入式圖表是浮在工作表之上，可以通過操作來改變圖表的位置和大小。嵌入式圖表的另一個特點就是圖表和數據在一個工作表上，這樣便于使用圖表和數據。（2）圖表工作表圖表占據整個工作表，數據和圖表在不同的工作表內，圖表將新建一個工作表。但當打印一個圖表工作表的時候會比較方便。72Excel中使用的圖表類型列舉7個大類；每個大類又細分為多種圖表類型。73柱形圖折線圖餅圖XY散點圖面積圖條形圖圓環(huán)圖柱形圖柱形圖是將各個數據點繪制成不同高度的柱形，以顯示各種數據的大小，比較直觀。274銷額（萬元） 2001802102202803203303404004503503