Excel進(jìn)階培訓(xùn)：數(shù)據(jù)分析篇課件

Excel高級培訓(xùn)：數(shù)據(jù)分析信息技術(shù)部辦公軟件技能培訓(xùn)系列Excel高級培訓(xùn)：數(shù)據(jù)分析信息技術(shù)部辦公軟件技能培訓(xùn)系列2本課程您將學(xué)到什么？這是一個(gè)面向進(jìn)階技巧的培訓(xùn)掌握一些必要的excel進(jìn)階函數(shù)理解并掌握數(shù)據(jù)立方體的概念通過Excel的數(shù)據(jù)透視分析的練習(xí)，熟悉數(shù)據(jù)立方體的一般形式提高數(shù)據(jù)分析工作的技能做好準(zhǔn)備，應(yīng)用更高級的商業(yè)智能工具2本課程您將學(xué)到什么？這是一個(gè)面向進(jìn)階技巧的培訓(xùn)Excel進(jìn)階函數(shù)Excel進(jìn)階函數(shù)445一些有用的進(jìn)階函數(shù)數(shù)據(jù)引用AddressIndirectoffset數(shù)據(jù)查找與關(guān)聯(lián)LookupVlookupHlookup統(tǒng)計(jì)Count,CountifSum,SumifAVERAGE,AVERAGEAMax,MinMedian,ModeFrequencyRank,PercentRank邏輯函數(shù)AndOrNot5一些有用的進(jìn)階函數(shù)數(shù)據(jù)引用Indirect功能按給定的文本形式的地址，來引用內(nè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的值。6Indirect功能67lookup功能函數(shù)LOOKUP在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值。如果需要指定包含待查找數(shù)值的區(qū)域，則可以使用函數(shù)LOOKUP的這種形式。函數(shù)LOOKUP的另一種形式為自動(dòng)在第一列或第一行中查找數(shù)值。7lookup功能8lookup語法1lookup(<要找的值>,<在哪里找>,<結(jié)果在哪里>)參數(shù)要找的內(nèi)容指定函數(shù)LOOKUP要在哪里查找。必須按升序排序.在哪里找指定要查找的內(nèi)容，應(yīng)該是一個(gè)單一值或是單一單元格。結(jié)果在哪里指定結(jié)果位于的區(qū)域，其大小必須與<在哪里找>相同。注意：如果函數(shù)lookup找不到<要找的內(nèi)容>，則查找<在哪里找>中小于或等于<要找的內(nèi)容>的最大數(shù)值。8lookup語法19lookupAB1產(chǎn)品銷售2桔子1.53蘋果2.94西瓜2.15草莓0.7公式說明（結(jié)果）=LOOKUP(“蘋果”,A2:A5,B2:B5)在A列中查找蘋果，并返回同一行B列的銷售值(2.9)9lookupAB1產(chǎn)品銷售2桔子1.53蘋果2.94西瓜210Vlookup功能在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值語法VLOOKUP(<要找的值>,<在哪里找>,<找到后取第幾列>,<是否模糊查找>)10Vlookup功能11Vlookup參數(shù)<要找的值>需要在數(shù)組第一列中查找的數(shù)值。<在哪里找>為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。可以使用對區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫或列表。<找到后取第幾列>為<在哪里找>中待返回的匹配值的列序號。從１開始。<是否模糊查找>為一邏輯值，指明是精確匹配，還是近似匹配。輸入“FALSE”將查找精確匹配值。11Vlookup參數(shù)12Vlookup(2)AB1產(chǎn)品銷售量2桔子1.53蘋果2.94西瓜2.15草莓0.7公式說明（結(jié)果）=VLOOKUP(“蘋果”,A2:B6,2)在A2:B6區(qū)域中的第一列中查找蘋果，并返回同一區(qū)域?qū)?yīng)第2列的銷售值(2.9)12Vlookup(2)AB1產(chǎn)品銷售量2桔子1.53蘋果213Hlookup說明在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值語法HLOOKUP(<要找的值>,<在哪里找>,<找到后取第幾行>,<是否模糊查找>)13Hlookup說明14Hlookup參數(shù)<要找的值>需要在數(shù)組第一列中查找的數(shù)值。<在哪里找>為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤^(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫或列表。<找到后取第幾行>為<在哪里找>中待返回的匹配值的行序號。從１開始。<是否模糊查找>為一邏輯值，指明是精確匹配，還是近似匹配。FALSE將查找精確匹配值。14Hlookup參數(shù)15HLookupABCD1桔子蘋果西瓜草莓21.52.92.10.7公式說明（結(jié)果）=HLOOKUP(“蘋果”,A1:D2,2)在A1:D2區(qū)域中的第一行中查找蘋果，并返回同一區(qū)域?qū)?yīng)第2行的銷售值(2.9)15HLookupABCD1桔子蘋果西瓜草莓21.52.9216Countif功能計(jì)數(shù)符合條件的那些記錄語法COUNTIF(<條件所在區(qū)域>,<條件>)參數(shù)<條件所在區(qū)域>為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。<條件> 為確定哪些單元格將被計(jì)算在內(nèi)的條件。例如，條件可以表示為32、"32"、">32"或"apples"。16Countif功能17Countif(2)

AB1數(shù)據(jù)數(shù)據(jù)2apples323oranges544peaches755apples86

公式說明（結(jié)果）

=COUNTIF(A2:A5,"apples")計(jì)算第一列中蘋果所在單元格的個(gè)數(shù)(2)17Countif(2)AB1數(shù)據(jù)數(shù)據(jù)2apples32318sumif功能對符合條件的那些記錄的指定列求和語法SUMIF(<條件區(qū)域>,<條件>[,<求和區(qū)域>])18sumif功能19sumif參數(shù)<條件區(qū)域>為用于條件判斷的單元格區(qū)域。<條件>為確定哪些單元格將被相加求和的條件。例如，條件可以表示為32、"32"、">32"或"apples"。<求和區(qū)域>是需要求和的實(shí)際單元格。說明只有在區(qū)域中相應(yīng)的單元格符合條件的情況下，<求和區(qū)域>中的單元格才求和。如果忽略了<求和區(qū)域>，則對<條件區(qū)域>中的單元格求和。19sumif參數(shù)20sumif(2)

AB1屬性值傭金2100,0007,0003200,00014,0004300,00021,0005400,00028,000

公式說明（結(jié)果）

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

“需要提醒”,“不要提醒”)ABCD1姓名消費(fèi)要求提醒是否提醒2張三13否3李四5是24邏輯計(jì)算例子判斷消費(fèi)是否在10到20之間，或者已經(jīng)特別要25請善用Excel的幫助自學(xué)是最好的老師.Excel的幫助包括了所有的函數(shù)用法,示例等資料,是最好的Excel學(xué)習(xí)材料.25請善用Excel的幫助自學(xué)是最好的老師.數(shù)據(jù)分析部分?jǐn)?shù)據(jù)分析部分27數(shù)據(jù)分析基礎(chǔ)知識數(shù)據(jù)挖掘數(shù)據(jù)挖掘（DataMining，DM）是指從大量不完全的、有噪聲的、模糊的、隨機(jī)的數(shù)據(jù)中，提取隱含在其中的、有用的信息和知識的過程?！皵?shù)據(jù)立方體”是一個(gè)常用的數(shù)據(jù)挖掘分析概念.27數(shù)據(jù)分析基礎(chǔ)知識數(shù)據(jù)挖掘28數(shù)據(jù)分析基礎(chǔ)知識數(shù)據(jù)立方體概念幾何立方體多維幾何立方體數(shù)據(jù)立方體透視表28數(shù)據(jù)分析基礎(chǔ)知識數(shù)據(jù)立方體概念29三維幾何立方體三個(gè)維度：長寬高一個(gè)被上述維度限定了位置的點(diǎn)O29三維幾何立方體三個(gè)維度：O30多維幾何立方體如更多的維度呢？人類生活在一個(gè)三維的空間里，無法感覺,也很難想象多于三維的空間但是可以想象通過其他變量來表示更多維度點(diǎn)的質(zhì)量、顏色、粒子的類型……還有什么？30多維幾何立方體如更多的維度呢？31數(shù)據(jù)立方體這是一個(gè)二維數(shù)據(jù)集(立方體)的例子。部門產(chǎn)品業(yè)績?nèi)A北蘋果10華北桔子5華南蘋果3華南桔子1431數(shù)據(jù)立方體這是一個(gè)二維數(shù)據(jù)集(立方體)的例子。部門產(chǎn)品業(yè)32數(shù)據(jù)立方體產(chǎn)品維部門維蘋果桔子華北510華北143部門產(chǎn)品業(yè)績?nèi)A北蘋果10華北桔子5華南蘋果3華南桔子1432數(shù)據(jù)立方體產(chǎn)品維蘋果桔子華北510華北143部門產(chǎn)品業(yè)績33數(shù)據(jù)立方體無限多的維度無限多的指標(biāo)33數(shù)據(jù)立方體無限多的維度34幾何立方體VS數(shù)據(jù)立方體幾何立方體數(shù)據(jù)立方體概念比較例子概念比較例子維度幾何體維度長寬高分析維度產(chǎn)品、部門、…特定點(diǎn)幾何點(diǎn)的量質(zhì)量粒子類型數(shù)據(jù)指標(biāo)集業(yè)績、定單數(shù)34幾何立方體VS數(shù)據(jù)立方體幾何立方體數(shù)據(jù)立方體概念比較35數(shù)據(jù)透視透視，顧名思義，就是對一個(gè)多維數(shù)據(jù)立方體，在某個(gè)角度上進(jìn)行“投影”觀察。投影時(shí)，一些維度被關(guān)注,其他維度被壓縮(忽略)。35數(shù)據(jù)透視透視，顧名思義，就是對一個(gè)多維數(shù)據(jù)立方體，在某個(gè)36數(shù)據(jù)透視上述二維數(shù)據(jù)集的例子。部門產(chǎn)品業(yè)績?nèi)A北蘋果10華北桔子5華南蘋果3華南桔子1436數(shù)據(jù)透視上述二維數(shù)據(jù)集的例子。部門產(chǎn)品業(yè)績?nèi)A北蘋果10華37數(shù)據(jù)透視當(dāng)從部門的角度觀察數(shù)據(jù)時(shí)，結(jié)果為：當(dāng)從產(chǎn)品的角度觀察時(shí)，結(jié)果為：部門業(yè)績?nèi)A北15華南17產(chǎn)品業(yè)績蘋果13桔子1937數(shù)據(jù)透視當(dāng)從部門的角度觀察數(shù)據(jù)時(shí)，結(jié)果為：部門業(yè)績?nèi)A北138透視表數(shù)據(jù)源數(shù)據(jù)源范式維度+統(tǒng)計(jì)指標(biāo)一個(gè)事實(shí)數(shù)據(jù)一行記錄部門(維度)