☰

计算数据透视表生成的“0”股票值

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function pivot-table
计算数据透视表生成的“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 的天数。这里有一个棘手的部分：我想将此计数保留为数据透视表的一部分，这样当我按月/季度/年对数据进行分组时，计数将是动态的（这样我们就可以看到每个时期每个商品缺货的总天数）。

如果我能利用基础数据做一些事情，让我获得缺货信息，那就太好了。如果我可以直接在数据透视表中做到这一点，那就更好了。

另一个更不切实际的解决方案是，当某件商品的库存量达到零时，我还可以生成连续几天缺货的数据，然后在数据透视表中按时间段查看该数据的平均库存量。我知道大部分工作都必须在源数据中完成，我甚至知道需要如何完成，但我无法在公式中将它们串联起来。首先，我按日期而不是按商品名称对数据进行排序。我创建了一个新列来保存库存总量（“运行总量”是列名）。一旦运行总量达到零，我需要找到下一行，其中日期晚于当前交易，商品名称相同，交易类型为“接收”。从那里，我需要找到交易日期之间的差异。有人能帮我想出一个公式吗？

最后，由于我们的数据仅包含仓库处理交易的日期，我想知道是否有办法将缺失的日期（即周末和节假日）也显示在数据透视表上？这不是很重要，但它会给高层留下深刻印象。

编辑：在看到这里的许多回复后，我也开始尝试通过 Access 或 SQL Server 来解决这个问题，并且我已将这个问题重新发布在堆栈溢出重点是使用查询和计算列来解决问题。

话虽如此，如果有人有更多想法，我仍然非常愿意通过 excel 来解决这个问题！由于我可以在源数据中获得累计总数，我想知道我是否可以在新列中使用 if 语句来处理缺货问题。

如果累计总数为零，那么我需要找到同一项目的下一笔交易，交易类型为“收款”，然后将下一笔交易的日期返回到列中。不过，我不确定如何将所有这些元素组合成一个公式。一旦我返回了下一笔收款交易的日期，我就可以从当前行的交易日期中减去天数差。

它看起来像这样：

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

从那里，我可以创建另一个按项目总计的数据透视表，或类似的东西。

答案1

我认为它（或大部分？）可以在没有 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 中按项目排序后从 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

我并没有完全理解你所有的观点，但让我们从最后一点开始，我认为我为你提供了一个很好的解决方案。也许我们可以从那里开始。

因此我使用了一些简洁的公式来得到这个表格：

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。通过偏移 match 正在处理的范围，您可以排除当前所在行之前的任何数据，然后 match 会查找当前项目的第一次出现。

我正在研究用这个公式来解决这个问题，但是在调整它时遇到了麻烦，只能得到当前项目的第一个日期，而不是任何项目的第一个日期。

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

希望这会对您或其他人有所帮助，我很高兴延长这个......

相关内容