編按：

哈嘍，大家好！在日常工作中我們會構(gòu)建很多的表格，其實這些表格無論多少，都可以歸結(jié)為三大類，分別為“源數(shù)據(jù)表”、“關系信息表”和“統(tǒng)計分析表”。上次我們提到了一套表格“統(tǒng)計的核心”——“源數(shù)據(jù)表”，今天繼續(xù)為大家分享“關系信息表”和“統(tǒng)計分析表”的創(chuàng)建過程。（本篇為該系列文章的下篇）

【前言】

繼續(xù)前篇內(nèi)容，當我們有了一個規(guī)范的《源數(shù)據(jù)表》之后，接下來就是需要處理這些數(shù)據(jù)的過程了，總不能拿著一張明細表給老板交差吧？！下面就來看看在后續(xù)的表格制作中，我們還需要注意些什么。

二、“關系信息表”是一套表格“關聯(lián)的紐帶”

“關系信息表”的作用是對“源數(shù)據(jù)表”中某一個或多個字段內(nèi)容的信息補充。其建表規(guī)則與“源數(shù)據(jù)表”基本相同。但是“源數(shù)據(jù)表”中的“關鍵字”可以多次重復出現(xiàn)，而“關系信息表”中的“關鍵字”必須是唯一的。用一個圖例輔助說明一下：

因為“關系信息表”的作用是補充說明，所以它的一些信息內(nèi)容，經(jīng)常被我們索引到其他表中使用。如果你懂得Vlookup函數(shù)被搜索區(qū)域的首列內(nèi)容必須是唯一存在的話，那么就應該明白這個表中的索引值必須保證是唯一存在的重要性了。

謹記良言：不要在建表之初，給自己在以后使用的時候挖坑！

除了這些信息性質(zhì)的內(nèi)容，還有一些輔助計算的說明性表格也屬于此類“關系信息表”的范疇，構(gòu)建表格的時候，需要我們單獨的列出一個Sheet以供引用。

1. 等級評定

此類問題，如果在沒有“關系信息表”輔助計算的情況下，我們需要使用常量數(shù)組的方式，用函數(shù)來解決。

B3單元格函數(shù)：=LOOKUP(A3,{0,50,100,200},{"D","C","B","A"})

不僅這樣寫函數(shù)比較麻煩，而且也增加了寫函數(shù)的難度，起碼只有會常量數(shù)組的同學能寫出來，否則就要用一串If函數(shù)嵌套解決。并且上面的這種方式有一個很大的弊端，如果評級條件發(fā)生改變，我們就又需要重新設定函數(shù)內(nèi)容。

但是如果我們增加一個“關系信息表”，用表的形式來表述這段文字，那么上面的這些問題就都可以解決，如下：

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é)果，但是考慮到以后的評級標準可能會變動，所以我們加入了Offset函數(shù)（往期有教程，不做贅述）形成一個動態(tài)的引用，所以無論評級怎么改變、增加，統(tǒng)計表中的“等級”字段都會自動調(diào)整。

2. 規(guī)定時間范圍內(nèi)的有效值

這類問題，在日常工作中也很普遍，比如說單價的問題，無論是進貨價格還是銷售價格，都不可能是一成不變的，會根據(jù)市場原材料的價格時常做出上調(diào)、下調(diào)。如果我們依然使用固定值，就經(jīng)常需要更改數(shù)據(jù)，那么很容易就會因漏改、錯改造成數(shù)據(jù)錯誤。所以，我們還是需要“關系信息表”來做輔助性計算，以此方式也能最好的規(guī)避數(shù)據(jù)錯誤的風險。

本例中使用了LOOKUP(1,0/(條件)......結(jié)構(gòu)，完成了多條件查詢。（不熟悉該結(jié)構(gòu)的小伙伴可以查看往期教程《VLOOKUP&LOOKUP雙雄戰(zhàn)（五）：野馬崛起！》）用這樣的處理方式就可以形成一個很好的價格自動引用的效果了。

三、“統(tǒng)計分析表”是一套表格“效率的表現(xiàn)”

“統(tǒng)計分析表”是按照“源數(shù)據(jù)表”的內(nèi)容，結(jié)合“關系信息表”的說明（或直接統(tǒng)計），根據(jù)具體統(tǒng)計分析的需求，使用Excel的技術(shù)手段形成自動化報表，作為最終數(shù)據(jù)展示的過程。

1、建模的基本原理

統(tǒng)計分析是用“表”的形式表現(xiàn)出來的，但在實際的Excel操作上，其實是一個運算的過程，可以通過“源數(shù)據(jù)表”直接得到我們需要的內(nèi)容，也可以結(jié)合“關系信息表”一起分析，具體問題具體分析。例如下面的數(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ù)的典型用法，在這里需要強調(diào)的是，關于日期范圍的條件判斷是對起始日期、截止日期兩個條件的判斷。在滿足A3:A6區(qū)域大于等于起始日期的A27單元格，且A3:A6區(qū)域小于等于截止日期的B27單元格，且C3:C6區(qū)域中等于C25單元格的條件下，匯總D3:D6區(qū)域的銷量。然后復制函數(shù)，復選中C27:C29、E27:E29、G27:G29，再選擇性粘貼為公式填充，一定要注意相對引用和絕對引用的使用。

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ù)解析：

這個函數(shù)看著有一點不好理解吧，如果嵌套函數(shù)不好理解的時候，我們可以拆分來看。OFFSET函數(shù)的返回值，被作為VLOOKUP函數(shù)的第二參數(shù)；而MATCH函數(shù)和COUNTIF函數(shù)被作為OFFSET函數(shù)的第二、四參數(shù)。

（1）通過MATCH函數(shù)，確定《關系信息表》中起始日期第一次出現(xiàn)的序號，作為行偏移的參數(shù)，再通過COUNTIF函數(shù)，確定《關系信息表》中本月中的等級條件有幾行，作為行擴展的參數(shù)；

（2）OFFSET通過5個參數(shù)的運算，返回了當月等級評定標準的區(qū)域范圍，并作為VLOOKUP的第二參數(shù)參與運算；

（3）最后通過VLOOKUP的模糊查詢找到對應的等級。

然后復制函數(shù)，復選中D27:D29、F27:F29、H27:H29，再選擇性粘貼為公式，進行填充。同樣需要注意相對引用和絕對引用的使用。

篇幅有限，案例中的數(shù)據(jù)內(nèi)容比較少，但是意圖和思路還是可以表述清晰：銷量是基于“源數(shù)據(jù)表”使用函數(shù)得到每個銷售員每個月的銷量合計；等級是按照返回的銷量結(jié)合“關系信息表”的各月各類參數(shù)使用函數(shù)得到。上面的這個思路，就是我們俗稱“建?！钡幕驹?！

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

如果我們要直接用這個案例生成“統(tǒng)計分銷表2”的話，在不使用VBA的情況下還是有點難度的。這里我們可以通過“統(tǒng)計分析表1”的內(nèi)容進行一個過渡，把復雜問題簡單化。上例的黃色區(qū)域是函數(shù)部分，都是sumif和vlookup函數(shù)的基礎用法，在這里就不列出來了，有興趣的同學可以按照這個思路去試著做一下。（不動手練習，看多少文章都學不會！）

3、“多條件查詢”式的“統(tǒng)計分析表”，讓你的數(shù)據(jù)模型初具規(guī)模

看一下這個需求，作者相信每個Excel使用者都對這個需求充滿了向往，那就一起來看看是如何創(chuàng)建的吧，先看一個效果圖：

3.1 在源數(shù)據(jù)表的首列插入兩個空列，作為輔助列使用

在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ù)解析：

因為我們的條件設置了5個內(nèi)容，當條件為空（不填），或者滿足條件的時候，計為1否則為0，目的在于如果這個函數(shù)返回值是5，那么說明5個條件均滿足，是我們需要的記錄條；如果不是5，那么說明不是我們需要統(tǒng)計分析的記錄條。

在A3單元格輸入下面內(nèi)容后，下拉填充：

=IF(B3<>5,"",MAX($A$2:A2)+1)

函數(shù)解析：

如果B列的值不等于5，則返回空值；否則返回此單元格在同列上方的區(qū)域中的最大值+1。目的是標記出我們需要使用的記錄條，并且給予一個“關鍵字”的賦值。

這里使用了相對引用和絕對引用的知識，不是今天的重點，大家可以找找相關的內(nèi)容補充一下。

3.2 制作導出新數(shù)據(jù)的展示區(qū)域

C18單元格函數(shù)輸入后，復制此單元格，并選擇性粘貼——公式到C18:G25區(qū)域：

=IFERROR(VLOOKUP(ROW(C1),$A$2:$G$6,MATCH(C$17,$A$2:$G$2,0),0),"")

函數(shù)解析：

通過Row函數(shù)，可以返回行號，下拉填充后，就可以得到一串1、2、3……的序號，這樣就可以和輔助列2的“關鍵字”相對應；再使用Match函數(shù)，可以得到字段在“源數(shù)據(jù)表”中的列序，以此作為Vlookup函數(shù)的第三參數(shù)，通過Vlookup函數(shù)就可以找到滿足條件的記錄條；最后再以Iferror函數(shù)，去掉Vlookup函數(shù)產(chǎn)生的#N/A值。這樣一套完整的多條件查詢系統(tǒng)就建立完畢了。

3.3 進一步完善“統(tǒng)計分析表”具有說明類的數(shù)據(jù)

案例中做的“銷量合計”就屬于說明類的數(shù)據(jù)，我們做出滿足條件的明細后，總不能讓別人再自己計算想要的數(shù)據(jù)吧！所以就需要我們將一些必要的數(shù)據(jù)在表頭的位置中羅列出來，還可以做出百分比、同期數(shù)據(jù)對比等等數(shù)據(jù)分析類的數(shù)據(jù)，或者也可以將索引出來的明細作為數(shù)據(jù)源，做成圖表輔以可視化展示。

【編后語】

Excel數(shù)據(jù)建模過程的原理部分就給大家介紹完了，能記下多少，又有多少內(nèi)容能夠應用到實際工作中，就要看每個人的理解程度了。在這里還是要給大家一句箴言：任何技能都是練習出來的，多看多用多總結(jié)，是學習的必經(jīng)之路。

本文配套的練習課件請加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

OFFSET函數(shù)的應用（上）《Excel進階之路必學函數(shù)：動態(tài)統(tǒng)計之王——OFFSET（上篇）》

OFFSET函數(shù)的應用（下）《Excel進階之路必學函數(shù)：動態(tài)統(tǒng)計之王——OFFSET（下篇）》

多條件查詢 《VLOOKUP&LOOKUP雙雄戰(zhàn)（五）：野馬崛起！》