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

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

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

輸入分數(shù),在開頭輸入0和空格即可;

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

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

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

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

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

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

HLOOKUP通過與首行值的對比來查找值

VLOOKUP通過與首列值的對比來查找值

財務

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

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

工程

日期和時間

…

函數(shù)函數(shù)分類函數(shù)名功能舉例文本MID從文本字符串中指定的起始25MID：從文本字符串中指定的起始位置起,返回指定長度的字符;例：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ù)MID：從文本字符串中指定的起始位置起,返回指定長度的字符;26IF：判斷是否滿足某個條件,滿足返會一個值,不滿足返回另一個值;例：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：只要其中一個條件為真,結(jié)果返回TRUE,否則返回FALSE;例：A1=68,B1=87，C1=OR(A1>80,B1>80），結(jié)果：TURE；邏輯函數(shù)IF：判斷是否滿足某個條件,滿足返會一個值,不滿足返回另一個27統(tǒng)計函數(shù)COUNTIF：計算某個區(qū)域中滿足給定條件的單元格個數(shù);例：=COUNTIF（B：B，B3），計算B列中與B3單元格值相同的單元格個數(shù)。COUNTIF（range,criteria)參數(shù)：range要計算其中非空單元格數(shù)目的區(qū)域參數(shù)：criteria以數(shù)字、表達式或文本形式定義的條件MAX：返回一組數(shù)值的最大值MIN：返回一組數(shù)值的最小值統(tǒng)計函數(shù)COUNTIF：計算某個區(qū)域中滿足給定條件的單元格個28查找與引用函數(shù)LOOKUP：從單行或單列或數(shù)組中查找一個值在計算第1個“稅率”時，輸入函數(shù)公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”，回車，便可得到“36.00%”。用F4中的第1個收入數(shù)“$123,409”，與左側(cè)表的“收入最低”各檔數(shù)據(jù)(“$B$3:$B$8”)進行對比，雖然“$123,409”在“收入最低”各檔數(shù)中沒有完全一致的數(shù)據(jù)與之匹配，但是會與其中小于它的最大數(shù)“$58,501”相匹配。這樣，同一行對應的“36.00%”就提取出來了。查找與引用函數(shù)LOOKUP：從單行或單列或數(shù)組中查找一個值在29EXCEL為用戶提供了11類73種圖表。最基本類型有六種:柱形圖折線圖餅圖條形圖面積圖散點圖圖表EXCEL為用戶提供了11類73種圖表。圖表30圖表元素腳注（對圖表進行補充說明）圖表標題圖例縱坐標軸（Y軸)網(wǎng)格線橫坐標軸（X軸）圖表區(qū)繪圖區(qū)數(shù)據(jù)系列插入到圖表中的其他對象圖表元素腳注（對圖表進行補充說明）圖表標題圖例縱坐標軸（Y軸31不等寬柱形圖說明：右鍵設置數(shù)據(jù)系列格式（重疊型、無間距）不等寬柱形圖說明：右鍵設置數(shù)據(jù)系列格式（重疊型、無間距）32處理超大值用截斷標記技巧：超大值44實用值為5，