Excel數(shù)據(jù)處理與可視化

目 錄
第1部分 數(shù)據(jù)采集與整理
1．1　數(shù)據(jù)錄入與基本設(shè)置
1．1．1　使用特殊符號(hào)自定義單元格格式
1．1．2　設(shè)置數(shù)字以小數(shù)點(diǎn)對(duì)齊
1．1．3　單元格數(shù)據(jù)換行
1．1．4　設(shè)置不能隔行或隔列填寫數(shù)據(jù)
1．1．5　設(shè)置傾斜的列標(biāo)簽
1．1．6　給單元格數(shù)據(jù)加滾動(dòng)條顯示
1．1．7　凍結(jié)窗格，輕松查看行、列數(shù)據(jù)
1．1．8　輕松繪制單斜線、雙斜線表頭
1．1．9　如何讓數(shù)字以“萬(wàn)”為計(jì)數(shù)單位來(lái)顯示
1．1．10　設(shè)置僅能修改部分單元格數(shù)據(jù)
1．1．11　隱藏工作表
1．2　合并單元格
1．2．1　批量合并單元格
1．2．2　批量拆分合并單元格
1．2．3　給合并單元格填充序號(hào)
1．2．4　合并單元格計(jì)算
1．2．5　合并單元格篩選
1．2．6　合并單元格數(shù)據(jù)查詢
1．3　數(shù)據(jù)規(guī)范
1．3．1　利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入
1．3．2　設(shè)置只能輸入規(guī)范的日期
1．3．3　巧用數(shù)據(jù)驗(yàn)證規(guī)范時(shí)間格式
1．3．4　數(shù)據(jù)輸入不規(guī)范，部分?jǐn)?shù)據(jù)帶數(shù)量單位，此時(shí)怎么計(jì)算平均值
1．3．5　一鍵添加“能計(jì)算 ”的數(shù)量單位
1．3．6　使用多級(jí)聯(lián)動(dòng)菜單規(guī)范數(shù)據(jù)輸入
1．3．7　處理不能計(jì)算的“數(shù)值”
1．3．8　規(guī)范全角、半角數(shù)據(jù)
1．3．9　數(shù)字與文本分離的方法
1．3．10　LOOKUP+FIND函數(shù)組合規(guī)范標(biāo)準(zhǔn)名稱
1．3．11　給同一單元格的姓名和電話號(hào)碼中間加分隔符號(hào)
1．4　行列設(shè)置
1．4．1　快速刪除空白行
1．4．2　插入行或刪除行后，都可自動(dòng)填寫序號(hào)
1．4．3　數(shù)據(jù)轉(zhuǎn)置與跳過(guò)單元格復(fù)制
1．4．4　最快捷的一列轉(zhuǎn)多列方式
1．4．5　聚光燈效果（閱讀模式）改變當(dāng)前行和列的顏色
1．4．6　將同部門員工姓名合并到同一單元格
1．4．7　將同一單元格的同部門員工姓名分行顯示
1．5　數(shù)據(jù)維度轉(zhuǎn)換
1．5．1　使用函數(shù)建立目錄
1．5．2　不使用函數(shù)建立目錄
1．5．3　鏈接到另一張表的4種方法
1．5．4　單擊訂單名稱，即可跳到訂單詳情工作表
1．6　數(shù)據(jù)格式轉(zhuǎn)換
1．6．1　數(shù)值取整的9種方法
1．6．2　數(shù)值的特殊舍入方式
1．6．3　NUMBERSTRING函數(shù)和TEXT函數(shù)
1．6．4　怎么計(jì)算長(zhǎng)短不一的文本算式結(jié)果
1．6．5　阿拉伯?dāng)?shù)字（小寫）轉(zhuǎn)為中文數(shù)字（大寫）來(lái)表示人民幣的金額
1．7　數(shù)據(jù)篩選
1．7．1　數(shù)據(jù)篩選基礎(chǔ)
1．7．2　高級(jí)篩選
1．7．3　篩選符合條件的兩種方法
1．7．4　不用公式的跨表查詢
1．7．5　CELL+SUMIF函數(shù)組合使隱藏列不參與匯總
1．7．6　序號(hào)經(jīng)過(guò)篩選后仍然不亂
1．8　排序和排名
1．8．1　多個(gè)排序條件的排序
1．8．2　數(shù)據(jù)按行排序
1．8．3　數(shù)據(jù)按自定義序列排序
1．8．4　剔除0值排名次，升序、降序由你定
1．8．5　只給有銷量的產(chǎn)品添加序號(hào)
1．8．6　RANK．EQ函數(shù)引用合并區(qū)域，實(shí)現(xiàn)多列數(shù)據(jù)排名
1．8．7　SUMPRODUCT函數(shù)實(shí)現(xiàn)中式排名
1．8．8　SUMPRODUCT函數(shù)實(shí)現(xiàn)分組排名
1．8．9　利用公式自動(dòng)生成字母序列
1．8．10　數(shù)據(jù)透視表實(shí)現(xiàn)排名
1．9　數(shù)據(jù)去重復(fù)
1．9．1　刪除重復(fù)項(xiàng)
1．9．2　計(jì)算報(bào)名人數(shù)
1．9．3　利用COUNT+MATCH函數(shù)組合統(tǒng)計(jì)兩列重復(fù)值
1．9．4　利用EXACT函數(shù)設(shè)置條件格式，標(biāo)記兩組不同的數(shù)據(jù)
1．10　多個(gè)工作簿、工作表合并、匯總與拆分
1．10．1　利用數(shù)據(jù)查詢功能實(shí)現(xiàn)多個(gè)工作表合并
1．10．2　利用Power Query編輯器實(shí)現(xiàn)多個(gè)工作簿合并與刷新
1．10．3　多個(gè)工作表數(shù)據(jù)匯總
1．10．4　對(duì)于結(jié)構(gòu)一致的多個(gè)工作表，合并計(jì)算是最好的匯總方法
1．10．5　多個(gè)工作表，不用合并，直接查詢
1．11　圖片處理
1．11．1　批量導(dǎo)入文件名
1．11．2　工作表中批量插入照片
1．11．3　在批注中插入圖片
1．11．4　圖片放在文件夾里，Excel也能查看
1．11．5　一次為上千幅圖片重命名
1．11．6　利用Excel照相機(jī)自動(dòng)匹配圖片
第2部分　數(shù)據(jù)統(tǒng)計(jì)分析
2．1　數(shù)據(jù)查詢和匹配
2．1．1　查找得票最多的姓名
2．1．2　查找開(kāi)獎(jiǎng)號(hào)碼對(duì)應(yīng)的數(shù)字
2．1．3　數(shù)值重復(fù)，如何提取前3名銷量
2．1．4　提取訂貨量對(duì)應(yīng)的訂貨型號(hào)
2．1．5　去掉最后一個(gè)特殊符號(hào)及其以后的內(nèi)容
2．1．6　兩列商品型號(hào)排序不一，對(duì)應(yīng)數(shù)量如何相減
2．1．7　利用VLOOKUP函數(shù)實(shí)現(xiàn)多表數(shù)據(jù)合并查詢
2．1．8　利用VLOOKUP+MATCH函數(shù)組合可以輕松查找數(shù)據(jù)
2．1．9　滿足條件的數(shù)據(jù)自動(dòng)“跑”到其他工作表中
2．1．10　INDEX+MATCH函數(shù)組合應(yīng)用―查找業(yè)績(jī)前幾名的員工姓名
2．1．11　INDEX+MATCH函數(shù)組合應(yīng)用―提取行與列交叉單元格的數(shù)值
2．1．12　INDEX+MATCH函數(shù)組合應(yīng)用―提取整行和整列數(shù)據(jù)
2．1．13　利用OFFSET函數(shù)在大量數(shù)據(jù)中查找指定數(shù)據(jù)
2．1．14　利用OFFSET函數(shù)在動(dòng)態(tài)單元格區(qū)域中查找指定數(shù)據(jù)
2．1．15　跨表查詢指定顧客的購(gòu)買記錄
2．1．16　在大量信息中，快速查找哪些員工信息是不完整的
2．1．17　利用OFFSET函數(shù)提取銷量最大的整列信息
2．1．18　利用OFFSET函數(shù)提取銷量前3名的整列信息
2．1．19　提取銷量最大的月份
2．1．20　提取銷量前3名的月份
2．1．21　如何給相同姓名添加相同編號(hào)
2．1．22　INDEX+SMALL函數(shù)組合完成一對(duì)多查找
2．1．23　IFERROR函數(shù)修正VLOOKUP函數(shù)返回錯(cuò)誤值
2．1．24　如何查找主單號(hào)對(duì)應(yīng)的子單號(hào)，且主單號(hào)與子單號(hào)同行顯示
2．1．25　利用公式從地址中提取省級(jí)行政區(qū)
2．1．26　利用VLOOKUP+IF函數(shù)組合、VLOOKUP+CHOOSE函數(shù)組合實(shí)現(xiàn)逆向查詢
2．1．27　IF、VLOOKUP、LOOKUP、CHOOSE、INDEX函數(shù)都可完成等級(jí)評(píng)定
2．1．28　利用VLOOKUP函數(shù)查詢同部門多個(gè)員工信息
2．1．29　利用VLOOKUP函數(shù)查詢一種產(chǎn)品多次的進(jìn)貨量
2．1．30　多條件查詢的函數(shù)
2．2　數(shù)據(jù)統(tǒng)計(jì)
2．2．1　根據(jù)等級(jí)計(jì)算總成績(jī)
2．2．2　依據(jù)評(píng)分標(biāo)準(zhǔn)，折算男女同學(xué)的體育分?jǐn)?shù)
2．2．3　統(tǒng)計(jì)除請(qǐng)假外參與考核的部門人數(shù)
2．2．4　根據(jù)規(guī)定好的占比劃分成績(jī)等級(jí)
2．2．5　利用公式填寫金額收據(jù)
2．2．6　正值、負(fù)值分別求和，盈虧情況一目了然
2．2．7　算算每戶有幾口人
2．2．8　依據(jù)收費(fèi)標(biāo)準(zhǔn)，計(jì)算不同地區(qū)、不同重量的快遞費(fèi)用
2．2．9　多人分組完成多個(gè)項(xiàng)目，統(tǒng)計(jì)每個(gè)人參與了哪些項(xiàng)目
2．2．10　同一單元格中有多個(gè)姓名，統(tǒng)計(jì)總?cè)藬?shù)
2．2．11　員工姓名和業(yè)績(jī)?cè)谕粏卧裰?，統(tǒng)計(jì)業(yè)績(jī)最大值
2．2．12　巧用ROW函數(shù)統(tǒng)計(jì)前N名數(shù)據(jù)
2．2．13　排除重復(fù)項(xiàng)統(tǒng)計(jì)每月缺勤人數(shù)
2．2．14　巧用動(dòng)態(tài)區(qū)域統(tǒng)計(jì)累計(jì)情況
2．2．15　根據(jù)銷售額分段提成標(biāo)準(zhǔn)計(jì)算累進(jìn)提成
2．2．16　統(tǒng)計(jì)代碼含有指定數(shù)字的商品銷售總量
2．2．17　利用LEN+SUBSTITUTE函數(shù)組合計(jì)算員工參與項(xiàng)目數(shù)
2．2．18　按不同字體或背景顏色統(tǒng)計(jì)數(shù)值
2．3　日期、時(shí)間范圍統(tǒng)計(jì)
2．3．1　制作按年、月自動(dòng)變化的考勤表表頭
2．3．2　設(shè)置考勤表周六、周日列自動(dòng)變色，且自動(dòng)統(tǒng)計(jì)工作日與非工作日加班時(shí)長(zhǎng)
2．3．3　制作日期豎排的考勤表表頭
2．3．4　日期與時(shí)間分離的3種方法
2．3．5　利用SUM +OFFSET函數(shù)組合查詢產(chǎn)品指定月的銷量合計(jì)
2．3．6　利用LOOKUP +DATEDIF函數(shù)組合計(jì)算賬齡
2．3．7　根據(jù)出生日期制作員工生日提醒
2．3．8　2018年是平年還是閏年
2．3．9　判斷會(huì)員是否可以升級(jí)到VIP
2．3．10　利用公式計(jì)算早班、中班、夜班工作時(shí)長(zhǎng)
2．3．11　計(jì)算加班時(shí)長(zhǎng)與補(bǔ)助工資
2．3．12　SUMIFS函數(shù)按月統(tǒng)計(jì)產(chǎn)品銷量
2．3．13　計(jì)算指定年與月的銷售總額
2．3．14　根據(jù)入職時(shí)間計(jì)算帶薪年假天數(shù)
2．4　數(shù)據(jù)透視表
2．4．1　數(shù)據(jù)透視表的8個(gè)典型應(yīng)用
2．4．2　巧用數(shù)據(jù)透視表批量合并單元格
2．4．3　利用OFFSET函數(shù)定義名稱，實(shí)現(xiàn)數(shù)據(jù)透視表的動(dòng)態(tài)更新
2．4．4　利用數(shù)據(jù)透視表對(duì)數(shù)據(jù)進(jìn)行分段統(tǒng)計(jì)
2．4．5　巧用數(shù)據(jù)透視表快速拆分工作表
2．5　數(shù)據(jù)專業(yè)分析
2．5．1　新個(gè)稅計(jì)算的3個(gè)公式
2．5．2　如何分析共享單車各站點(diǎn)的借車與還車高峰時(shí)段
2．5．3　中了五千萬(wàn)大獎(jiǎng)，Excel來(lái)幫忙規(guī)化
2．5．4　根據(jù)身份證號(hào)碼統(tǒng)計(jì)易出現(xiàn)的錯(cuò)誤
2．5．5　利用INDEX+RANDBETWEEN函數(shù)組合和RAND函數(shù)實(shí)現(xiàn)隨機(jī)分組
2．5．6　TREND函數(shù)預(yù)測(cè)交易額
第3部分　數(shù)據(jù)可視化
3．1　條件展現(xiàn)
3．1．1　靈活使用公式，設(shè)置條件格式
3．1．2　自定義奇、偶行顏色
3．1．3　數(shù)據(jù)增減，顏色始終追隨匯總行
3．1．4　自動(dòng)填充單元格顏色，提示身份證號(hào)碼位數(shù)出錯(cuò)
3．1．5　完全相同的行填充相同顏色
3．1．6　突出顯示兩個(gè)工作表中完全相同的行
3．1．7　設(shè)置繳費(fèi)的行為綠色
3．1．8　高亮顯示需要查看的商品
3．1．9　用不同顏色突出顯示前3名的數(shù)據(jù)
3．1．10　根據(jù)貨號(hào)設(shè)置間隔色
3．1．11　在所有員工的工資表中，僅能查看本人信息
3．1．12　商品到期的文字提醒不醒目，可加個(gè)圖標(biāo)
3．1．13　實(shí)現(xiàn)智能添加單元格邊框
3．1．14　突出顯示多次考核業(yè)績(jī)不合格的員工姓名
3．1．15　標(biāo)記兩組中不同的數(shù)據(jù)
3．1．16　計(jì)算合同到期天數(shù)，并設(shè)置“交通三色燈”提醒
3．2　典型圖表及應(yīng)用
3．2．1　產(chǎn)品銷量對(duì)比圖：6個(gè)月用6行顯示
3．2．2　數(shù)量對(duì)比圖，加一條線突顯效果
3．2．3　甘特圖―項(xiàng)目進(jìn)度清晰可見(jiàn)
3．2．4　旭日?qǐng)D―體現(xiàn)數(shù)據(jù)層次及占比的最好圖表
3．2．5　帶數(shù)值或極值的圖表
3．2．6　利用模板快速制作N個(gè)數(shù)據(jù)分析圖表
3．2．7　如何制作表達(dá)數(shù)據(jù)簡(jiǎn)潔醒目的迷你圖
3．2．8　如何做出雙縱坐標(biāo)軸圖表
3．2．9　圖表更新的3個(gè)方法
3．2．10　圖表的翻轉(zhuǎn)
3．3　動(dòng)態(tài)圖表
3．3．1　快速制作動(dòng)態(tài)圖表
3．3．2　制作帶控件的動(dòng)態(tài)圖表
3．3．3　制作“雙列”數(shù)據(jù)動(dòng)態(tài)圖表
3．3．4　制作帶滾動(dòng)條的動(dòng)態(tài)圖表
3．3．5　雙控件柱形圖的制作
3．3．6　雙控件動(dòng)態(tài)復(fù)合餅圖的制作
3．3．7　制作突出顯示某系列數(shù)據(jù)的動(dòng)態(tài)圖表
3．4　打印輸出
3．4．1　多頁(yè)打印，并設(shè)置每頁(yè)都有標(biāo)題
3．4．2　設(shè)置按類別分頁(yè)打印
3．4．3　設(shè)置工作表的完整打印
3．4．4　設(shè)置打印工作表時(shí)添加水印
第4部分 函數(shù)與公式
4．1　公式綜述
4．1．1　必須知道的公式基礎(chǔ)知識(shí)
4．1．2　公式中常出現(xiàn)的錯(cuò)誤代碼及修正方法
4．1．3　使用“追蹤錯(cuò)誤”對(duì)公式進(jìn)行檢查
4．1．4　將公式保護(hù)起來(lái)
4．1．5　數(shù)組公式―基礎(chǔ)知識(shí)
4．1．6　數(shù)組公式―應(yīng)用初步
4．1．7　數(shù)組公式―典型應(yīng)用
4．2　統(tǒng)計(jì)函數(shù)
4．2．1　利用MODE．MULT函數(shù)統(tǒng)計(jì)出現(xiàn)最多的數(shù)字（一）
4．2．2　利用MODE．MULT函數(shù)統(tǒng)計(jì)出現(xiàn)最多的數(shù)字（二）
4．2．3　利用COUNTIF函數(shù)給眾多班級(jí)中相同班級(jí)的學(xué)生編號(hào)
4．2．4　5個(gè)常用的“IFS”結(jié)尾的多條件統(tǒng)計(jì)函數(shù)
4．2．5　利用SUM+COUNTIF函數(shù)組合統(tǒng)計(jì)不重復(fù)值的個(gè)數(shù)
4．2．6　利用FREQUENCY函數(shù)分段計(jì)數(shù)
4．2．7　利用COUNTIFS函數(shù)統(tǒng)計(jì)滿足多個(gè)條件的單元格數(shù)量
4．2．8　利用TRIMMEAN函數(shù)計(jì)算去掉最高分和最低分后的平均分
4．3　文本函數(shù)
4．3．1　MID+FIND函數(shù)組合提取括號(hào)內(nèi)數(shù)據(jù)
4．3．2　TEXT―超級(jí)好用的文本函數(shù)
4．3．3?。ΘD文本連接符的使用
4．3．4　CONCATENATE文本連接函數(shù)
4．3．5　CONCAT文本連接函數(shù)
4．3．6　TEXTJOIN文本連接函數(shù)
4．3．7　CLEAN函數(shù)清除非打印字符
4．3．8　SUBSTITUTE文本替換函數(shù)的使用
4．3．9　REPLACE函數(shù)―隱藏身份證號(hào)碼的部分?jǐn)?shù)字
4．4　時(shí)間與日期函數(shù)
4．4．1　根據(jù)身份證號(hào)碼計(jì)算退休日期
4．4．2　根據(jù)續(xù)費(fèi)月數(shù)計(jì)算到期日
4．4．3　利用DATEDIF函數(shù)計(jì)算精確到年、月、天的賬齡
4．4．4　利用WORKDAY函數(shù)計(jì)算幾個(gè)工作日之后的日期
4．4．5　利用EOMONTH函數(shù)取某月的最后一天
4．4．6　利用NETWORKDAYS．INTL函數(shù)計(jì)算工作日
4．5　數(shù)學(xué)函數(shù)
4．5．1　SUMIF函數(shù)應(yīng)用―單條件、多條件、模糊條件求和
4．5．2　SUMIF函數(shù)應(yīng)用―非空值條件、排除錯(cuò)誤值、日期區(qū)間求和
4．5．3　SUMIF函數(shù)應(yīng)用―隔列求和、查找與引用
4．5．4　SUMIFS函數(shù)多字段、多條件求和
4．5．5　SUMPRODUCT函數(shù)用法解析
4．5．6　SUMPRODUCT函數(shù)的注意事項(xiàng)
4．5．7　利用SUBTOTAL函數(shù)實(shí)現(xiàn)忽略隱藏行統(tǒng)計(jì)
4．5．8　AGGREGATE函數(shù)―忽略錯(cuò)誤值計(jì)算的萬(wàn)能函數(shù)
4．5．9　ROUND函數(shù)對(duì)數(shù)據(jù)四舍五入
4．5．10　QUOTIENT函數(shù)與TRUNC函數(shù)―截去小數(shù)，保留整數(shù)
4．6　查找與引用函數(shù)
4．6．1　VLOOKUP函數(shù)應(yīng)用之基礎(chǔ)―基本查找
4．6．2　VLOOKUP函數(shù)應(yīng)用之小成―多行多列查找
4．6．3　VLOOKUP函數(shù)應(yīng)用之提升―區(qū)間查找、等級(jí)評(píng)定、模糊查找
4．6．4　VLOOKUP函數(shù)應(yīng)用之進(jìn)階―多條件查找、逆向查找
4．6．5　VLOOKUP函數(shù)應(yīng)用之高級(jí)篇―一對(duì)多查找
4．6．6　HLOOKUP函數(shù)實(shí)現(xiàn)行查找
4．6．7　LOOKUP函數(shù)查詢的10種方法
4．6．8　LOOKUP函數(shù)典型應(yīng)用―根據(jù)抽樣標(biāo)準(zhǔn)計(jì)算抽樣數(shù)量
4．6．9　LOOKUP函數(shù)典型應(yīng)用―合并單元格的拆分與查找
4．6．10 MATCH函數(shù)―查找數(shù)據(jù)所在的位置
4．6．11 INDEX函數(shù)―查找某行、某列的值
4．6．12　查找“行列交叉單元格”數(shù)據(jù)的4個(gè)函數(shù)
4．6．13　OFFSET偏移函數(shù)應(yīng)用
4．6．14　INDIRECT函數(shù)匯總各倉(cāng)庫(kù)的合計(jì)到銷售總表
4．6．15　CHOOSE函數(shù)的用法集錦
4．6．16　空格―交叉運(yùn)算符
4．7　信息與邏輯函數(shù)
4．7．1　IF函數(shù)―最常用的邏輯函數(shù)
4．7．2　IF、OR、AND等邏輯函數(shù)的使用―以閏年為例
附錄A Excel與Word“雙劍合璧”，提取無(wú)規(guī)律分布的字母、漢字、數(shù)
附錄B 組合鍵大全