如何将桶值推算到数据透视表中？ [英] How to extrapolate bucketed values into pivot table?

问题描述

我需要将项目级别的库存推算为0到52周之间的4周桶。我有大约4500个独特的行如下：

I need to extrapolate my inventory at the item level into 4-week buckets between 0 and 52 weeks. I have about 4500 unique lines as follows:

 Item                               Unit   Weekly       
 Code      Description       Cases   Cost   Mvmnt    WOH    Bucket
 595384  PREMIUM FRYING OIL   500    $17.92    50     10   8-12 wks
 546760  DARK BROWN SUGAR     650    $11.81    10     65   >1 year
 598456  STEAM CORN           330    $15.42    15     22   20-24 wks
 532943  CHC SEMI SWEET       240    $34.13    80      3   <4 wks

第一个项目每周移动50个案例，所以库存500件，共10周（WOH），并入8-12桶。我想显示这个库存下降的地方，但说500美元的PREMIUM FRYING OIl落入8-12周的桶是不准确的。实际上，二百箱跌至四码，二百箱跌至4-8码，其余100箱落入8-12码桶。所以我想将第一行转换成以下内容：

The first item moves 50 cases a week, so the 500 cases in inventory represents 10 weeks on hand (WOH) and it falls into the 8-12 wks bucket. I want to show where this inventory falls, but it is inaccurate to say 500 cases of PREMIUM FRYING OIl fall into the 8-12 wks bucket. In reality 200 cases fall into <4 wks, 200 cases fall into 4-8 wks, and the remaining 100 cases fall into the 8-12 wks bucket. So I want to convert the first line into the following:

 Item                               Unit   Weekly       
 Code      Description       Cases   Cost   Mvmnt    WOH    Bucket
 595384  PREMIUM FRYING OIL   200    $17.92    50     10   <4 wks
 595384  PREMIUM FRYING OIL   200    $17.92    50     10   4-8 wks
 595384  PREMIUM FRYING OIL   100    $17.92    50     10   8-12 wks

...并为每个项目重复此操作。最终我想把它转换成一个数据透视表，以便将所有的案例分组。

...and repeat this for each item. Ultimately I want to turn this into a pivot table to sum all cases by bucket.

我实际做的是在数据结尾创建14列，每个4周的桶，并写了一个公式来创建运行减法。这是有效的，但是耗时且难以重复，因此我正在寻找一种不同的解决方案。请让我知道，如果我需要更好地澄清一下。

What I actually did was create 14 columns at the end of this data, one column for each 4-week bucket and wrote up a formulas to create running subtraction. This worked, but is time consuming and difficult to repeat, hence I am looking for a different solution. Please let me know if I need to clarify anything better.

推荐答案

可能让你开始，或澄清你的要求，假设项目代码在A1中：

Might get you started, or clarify your requirement, assuming Item Code is in A1:

在I2： = 4 * E2 （4周每周运动）

在J2： = INT（C2 / I2）（整个4周的行数速率）
K2中的
： = I2 *（C2 / I2-INT（C2 / I2））（如果需要，额外行的值）

in I2: =4*E2 (the 4-weekly movement)
in J2: =INT(C2/I2) (the number of rows at the full 4-weekly rate)
in K2: =I2*(C2/I2-INT(C2/I2)) (the value of an extra row, if required)

将所有三个公式复制到适合。

all three formulae copied down to suit.

但是，似乎您可能会更好地转动源数据，并在结果表中添加计算。

But it seems you may be better off pivoting your source data and adding calculations in the resulting table.

我特别不明白0和52周之间的桶与> 1年结合，我看不出什么/为什么外推（也许是插值？）。

I particular I do not understand "buckets between 0 and 52 weeks" in conjunction with ">1 year" and I don't see what/why "extrapolation" (maybe interpolation?).

这篇关于如何将桶值推算到数据透视表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！