題 計算由數據透視表生成的“0”股票值


我有一個庫存分類帳。它看起來像這樣:

Tran Type         Txn Date     Item     Quantity
Opening Balance   6/30/12      Item1     4
Opening Balance   6/30/12      Item2     7
Shipping          7/14/12      Item2    -1
Opening Balance   6/30/12      Item3     3
Shipping          7/2/12       Item3    -1
Opening Balance   6/30/12      Item4     5
Shipping          7/3/12       Item4    -1
Shipping          7/3/12       Item4    -1
Shipping          7/3/12       Item4    -1
Shipping          7/5/12       Item4    -1
Shipping          7/5/12       Item4    -1
Receiving         7/9/12       Item4    10

分類賬顯然只標記給定時期的每筆交易:期初餘額,發貨,收貨和退貨。

我使用該庫存分類帳來形成一個數據透視表,如下所示:

Sum of Quantity     Column Labels
Row Labels          6/30/12     7/2/12     7/3/12     7/5/12     7/6/12     7/9/12
Item1                 4           4           4          4          4          4
Item2                 7           7           7          7          7          7
Item3                 3           2           2          2          2          2
Item4                 5           5           2          0          0         10

數據透視表的功能是保持每天每個項目的庫存水平的運行總計。數據透視表格式很有用,因為它為我們的倉庫處理任何項目的交易的每一天維護這些總計,而不是到源數據,它只列出了特定項目發生的事務日期。

使用數據透視表中的此運行總數據,我想跟踪每個項目的庫存數量為0的天數。這裡有一個棘手的部分:我希望將此計數作為數據透視表的一部分,以便在按月/季度/年對數據進行分組時計數將是動態的(因此我們可以看到按項目計算的總天數缺貨每個時期)。

如果我可以使用基本數據做一些事情,這將允許我有這個缺貨信息,那很好。如果我可以直接在數據透視表中進行,那就更好了。

另一個更加空白的解決方案將允許我在項目達到零時生成連續幾天缺貨的數據,然後在數據透視表中按時間段查看此平均值。我知道大部分內容都必須在源數據中完成,我甚至知道它是如何完成的,但是我不能在公式中把它串起來。首先,我按日期而不是按項目名稱對數據進行排序。我創建了一個新列以保持運行庫存總計(“運行總計”是列名稱)。一旦運行總數達到零,我需要找到Date晚於Current事務的下一行,其中Item名稱相同,並且事務類型為“receive”。從那裡,我需要找到交易日期之間的差異。也有人可以幫我提出一個配方嗎?

最後,由於我們的數據只包含倉庫處理交易的天數,我想知道是否有辦法獲得數據透視表上顯示的缺失日期(即周末和假日)?這不是什麼重要的事情,但它會給最高管理員留下深刻的印象。

編輯: 在看到這裡的許多回復後,我已經開放嘗試通過Access或SQL Server解決此問題,我已經轉發了問題 堆棧溢出 重點是使用查詢和計算列來計算出來。

也就是說,如果有人有任何想法,我仍然非常願意通過excel解決這個問題!由於我可以在源數據中獲得運行總計,我想知道我是否可以使用新列中的if語句執行Out of Stock。

如果運行總計為零,那麼我需要查找同一項目的下一個事務,其中事務類型為“正在接收”,然後將該下一個事務的日期返回到列中。不過,我不確定如何將所有這些元素組合成一個公式。一旦我返回下一個接收交易的日期,我就可以減去當前行的Txn日期,以獲得天數的差異。

它看起來像這樣:

Tran Type         Txn Date     Item     Quantity  Stock   Out of Stock
Opening Balance   6/30/12      Item4     5          5
Shipping          7/3/12       Item4    -1          4
Shipping          7/3/12       Item4    -1          3
Shipping          7/3/12       Item4    -1          2
Shipping          7/5/12       Item4    -1          1
Shipping          7/5/12       Item4    -1          0         4 Days
Receiving         7/9/12       Item4    10         10

從那裡,我可以按項目或其他方式執行另一個數據透視表。


4
2017-07-16 14:22


起源


啊!美國日期格式 - 對於我們這些不在美國的人來說,ISO日期(yyyy-mm-dd)可以在任何地方使用都會容易得多;) - Julian Knight
如果有幫助,Excel會將值/格式識別為日期值 - Matt
不,我不害怕。僅當您將Windows位置設置為使用美國日期格式的內容時。在英國,默認值是dd / mm / yyyy而不是mm / dd / yyyy - 這兩種格式對於處理日期都沒有特別的幫助,這就是我總是使用ISO(yyyy-mm-dd)的原因。 - Julian Knight
至於你的問題。我一直在撓頭。我認為你必須生成一個日期表來實現你想要的。然後,您可以將其與事務數據合併。就個人而言,我不會在Excel中這樣做,因為它讓我的小腦疼痛只是考慮它 - 時間打破MS Access我懷疑。 - Julian Knight
:(我甚至不確定我在訪問中對這些數據做了什麼;我已經很久沒有使用過這個程序了。但是獲取缺少的日期並不像其他兩個部分那樣大問題:擁有一個運行總計中零的動態計數,並在源數據中連續幾天缺貨。那些訪問真的更容易嗎? - Matt


答案:


我認為它(或大部分?)可以在沒有VBA的Excel中完成,但試圖解釋另一個問題。

  1. 有助於能夠按項目識別期初餘額,因此將ColumnA添加到庫存分類帳(此後稱為Sheet1)並放入 =B2&D2 在 A2 (假設“Tran Type”在 A1)。

  2. 所以不要干擾Sheet1 Sheet2!ColumnB 放置每個項目的一個實例(數據>高級>列表範圍> =$D2:$D[last occupied row] >複製到另一個位置>複製到:[在Sheet1內]>僅限唯一記錄,然後復制結果列表)

  3. 填寫6/30/12餘額 Sheet2!ColumnC ['手動'可能是最容易的,如果你在Tran Type中按Item排序後從Sheet1複製]

  4. 6/30/12 (如果你必須使用那種表示法!) Sheet2!C1 和 =1+C1 成 Sheet2!D1

  5. =AND(D1>=$A2,D1<=$B2) 成 Sheet2!D2 [用於計算一段時間內的缺貨]。

  6. 輸入計算缺貨的開始日期 A2 和結束日期 B2

  7. =IF(VLOOKUP("Opening Balance"&$B4,Sheet1!$A:$C,3,FALSE)>D$1,"",IF(VLOOKUP("Opening Balance"&$B4,Sheet1!$A:$C,3,FALSE)=D$1,VLOOKUP("Opening Balance"&$B4,Sheet1!$A:$E,5,FALSE),C4+SUMIFS(Sheet1!$E:$E,Sheet1!$C:$C,Sheet1!K$2,Sheet1!$D:$D,$B4)))’ 在 Sheet2!D4 並複制所有項目。

  8. 複製 Sheet2!ColumnD 盡可能遠[我建議不超過1年!]並註意最後一欄[這裡假設為Q]。

  9. Sheet2!A4 放 =COUNTIFS(C4:Q4,0,C$2:Q$2,"=TRUE") 並複制所有項目。

  10. 如果您的項目確實在示例中命名,您可以設置比您選擇的時間段更多的設置,但是否則必須注意新項目。

  11. 在您選擇的期間之後,將新的值轉移為新的工作表中的“期初餘額”。

  12. 嘗試任何替代品!


2
2017-07-17 21:35





我沒有得到你所有的積分,但讓我們從最後一點開始,我認為我有一個很好的解決方案。也許我們可以從那裡建造它。

所以我使用了一些整潔的fomulars來到這個表:

Tran Type   Txn Date    Item    Quantity    Stock   Outstock day    Restock day Days without stock
Receiving   03.06.2012  Item4   5           5           
Receiving   30.06.2012  Item1   4           4           
Shipping    02.07.2012  Item3   -1          -1          
Shipping    03.07.2012  Item4   -1          4           
Shipping    03.07.2012  Item4   -1          3           
Shipping    03.07.2012  Item4   -1          2           
Shipping    03.07.2012  Item4   -1          1           
Shipping    03.07.2012  Item4   -1          0       03.07.2012     03.08.2012      31
Receiving   09.07.2012  Item3   10          9           
Shipping    14.07.2012  Item2   -1          -1          
Receiving   01.08.2012  Item2   7           6           
Receiving   02.08.2012  Item3   3           12          
Receiving   03.08.2012  Item4   5           5           
Receiving   09.08.2012  Item4   10          15          
Receiving   10.08.2012  Item5   11          11          
Receiving   11.08.2012  Item6   12          12          
Receiving   12.08.2012  Item4   13          28          
Receiving   13.08.2012  Item8   14          14          

股票

C2 = SUMIF($C$1:$C2,$C2,$D$1:D2)

- >這將從一開始就為您提供運行庫存,而不指定起點。

擠壓日 

F2 = IF(E2=0,B2,"")`

補貨日 

G2 = IF(E2=0,=INDEX($B:$B,MATCH($C2,OFFSET($C:$C,ZEILE($C2),0,ROWS($C:$C)-ROW($C2),1),0)+ROW($C2)),"")`

- >這樣可行,如果您按日期對數據進行排序,並且沒有為某件商品發貨,則在他缺貨之後

沒有股票的日子

H2 = IF(E2=0,G2-F2,"")

- >在這裡你可能要注意,減去日期可能並不總是導致正確的計數,但這是另一個問題。

主要的魔力是 offset 功能結合 match。通過偏移匹配範圍匹配,您可以在當前所在的行之前排除任何數據,然後匹配則查找當前項的第一個出現。

我正在研究用這個公式解決這個問題的方法,但是在調整它時遇到了麻煩,只得到當前項目的第一個日期 - 而不是任何項目。

VLOOKUP("Receiving",OFFSET(A:C,ROW(C2)-1,0,ROWS(C:C)-ROW(C2)-1,3),2,FALSE)

希望這會對​​你或其他人有所幫助,我很樂意延長這個......


0
2017-09-20 12:22



@pnuts是的,ZEILE = ROW,對不起。是的,我已經看到興趣是shiftet,但這讓我感興趣,當我得到一個解決方案,我想我應該分享它。 - Jook