編按：哈嘍，大家好！在日常工作中我們會(huì)構(gòu)建很多的表格，其實(shí)這些表格無(wú)論多少，都可以歸結(jié)為三大類(lèi)，分別為“源數(shù)據(jù)表”、“關(guān)系信息表”和“統(tǒng)計(jì)分析表”。上次我們提到了一套表格“統(tǒng)計(jì)的核心”——“源數(shù)據(jù)表”，今天繼續(xù)為大家分享“關(guān)系信息表”和“統(tǒng)計(jì)分析表”的創(chuàng)建過(guò)程。（本篇為該系列文章的下篇）
＊＊＊＊＊＊＊＊＊
【前言】繼續(xù)前篇內(nèi)容，當(dāng)我們有了一個(gè)規(guī)范的《源數(shù)據(jù)表》之后，接下來(lái)就是需要處理這些數(shù)據(jù)的過(guò)程了，總不能拿著一張明細(xì)表給老板交差吧？！下面就來(lái)看看在后續(xù)的表格制作中，我們還需要注意些什么。
二、“關(guān)系信息表”是一套表格“關(guān)聯(lián)的紐帶”
“關(guān)系信息表”的作用是對(duì)“源數(shù)據(jù)表”中某一個(gè)或多個(gè)字段內(nèi)容的信息補(bǔ)充。其建表規(guī)則與“源數(shù)據(jù)表”基本相同。但是“源數(shù)據(jù)表”中的“關(guān)鍵字”可以多次重復(fù)出現(xiàn)，而“關(guān)系信息表”中的“關(guān)鍵字”必須是唯一的。用一個(gè)圖例輔助說(shuō)明一下：
因?yàn)椤瓣P(guān)系信息表”的作用是補(bǔ)充說(shuō)明，所以它的一些信息內(nèi)容，經(jīng)常被我們索引到其他表中使用。如果你懂得VLOOKUP函數(shù)被搜索區(qū)域的首列內(nèi)容必須是唯一存在的話，那么就應(yīng)該明白這個(gè)表中的索引值必須保證是唯一存在的重要性了。
謹(jǐn)記良言：不要在建表之初，給自己在以后使用的時(shí)候挖坑！
除了這些信息性質(zhì)的內(nèi)容，還有一些輔助計(jì)算的說(shuō)明性表格也屬于此類(lèi)“關(guān)系信息表”的范疇，構(gòu)建表格的時(shí)候，需要我們單獨(dú)的列出一個(gè)Sheet以供引用。
1. 等級(jí)評(píng)定
此類(lèi)問(wèn)題，如果在沒(méi)有“關(guān)系信息表”輔助計(jì)算的情況下，我們需要使用常量數(shù)組的方式，用函數(shù)來(lái)解決。
B3單元格函數(shù)：=LOOKUP(A3,{0,50,100,200},{"D","C","B","A"})
不僅這樣寫(xiě)函數(shù)比較麻煩，而且也增加了寫(xiě)函數(shù)的難度，起碼只有會(huì)常量數(shù)組的同學(xué)能寫(xiě)出來(lái)，否則就要用一串IF函數(shù)嵌套解決。并且上面的這種方式有一個(gè)很大的弊端，如果評(píng)級(jí)條件發(fā)生改變，我們就又需要重新設(shè)定函數(shù)內(nèi)容。
但是如果我們?cè)黾右粋€(gè)“關(guān)系信息表”，用表的形式來(lái)表述這段文字，那么上面的這些問(wèn)題就都可以解決，如下：
B3單元格的函數(shù)：=VLOOKUP(A3,OFFSET($E$3,,,COUNT($E$3:$E$50),2),2,1)
本身我們直接用=VLOOKUP(A3,$E$3:$F$6,2,1)函數(shù)就可以得到結(jié)果，但是考慮到以后的評(píng)級(jí)標(biāo)準(zhǔn)可能會(huì)變動(dòng)，所以我們加入了OFFSET函數(shù)（往期有教程，不做贅述）形成一個(gè)動(dòng)態(tài)的引用，所以無(wú)論評(píng)級(jí)怎么改變、增加，統(tǒng)計(jì)表中的“等級(jí)”字段都會(huì)自動(dòng)調(diào)整。
2. 規(guī)定時(shí)間范圍內(nèi)的有效值
這類(lèi)問(wèn)題，在日常工作中也很普遍，比如說(shuō)單價(jià)的問(wèn)題，無(wú)論是進(jìn)貨價(jià)格還是銷(xiāo)售價(jià)格，都不可能是一成不變的，會(huì)根據(jù)市場(chǎng)原材料的價(jià)格時(shí)常做出上調(diào)、下調(diào)。如果我們依然使用固定值，就經(jīng)常需要更改數(shù)據(jù)，那么很容易就會(huì)因漏改、錯(cuò)改造成數(shù)據(jù)錯(cuò)誤。所以，我們還是需要“關(guān)系信息表”來(lái)做輔助性計(jì)算，以此方式也能最好的規(guī)避數(shù)據(jù)錯(cuò)誤的風(fēng)險(xiǎn)。
本例中使用了LOOKUP(1,0/(條件)......結(jié)構(gòu)，完成了多條件查詢(xún)。（不熟悉該結(jié)構(gòu)的小伙伴可以查看往期教程《VLOOKUP&LOOKUP雙雄戰(zhàn)（五）：野馬崛起！》）用這樣的處理方式就可以形成一個(gè)很好的價(jià)格自動(dòng)引用的效果了。
三、“統(tǒng)計(jì)分析表”是一套表格“效率的表現(xiàn)”
“統(tǒng)計(jì)分析表”是按照“源數(shù)據(jù)表”的內(nèi)容，結(jié)合“關(guān)系信息表”的說(shuō)明（或直接統(tǒng)計(jì)），根據(jù)具體統(tǒng)計(jì)分析的需求，使用Excel的技術(shù)手段形成自動(dòng)化報(bào)表，作為最終數(shù)據(jù)展示的過(guò)程。
1、建模的基本原理
統(tǒng)計(jì)分析是用“表”的形式表現(xiàn)出來(lái)的，但在實(shí)際的Excel操作上，其實(shí)是一個(gè)運(yùn)算的過(guò)程，可以通過(guò)“源數(shù)據(jù)表”直接得到我們需要的內(nèi)容，也可以結(jié)合“關(guān)系信息表”一起分析，具體問(wèn)題具體分析。例如下面的數(shù)據(jù)：
黃色的區(qū)域是函數(shù)部分。
C27單元格函數(shù)：
=SUMIFS($D$3:$D$6,$A$3:$A$6,">="&$A27,$A$3:$A$6,"<="&$B27,$C$3:$C$6,C$25)
函數(shù)解析：這是多條件求和函數(shù)的典型用法，在這里需要強(qiáng)調(diào)的是，關(guān)于日期范圍的條件判斷是對(duì)起始日期、截止日期兩個(gè)條件的判斷。在滿足A3:A6區(qū)域大于等于起始日期的A27單元格，且A3:A6區(qū)域小于等于截止日期的B27單元格，且C3:C6區(qū)域中等于C25單元格的條件下，匯總D3:D6區(qū)域的銷(xiāo)量。然后復(fù)制函數(shù)，復(fù)選中C27:C29、E27:E29、G27:G29，再選擇性粘貼為公式填充，一定要注意相對(duì)引用和絕對(duì)引用的使用。
D27單元格函數(shù)：
=VLOOKUP(C27,OFFSET($A$9,MATCH($A27,$A$10:$A$21,0),3,COUNTIF($A$10:$A$21,$A27),2),2,1)
函數(shù)解析：這個(gè)函數(shù)看著有一點(diǎn)不好理解吧，如果嵌套函數(shù)不好理解的時(shí)候，我們可以拆分來(lái)看。OFFSET函數(shù)的返回值，被作為VLOOKUP函數(shù)的第二參數(shù)；而MATCH函數(shù)和COUNTIF函數(shù)被作為OFFSET函數(shù)的第二、四參數(shù)。
（1）通過(guò)MATCH函數(shù)，確定《關(guān)系信息表》中起始日期第一次出現(xiàn)的序號(hào)，作為行偏移的參數(shù)，再通過(guò)COUNTIF函數(shù)，確定《關(guān)系信息表》中本月中的等級(jí)條件有幾行，作為行擴(kuò)展的參數(shù)。
（2）OFFSET通過(guò)5個(gè)參數(shù)的運(yùn)算，返回了當(dāng)月等級(jí)評(píng)定標(biāo)準(zhǔn)的區(qū)域范圍，并作為VLOOKUP的第二參數(shù)參與運(yùn)算。
（3）最后通過(guò)VLOOKUP的模糊查詢(xún)找到對(duì)應(yīng)的等級(jí)。
然后復(fù)制函數(shù)，復(fù)選中D27:D29、F27:F29、H27:H29，再選擇性粘貼為公式，進(jìn)行填充。同樣需要注意相對(duì)引用和絕對(duì)引用的使用。
篇幅有限，案例中的數(shù)據(jù)內(nèi)容比較少，但是意圖和思路還是可以表述清晰：銷(xiāo)量是基于“源數(shù)據(jù)表”使用函數(shù)得到每個(gè)銷(xiāo)售員每個(gè)月的銷(xiāo)量合計(jì)；等級(jí)是按照返回的銷(xiāo)量結(jié)合“關(guān)系信息表”的各月各類(lèi)參數(shù)使用函數(shù)得到。上面的這個(gè)思路，就是我們俗稱(chēng)“建?！钡幕驹恚?/p>
2、“統(tǒng)計(jì)分析表”也可以是另一個(gè)表的新“數(shù)據(jù)源”
如果我們要直接用這個(gè)案例生成“統(tǒng)計(jì)分銷(xiāo)表2”的話，在不使用VBA的情況下還是有點(diǎn)難度的。這里我們可以通過(guò)“統(tǒng)計(jì)分析表1”的內(nèi)容進(jìn)行一個(gè)過(guò)渡，把復(fù)雜問(wèn)題簡(jiǎn)單化。上例的黃色區(qū)域是函數(shù)部分，都是SUMIF和VLOOKUP函數(shù)的基礎(chǔ)用法，在這里就不列出來(lái)了，有興趣的同學(xué)可以按照這個(gè)思路去試著做一下。（不動(dòng)手練習(xí)，看多少文章都學(xué)不會(huì)?。?/p>
3、“多條件查詢(xún)”式的“統(tǒng)計(jì)分析表”，讓你的數(shù)據(jù)模型初具規(guī)模
看一下這個(gè)需求，作者相信每個(gè)Excel使用者都對(duì)這個(gè)需求充滿了向往，那就一起來(lái)看看是如何創(chuàng)建的吧，先看一個(gè)效果圖：
3-1 在源數(shù)據(jù)表的首列插入兩個(gè)空列，作為輔助列使用
在B3單元格輸入下面內(nèi)容后，下拉填充：
=IF($D$11="",1,IF(C3>=$D$11,1,0))+IF($D$12="",1,IF(C3<=$D$12,1,0))+IF($D$13="",1,IF(D3=$D$13,1,0))+IF($D$14="",1,IF(E3=$D$14,1,0))+IF($D$15="",1,IF(F3=$D$15,1,0))
函數(shù)解析：因?yàn)槲覀兊臈l件設(shè)置了5個(gè)內(nèi)容，當(dāng)條件為空（不填），或者滿足條件的時(shí)候，計(jì)為1否則為0，目的在于如果這個(gè)函數(shù)返回值是5，那么說(shuō)明5個(gè)條件均滿足，是我們需要的記錄條；如果不是5，那么說(shuō)明不是我們需要統(tǒng)計(jì)分析的記錄條。
在A3單元格輸入下面內(nèi)容后，下拉填充：
=IF(B3<>5,"",MAX($A$2:A2)+1)
函數(shù)解析：如果B列的值不等于5，則返回空值；否則返回此單元格在同列上方的區(qū)域中的最大值+1。目的是標(biāo)記出我們需要使用的記錄條，并且給予一個(gè)“關(guān)鍵字”的賦值。
這里使用了相對(duì)引用和絕對(duì)引用的知識(shí)，不是今天的重點(diǎn)，大家可以找找相關(guān)的內(nèi)容補(bǔ)充一下。
3-2 制作導(dǎo)出新數(shù)據(jù)的展示區(qū)域
C18單元格函數(shù)輸入后，復(fù)制此單元格，并選擇性粘貼——公式到C18:G25區(qū)域：
=IFERROR(VLOOKUP(ROW(C1),$A$2:$G$6,MATCH(C$17,$A$2:$G$2,0),0),"")
函數(shù)解析：通過(guò)ROW函數(shù)，可以返回行號(hào)，下拉填充后，就可以得到一串1、2、3……的序號(hào)，這樣就可以和輔助列2的“關(guān)鍵字”相對(duì)應(yīng)；再使用MATCH函數(shù)，可以得到字段在“源數(shù)據(jù)表”中的列序，以此作為VLOOKUP函數(shù)的第三參數(shù)，通過(guò)VLOOKUP函數(shù)就可以找到滿足條件的記錄條；最后再以IFERROR函數(shù)，去掉VLOOKUP函數(shù)產(chǎn)生的#N/A值。這樣一套完整的多條件查詢(xún)系統(tǒng)就建立完畢了。
3-3 進(jìn)一步完善“統(tǒng)計(jì)分析表”具有說(shuō)明類(lèi)的數(shù)據(jù)
案例中做的“銷(xiāo)量合計(jì)”就屬于說(shuō)明類(lèi)的數(shù)據(jù)，我們做出滿足條件的明細(xì)后，總不能讓別人再自己計(jì)算想要的數(shù)據(jù)吧！所以就需要我們將一些必要的數(shù)據(jù)在表頭的位置中羅列出來(lái)，還可以做出百分比、同期數(shù)據(jù)對(duì)比等等數(shù)據(jù)分析類(lèi)的數(shù)據(jù)，或者也可以將索引出來(lái)的明細(xì)作為數(shù)據(jù)源，做成圖表輔以可視化展示。
【編后語(yǔ)】Excel數(shù)據(jù)建模過(guò)程的原理部分就給大家介紹完了，能記下多少，又有多少內(nèi)容能夠應(yīng)用到實(shí)際工作中，就要看每個(gè)人的理解程度了。在這里還是要給大家一句箴言：任何技能都是練習(xí)出來(lái)的，多看多用多總結(jié)，是學(xué)習(xí)的必經(jīng)之路。
＊＊＊＊部落窩教育－excel統(tǒng)計(jì)分析表制作技巧＊＊＊＊
原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）
聯(lián)系客服
