構建高效數據分析模板：職場必學的Excel函數與動態圖表高級應用

Part 01 函數和公式
第1章 為什麼要進行數據分析
1.1 一個實際咨詢案例
1.2 企業要做好四個數據流的管理和分析
1.3 正確認識Excel工具
1.4 怎樣正確高效使用
1．樹立正確的Excel理念
2．設計標准化、規范化的Excel表格
3．掌握Excel實用的數據管理和分析的思路、方法和技能
4．利用掌握的Excel數據分析技能制作各種適合企業具體情況的自動化數據分析模板
第2章 標准化、規范化Excel 表格數據
2.1 設計標准化、規范化的Excel基礎表格
1．結構的科學性
2．數據的易讀性
3．匯總的方便性
4．分析的靈活性
5．外觀的美觀性
案例2-1 設計標准化表格的方法與技巧
1．表格結構設計
2．工號的自動連續填充
3．規范姓名的輸入，不允許在姓名文字中輸入空格
4．規范快速輸入部門名稱
5．快速輸入學歷名稱
6．快速輸入婚姻狀況
7．輸入不重復的18位身份證號碼
8．自動輸入性別
9．自動輸入出生日期
10．自動計算年齡
11．規范輸入入職時間
12．自動計算本公司工齡
13．保證員工基本信息的完整性
14．規范輸入離職時間
15．規范輸入離職原因
16．創建表格，自動復制有效性和公式，並自動美化表格
2.2 不規范表格數據的標准化、規范化
案例2-2 修改非法日期，使之成為真正日期
案例2-3 修改文本型數字，使之能夠使用函數計算
案例2-4 刪除數據中的特殊字符，讓數據還原本來面目
案例2-5 填充數據，保證數據的完整性
案例2-6 數據分列，讓不同類型數據各居其位
案例2-7 分列數字和漢字——規律明顯
案例2-8 分列數字和漢字——規律不明顯
案例2-9 數據重新布局，將多次刷卡數據分成列
案例2-10 將道路及其門牌號分列
總結
第3章 必須掌握的函數和公式基本技能
3.1 梳理表格數據的邏輯關系
1．搞清楚自己要做什麼
案例3-1 管理費用的趨勢分析和結構分析
2．分析表格數據之間的邏輯關系
案例3-2 銷售收入達成分析
案例3-3 計算物料最新采購價格和加權平均采購價格
3．學會畫邏輯流程圖
案例3-4 根據工齡工資標准計算工齡工資
案例3-5 根據不同城市、不同工齡計算補貼標准
4．設計好分析報告的結構和標題
案例3-6 重新設計報告標題以簡化計算公式
案例3-7 設計二級分層表格結構
3.2 學會創建嵌套函數公式
1．分解綜合法
案例3-8 根據商品名稱查找對應編碼

2．函數對話框——名稱框法
案例3-9 應用單流程嵌套IF公式計算工齡工資
3.3 學會使用條件表達式解決復雜問題
1．什麼是條件表達式
2．了解邏輯運算符
3．簡單的條件表達式
4．復雜的條件表達式
5．很多問題使用條件表達式來解決更方便
3.4 學點數組公式的知識
1．數組的概念
2．數組公式的概念
3．輸入數組公式
4．數組公式應用案例剖析
5．常見數組公式應用舉例
案例3-10 計算數據區域內N 個最大數或最小數之和
案例3-11 獲取數據列中最后一個非空單元格數據
3.5 適當使用名稱
1．定義名稱的規則
2．定義名稱的三種方法
3．編輯、修改和刪除名稱
3.6 不可忽略的絕對引用和相對引用知識
1．單元格引用方式
2．單元格引用方式對公式的影響
3．引用其他工作表單元格
4．單元格引用的兩種表示法A1 和R1C1有什麼不同
3.7 公式和函數的其他應當掌握的小技巧
1．復制公式本身
2．復制公式的一部分
3．查看公式中的某些表達式計算結果
4．將公式分行輸入，以便使公式更加容易理解和查看
第4 章 邏輯判斷函數及其實際應用
4.1 只使用IF函數的邏輯判斷
1．IF函數的基本語法
案例4-1 計算含有錯誤值的單元格區域
2．嵌套IF函數
3．使用條件表達式來代替嵌套IF函數
4.2 邏輯函數、信息函數聯合使用的復雜邏輯判斷
1．使用AND函數組合與條件
案例4-2 將逾期數據按時間不同標識出來
2．使用OR 函數組合或條件
案例4-3 計算公司年休假天數
3．使用IFERROR 或者ISERROR函數處理公式的錯誤值
4．使用ISNUMBER函數處理特殊的問題
4.3 實際應用案例解析
案例4-4 統計員工的出勤情況（固定時間）
案例4-5 統計員工的出勤情況（階段時間）
案例4-6 統計18:00~20:00 以及20:00以后的刷卡人數
案例4-7 處理重復刷卡數據
案例4-8 按成本中心分析工資的四分位值
案例4-9 根據稅后工資反算稅前工資
案例4-10 制作個人所得稅計算表
案例4-11 調整信用期
案例4-12 判斷應收賬款賬齡區間
4.4 邏輯函數和信息函數的其他應用
1．自定義數據有效性
案例4-13 在員工信息表中輸入身份證號碼
2．復雜的條件格式
案例4-14 自動跟蹤不同的業績變動率
第5章 日期函數及其實際應用
5.1 常用日期函數的基本使用方法
1．TODAY函數：動態日期計算
2．EDATE函數：計算幾個月之前或之后的日期
3．EOMONTH函數：計算幾個月之前或之后的月底日期
4．DATEDIF 函數：計算兩個日期之間的期限
5．DATE函數：把代表年、月、日的三個數字組合成一個日期
6．YEAR、MONTH和DAY函數：把日期拆分成年、月、日三個數字
7．WEEKDAY函數：判斷日期為星期幾
8．WEEKNUM函數：判斷日期為當年的第幾周
9．YEARFRAC函數：計算小數點年數
5.2 常用日期函數的實際應用案例
案例5-1 指定月數或年數后的具體日期
案例5-2 指定天數、月數或年數后的月底日期
案例5-3 根據出生日期計算年齡
案例5-4 根據入職日期計算工齡
案例5-5 設計動態的日程表和考勤表
案例5-6 計算應付款截止日，遇雙休日順延
案例5-7 設計動態的固定資產折舊表
1．計算折舊開始日期
2．計算固定資產到期日
3．計算已計提折舊月數
4．計算剩余折舊月數
5．計算當月折舊額
6．計算本年度累計折舊額
7．計算累計折舊額
8．其他計算公式和輸入項目
9．折舊表的使用
案例5-8 編制銷售周報表
5.3 日期函數的其他應用案例
案例5-9 確定某日期所在年已經過去的天數和剩余天數
案例5-10 獲取某個月最后一天的日期以及該月有多少天
案例5-11 確定某個日期在該年的第幾季度
第6 章 文本函數及其實際應用
6.1 獲取文本的部分數據
案例6-1 從文本字符串左邊往右取指定長度的字符
案例6-2 從文本字符串右邊往左取指定長度的字符
案例6-3 從文本字符串中間指定位置往右取指定長度的字符
案例6-4 從文本字符串右邊數指定位置往左取指定長度的字符
案例6-5 從某個特定字符位置取指定長度的字符
案例6-6 將前部分是數字、后部分是漢字的字符串分成兩列
案例6-7 將前部分是漢字、后部分是數字的字符串分成兩列
案例6-8 將數字、漢字混合的字符串中的數字取出
案例6-9 將前部分是字母、后部分是數字的字符串分成兩列
案例6-10 將前部分是數字、后部分是字母的字符串分成兩列
案例6-11 將金額的各個數字依次填入到連續的單元格
案例6-12 從身份證號碼獲取出生日期和性別
6.2 把數字和日期轉換為指定格式的文本
1．TEXT函數的基本用法
2．將一個單元格的日期和時間分成兩列
3．將日期轉換成中文月份或者英文月份名稱
案例6-13 根據日期流水賬制作月份（英文標題）匯總表
6.3 獲取當前文件的有關信息
案例6-14 獲取文件路徑、文件名和工作表名等信息
第7章 分類匯總函數及其實際應用
7.1 常見的數據分類匯總問題概述
1．兩種常見的分類匯總方式：計數與求和
2．兩種條件下的分類匯總：單條件與多條件
3．兩種匹配模式下的分類匯總：精確匹配與模糊匹配
4．五個分類匯總函數的基本使用語法
7.2 單條件計數與單條件求和
1．精確匹配下的單條件計數
案例7-1 COUNTIF 函數進行單條件計數：簡單應用
案例7-2 使用COUNTIF函數進行自定義有效性設置：
不允許輸入重復數據
案例7-3 使用COUNTIF函數核對兩個表格的數據
2．模糊匹配下的單條件計數
案例7-4 根據明細表統計每種材料的購買次數
3．精確匹配下的單條件求和
案例7-5 計算每個供應商的采購次數和采購總金額
4．模糊匹配下的單條件求和
案例7-6 計算每個部門和每個費用的總金額
7.3 多條件計數與多條件求和
1．精確匹配下的多條件計數
案例7-7 按部門統計總人數、男女人數、已婚未婚人數、
各個學歷的人數
2．模糊匹配下的多條件計數
3．精確匹配下的多條件求和
案例7-8 按地區分別匯總自營店和加盟店的銷售總額和銷售成本
4．模糊匹配下的多條件求和
案例7-9 按客戶、按產品大類進行匯總
7.4 SUMPRODUCT函數的綜合應用
1．SUMPRODUCT 函數的基本用法
案例7-10 計算所有產品的銷售總額、折扣額、銷售凈額
2．SUMPRODUCT 函數的變形用法
案例7-11 通過銷售表計算每個客戶的銷售總額
3．SUMPRODUCT 函數用來進行條件計數
案例7-12 制作能夠查看任意指定年份員工流動率變化的模板
4．SUMPRODUCT 函數用來進行多條件求和
案例7-13 計算每個產品類別、每個月的總金額
案例7-14 根據管理費用科目余額表匯總每個費用項目
每個月的總金額
案例7-15 從銷售及回款清單匯總每個客戶每個賬齡的應收
賬款總額
5．SUMPRODUCT 函數的其他應用
案例7-16 從采購數據計算物料的加權平均采購價格和算術
平均采購價格
案例7-17 按照客戶編碼和存貨名稱核對從兩套軟件中導出
的數據是否一致
6．SUMPRODUCT 函數的優點和缺點
7.5 條件計數函數與條件求和函數的一個特殊問題
第8章 查找引用函數及其實際應用
8.1 查找引用問題概述
1．單條件查找和多條件查找
2．精確匹配數據查找和模糊匹配數據查找
3．精確定位單元格查找和模糊定位單元格查找
4．單一數據查找和重復數據查找
5．單區域查找和多區域查找
6．固定區域查找和滾動區域查找
7．常用查找引用函數的使用語法
8.2 VLOOKUP函數各種實際應用
1．精確匹配下的單條件查詢
案例8-1 查詢每個人的實發工資
2．模糊匹配下的單條件查詢
案例8-2 查找指定城市價格
3．多個區域的數據查找
案例8-3 根據編碼從「低值品」和「原材料」中查詢對應的數據
4．從不同列取數的數據查找
案例8-4 隨意指定一個工資項目就能把每個員工的工資項目取出來
案例8-5 查詢指定員工的所有工資項目
（工資項目橫向排列，順序與工資表一致）
案例8-6 查詢指定員工的所有工資項目（工資項目垂直排列，順序
與工資表一致）
5．模糊定位單元格查找
案例8-7 在應收賬款清單中通過逾期天數判斷賬齡
案例8-8 統計各個稅率下的人數、稅前工資總額、個稅總額
8.3 MATCH函數和INDEX函數的各種實際應用
1．核對兩個表格
案例8-9 使用MATCH 函數核對兩個表格數據
2．精確定位指定數據的單元格地址
案例8-10 使用MATCH函數和ADDRESS函數查找指定
數據的單元格地址
3．模糊定位指定數據所在單元格
案例8-11 利用查找函數進行數學插值計算
4．定位最大值/最小值的單元格地址
案例8-12 從一個數據區域中查找最大值所屬的地區和產品
5．多條件精確查找
案例8-13 根據數據表計算每個員工的小時津貼
案例8-14 從流水賬整理成分類匯總表
6．模糊查找
案例8-15 分列漢字、字母和數字
案例8-16 自動根據工作令號把真正的成本對象編碼查詢出來
7．財務中的典型應用：成本費用分析
案例8-17 從管理費用數據中按照部門和費用項目分類匯總
案例8-18 制作動態成本分析報告，查看任意產品的成本構成
8．人力資源中的典型應用：制作員工簡歷表
案例8-19 設計動態查詢表，查詢指定員工的基本信息
8.4 INDIRECT函數的各種實際應用
1．滾動循環查找技術：制作明細表
案例8-20 制作對賬單表格，把指定客戶的所有銷售明細查找出來
2．滾動匯總技術：快速匯總大量工作表
案例8-21 各月利潤表數據的動態滾動匯總
案例8-22 制作動態員工工資單查詢表
案例8-23 制作每個部門各個月的預算執行情況跟蹤分析表
案例8-24 制作查看指定部門、指定費用各個月的跟蹤分析表
案例8-25 按部門匯總每個月的應發工資合計數
案例8-26 按部門、按費用匯總跟蹤分析每個月的預算執行情況
案例8-27 指定部門、指定費用的各個月匯總分析
3．工作表間的累計值自動計算
案例8-28 自動計算每天的累計數
4．利用INDIRECT 函數與ROW函數構建常量數組
8.5 OFFSET函數的各種實際應用
案例8-29 獲取動態數據區域，制作動態數據源的數據透視表
案例8-30 獲取動態數據區域，作為數據有效性序列來源
案例8-31 計算截至指定月份的累計值
案例8-32 從任意指定項目列中進行分類匯總
案例8-33 復雜的考勤匯總
8.6 其他查找函數的實際應用
案例8-34 LOOKUP函數實際應用
案例8-35 CHOOSE函數實際應用
案例8-36 TRANSPOSE 函數實際應用
案例8-37 使用HYPERLINK函數自動建立超鏈接
案例8-38 使用HYPERLINK函數設計產品圖片查看系統
8.7 查詢數據的幾個應注意的問題
1．查不到數據出現錯誤怎麼辦
2．為什麼會查不到數據
3．如何區別對待零值與空值
Part 02 動態圖表
第9章 制作動態圖表的基本原理和方法
9.1 兩類動態圖表解析
案例9-1 自動調整數據源的動態圖表
案例9-2 控件控制的動態圖表
案例9-3 兩類動態圖表的結合
案例9-4 在單元格利用數據有效性來控制圖表的顯示
9.2 制作動態圖表五大基本方法及其原理
1．篩選法
案例9-5 利用Excel的篩選功能實現訂單動態監控
2．動態名稱法
3．輔助區域法
4．數據透視圖法
案例9-6 制作數據透視圖
5．導入數據法
案例9-7 制作動態圖表
6．必須牢記的動態圖表制作的六大步驟
9.3 讓圖表能動的元素都動起來
案例9-8 制作動態的圖表標題
案例9-9 制作動態的坐標軸標題
案例9-10 制作動態的數據標簽
9.4 制作動態圖表需要注意的幾個問題
1．如何保護繪圖數據的安全性
2．使用表單控件制作動態圖表需要注意的一個問題
第10章 利用表單控件制作各種動態圖表
10.1 插入控件的基本方法和注意事項
1．插入表單控件的基本方法
2．在功能區顯示開發工具
10.2 選項按鈕使用方法及應用案例
案例10-1 可控費用與不可控費用分析
案例10-2 兩年銷售收入、銷售成本、銷售利潤分析
10.3 復選框使用方法及應用案例
案例10-3 三年資產價格同比分析
案例10-4 成本趨勢分析
10.4 組合框使用方法及應用案例
案例10-5 使用一個組合框
案例10-6 使用多個彼此獨立的組合框
案例10-7 使用多個彼此關聯的組合框
案例10-8 分析指定時間段內的數據
10.5 滾動條使用方法及應用案例
案例10-9 利潤敏感性分析模型
案例10-10 動態高亮顯示數據點
案例10-11 動畫效果的計划完成對比分析圖
10.6 數值調節鈕使用方法及應用案例
案例10-12 動態顯示最新幾個數據
案例10-13 動態顯示指定日期之前或之后的幾個數據
10.7 列表框使用方法及應用案例
案例10-14 使用列表框控制圖表顯示
Part 03 自動化數據分析模板
第11章 管理費用滾動跟蹤分析模板
11.1 設計滾動匯總表
11.2 制作動態趨勢分析圖
11.3 制作動態結構分析圖
1．指定部門下的費用結構分析
2．指定費用下的部門占比分析
第12章 產品成本滾動跟蹤分析模板
12.1 分析模板效果
12.2 產品成本變動趨勢跟蹤分析
12.3 產品成本結構跟蹤分析
第13章 預算執行情況滾動跟蹤分析模板
13.1 基礎數據
13.2 模板效果
13.3 模板制作
1．建立滾動匯總表
2．繪制動態分析圖
第14章 薪酬滾動匯總分析模板
14.1 基礎薪酬表
14.2 制作員工的全年工資單
14.3 制作部門工資清單
14.4 制作部門工資項目分析表
14.5 制作全部工資項目分析表
14.6 制作工資四分位值分析表
1．分析指定部門的各個月的工資四分位值
2．分析指定月份的各個部門的工資四分位值