EXCEL在日常工作中的運用20160415

1、Excel在日常工作中的運用在日常工作中的運用目錄目錄1 Excel在銷售收入管控中的在銷售收入管控中的8個應用個應用2 Excel在成本費用管控中的在成本費用管控中的8個應用個應用3 Excel在資產管控中的在資產管控中的7個應用個應用4 Excel在負債管控中的在負債管控中的3個應用個應用5 Excel在財務報表分析中的在財務報表分析中的6個應用個應用6 Excel在量本利分析中的在量本利分析中的4個應用個應用 1 Excel在銷售收入管控中的在銷售收入管控中的8個應用個應用 案例案例1 訂單數據整理與篩選分析訂單數據整理與篩選分析 案例案例2 銷售數據整理與分類匯總分析銷售數據整理與分類

2、匯總分析 案例案例3 銷售數據的分級顯示銷售數據的分級顯示 案例案例4 使用函數制作銷售月報和周報使用函數制作銷售月報和周報 案例案例5 銷售數據整理與多維度透視分析銷售數據整理與多維度透視分析 案例案例6 各分公司多重區(qū)域銷售數據匯總分析各分公司多重區(qū)域銷售數據匯總分析 案例案例7 制作銷售預算完成情況進度圖制作銷售預算完成情況進度圖 案例案例8 制作動態(tài)銷售圖表制作動態(tài)銷售圖表 案例案例1 訂單數據整理與篩選分析訂單數據整理與篩選分析 數據分列數據分列 設置分隔符號；設置列數據格式設置分隔符號；設置列數據格式 自動篩選自動篩選 篩選某個項目；空白篩選某個項目；空白/非空白；自定義篩選非空白

3、；自定義篩選 高級篩選高級篩選 邏輯與條件邏輯與條件 邏輯或條件邏輯或條件 篩選不含重復記錄的數據表篩選不含重復記錄的數據表案例案例2 銷售數據整理與分類匯總分析銷售數據整理與分類匯總分析 將文本型數據轉換為純數字將文本型數據轉換為純數字 利用智能標記利用智能標記 利用選擇性粘貼工具利用選擇性粘貼工具 單級分類匯總單級分類匯總 按分類字段排序；建立分類匯總表按分類字段排序；建立分類匯總表 復制與刪除分類匯總結果復制與刪除分類匯總結果 定位可見單元格定位可見單元格 多級分類匯總多級分類匯總 求和；計數；求平均值；最大值；最小值等方式求和；計數；求平均值；最大值；最小值等方式 嵌套分類匯總嵌套分類

4、匯總 由外向內進行由外向內進行案例案例3 銷售數據的分級顯示銷售數據的分級顯示 定位空單元格定位空單元格 快速填充小計數快速填充小計數(ALT+=組合鍵組合鍵) 自動建立分級顯示自動建立分級顯示 分級顯示表的使用方法分級顯示表的使用方法 清除分級顯示清除分級顯示案例案例4 使用函數制作銷售月報和周報使用函數制作銷售月報和周報 為單元格或單元格區(qū)域定義名稱的方法為單元格或單元格區(qū)域定義名稱的方法 MONTH函數：返回一個日期型數據中的月份數字函數：返回一個日期型數據中的月份數字 WEEKNUM函數：返回一個日期型數據中的周次函數：返回一個日期型數據中的周次數字數字 SUMIF函數：根據指定條件對

5、若干單元格求和函數：根據指定條件對若干單元格求和 SUMPRODUCT函數：將給定的幾個數組中的對函數：將給定的幾個數組中的對應元素相乘并返回乘積之和應元素相乘并返回乘積之和 建立與復制公式建立與復制公式 引用單元格的方式引用單元格的方式 創(chuàng)建列表的方法創(chuàng)建列表的方法 案例案例5 銷售數據整理與多維度透視分析銷售數據整理與多維度透視分析 銷售數據整理銷售數據整理 LEFT函數：從文本字符串左邊提取指定數目的字函數：從文本字符串左邊提取指定數目的字符符 MID函數：從文本字符串的指定位置開始提取特函數：從文本字符串的指定位置開始提取特定數目的字符定數目的字符 RIGHT函數：從文本字符串右邊提取

6、若干個字符函數：從文本字符串右邊提取若干個字符 DATE函數：返回代表特定日期的序列號函數：返回代表特定日期的序列號 案例案例5 銷售數據整理與多維度透視分析銷售數據整理與多維度透視分析 制作與編輯數據透視表制作與編輯數據透視表 合理設置數據透視表的布局合理設置數據透視表的布局頁字段；行字頁字段；行字段；列字段；數據字段段；列字段；數據字段 修改字段名稱修改字段名稱 設置字段的數字格式設置字段的數字格式 取消數據透視表行總計或列總計取消數據透視表行總計或列總計 取消字段的分類匯總取消字段的分類匯總 合并數據標志合并數據標志 設置錯誤值或空值的顯示方式設置錯誤值或空值的顯示方式 更新數據透視表的

7、數據更新數據透視表的數據案例案例5 銷售數據整理與多維度透視分析銷售數據整理與多維度透視分析 使用數據透視表多維度分析數據使用數據透視表多維度分析數據 修改數據透視表布局修改數據透視表布局 顯示明細數據顯示明細數據 通過組合字段創(chuàng)建多維數據匯總表通過組合字段創(chuàng)建多維數據匯總表 按日期、地區(qū)、商品類別分組按日期、地區(qū)、商品類別分組 通過設置字段顯示方式創(chuàng)建各種比較分析表通過設置字段顯示方式創(chuàng)建各種比較分析表 數據的顯示方式數據的顯示方式差異；差異百分比；占同行或同列差異；差異百分比；占同行或同列百分比；占總合百分比；累計值顯示百分比；占總合百分比；累計值顯示 創(chuàng)建各種比較分析表創(chuàng)建各種比較分析表

8、案例案例5 銷售數據整理與多維度透視分析銷售數據整理與多維度透視分析 制作與使用數據透視圖制作與使用數據透視圖 在數據清單基礎上制作數據透視圖在數據清單基礎上制作數據透視圖 在數據透視表基礎上制作數據透視圖在數據透視表基礎上制作數據透視圖案例案例6 各分公司多重區(qū)域銷售數據匯總分析各分公司多重區(qū)域銷售數據匯總分析 利用合并計算工具制作匯總表利用合并計算工具制作匯總表 設置引用區(qū)域設置引用區(qū)域 創(chuàng)建連至源數據的鏈接創(chuàng)建連至源數據的鏈接 利用數據透視表匯總多重區(qū)域的分公司銷售數據利用數據透視表匯總多重區(qū)域的分公司銷售數據 設置引用區(qū)域設置引用區(qū)域 編輯與美化匯總表編輯與美化匯總表案例案例7 制作銷

9、售預算完成情況進度圖制作銷售預算完成情況進度圖 制作條形圖制作條形圖 編輯與美化圖表編輯與美化圖表案例案例8 制作動態(tài)銷售圖表制作動態(tài)銷售圖表 INDEX函數：返回列表或數組中的元素值函數：返回列表或數組中的元素值 設置組合框控件設置組合框控件 2 Excel在成本費用管控中的在成本費用管控中的8個應用個應用 案例案例9 整理費用報銷數據與制作費用匯總表整理費用報銷數據與制作費用匯總表 案例案例10 工資與個人所得稅計算工資與個人所得稅計算 案例案例11 實發(fā)工資尾數處理與快速制作工資條實發(fā)工資尾數處理與快速制作工資條 案例案例12 制作費用報銷單制作費用報銷單 案例案例13 費用數據自動歸類

10、費用數據自動歸類 案例案例14 多種產品材料成本匯總表多種產品材料成本匯總表 案例案例15 制作多期營業(yè)費用自動匯總表制作多期營業(yè)費用自動匯總表 案例案例16 制作管理費用預算執(zhí)行情況追蹤分析圖制作管理費用預算執(zhí)行情況追蹤分析圖 案例案例9 整理費用報銷數據與制作費用匯總表整理費用報銷數據與制作費用匯總表 整理費用報銷數據整理費用報銷數據 利用分列工具整理日期數據利用分列工具整理日期數據 使用函數提取經手人信息使用函數提取經手人信息 使用查找替換功能轉換文本數字使用查找替換功能轉換文本數字 FIND函數：返回一個字符串在另一個字符串中的函數：返回一個字符串在另一個字符串中的起始位置起始位置 費

11、用報銷數據透視分析費用報銷數據透視分析案例案例10 工資與個人所得稅計算工資與個人所得稅計算 VLOOKUP函數：在表格或數值數組的首列查找函數：在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指指定的數值，并由此返回表格或數組當前行中指定列處的數值定列處的數值 TODAY函數：返回系統(tǒng)當前的日期函數：返回系統(tǒng)當前的日期 INT函數：無條件舍去小數，只保留整數函數：無條件舍去小數，只保留整數 IF函數：執(zhí)行真假值判斷，根據邏輯計算的真假函數：執(zhí)行真假值判斷，根據邏輯計算的真假值，返回不同結果值，返回不同結果 案例案例11 實發(fā)工資尾數處理與快速制作工資條實發(fā)工資尾數處理與快速

12、制作工資條 實發(fā)工資尾數處理實發(fā)工資尾數處理 ROUND函數：返回按指定位數對數值進行四舍五入處函數：返回按指定位數對數值進行四舍五入處理后的結果理后的結果 ROUNDUP函數：朝著遠離零值的方向按指定位數向函數：朝著遠離零值的方向按指定位數向上舍入數字上舍入數字 ROUNDDOWN函數：朝著靠近零值的方向按指定位數函數：朝著靠近零值的方向按指定位數向下舍入數字向下舍入數字 CEILING函數：沿著絕對值增大的方向向上舍入為最函數：沿著絕對值增大的方向向上舍入為最接近的指定數字的倍數接近的指定數字的倍數 FLOOR函數：沿著絕對值減小的方向向下舍入為最接函數：沿著絕對值減小的方向向下舍入為最接

13、近的指定數字的倍數近的指定數字的倍數 案例案例11 實發(fā)工資尾數處理與快速制作工資條實發(fā)工資尾數處理與快速制作工資條 快速制作工資條快速制作工資條案例案例12 制作費用報銷單制作費用報銷單 設置數據的有效性設置數據的有效性 NUMBERSTRING函數：將數字按指定的函數：將數字按指定的格式轉換成大寫格式轉換成大寫 刪除網格線刪除網格線 隱藏部分行和列隱藏部分行和列 保護部分單元格保護部分單元格 保護工作表保護工作表案例案例13 費用數據自動歸類費用數據自動歸類 設置數據有效性設置數據有效性 運用條件格式運用條件格式 自動設置邊框線自動設置邊框線 正確設置引用單元格的方式正確設置引用單元格的方

14、式 費用按部門歸類費用按部門歸類 費用按性質歸類費用按性質歸類案例案例14 多種產品材料成本匯總表多種產品材料成本匯總表 IF函數；函數；VLOOKUP函數；函數；SUMIF函數函數 ISERROR函數：判斷所引用的參數是否為函數：判斷所引用的參數是否為錯誤值錯誤值 ROW函數：返回所引用的單元格的行號函數：返回所引用的單元格的行號 INDIRECT函數：返回由文本字符串指定的函數：返回由文本字符串指定的引用引用 案例案例15 制作多期營業(yè)費用自動匯總表制作多期營業(yè)費用自動匯總表 新增月份工作表中的數據自動列示在匯總新增月份工作表中的數據自動列示在匯總表中表中 IF函數函數 ISERROR函數

15、函數 INDIRECT函數函數 ROW函數函數案例案例16 制作管理費用預算制作管理費用預算執(zhí)行情況追蹤分析圖執(zhí)行情況追蹤分析圖 新增月份的數據自動顯示在圖中新增月份的數據自動顯示在圖中3 Excel在資產管控中的在資產管控中的7個應用個應用 案例案例17 普通日記賬規(guī)范整理與制作現(xiàn)金日記賬普通日記賬規(guī)范整理與制作現(xiàn)金日記賬 案例案例18 應收賬款余額計算及信用政策自動調整應收賬款余額計算及信用政策自動調整 案例案例19 制作應收賬款賬齡分析表制作應收賬款賬齡分析表 案例案例20 快速編制暫付款期末余額明細表快速編制暫付款期末余額明細表 案例案例21 編制存貨的收發(fā)存匯總表并設置庫存量預警編制

16、存貨的收發(fā)存匯總表并設置庫存量預警 案例案例22 存貨信息動態(tài)查詢存貨信息動態(tài)查詢 案例案例23 固定資產折舊計算與臺賬管理固定資產折舊計算與臺賬管理案例案例17 普通日記賬規(guī)范整理與制作現(xiàn)金日記普通日記賬規(guī)范整理與制作現(xiàn)金日記賬賬 整理日期數據整理日期數據 整理憑單號數據整理憑單號數據 快速填充缺失的數據快速填充缺失的數據 篩選現(xiàn)金日記賬篩選現(xiàn)金日記賬 計算現(xiàn)金余額計算現(xiàn)金余額案例案例18 應收賬款余額計算及信用政策自動調應收賬款余額計算及信用政策自動調整整 應收賬款期末余額的計算應收賬款期末余額的計算 為單元格區(qū)域定義名稱為單元格區(qū)域定義名稱 運用條件求和函數運用條件求和函數 信用政策自動

17、調整信用政策自動調整 IF函數函數 OR函數：進行函數：進行“邏輯或邏輯或”關系的判斷關系的判斷 案例案例19 制作應收賬款賬齡分析表制作應收賬款賬齡分析表 為單元格區(qū)域指定名稱為單元格區(qū)域指定名稱 HLOOKUP函數：在表格或數值數組的首函數：在表格或數值數組的首行查找指定的數值，并由此返回表格或數行查找指定的數值，并由此返回表格或數組當前列中指定行處的數值組當前列中指定行處的數值 SUMPRODUCT函數：多條件求和函數：多條件求和 案例案例20 快速編制暫付款期末余額明細表快速編制暫付款期末余額明細表 自動篩選自動篩選 COUNTIF函數：計算區(qū)域中滿足給定條件函數：計算區(qū)域中滿足給定條

18、件的單元格的個數的單元格的個數 案例案例21 編制存貨的收發(fā)存匯總表編制存貨的收發(fā)存匯總表并設置庫存量預警并設置庫存量預警 設置數據有效性設置數據有效性 IF函數函數 ISERROR函數函數 VLOOKUP函數函數 設置條件格式設置條件格式 案例案例22 存貨信息動態(tài)查詢存貨信息動態(tài)查詢 定義名稱定義名稱 VLOOKUP函數函數 COLUMN函數：返回引用的參數所在列的序號函數：返回引用的參數所在列的序號 INDEX函數函數 MATCH函數：返回在指定方式下與指定數值匹函數：返回在指定方式下與指定數值匹配的數組中元素的相應位置配的數組中元素的相應位置 創(chuàng)建列表創(chuàng)建列表 案例案例23 固定資產折

19、舊計算與臺賬管理固定資產折舊計算與臺賬管理 計算固定資產折舊計算固定資產折舊 SLN函數：按直線法計算某項資產在一個期間函數：按直線法計算某項資產在一個期間中的折舊值中的折舊值 SYD函數：返回某項資產按年限總和法計算的函數：返回某項資產按年限總和法計算的指定期間的折舊值指定期間的折舊值 DDB函數：使用雙倍余額遞減法或其他指定方函數：使用雙倍余額遞減法或其他指定方法，計算一筆資產在給定期間內的折舊值法，計算一筆資產在給定期間內的折舊值 案例案例23 固定資產折舊計算與臺賬管理固定資產折舊計算與臺賬管理 登記固定資產臺賬登記固定資產臺賬 使用使用“記錄單記錄單”功能錄入和編輯數據功能錄入和編輯

20、數據 DATEDIF函數：計算兩個日期之間間隔的年、函數：計算兩個日期之間間隔的年、月或天數月或天數 制作固定資產數據匯總表制作固定資產數據匯總表4 Excel在負債管控中的在負債管控中的3個應用個應用 案例案例24 應付賬款到期日提前提醒設置應付賬款到期日提前提醒設置 案例案例25 編制付款合同執(zhí)行進度匯總表編制付款合同執(zhí)行進度匯總表 案例案例26 編制分期還款計劃表編制分期還款計劃表案例案例24 應付賬款到期日提前提醒設置應付賬款到期日提前提醒設置 計算應付賬款到期日計算應付賬款到期日 設置條件格式設置條件格式案例案例25 編制付款合同執(zhí)行進度匯總表編制付款合同執(zhí)行進度匯總表 定義名稱定義

21、名稱 設置數據有效性設置數據有效性 創(chuàng)建列表創(chuàng)建列表 運用條件求和函數運用條件求和函數案例案例26 編制分期還款計劃表編制分期還款計劃表 PMT函數：基于固定利率及等額分期付款方式，函數：基于固定利率及等額分期付款方式，返回貸款的每期償還額返回貸款的每期償還額 IPMT函數：基于固定利率及等額分期付款方式，函數：基于固定利率及等額分期付款方式，返回給定期數內對投資的利息償還額返回給定期數內對投資的利息償還額 PPMT函數：基于固定利率及等額分期付款方式，函數：基于固定利率及等額分期付款方式，返回一項投資在某一給定期間內的本金償還額返回一項投資在某一給定期間內的本金償還額5 Excel在財務報表

22、分析中的在財務報表分析中的6個應用個應用 案例案例27 編制比較財務報表和共同比報表編制比較財務報表和共同比報表 案例案例28 繪制資產結構分析圖繪制資產結構分析圖 案例案例29 繪制收入和利潤趨勢分析圖繪制收入和利潤趨勢分析圖 與凈利潤因素分析圖與凈利潤因素分析圖 案例案例30 繪制兩期財務指標對比分析圖繪制兩期財務指標對比分析圖 案例案例31 計算財務比率及繪制杜邦系統(tǒng)圖計算財務比率及繪制杜邦系統(tǒng)圖 案例案例32 制作財務指標雷達圖及報表分析模型的封制作財務指標雷達圖及報表分析模型的封面面 案例案例27 編制比較財務報表和共同比報表編制比較財務報表和共同比報表 縮位顯示數字自定義格式代碼：

23、縮位顯示數字自定義格式代碼： 縮小縮小1百位百位 0.00 縮小縮小1千位顯示數字：千位顯示數字： 0.00， 縮小縮小1萬位顯示數字：萬位顯示數字： 0!.0, 縮小縮小10萬位顯示數字：萬位顯示數字： 0!.00, 縮小縮小100萬位顯示數字：萬位顯示數字： 0.00, 縮小縮小1000萬位顯示數字：萬位顯示數字： 0!.0, 縮小縮小1億位顯示數字：億位顯示數字： 0!.00, 縮小縮小10億位顯示數字：億位顯示數字： 0.00, 通過組合方式設置分級顯示通過組合方式設置分級顯示 案例案例28 繪制資產結構分析圖繪制資產結構分析圖案例案例29 繪制收入和利潤趨勢分析圖繪制收入和利潤趨勢分析圖與凈利潤因素分析圖與凈利潤因素分析圖案例案例29 繪制收入和利潤趨勢分析圖繪制收入和利潤趨勢分析圖與凈利潤因素分析圖與凈利潤因素分析圖案例案例30 繪制兩期財務指標對比分析圖繪制兩期財務指標對比分析圖案例案例31 計算財務比率及繪制杜邦系統(tǒng)圖計算財務比率及繪制杜邦系統(tǒng)圖 計算財務比率計算財務比率 償債能力比率償債能力比率 獲利能力比率獲利能力比率 營運能力比率營運能力比率 成長性比率成長性比率案例案例31 計算財務比率及繪制杜邦系統(tǒng)圖計算財務比