Excel進(jìn)階培訓(xùn)數(shù)據(jù)分析篇(共43張)(PPT 43頁(yè))

1、Excel高級(jí)培訓(xùn)：數(shù)據(jù)分析信息技術(shù)部 辦公軟件技能培訓(xùn)系列第1頁(yè)，共43頁(yè)。2本課程您將學(xué)到什么？這是一個(gè)面向進(jìn)階技巧的培訓(xùn)掌握一些必要的excel進(jìn)階函數(shù)理解并掌握數(shù)據(jù)立方體的概念通過(guò)Excel的數(shù)據(jù)透視分析的練習(xí)，熟悉數(shù)據(jù)立方體的一般形式提高數(shù)據(jù)分析工作的技能做好準(zhǔn)備，應(yīng)用更高級(jí)的商業(yè)智能工具第2頁(yè)，共43頁(yè)。Excel進(jìn)階函數(shù)第3頁(yè)，共43頁(yè)。4第4頁(yè)，共43頁(yè)。5一些有用的進(jìn)階函數(shù)數(shù)據(jù)引用AddressIndirectoffset數(shù)據(jù)查找與關(guān)聯(lián)LookupVlookupHlookup統(tǒng)計(jì)Count,CountifSum,SumifAVERAGE, AVERAGEAMax,MinMed

2、ian,ModeFrequencyRank,PercentRank邏輯函數(shù)AndOrNot第5頁(yè)，共43頁(yè)。Indirect功能按給定的文本形式的地址，來(lái)引用內(nèi)容。由于地址是文本形式的，這意味著它可以用公式來(lái)“合成”。示例：=Indirect(“1月數(shù)據(jù)!A1”)引用了“1月數(shù)據(jù)”工作表里的A1單格內(nèi)容=SUM(INDIRECT(統(tǒng)計(jì)函數(shù)!a1:a10)合計(jì)了“統(tǒng)計(jì)函數(shù)”表中的A1到A10的值。6第6頁(yè)，共43頁(yè)。7lookup功能函數(shù) LOOKUP 在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值。如果需要指定包含待查找數(shù)值的區(qū)域，則可以使用函數(shù) LO

3、OKUP的這種形式。函數(shù)LOOKUP的另一種形式為自動(dòng)在第一列或第一行中查找數(shù)值。第7頁(yè)，共43頁(yè)。8lookup語(yǔ)法 1lookup(, , )參數(shù)要找的內(nèi)容 指定函數(shù) LOOKUP要在哪里查找。必須按升序排序.在哪里找 指定要查找的內(nèi)容，應(yīng)該是一個(gè)單一值或是單一單元格。結(jié)果在哪里 指定結(jié)果位于的區(qū)域，其大小必須與 相同。注意：如果函數(shù)lookup找不到，則查找中小于或等于的最大數(shù)值。第8頁(yè)，共43頁(yè)。9lookupAB1產(chǎn)品銷(xiāo)售2桔子1.53蘋(píng)果2.94西瓜2.15草莓0.7公式說(shuō)明（結(jié)果）=LOOKUP(“蘋(píng)果”, A2:A5, B2:B5)在 A 列中查找蘋(píng)果，并返回同一行 B 列的銷(xiāo)

4、售值 (2.9)第9頁(yè)，共43頁(yè)。10Vlookup功能在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值語(yǔ)法VLOOKUP(,)第10頁(yè)，共43頁(yè)。11Vlookup參數(shù) 需要在數(shù)組第一列中查找的數(shù)值。 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫(kù)或列表。 為 中待返回的匹配值的列序號(hào)。從開(kāi)始。 為一邏輯值，指明是精確匹配，還是近似匹配。 輸入“FALSE”將查找精確匹配值。第11頁(yè)，共43頁(yè)。12Vlookup(2)AB1產(chǎn)品銷(xiāo)售量2桔子1.53蘋(píng)果2.94西瓜2.15草莓0.7公式說(shuō)明（結(jié)果）=VLOOKUP(“蘋(píng)果”,A2:B6

5、,2)在 A2:B6區(qū)域中的第一列中查找蘋(píng)果，并返回同一區(qū)域?qū)?yīng)第2列的銷(xiāo)售值 (2.9)第12頁(yè)，共43頁(yè)。13Hlookup說(shuō)明在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值語(yǔ)法HLOOKUP(,)第13頁(yè)，共43頁(yè)。14Hlookup參數(shù) 需要在數(shù)組第一列中查找的數(shù)值。 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫(kù)或列表。 為 中待返回的匹配值的行序號(hào)。從開(kāi)始。 為一邏輯值，指明是精確匹配，還是近似匹配。 FALSE將查找精確匹配值。第14頁(yè)，共43頁(yè)。15HLookupABCD1桔子蘋(píng)果西瓜草莓21.52.92.10.7公式

6、說(shuō)明（結(jié)果）=HLOOKUP(“蘋(píng)果”,A1:D2,2)在 A1:D2區(qū)域中的第一行中查找蘋(píng)果，并返回同一區(qū)域?qū)?yīng)第2行的銷(xiāo)售值 (2.9)第15頁(yè)，共43頁(yè)。16Countif功能計(jì)數(shù)符合條件的那些記錄語(yǔ)法COUNTIF(,)參數(shù) 為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。為確定哪些單元格將被計(jì)算在內(nèi)的條件。例如，條件可以表示為 32、32、32 或 apples。第16頁(yè)，共43頁(yè)。17Countif(2)AB1數(shù)據(jù)數(shù)據(jù)2apples323oranges544peaches755apples86公式說(shuō)明（結(jié)果）=COUNTIF(A2:A5,apples)計(jì)算第一列中蘋(píng)果所在單元格的個(gè)

7、數(shù) (2)第17頁(yè)，共43頁(yè)。18sumif功能對(duì)符合條件的那些記錄的指定列求和語(yǔ)法SUMIF(, , )第18頁(yè)，共43頁(yè)。19sumif參數(shù) 為用于條件判斷的單元格區(qū)域。 為確定哪些單元格將被相加求和的條件。例如，條件可以表示為 32、32、32 或 apples。 是需要求和的實(shí)際單元格。說(shuō)明只有在區(qū)域中相應(yīng)的單元格符合條件的情況下， 中的單元格才求和。如果忽略了 ，則對(duì)中的單元格求和。第19頁(yè)，共43頁(yè)。20sumif (2)AB1屬性值傭金2100,0007,0003200,00014,0004300,00021,0005400,00028,000公式說(shuō)明（結(jié)果）=SUMIF(A2:

8、A5,“160000”, B2:B5)屬性值超過(guò) 160,000 的傭金的和 (63,000)第20頁(yè)，共43頁(yè)。21第21頁(yè)，共43頁(yè)。22邏輯函數(shù)And：與Or：或Not：非條件1真真假假條件2真假真假and真假假假or真真真假第22頁(yè)，共43頁(yè)。23條件結(jié)果函數(shù)ifIf(邏輯值, 結(jié)果1, 結(jié)果2)如果邏輯值為真，則公式返回結(jié)果1如果邏輯值為假，則公式返回結(jié)果2第23頁(yè)，共43頁(yè)。24邏輯計(jì)算例子判斷消費(fèi)是否在10到20之間，或者已經(jīng)特別要求進(jìn)行提醒，這些客戶要求進(jìn)行短信提醒：=if(or( and(B2=10, B2=20), C2=“是“), “需要提醒”, “不要提醒”)ABCD1

9、姓名消費(fèi)要求提醒是否提醒2張三13否3李四5是第24頁(yè)，共43頁(yè)。25請(qǐng)善用Excel的幫助自學(xué)是最好的老師.Excel的幫助包括了所有的函數(shù)用法, 示例等資料, 是最好的Excel學(xué)習(xí)材料.第25頁(yè)，共43頁(yè)。數(shù)據(jù)分析部分第26頁(yè)，共43頁(yè)。27數(shù)據(jù)分析基礎(chǔ)知識(shí)數(shù)據(jù)挖掘數(shù)據(jù)挖掘（Data Mining，DM）是指從大量不完全的、有噪聲的、模糊的、隨機(jī)的數(shù)據(jù)中，提取隱含在其中的、有用的信息和知識(shí)的過(guò)程?！皵?shù)據(jù)立方體”是一個(gè)常用的數(shù)據(jù)挖掘分析概念.第27頁(yè)，共43頁(yè)。28數(shù)據(jù)分析基礎(chǔ)知識(shí)數(shù)據(jù)立方體概念幾何立方體多維幾何立方體數(shù)據(jù)立方體透視表第28頁(yè)，共43頁(yè)。29三維幾何立方體三個(gè)維度：長(zhǎng)寬高一

10、個(gè)被上述維度限定了位置的點(diǎn)O第29頁(yè)，共43頁(yè)。30多維幾何立方體如更多的維度呢？人類生活在一個(gè)三維的空間里，無(wú)法感覺(jué), 也很難想象多于三維的空間但是可以想象通過(guò)其他變量來(lái)表示更多維度點(diǎn)的質(zhì)量、顏色、粒子的類型還有什么？第30頁(yè)，共43頁(yè)。31數(shù)據(jù)立方體這是一個(gè)二維數(shù)據(jù)集(立方體)的例子。部門(mén)產(chǎn)品業(yè)績(jī)?nèi)A北蘋(píng)果10華北桔子5華南蘋(píng)果3華南桔子14第31頁(yè)，共43頁(yè)。32數(shù)據(jù)立方體產(chǎn)品維部門(mén)維蘋(píng)果桔子華北510華北143部門(mén)產(chǎn)品業(yè)績(jī)?nèi)A北蘋(píng)果10華北桔子5華南蘋(píng)果3華南桔子14第32頁(yè)，共43頁(yè)。33數(shù)據(jù)立方體無(wú)限多的維度無(wú)限多的指標(biāo)第33頁(yè)，共43頁(yè)。34幾何立方體 VS 數(shù)據(jù)立方體幾何立方體數(shù)據(jù)

11、立方體概念比較例子概念比較例子維度幾何體維度長(zhǎng)寬高分析維度產(chǎn)品、部門(mén)、特定點(diǎn)幾何點(diǎn)的量質(zhì)量粒子類型數(shù)據(jù)指標(biāo)集業(yè)績(jī)、定單數(shù)第34頁(yè)，共43頁(yè)。35數(shù)據(jù)透視透視，顧名思義，就是對(duì)一個(gè)多維數(shù)據(jù)立方體，在某個(gè)角度上進(jìn)行 “投影” 觀察。投影時(shí)，一些維度被關(guān)注, 其他維度被壓縮(忽略)。第35頁(yè)，共43頁(yè)。36數(shù)據(jù)透視上述二維數(shù)據(jù)集的例子。部門(mén)產(chǎn)品業(yè)績(jī)?nèi)A北蘋(píng)果10華北桔子5華南蘋(píng)果3華南桔子14第36頁(yè)，共43頁(yè)。37數(shù)據(jù)透視當(dāng)從部門(mén)的角度觀察數(shù)據(jù)時(shí)，結(jié)果為：當(dāng)從產(chǎn)品的角度觀察時(shí)，結(jié)果為：部門(mén)業(yè)績(jī)?nèi)A北15華南17產(chǎn)品業(yè)績(jī)蘋(píng)果13桔子19第37頁(yè)，共43頁(yè)。38透視表數(shù)據(jù)源數(shù)據(jù)源范式維度 + 統(tǒng)計(jì)指標(biāo)一個(gè)事實(shí)數(shù)據(jù)一行記錄部門(mén)(維度)產(chǎn)品(維度)業(yè)績(jī)(指標(biāo))華北蘋(píng)果10華北桔子5華南蘋(píng)