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

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