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

Excel高級(jí)培訓(xùn)：數(shù)據(jù)分析信息技術(shù)部辦公軟件技能培訓(xùn)系列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è)智能工具Excel進(jìn)階函數(shù)45一些有用的進(jìn)階函數(shù)數(shù)據(jù)引用AddressIndirectoffset數(shù)據(jù)查找與關(guān)聯(lián)LookupVlookupHlookup統(tǒng)計(jì)Count,CountifSum,SumifAVERAGE,AVERAGEAMax,MinMedian,ModeFrequencyRank,PercentRank邏輯函數(shù)AndOrNotIndirect功能按給定的文本形式的地址，來(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的值。67lookup功能函數(shù)LOOKUP在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值。如果需要指定包含待查找數(shù)值的區(qū)域，則可以使用函數(shù)LOOKUP的這種形式。函數(shù)LOOKUP的另一種形式為自動(dòng)在第一列或第一行中查找數(shù)值。8lookup語(yǔ)法1lookup(<要找的值>,<在哪里找>,<結(jié)果在哪里>)參數(shù)要找的內(nèi)容指定函數(shù)LOOKUP要在哪里查找。必須按升序排序.在哪里找指定要查找的內(nèi)容，應(yīng)該是一個(gè)單一值或是單一單元格。結(jié)果在哪里指定結(jié)果位于的區(qū)域，其大小必須與<在哪里找>相同。注意：如果函數(shù)lookup找不到<要找的內(nèi)容>，則查找<在哪里找>中小于或等于<要找的內(nèi)容>的最大數(shù)值。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)售值(2.9)10Vl樂(lè)oo跑ku脖p功能在表貌格或融數(shù)值錄數(shù)組拖的首織列查剖找指薄定的菠數(shù)值折，并報(bào)由此示返回壓表格套或數(shù)惱組當(dāng)粉前行誓中指顯定列選處的即數(shù)值語(yǔ)法VL輸OO紫KU旁P(朋<要找狠的值>,恥<在哪芳里找>,辱<找到董后取意第幾崖列>,笑<是否針模糊公查找>)11Vl租oo去ku邀p參數(shù)<要找糞的值>需要河在數(shù)防組第胳一列系中查判找的讓數(shù)值繼。<在哪時(shí)里找>為需漁要在寄其中宜查找泳數(shù)據(jù)購(gòu)的數(shù)皆據(jù)表倍。可摸以使妖用對(duì)少區(qū)域錘或區(qū)銜域名蓋稱(chēng)的衣引用頭，例憂(yōu)如數(shù)薯?yè)?jù)庫(kù)削或列周表。<找到豆后取開(kāi)第幾波列>為<在哪賴(lài)?yán)镎?gt;中待搖返回傳的匹脈配值澆的列灘序號(hào)意。從喝１開(kāi)廚始。<是否平模糊雄查找>為一良邏輯牢值，率指明脖是精漁確匹坦配，嶼還是柜近似秀匹配卷。鴉輸入紛“FA行LS梳E”將查寸找精餓確匹漁配值望。12Vl肯oo杏ku聯(lián)p(儀2)AB1產(chǎn)品銷(xiāo)售量2桔子1.53蘋(píng)果2.94西瓜2.15草莓0.7公式說(shuō)明（結(jié)果）=VLOOKUP(“蘋(píng)果”,A2:B6,2)在A2:B6區(qū)域中的第一列中查找蘋(píng)果，并返回同一區(qū)域?qū)?yīng)第2列的銷(xiāo)售值(2.9)13Hl被oo豪ku州p說(shuō)明在表燦格或餃數(shù)值稼數(shù)組擠的首摘行查歉找指答定的脊數(shù)值物，并傻由此學(xué)返回盟表格潮或數(shù)陡組當(dāng)際前列代中指鑄定行侄處的怪?jǐn)?shù)值語(yǔ)法HL縱OO睡KU歉P(皆<要找訴的值>,鼠<在哪毯里找>,壇<找到曾后取孫第幾滑行>,駝<是否鵲模糊閘查找>)14Hl鄙oo叮ku慣p參數(shù)<要找論的值>需要摟在數(shù)懷組第嗚一列禮中查降找的篩數(shù)值寧。<在哪遍里找>為需碌要在恐其中周查找由數(shù)據(jù)釋的數(shù)炒據(jù)表霧?？蓺堃允拐裼脤?duì)污區(qū)域蘭或區(qū)病域名嚴(yán)稱(chēng)的品引用豎，例粘如數(shù)伯據(jù)庫(kù)啟或列低表。<找到栗后取撲第幾改行>為<在哪棟里找>中待賞返回戰(zhàn)的匹醒配值甚的行扯序號(hào)隙。從詳１開(kāi)炮始。<是否先模糊徹查找>為一槍邏輯要值，澇指明翼是精符確匹擊配，沃還是練近似鞏匹配娛。FA磨LS謹(jǐn)E將查違找精達(dá)確匹筍配值吳。15HL但oo花ku吃pABCD1桔子蘋(píng)果西瓜草莓21.52.92.10.7公式說(shuō)明（結(jié)果）=HLOOKUP(“蘋(píng)果”,A1:D2,2)在A1:D2區(qū)域中的第一行中查找蘋(píng)果，并返回同一區(qū)域?qū)?yīng)第2行的銷(xiāo)售值(2.9)16Co姓un測(cè)ti璃f功能計(jì)數(shù)譽(yù)符合紋條件討的那基些記朋錄語(yǔ)法CO沃UN棚TI備F(肌<條件湖所在派區(qū)域>,獎(jiǎng)<條件>)參數(shù)<條件努所在范區(qū)域>為需接要計(jì)組算其昨中滿(mǎn)商足條連件的詳單元定格數(shù)抄目的映單元芬格區(qū)妻域。<條件>為確塘定哪鄭些單幟元格粱將被齒計(jì)算嚇在內(nèi)渴的條旋件。持例如歌，條黎件可偏以表沾示為32、"3設(shè)2"、">守32短"或"a遷pp停le剛s"。17Co括un雞ti宵f(樣2)

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

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

=COUNTIF(A2:A5,"apples")計(jì)算第一列中蘋(píng)果所在單元格的個(gè)數(shù)(2)18su仁mi稻f功能對(duì)符羨合條當(dāng)件的崇那些學(xué)記錄看的指懇定列跳求和語(yǔ)法SU呀MI坦F(炊<條件暑區(qū)域>,面<條件>[駱,朱<求和樓區(qū)域>]傻)19su卻mi走f參數(shù)<條件較區(qū)域>為用檢于條奴件判惠斷的扇單元悼格區(qū)晝域。<條件>為確豈定哪押些單紋元格脊將被個(gè)相加嫩求和寧的條欠件。飲例如瓜，條財(cái)件可暫以表鮮示為32、"3匪2"、">瓦32先"或"a糾pp族le肌s"。<求和容區(qū)域>是需喪要求貢和的叔實(shí)際貪單元席格。說(shuō)明只有事在區(qū)嘉域中南相應(yīng)熱的單應(yīng)元格怠符合漂條件庭的情熄況下冠，<求和瀉區(qū)域>中的宜單元近格才某求和桶。如果奧忽略訴了<求和運(yùn)區(qū)域>，則愁對(duì)<條件婦區(qū)域>中的露單元槐格求授和。20su繳mi免f恥(2且)

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

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

=SUMIF(A2:A5,“>160000”,B2:B5)屬性值超過(guò)160,000的傭金的和(63,000)2122邏輯億函數(shù)An屬d：與Or：或No亭t：非條件1真真假假條件2真假真假and真假假假or真真真假23條件棗結(jié)果傾函數(shù)ifIf繡(邏輯膠值,結(jié)果1,結(jié)果2)如果剖邏輯奔值為唱真，濤則公唉式返師回結(jié)領(lǐng)果1如果租邏輯臉值為止假，權(quán)則公凝式返毫回結(jié)卵果224邏輯而計(jì)算閥例子判斷攀消費(fèi)奔是否鄰在10到20之間腔，或天者已查經(jīng)特承別要灘求進(jìn)頃行提斧醒，開(kāi)這些厘客戶(hù)山要求轟進(jìn)行聚短信陽(yáng)提醒拋：=i料f(灰or仔(召an堆d(步B2畝>=次10誼,裁B2屈<=鍵20愛(ài)),苦C直2=憐“是“),搜“需要尤提醒團(tuán)”,賺“不要柴提醒舟”)ABCD1姓名消費(fèi)要求提醒是否提醒2張三13否3李四5是25請(qǐng)善秘用Ex害ce米l的幫抖助自學(xué)糧是最蘭好的搖老師.Ex卡ce繩l的幫踢助包自括了晉所有俘的函喝數(shù)用棄法,示例她等資握料,是最客好的Ex夠ce裝l學(xué)習(xí)負(fù)材料.數(shù)據(jù)喇分析端部分27數(shù)據(jù)趟分析車(chē)基礎(chǔ)同知識(shí)數(shù)據(jù)忍挖掘數(shù)據(jù)悟挖掘土（Da蜂ta浪M盟in量in塘g，DM）是付指從面大量恨不完認(rèn)全的曲、有形噪聲都的、擠模糊阻的、爺隨機(jī)親的數(shù)槳據(jù)中樸，提污取隱威含在驅(qū)其中遣的、牛有用爪的信骨息和艙知識(shí)倉(cāng)的過(guò)睛程。“數(shù)純據(jù)立指方體價(jià)”是閉一個(gè)化常用破的數(shù)勞據(jù)挖破掘分此析概覽念.28數(shù)據(jù)鴿分析節(jié)基礎(chǔ)鋒知識(shí)數(shù)據(jù)哄立方撥體概滲念幾何谷立方豬體多維毀幾何妥立方淚體數(shù)據(jù)刪立方努體透視殊表29三維溪幾何航立方娃體三個(gè)靠維度蠅：長(zhǎng)寬高一個(gè)捧被上柱述維示度限諸定了畫(huà)位置豆的點(diǎn)O30多維肥幾何費(fèi)立方遣體如更旗多的務(wù)維度粗呢？人類(lèi)生活費(fèi)在一甚個(gè)三順維的底空間齡里，帥無(wú)法絨感覺(jué),也很顯難想胖象多落于三勤維的幅空間但是兔可以列想象廈通過(guò)擋其他撿變量褲來(lái)表燙示更缺多維吧度點(diǎn)的譯質(zhì)量湖、顏稻色、尚粒子壺的類(lèi)強(qiáng)型……還有嗎什么幣？31數(shù)據(jù)透立方選體這是駱一個(gè)貢二維幅數(shù)據(jù)治集(立方之體)的例鋼子。部門(mén)產(chǎn)品業(yè)績(jī)?nèi)A北蘋(píng)果10華北桔子5華南蘋(píng)果3華南桔子1432數(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華南桔子1433數(shù)據(jù)膝立方腐體無(wú)限為多的肺維度無(wú)限墓多的出指標(biāo)34幾何普立方蜓體VS數(shù)據(jù)淋立方計(jì)體幾何立方體數(shù)據(jù)立方體概念比較例子概念比較例子維度幾何體維度長(zhǎng)寬高分析維度產(chǎn)品、部門(mén)、…特定點(diǎn)幾何點(diǎn)的量質(zhì)量粒子類(lèi)型數(shù)據(jù)指標(biāo)集業(yè)績(jī)、定單數(shù)35數(shù)據(jù)弊透視透視堪，顧頌名思考義，芝就是蓋對(duì)一帝個(gè)多酷維數(shù)侮據(jù)立造方體得，在見(jiàn)某個(gè)冶角度嗚上進(jìn)輩行劑“投坐影”挨觀稈察。投影眨時(shí)，揉一些鈔維度啞被關(guān)捉注,其他源維度寶被壓縮(忽略)。36數(shù)據(jù)沿透視上述取二維時(shí)數(shù)據(jù)奔集的屆例子曲。部門(mén)產(chǎn)品業(yè)績(jī)?nèi)A北蘋(píng)果10華北桔子5華南蘋(píng)果3華南桔子1437數(shù)據(jù)構(gòu)透視當(dāng)從怪部門(mén)周的角吼度觀厭察數(shù)答據(jù)時(shí)鄰，結(jié)慕果為楚：當(dāng)從無(wú)產(chǎn)品京的角頓度觀繩察時(shí)冊(cè)，結(jié)頓果為僑：部門(mén)業(yè)績(jī)?nèi)A北15華南17產(chǎn)品業(yè)績(jī)蘋(píng)果13桔子1938透視次表數(shù)或據(jù)源數(shù)據(jù)價(jià)源范飽式維度+統(tǒng)