用EXCEL創(chuàng)造價值課件

1、趙明哲用EXCEL創(chuàng)造價值用EXCEL創(chuàng)造價值個人介紹工作經(jīng)歷曾經(jīng)就職于摩托羅拉，搜狐，世界銀行所獲榮譽OFFICE 大師級認證，2019&2019年連續(xù)兩年最佳EXCEL應用講師首位香港大學中國商業(yè)學院特聘講師，搜狐集團財務數(shù)據(jù)分析顧問部分服務過的客戶個人介紹工作經(jīng)歷曾經(jīng)就職于摩托羅拉，搜狐，世界銀行所獲榮譽O基礎功能函數(shù)數(shù)據(jù)透視表培訓內(nèi)容數(shù)據(jù)透視表函數(shù)基礎功能排序分類匯總保護工作表選擇性粘貼分列查找引用類函數(shù)截取數(shù)據(jù)類函數(shù)條件判斷類函數(shù)數(shù)據(jù)透視表的基礎數(shù)據(jù)透視表的美化數(shù)據(jù)透視表的邏輯數(shù)據(jù)透視表的技巧AIM基礎功能函數(shù)數(shù)據(jù)透視表培訓內(nèi)容數(shù)據(jù)透視表函數(shù)基礎功能排序AI排序分類匯總選擇性粘貼分列

2、高效辦公與發(fā)散思維高效辦公與發(fā)散思維一般排序多條件排序按照特定字段排序排序的延伸排序一般排序排序 擴展/當前的區(qū)別一般排序一般排序注意問題： 數(shù)據(jù)包含標題多條件排序多條件排序自定義序列按特定字段培訓按特定字段培訓Office按鈕- Excel選項- 常用- 編輯自定義列表2019自定義序列Office按鈕-2019自定義序列文件- 選項- 高級- 編輯自定義列表2019自定義序列文件-2019自定義序列還原排序?qū)o規(guī)律的文本進行排序排序的延伸排序的延伸多用于篩選后，將數(shù)據(jù)粘回時使用還原排序還原排序保護工作表保護工作表一般分類匯總多重分類匯總分類匯總常見問題分類匯總一般分類匯總分類匯總請分類匯總

3、出： 每位銷售人員銷售額是多少,一共賣出多少件商品，每種商品賣出多少件 練習請分類匯總出：練習粘貼具有公式的數(shù)據(jù)選擇性粘貼的運算轉(zhuǎn)置選擇性粘貼粘貼具有公式的數(shù)據(jù)選擇性粘貼只想復制原始區(qū)域的數(shù)值而不是公式,或者只想復制原始區(qū)域的格式而不需要內(nèi)容時選擇性粘貼選擇性粘貼粘貼具有公式的數(shù)據(jù)粘貼具有公式的數(shù)據(jù)超鏈接的處理方法選擇性粘貼的運算選擇性粘貼的運算轉(zhuǎn)置選擇性粘貼轉(zhuǎn)置選擇性粘貼斜線單元格斜線單元格分列的兩大作用 分欄 強制轉(zhuǎn)換格式分列分列的兩大作用分列11 01 24 19850816 228 1身份證的含義身份證的含義分列與選擇性粘貼分列與選擇性粘貼VlookupLookup_value 要查找

4、的值KEY 兩個表共同的點在查詢表中選擇Table_array 查找的范圍一般情況下全選即可在被查詢表中選擇VlookupLookup_value 要查找的VlookupCol_index_num 查詢的數(shù)據(jù)所在列號相對于第一列，查詢的值在第幾列在被查詢表中數(shù)Range_lookup 精確/模糊 查詢 選擇False即可VlookupCol_index_num 查詢的數(shù)據(jù)所在VLOOKUPVLOOKUP=substitute(text, ” ” , ” )去除中間空格的Substitute對哪個單元格處理雙引號之間有一個空格雙引號無空格Substitute對哪個單元格處理雙引號之間有一個空格雙

5、引MID =MID（ , , ）截取類函數(shù)對哪個單元格截取TEXT A1從第幾位開始截取Start_Num截取的長度，而不是截取到第幾位3Num_Chars4MID截取類函數(shù)對哪個單元格截取TEXT A1從第幾位開數(shù)據(jù)透視表 數(shù)據(jù)透視表的基礎 數(shù)據(jù)透視表常見問題 數(shù)據(jù)透視表的分頁 數(shù)據(jù)透視表的組合 數(shù)據(jù)透視表與函數(shù)數(shù)據(jù)處理與數(shù)據(jù)分析數(shù)據(jù)處理與數(shù)據(jù)分析數(shù)據(jù)透視表 何時使用數(shù)據(jù)透視表數(shù)據(jù)透視表的作用 數(shù)據(jù)透視表 包括大量錯綜復雜數(shù)據(jù)的表格，希望快速整理出一份具有實際意義的報表希望找出同類數(shù)據(jù)在不同時期的某種特定關系希望對數(shù)據(jù)進行合理有效地分組需要經(jīng)常查詢分析數(shù)據(jù)的變化趨勢數(shù)據(jù)源經(jīng)常變化，然而又需要

6、經(jīng)常分析和處理最新的數(shù)據(jù)源什么時候使用數(shù)據(jù)透視表包括大量錯綜復雜數(shù)據(jù)的表格，希望快速整理出一份具有實際意義的數(shù)據(jù)透視表的基礎行標簽/列標簽/數(shù)值/報表篩選值的匯總方式刷新數(shù)據(jù)透視表建立總分關系合并標簽插入空行空單元格填充數(shù)據(jù)透視表的基礎行標簽/列標簽/數(shù)值/報表篩選數(shù)據(jù)透視表的常見問題數(shù)據(jù)透視表的常見問題數(shù)據(jù)透視表基礎練習1數(shù)據(jù)透視表基礎練習11. 請統(tǒng)計出每個部門工資是多少2. 請統(tǒng)計出每個職位在各個部門的工資是多少3. 請統(tǒng)計出每個職位都有哪些學歷，每個學歷都有多少人4. 請統(tǒng)計出每個部門的工資占整體工資多少比例數(shù)據(jù)透視表的基礎練習21. 請統(tǒng)計出每個部門工資是多少數(shù)據(jù)透視表的基礎練習2數(shù)

7、據(jù)透視表的分頁數(shù)據(jù)透視表的分頁數(shù)據(jù)透視表的分頁下拉箭頭數(shù)據(jù)透視表的分頁下拉箭頭請按照面試部門進行拆分拆分條件如下拆分后的各個面試部門數(shù)據(jù)僅展示參加初試者，忽略掉不參加者樣式與下圖一樣：面試崗位合并且居中，每個項目各獨自在一列展示，無各個匯總項數(shù)據(jù)透視表的分頁練習請按照面試部門進行拆分數(shù)據(jù)透視表的分頁練習數(shù)據(jù)透視的組合數(shù)據(jù)透視表的組合數(shù)據(jù)透視表的組合與分列數(shù)據(jù)透視表的組合與函數(shù)數(shù)據(jù)透視的組合數(shù)據(jù)透視表的組合請按照要求展示出公司的人數(shù)分布1. 組合出公司25歲以下，45歲以上，中間為每五歲的人數(shù)分布 2.并在上述基礎上再組合出10000元以下，40000元以上，中間位每5000元的人數(shù)分布數(shù)據(jù)透視

8、表的組合練習請按照要求展示出公司的人數(shù)分布數(shù)據(jù)透視表的組合練習 =IF（ , , ）IF函數(shù)Logical_test B2=1Value_True Value_False 邏輯判斷IF函數(shù)Logical_test B2=1Va不包含的寫法A. 不等于B. C. B2*C2, 評估值為&B2*C2, “請重新評估)=IF(B2-$H$2B2*C2,IF的數(shù)字比較IF的文本比較IF的計算IF的單元格比較與計算IF的基礎使用方式IF的數(shù)字比較IF的基礎使用方式IF高級進階-IF進階1銷售額在800萬（含800萬）以上的獎金系數(shù)為銷售額的20%銷售額在500萬（含500萬）至800萬之間的獎金系數(shù)為銷

9、售額的10%銷售額在500萬以下為銷售額的5%IF高級進階-IF進階1銷售額在800萬（含800萬）以上的IF的嵌套IValue_True，=IF(Logical_Test,如果是足球褲且如果是黑色的Value_False）IF(Logical_test,Value_True,Value_Fales),IF的嵌套IValue_True，=IF(Logical_TIF的嵌套IIValue_True，=IF(Logical_Test,如果不是足球褲，則Value_False）IF(Logical_test,Value_True,Value_Fales)IF的嵌套IIValue_True，=IF(L

10、ogical_=IF(F2=8000000,F2*20%,IF(F2=5000000,F2*10%,F2*5%)=IF(F2=8000000,F2*20%,IF(F2=銷售額在800萬（含800萬）以上的獎金系數(shù)為銷售額的20%銷售額在500萬（含500萬）至800萬之間的獎金系數(shù)為銷售額的10%銷售額在300萬（含300萬）至500萬之間的獎金系數(shù)為銷售額的8%銷售額在300萬以下的獎金系數(shù)為銷售額的5%銷售額在800萬（含800萬）以上的獎金系數(shù)為銷售額的20%用EXCEL創(chuàng)造價值課件請判斷所在區(qū)域如果是以 “BJ”起始的，那么則為北京地區(qū)如果是以 “SH”起始的，那么則為上海地區(qū)如果是以

11、 “HK”起始的，那么則為香港地區(qū)IF高級進階-IF進階3請判斷所在區(qū)域IF高級進階-IF進階3北京天地在線廣告有限公司協(xié)議規(guī)定銷售額大于800萬時，返點為銷售額的20%銷售額500-800萬時，返點為銷售額的10%銷售額500以下時，返點為銷售額的8%鞍山天鼎文化廣告公司協(xié)議規(guī)定銷售額大于500萬時，返點為銷售額的8%銷售額大于300-500萬時，返點為銷售額的6%銷售額300萬以下時，返點為銷售額的5%杭州開創(chuàng)網(wǎng)絡廣告有限公司協(xié)議規(guī)定銷售額大于700萬時，返點為銷售額的15%銷售額大于400-700萬時，返點為銷售額的8%銷售額400萬以下時，返點為銷售額的6%IF高級進階-IF進階4根據(jù)協(xié)議只有以下3家有返點費，而其他公司沒有返點費北京天地在線廣告有限公司鞍山天鼎文化廣告公司杭州開創(chuàng)網(wǎng)絡廣告有限公司北京天地在線廣告有限公司協(xié)議規(guī)定IF高級進階-IF進階4根據(jù)=IF(A2=北京天地在線廣告有限公司,IF(B2=8000000,B2*20%,IF(B2=5000000,B2*10%,B2*8%),IF(A2=鞍山天鼎文化廣告公司,IF(B2=5000000,B2*8%,IF(B2=3000000,B2*6%,B2*5%),IF(A2=杭州開創(chuàng)網(wǎng)絡廣告有限公司,IF(B2=7000000,B2*1