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

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

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

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

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

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

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

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

8、A5,“160000”, B2:B5)屬性值超過 160,000 的傭金的和 (63,000)第20頁，共43頁。21第21頁，共43頁。22邏輯函數(shù)And：與Or：或Not：非條件1真真假假條件2真假真假and真假假假or真真真假第22頁，共43頁。23條件結(jié)果函數(shù)ifIf(邏輯值, 結(jié)果1, 結(jié)果2)如果邏輯值為真，則公式返回結(jié)果1如果邏輯值為假，則公式返回結(jié)果2第23頁，共43頁。24邏輯計算例子判斷消費(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頁，共43頁。25請善用Excel的幫助自學(xué)是最好的老師.Excel的幫助包括了所有的函數(shù)用法, 示例等資料, 是最好的Excel學(xué)習(xí)材料.第25頁，共43頁。數(shù)據(jù)分析部分第26頁，共43頁。27數(shù)據(jù)分析基礎(chǔ)知識數(shù)據(jù)挖掘數(shù)據(jù)挖掘（Data Mining，DM）是指從大量不完全的、有噪聲的、模糊的、隨機(jī)的數(shù)據(jù)中，提取隱含在其中的、有用的信息和知識的過程。“數(shù)據(jù)立方體”是一個常用的數(shù)據(jù)挖掘分析概念.第27頁，共43頁。28數(shù)據(jù)分析基礎(chǔ)知識數(shù)據(jù)立方體概念幾何立方體多維幾何立方體數(shù)據(jù)立方體透視表第28頁，共43頁。29三維幾何立方體三個維度：長寬高一

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

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