10.2 本期累計

上節的銷售日報過於簡單,假設我們希望在日報上不僅要體現當天的銷售情況,還要體現從月初到日報當天的累積銷售情況,就像下圖所示的這樣:

104 帶本期累計的銷售日報

10.2.1 問題分析

來分析一下這個問題的解決思路:當日資料的統計很容易,我們在8.1節已經實現了,主要的問題的本月累計怎麼做。注意一點,日報是要每天都要做的,也就是說,當我們作5日的日報時,4日的日報已存在了。這就給我們提供了解決問題的思路:

今天的“本月累計”= 今天的當日資料+昨天日報上的“本月累計”數

具體來說,在範本上增加幾個欄位:在G列增加“昨天累計數量”,H列增加昨天累計金額,範本全部設計好之後,可以把G列和H列隱藏。

105 在日報上增加幾個隱藏欄位

用表間公式去查詢“昨天日報的本月累計”

Excel公式計算今天的本月累計:E5=C5+G5,餘類推。

另外,因為包含了“本月累計”,所以不能像上節那樣,把產品名稱和當日數量、當日金額一次性提取出來,這樣會導致某產品由於當天沒有訂單就沒有被包含在當天的日報上,但該產品可能當月的前些日子是有訂單的,這樣的日報無法正確地反映每種產品的本月累計。

分析就到這堙A下面實際地把這張日報做出來。

10.2.2 建立範本《新日報》

1.  按圖10-5所示表樣建立範本《新日報》

2.  定義單一資料項目:新日報_主表

主鍵

區域

欄位名

資料類型

必填

填寫規範

其他

C2

日期

日期

 

當前日期

 

 

3.  選中B5:H14,定義重復資料項。注意第一步的上方標題行數要輸入2

106

4.  最後定義好的資料表如下:

10-7

10.2.3 第一條公式----提取所有產品

在《新日報》範本上新建如下的表間公式

8-8

注意兩處:1)應用方式我們選擇了“打開後自動執行(初始填報)”,效果就是,當我們填報的時候,空白日報一打開,這條表間公式馬上就執行。2)在顯示方式處,我們選中了“重復資料只顯示一次”。這是因為物料表中可能有這樣的資料:名稱相同,規格不同,所以如果全部提取出來會出現同樣的品名重複顯示好幾行的情況,此選項用於保證重複的資料只顯示一次。

10.2.4 按行(列)匹配

什麼叫按行(列)匹配

8.2.3,我們定義了單獨的一條表間公式,把物料表中的全部品名都提取出來,填充到了產品名稱欄。假設物料表中共有四種產品ABCD,那麼應用了“提取所有產品”表間公式後,日報的內容如下:

8-10      產品名稱事先寫好

再假設525日的只有B產品和C產品的訂單,參照8.2.3,我們能統計出下面的結果:

產品名稱

當日數量

當日金額

B

100

120000

C

60

80000

 

這個統計的結果應該如何體現到日報上呢?

注意到日報上的產品名稱已經事先寫好了,所以填充統計資料的時候應該考慮到每種產品名稱所在的位置:B產品在第6行,所以應該把100填到C6120000填到D6C產品在第7行,所以應該把60填到C780000填到D7。結果如下:

8-11      “按行匹配”產品名稱,填充統計結果

這種填充方式稱為“按行(列)匹配”。

具體來說,就是依照表間公式的查詢結果中的某個部分(在這堿O“產品名稱”),到工作表上找到相同的取值,確定其所在的行(或列),然後把查詢結果的其餘部分填充到對應的行(或列)上。

定義按行列匹配的表間公式

在新日報上定義表間公式,其來源資料和篩選條件均和8.1節的公式相同,僅填充方式有變化,如下圖8-12,對“產品名稱”要選擇“按行(列)匹配”

8-12

完整的表間公式如下:

8-13

10.2.5 滾動累加

現在來提取昨天日報上的累計數,定義表間公式如下:

8-14

在這條公式的篩選條件中,用到了三個日期函數:年份值、月份值和日期值。

10.2.6 總結與思考

至此,我們已經完整地實現了《新日報》範本,總結一下,本範本的設計要點包括:

1.  設計兩個隱藏欄位:昨天累計數量和昨天累計金額

2.  分別提取當天的統計資料以及昨天日報的累計資料,用到了按行列匹配。

3.  用表間公式查詢資料,用Excel公式計算結果,二者結合起來。

下面,請大家多輸入一些訂單,連續做幾天的日報,進一步理解和體會本範本的設計要點