EXCEL培訓課程Excel數據分析與圖表可視化培訓:

Excel數據處理分析數據預處理公式與函數透視表與分析功能

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

①手動輸入數據

選中→鍵入單元格

方向鍵(↓↑→←)

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

→快速移動光標選中單元格連續單元格區域

Enter/Tab/Shift+Enter/Shift+Tab→在區域內快速移動選中

×鼠標/方向鍵→取消對區域de選擇多個單元格/區域同時輸入相同數據

選中目標單元格/區域→在活動單元格中鍵入數據→Ctrl+Enter數據預處理-基本操作Excel數據預處理

Excel圖表可視化展現圖表類型選型原則圖表可視化介紹自定義圖表定制單元格—輸入數據

自動輸入數據

序列自定義序列數據預處理-基本操作單元格—設置格式條件格式(基于條件更改單元格區域de外觀)數據預處理-條件格式

選擇性粘貼數據合并轉換數據行列轉置表格同步更新區域格式復制數據預處理-選擇性粘貼

Excel數據分析–排序與篩選數據排序與篩選自定義排序排序條件高級篩選通配符de使用請使用

若要查找

?（問號）

任何單個字符

*（星號）

任何字符數

~（波形符）后跟

?、*

或

~

問號、星號或波形符

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

→使用數據de地址/名稱并非數據本身建立數據之間de聯系賦予工作表動態特性(隨被引單元格de值變化)引用類型:單元格引用單元格區域引用名稱引用數據預處理-公式與函數F四鍵→切換引用方式輸入公式時,用方向鍵選取引用單元格編輯公式時,用方向鍵在公式中移動光標移動公式時,引用均不變化,就是原有單元格中de引用地址公式—單元格引用

相對引用:當公式復制到其他單元格時,行和列引用相應改變

（引、被引單元格相對位置不變）—A一絕對引用:當公式復制到其他單元格時,行和列引用不會改變

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

—&A一、A&一數據預處理-公式與函數三.二公式—計算運算符類別運算符含義示例優先級引用運算符:區域運算,生成對兩個引用之間所有單元格de引用C一:E六一[空格]交叉運算,生成對兩個引用中共有單元格de引用A一:C五B二:E九二,聯合運算,合并多個引用為一個引用A一:C五,B二:E九三算術運算符–負號-一零四%百分比六零%五^乘方五^三六*/乘、除五*九/三七+–加、減七+二–三八文本運算符&連接兩/多個文本值合并為一個文本值“三”&“m”→“三m”九比較運算符=><>=<=<>比較兩個值de大小,結果為邏輯值一零<六→FALSE一零數據預處理-公式與函數IF函數IF函數也稱條件函數,它根據參數條件de真假,返回不同de結果.在實踐中,經常使用函數IF對數值和公式進行條件檢測.公式為=IF(logical_test,value_if_true,value_if_false)數據預處理-公式與函數COUNT函數和COUNTIF函數COUNT函數de功能是計算給定區域內數值型參數de數目.公式為=COUNT（參數一,參數二,…,參數N）COUNTIF函數de功能是計算給定區域內滿足條件de單元格de數目.公式為=COUNTIF（range,criteria）range需要計算其中滿足條件de單元格數目de單元格區域；criteria—確定哪些單元格將被計算在內de條件,其形式可以為數字、表達式或文本.COUNT函數和COUNTIF函數在數據匯總統計分析中是非常有用de函數.數據預處理-公式與函數Lookup相關函數功能

在表格de首列查找指定de數據,并返回指定de數據所在行中de指定列處de數據.語法

標準格式:

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

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

圖表可視化-象限圖魔力象限（GartnerMagicQuadrant）使用What-If假設分析模擬變量表方案管理器使用反轉de假設分析單變量求解規劃求解使用分析工具庫方差分析/相關系數/協方差/描述統計/指數平滑F-檢驗/傅里葉分析/直方圖/移動平均/隨機數發生器排位與百分比排位/回歸分析/抽樣分析/t-檢驗/z-檢驗Excel高級數據分析Excel數據分析與圖表可視化Excel數據

透視表教程演講人:什么叫數據透視表

您de工作表含有大量數據,但是您知道這些數字de含義嗎?這些數據能夠解答您de問題嗎?如左圖,數據源表是各商店每日銷售de明細表.你能迅速知道各品類de銷售情況嗎?哪個店鋪de銷售最好?您可以使用數據透視表.它們能夠將數據轉換為短小簡潔de報表,并準確地告訴您需要了解de內容.什么叫數據透視表

數據透視表是對工作表數據de重新組合,它通過組合、計數、分類匯總、排序等方式從大量數據中提取總結性信息,用以制作各種分析報表和統計報表.準備數據源表一.報表字段dede名稱來自源數據中de列標題.因此,源數據中工作表第一行各列都需要有名稱.名稱必須具有唯一性,而且數據源表de表頭是沒有合并單元格形式de表頭.二.刪除數據源中de空行.三.刪除數據源中de合計項.準備數據源表

在開始使用透視數據表之前,請觀察一下您deExcel工作表,以確保它為報表做好了準備.創建數據透視表步驟一一.準備好數據源后,單擊數據中de任意位置.二.在“插入”功能選項卡中de“表”功能組中,單擊“數據透視表”,然后再次單擊“數據透視表”.彈出“創建數據透視表”對話框.此處介紹如何開始使用數據透視表.您可以使用此處顯示de“創建數據透視表”對話框.創建數據透視表步驟二三.已選中“選擇一個表或區域”選項.“表/區域”框顯示所選數據de范圍,該范圍可以根據需要修改.四.“選擇放置數據透視表de位置”,默認選中“新工作表”,也可選擇“現有工作表”,在“位置”框中選擇需要存放de位置.五.單擊“確定”按鈕.此處介紹如何開始使用數據透視表.您可以使用此處顯示de“創建數據透視表”對話框.創建數據透視表步驟三這是在關閉“創建數據透視表”對話框后,在新工作表中看到de容.其中一側是為數據透視表準備de布局區域.另一側是“數據透視表字段列表”.該列表顯示來自源數據de列標題.如前所述,每個標題都是一個字段.創建數據透視表步驟四六.在“數據透視表字段列表”中選中字段名稱旁邊de復選框或者右鍵單擊字段名稱,然后選擇要將該字段移動到de位置.通過將“數據透視表字段列表”中顯示de任何字段移動到布局區域,可創建數據透視表.數據透視表de結構從結構上看,數據透視表分四部分構成.行區域:此區域中de字段作為數據透視表de行字段.列區域:此區域中de字段作為數據透視表de列字段.數據區域:顯示匯總de數據.報表篩選區域:此區域中de字段作為數據透視表de頁字段.數據透視表字段列表de結構“數據透視表字段列表”對話框中也能清晰地反映數據透視表de結構,利用它,用戶可以向數據透視表內添加、刪除、移動字段,設置字段格式,并對數據透視表中de字段進行排序和篩選.打開“數據透視表字段列表”對話框de方法:在數據透視表de任意單元格單擊鼠標右鍵,在彈出de快捷菜單中選擇“顯示字段列表”命令.啟用EXCEL二零零三經典數據透視表一.在數據透視表任意單元格單擊鼠標右鍵,二.選擇“數據透視表選項”命令,彈出“數據透視表選項”對話框,三.在“數據透視表選項”對話框中,單擊“顯示”選項卡,自EXCEL二零零七版本起,數據透視表工具與之前二零零三版本發生了很大變化,如果用戶希望運用EXCEL二零零三版de拖拽方式操作數據透視表,可以啟用EXCEL二零零三經典數據透視表布局.啟用EXCEL二零零三經典數據透視表四.勾選“經典數據透視表布局（啟用網格中de字段拖放）”復選框,五.單擊“確定”按鈕.自EXCEL二零零七版本起,數據透視表工具與之前二零零三版本發生了很大變化,如果用戶希望運用EXCEL二零零三版de拖拽方式操作數據透視表,可以啟用EXCEL二零零三經典數據透視表布局.重命名字段一.單擊數據透視表中列字段de標題單元格.二.在“編輯欄”中輸入新標題,按回車鍵結束.當用戶向數值區域添加字段后,它們都會被命名,如“銷售數量”變成了“求和項:銷售數量”,這樣加大了字段de列寬,影響美觀,我們可以重新命名字段,讓標題更簡潔.刪除字段一.在“數據透視表字段列表”對話框中,點擊需要刪除de字段,二.在彈出de快捷菜單中選擇“刪除字段”命令.數據透視創建以后,如果用戶在進行數據分析時,可以對數據透視表不需要顯示de字段可以進行刪除.改變數據透視表de報表布局一.點擊數據透視表de任意單元格.二.單擊“數據透視表工具”項下de“設計”選項卡de“報表布局”按鈕.三.在下拉菜單中選擇“以表格形式顯示”命令.數據透視表提供了“以壓縮形式顯示”,“以大綱形式顯示”、“以表格形式顯示”三種報表布局de顯示方式.新創建de數據透視表顯示形式都是默認de“以壓縮形式顯示”,行字段都堆積在一起,不符合我們de閱讀習慣.以表格形式顯示de數據透視表顯示直觀,方便閱讀,是用戶首選de顯示方式.改變數據透視表de報表布局更改分類匯總de顯示方式一.點擊數據透視表de任意單元格.二.單擊“數據透視表工具”項下de“設計”選項卡de“分類匯總”按鈕.三.在下拉菜單中選擇“不顯示分類匯總”命令.用戶可根據實際需求,顯示或刪除字段de分類匯總.總計de禁用和啟用一.點擊數據透視表de任意單元格.二.單擊“數據透視表工具”項下de“設計”選項卡de“總計”按鈕.三.在下拉菜單中選擇“對行和列禁用”命令.用戶同樣可以設置顯示或刪除數據透視表de行和列de總計項.活動字段de折疊與展開一.單擊活動字段de隨意某個單元格,二.在數據透視表工具項下de選項選項卡中單擊活動字段組中de折疊整個字段.折疊和展開字段可以使用戶在不同場合顯示和隱藏一些數據信息.合并且居中排列帶標簽de單元格一.在數據透視表任意單元格單擊鼠標右鍵,二.選擇“數據透視表選項”命令,彈出“數據透視表選項”對話框,數據透視表“合并居中”de布局方式簡單明了,也符合讀者de閱讀方式.合并且居中排列帶標簽de單元格三.在“數據透視表選項”對話框中,單擊“布局和格式”選項卡,四.勾選“合并且居中排列帶標簽de單元格”復選框,五.單擊“確定”按鈕.數據透視表“合并居中”de布局方式簡單明了,也符合讀者de閱讀方式.數據透視表de排序一.單擊數據透視表中需要排序字段de下拉箭頭,二.在彈出de下拉菜單中選擇“升序”或“降序“命令.數據透視表可以實現在普通數據表中de排序效果.數據透視表de篩選一.單擊數據透視表中需要篩選字段de下拉箭頭,二.在彈出de下拉菜單中取消對不要顯示字段項de復選框勾選,三.單擊“確定”按鈕.數據透視表同樣可以實現在普通數據表中de篩選效果.組合一.選中數據透視表中需要組合de單元格區域,二.在“數據透視表工具”項下de“選項”選項卡中單擊“將所選內容分組”按鈕.默認顯示分組de標題名稱為“數據組一”.組合可以對數字、日期和文本等不同類型de數據項采取多種組合方式,達到增強了數據透視表分類匯總de功能性.組合三.單擊“數據組一”單元格,在編輯欄中,可以更改名稱,然后按回車鍵結束.四.重復以上步驟,對其他區域進行相應分組.組合可以對數字、日期和文本等不同類型de數據項采取多種組合方式,達到增強了數據透視表分類匯總de功能性.更改字段de匯總方式一.在數據透視表de數值區域中de任意單元格單擊鼠標右鍵,二.在彈出de快捷菜單中選擇“值字段設置”命令,彈出“值字段設置”對話框.默認狀態下,數據透視表對數值區域中de數值字段使用求和方式匯總,對非數值字段使用計數方式匯總.除此之外,數據透視表還提供了“平均值”,“最大值”,“最小值”等其他多種匯總方式.更改字段de匯總方式三.在“匯總方式”選項卡下de“計算類型”下de選擇框下選擇要采用de匯總方式,四.單擊“確定”按鈕.默認狀態下,數據透視表對數值區域中de數值字段使用求和方式匯總,對非數值字段使用計數方式匯總.除此之外,數據透視表還提供了“平均值”,“最大值”,“最小值”等其他多種匯總方式.更改值顯示方式一.在數據透視表de數值區域中de任意單元格單擊鼠標右鍵,二.在彈出de快捷菜單中選擇“值字段設置”命令,彈出“值字段設置”對話框.如果值字段設置對話框內de匯總方式仍然不能滿足用戶de分析需求,EXCEL提供了更多de字段值顯示方式,利用此功能,可以顯示數據透視表de數值區域中每項占同行貨同列數據總和de百分比,或顯示每個數值占總和de百分比等.更改值顯示方式三.在“值字段設置”對話框中,單擊“值顯示方式”選項卡四.在“值顯示方式”選項卡下選擇框下選擇要采用de值顯示方式,五.單擊“確定”按鈕.如果值字段設置對話框內de匯總方式仍然不能滿足用戶de分析需求,EXCEL提供了更多de字段值顯示方式,利用此功能,可以顯示數據透視表de數值區域中每項占同行貨同列數據總和de百分比,或顯示每個數值占總和de百分比等.同一字段使用多種匯總方式一.在“數據透視表字段列表”中,將需要使用多種匯總方式de字段多次添加到“∑數值”區域中.二.再更改同一字段de匯總方式.用戶可對數值區域中de同一字段同時使用多種匯總方式.計算字段一.單擊數據透視表任意單元格,二.在“數據透視表工具”項下de“選項”選項