Excel在財務與管理中的高效運用

韓小良課程目錄實用高效的數(shù)據(jù)整理技巧常用函數(shù)的高效運用快速匯總多個工作簿和工作表數(shù)據(jù)數(shù)據(jù)透視表工具的靈活應用其他應用韓小良實用高效的數(shù)據(jù)整理技巧快速修改非法日期快速轉(zhuǎn)換文本型數(shù)字快速刪除數(shù)據(jù)中的特殊字符快速分列數(shù)據(jù)快速填充數(shù)據(jù)韓小良快速修改非法日期日期是特殊的數(shù)字，是從1開始的序列號。日期永遠是大于零的正整數(shù)。正確輸入日期正確輸入日期：2010-11-17，或者2010/11/17錯誤的輸入日期方式：，或者20101118很多情況下，從系統(tǒng)導入的日期數(shù)據(jù)是文本，并不是真正的日期，必須進行修改（案例01）韓小良快速轉(zhuǎn)換文本型數(shù)字在有些情況下，從數(shù)據(jù)庫導入的數(shù)字是文本型數(shù)字。文本型數(shù)字無法使用SUM等函數(shù)進行計算，也無法利用數(shù)據(jù)透視表等工具進行分析，需要轉(zhuǎn)換為純數(shù)字。方法1：利用智能標記方法2：利用VALUE函數(shù)方法3：利用公式（兩個負號或者乘以1或除以1）方法4：利用選擇性粘貼的批量修改功能（乘以1或者除以1）方法5：利用分列工具案例02將文本型數(shù)字轉(zhuǎn)化為純數(shù)字韓小良快速刪除數(shù)據(jù)中的特殊字符數(shù)據(jù)中的很多“垃圾”字符會影響到數(shù)據(jù)的處理和分析，應當刪除。比如：刪除數(shù)據(jù)中的空格刪除字符中的看不見、打印不出的特殊字符（案例03）韓小良快速分列數(shù)據(jù)有些情況下，從數(shù)據(jù)庫導入的數(shù)據(jù)是一列數(shù)據(jù)，需要根據(jù)實際情況進行分列。方法方法1：使用“分列”工具方法2：實用文本函數(shù)案例04數(shù)據(jù)分列

分列前的數(shù)據(jù)分列后的數(shù)據(jù)韓小良快速填充數(shù)據(jù)有些時候，從系統(tǒng)導入的數(shù)據(jù)表格有大量的空白單元格，需要進行填充。根據(jù)具體情況：填充為上一行的數(shù)據(jù)填充為數(shù)字0案例05快速填充數(shù)據(jù)韓小良常用函數(shù)的高效運用一個表格中，處理分析最多的是數(shù)據(jù)求和匯總和數(shù)據(jù)查詢，因此必須熟練掌握相關(guān)求和匯總函數(shù)和查找函數(shù)的使用。高效求和匯總數(shù)據(jù)的靈活查詢韓小良高效求和匯總低效率的加減公式，既不科學，也容易出錯。在很多情況下，可以使用有關(guān)的求和函數(shù)來解決。常用的求和函數(shù)SUM函數(shù)：無條件求和。但SUM函數(shù)還有另類用法，比如快速計算表格中的小計總和；快速對多個工作表求和SUMIF函數(shù)：單條件求和=SUMIF(條件判斷區(qū)域，條件值，實際求和區(qū)域)SUMPRODUCT函數(shù)：計算乘積和，可以用于多條件求和與多條件計數(shù)=SUMPRODUCT(數(shù)組1，數(shù)組2，數(shù)組3，……..)SUBTOTAL函數(shù)：對數(shù)據(jù)進行分類匯總，主要用于篩選和列表

=SUBTOTAL(函數(shù)類型，計算區(qū)域1，計算區(qū)域2，……..)案例06高效求和匯總韓小良數(shù)據(jù)的靈活查詢查找函數(shù)有很多，其中使用最多的是VLOOKUP函數(shù)、INDEX函數(shù)、MATCH函數(shù)、INDIRECT函數(shù)。VLOOKUP函數(shù)：根據(jù)首列數(shù)據(jù)進行查詢（案例07）=VLOOKUP(查找依據(jù)，查找區(qū)域，指定列位置，邏輯值)INDEX函數(shù)：根據(jù)行索引和列索引進行查詢（案例08）=INDEX(查找區(qū)域，指定行位置，指定列位置)MATCH函數(shù)：對數(shù)據(jù)進行定位（案例08）=MATCH(查找依據(jù)，指定行位置，指定列位置)

韓小良快速匯總多個工作簿和工作表數(shù)據(jù)快速匯總多個結(jié)構(gòu)完全相同的工作表快速匯總結(jié)構(gòu)不同的多個工作表（特殊情況）快速匯總結(jié)構(gòu)不同的多個工作表（一般情況）快速匯總多個有關(guān)聯(lián)的工作表韓小良快速匯總多個結(jié)構(gòu)完全相同的工作表當多個工作表的結(jié)構(gòu)相同時，可以使用合并計算工具快速進行合并計算，同時還可以創(chuàng)建分類匯總和分級顯示。案例研究:案例09快速合并多個結(jié)構(gòu)相同的分報表(當前工作簿)案例10快速合并多個結(jié)構(gòu)相同的分報表(不同工作簿)

韓小良快速匯總結(jié)構(gòu)不同的多個工作表（特殊情況）利用多重合并計算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表，可以快速把數(shù)十個甚至上百個工作表的數(shù)據(jù)匯總在一起，然后在此基礎(chǔ)上進行相關(guān)的統(tǒng)計分析。注意：要匯總的多個工作表數(shù)據(jù)行數(shù)和列數(shù)可以不同，但從數(shù)據(jù)區(qū)域的第2列開始必須是數(shù)字。案例11快速匯總多個工作表數(shù)據(jù)(特殊情況)

三個分公司數(shù)據(jù)匯總到一張工作表韓小良快速匯總結(jié)構(gòu)不同的多個工作表（一般情況）一般情況下，要匯總的各個工作表數(shù)據(jù)可能含有多列文本，此時就不能采用普通的多重合并計算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表來進行匯不總了。最好的解決方法：使用導入外部數(shù)據(jù)+SQL語句。案例12快速匯總多個工作表數(shù)據(jù)(一般情況)

韓小良快速匯總多個有關(guān)聯(lián)的工作表有些情況下，幾個工作表中分別保存不同的數(shù)據(jù)，但他們通過一個關(guān)鍵列數(shù)據(jù)（比如工號）聯(lián)系起來。要將這樣幾個相關(guān)聯(lián)的工作表數(shù)據(jù)進行匯總，可使用MicrosoftQuery。案例13快速匯總多個有關(guān)聯(lián)的工作表數(shù)據(jù)

韓小良數(shù)據(jù)透視表的靈活應用數(shù)據(jù)透視表的基本概念數(shù)據(jù)透視表的類型準備工作創(chuàng)建數(shù)據(jù)透視表的基本方法和注意事項設置數(shù)據(jù)透視的格式利用數(shù)據(jù)透視表快速編制各類統(tǒng)計分析報表綜合應用1：對比分析兩個表格數(shù)據(jù)綜合應用2：進銷存數(shù)據(jù)查詢韓小良數(shù)據(jù)透視表的基本概念什么是數(shù)據(jù)透視表？數(shù)據(jù)透視表能做什么？怎樣靈活使用數(shù)據(jù)透視表？韓小良數(shù)據(jù)透視表的類型普通區(qū)域數(shù)據(jù)透視表合并區(qū)域數(shù)據(jù)透視表外部數(shù)據(jù)源的數(shù)據(jù)透視表OLAP數(shù)據(jù)透視表韓小良準備工作制作數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域必須是數(shù)據(jù)清單數(shù)據(jù)區(qū)域第一行為列標題列標題不能重名數(shù)據(jù)區(qū)域中不能有空行和空列數(shù)據(jù)區(qū)域中不能有合并單元格每列數(shù)據(jù)為同一種類型的數(shù)據(jù)整理數(shù)據(jù)區(qū)域修改非法日期把文本型數(shù)字轉(zhuǎn)換為純數(shù)字刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行和空列取消合并單元格并進行填充去掉字符串前后的空格刪除數(shù)字中的特殊字符刪除不必要的小計行和總計行將二維表格整理為數(shù)據(jù)清單等等韓小良創(chuàng)建數(shù)據(jù)透視表的基本方法和注意事項利用數(shù)據(jù)透視表向?qū)Вò咐?4）按照向?qū)Р襟E進行操作缺點：無法實現(xiàn)數(shù)據(jù)源的動態(tài)更新，除非使用動態(tài)數(shù)據(jù)區(qū)域名稱解決方法：（1）利用導入數(shù)據(jù)方法；（2）利用OFFSET函數(shù)定義動態(tài)名稱利用導入數(shù)據(jù)方法按照導入數(shù)據(jù)向?qū)нM行操作優(yōu)點：可以實現(xiàn)數(shù)據(jù)源的動態(tài)更新；可以在不打開其他工作簿的情況下創(chuàng)建數(shù)據(jù)透視表可以以數(shù)據(jù)庫的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表缺點：刪除數(shù)據(jù)項目后，會遺留“假”項目名稱利用數(shù)據(jù)庫查詢方法按照數(shù)據(jù)庫查詢向?qū)нM行操作優(yōu)點：不用導入數(shù)據(jù)，就可以制作需要的報表可以有選擇性地對某些符合條件的數(shù)據(jù)制作報表可以在不打開其他工作簿的情況下創(chuàng)建數(shù)據(jù)透視表可以以數(shù)據(jù)庫的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表韓小良設置數(shù)據(jù)透視的的格式在數(shù)據(jù)透視表上對數(shù)據(jù)透視表進行重新布局修改字段名稱設置字段的數(shù)字格式取消數(shù)據(jù)透視表的行或列匯總?cè)∠侄蔚姆诸悈R總讓數(shù)據(jù)透視表數(shù)據(jù)按照某一字段進行排序合并數(shù)據(jù)標志設置錯誤值的顯示方式更新數(shù)據(jù)透視表的數(shù)據(jù)韓小良利用數(shù)據(jù)透視表快速編制各類統(tǒng)計分析報表布局透視表設置字段的分類匯總方式（案例14）設置字段的顯示方式（案例14）自定義數(shù)據(jù)透視表（案例14）組合字段（案例15）制作明細表快速匯總大量工作表數(shù)據(jù)韓小良綜合應用1：對比分析兩個表格數(shù)據(jù)兩個工作表有什么不同？如何快速把兩個工作表的差異數(shù)據(jù)找出來？案例16對比分析兩個表格數(shù)據(jù)

韓小良綜合應用2：進銷存數(shù)據(jù)查詢?nèi)绾慰焖倭私飧鱾€物料、商品等的入庫、出庫以及庫存情況？利用數(shù)據(jù)透視表，可以非常容易地制作出進銷存數(shù)據(jù)查詢表格。案例17進銷存數(shù)據(jù)查詢韓小良其他應用圖表制作的注意事項和典型圖表演示宏和VBA簡介Excel報表和圖表與Word/PPT的鏈接韓小良圖表制作的注意事項和典型圖表演示用圖表準確表達出你的觀點繪制圖表基本方法及注意事項圖表的修飾與美化用組合圖表表達更加復雜的信息讓圖表按照您的要求靈活顯示案例18組合圖表演示案例案例19動態(tài)圖表演示案例韓小良宏和VBA簡介什么是宏？什么是VBA？如何學習宏和VBA？宏和VBA的應用：提高數(shù)