EXCEL與數(shù)據(jù)分析課件

1、授課教師：馬銀戌,excel與數(shù)據(jù)分析,2,課程教學(xué)內(nèi)容（34課時(shí),第一章 excel數(shù)據(jù)處理入門(mén)（2課時(shí)） 第二章 定性數(shù)據(jù)的基本特征分析（6課時(shí)） 第三章 定量數(shù)據(jù)的基本特征分析（4課時(shí)） 第四章 數(shù)據(jù)間的相關(guān)性分析（6課時(shí)） 第五章 數(shù)據(jù)間的差異性分析（4課時(shí)） 第六章 數(shù)據(jù)的變動(dòng)趨勢(shì)分析（6課時(shí)） 第七章 excel與數(shù)據(jù)綜合分析（6課時(shí),3,第一節(jié) excel概述,第二節(jié) 數(shù)據(jù)的錄入與編輯,第三節(jié) 數(shù)據(jù)的類(lèi)型與預(yù)處理,第一章 excel數(shù)據(jù)處理入門(mén),4,第一節(jié) excel概述,一、excel的基本功能 二、excel的基本概念 三、excel數(shù)據(jù)分析工具的加載,5,一、excel的基

2、本功能,microsoft excel是由微軟公司開(kāi)發(fā)的一種電子表格程序，是微軟公司的辦公套裝軟件microsoft office的重要組成部分。 excel功能強(qiáng)大、使用方便。為日常生活中處理各式各樣的表格提供了良好的工具。 它可完成表格數(shù)據(jù)的輸入、加工整理、分類(lèi)匯總、簡(jiǎn)單計(jì)算等多項(xiàng)工作，可生成精美直觀的數(shù)據(jù)清單、表格、圖表。 excel 中大量的公式函數(shù)可以應(yīng)用選擇，使用excel可以執(zhí)行繁重而復(fù)雜的計(jì)算，可以分析信息并管理電子表格或網(wǎng)頁(yè)中的數(shù)據(jù)信息列表，帶給使用者方便。 excel的數(shù)據(jù)分析工具，還可以進(jìn)行各種數(shù)據(jù)的高級(jí)處理、統(tǒng)計(jì)分析和輔助決策操作，因而可以廣泛地應(yīng)用于統(tǒng)計(jì)、管理、財(cái)經(jīng)、

3、金融等眾多領(lǐng)域,6,二、 excel的基本概念,excel中的基本概念有：工作簿、工作表、單元格及單元格地址,工作簿,工作簿名,一張工作表及其名稱,單元格,行號(hào),列號(hào),7,工作簿是工作表、圖表及宏表的集合，它以文件 的形式存放在計(jì)算機(jī)的外存貯器中。新創(chuàng)建的工作簿，excel將自動(dòng)給其命名，如book1、book2用戶可以重新賦予工作簿有意義的名字，如“員工檔案資料”。 工作表是用于編輯、顯示和分析一組數(shù)據(jù)的表格，它由排成行和列的單元格組成，每張工作表由65536行和256列單元格組成。工作表是包含在工作簿中的。一個(gè)工作簿最多可包含255張工作表。在新建工作簿時(shí)，excel默認(rèn)3張工作表，并自動(dòng)

4、給工作表命名為sheet1、sheet2、sheet3，用戶可根據(jù)需要添加工作表，可對(duì)每張工作表重新命名，如“員工工資”等,8,單元格是構(gòu)成工作表的基本元素，用于輸入、顯示和計(jì)算數(shù)據(jù)，一個(gè)單元格內(nèi)只能存放一個(gè)數(shù)據(jù)。 單元格地址用來(lái)標(biāo)識(shí)一個(gè)單元格的坐標(biāo)，它由列號(hào)行號(hào)組合表示。其中，行號(hào)由1、2、3表示，列號(hào)由a、b、c表示。 excel對(duì)單元格地址的引用有四種方式： 相對(duì)地址：形式為“行號(hào)列號(hào)”，如“a5”。 絕對(duì)地址：形式為“$行號(hào)$列號(hào)”，如“$a$5”。 混合地址：是相對(duì)地址和絕對(duì)地址的混合，如“a$5”。 三維地址：是在單元格地址前面加上工作表的名稱和感嘆號(hào)。如“sheet3！a5”表示

5、sheet3工作表中的a5單元格,9,三、excel數(shù)據(jù)分析工具的加載,excel中提供了一組數(shù)據(jù)分析工具，稱為“分析工具庫(kù) ” ，使用這些工具在建立復(fù)雜統(tǒng)計(jì)或工程分析時(shí)可節(jié)省操作步驟和時(shí)間。應(yīng)用時(shí)，只需為每一個(gè)分析工具提供必要的數(shù)據(jù)和參數(shù)，該工具就會(huì)使用適當(dāng)?shù)慕y(tǒng)計(jì)或工程宏函數(shù)，在輸出表格中顯示相應(yīng)的結(jié)果，其中有些工具在生成輸出表格的同時(shí)還能生成圖表，用戶通過(guò)這些分析工具可以很輕松地分析數(shù)據(jù)。 但需要注意的是，“分析工具庫(kù)” 在安裝microsoft office excel后，需要通過(guò)在excel中加載宏之后才能使用。加載“分析工具庫(kù)”有兩種方法,10,方法一： （1）打開(kāi)excel，單擊“

6、工具”，在下拉菜單中單擊“加載宏”，打開(kāi)如下的對(duì)話框,2）勾選“可用加載宏”列表框中的“分析工具庫(kù)”，然后單擊“確定”，系統(tǒng)將會(huì)引導(dǎo)用戶進(jìn)行安裝,3）安裝完畢后，“數(shù)據(jù)分析”會(huì)自動(dòng)出現(xiàn)在excel的“工具”菜單中,11,方法二： （1）在windows中單擊“開(kāi)始”“搜索”，在彈出的對(duì)話框中單擊“所有文件和文件夾”，然后在“全部或部分文件名”下面的框內(nèi)輸入“analys32.xll”，單擊 “搜索”，搜索程序就會(huì)在硬盤(pán)中搜索該文件,2）雙擊搜索到的“analys32.xll”文件，在打開(kāi)的對(duì)話框中單擊“啟用宏”，系統(tǒng)就會(huì)將該文件加載宏安裝到excel中,3）安裝完畢后，“數(shù)據(jù)分析”會(huì)自動(dòng)出現(xiàn)在

7、excel的“工具”菜單中,12,第二節(jié) 數(shù)據(jù)的錄入與編輯,一、工作簿和工作表的基本操作 二、錄入數(shù)據(jù) 三、編輯數(shù)據(jù),13,一、工作簿和工作表的基本操作,新建工作簿 有三種途徑： *啟動(dòng)excel自動(dòng)創(chuàng)建。工作簿自動(dòng)命名為book1（或book2、book3），用戶可根據(jù)需要對(duì)工作簿重命名，每個(gè)工作簿默認(rèn)包含3張工作表。 *單擊工具欄中新建按鈕，自動(dòng)出現(xiàn)一新工作簿，其命名方法同上。 *根據(jù)模板新建工作簿。單擊“文件”菜單，選擇“新建”，窗口右側(cè)會(huì)出現(xiàn)一個(gè)對(duì)話框，允許用戶選擇“模板”，然后excel再根據(jù)模板來(lái)創(chuàng)建新工作簿,14,保存工作簿 新建的工作簿呈現(xiàn)打開(kāi)的可編輯狀態(tài)，如果不保存起來(lái)則所做

8、的工作就無(wú)效。要養(yǎng)成隨時(shí)保存的良好習(xí)慣。保存工作簿可以分為保存新建工作簿、另存工作簿和自動(dòng)保存工作簿3種情況： *保存新建工作簿。新建的工作簿在第一次保存時(shí)，應(yīng)該為其指定保存位置，并輸入文件名。 *另存工作簿。對(duì)打開(kāi)的工作簿進(jìn)行編輯后，可以直接保存，也可以將其另存：一是將工作簿以其他名字保存在相同路徑下；二是以相同名字保存在其他路徑下；三是以其他名字保存在其他路徑下。 *自動(dòng)保存工作簿。單擊“工具”菜單，點(diǎn)擊 “選項(xiàng)”，在選項(xiàng)對(duì)話框中選中“保存自動(dòng)恢復(fù)信息”復(fù)選框，在右側(cè)的數(shù)值框中輸入進(jìn)行自動(dòng)保存的時(shí)間間隔（如10分鐘），單擊“確定”即可。也可在工作過(guò)程中隨時(shí)點(diǎn)擊保存按鈕手動(dòng)保存文件,15,工

9、作表的基本操作 每個(gè)工作簿默認(rèn)包含3張工作表，用戶可根據(jù)需要添加或刪除工作表；可移動(dòng)工作表的位置，可重新命名工作表。 *添加工作表。單擊“插入”菜單，點(diǎn)擊“工作表”即可插入一張新工作表，一個(gè)工作簿最多可容納255張工作表。 *刪除工作表。打開(kāi)要?jiǎng)h除的工作表，單擊“編輯”菜單，點(diǎn)擊“刪除工作表”即可。 *移動(dòng)工作表。單擊要移動(dòng)的工作表，拖到相應(yīng)的位置即可。 *重新命名工作表。右擊要重命名的工作表名稱，在復(fù)選框中點(diǎn)擊“重命名”，然后輸入新的名稱即可,16,二、錄入數(shù)據(jù),excel中廣義的數(shù)據(jù)包括數(shù)字、文本、符號(hào)、字母等各種形式。錄入各種形式數(shù)據(jù)的步驟相同： *單擊要輸入數(shù)據(jù)的單元格，使其成為當(dāng)前單

10、元格。 *在此單元格中輸入數(shù)據(jù)。 *按回車(chē)鍵結(jié)束。 注意： *如果要將數(shù)字作為文本型數(shù)據(jù)保存，必須在前面加單撇號(hào)（）。 *輸入日期和時(shí)間型數(shù)據(jù)，一般用斜線（/）和連字符（-）用作日期分隔符，冒號(hào)（ ：）用作時(shí)間分隔符,17,快速填充數(shù)據(jù),excel提供了數(shù)據(jù)填充功能，用以自動(dòng)生成有規(guī)律的數(shù)據(jù)，如相同數(shù)據(jù)、等比、等差數(shù)列等，可提高輸入數(shù)據(jù)的效率,在一行或一列中產(chǎn)生相同數(shù)據(jù)： 向某一單元格輸入第一個(gè)數(shù)據(jù)，然后單擊該單元格右下角，此時(shí)鼠標(biāo)變?yōu)?號(hào)，按住鼠標(biāo)左鍵向下（或左右）拖動(dòng)即可。 *在一行或一列中產(chǎn)生等差或等比數(shù)列： 向某一單元格輸入第一個(gè)數(shù)據(jù)，單擊“編輯”菜單，單擊“填充”子菜單，選擇“序列”

11、菜單項(xiàng)，出現(xiàn)如下對(duì)話框,18,在對(duì)話框中，根據(jù)需要確定如下內(nèi)容：數(shù)列在工作表上是以行還是列方式生成；數(shù)列的類(lèi)型；日期型數(shù)據(jù)的時(shí)間單位；等差或?qū)Ρ葦?shù)列的步長(zhǎng)值及終止值。 單擊“確定”按鈕，即可生成相應(yīng)數(shù)列。 對(duì)于步長(zhǎng)值為1的等差數(shù)列，也可按住ctrl鍵，同時(shí)單擊第一個(gè)單元格右下角，向下（或左右）拖動(dòng)即可,19,三、編輯數(shù)據(jù),數(shù)據(jù)移動(dòng)、復(fù)制和粘貼 對(duì)于普通數(shù)據(jù)的移動(dòng)、復(fù)制和粘貼操作，可通過(guò)三種方法實(shí)現(xiàn)： （1）選中要操作的單元格單擊“編輯”菜單點(diǎn)擊“復(fù)制”或“剪切” 單擊目標(biāo)單元格單擊“編輯”菜單點(diǎn)擊“粘貼”。 （2）選中要操作的單元格點(diǎn)擊鼠標(biāo)右鍵點(diǎn)擊“復(fù)制”或“剪切” 右擊目標(biāo)單元格點(diǎn)擊“粘貼”

12、。 （3）使用快捷鍵ctrl+x（剪切）、ctrl+c（復(fù)制）和ctrl+v（粘貼）完成,20,使用“選擇性粘貼”完成數(shù)據(jù)復(fù)制和粘貼 選中要操作的單元格單擊“編輯”菜單（或單擊右鍵）點(diǎn)擊“復(fù)制”（注意不能使用“剪切”） 單擊目標(biāo)單元格單擊“編輯”菜單（或右擊目標(biāo)單元格）點(diǎn)擊“選擇性粘貼”。出現(xiàn)如下復(fù)選框,可根據(jù)需要在復(fù)選框中“粘貼”子菜單下選擇有關(guān)項(xiàng)目,21,“選擇性粘貼”具有特殊功能，常用的有： 以“全部”方式粘貼：將要復(fù)制的文件，按照原樣粘貼到相應(yīng)的位置。 以“公式”方式粘貼：將要復(fù)制的文件，按照公式方式粘貼到相應(yīng)的位置。 以“數(shù)值”方式粘貼：將要復(fù)制的文件，從中去除格式、公式，僅提取數(shù)據(jù)

13、內(nèi)容，粘貼到相應(yīng)的位置。 以“格式”方式粘貼：將要復(fù)制的文件，從中去除數(shù)據(jù)、公式，僅提取格式內(nèi)容，粘貼到相應(yīng)的位置,22,使用“選擇性粘貼”完成數(shù)據(jù)計(jì)算 “選擇性粘貼”不僅可以進(jìn)行數(shù)據(jù)的復(fù)制，還可進(jìn)行數(shù)據(jù)計(jì)算,如果要對(duì)表格中的特定數(shù)據(jù)進(jìn)行加減乘除計(jì)算，可在復(fù)選框中“運(yùn)算”子菜單下選擇有關(guān)項(xiàng)目,23,案例：一次性為所有員工增加600元節(jié)日獎(jiǎng)金 （1）在任意單元格中輸入600，并按ctrl+c快捷鍵； （2）選中所有員工獎(jiǎng)金單元格 （3）選中“選擇性粘貼”選項(xiàng) （4）在“運(yùn)算”子菜單下選中“加,24,使用“選擇性粘貼”完成行列轉(zhuǎn)置 利用“選擇性粘貼” 還可以進(jìn)行數(shù)據(jù)的行列轉(zhuǎn)置。所謂行列轉(zhuǎn)置，指的是

14、將選擇復(fù)制的行或列，在粘貼復(fù)制時(shí)轉(zhuǎn)置為列或行,只需在“選擇性粘貼”復(fù)選框中選中“轉(zhuǎn)置”項(xiàng)即可實(shí)現(xiàn),25,數(shù)據(jù)查找和替換 *一般數(shù)據(jù)的查找和替換： 單擊“編輯”菜單點(diǎn)擊“查找” 輸入要查找的文本或數(shù)字單擊“查找下一個(gè)” 單擊“替換”下拉子菜單在“替換為”后面的空白框中輸入要替換的內(nèi)容 。 *按照特定的格式條件查找指定數(shù)據(jù) 如果事先對(duì)某些數(shù)據(jù)設(shè)置了特定格式，要查找這些數(shù)據(jù)，單擊“編輯”菜單點(diǎn)擊“查找” 單擊“選項(xiàng)” 單擊“格式” 單擊“格式”，然后從各種格式的復(fù)選框中尋找要查找的格式 單擊“確定” 單擊“查找下一個(gè)” 。 案例：在員工初始檔案中，有幾個(gè)人的學(xué)歷掌握不準(zhǔn)，因此錄入時(shí)用了紅色字體。經(jīng)核

15、對(duì)后，需進(jìn)行調(diào)整,26,第三節(jié) 數(shù)據(jù)的類(lèi)型和預(yù)處理,一、數(shù)據(jù)的類(lèi)型劃分 二、數(shù)據(jù)的預(yù)處理,27,一、數(shù)據(jù)的類(lèi)型劃分,數(shù)據(jù),定性數(shù)據(jù) 定量數(shù)據(jù),定性數(shù)據(jù)：不能用數(shù)字表示，可用文字、符號(hào)等表示。 又可分為分類(lèi)數(shù)據(jù)和順序數(shù)據(jù)。 分類(lèi)數(shù)據(jù)是只能劃分類(lèi)別的非數(shù)值型數(shù)據(jù)； 順序數(shù)據(jù)是可以劃分有序類(lèi)別的非數(shù)值型數(shù)據(jù),定量數(shù)據(jù)：數(shù)值型數(shù)據(jù)，用數(shù)字表示,不同類(lèi)型的數(shù)據(jù)加工整理和分析的方法不同,28,二、數(shù)據(jù)的預(yù)處理,數(shù)據(jù)的預(yù)處理是進(jìn)行數(shù)據(jù)分析的前提步驟，包括數(shù)據(jù)的審核、篩選、排序、分類(lèi)（分組）、簡(jiǎn)單匯總以及所需新指標(biāo)數(shù)據(jù)的計(jì)算等,一）數(shù)據(jù)的審核和篩選,數(shù)據(jù)的審核主要從完整性和準(zhǔn)確性兩個(gè)方面進(jìn)行。 *完整性審核主

16、要是檢查數(shù)據(jù)是否有遺漏，各種相關(guān)項(xiàng)目如行列標(biāo)題、編碼、序號(hào)、計(jì)量單位等是否齊全。 *準(zhǔn)確性審核主要是檢查數(shù)據(jù)是否有錯(cuò)誤，計(jì)算是否正確等。具體又包括邏輯檢查和計(jì)算檢查。邏輯檢查主要是針對(duì)定性數(shù)據(jù)進(jìn)行，計(jì)算檢查主要是針對(duì)定量數(shù)據(jù)進(jìn)行,29,邏輯檢查主要是從定性角度審核數(shù)據(jù)是否符合邏輯，內(nèi)容是否合理，各項(xiàng)目或數(shù)字之間有無(wú)相互矛盾的現(xiàn)象等。如工資總額等于基本工資加獎(jiǎng)金，而某人的獎(jiǎng)金高于其工資總額，顯然屬于邏輯性錯(cuò)誤，應(yīng)糾正。 *計(jì)算檢查是檢查各項(xiàng)數(shù)據(jù)在錄入、匯總、計(jì)算結(jié)果和計(jì)算方法等方面是否有錯(cuò)。 * 數(shù)據(jù)的篩選 數(shù)據(jù)篩選包括兩方面內(nèi)容：一是將某些不符合要求的數(shù)據(jù)或有明顯錯(cuò)誤的數(shù)據(jù)予以剔除；二是將符合

17、某種特定條件的數(shù)據(jù)篩選出來(lái)，對(duì)不符合條件的數(shù)據(jù)予以剔除。利用excel進(jìn)行篩選，包括“自動(dòng)篩選”和“高級(jí)篩選”兩種形式,30,自動(dòng)篩選的使用 “自動(dòng)篩選”功能只能對(duì)單一條件進(jìn)行篩選，是excel最基本的操作。操作步驟為： 打開(kāi)excel文件，選中文件數(shù)據(jù)區(qū)域任一單元格，單擊“數(shù)據(jù)”下拉菜單點(diǎn)擊“篩選” “自動(dòng)篩選”，然后在每列第一行的字段名下顯出一個(gè)小三角，單擊小三角，在其列表框中選中所要篩選的條件內(nèi)容即可。若有多個(gè)條件需要篩選，則需多次從相應(yīng)字段名小三角下的列表框中進(jìn)行篩選。 案例：從企業(yè)員工基本資料中篩選出性別為“男”、學(xué)歷為“碩士”的員工,31,高級(jí)篩選的使用 “高級(jí)篩選”功能可以一次性

18、對(duì)多個(gè)條件進(jìn)行篩選，在數(shù)據(jù)較大較復(fù)雜時(shí)使用此功能較為快捷。操作步驟為： 打開(kāi)excel文件，在空白位置輸入篩選條件，單擊“數(shù)據(jù)”下拉菜單點(diǎn)擊“篩選” 打開(kāi)“高級(jí)篩選”對(duì)話框設(shè)置高級(jí)篩選條件以及篩選結(jié)果顯示的位置，即可顯示出滿足篩選條件的數(shù)據(jù)。 案例：從企業(yè)員工基本資料中篩選出性別為“男” 、年齡40 、學(xué)歷為“碩士”的員工,32,二）數(shù)據(jù)的排序和分類(lèi)匯總,一般來(lái)說(shuō)，錄入數(shù)據(jù)清單的數(shù)據(jù)是無(wú)序的，不能反映現(xiàn)象是本質(zhì)與規(guī)律性。為了方便分析，要將其進(jìn)行排序、分組，以使數(shù)據(jù)按要求排列，同時(shí)把性質(zhì)相同的數(shù)據(jù)歸為一組，讓不同組數(shù)據(jù)之間的差異性顯示出來(lái)。 *數(shù)據(jù)的排序，基本步驟如下： 打開(kāi)excel文件，選中

19、文件數(shù)據(jù)區(qū)域任一單元格，單擊“數(shù)據(jù)”下拉菜單點(diǎn)擊“排序” ，然后在“主要關(guān)鍵字”下的列表框中選擇要排序數(shù)據(jù)的字段名，在右側(cè)選擇“升序”或“降序”。如果要把相同的記錄再排序，則可在“次要關(guān)鍵字”下的列表框中選擇要排序數(shù)據(jù)的字段名，在右側(cè)選擇“升序”或“降序,33,案例：對(duì)所有員工按學(xué)歷和工資總額排序 （1）打開(kāi)excel文件，單擊數(shù)據(jù)區(qū)域任意單元格； （2）單擊“數(shù)據(jù)”下拉菜單點(diǎn)擊“排序”，出現(xiàn)如下對(duì)話框,3）在“主要關(guān)鍵字”下的列表框中選擇“學(xué)歷”，在右側(cè)選擇“升序”； （4）在“次要關(guān)鍵字”下的列表框中選擇“工資總額”，在右側(cè)選擇“降序”； （5）單擊“確定”，完成排序,34,數(shù)據(jù)的分類(lèi)匯總，基本步驟如下： （1）在數(shù)據(jù)排序的基礎(chǔ)上，單擊文件數(shù)據(jù)區(qū)域任一單元格； （2）單擊“數(shù)據(jù)”下拉菜單點(diǎn)擊“分類(lèi)匯總” ，出現(xiàn)分類(lèi)匯總的對(duì)話框,3）在“分類(lèi)字段”下的列表框中選擇要分類(lèi)的字段名； （4）在“匯總方式”下的列表框中選擇“求和”； （5）在“選定匯總項(xiàng)”下的列表框中選擇要匯總數(shù)值的字段名。 （6）單擊“確定”，完成分類(lèi)匯總,35,案例：對(duì)所有員工按學(xué)歷進(jìn)行工資總額的分類(lèi)匯總 （1）打開(kāi)excel文件，單擊數(shù)據(jù)區(qū)域任意單元格； （2）單擊