 Excel自帶的數(shù)據(jù)透視表是一個(gè)數(shù)據(jù)分析的利器，讓你的數(shù)據(jù)分析速度高效。以前用公式函數(shù)來(lái)做的數(shù)據(jù)分析可能需要花半天，但如果使用透視表來(lái)處理的話可能5分鐘就搞定。但是遺憾的是，很多天天和excel打交道的人還不會(huì)數(shù)據(jù)透視表，或者只會(huì)基本功能。今天介紹幾個(gè)“小功能”的“大應(yīng)用”?？赡軙?huì)讓很多人有腦洞大開(kāi)之感。 先簡(jiǎn)單介紹一下透視表的如何入門(mén)？注：以下分析全部是基于excel2007版本的截圖 首先你的數(shù)據(jù)源應(yīng)該是這樣的一維數(shù)據(jù)表格，且第一行不能有空值  把鼠標(biāo)放到數(shù)據(jù)域的區(qū)域內(nèi)的任何地方，然后依次點(diǎn)擊excel面板上的數(shù)據(jù)-數(shù)據(jù)透視表-數(shù)據(jù)透視表（別點(diǎn)數(shù)據(jù)透視圖）-（在彈出的對(duì)話框中點(diǎn)）確定，此時(shí)你就創(chuàng)建了一個(gè)數(shù)據(jù)透視表了，如下圖：  各位看官，你們數(shù)據(jù)透視表就算入門(mén)了。然后就可以將右邊圖中的字段拖拽到數(shù)值、行列標(biāo)簽和報(bào)表篩選中去生成各種分析報(bào)表了，如圖。  是不是很簡(jiǎn)單？是不是瞬間就掌握了一門(mén)新技術(shù)！你還可以瞬間做出這樣的透視表：  除了將價(jià)格進(jìn)行分段處理外，還可以快速生成月、季、年報(bào)告，完全不需要公式。  注意我前面的措辭，以上這些報(bào)表都是瞬間完成，根本不需要公式，分列等亂七八糟的東西，厲害了我的Excel。在我這幾年的數(shù)據(jù)分析培訓(xùn)中，發(fā)現(xiàn)有一大半號(hào)稱會(huì)透視表的人其實(shí)并不會(huì)這些簡(jiǎn)便用法，今天我就奉獻(xiàn)三招簡(jiǎn)便而不簡(jiǎn)單的透視表用法，保證將你的效率提高幾百倍。 1、透視表的分組功能 在不會(huì)透視表的分組功能前，很多人是將數(shù)據(jù)源中的日期通過(guò)分列或公式的方法拆分成月、季、年。其中月、年的公式還比較簡(jiǎn)單，季度的判斷會(huì)復(fù)雜一些，于是各種if語(yǔ)句的嵌套，水平高點(diǎn)的人則用vlookup解決。  其實(shí)完全不需要這樣復(fù)雜，用公式的方法除了費(fèi)時(shí)費(fèi)力外，還會(huì)造成文件變大，運(yùn)行速度變慢。其實(shí)透視表早就有這種通用問(wèn)題的解決方案。 這就是透視表的“分組”功能，超級(jí)無(wú)敵簡(jiǎn)單的功能！ 步驟如下圖：生成一個(gè)如圖的透視表，選中日期中的任一單元格，再點(diǎn)選項(xiàng)，最后選將所選內(nèi)容分組的按鈕。| 注意：2013版本的分組功能在分析-組選擇中。  點(diǎn)擊后會(huì)彈出一個(gè)如下的對(duì)話框，什么也別想，先把月、季、年全部選中，然后點(diǎn)擊確定，ok大功告成。  此時(shí)你會(huì)發(fā)現(xiàn)你的字段中，多了一個(gè)季度、年的字段，下圖的銷售日期則被賦予月字段的意義。  透視表也成為下左圖這樣了，將年、季度拖拽到列標(biāo)簽中就成為一份完美的年報(bào)表了（下右），so easy！  透視表的分組功能就是這樣牛逼，更牛逼的是它能對(duì)所有數(shù)字（包括日期）格式的字段進(jìn)行分組，比如價(jià)格段分組、工齡分組...... 方法大同小異，只是在分組設(shè)置區(qū)域的設(shè)置略有不同，下圖是將零售價(jià)拖拽到行標(biāo)簽，選擇分組后彈出的對(duì)話框（下左圖）。起始和終止值是數(shù)據(jù)源中的最小、最大值，步長(zhǎng)是系統(tǒng)自動(dòng)推薦。不過(guò)一般情況系統(tǒng)推薦不是最優(yōu)的分組段，所以可以自己修改這三個(gè)值，如下右圖。 右圖其實(shí)是將零售價(jià)分成了四段，點(diǎn)擊確定后就成為如下的圖表。將成交金額換成百分比就成為前面第四張圖的格式了。 將工齡、年齡、會(huì)員購(gòu)買(mǎi)頻率等分組是一樣的道理，就不累述了，大家自己研究吧。好用吧？有沒(méi)有被震驚到？當(dāng)然會(huì)這些功能的表哥表妹會(huì)覺(jué)得是小兒科，別急，往下看。 2、透視表算同比和環(huán)比 在計(jì)算同比和環(huán)比時(shí)很多人是先用透視表生成年、月的數(shù)據(jù)，然后再用計(jì)算器或excel的單元格設(shè)個(gè)公式來(lái)計(jì)算。麻不麻煩？其實(shí)透視表有現(xiàn)成的算法，就看你get到?jīng)]有。 先把透視表按如下格式放置（上為字段年，左為月，中間的值根據(jù)需要放置）： 找到數(shù)據(jù)透視表字段列表，點(diǎn)擊成交金額邊上的三角，再在彈出的列表中選“值字段設(shè)置”。 又出現(xiàn)一個(gè)如下的新對(duì)話框，依次選擇值顯示方式-普通旁邊的那個(gè)三角-差異百分比。最后點(diǎn)擊確定。 繼續(xù)選擇。同比是年vs年，所以基本字段選“年”（環(huán)比則選“月”），基本項(xiàng)選“上一個(gè)”，就是今年和上一年對(duì)比（就是同比），一般都是選上一個(gè)?；卷?xiàng)也可以選具體的年，那就是定基比的分析了，不懂定基比為何物的請(qǐng)自己百度。最后確定，靜等奇跡的發(fā)生。 看看這是不是你們想要的東西？2017年為空值是因?yàn)闆](méi)有2016年的數(shù)據(jù)可對(duì)比，2020年5月后為-100%也是沒(méi)有數(shù)據(jù)的原因。 環(huán)比也是可以滴！ 如果你看到這兒有種想馬上打開(kāi)電腦實(shí)操一下的沖動(dòng)，說(shuō)明我已經(jīng)打動(dòng)你了，還等什么呢？當(dāng)然下一個(gè)功能也許更有用。 3、透視表添加公式 透視表不僅僅限于數(shù)據(jù)源中有的字段，我們其實(shí)可以根據(jù)業(yè)務(wù)邏輯生成新的字段，而這些新的字段并不需要在數(shù)據(jù)源中出現(xiàn)，只需要有對(duì)應(yīng)的邏輯關(guān)系就行。 我們仔細(xì)看上面的數(shù)據(jù)源，除了目前已經(jīng)有的7個(gè)基本字段外，其實(shí)還隱藏著折扣率這個(gè)指標(biāo)，邏輯是折扣率=成交價(jià)÷零售價(jià)。同樣我們并不需要在數(shù)據(jù)源中新增一個(gè)折扣率的字段，雖然我知道你們絕大多數(shù)是這樣干的！ 首先選中透視表中任意單元格，再依次選擇：選項(xiàng)-公式-計(jì)算字段。| 注意2013版本excel的公式在項(xiàng)目和集中。 接下來(lái)在彈出的對(duì)話框中進(jìn)行公式邏輯的配置。名稱輸入你新增的字段名（不能和已有字段名重復(fù)），在字段中選中對(duì)應(yīng)的字段點(diǎn)擊“插入字段”，這個(gè)字段就會(huì)出現(xiàn)在公式中。有幾個(gè)字段就需要插入幾次，然后把對(duì)應(yīng)的邏輯關(guān)系寫(xiě)到公式中（折扣率=成交價(jià)÷零售價(jià)）。 最后點(diǎn)擊確定，大功告成。折扣率成為一個(gè)新的字段，通過(guò)拖拽其他字段就可以隨心所以的分析折扣了，如下圖。 怎么樣？數(shù)據(jù)透視表就是應(yīng)該這樣玩的，這樣三招肯定會(huì)讓你平日的工作效率提升一大截的。明天你就可以拿這三招到公司去show了，根據(jù)我培訓(xùn)時(shí)的經(jīng)驗(yàn)，絕對(duì)迷倒一大片。到時(shí)記得回來(lái)感謝我哦！ |
|
|
來(lái)自： nmzhiguofan > 《知識(shí)類》
