第五節(jié)
在本章前四節(jié)的內(nèi)容中，我們通過案例詳細(xì)展示了四套不同用途的財(cái)務(wù)工作表的設(shè)計(jì)方法，不過這四個工作表都屬于不需要編制者之外的其他人參與填寫的獨(dú)立編制模式。而在實(shí)際工作中，我們經(jīng)常會遇到這種情況：先設(shè)計(jì)好表格分發(fā)給其他部門或下屬公司，其他部門或下屬公司填好后傳回，然后進(jìn)行匯總統(tǒng)計(jì)。有些表格是需要定期填報(bào)匯總的，這些表格如果設(shè)計(jì)不好，既不方便下屬公司填報(bào)，也不方便集團(tuán)公司匯總。那怎么設(shè)計(jì)表格才能讓集團(tuán)公司可以快速合并匯總數(shù)據(jù)，下屬公司又能填報(bào)方便，且盡可能不重復(fù)輸入數(shù)據(jù)呢？
本節(jié)我們就以逸凡公司“營運(yùn)周報(bào)表”為例，對此類分發(fā)后又收回匯總的工作表進(jìn)行介紹，主要介紹此類表格的設(shè)計(jì)思路。這一節(jié)是第二章第三節(jié)理念和方法的具體應(yīng)用，通過本示例你將看到，原來集團(tuán)數(shù)據(jù)匯總可以如此簡單而快速。
一、工作需求
【案例5-5】逸凡公司為集團(tuán)公司，下轄礦井十余個（為便于展示，本示例只考慮四個），公司領(lǐng)導(dǎo)每周五需要了解下屬礦井本周（上周六到本周五）的營運(yùn)數(shù)據(jù)：進(jìn)尺、產(chǎn)量、銷量、回款情況、煤炭發(fā)熱量等指標(biāo)情況及當(dāng)月和年度預(yù)算完成進(jìn)度。逸凡公司財(cái)務(wù)人員收集下屬礦井的上述指標(biāo)后統(tǒng)一匯總呈報(bào)給公司領(lǐng)導(dǎo)，報(bào)送的表格既要有總體匯總數(shù)，也要有各公司當(dāng)期數(shù)據(jù)。
二、設(shè)計(jì)思路及應(yīng)實(shí)現(xiàn)的功能
設(shè)計(jì)總體要求：作為集團(tuán)的財(cái)務(wù)人員一定要有服務(wù)的理念，在設(shè)計(jì)表格時，除了要便于集團(tuán)公司匯總，還要考慮下屬礦井填列數(shù)據(jù)是否方便。匯總表格要使用公式實(shí)現(xiàn)下屬公司填報(bào)的數(shù)據(jù)自動匯總，不必手工填列、不重復(fù)抄填數(shù)據(jù)。
1.表格布局
（1）下屬公司的上報(bào)表格。
各礦井上報(bào)表格的列分別列示本月/本年實(shí)際數(shù)、本月/本年預(yù)算數(shù)、預(yù)算完成進(jìn)度等，表格的行分別列示各項(xiàng)營運(yùn)指標(biāo)：開拓進(jìn)尺、生產(chǎn)進(jìn)尺、產(chǎn)量、銷量、含稅銷售收入、銷售回款、平均售價、平均發(fā)熱量。各公司上報(bào)表格的布局參見圖5-105表格的格式（本案例的示例文件請參見“營運(yùn)周報(bào)”文件夾）。
考慮到數(shù)據(jù)是按周填報(bào)，并且還需要填報(bào)本月累計(jì)和本年累計(jì)數(shù)據(jù)，如果手工填列需要手工將本期數(shù)據(jù)加到上期累計(jì)數(shù)上去，填寫很麻煩且容易出錯。為避免手工填列這些數(shù)據(jù)，各礦井還應(yīng)該增加一個臺賬表格，使用求和公式根據(jù)臺賬數(shù)據(jù)自動統(tǒng)計(jì)本月累計(jì)和本年累計(jì)數(shù)據(jù)?！懊恐芘_賬”表格格式如圖5-106所示。
在臺賬表格填列每周數(shù)據(jù)，臺賬的列就是各項(xiàng)營運(yùn)指標(biāo)。為了提高填寫效率，上報(bào)表格的本周完成數(shù)直接采用查找引用公式從臺賬表格取數(shù)，上報(bào)表格的本月累計(jì)和本年累計(jì)數(shù)據(jù)使用求和公式自動計(jì)算臺賬數(shù)據(jù)生成。
（2）集團(tuán)公司的匯總表格。
集團(tuán)公司的匯總表格有兩種選擇。
方案1：所有礦井的所有指標(biāo)在一張表格列示，某一指標(biāo)下每個公司的數(shù)據(jù)在不同行展示，然后用一匯總行列示本指標(biāo)的匯總數(shù)據(jù)。具體格式如圖5-107所示。
缺點(diǎn)：不方便查看整體匯總數(shù)據(jù)，也不方便查看每個礦井的各項(xiàng)數(shù)據(jù)。
方案2：所有數(shù)據(jù)在同一工作簿列示，每個公司使用一個工作表，各公司表格結(jié)構(gòu)一致，集團(tuán)整體情況使用匯總表列示。
缺點(diǎn)：不方便對比查看某一指標(biāo)各公司的情況。
兩種方案各有優(yōu)缺點(diǎn)，但是第一種方案的缺點(diǎn)可以使用Excel的分組、篩選功能來克服。使用分組的分級顯示功能可以很方便地查看匯總數(shù)據(jù)，使用篩選功能可以很方便地查看某一礦井的各項(xiàng)指標(biāo)。因而，第一種方案更能滿足工作需求，我們采用第一種方案。
2.表格的公式
各礦井上報(bào)的表格應(yīng)該實(shí)現(xiàn)全自動取數(shù)，上報(bào)表格只需填列周數(shù)或日期，表格自動從臺賬表格取數(shù)：本周數(shù)使用VLOOKUP函數(shù)進(jìn)行查找引用，本月和本年累計(jì)數(shù)使用SUMIF函數(shù)進(jìn)行統(tǒng)計(jì)。
集團(tuán)公司的匯總表格應(yīng)該能使用公式從各礦井上報(bào)的表格中鏈接取數(shù)。
3.報(bào)表的后續(xù)使用
由于營運(yùn)周報(bào)是定期報(bào)表，匯總報(bào)表應(yīng)能實(shí)現(xiàn)自動更新或方便批量翻新。即：
如果上報(bào)的表格名稱是固定不變的，每周當(dāng)下屬公司報(bào)表上報(bào)后，將數(shù)據(jù)放入指定的文件夾，匯總表格應(yīng)能自動更新。
如果上報(bào)的表格名稱是按周數(shù)或日期命名，比如類似“甲公司營運(yùn)周報(bào)（5/17- 5/23）”的格式，那么上報(bào)表格的名稱應(yīng)規(guī)范，要方便使用查找替換實(shí)現(xiàn)批量翻新（報(bào)表翻新的技術(shù)請參閱第二章第三節(jié)）。
三、知識點(diǎn)裝備
在閱讀本節(jié)下面的內(nèi)容前，請各位讀者朋友首先確認(rèn)大腦中是否已經(jīng)基本裝備了圖5-109中的相關(guān)知識點(diǎn)。
四、主要信息的公式設(shè)計(jì)
1.建立表格框架
根據(jù)前文所述的表格設(shè)計(jì)思路，首先要建立表格整體框架，各礦井表格的結(jié)構(gòu)應(yīng)該完全一致（一致性原則）。具體格式如圖5-105所示。
圖5-105是各礦井上報(bào)表格的樣式，也是集團(tuán)公司匯總表格的雛形。集團(tuán)公司的匯總表由于需要在各指標(biāo)行下列示各礦井的數(shù)據(jù)，故還需在各指標(biāo)行下都插入四行。插入后表格樣式如圖5-107所示。在插入行后，表格由原來的12行變成了44行，很不方便查看整體數(shù)據(jù)，這個問題可以使用分組的分級顯示功能來解決。
選定集團(tuán)匯總表格的第6～9行，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡的“分級顯示”組中的“創(chuàng)建組”按鈕，將6～9行組合在一起。然后分別選定11～14行、16～19行……41～44行，重復(fù)以上步驟，分別創(chuàng)建組。創(chuàng)建組后，表格如圖5-107所示，在表格行號的左側(cè)增加了“分級顯示”的按鈕。如果點(diǎn)擊“1”，表示顯示第一級，則表格如圖5-110所示。
如果要顯示某一指標(biāo)的明細(xì)數(shù)據(jù)，則點(diǎn)擊該指標(biāo)前的“+”號即可。
至此，集團(tuán)匯總表格的框架和下屬礦井上報(bào)表格的格式已經(jīng)確定。下面我們來確定下屬礦井每周數(shù)據(jù)的臺賬。簡單來說，臺賬表格應(yīng)將各項(xiàng)指標(biāo)分別列示在各列，同時將第一列增加一個本周日期區(qū)間的字段即可，如前文圖5-106所示。
2.編制計(jì)算公式
我們先來看下屬礦井報(bào)表的公式設(shè)置。
（1）每周起止日期的自動計(jì)算。
如果每周的起止天數(shù)手工錄入的話，由于每年有52周左右，周數(shù)較多，費(fèi)時費(fèi)力且容易出錯，故應(yīng)該考慮用公式來實(shí)現(xiàn)。用公式自動計(jì)算出起止日期，生成類似“1/1-1/3”起止日期的字符串。
由于每周的天數(shù)都是固定的七天，如果知道每周起始日期是每周的第幾天就可推算出每周結(jié)束的具體日期，具體公式如下：
=每周起始日+（7-每周起始日在本周的第幾天）
計(jì)算日期在本周的第幾天可用WEEKDAY函數(shù)，此函數(shù)返回某日期為星期幾。當(dāng)使用不同的參數(shù)，當(dāng)參數(shù)為2時，返回?cái)?shù)字1（星期一）到數(shù)字7（星期日）；當(dāng)參數(shù)為16時，返回?cái)?shù)字1（星期六）到數(shù)字7（星期五）。下面以“2014-1-1”為例簡單介紹此函數(shù)兩種常用參數(shù)的使用。
如果每周起止日期從周一到周日，那么用函數(shù)公式“=WEEKDAY("2014/1/1",2)”可計(jì)算出它是本周的第三天；
如果每周起止日期從周六到周五，那么用函數(shù)公式“=WEEKDAY("2014/1/1",16)”可計(jì)算出它是本周的第五天。
關(guān)于此函數(shù)的參數(shù)及詳細(xì)使用請參閱Excel幫助。
如前所述，逸凡公司周報(bào)統(tǒng)計(jì)日期是從上周六到本周五，為簡化計(jì)算，同時也是為方便統(tǒng)計(jì)本月和本年數(shù)據(jù)，故計(jì)算起止日期增加兩個輔助列（輔助列化繁為簡的思想，請參見第二章第六節(jié)）。第一列為每周起始日期，第二列為每周截止日期。每年第一周的起始日期為固定的1月1日，即在A2單元格直接輸入“2014-1-1”。第一周的截止日期可以用公式計(jì)算，B2單元格的公式為：
=A2+7-WEEKDAY(A2,16)
但是此公式計(jì)算周截止日期有點(diǎn)小問題：當(dāng)周報(bào)遇到月底時應(yīng)該以最后一天為截止日期，而不是以周自然天數(shù)來計(jì)算。故第一周結(jié)束日期（B2單元格）的公式應(yīng)該用IF函數(shù)來進(jìn)行邏輯判斷：當(dāng)使用上述公式計(jì)算出的日期跨月時，應(yīng)該采用最后一天，否則才能使用上述公式。故此公式完善為：
=IF(MONTH(A2+7-WEEKDAY(A2,16))< >MONTH(A2),EOMONTH(A2,0), A2+7-WEEKDAY(A2,16))
然后執(zhí)行列填充至本年度最后一天即可（EOMONTH函數(shù)的用法請參見第四章相關(guān)內(nèi)容）。
第二周的起始日期為第一周截止日期的第二天，由于日期型數(shù)據(jù)可以用簡單的加減法，故A3單元格公式為：=B2+1
然后執(zhí)行列填充至本年度最后一周即可。
增加C列為每周的序號列，增加D列為起止日期列，D2公式為：
=TEXT(A2,"m/d")&"-"&TEXT(B2,"m/d")
然后拖動填充柄下拉填充。
公式解釋：
“=TEXT(A2,"m/d")”是將A2單元格的日期按“月/日”格式顯示，如“1/1”；如果要顯示為“01/01”，則使用公式“=TEXT(A2,"mm/dd")”。
增加輔助列后每周臺賬表格的結(jié)構(gòu)如圖5-111所示。
（2）“本周完成”的公式。
如前面設(shè)計(jì)思路所述，各礦井上報(bào)表格的數(shù)據(jù)都是從“每周臺賬”表格取數(shù)計(jì)算生成的。“本周完成”數(shù)可使用VLOOKUP函數(shù)來查找引用。為了能直接使用VLOOKUP函數(shù)，我們應(yīng)該直接在C3單元格直接輸入每周的起止日期。如“5/17-5/23”，但此日期不能作為表格的列標(biāo)題，表格的列標(biāo)題應(yīng)該為“本周完成(5/17-5/23)”的類似格式。
為同時滿足兩種要求，我們可以將C3單元格設(shè)置為自定義格式“"本周完成("@)”，將“5/17-5/23”直接顯示為“本周完成(5/17-5/23)”。這樣即可直接使用VLOOKUP查找引用，也可滿足作為列標(biāo)題的需要。
通過以上設(shè)置后，“上報(bào)表格”C5單元格的公式可以直接使用VLOOKUP函數(shù)：
=VLOOKUP($C$3,每周臺賬!$D$2:$L$62,2,0)
此公式有點(diǎn)小問題，不能直接下拉填充至C6:C12單元格區(qū)域。
C6和C7單元格的公式為：
=VLOOKUP($C$3,每周臺賬!$D$2:$L$62,3,0)
=VLOOKUP($C$3,每周臺賬!$D$2:$L$62,4,0)
通過觀察可以發(fā)現(xiàn)A5:A12各項(xiàng)指標(biāo)的順序與“每周臺賬”表格各項(xiàng)指標(biāo)的順序完全一致，C6:C12單元格區(qū)域的公式只是引用的列不同，故為了增加公式的可拓展性（可擴(kuò)展性原則），可以將C5單元格的公式修改完善為：
=VLOOKUP($C$3,每周臺賬!$D$2:$L$62,ROW()-3,0)
然后直接拖動填充柄下拉填充即可。
在“上報(bào)表格”C3單元格手工輸入每周的起止日期容易出錯，一旦錄入錯誤，“本周完成、本月達(dá)成、累計(jì)達(dá)成”將無法計(jì)算出正確值。為避免錄入錯誤，應(yīng)該使用數(shù)據(jù)有效性來限制錄入的值，并且數(shù)據(jù)有效性還提供下拉列表，不必通過鍵盤錄入，直接用鼠標(biāo)點(diǎn)擊選取下拉值還可提高工作效率。設(shè)置數(shù)據(jù)有效性的方法：
點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡→點(diǎn)擊“數(shù)據(jù)工具”組中的“數(shù)據(jù)有效性”按鈕→在彈出的“數(shù)據(jù)有效性”對話框按圖5-112進(jìn)行設(shè)置。
（3）“本月達(dá)成”的公式。
我們知道，要計(jì)算“本月達(dá)成”可以使用SUMIF來進(jìn)行條件求和。那如何來構(gòu)架這個求和的條件呢？我們觀察“上報(bào)表格”C3單元格和“每周臺賬”表格D列可以發(fā)現(xiàn)，直接提取起始日期的月份作為模糊求和的條件即可，即從“5/17-5/23”提取出“5/”，然后對所有以“5/”開頭的記錄進(jìn)行求和的結(jié)果，即是5月達(dá)成數(shù)。即求和條件為：LEFT($C$3,FIND("/",$C$3))&"*"。
因而，上報(bào)表格D5單元格公式為：
=SUMIF(每周臺賬!$D$2:$D$62,LEFT($C$3,FIND("/",$C$3))&"*",每周臺賬!$E$2:$E$62)
當(dāng)然，也可以使用此公式：
=SUMPRODUCT((MONTH(每周明細(xì)!A2:A62)=--LEFT(C3,FIND("/",C3)-1))*每周明細(xì)!E2:E62)
D6:D10單元格區(qū)域的公式使用以上公式類推即可。
計(jì)算平均發(fā)熱量時不能簡單求和，而是應(yīng)該對當(dāng)月發(fā)熱量進(jìn)行算術(shù)平均，故D12單元格的公式為：
=SUMIF(每周臺賬!$D$2:$D$62,LEFT($C$3,FIND("/",$C$3))&"*",每周臺賬!$L$2:$L$62)/SUMPRODUCT((MONTH(每周臺賬!A2:A62)=(--LEFT($C$3,FIND("/",$C$3)-1)))*(每周臺賬!L2:L62< >""))
（4）“累計(jì)達(dá)成”的公式。
要計(jì)算累計(jì)達(dá)成也是要使用SUMIF函數(shù)來求和。要統(tǒng)計(jì)累計(jì)達(dá)成肯定是要統(tǒng)計(jì)小于等于截止日期指標(biāo)之和，因而，其條件應(yīng)該為：
"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3))
公式解釋：RIGHT函數(shù)即截取C3單元格的截止日期，即“5/17-5/23”中的“5/23”，截取出來“5/23”為文本數(shù)據(jù)，不能進(jìn)行運(yùn)算，故還需使用兩個負(fù)號將其轉(zhuǎn)化為日期數(shù)據(jù)（乘以負(fù)壹，再乘以負(fù)壹，負(fù)負(fù)得正。類似于使用“選擇性粘貼-運(yùn)算”將文本數(shù)值轉(zhuǎn)換為數(shù)字型）。
G5單元格累計(jì)達(dá)成的公式為：
=SUMIF(每周臺賬!$A$2:$A$62,"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3)),每周臺賬!$E$2:$E$62)
G6:G10單元格區(qū)域的公式以此類推，不詳述。平均發(fā)熱量G12單元格的公式為：
=SUMIF(每周臺賬!$A$2:$A$62,"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3)),每周臺賬!$L$2:$L$62)/COUNTIF(每周臺賬!$A$2:$A$62,"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3)))
各項(xiàng)指標(biāo)的完成進(jìn)度使用類似F5單元格的公式：
=IFERROR(D5/E5,"")
（5）集團(tuán)公司匯總表格的公式。
如果下屬礦井每月上報(bào)的表格名稱固定不變，均為“營運(yùn)周報(bào)（甲公司）”這種格式，那么，集團(tuán)公司匯總表格中各礦井的各項(xiàng)指標(biāo)可以使用簡單的單元格鏈接。如：D6單元格甲公司開拓進(jìn)尺指標(biāo)“本周完成”的公式為：
='[營運(yùn)周報(bào)（甲公司）.xlsx]上報(bào)'!C5
D7單元格乙公司開拓進(jìn)尺指標(biāo)“本周完成”的公式為：
='[營運(yùn)周報(bào)（乙公司）.xlsx]上報(bào)'!C5
其他公司其他指標(biāo)類推。
以后每月只需將舊表格替換成最新的表格即可。但為了保證數(shù)據(jù)的正確引用，需確保以下事項(xiàng)：
1）各公司上報(bào)表格的工作簿名稱和工作表名稱保持不變，“營運(yùn)周報(bào)（甲公司）”不能改成“營運(yùn)周報(bào)[甲公司]”等，否則將出現(xiàn)鏈接錯誤，無法引用數(shù)據(jù)。
2）各公司上報(bào)表格布局不能改變，否則將出現(xiàn)數(shù)據(jù)引用錯位。
為了防范上述事項(xiàng)的發(fā)生，在將上報(bào)表格分發(fā)給各礦井之前，應(yīng)對工作表和工作簿加密碼保護(hù)，防止下屬礦井修改表格的名稱和結(jié)構(gòu)（安全性原則）。操作方法：
先選定需要錄入數(shù)據(jù)的單元格→點(diǎn)擊右鍵——設(shè)置單元格格式→“保護(hù)”選項(xiàng)卡→將“鎖定”選項(xiàng)前的勾去掉→點(diǎn)擊“確定”退出（見圖5-113）。
然后點(diǎn)擊【審閱】選項(xiàng)卡下的“保護(hù)工作表”按鈕→在彈出的“保護(hù)工作表”對話框輸入密碼，然后確定退出（見圖5-114）。
保護(hù)好各工作表后，還可設(shè)置保護(hù)工作簿，操作方法：點(diǎn)擊【審閱】選項(xiàng)卡下的“保護(hù)工作簿”按鈕→在彈出的“保護(hù)結(jié)構(gòu)和窗口”對話框輸入密碼，然后確定退出（見圖5-115）。
當(dāng)勾選保護(hù)“結(jié)構(gòu)”選項(xiàng)后，將不能進(jìn)行以下操作：查看已隱藏的工作表；移動、刪除、隱藏或更改工作表的名稱；插入新工作表或圖表工作表；將工作表移動或復(fù)制到另一工作簿中。
當(dāng)勾選保護(hù)“窗口”選項(xiàng)后，將不能進(jìn)行以下操作：打開工作簿時，更改工作簿窗口的大小和位置；移動窗口、調(diào)整窗口大小或關(guān)閉窗口。
3.報(bào)表的后續(xù)使用
如果上報(bào)的表格名稱是按周數(shù)或日期命名，比如類似“甲公司營運(yùn)周報(bào)（5/17-5/23）”的格式，仍可使用簡單的單元格鏈接。但為了能使用查找替換將匯總表表格的公式對上周表格的引用更改為對本周表格的引用。要做到以下兩點(diǎn)：
（1）上報(bào)表格的名稱應(yīng)規(guī)范，同類型工作簿、工作表應(yīng)使用統(tǒng)一格式的文件名（一致性原則）。如“甲公司營運(yùn)周報(bào)（5/17-5/23）”“丁公司營運(yùn)周報(bào)（5/24-5/30）”。
（2）各公司表格應(yīng)放在同一文件夾下（整體性原則）。
在滿足以上條件后，收集齊各礦井下一周最新報(bào)表時，將上周各公司的報(bào)表刪除，將本周各公司上報(bào)的表格放入營運(yùn)周報(bào)文件夾，然后打開“集團(tuán)公司匯總”表格，使用查找替換，比如：查找“（5/17-5/23）”替換為“（5/24-5/30）”，即可實(shí)現(xiàn)報(bào)表的快速翻新，具體操作請參閱第二章第三節(jié)報(bào)表翻新的技術(shù)。
