編按：哈嘍,，大家好,！在日常工作中我們會(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)容必須是唯一存在的話(huà),，那么就應(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è)條件的判斷。在滿(mǎn)足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)“建模”的基本原理,！

2、“統(tǒng)計(jì)分析表”也可以是另一個(gè)表的新“數(shù)據(jù)源”

如果我們要直接用這個(gè)案例生成“統(tǒng)計(jì)分銷(xiāo)表2”的話(huà),，在不使用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è)需求充滿(mǎn)了向往,，那就一起來(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)條件為空（不填）,，或者滿(mǎn)足條件的時(shí)候,，計(jì)為1否則為0，目的在于如果這個(gè)函數(shù)返回值是5,，那么說(shuō)明5個(gè)條件均滿(mǎn)足,，是我們需要的記錄條,；如果不是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ù)就可以找到滿(mǎn)足條件的記錄條,；最后再以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ù)，我們做出滿(mǎn)足條件的明細(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)載）