Excel高效數(shù)據(jù)與可視化展示

1、Efficient data collection and visual representation in ExcelExcelExcel 高效數(shù)據(jù)匯總與可視化展示主辦：四川納稅人俱樂(lè)部主講：羅惠民日期：2015-08-23QQ群： 171824249(示例文件密碼）網(wǎng)名：龍逸凡，Excel愛(ài)好者一枚，資深表哥作品：1、“偷懶”的技術(shù)：打造財(cái)務(wù)Excel達(dá)人2、龍逸凡Excel培訓(xùn)手冊(cè)之潛龍?jiān)跍Y3、龍逸凡Excel培訓(xùn)手冊(cè)之飛龍?jiān)谔?、Excel你真會(huì)了嗎？系列文章5、逸凡工作簿合并助手6、逸凡對(duì)賬能手7、逸凡個(gè)稅籌劃助手8、逸凡賬務(wù)系統(tǒng)9、會(huì)計(jì)憑證填制的誤區(qū)、素養(yǎng)與技巧10、財(cái)務(wù)人員不可不

2、知的幾項(xiàng)風(fēng)險(xiǎn)羅惠民資料課前調(diào)查你用過(guò)Excel哪些功能？SUMIF函數(shù)、VLOOKUP函數(shù)、INDEX函數(shù)、INDIRECT函數(shù)、定位、分列、條件格式、數(shù)據(jù)透視表數(shù)組公式、VBA課前問(wèn)題怎樣才能學(xué)好Excel？要有由頭 工作上有需要要想學(xué)好EXCEL吃點(diǎn)苦頭 低效率的加班有了勁頭 學(xué)習(xí)的內(nèi)在動(dòng)力才有奔頭 提高效率的巨大收益嘗點(diǎn)甜頭 提高了效率Excel的主要功能/需求示例01ExcelExcel操作全過(guò)程數(shù)據(jù)的輸入與導(dǎo)入數(shù)據(jù)的展示與匯報(bào)數(shù)據(jù)的加工數(shù)據(jù)的選擇數(shù)據(jù)的整理目錄頁(yè) CONTENTS PAGE 高效選擇數(shù)據(jù)的操作技巧高效選擇數(shù)據(jù)的操作技巧1財(cái)務(wù)經(jīng)典求和技巧與函數(shù)講解財(cái)務(wù)經(jīng)典求和技巧與函數(shù)

3、講解2高效合并數(shù)據(jù)的方法與技巧高效合并數(shù)據(jù)的方法與技巧3表格美化與數(shù)據(jù)可視化展示表格美化與數(shù)據(jù)可視化展示4高效數(shù)據(jù)選擇的操作技巧用鼠標(biāo)高效選擇數(shù)據(jù)的技巧 跳轉(zhuǎn) 選擇 移動(dòng) 插入 填充 刪除示例02高效數(shù)據(jù)選擇的操作技巧用鍵盤高效選擇數(shù)據(jù)的技巧 跳轉(zhuǎn)：Ctrl+方向鍵 擴(kuò)展：Shift+方向鍵 選擇：Ctrl+Shift+箭頭 填充：Ctlr+D，Ctrl+R 批量輸入：Ctrl+Enter F4的作用查找的操作技巧與示例 查找特定格式的單元格 使用查找進(jìn)行刪除（刪除零值、分行符） 定位含有指定字符、特定區(qū)間值的單元格 使用通配符 轉(zhuǎn)置公式 修改公式報(bào)表翻新（財(cái)務(wù)報(bào)表）示例03定位的操作技巧與示

4、例 定位功能解釋 即按條件選擇單元格。 定位應(yīng)用案例1、報(bào)表翻新（定位常量）2、查看本單元格數(shù)據(jù)變化后將影響哪些單元格3、整理合并單元格，等于上面、下面的單元格（定位空值）4、可見(jiàn)單元格，復(fù)制分類匯總篩選結(jié)果5、比對(duì)數(shù)字6、刪除空行（行內(nèi)容差異）、插入空行示例04目錄頁(yè) CONTENTS PAGE 高效選擇數(shù)據(jù)的操作技巧高效選擇數(shù)據(jù)的操作技巧1財(cái)務(wù)經(jīng)典求和技巧與函數(shù)講解財(cái)務(wù)經(jīng)典求和技巧與函數(shù)講解2高效合并數(shù)據(jù)的方法與技巧高效合并數(shù)據(jù)的方法與技巧3表格美化與數(shù)據(jù)可視化展示表格美化與數(shù)據(jù)可視化展示4簡(jiǎn)單求和SUM函數(shù) 簡(jiǎn)單的求和SUM，快捷鍵Alt+，批量輸入求和公式 對(duì)多個(gè)表格的同一單元格區(qū)域求

5、和方法1：=SUM(*!B2) （即：對(duì)除本表之外的其他工作表的B2單元格求和）方法2：在公式編輯欄輸入:sum(,然后選中1月工作表，再按住【Shift】鍵，點(diǎn)選12月工作表，再點(diǎn)選B2單元格，再回車即可。u 要充分理解*和?在工作表名稱中的含義，如果方法1換成=SUM(?!B2)和=SUM(?!B2)會(huì)是什么結(jié)果u 如果表格再調(diào)換一下順序呢？示例06示例05單條件求和SUMIF函數(shù)作用：對(duì)條件區(qū)域進(jìn)行判斷，如果某些單元格滿足指定條件，則對(duì)求和區(qū)域所對(duì)應(yīng)的若干單元格進(jìn)行求和。定位原理：第三參數(shù)單元格區(qū)域起作用的就是左上角那單元格，此單元格的作用是定位定點(diǎn)，只要有此定位點(diǎn)，SUMIF會(huì)自動(dòng)以此

6、單元格為原點(diǎn)，按照第一參數(shù)區(qū)域符合條件的單元格的坐標(biāo)，找到同樣坐標(biāo)位置的單元格，并對(duì)其數(shù)值求和。示例07SUMIF函數(shù)的多條件求和 多條件求和：SUMIFS（見(jiàn)下一節(jié)）、SUMPRODUCT（見(jiàn)下一頁(yè)）=SUM(SUMIF(A2:A11,龍逸凡,羅惠民,B2:B11) =SUM(SUMIF(B2:B10,&EOMONTH(C5,-1),表2日期,=&DATE(YEAR(C5),1,1),表2日期,=&DATE(YEAR(C5),1,1),表2日期,=&C5)示例09財(cái)務(wù)經(jīng)典求和公式 報(bào)表型表格中的求和1、每月數(shù)據(jù)相鄰：求累計(jì)=SUM(OFFSET(D4,0,0,1,MATCH($B$2,$D$

7、3:$O$3,0)2、每隔四列求和：名稱固定：SUMIF名稱不固定：=SUMPRODUCT(B2:F2)*(MOD(COLUMN(B2:F2),2)=0)3、指定區(qū)間求和：=SUMIF(INDIRECT(R3C6:R3C&$B$2*3+5,0),*預(yù)算,INDIRECT(RC6:RC&$B$2*3+5,0)4、僅對(duì)篩選出的行求和=SUBTOTAL(9,D5:D75)示例10示例17 OFFSET函數(shù)目錄頁(yè) CONTENTS PAGE 高效選擇數(shù)據(jù)的操作技巧高效選擇數(shù)據(jù)的操作技巧1財(cái)務(wù)經(jīng)典求和技巧與函數(shù)講解財(cái)務(wù)經(jīng)典求和技巧與函數(shù)講解2高效合并數(shù)據(jù)的方法與技巧高效合并數(shù)據(jù)的方法與技巧3表格美化與數(shù)

8、據(jù)可視化展示表格美化與數(shù)據(jù)可視化展示4課前問(wèn)題怎樣才算精通Excel？數(shù)據(jù)透視表數(shù)據(jù)透視表應(yīng)用講解應(yīng)用講解 應(yīng)用舉例：用透視表作本年累計(jì)、同比增長(zhǎng)、環(huán)比增長(zhǎng)分析方法：分析項(xiàng)目分析項(xiàng)目顯示方式顯示方式基本字段基本字段基本項(xiàng)基本項(xiàng)本年累計(jì)按某一字段匯總訂單日期同比增長(zhǎng)差異百分比年上一個(gè)環(huán)比增長(zhǎng)差異百分比訂單日期上一個(gè)快速制表、匯總的前提與思路1、前提：表格布局、文件名稱、項(xiàng)目名稱要統(tǒng)一；數(shù)據(jù)規(guī)范。2、保障手段：數(shù)據(jù)有效性、保護(hù)工作表/工作簿3、思路： 充分利用已有的報(bào)表，利用已有的報(bào)表布局及公式：刪除數(shù)值保留公式利用已有報(bào)表的文件夾和表格布局：快速翻新公式 使用INDIRECT函數(shù)，提高表格的設(shè)計(jì)

9、效率 使用查找替換批量修改公式，提高表格設(shè)計(jì)效率一、同一工作簿多工作表的匯總1、工作表結(jié)構(gòu)布局相同 不需列示分項(xiàng)數(shù)據(jù)：示例06（略） 需要列示分項(xiàng)數(shù)據(jù)： 查找替換法、使用INDIRECT2、工作表結(jié)構(gòu)布局不同使用VLOOKUP、INDIRECT使用合并計(jì)算，參見(jiàn)后一節(jié)示例12示例11二、不同工作簿多工作表的匯總（一）、需要列示分項(xiàng)數(shù)據(jù)的匯總1、使用查找替換快速修改引用公式（報(bào)表翻新）示例參見(jiàn)財(cái)務(wù)報(bào)表文件夾2、使用INDIRECT函數(shù)實(shí)現(xiàn)快速引用（略）注意：INDIRECT引用其他工作簿，該表必須處理打開(kāi)狀態(tài)，否則 INDIRECT會(huì) 返回 #REF! 錯(cuò)誤財(cái)務(wù)報(bào)表二、不同工作簿多工作表的匯總（

10、二）、僅需列示匯總結(jié)果1、使用合并計(jì)算進(jìn)行跨工作簿匯總2、使用數(shù)據(jù)透視表進(jìn)行跨工作簿匯總（略）合并計(jì)算二、不同工作簿多工作表的匯總（三）、集團(tuán)公司如何快速匯總下屬公司報(bào)表？設(shè)計(jì)總體要求：作為集團(tuán)的財(cái)務(wù)人員一定要有服務(wù)的理念，在設(shè)計(jì)表格時(shí)，除了要便于集團(tuán)公司匯總，還要考慮下屬礦井填列數(shù)據(jù)是否方便。匯總表格要使用公式根據(jù)下屬公司填報(bào)的數(shù)據(jù)自動(dòng)匯總，不必手工填列。集團(tuán)公司設(shè)計(jì)的表格不合理，下屬公司應(yīng)怎么解決不方便填列的問(wèn)題？工具方法：參見(jiàn)前述方法營(yíng)運(yùn)周報(bào) 目錄頁(yè) CONTENTS PAGE 高效選擇數(shù)據(jù)的操作技巧高效選擇數(shù)據(jù)的操作技巧1財(cái)務(wù)經(jīng)典求和技巧與函數(shù)講解財(cái)務(wù)經(jīng)典求和技巧與函數(shù)講解2表格美化與

11、數(shù)據(jù)可視化展示表格美化與數(shù)據(jù)可視化展示4高效合并數(shù)據(jù)的方法與技巧高效合并數(shù)據(jù)的方法與技巧3一、錦上添花表格的美化（一）、表格外觀設(shè)計(jì)的常見(jiàn)表格外觀設(shè)計(jì)的常見(jiàn)壞習(xí)慣示例13 一、錦上添花表格的美化（二）、表格美化的必要性表格美化的必要性你提交的報(bào)表有沒(méi)有美化過(guò)？你認(rèn)為表格美化有無(wú)必要？1、讓表格變得更美觀；2、讓表格更方便閱讀；3、體現(xiàn)自己的專業(yè)素養(yǎng)；一、錦上添花表格的美化（三）、表格美化的方法1、正確布局讓邏輯更合理 注意數(shù)據(jù)的邏輯性； 注意數(shù)據(jù)記錄有無(wú)排序要求； 注意版式的選擇； 注意合計(jì)行/列的位置一、錦上添花表格的美化2、去粕降噪讓版面更清爽僅在表格區(qū)域設(shè)置單元格邊框和填充色，刪除表格之

12、外單元格的內(nèi)容和格式；盡量少用批注，如果必須使用批注，至少要做到不遮擋其他數(shù)據(jù)。也可以吸取國(guó)外著名財(cái)經(jīng)雜志的經(jīng)驗(yàn)，需要批注的加*號(hào)標(biāo)注，然后在表格末尾備注；公式檢驗(yàn)完后應(yīng)使用IF、ISNA、IFERROR來(lái)消除錯(cuò)誤值，比如=IF(ISERROR(A1/B1),0, A1/B1)、=IFERROR(A1/B1,0)， 公式未校驗(yàn)時(shí)最好不要先消除錯(cuò)誤值。隱藏或刪除零值或顯示成小短橫線。一、錦上添花表格的美化3、層次分明讓結(jié)構(gòu)更清晰 將同一類記錄歸組在一起；并將不同類別的記錄或字段增加間距、拉開(kāi)距離，同時(shí)將標(biāo)題與數(shù)據(jù)間的間距拉開(kāi)。一、錦上添花表格的美化3、層次分明讓結(jié)構(gòu)更清晰 縮進(jìn)：明細(xì)級(jí)的項(xiàng)目應(yīng)該

13、與上一層級(jí)保持一定的縮進(jìn)，以凸顯各項(xiàng)目的邏輯層次。同級(jí)的項(xiàng)目保持同樣的縮進(jìn)距離。 邊框：用添加邊框線條與否或其粗細(xì)來(lái)區(qū)分?jǐn)?shù)據(jù)的層級(jí)：可只在重要層級(jí)的數(shù)據(jù)上設(shè)置邊框或加粗。同一層級(jí)應(yīng)使用同一粗細(xì)的線條邊框。如果不是出于以下四個(gè)目的之一就不必使用邊框：結(jié)構(gòu)化表格、引導(dǎo)閱讀、強(qiáng)調(diào)突出數(shù)據(jù)、美化表格。一、錦上添花表格的美化3、層次分明讓結(jié)構(gòu)更清晰 字號(hào)：使用不同粗細(xì)的字號(hào)來(lái)體現(xiàn)數(shù)據(jù)的層級(jí)，與邊框同理， 填充色：為不同層次的數(shù)據(jù)設(shè)置不同的填充色，填充色不能太暗也不能太亮，顏色要與字體顏色相協(xié)調(diào)。填充色種類不能太多，多了顯得花哨。一、錦上添花表格的美化4、鶴立雞群讓重點(diǎn)更突出對(duì)需要強(qiáng)調(diào)的重點(diǎn)數(shù)據(jù)，施以特別

14、的字體顏色、添加不同的單元格底色、加大加粗的字體、較粗的邊框、加圖形標(biāo)注等。在顏色運(yùn)用上可使用表格主色的互補(bǔ)色進(jìn)行強(qiáng)調(diào)。一、錦上添花表格的美化5、格式得體讓表格更美觀字體要與表格用途協(xié)調(diào)、要與其他字體風(fēng)格、大小相協(xié)調(diào)，顏色表格顏色不能太多，最好不要超過(guò)三種；如果要選用多種顏色，盡量使用類似色，可避免表格色彩雜亂?；蛘哌x定一種顏色，然后搭配此種顏色系列不同亮度的顏色（變淡或加深），這樣的搭配，既可實(shí)現(xiàn)顏色的統(tǒng)一，也可體現(xiàn)層次感。造型可以添加簡(jiǎn)單的邊框或單元格底色進(jìn)行美化，還可以將邊框和底色相結(jié)合進(jìn)行造型。示例14 二、錦上添花數(shù)據(jù)可視化展示的方法與技巧（一）、數(shù)據(jù)的顯示目標(biāo)：條理清晰、重點(diǎn)突出、

15、方便閱讀、智能化 條理清晰：參見(jiàn)報(bào)表的美化 重點(diǎn)突出：?jiǎn)卧駱邮?方便閱讀：凍結(jié)窗格（如何保持底部合計(jì)行不動(dòng)？）分組顯示照相機(jī) 智能化：自定義格式、條件格式示例15 示例16示例17二、錦上添花數(shù)據(jù)可視化展示的方法與技巧（二）、報(bào)表的展示目標(biāo)：高端大氣上檔次方法： 鏈接 報(bào)表美化（已介紹） 分級(jí)顯示（已介紹） 迷你圖 切片器 日程管理器示例18 示例20示例18示例19 示例21示例22二、錦上添花數(shù)據(jù)可視化展示的方法與技巧（三）、一圖勝千言用圖表說(shuō)話方法： 迷你圖 財(cái)務(wù)經(jīng)典圖表 交互圖表 儀表盤 Power View二、錦上添花數(shù)據(jù)可視化展示的方法與技巧（三）、一圖勝千言用圖表說(shuō)話財(cái)務(wù)經(jīng)典圖

16、表： 發(fā)展趨勢(shì)分析經(jīng)典圖表 對(duì)比分析經(jīng)典圖表 組成結(jié)構(gòu)分析經(jīng)典圖表 達(dá)成及進(jìn)度分析經(jīng)典圖表 影響因素分析經(jīng)典圖表 財(cái)務(wù)管理分析經(jīng)典圖表本量利分析圖二、錦上添花數(shù)據(jù)可視化展示的方法與技巧 發(fā)展趨勢(shì)分析經(jīng)典圖表示例23 二、錦上添花數(shù)據(jù)可視化展示的方法與技巧 對(duì)比分析經(jīng)典圖表示例24 二、錦上添花數(shù)據(jù)可視化展示的方法與技巧 對(duì)比分析經(jīng)典圖表二、錦上添花數(shù)據(jù)可視化展示的方法與技巧 對(duì)比分析經(jīng)典圖表二、錦上添花數(shù)據(jù)可視化展示的方法與技巧 組成結(jié)構(gòu)分析經(jīng)典圖表示例25 Global investment banks,share of revenue by region,%Source:DealogicAmericasAsia PacificEurope