輕松玩轉Excel函數，高效進行商業數據處理

課程背景：

很多學員一聽到函數，第一印象就是很難很強大，門檻太高。

本次課程，老師將通過企業實操案例（案例均來自老師10多年企培收集而來）對各類實用函數做分析講解，講述多種函數的組合使用，分析函數在商務辦公中的動態實用性，利用函數全動態捕獲分析表格中的數據，動態程序各類高階圖表，通過生動有趣的講解，讓函數的課堂變得更加生動、直觀、實用及有趣。

課程收益：

● 利用函數的嵌套組合用法，幫助學員解決工作中的復雜問題

● 掌握高階數組函數的概念和用法，運用函數高效處理數據，提高工作效率

● 在各種工作場景下舉一反三的靈活處理函數的綜合應用，輕松玩轉函數的靈活應用

課程時間：2天，6小時/天

課程對象：OFFICE用戶；財務、行政、市場分析人員；企業白領、企業管理者

課程方式：講師講授+案例分析+實操演練

課程工具包：EXCEL高效數據分析操作工具包、學員操作手冊

課程大綱

導入篇：數據提效基石——函數與公式通用技巧解析

技巧一：單元格地址靈活鎖定

技巧二：常見函數錯誤的分析和處理

1. 解密函數錯誤類型

2. 使用錯誤檢查工具

技巧三：保護和隱藏工作表中的公式

第一講：合計加速器——數字函數用法

一、求和也能這樣用，鮮為人知的應用技巧

1. 合計各項匯總值，以前至少點擊5次求和按鈕，現只需一次全部搞定

2. 格式相同的不同數據表，一個求和按鈕全部搞定

操作：余額累計中隱藏的函數處理思維

3. 快速給合并單元格添加序號

二、統計也能這么快，分類匯總函數subtotal

優勢1：認認真真做合計，不把小計算進來

優勢2：只統計篩選出來的數據

優勢3：永遠連續的序號

第二講：整理判斷優化劑——邏輯判斷函數

一、邏輯函數If/And/Or靈活使用、多層嵌套

運用：考勤統計

分析：邏輯不再混亂的整理思維

二、2016版函數ifs、switch進行多條件判斷

第三講：統計數據照明鏡——統計分析函數

一、Countif、Countifs\Sumif、Sumifs精確、模糊計數和求和

運用1：統計關鍵字產品數量

運用2：計算指定內容數據重復記錄數

運用3：兩列對比數據的差異

運用4：多列條件求和

運用5：同列條件求和

二、2016版函數minifs\maxifs多條件求最小值、最大值

三、Sumproduct乘積之和高端用法

用法1：乘積之和

用法2：Excel老玩家才熟悉的條件統計

1）包含關鍵字的條件統計

2）可“且”可“或”的多條件統計

3）不重復技術（如各部門下有多少種產品）

四、Rank對業績排名次

1. Rank實現數據自動排名

2. 每位員工在部門內部的排名

3. 分析數據前幾名Large和后幾名Small

五、返回當前行號Row和列號Column

第四講：統計減壓器——數據庫函數

痛點導入：條件太多數據太多，復雜數據整合又慢又雜，還總是出現錯誤？

1. 按條件求區域數據的平均值Daverage

2. 按條件求區域中的最大值dmax

3. 按條件求區域中的最小值dmin

第五講：數據追蹤機——查找引用函數

一、用Vlookup函數來抓取另一個表中的數據

1. 結合Match函數獲取查詢值的所有相關數據

思考：明明兩張表都有數據，為什么就查不到，深入解析出錯原因

運用：模糊查詢功能計算工資稅率、按公司簡稱查詢數據

優勢：突破Vlookup只能返回一個結果，實現一對多查詢

2. Index匹配員工照片

運用：新舊兩表，快速核對每個單元格數據差異

3. VLookup、indirect、match超高階應用：跨多表查詢

二、Hlookup按行索數據

三、一個Lookup搞定多條件查找

四、實用又有趣的動態數據源函數Offset

第六講：字符提取百寶箱——文本函數

1. Left\Mid\Right讓文本自由截取

2. Find函數提取地址中的城市名

3. 查找替換，中文、英文、數字拆分

4. 2016版函數Concat、Textjion連接多單元格文本

5. “--”和Text輕松提取身份證號碼中的出生日期

第七講：日期時間匹配器——DateDif函數

1. 計算日期的間隔年份、月份、天數

2. 輕松計算年齡、工齡，精確到多少月多少天

運用1：根據入職日期計算轉正日期

運用2：超過24小時的時間間隔

第八講：交叉運算操作儀——高級數組公式

一、認識數組的概念和特性

二、靈活應用數組公式

1. Sumif/sumifs在數組中的應用

2. Textjoin根據部門將所有人員姓名放在一個單元格里

3. 公式里的轉置TRANSPOSE

第九講：VBA的“前身”——宏表函數

一、靈活使用名稱，用更直觀的方法引用數據

1. 單元格及區域命名

2. 指定名稱實現命名自動化

3. 命名公式打破函數嵌套限制

二、Get.Cell按單元格格式統計分析

1. 根據單元格顏色求和

2. 提取公式

三、Get.Workbook批量獲取工作表名字

四、Files批量提取文件夾里所有文檔名字

五、evaluate文本算式也能運算

六、宏表函數的更新

補充篇：動態數組王炸工具——O365版動態數組函數（拓展選講）

1. FILTER函數實現高級篩選

2. UNIQUE函數動態提取唯一值

3. SORT、SORTBY區域動態排序

4. SEQUENCE解決單行單列轉多行多列問題

5. RANDARRAY創建隨機數組