編按：

如何打開(kāi)周報(bào)表模板不用做任何輸入或設(shè)置操作即可得到當(dāng)周報(bào)表呢？這需要做好幾個(gè)巧妙設(shè)置。第一，數(shù)據(jù)自動(dòng)按周引用原始數(shù)據(jù)文件；第二，實(shí)現(xiàn)原始數(shù)據(jù)文件的網(wǎng)絡(luò)共享填寫(xiě)，不用自己再匯總；第三，自動(dòng)判定打開(kāi)模板時(shí)的周數(shù)；第四，在模板中設(shè)置好包括打印區(qū)域在內(nèi)的所有樣式。

一、周報(bào)表的公式等設(shè)置

注意：讀者如果用本文素材練習(xí)，可以完全按文中公式輸入；如果是用自己公司的周報(bào)表練習(xí)，則需要根據(jù)實(shí)際修改公式。

Step1：添加驗(yàn)證數(shù)據(jù)

打開(kāi)“周報(bào).xlsx”，在“周報(bào)表”工作表中，選中B1:G1單元格將其合并。

然后定位到B1單元格，點(diǎn)擊“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證”，在“驗(yàn)證條件”選擇“來(lái)源”，來(lái)源數(shù)據(jù)選擇“=周次表!$C$1:$C$54”。

現(xiàn)在生成了下拉菜單。隨便選擇一個(gè)周數(shù)，如第49周。

Step2：設(shè)置周報(bào)標(biāo)題

選中A2:G2單元格，設(shè)置為跨列居中。

然后定位到A2單元格輸入公式“=B1&"工程結(jié)算申請(qǐng)表"”，即將B1的內(nèi)容和周報(bào)名稱(chēng)組合在一起。

Step3:設(shè)置明細(xì)數(shù)據(jù)按周數(shù)引用

定位到I4單元格輸入公式并按Ctrl+Shift+Enter組合鍵完成輸入：

=IFERROR(INDEX(引用表!A$1:A$200,SMALL(IF(引用表!$E$1:$E$200=MID($B$1,2,(FIND("周",$B$1)-2))*1,ROW($A$1:$A$200),4^8),ROW(1:1))),"")

公式解析：

（1）FIND("周",$B$1)-2)部分用于計(jì)算標(biāo)題中周數(shù)的字符數(shù)，如果周數(shù)是“2”等，字符數(shù)就是1；如果周數(shù)是“14”等，字符數(shù)就是2。

（2）MID($B$1,2,(FIND("周",$B$1)-2))*1部分是提取出周數(shù)文本，乘以1轉(zhuǎn)成數(shù)字。

（3）接著是套用經(jīng)典的萬(wàn)金油引用組合，不明白的可以看文章《Excel萬(wàn)金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀》。

（4）最后嵌套IFERROR函數(shù)，將錯(cuò)誤值顯示為空。

接著將公式向下、向右填充，這樣通過(guò)數(shù)組公式，就可以將符合要求的周次數(shù)據(jù)依次提取到I4:L24區(qū)域中。注意下拉后單擊“自動(dòng)填充項(xiàng)”，選擇“不帶格式填充”，避免設(shè)計(jì)好的表格格式別修改。

選中日期部分，設(shè)置格式為“短日期”恢復(fù)為日期數(shù)據(jù)。

Step4：將明細(xì)數(shù)據(jù)匯總

定位到B11單元格輸入公式“=SUMIF($I$4:$I$27,A11,$L$4:$L$27)”，向下填充完成各分公司周工作量數(shù)據(jù)統(tǒng)計(jì)。

公式解析：

典型的SUMIF函數(shù)條件求和，統(tǒng)計(jì)條件區(qū)域是$I$4:$I$25，統(tǒng)計(jì)條件是A11（即分公司名稱(chēng)），求和區(qū)域則為$L$4:$L$25。

再定位到C11單元格輸入公式“=B11*100”（每個(gè)工作量的單價(jià)是100）并下拉公式，最后進(jìn)行小計(jì)即可。

保存文件。

OK，到此我們就以小青公司周報(bào)為例基本完成了周報(bào)表模板的設(shè)計(jì)。剩下的是一些小細(xì)節(jié)。

二、三點(diǎn)便利設(shè)計(jì)實(shí)現(xiàn)直接打印

周報(bào)固定在周五生成報(bào)送。小青希望在檢查確認(rèn)各分公司都填報(bào)原始數(shù)據(jù)后，打開(kāi)“周報(bào).xlsx”文件，即可直接打印。

真的是要便利到死??！

第一，將原始數(shù)據(jù)文檔“協(xié)作數(shù)據(jù).xlsx”設(shè)置為共享。

文檔共享后，各部門(mén)、分公司可以通過(guò)局域網(wǎng)或者云盤(pán)共同填寫(xiě)，不用各自發(fā)送文件。至于如何共享，有需要的朋友可以留言。

第二，將周報(bào)表的周數(shù)默認(rèn)為本周（文件打開(kāi)時(shí)的周數(shù)）。

Step1：添加本周周次數(shù)據(jù)

（這就是我們?yōu)楹巫畛蹩罩艽伪淼?span>1行的原因。）

切換到“周次表”工作表中，定位到B1，輸入公式=WEEKNUM(TODAY(),2)。

定位到C1，輸入公式=VLOOKUP(B1,B2:C200,2,0)

Step2：在周報(bào)表設(shè)置公式引用本周周次

切換到“周報(bào)表”工作表，定位到B1單元格，輸入公式=周次表!C1。

第三，預(yù)設(shè)好報(bào)表布局和打印區(qū)域。

Step1：報(bào)表頁(yè)面布局

然后單擊“視圖→頁(yè)面布局格式”，調(diào)整行列大小、寬高，使得每頁(yè)內(nèi)容正好填滿頁(yè)面。還可以添加頁(yè)面頁(yè)腳，我們這里在頁(yè)腳處添加了頁(yè)碼。

Step2：設(shè)置打印區(qū)域

根據(jù)公司的要求，周報(bào)表控制兩個(gè)頁(yè)碼。點(diǎn)擊“視圖→分頁(yè)預(yù)覽”，檢查是否剛好只有兩頁(yè)。如果有多余空白頁(yè)面，譬如第3頁(yè)，這時(shí)，可以直接選中這一頁(yè)，然后按Delete鍵刪除。

刪除多余空白頁(yè)后，在區(qū)域內(nèi)選中A2:M26,右擊選擇“設(shè)置打印區(qū)域”，將第一行數(shù)據(jù)排除在打印區(qū)域之外。

完成上述的設(shè)置后，點(diǎn)擊“文件→打印”，可以在右側(cè)預(yù)覽打印效果，符合要求后即可完成設(shè)置了。

三、打印預(yù)覽效果

使用效果：

完成上述工作后，以后每個(gè)分公司的負(fù)責(zé)人，打開(kāi)服務(wù)器上共享文件“協(xié)作數(shù)據(jù).xlsx”，然后依次輸入自己分公司的數(shù)據(jù)。

小青則只需在每周五打開(kāi)本地的“周報(bào).xlsx”工作簿，檢查無(wú)誤后按下Ctrl+P打印即可。

如果需要查詢(xún)其他周次的報(bào)表內(nèi)容，那么在B1點(diǎn)擊下拉列表，選擇相應(yīng)的周次，報(bào)表的標(biāo)題和數(shù)據(jù)都會(huì)同步發(fā)生變化。

怎么樣？通過(guò)上述的設(shè)計(jì)，周報(bào)的制作是不是超簡(jiǎn)單了呢。如果您還在為周報(bào)制作頭疼，趕緊行動(dòng)起來(lái)吧！

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

如何提取品牌信息？LOOKUP函數(shù)有絕招！

沒(méi)有Textjoin函數(shù)，如何解決提取數(shù)據(jù)的問(wèn)題？

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者ITFANS；同時(shí)部落窩教育享有專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。