EXCEL培訓(xùn)課程Excel數(shù)據(jù)分析與圖表可視化培訓(xùn)：

Excel數(shù)據(jù)處理分析數(shù)據(jù)預(yù)處理公式與函數(shù)透視表與分析功能

Excel圖表可視化展現(xiàn)圖表類型選型原則圖表可視化介紹自定義圖表定制主題Excel數(shù)據(jù)分析思路需要分析什么問題希望得到什么結(jié)果結(jié)果用在什么地方學(xué)會排列組合。這個排列組合不是真的排列組合，日常我們在進行數(shù)據(jù)處理時，基本上80%以上的工作只需要20%左右的函數(shù)和方法就可以解決，比如vlookup，sumif，countif，if，條件篩選，排序等等，這也是符合冪律分布的。盡管面臨不同的問題和需求，但是通過公式的嵌套，組合，最后基本上都能解決數(shù)據(jù)處理的分析需求，進一步就是如何優(yōu)化，提高分析的效率Excel數(shù)據(jù)分析思路解決問題獲取答案提出問題數(shù)據(jù)預(yù)處理-基本操作單元格—選擇通過名稱框→地址或名稱選定利用名稱框下拉列表利用定位功能按條件選定開始→查找與選擇→轉(zhuǎn)到(定位條件)單元格—輸入數(shù)據(jù)

①手動輸入數(shù)據(jù)

選中→鍵入單元格

方向鍵(↓↑→←)

Enter(↓)/Tab(→)[Shift+Enter/Tab反向]

→快速移動光標選中單元格連續(xù)單元格區(qū)域

Enter/Tab/Shift+Enter/Shift+Tab→在區(qū)域內(nèi)快速移動選中

×鼠標/方向鍵→取消對區(qū)域的選擇多個單元格/區(qū)域同時輸入相同數(shù)據(jù)

選中目標單元格/區(qū)域→在活動單元格中鍵入數(shù)據(jù)→Ctrl+Enter數(shù)據(jù)預(yù)處理-基本操作Excel數(shù)據(jù)預(yù)處理

Excel圖表可視化展現(xiàn)圖表類型選型原則圖表可視化介紹自定義圖表定制單元格—輸入數(shù)據(jù)

自動輸入數(shù)據(jù)

序列自定義序列數(shù)據(jù)預(yù)處理-基本操作單元格—設(shè)置格式條件格式(基于條件更改單元格區(qū)域的外觀)數(shù)據(jù)預(yù)處理-條件格式

選擇性粘貼數(shù)據(jù)合并轉(zhuǎn)換數(shù)據(jù)行列轉(zhuǎn)置表格同步更新區(qū)域格式復(fù)制數(shù)據(jù)預(yù)處理-選擇性粘貼

Excel數(shù)據(jù)分析–排序與篩選數(shù)據(jù)排序與篩選自定義排序排序條件高級篩選通配符的使用請使用

若要查找

?（問號）

任何單個字符

*（星號）

任何字符數(shù)

~（波形符）后跟

?、*

或

~

問號、星號或波形符

數(shù)據(jù)處理—分類匯總對數(shù)據(jù)按類別進行匯總→統(tǒng)計分析分類匯總之前一定要先進行排序各列須有列標題（字段名）分類匯總類型單字段多字段嵌套匯總數(shù)據(jù)預(yù)處理-分類匯總公式—組成以等號（=）開始引用（單元格引用①/區(qū)域引用②/名稱引用③）運算符④函數(shù)⑤常量(數(shù)字或文本)⑥公式：=表達式=(A3-average)/SUM(A1:A20)+12①③⑤②④⑥數(shù)據(jù)預(yù)處理-公式與函數(shù)公式—使用引用引用：通過指定單元格或區(qū)域的地址/名稱來使用其中的數(shù)據(jù)

→使用數(shù)據(jù)的地址/名稱并非數(shù)據(jù)本身建立數(shù)據(jù)之間的聯(lián)系賦予工作表動態(tài)特性(隨被引單元格的值變化)引用類型：單元格引用單元格區(qū)域引用名稱引用數(shù)據(jù)預(yù)處理-公式與函數(shù)F4鍵→切換引用方式輸入公式時，用方向鍵選取引用單元格編輯公式時，用方向鍵在公式中移動光標移動公式時，引用均不變化，就是原有單元格中的引用地址公式—單元格引用

相對引用：當公式復(fù)制到其他單元格時，行和列引用相應(yīng)改變

（引、被引單元格相對位置不變）—A1絕對引用：當公式復(fù)制到其他單元格時，行和列引用不會改變

（行標和列標之前加上$）—$A$1混合引用：行、列引用中一個是相對引用，另一個是絕對引用

—&A1、A&1數(shù)據(jù)預(yù)處理-公式與函數(shù)3.2公式—計算運算符類別運算符含義示例優(yōu)先級引用運算符:區(qū)域運算，生成對兩個引用之間所有單元格的引用C1:E61[空格]交叉運算，生成對兩個引用中共有單元格的引用A1:C5B2:E92,聯(lián)合運算，合并多個引用為一個引用A1:C5,B2:E93算術(shù)運算符–負號-104%百分比60%5^乘方5^36*/乘、除5*9/37+–加、減7+2–38文本運算符&連接兩/多個文本值合并為一個文本值“3”&“m”→“3m”9比較運算符=><>=<=<>比較兩個值的大小，結(jié)果為邏輯值10<6→FALSE10數(shù)據(jù)預(yù)處理-公式與函數(shù)IF函數(shù)IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。在實踐中，經(jīng)常使用函數(shù)IF對數(shù)值和公式進行條件檢測。公式為=IF(logical_test,value_if_true,value_if_false)數(shù)據(jù)預(yù)處理-公式與函數(shù)COUNT函數(shù)和COUNTIF函數(shù)COUNT函數(shù)的功能是計算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目。公式為=COUNT（參數(shù)1，參數(shù)2，…，參數(shù)N）COUNTIF函數(shù)的功能是計算給定區(qū)域內(nèi)滿足條件的單元格的數(shù)目。公式為=COUNTIF（range，criteria）range需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域；criteria—確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。COUNT函數(shù)和COUNTIF函數(shù)在數(shù)據(jù)匯總統(tǒng)計分析中是非常有用的函數(shù)。數(shù)據(jù)預(yù)處理-公式與函數(shù)Lookup相關(guān)函數(shù)功能

在表格的首列查找指定的數(shù)據(jù)，并返回指定的數(shù)據(jù)所在行中的指定列處的數(shù)據(jù)。語法

標準格式：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)解釋：VLOOKUP(‘你要檢索的內(nèi)容或指定單元格’,‘你要檢索的范圍,檢索到內(nèi)容時返回你檢索表的第幾列中的內(nèi)容’,‘真或假參數(shù)真代表查詢的表已經(jīng)排序，假代表沒有排序)例：VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE)說明：在表SHEET2中檢索當前表中A2中的內(nèi)容，如果檢索到，就返回表SHEET2中B2中的內(nèi)容，因為B2是表SHEET2中的第二列，所以VLOOKUP的第三個參數(shù)，使用2，表示如果滿足條件，就返回查詢表的第二列，最后的參數(shù)FALSE表示‘假’，意思是被查詢的表，沒有排序，這種情況下，會從被查詢的表中第一行開始，一直查詢到結(jié)束。3.5公式錯誤值類型錯誤值類型說明######單元格列寬不足以顯示內(nèi)容，或日期或時間為負值#DIV/0!0作為除數(shù)，或引用空白單元格作為除數(shù)#N/A無信息/數(shù)值可引用#NAME?引用無法識別的文本/不存在的名稱#NULL!指定兩個并不相交區(qū)域的交點#NUM!引用無效的數(shù)值（超限，應(yīng)介于-1*10307~1*10307之間）#REF!引用無效的單元格（被引單元格被刪除或覆蓋）#VALUE!引用不正確的數(shù)據(jù)類型數(shù)據(jù)處理-透視表數(shù)據(jù)透視表是交互式報表，可快速合并和比較大量數(shù)據(jù)。旋轉(zhuǎn)其行和列以看到源數(shù)據(jù)的不同匯總，而且可顯示感興趣區(qū)域的明細數(shù)據(jù)。分析相關(guān)的匯總值，尤其是在要合計較大的列表并對每個數(shù)字進行多種比較時，可以使用數(shù)據(jù)透視表切片與篩選值匯總方式日期，數(shù)值分組Excel圖表可視化展現(xiàn)圖表可視化-類型選擇圖表盡量簡潔明了，突出主題。充分考慮讀者的習(xí)慣，選則更容易被接受和理解的圖表類型，避免過多的解釋。常用圖表的一般用途：柱形圖、條形圖：數(shù)量對比；折線圖、面積圖、柱形圖：反映趨勢；餅圖、堆積百分比柱形/條形圖：反映結(jié)構(gòu)；散點圖、氣泡圖：反映數(shù)據(jù)間的聯(lián)系。不恰當?shù)膱D表類型可能會傳達錯誤信息。案例：用折線圖描繪一組沒有時序關(guān)系的數(shù)據(jù)盡量不要用三維圖形貌似美觀，但沒有傳遞更多的信息，反倒分散了關(guān)注點。維圖無法做組合圖。組合圖可以在一張圖中有效反映關(guān)聯(lián)數(shù)據(jù)，但不可濫用。一張圖中不宜反映太多信息，否則無法聚焦，容易使人迷惑。不同系列之間的關(guān)聯(lián)關(guān)系如果不可靠，圖形反映的將是錯誤信息。

圖表可視化-配色原則Excel圖表的默認配色采用的主題顏色，可以選擇其他主題或自定義主題。無論采用什么配色方案，都應(yīng)保持一致性的延續(xù)性——風(fēng)格化。顏色也可以傳遞信息。要注意配色慣例，如紅、橙、黃通常具有警告含義，可用于異常數(shù)據(jù)的突出顯示，藍色通常代表進展，綠色通常代表成長。避免不協(xié)調(diào)的色彩搭配：紅綠搭配通常會引起不舒服的感覺；低反差的配色不易分辨；各系列均采用濃重的顏色不但無法突出重點，而且容易產(chǎn)生頭重腳輕的感覺。穩(wěn)妥的配色方案：沒有時序關(guān)系的系列采用不同色系搭配；具有時序關(guān)系的系列采用同色系不同明度的方式搭配；盡量不使用太淺的顏色。圖表可視化-配色原則1.遵循你公司的Logo的顏色-為品牌做可視化圖象。2.讓你的內(nèi)容來決定所用的色彩-為市場內(nèi)容的可視化圖象。3.使用季節(jié)或者節(jié)日屬性的色彩-為季度性市場可視化圖象。圖表—組成圖表區(qū)繪圖區(qū)數(shù)據(jù)系列（某一行/列）橫坐標軸（分類軸）縱坐標軸（數(shù)值軸）網(wǎng)格線圖例標題（圖表標題、坐標軸標題）數(shù)據(jù)標簽①②③④⑤⑥⑦⑧⑧⑧⑨圖表可視化圖表可視化-組合圖表在二維圖表中，每一個系列可單獨更改為不同的圖表類型，由此成為組合圖表。圖表中包含多個系列，其中一個系列與其它系列的計量單位不同時，應(yīng)將其放在次坐標軸上，并更改為不同的圖表類型，形成組合圖表。技巧：當要修改的系列與其它系列的值差別特別大，使得圖形緊貼坐標軸時（如百分比），該如何選中這個系列？圖表可視化-折線圖分離單位利用坐標軸的最大值和最小值，將折線圖推到上面，避免覆蓋，使整個圖形更加清晰。圖表可視化-突出重點數(shù)據(jù)在基礎(chǔ)數(shù)據(jù)基礎(chǔ)上增加業(yè)務(wù)標識列在圖表中添加數(shù)據(jù)系列設(shè)置系列的圖表類型為柱狀圖調(diào)整圖表的具體展現(xiàn)格式(間距，填充顏色等)圖表可視化-對稱條形圖成對條形圖（或旋風(fēng)圖）常用于對比兩類事物在不同特征項目的數(shù)據(jù)情況，例如美國總統(tǒng)選舉中不同地區(qū)投支持票和反對票的比例情況在圖表中添加數(shù)據(jù)系列圖表的特點是兩組條形圖的數(shù)據(jù)條沿中間的縱軸分別朝左右兩個方向伸展，通過把兩個數(shù)據(jù)系列分別置于主坐標軸和次坐標軸，然后對其中一個坐標軸逆序顯示并且對稱地設(shè)置主次坐標軸的最大值和最小值即可實現(xiàn)圖表效果。圖表可視化-復(fù)合餅圖與雙層餅圖復(fù)合餅圖和雙層餅圖可以提高小百分比的可讀性或者強調(diào)一組數(shù)值，對多組數(shù)據(jù)或多個系列的數(shù)據(jù)能更好的顯示對比的效果。雙層餅圖經(jīng)常用于表示兩個數(shù)據(jù)系列的比例結(jié)構(gòu)，由大小不同的兩個餅圖疊加在一起組成，一個普通的餅和一個分離的餅，分別位于主坐標軸和次坐標軸，疊加后再調(diào)整各自的大小，以區(qū)分顯示的一種餅圖。這是與復(fù)合餅圖的最大區(qū)別。圖表可視化-象限圖象限圖是利用兩個關(guān)鍵性能指標分別在橫坐標和縱坐標交叉劃分出四個區(qū)域，用于分析和發(fā)現(xiàn)兩個指標的匹配優(yōu)劣程度。魔力象限（GartnerMagicQuadrant）是設(shè)計來監(jiān)控、評估公司在專業(yè)、技術(shù)市場的發(fā)展與位置的研究方法。不管是對尋找適合自身需求公司的投資者還是對那些對比市場競爭者并可能占上風(fēng)的企業(yè)來說，魔力象限創(chuàng)建的報告可能是高度實用的工具。套餐活躍用戶注冊用戶資費套餐23214套餐35132套餐416410套餐5953套餐61167套餐11211117套餐1212125

圖表可視化-象限圖魔力象限（GartnerMagicQuadrant）使用What-If假設(shè)分析模擬變量表方案管理器使用反轉(zhuǎn)的假設(shè)分析單變量求解規(guī)劃求解使用分析工具庫方差分析/相關(guān)系數(shù)/協(xié)方差/描述統(tǒng)計/指數(shù)平滑F(xiàn)-檢驗/傅里葉分析/直方圖/移動平均/隨機數(shù)發(fā)生器排位與百分比排位/回歸分析/抽樣分析/t-檢驗/z-檢驗Excel高級數(shù)據(jù)分析Excel數(shù)據(jù)分析與圖表可視化EXCEL操作培訓(xùn)Excel技巧與操作培訓(xùn)演講人：ONTENTSC目錄Excel操作與技巧2314基礎(chǔ)常識報表制作圖表制作常用技巧基礎(chǔ)常識1Excel基礎(chǔ)常識（1）工作簿（2）工作表（3）單元格工作簿菜單欄單元格，通過位置（列標行標）進行標識。工作表狀態(tài)欄1張工作表（sheet）由65536行*256列，即65536*256=1677萬個單元格組成；1個工作簿（book）最多可含有255張工作表。注：EXCEL2007以下版本。工具欄Excel基礎(chǔ)常識（1）公式（2）函數(shù)公式與函數(shù)公式：在工作表中對數(shù)據(jù)進行計算的式子，它可以對工作表數(shù)值進行加、減、乘、除等運算。函數(shù)：EXCEL預(yù)定義的內(nèi)置程序，可以使用在公式中。公式與函數(shù)數(shù)據(jù)類型（1）數(shù)值型：一般的數(shù)字格式。（2）貨幣型：貨幣格式，對一列數(shù)值進行小數(shù)點對齊。（3）日期時間型：可以對兩個時間段的間隔時長進行計算。（4）文本型：在文本單元格中，數(shù)字、公式作為文本處理。公式與函數(shù)運算符（1）算術(shù)運算：+（加）、-（減）、*（乘）、/（除）、%（百分比）、^（乘方)（2）比較運算：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）（3）字符運算：&（連接）（4）引用運算：（：冒號）、（，逗號）、（空格）要創(chuàng)建一個公式，首先需要選定一個單元格，輸入一個等于號“=”，然后在其后輸入公式的內(nèi)容，按回車鍵就可以按公式計算得出結(jié)果公式與函數(shù)單元格引用引用運算符：（：冒號）、（，逗號）、（空格）（1）：（冒號）——區(qū)域運算符，如B2:F6表示B2單元格到F6單元格矩形區(qū)域內(nèi)的所有單元格。（2），（逗號）——聯(lián)合運算符，將多個引用合并為一個引用，如SUM(B5:B26,D4:D22)，表示B5至B26以及D4至D22所有單元格求和（SUM是求和函數(shù)）（3）（空格）——交叉運算符，如SUM(B5:B26A4:D22)表示求B5至B26與A4至D22兩區(qū)域交叉單元格之和，即B5至B22之和。公式與函數(shù)絕對地址與相對地址相對地址：=B3+B4+B5+B6絕對地址：=$B$3+$B$4+$B$5+$B$6混合運用，F(xiàn)4切換。兩者的差異是：相對地址：如果在A7單元格內(nèi)輸入公式=B3+B4+B5+B6，那么將A7中的公式復(fù)制至A8單元格時，A8的結(jié)果變成=B4+B5+B6+B7，再將A8中的公式復(fù)制至B8時，B8的結(jié)果變成=C4+C5+C6+C7，也就是相對地址在公式復(fù)制時，會隨單元格的不同而發(fā)生變化。絕對地址：在公式復(fù)制時，不會隨單元格的不同而發(fā)生變化。不管復(fù)制到哪個單元格，結(jié)果都是同一個公式，即=$B$3+$B$4+$B$5+$B$6。輸入公式公式的輸入類似于數(shù)據(jù)的輸入，在單元格中輸入公式時以等號（=）作為開始，然后輸入公式表達式。（1）啟動Excel2010，在工作簿中創(chuàng)建工作表。在工作表中選擇需要創(chuàng)建公式的單元格，在單元格中輸入公式，如表1所示。（2）完成公式輸入后，按Enter鍵或單擊其他的單元格，輸入公式的單元格中將顯示計算結(jié)果。輸入公式在工作表中可以將公式復(fù)制到其他的單元格中。（1）將鼠標指針放置到需要復(fù)制的單元格右下角的控制柄上，鼠標指針變?yōu)槭中危醋∈髽俗箧I拖動鼠標至最后一個單元格釋放鼠標，公式將被復(fù)制到鼠標拖動過的單元格中并顯示計算結(jié)果。（2）在工作表中選擇源單元格，打開“開始”選項卡，在“剪貼板”組中單擊“復(fù)制”按鈕。在工作表中選擇目標單元格，單擊“粘貼”按鈕上的下三角按鈕，在下拉列表中“公式”選項，此時選擇的單元格中將能預(yù)覽公式粘貼后的計算結(jié)果。使用函數(shù)向?qū)Р迦牒瘮?shù)使用函數(shù)欄輸入使用公式記憶輸入功能在單元格中輸入函數(shù)的第一個字母時，Excel2010會給出一個以該字母開頭的函數(shù)列表，顯示一個列表，按“↓”鍵在列表中移動光村選擇需要的函數(shù)，按Tab鍵將選擇的函數(shù)輸入到單元格中。函數(shù)功能數(shù)值統(tǒng)計（SUM、SUMIF、COUNT、COUNTIF等）邏輯判斷（AND、IF、ISERROR、NOT、TRUE等）文本處理（MID、MIDB、RIGHT、FIND等）日期時間（NOW、TODAY、TIME、TIMEVALUE等）查找引用（VLOOKUP、ROWS、HYPERLINK等）財務(wù)計算工程分析公式與函數(shù)SUM、SUMPRODUCT、SUMIF、SUMIFS函數(shù)公式與函數(shù)COUNT、COUNTA、COUNTIF、COUNTIFS函數(shù)公式與函數(shù)AND、OR、NOT函數(shù)公式與函數(shù)IF、ISERROR、IFERROR函數(shù)公式與函數(shù)MID、LEFT、RIGHT函數(shù)FIND函數(shù)NOW、TODAY函數(shù)TIME&TIMEVALUE函數(shù)VLOOKUP函數(shù)HYPERLINK函數(shù)注意事項(一)（1）括號（）要對應(yīng)顯示，不可出現(xiàn)單獨的括號，或少括號（2）輸入法的半角全角、大小寫需注意，如：“”不可輸入成“”*不可輸入成×（3）如條件是尋找某個文字：南京，在函數(shù)中應(yīng)輸入為:“南京”如條件是尋找某個單元格或數(shù)字：A5(A列第5行)或5，在函數(shù)中應(yīng)輸入為：A5或5，無需加""符號（4）函數(shù)中設(shè)置的計算范圍需一致（5）需在函數(shù)公式打開狀態(tài)下,同時按下Ctrl+Shift+Enter,當公式頭尾出現(xiàn)花括號｛｝時才算計算完成注意事項(二)（1）源數(shù)據(jù)較大時，應(yīng)將“菜單欄/工具”的重新計算調(diào)整為“手動計算”（2）格式：不得出現(xiàn)空格，如：“黑電”和“黑電”（3）某一字段暫不需/無法統(tǒng)計，如是需量化的單元格，則需以“0”填充，不可寫“無”（4）不可將明細全部刪除后，在原有模板的基礎(chǔ)上重寫貼入明細再次使用，將會改變計算范圍，應(yīng)重新使用新模板（5）注意統(tǒng)計周期的輸入（6）注意EXCEL函數(shù)設(shè)置中某條件的計算列是否是需要的計算列（7）如需從一個EXCEL表格的數(shù)據(jù)復(fù)制到另一表格中，應(yīng)點擊“菜單欄/編輯”的鏈接中“斷開鏈接”報表制作2數(shù)據(jù)排序一次排序最多可以選擇三個關(guān)鍵字：主關(guān)鍵字、次關(guān)鍵字、第三關(guān)鍵字。或者利用工具欄上的兩個排序按鈕。數(shù)據(jù)篩選數(shù)據(jù)透視數(shù)據(jù)透視表實際上是一種交互式表格，能夠方便地對大量數(shù)據(jù)進行快速匯總，并建立交叉表，還能根據(jù)用戶的需要顯示區(qū)域中的細節(jié)數(shù)據(jù)。報表排版報表應(yīng)當含有：1、報表名稱（在表格抬頭，通常字號略大些）2、表頭（在表格主體的頂端與側(cè)端，通常可用顏色著重標注）3、各字段數(shù)據(jù)值（如有文字內(nèi)容應(yīng)相對簡略，避免視覺疲勞）報表需要：1、合適的列寬和行高（鼠標放置需求列或行線的最末端，雙擊即可）2、當表格過長或過寬時，應(yīng)使用“凍結(jié)窗口”功能。圖表制作3圖表制作在處理電子表格時，需要對大量繁瑣的數(shù)據(jù)進行分析和研究，我們需要利用圖形方式再現(xiàn)數(shù)據(jù)變動和發(fā)展趨勢。EXCEL提供了強有力的圖表處理功能，使得用戶很快就可得到所要的圖表。我們?nèi)粘９ぷ骱蜕钪薪?jīng)常使用的圖表類型有：1、柱形圖：比較分析2、折線圖：趨勢分析3、餅圖：占比分析著重標色以柱形圖為例常用技巧4快捷鍵復(fù)制Ctrl+C粘貼