電子表格（完美版）課件兩篇

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

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

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

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

選中→鍵入單元格

方向鍵(↓↑→←)

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

→快速移動(dòng)光標(biāo)選中單元格連續(xù)單元格區(qū)域

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

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

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

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

自動(dòng)輸入數(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ù)排序與篩選自定義排序排序條件高級(jí)篩選通配符的使用請(qǐng)使用

若要查找

?（問(wèn)號(hào)）

任何單個(gè)字符

*（星號(hào)）

任何字符數(shù)

~（波形符）后跟

?、*

或

~

問(wèn)號(hào)、星號(hào)或波形符

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

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

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

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

（行標(biāo)和列標(biāo)之前加上$）—$A$1混合引用：行、列引用中一個(gè)是相對(duì)引用，另一個(gè)是絕對(duì)引用

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

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

標(biāo)準(zhǔn)格式：

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

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

圖表可視化-象限圖魔力象限（GartnerMagicQuadrant）使用What-If假設(shè)分析模擬變量表方案管理器使用反轉(zhuǎn)的假設(shè)分析單變量求解規(guī)劃求解使用分析工具庫(kù)方差分析/相關(guān)系數(shù)/協(xié)方差/描述統(tǒng)計(jì)/指數(shù)平滑F(xiàn)-檢驗(yàn)/傅里葉分析/直方圖/移動(dòng)平均/隨機(jī)數(shù)發(fā)生器排位與百分比排位/回歸分析/抽樣分析/t-檢驗(yàn)/z-檢驗(yàn)Excel高級(jí)數(shù)據(jù)分析Excel數(shù)據(jù)分析與圖表可視化EXCEl的使用技巧.新.35EXCEL學(xué)習(xí)介紹EXCEL基本操作EXCEL函數(shù)EXCEL圖表內(nèi)容提綱.新.36.新.37掌握EXCEL菜單命令用法掌握常用快捷鍵EXCEL基本操作.新.38單元格了解

主要了解單元格周圍的小圖標(biāo)鼠標(biāo)放在單元格邊框上可以拖動(dòng)單元格，放在右下角可以向上/下或者左/右復(fù)制單元格，放在右下角按住SHIFT可以插入單元格。EXCEL基本操作.新.39數(shù)據(jù)錄入

1.身份證號(hào)碼輸入（設(shè)置單元格屬性為“文本”或者前面加’）

2.分?jǐn)?shù)輸入（直接輸1/3EXCEL默認(rèn)為1月3日，應(yīng)該寫成0空格1/3）

3.平方、立方的輸入(右鍵選擇單元格格式，選上標(biāo)、下標(biāo))4.單元格里回車（alt+enter）EXCEL基本操作.新.40查找

1.模糊查找按CTRL+F進(jìn)入查找，在查找里直接輸查找內(nèi)容為模糊查找。

2.精確查找在查找里點(diǎn)開選項(xiàng)，將單元格匹配的勾打上為精確查找

3.值查找在查找里點(diǎn)開選項(xiàng)，查找范圍選擇值為值查找。（如果單元格里是公式那必須用值查找）EXCEL基本操作.新.41常用功能

1.凍結(jié)窗口光標(biāo)放在我們需要固定范圍旁邊的單元格上，選擇菜單“窗口”-“凍結(jié)窗口”。

2.排序選擇“數(shù)據(jù)”-“排序”

3.篩選選擇“數(shù)據(jù)”-”篩選“4.插入對(duì)象選擇“插入”-“對(duì)象”

5.下拉菜單制作點(diǎn)到你要制作下拉菜單的單元格，選擇“數(shù)據(jù)”-“有效性”，在允許框中選“序列”，在來(lái)源框中選擇數(shù)據(jù)范圍或者直接輸入值，主要在直接輸入值時(shí)要用“，”隔開。EXCEL基本操作.新.42什么是函數(shù)

Excel函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。以常用的求和函數(shù)SUM為例，它的語(yǔ)法是“SUM(number1,number2,......)”。其中“SUM”稱為函數(shù)名稱，一個(gè)函數(shù)只有唯一的一個(gè)名稱，它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號(hào)，接著是用逗號(hào)分隔的稱為參數(shù)的內(nèi)容，最后用一個(gè)右括號(hào)表示函數(shù)結(jié)束。什么是公式

函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說(shuō)前者是Excel預(yù)先定義好的特殊公式，后者就是由用戶自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的公式。以公式“=SUM(E1:H1)*A1+26”為例，它要以等號(hào)“=”開始，其內(nèi)部可以包括函數(shù)、引用、運(yùn)算符和常量。上式中的“SUM(E1:H1)”是函數(shù)，“A1”則是對(duì)單元格A1的引用(使用其中存儲(chǔ)的數(shù)據(jù))，“26”則是常量，“*”和“+”則是算術(shù)運(yùn)算符。EXCEL函數(shù).新.43基礎(chǔ)概念—引用

1.認(rèn)識(shí)工作表區(qū)域

EXCEL共有256列，65536行。

2.單元格引用方式

相對(duì)應(yīng)用A1、B13.單元格引用分類一維引用A1:A10,A1:F1

二維引用A1:C10

三位引用sheet1：sheet3！A1EXCEL函數(shù).新.44常用函數(shù)

SUM，SUMIF，MAX，COUNT/COUNTA/COUNTIF，ROUND/ROUNDUP/ROUNDDOWN，HEX2DEC/DEC2HEX，LEFT/RIGHT，AVERAGE，VLOOKUP，LEN，MID，SUBSTITUTE，IF，AND/OREXCEL函數(shù).新.45SUM用途：返回某一單元格區(qū)域中所有數(shù)字之和。語(yǔ)法：SUM(number1，number2，...)。參數(shù)：Number1，number2，...為1到N個(gè)需要求和的數(shù)值。用法舉例：EXCEL函數(shù).新.46SUMIF用途：根據(jù)指定條件對(duì)若干單元格、區(qū)域或引用求和。語(yǔ)法：SUMIF(range，criteria，sum_range)參數(shù)：Range為用于條件判斷的單元格區(qū)域，Criteria是由數(shù)字、邏輯表達(dá)式等組成的判定條件，Sum_range為需要求和的單元格、區(qū)域或引用。用法舉例：EXCEL函數(shù).新.47MAX用途：返回?cái)?shù)據(jù)集中的最大數(shù)值。語(yǔ)法：MAX(number1，number2，...)參數(shù)：Number1，number2，...是需要找出最大數(shù)值的1至N個(gè)數(shù)值。用法舉例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，則公式“=MAX(A1:A7)”返回96。EXCEL函數(shù).新.48COUNTIF用途：統(tǒng)計(jì)某一區(qū)域中符合條件的單元格數(shù)目。語(yǔ)法：COUNTIF(range，criteria)參數(shù)：range為需要統(tǒng)計(jì)的符合條件的單元格數(shù)目的區(qū)域；Criteria為參與計(jì)算的單元格條件，其形式可以為數(shù)字、表達(dá)式或文本。其中數(shù)字可以直接寫入，表達(dá)式和文本必須加引號(hào)。用法舉例：EXCEL函數(shù).新.