Excel數據處理與可視化

韓春玲

高職院校教師，市級office應用大賽優秀指導老師，
具有十餘年辦公軟件office一線教學經驗，
曾無數次應邀到多家企事業單位進行EXCEL應用培訓，輔助許多公司數據處理工作。
主編的《辦公軟件高級應用》，被多所高校用作教學用書。
獨自運營的微信公眾平台"韓老師講office”、今日頭條"韓老師講office”，
深受近十萬Excel學習與使用者喜愛

目錄
□□部分數據採集與整理
1．1數據錄入與基本設置
1．1．1使用特殊符號自定義單元格格式
1．1．2設置數字以小數點對齊
1．1．3單元格數據換行
1． 1．4設置不能隔行或隔列填寫數據
1．1．5設置傾斜的列標籤
1．1．6給單元格數據加滾動條顯示
1．1．7凍結窗格，輕鬆查看行、列數據
1 ．1．8輕鬆繪製單斜線、雙斜線表頭
1．1．9如何讓數字以“萬”為計數單位來顯示
1．1．10設置僅能修改部分單元格數據
1．1．11隱藏工作表
1．2合併單元格
1．2．1批量合併單元格
1．2．2批量拆分合併單元格
1．2．3給合併單元格填充序號
1．2．4合併單元格計算
1 ．2．5合併單元格篩選
1．2．6合併單元格數據查詢
1．3數據規範
1．3．1利用數據驗證（數據有效性）規範數據輸入
1．3．2設置只能輸入規範的日期
1．3．3巧用數據驗證規範時間格式
1．3．4數據輸入不規範 部分數據帶數量單位，此時怎麼計算平均值
1．3．5一鍵添加“能計算”的數量單位
1．3．6使用多級聯動菜單規範數據輸入
1．3．7處理不能計算的“數值”
1．3．8規範全角、半角數據
1．3．9數字與文本分離的方法
1．3．10 LOOKUP+FIND函數組合規範標準名稱
1．3． 11給同一單元格的姓名和電話號碼中間加分隔符號
1．4行列設置
1．4．1快速刪除空白行
1．4．2插入行或刪除行後，都可自動填寫序號
1．4．3數據轉置與跳過單元格複製
1．4．4 □快捷的一列轉多列方式
1．4．5聚光燈效果（閱讀模式）改變當前行和列的顏色
1．4．6將同部門員工姓名合併到同一單元格
1．4．7將同一單元格的同部門員工姓名分行顯示
1．5數據維度轉換
1．5．1使用函數建立目錄
1．5．2不使用函數建立目錄
1．5． 3鏈接到另一張表的4種方法
1．5．4單擊訂單名稱，即可跳到訂單詳情工作表
1．6數據格式轉換
1．6．1數值取整的9種方法
1．6 ．2數值的特殊舍入方式
1．6．3 NUMBERSTRING函數和TEXT函數
1．6．4怎麼計 算長短不一的文本算式結果
1．6．5阿拉伯數字（小寫）轉為中文數字（大寫）來表示人民幣的金額
1．7數據篩選
1．7．1數據篩選基礎
1．7．2高級篩選
1．7．3篩選符合條件的兩種方法
1．7．4不用公式的跨表查詢
1．7．5 CELL+SUMIF函數組合使隱藏列不參與匯總
1．7．6序號經過篩選後仍然不亂
1．8排序和排名
1．8．1多個排序條件的排序
1．8．2數據按行排序
1．8．3數據按自定義序列排序
1．8．4剔除0值排名次，升序、降序由你定
1．8．5只給有銷量的產品添加序號
1．8．6 RANK．EQ函數引用合併區域，實現多列數據排名
1．8．7 SUMPRODUCT函數實現中式排名
1．8．8 SUMPRODUCT函數實現分組排名
1．8．9利用公式自動生成字母序列
1．8．10數據透視表實現排名
1．9數據去重複
1．9．1刪除重複項
1．9．2計算報名人數
1．9．3利用COUNT+MATCH函數組合統計兩列重複值
1．9．4利用EXACT函數設置條件格式，標記兩組不同的數據
1．10多個工作簿、工作表合併、匯總與拆分
1．10．1利用數據查詢功能實現多個工作表合併
1．10．2利用Power Query編輯器實現多個工作簿合併與刷新
1．10．3多個工作表數據匯總
1．10．4對於結構一致的多個工作表，合併計算是□好的匯總方法
1．10．5多個工作表，不用合併，直接查詢
1．11圖片處理
1．11．1批量導入文件名
1．11．2工作表中批量插入照片
1．11．3在批註中插入圖片
1．11．4圖片放在文件夾裡，Excel也能查看
1．11．5一次為上千幅圖片重命名
1．11．6利用Excel照相機自動匹配圖片

第2部分數據統計分析
2．1數據查詢和匹配
2．1．1查找得票□多的姓名
2．1．2查找開獎號碼對應的數字
2．1．3數值重複，如何提取前3名銷量
2．1．4提取訂貨量對應的訂貨型號
2．1．5去掉□後一個特殊符號及其以後的內容
2．1．6兩列商品型號排序不一，對應數量如何相減
2．1．7利用VLOOKUP函數實現多表數據合併查詢
2 ．1．8利用VLOOKUP+MATCH函數組合可以輕鬆查找數據
2．1．9滿足條件的數據自動“跑”到其他工作表中
2．1．10 INDEX+MATCH函數組合應用―查找業績前幾名的員工姓名
2．1．11 INDEX+MATCH函數組合應用 ―提取行與列交叉單元格的數值
2．1．12 INDEX+MATCH函數組合應用―提取整行和整列數據
2．1．13利用OFFSET函數在大量數據中查找指定數據
2．1．14利用OFFSET函數在動態單元格區域中查找指定數據
2．1．15跨表查詢指定顧客的購買記錄
2．1．16在大量信息中，快速查找哪些員工信息是不完整的
2．1．17利用OFFSET函數提取銷量□大的整列信息
2．1 ．18利用OFFSET函數提取銷量前3名的整列信息
2．1．19提取銷量□大的月份
2．1．20提取銷量前3名的月份
2．1．21如何給相同姓名添加相同編號
2． 1．22 INDEX+SMALL函數組合完成一對多查找
2．1．23 IFERROR函數修正VLOOKUP函數返回錯誤值
2．1．24如何查找主單號對應的子單號，且主單號與子單號同行顯示
2．1．25利用公式從地址中提取省級行政區
2．1．26利用VLOOKUP+IF函數組合、VLOOKUP+CHOOSE函數組合實現逆向查詢
2．1．27 IF、VLOOKUP、LOOKUP、CHOOSE、INDEX函數都可完成等級評定
2．1．28利用VLOOKUP函數查詢同部門多個員工信息
2．1．29利用VLOOKUP函數查詢一種產品多次的進貨量
2．1．30多條件查詢的函數
2． 2數據統計
2．2．1根據 等級計算總成績
2．2．2依據評分標準，折算男女同學的體育分數
2．2．3統計除請假外參與考核的部門人數
2．2．4根據規定好的佔比劃分成績等級
2．2 ．5利用公式填寫金額收據
2．2．6正值、負值分別求和，盈虧情況一目了然
2．2．7算算每戶有幾口人
2．2．8依據收費標準，計算不同地區、不同重量的快遞費用
2． 2．9多人分組完成多個項目，統計每個人參與了哪些項目
2．2．10同一單元格中有多個姓名，統計總人數
2．2．11員工姓名和業績在同一單元格中，統計業績□大值
2．2．12巧用ROW函數統計前N名數據
2．2．13排除重複項統計每月缺勤人數
2．2．14巧用動態區域統計累計情況
2．2．15根據銷售額分段提成標準計算累進提成
2．2．16統計代碼含有指定數字的商品銷售總量
2．2．17利用LEN+SUBSTITUTE函數組合計算員工參與項目數
2．2．18按不同字體或背景顏色統計數值
2．3日期、時間範圍統計
2．3．1製作按年、月自動變化的考勤表表頭
2．3．2設置考勤表週六、週日列自動變色，且自動統計工作日與非工作日加班時長
2．3．3製作 日期豎排的考勤表表頭
2．3．4日期與時間分離的3種方法
2．3．5利用SUM +OFFSET函數組合查詢產品指定月的銷量合計
2．3．6利用LOOKUP +DATEDIF函數組合計算賬齡
2．3．7根據出生日期製作員工生日提醒
2．3．8 2018年是平年還是閏年
2．3．9判斷會員是否可以升級到VIP
2．3．10利用公式計算早班、中班、夜班工作時長
2．3．11計算加班時長與補助工資
2．3．12 SUMIFS函數按月統計產品銷量
2．3．13計算指定年與月的銷售總額
2．3．14根據入職時間計算帶薪年假天數
2．4數據透視表
2．4．1數據透視表的8個典型應用
2．4．2巧用數據透視表批量合併單元格
2 ．4．3利用OFFSET函數定義名稱，實現數據透視表的動態更新
2．4．4利用數據透視表對數據進行分段統計
2．4．5巧用數據透視表快速拆分工作表
2．5數據專業分析
2．5．1新個稅計算的3個公式
2．5．2如何分析共享單車各站點的借車與還車高峰時段
2．5．3中了五千萬大獎，Excel來幫忙規化
2．5．4根據身份證號碼統計易出現的錯誤
2．5．5利用INDEX+RANDBETWEEN函數組合和RAND函數實現隨機分組
2．5．6 TREND函數預測交易額

第3部分數據可視化
3． 1條件展現
3．1．1靈活使用公式，設置 件格式
3．1．2自定義奇、偶行顏色
3．1．3數據增減，顏色始終追隨匯總行
3．1．4自動填充單元格顏色，提示身份證號碼位數出錯
3．1． 5完全相同的行填充相同顏色
3．1．6突出顯示兩個工作表中完全相同的行
3．1．7設置繳費的行為綠色
3．1．8高亮顯示需要查看的商品
3．1．9用不同顏色突出顯示前3名的數據
3．1．10根據貨號設置間隔色
3．1．11在所有員工的工資表中，僅能查看本人信息
3．1．12商品到期的文字提醒不醒目，可加個圖標
3 ．1．13實現智能添加單元格邊框
3．1．14突出顯示多次考核業績不合格的員工姓名
3．1．15標記兩組中不同的數據
3．1．16計算合同到期天數，並設置“交通三色燈”提醒
3．2典型圖表及應用
3．2．1產品銷量對比圖：6個月用6行顯示
3．2．2數量對比圖，加一條線突顯效果
3．2． 3甘特圖―項目進度清晰可見
3．2．4旭日圖―體現數據層次及佔比的□好圖表
3．2．5帶數值或極值的圖表
3．2．6利用模板快速製作N個數據分析圖表
3．2．7如何製作表達數據簡潔醒目的迷你圖
3．2．8如何做 雙縱坐標軸圖表
3．2．9圖表更新的3個方法
3．2．10圖表的翻轉
3．3動態圖表
3．3．1快速製作動態圖表
3．3．2製作帶控件的動態圖表
3 ．3．3製作“雙列”數據動態圖表
3．3．4製作帶滾動條的動態圖表
3．3．5雙控件柱形圖的製作
3．3．6雙控件動態復合餅圖的製作
3．3．7製作突出顯示某系列數據的動態圖表
3．4打印輸出
3．4．1多頁打印，並設置每頁都有標題
3．4．2設置按類別分頁打印
3．4．3設置工作表的完整打印
3．4．4設置打印工作表時添加水印

第4部分函數與公式
4 ．1公式綜述
4．1．1必須知道的公式基礎知識
4．1．2公式中常出現的錯誤代碼及修正方法
4．1．3使用“追踪錯誤”對公式進行檢查
4．1．4將公式保護起來
4．1．5數組公式―基礎知識
4．1．6數組公式―應用初步
4．1．7數組公式―典型應用
4．2統計函數
4．2．1利用MODE．MULT函數統計出現□多的數字（一）
4．2．2利用MODE．MULT函數統計出現□多的數字（二）
4．2．3利用COUNTIF函數給眾多班級中相同班級的學生編號
4．2．4 5個常用的“IFS”結尾的多條件統計函數
4．2． 5利用SUM+COUNTIF函數組合統計不重複值的個數
4．2．6利用FREQUENCY函數分段計數
4．2．7利用COUNTIFS函數統計滿足多個條件的單元格數量
4．2．8利用TRIMMEAN函數計算去掉□高分和□低分後的平均分
4．3文本函數
4．3．1 MID+FIND函數組合提取括號內數據
4．3．2 TEXT―超級好用的文本函數
4．3．3 ＆―文本連接符的使用
4．3．4 CONCATENATE文本連接函數
4．3．5 CONCAT文本連接函數
4．3．6 TEXTJOIN文本連接函數
4．3．7 CLEAN函數清除非打印字符
4．3．8 SUBSTITUTE文本替換函數的使用
4．3．9 REPLACE函數―隱藏身份證號碼的部分數字
4．4時間與日期函數
4．4．1根據身份證號碼計算退休日期
4．4．2根據續費月數計算到期日
4．4．3利用DATEDIF函數計算精確到年、月、天的賬齡
4．4．4利用WORKDAY函數計算幾個工作日之後的日期
4．4．5利用EOMONTH函數取某月的□後一天
4．4．6利用NETWORKDAYS．INTL函數計算工作日
4．5數學函數
4．5．1 SUMIF函數應用―單條件、多條件、模糊條件求和
4．5．2 SUMIF函數應用―非空值條件、排除錯誤值、日期區間求和
4．5．3 SUMIF函數應用―隔列求和、查找與引用
4．5．4 SUMIFS函數多字段、多條件求和
4．5．5 SUMPRODUCT函數用法解析
4．5．6 SUMPRODUCT函數的注意事項
4．5．7利用SUBTOTAL函數實現忽略隱藏行統計
4．5．8 AGGREGATE函數―忽略錯誤值計算的萬能函數
4．5．9 ROUND函數對數據四捨五入
4．5．10 QUOTIENT函數與TRUNC函數―截去小數，保留整數
4．6查找與引用函數
4．6．1 VLOOKUP函數應用之基礎―基本查找
4．6．2 VLOOKUP函數應用之小成―多行多列查找
4．6．3 VLOOKUP函數應用之提升―區間查找、等級評定、模糊查找
4．6．4 VLOOKUP函數應用之進階―多條件查找、逆向查找
4．6．5 VLOOKUP函數應用之高級篇―一對多查找
4．6．6 HLOOKUP函數實現行查找
4．6．7 LOOKUP函數查詢的10種方法
4．6．8 LOOKUP函數典型應用―根據抽樣標準計算抽樣數量
4．6．9 LOOKUP函數典型應用―合併單元格的拆分與查找
4．6．10 MATCH函數―查找數據所在的位置
4．6．11 INDEX函數―查找某行、某列的值
4．6．12查找“行列交叉單元格”數據的4個函數
4．6 ．13 OFFSET偏移函 數應用
4．6．14 INDIRECT函數匯總各倉庫的合計到銷售總表
4．6．15 CHOOSE函數的用法集錦
4．6．16空格―交叉運算符
4．7信息與邏輯函數
4．7．1 IF函數―□常用的邏輯函數
4．7．2 IF、OR、AND等邏輯函數的使用―以閏年為例
附錄A Excel與Word“雙劍合璧”，提取無規律分佈的字母、漢字、數
附錄B組合鍵大全