《Excel數(shù)據(jù)處理與可視化》（第二版） 課件 韓春玲 第1部分 數(shù)據(jù)采集整

第1部分 數(shù)據(jù)采集整理----1.1數(shù)據(jù)錄入與基本設置1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式1．“0”數(shù)字占位符數(shù)字“0”代表占位符，如果單元格的內容大于指定占位符，則顯示實際數(shù)字；如果小于占位符的數(shù)量，則用0補足。1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式2．“#”數(shù)字占位符“#”數(shù)字占位符，只顯有意義的零，不顯示無意義的零。小數(shù)點后數(shù)字如大于“#”的數(shù)量,則按“#”的位數(shù)四舍五入。1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式3．“?”數(shù)字占位符“?”數(shù)字占位符，在小數(shù)點兩邊為無意義的零添加空格，以實現(xiàn)按小數(shù)點對齊。1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式4．“,”千分位分隔符“,”千分位分隔符。在數(shù)字中，每隔三位數(shù)加進一個逗號，也就是千位分隔符，以免數(shù)字太多不好讀取，1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式5.“@”文本占位符使用單個@，作用是引用固定文本。使用多個@，則可以重復文本，1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式6．“!”原樣顯示后面的符號在自定義格式中，“”、#、？等都是有特殊意義的字符，如果想在單元格中顯示這些字符，需要在前符號前加“!”，，1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式7．“*”重復后面的字符重復顯示*后面的字符，直到充滿整個列寬。1.1 數(shù)據(jù)錄入與基本設置1.1.1使用特殊符號自定義單元格格式8．顏色顯示符代碼：[紅色];[藍色];[黑色];[綠色]，顯示結果為正數(shù)為紅色，負數(shù)顯示藍色，零顯示黑色1.1 數(shù)據(jù)錄入與基本設置1.1.2設置數(shù)字以小數(shù)點對齊，使數(shù)值大小一目了然第1步：設置數(shù)據(jù)右對齊第2步：設置單元格格式，自定義單元格格式為：#.0?????，1.1 數(shù)據(jù)錄入與基本設置1.1.3單元格數(shù)據(jù)換行第1種方法：自動換行。第2種方法：Alt+Enter。1.1 數(shù)據(jù)錄入與基本設置1.1.4設置不能隔行隔列填寫數(shù)據(jù)1．打開“數(shù)據(jù)驗證”2．設置驗證條件3、設置出錯警告1.1 數(shù)據(jù)錄入與基本設置1.1.5設置傾斜列標簽單擊“開始”菜單的“對齊方式”功能區(qū)中的“方向”按鈕，可以沿對角或垂直方向旋轉文字，這是標記窄列的好方式，1.1 數(shù)據(jù)錄入與基本設置1.1.6單元格數(shù)據(jù)太多，加滾動條顯示第1步：插入文本控件第3步：關閉設計模式，輸入文字第2步：設置文本框屬性1.1 數(shù)據(jù)錄入與基本設置1.1.7凍結拆分窗格，輕松查看行列數(shù)據(jù)1．凍結行2．凍結列3．凍結行和列1.1 數(shù)據(jù)錄入與基本設置1.1.8輕松繪制單斜線、雙斜線表頭1．單斜線表頭。2．雙斜線表頭。1.1 數(shù)據(jù)錄入與基本設置1.1.9如何讓數(shù)字以“萬”為計數(shù)單位來顯示第1種方法：設置單元格格式第2種方法：選擇性粘貼除以10000。1.1 數(shù)據(jù)錄入與基本設置1.1.10設置僅能修改部分單元格數(shù)據(jù)第1步：解除“鎖定”。第2步：設置保護1.1 數(shù)據(jù)錄入與基本設置1.1.11隱藏工作表第1步：打開VBA對話框。第2步：設置要隱藏的工作表屬性。第1部分 數(shù)據(jù)采集整理----1.2合并單元格1.2 合并單元格1.2.1批量合并單元格第1步：“數(shù)據(jù)”→“分類匯總”第2步：“定位”→“定位條件”第3步：“開始”→“合并后居中”1.2 合并單元格1.2.2批量拆分合并單元格第1步：取消“合并后居中”第2步：“定位”→“空值”第3步：輸入“=A2”,按Ctrl+Enter組合鍵執(zhí)行計算1.2 合并單元格1.2.3合并單元格填充序號選中整個合并單元格區(qū)域，輸入公式“=MAX($A$1:A1)+1”，按“Ctrl+Enter”組合鍵執(zhí)行計算。1.2 合并單元格1.2.4合并單元格計算1.合并單元格求和3.合并單元格平均值2.合并單元格計數(shù)1.2 合并單元格1.2.5合并單元格篩選1. 選擇合并單元格，復制到另一列，備用。3.“定位”→“定位條件”→“空值”2.“開始”→“合并單元格”→“取消合并單元格”4.輸入公式=A2，按“Ctrl+Enter”鍵結束5.備用的合并單元格區(qū)域，單擊“格式刷”1.2 合并單元格1.2.6合并單元格數(shù)據(jù)查詢公式：=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,0),,3),2,)第1部分 數(shù)據(jù)采集整理----1.3數(shù)據(jù)規(guī)范1.3 數(shù)據(jù)規(guī)范1.3.1利用數(shù)據(jù)驗證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入1．規(guī)范性別輸入1.3 數(shù)據(jù)規(guī)范1.3.1利用數(shù)據(jù)驗證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入2．限定輸入內容1.3 數(shù)據(jù)規(guī)范1.3.1利用數(shù)據(jù)驗證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入3．限定數(shù)字范圍1.3 數(shù)據(jù)規(guī)范1.3.1利用數(shù)據(jù)驗證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入4．限定文本長度1.3 數(shù)據(jù)規(guī)范1.3.1利用數(shù)據(jù)驗證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入5．限制輸入重復信息1.3 數(shù)據(jù)規(guī)范1.3.1利用數(shù)據(jù)驗證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入6．限定身份證號碼1.3 數(shù)據(jù)規(guī)范1.3.2設置只能輸入規(guī)范的日期第1步：設置日期格式。第2步：數(shù)據(jù)驗證規(guī)范日期區(qū)間。1.3 數(shù)據(jù)規(guī)范1.3.3巧用數(shù)據(jù)有效性規(guī)范報到時間1.公式“=NOW()”2.“數(shù)據(jù)”→“數(shù)據(jù)驗證”→“序列”，“來源”設為“=$E$2”，3.“報到時間”一列單元格格式4.隱藏E列。1.3 數(shù)據(jù)規(guī)范1.3.4數(shù)量單位，怎么計算平均值{=ROUND(AVERAGE(--SUBSTITUTE(B2:B10,"分",)),2)}，1.3 數(shù)據(jù)規(guī)范1.3.5一鍵添加“能計算”的數(shù)量單位“設置單元格格式”對話框，在“分類”中選擇“自定義”，直接在“類型”文本框的“G/通用格式”后輸入數(shù)量單位，1.3 數(shù)據(jù)規(guī)范1.3.6多級聯(lián)動菜單，規(guī)范數(shù)據(jù)輸入第1步：分級數(shù)據(jù)整理。第2步：自定義名稱。第3步：建立各級菜單。1.3 數(shù)據(jù)規(guī)范1.3.7”數(shù)值”不能計算，怎么辦第1種方法：選擇性粘貼。第2種方法：數(shù)據(jù)分列。第3種方法：VALUE函數(shù)。1.3 數(shù)據(jù)規(guī)范1.3.8規(guī)范全角半角數(shù)據(jù)公式“=ASC(D3)”公式“=WIDECHAR(D3)”1.3 數(shù)據(jù)規(guī)范1.3.9數(shù)字與文本分離的三種方法第1種方法：函數(shù)法。第2種方法：分列。第3種方法：快速填充。1.3 數(shù)據(jù)規(guī)范1.3.10用LOOKUP和FIND函數(shù)規(guī)范標準名稱第1步：建立關鍵字與標準名稱對應表。第2步：函數(shù)實現(xiàn)公式“=LOOKUP(1,0/FIND($D$2:$D$7,A2),$E$2:$E$7)”1.3 數(shù)據(jù)規(guī)范1.3.11給同一單元格中姓名和電話號碼中間加分隔符號公式“=REPLACEB(A2,SEARCHB("?",A2),0,"：")第1部分 數(shù)據(jù)采集整理----1.4行列設置1.4 行列設置1.4.1快速刪除空白行1．純空白行，無其它空白單元格2．既有空白行，又有空白單元格1.4 行列設置1.4.2不管插入行還是刪除行，序號都可自動填寫方法1：ROW函數(shù)。方法2：ROW函數(shù)+表格。方法3：SUBTOTAL函數(shù)。1.4 行列設置1.4.3數(shù)據(jù)的轉置與跳過單元格復制１．選擇性粘貼，數(shù)據(jù)轉置的使用。2．跳過單元格復制。1.4 行列設置1.4.4最快捷的一列變多列方式1．將數(shù)據(jù)復制到C列。2．在D1單元格中輸入公式“=C6”，按Enter鍵執(zhí)行計算。因為從C6開始名字另起一列顯示。3．公式向下填充，再向右填充，在C1：G5區(qū)域會囊括原來的一整列分布的數(shù)據(jù)。4．選中D1：G5區(qū)域，復制，再進行選擇性粘貼，用數(shù)值覆蓋原有的公式。5．刪除C1：G5區(qū)域以外的數(shù)據(jù)。1.4 行列設置1.4.5聚光燈效果（閱讀模式）改變當前行和列的顏色1．條件格式設計顏色。2．顏色隨單元格改變而移動1.4 行列設置1.4.6同一部門員工合并到同一單元格1.4 行列設置1.4.7同一部門員工，由同一單元格變分行顯示1．打開查詢編輯器。2．姓名的分列顯示3．逆透視列。4．關閉并上載第1部分 數(shù)據(jù)采集整理----1.5數(shù)據(jù)維度轉換1.5 數(shù)據(jù)維度轉換1.5.1為工作表建目錄1．“公式”菜單中“定義名稱”公式“=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())”公式“=IFERROR(HYPERLINK(目錄&"!A1",MID(目錄,FIND("]",目錄)+1,99)),"")”2．公式1.5 數(shù)據(jù)維度轉換1.5.2旁門左道創(chuàng)建目錄第1步：選定所有工作表。第2步：輸入公式。第3步：自動生成“兼容性報表”。第4步：復制目錄到“目錄”工作表。1.5 數(shù)據(jù)維度轉換1.5.3鏈接到另一張表的四種姿勢第1種方法：文字。1.5 數(shù)據(jù)維度轉換1.5.3鏈接到另一張表的四種姿勢第2種方法：形狀。1.5 數(shù)據(jù)維度轉換1.5.3鏈接到另一張表的四種姿勢第3種方法：圖標。1.5 數(shù)據(jù)維度轉換1.5.3鏈接到另一張表的四種姿勢第4種方法：ActiveX控件。1.5 數(shù)據(jù)維度轉換1.5.4單擊訂單名稱即可跳到訂單詳情工作表第1部分 數(shù)據(jù)采集整理----1.6數(shù)據(jù)格式轉換1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式1．INT取整。1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式2．TRUNC取整。1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式3．ROUND小數(shù)取整。1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式4．ROUND整數(shù)取整。1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式5．ROUNDUP向上舍入函數(shù)。1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式6．ROUNDDOWN向下舍入函數(shù)。1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式7．MROUND函數(shù)。1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式8．CEILING函數(shù)1.6 數(shù)據(jù)格式轉換1.6.1數(shù)值取整的9種方式9．FLOOR函數(shù)1.6 數(shù)據(jù)格式轉換1.6.2數(shù)值的特殊舍入方式：舍入到偶數(shù)或奇數(shù)1．舍入到偶數(shù)MROUND四舍五入到偶數(shù)CEILING函數(shù)FLOOR函數(shù)EVEN函數(shù)1.6 數(shù)據(jù)格式轉換1.6.2數(shù)值的特殊舍入方式：舍入到偶數(shù)或奇數(shù)2．舍入到奇數(shù)1.6 數(shù)據(jù)格式轉換1.6.3NUMBERSTRING和TEXT函數(shù)：阿拉伯數(shù)字和中文數(shù)字轉換1．阿拉伯數(shù)字轉中文數(shù)字2．中文數(shù)字轉阿拉伯數(shù)字1.6 數(shù)據(jù)格式轉換1.6.4一串串長短不一的文本算式，怎么算結果第1步：選項設置。第2步：數(shù)據(jù)分列。第3步：選項設置。1.6 數(shù)據(jù)格式轉換1.6.5人民幣阿拉伯數(shù)字轉為中文大寫格式公式“=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";負")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零元整"),"零角",IF(A2^2<1,,"零")),"零分","整")”第1部分 數(shù)據(jù)采集整理----1.7數(shù)據(jù)篩選1.7 數(shù)據(jù)篩選1.7.1數(shù)值篩選基礎1．篩選指定某值。1.7 數(shù)據(jù)篩選1.7.1數(shù)值篩選基礎2．排除指定值篩選。1.7 數(shù)據(jù)篩選1.7.1數(shù)值篩選基礎3．高于指定值篩選。1.7 數(shù)據(jù)篩選1.7.1數(shù)值篩選基礎4．篩選指定范圍數(shù)值。1.7 數(shù)據(jù)篩選1.7.1數(shù)值篩選基礎5．篩選前幾位數(shù)值。1.7 數(shù)據(jù)篩選1.7.1數(shù)值篩選基礎6．高于平均值篩選。1.7 數(shù)據(jù)篩選1.7.2高級篩選高級篩選的關鍵點：必須按照篩選要求自己寫一個條件區(qū)域。高級篩選的原數(shù)據(jù)區(qū)域第一行（列標簽）一定要包含條件區(qū)域的第一行（列標簽）。高級篩選條件區(qū)域，一定要注意各條件之間的關系。1.7 數(shù)據(jù)篩選1.7.3篩選符合條件人員的兩種方法1.IF+COUNTIF、2.高級篩選1.7 數(shù)據(jù)篩選1.7.4不用公式的跨表查詢：查詢指定顧客的購買記錄第1步：寫條件。第2步：高級篩選。1.7 數(shù)據(jù)篩選1.7.5SUMIF+CELL使隱藏列不參與匯總第1步：建立輔助行第2步：函數(shù)實現(xiàn)。1.7 數(shù)據(jù)篩選1.7.6序號經過篩選后仍然不亂第1部分 數(shù)據(jù)采集整理----1.8排序和排名1.8 排序和排名1.8.1多關鍵字排序，多少個排序條件都可以一起來“數(shù)據(jù)”→“排序”“添加條件”1.8 排序和排名1.8.2數(shù)據(jù)也可按行排序第1步：選定除了行標題（首列）以外的所有數(shù)據(jù)，單擊“數(shù)據(jù)”→“排序”第2步：選擇“主要關鍵字”為消費記錄所在“行2”，“次序”為“降序”；單擊“添加條件”按鈕，添加“次要關鍵字”，次要關鍵字選擇消費方式所在的“行4”，根據(jù)要求選擇升序或者降序，1.8 排序和排名1.8.3按自定義序列排序，誰在前誰在后由你定1.8 排序和排名1.8.4剔除0值排名次，升序降序由你來定1．剔除0值降序排。2．剔除0值升序排名。1.8 排序和排名1.8.5只給有銷量的產品添加序號公式“=IF(C2=0,"",(C2<>0)*(COUNT($C$2:C2)-COUNTIF($C$2:C2,"0")))”1.8 排序和排名1.8.6RANK.EQ引用合并區(qū)域，實現(xiàn)多列數(shù)據(jù)排名公式“=RANK.EQ(B3,($B$3:$B$11,$E$3:$E$11,$H$3:$H$11,$K$3:$K$11))”1.8 排序和排名1.8.7SUMPRODUCT實現(xiàn)中式排名公式“=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))”1.8 排序和排名1.8.8SUMPRODUCT分組排名公式公式“=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14))”1.8 排序和排名1.8.9利用公式自動生成字母序列公式“=CHAR(COLUMN(A1)+64)”公式“=CHAR(ROW(A1)+64)”公式“=CHAR(COLUMN()+96)”公式“=CHAR(ROW(B1)+96)”1.8 排序和排名1.8.10數(shù)據(jù)透視表實現(xiàn)排名（1）建立數(shù)據(jù)透視表。（2）添加字段到“行”（3）更改“值顯示方式”第1部分 數(shù)據(jù)采集整理----1.9數(shù)據(jù)去重復1.9數(shù)據(jù)去重復1.9.1刪除重復項，也可以隨你所欲1．只有一列數(shù)據(jù)。2．數(shù)據(jù)有多列。1.9數(shù)據(jù)去重復1.9.2每人報名項目多少不一，計算有多少人報名公式“=SUMPRODUCT(1/COUNTIF($A$2:$A$16,$A$2:$A$16))”1.9數(shù)據(jù)去重復1.9.3COUNT+MATCH，統(tǒng)計兩列有多少重復值公式“=COUNT(MATCH(A2:A11,B2:B11,0))”，按Ctrl+Shift+Enter組合鍵1.9數(shù)據(jù)去重復1.9.4利用EXACT函數(shù)設置條件格式，標記兩組數(shù)據(jù)的不同在“新增格式規(guī)則”對話框中，“選擇規(guī)則類型”為“使用公式確定要設置格式的單元格”，并在“為符合此公式的值設計格式”中輸入公式“=OR(EXACT(A2,$B$2:$B$21))=FALSE”，第1部分 數(shù)據(jù)采集整理----1.10多工作簿、工作表合并、匯總與拆分1.10 多工作簿、工作表合并、匯總與拆分1.10.1數(shù)據(jù)查詢功能實現(xiàn)多工作表合并第1步：打開查詢編輯器。第2步：追加查詢。第3步：關閉并上載1.10 多工作簿、工作表合并、匯總與拆分1.10.2PowerQuery編輯器實現(xiàn)多工作簿合并與刷新第1步：將需要合并的工作簿存入一個文件夾中。第2步：新建“合并”工作簿。新建的工作簿不要和需要合并的工作簿放在同一文件夾。第3步：新建查詢。第4步：刪除多余數(shù)據(jù)列。第5步：添加列選項。第6步：數(shù)據(jù)加工。第7步：數(shù)據(jù)加載到表格。第8步：數(shù)據(jù)刷新。1.10 多工作簿、工作表合并、匯總與拆分1.10.3多工作表數(shù)據(jù)匯總第1種情況：所有表“姓名”排序一致。公式“=SUM('*'!C2)”1.10 多工作簿、工作表合并、匯總與拆分1.10.3