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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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