利用EXCEL進(jìn)行數(shù)據(jù)分析與圖表處理課件

利用EXCEL進(jìn)行數(shù)據(jù)分析與圖表處理大綱１、認(rèn)識(shí)EXCEL２、數(shù)據(jù)輸入那些事兒３、給數(shù)據(jù)整整容４、數(shù)據(jù)計(jì)算很簡單５、數(shù)據(jù)分析人人會(huì)６、拿圖表講故事定義：EXCEL是一款制作圖表的工具軟件。它是微軟公司的辦公軟件Microsoftoffice的組件之一，它可以進(jìn)行各種數(shù)據(jù)的處理、統(tǒng)計(jì)分析和輔助決策操作，廣泛地應(yīng)用于管理、統(tǒng)計(jì)財(cái)經(jīng)、金融等眾多領(lǐng)域。認(rèn)識(shí)EXCELEXCEL分析數(shù)據(jù)五步驟:認(rèn)識(shí)EXCEL明確分析目的收集數(shù)據(jù)處理數(shù)據(jù)分析數(shù)據(jù)展示數(shù)據(jù)認(rèn)識(shí)EXCEL標(biāo)題欄功能區(qū)編輯欄工作表編輯區(qū)自定義訪問工具欄單元格名稱框狀態(tài)欄一張?jiān)磾?shù)據(jù)表是做數(shù)據(jù)統(tǒng)計(jì)分析和制作圖表的基礎(chǔ)；數(shù)據(jù)表“列”為字段，“行”為記錄，一條記錄有多個(gè)屬性用多個(gè)列值來體現(xiàn)；

例：一批圖書信息購成一張表，每一種圖書屬性包括（書名、作者、出版社、出版時(shí)間、ISBN等信息），每一種圖書信息占一行。數(shù)據(jù)輸入那些事兒書名作者出版社出版時(shí)間ISBN公共政策與地區(qū)差距劉尚希,孟春等著中國財(cái)政經(jīng)濟(jì)出版社20137-5005-9398-8壓力應(yīng)對(duì)與大學(xué)生心理健康李虹著北京師范大學(xué)出版社20147-303-07163-6企業(yè)管理學(xué)主編胥悅紅經(jīng)濟(jì)管理出版社2012978-7-5096-0212-6國際貿(mào)易實(shí)務(wù)百問百答馬榮編著企業(yè)管理出版社20127-80147-708-1問題首富黃光裕張小平著浙江人民出版社2014978-7-213-04382-6歐萊雅美容王國的財(cái)富傳奇李野新,周俊宏著浙江人民出版社2014978-7-213-04251-5一維表與二維表一維表的列標(biāo)簽是字段。要從上述表中找銷售量最高是哪一年哪個(gè)地區(qū),對(duì)一維表一個(gè)排序簡單搞定,對(duì)二維表就相對(duì)復(fù)雜，為了數(shù)據(jù)分析的方便,盡量使用一維表；表1：二維表表2：一維表數(shù)據(jù)分析前請(qǐng)將合并表拆分此表按地區(qū)進(jìn)行數(shù)據(jù)篩選時(shí)(比如:北京)，結(jié)果只有一條數(shù)據(jù),本來有3條銷售信息;

方法:選中合并格,點(diǎn)擊“合并后居中”,將單元格進(jìn)行拆分。對(duì)于合并單元格,系統(tǒng)默認(rèn)把它置于被合并的最左上單元格中,其它單元格為空;數(shù)據(jù)表站著比躺著好方法：讓躺著的表站起來，通過"復(fù)制--選擇性粘貼--轉(zhuǎn)置"完成;數(shù)據(jù)輸入技巧輸入以0開頭的數(shù)字，先輸入英文狀態(tài)的單引號(hào)即可，也可在輸入前將單元格格式設(shè)置為"文本"再輸入;

輸入分?jǐn)?shù),在開頭輸入0和空格即可;

輸入身份證號(hào)碼,先輸入英文狀態(tài)單引號(hào),系統(tǒng)會(huì)將其識(shí)別為文本而非數(shù)值;

設(shè)置單元格格式為日期即可；選中當(dāng)前列，設(shè)置單元格格式,自定義格式中輸入"HDFT"00"-"000,在輸入時(shí)直接輸入00020，系統(tǒng)自動(dòng)顯示為HDFT00-020,下列各單元格依次輸入04150,06018…即可;自定義數(shù)字格式在輸入系列設(shè)備代號(hào)或人事編號(hào)是可以使用此功能;此處0為數(shù)字占位符;大量小數(shù)的輸入通過"文件-選項(xiàng)-高級(jí)-自動(dòng)插入小數(shù)點(diǎn)",設(shè)置小數(shù)點(diǎn)位數(shù)(例如:2位),只輸入數(shù)字部分,提高輸入效率;整數(shù)后需補(bǔ)齊0(0的個(gè)數(shù)與設(shè)置的小位點(diǎn)位數(shù)一致);使用：學(xué)生成績、財(cái)務(wù)金額等；數(shù)據(jù)有效性設(shè)置當(dāng)一張表格給多人來填報(bào)或需輸入數(shù)據(jù)條數(shù)較多時(shí)通過數(shù)據(jù)有限性設(shè)置保證數(shù)據(jù)輸入的正確性和規(guī)范性;可以設(shè)置數(shù)據(jù)類型（整數(shù)、小數(shù)、序列、日期等）；可以設(shè)置輸入提示，也可以設(shè)置輸入錯(cuò)誤時(shí)警告語；填充柄的妙用填充柄可以將事先設(shè)置好的系列內(nèi)容填充到拖拉到的區(qū)域,系統(tǒng)提供了等差填充,等比填充,星期填充,月份填充,日期填充等；在星期填充,日期填充中還可選擇工作日填充;在帶公式的填充中填充柄也發(fā)揮極大的作用；通過“文件--選項(xiàng)--高級(jí)--常規(guī)--自定義序列”可以導(dǎo)入表中某一列固定內(nèi)容,下次使用時(shí)填入第一項(xiàng),拖拉填充柄實(shí)現(xiàn)自動(dòng)填充序列；單元格引用單元格引用：相對(duì)引用、絕對(duì)引用、混合引用使用“$”符號(hào)來鎖定引用。相對(duì)引用：A1、B5絕對(duì)引用：$A$5、$C$2快捷鍵為F4混合引用：$A2、D$1主要在公式中靈活選用。引用格式:工作薄存放地址[工作薄名稱]工作表名稱!單元格地址例：E3=[EXCEL演示圖表.xlsx]加班!C2跨工作薄引用目的:使數(shù)據(jù)便于閱讀;路徑:視圖--窗口--凍結(jié)窗格三種方式:凍結(jié)首行,凍結(jié)首列,凍結(jié)拆分窗格;凍結(jié)首行:光標(biāo)條上下拉動(dòng)時(shí)首行不動(dòng),對(duì)于行數(shù)多的表,往下翻看數(shù)據(jù)時(shí)可以清楚看到表頭;凍結(jié)首列:光標(biāo)條左右拉動(dòng)時(shí)首列不動(dòng),對(duì)于列數(shù)多的表,往下翻看數(shù)據(jù)時(shí)可以清楚看到第一列;凍結(jié)拆分窗格:將光標(biāo)放在C4單元格,執(zhí)行此命令,表示C之前的列(即AB列凍結(jié))4之前的行(123行凍結(jié));凍結(jié)窗格目的:使數(shù)據(jù)便于閱讀;方式:選中要隱藏的行(或列),點(diǎn)擊鼠標(biāo)右鍵,執(zhí)行"隱藏"命令;取消:選中被隱藏的行(或列)的上下(或前后)兩行,點(diǎn)擊鼠標(biāo)右鍵,執(zhí)行"取消隱藏"命令即可;

隱藏行或列目的:快速閱讀數(shù)據(jù)的最外邊(Ctrl+右箭頭),最下邊(Ctrl+下箭頭);在大量數(shù)據(jù)面前,用鼠標(biāo)滾動(dòng)下翻實(shí)在OUT了;"Ctrl+箭頭鍵"數(shù)據(jù)一步到底選中數(shù)據(jù)區(qū)域,通過"數(shù)據(jù)--分級(jí)顯示--分類匯總",設(shè)置匯總項(xiàng)(銷售額),分類字段(省份),匯總方式(求和),系統(tǒng)自動(dòng)生成按省份的分類匯總表;通過左側(cè)+和-按鈕可以顯示和隱藏被匯總的信息;前提:進(jìn)行分類匯總前需對(duì)分類字段進(jìn)行排序;缺點(diǎn):一次只能對(duì)一個(gè)字段進(jìn)行匯總,只能構(gòu)成一維匯總表;要想對(duì)兩個(gè)或兩個(gè)以上字段進(jìn)行匯總,制作二維匯總表.分類匯總表例如:要進(jìn)行每個(gè)省份每個(gè)月銷售匯總;或者每個(gè)產(chǎn)品在每個(gè)省份的銷售匯總,這類問題比較常見,需使用"數(shù)據(jù)透視表"功能;選中數(shù)據(jù)區(qū)域,"插入--數(shù)據(jù)透視表",設(shè)置存放位置,選擇字段,在行標(biāo)簽,列標(biāo)簽,數(shù)值區(qū)域拖動(dòng)字段;數(shù)據(jù)透視表查找與去重查找與替換排序與篩選函數(shù)函數(shù)分類函數(shù)名功能舉例文本MID從文本字符串中指定的起始位置起,返回指定長度的字符=MID(D3,7,4)"1980LEFT從文本字符串左邊起,返回指定長度的字符;A11"=LEFT(D4,1)"ALEN返回文本字符串的字符數(shù)F713.50"=LEN(D4)"7邏輯IF判斷是否滿足某個(gè)條件,滿足返會(huì)一個(gè)值,不滿足返回另一個(gè)值;89"=IF(D5>80,"優(yōu)","")"優(yōu)AND所有條件參數(shù)均為真,結(jié)果返回TRUE,否則返回FALSE;A1=68,B1=87"=AND(A1>80,B1>80)"FALSEOR只要其中一個(gè)條件為真,結(jié)果返回TRUE,否則返回FALSE;A1=68,B1=87"=OR(A1>80,B1>80）"TRUE統(tǒng)計(jì)COUNTIF計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格個(gè)數(shù);參考函數(shù)說明和幫助

MAX返回一組數(shù)值的最大值

MIN返回一組數(shù)值的最小值

查找與引用LOOKUP從單行或單列或數(shù)組中查找一個(gè)值

HLOOKUP通過與首行值的對(duì)比來查找值

VLOOKUP通過與首列值的對(duì)比來查找值

財(cái)務(wù)

可以計(jì)算貸款月支付額,累計(jì)償還金額,資產(chǎn)折舊值多種財(cái)務(wù)數(shù)據(jù);

數(shù)學(xué)和三角函數(shù)

工程

日期和時(shí)間

…

MID：從文本字符串中指定的起始位置起,返回指定長度的字符;例：A1B1=MID（A1，7，4）結(jié)果：1980LEFT：從文本字符串左邊起,返回指定長度的字符;例：A1（F713.50）B1=LEFT（A1，1）結(jié)果：FRIGHT：從文本字符串右邊起,返回指定長度的字符;例：A1（HDFT020-25）B1=RIGHT（A1，2）結(jié)果：25LEN：返回文本字符串的字符數(shù)；例：A1（F713.50）B1=LEN（A1）結(jié)果：7文本函數(shù)IF：判斷是否滿足某個(gè)條件,滿足返會(huì)一個(gè)值,不滿足返回另一個(gè)值;例：A1=89，D1=IF（A1>80，,“優(yōu)”,“”),結(jié)果：優(yōu)AND：所有條件參數(shù)均為真,結(jié)果返回TRUE,否則返回FALSE;例：A1=68,B1=87，C1=AND(A1>80,B1>80)，結(jié)果：FALSE；OR：只要其中一個(gè)條件為真,結(jié)果返回TRUE,否則返回FALSE;例：A1=68,B1=87，C1=OR(A1>80,B1>80），結(jié)果：TURE；邏輯函數(shù)統(tǒng)計(jì)函數(shù)COUNTIF：計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格個(gè)數(shù);例：=COUNTIF（B：B，B3），計(jì)算B列中與B3單元格值相同的單元格個(gè)數(shù)。COUNTIF（range,criteria)參數(shù)：range要計(jì)算其中非空單元格數(shù)目的區(qū)域參數(shù)：criteria以數(shù)字、表達(dá)式或文本形式定義的條件MAX：返回一組數(shù)值的最大值MIN：返回一組數(shù)值的最小值查找與引用函數(shù)LOOKUP：從單行或單列或數(shù)組中查找一個(gè)值在計(jì)算第1個(gè)“稅率”時(shí)，輸入函數(shù)公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”，回車，便可得到“36.00%”。用F4中的第1個(gè)收入數(shù)“$123,409”，與左側(cè)表的“收入最低”各檔數(shù)據(jù)(“$B$3:$B$8”)進(jìn)行對(duì)比，雖然“$123,409”在“收入最低”各檔數(shù)中沒有完全一致的數(shù)據(jù)與之匹配，但是會(huì)與其中小于它的最大數(shù)“$58,501”相匹配。這樣，同一行對(duì)應(yīng)的“36.00%”就提取出來了。EXCEL為用戶提供了11類73種圖表。最基本類型有六種:柱形圖折線圖餅圖條形圖面積圖散點(diǎn)圖圖表圖表元素腳注（對(duì)圖表進(jìn)行補(bǔ)充說明）圖表標(biāo)題圖例縱坐標(biāo)軸（Y軸)網(wǎng)格線橫坐標(biāo)軸（X軸）圖表區(qū)繪圖區(qū)數(shù)據(jù)系列插入到圖表中的其他對(duì)象不等寬柱形圖說明：右鍵設(shè)置數(shù)據(jù)系列格式（重疊型、無間距）處理超大值用截?cái)鄻?biāo)記技巧：超大值44實(shí)用值為5，手工繪制截?cái)鄻?biāo)記處理負(fù)數(shù)技巧：用堆積柱形圖，繪圖前將正負(fù)數(shù)放在不同列給折線圖整容美化后的折線圖美化的折線圖是對(duì)數(shù)據(jù)進(jìn)行了預(yù)處理迷你圖餅圖源數(shù)據(jù)表注意：計(jì)算百分比涉及到相對(duì)引用和絕對(duì)引用。