更快的计算来获得许多不同级别的倍数 [英] Faster computation to get multiples of number at different levels

问题描述

下面是该方案：

我们都被运到多店的几个项目。我们希望能够分配一定量的每一个项目，以根据需要存储。每个这些商店也被关联到一个特定的仓库。

We have several items that are shipped to many stores. We want to be able to allocate a certain quantity of each item to a store based on need. Each of these stores is also associated to a specific warehouse.

美中不足的是，在仓库级别，每个项目的总数量必须是数字（6为例）的整数倍。

The catch is that at the warehouse level, the total quantity of each item must be a multiple of a number (6 for example).

我已经计算出在商品一级还需由每家商店的数量，但他们不总结为6的倍数在仓库的水平。

I have already calculated out the quantity needed by each store at store level, but they do not sum up to a multiple of 6 at the warehouse level.

我的解决方案是这样使用Excel：

My solution was this using Excel:

使用SUMIFS公式来跟踪分配在仓库水平每个项目的总和。然后另外一个MOD（6）公式计算剩余直到6的倍数。然后我实际上VBA code遍历和减1（如果MOD＆LT; = 3）或增加（如MOD> 3）从存储一级还需直到MOD = 0所有行的单位。

Using a SUMIFS formula to keep track of the sum of each item allocated at the warehouse level. Then another MOD(6) formula that calculates the remaining until a multiple of 6. Then my actually VBA code loops through and subtracts 1 (if MOD <= 3) or adds (if MOD > 3) from the store level units needed until MOD = 0 for all rows.

现在这对我的作品，但非常缓慢，甚至当我刚刚〜5000行。

Now this works for me, but is extremely slow even when I have just ~5000 rows.

我在找一个更快的解决方案，因为每次我减/加给所需单位，SUMIFS和MOD需要重新计算。

I am looking for a faster solution, because everytime I subtract/add to units needed, the SUMIFS and MOD need to be calculated again.

编辑：（试图更清晰）

我有我贴我的数据与下面设置一个模板文件：

I have a template file that I paste my data into with the following setup:

+------+-------+-----------+----------+--------------+--------+
| Item | Store | Warehouse | StoreQty | WarehouseQty | Mod(6) |
+------+-------+-----------+----------+--------------+--------+
|    1 |     1 |         1 |        2 |            8 |      2 |
|    1 |     2 |         1 |        3 |            8 |      2 |
|    1 |     3 |         1 |        1 |            8 |      2 |
|    1 |     4 |         1 |        2 |            8 |      2 |
|    2 |     1 |         2 |        1 |            4 |      2 |
|    2 |     2 |         2 |        3 |            4 |      2 |
+------+-------+-----------+----------+--------------+--------+

目前的WarehouseQty列是SUMIFS式总结StoreQty对于关联到仓库每个Item商店组合。所以我想仓库/ WarehouseQty列的每一个项目，商店组合显示时间居然重复了好几次。所述WarehouseQty是一个需要是6的倍数。

Currently the WarehouseQty column is the SUMIFS formula summing up the StoreQty for each Item-Store combo that is associated to the Warehouse. So I guess the Warehouse/WarehouseQty columns is actually duplicated several times every time an Item-Store combo shows up. The WarehouseQty is the one that needs to be a multiple of 6.

推荐答案

猜你可能会发现，停止屏幕刷新可以帮助相当一大块，因此不需要任何更多的建议。

Guessing you may find that stopping the screen refresh may help quite a chunk and therefore not need any more suggestions.

另一种选择是由6至减少调整量是整除的若干if语句，这取决于的值MOD（6）。

Another option would be to reduce your adjustment to a quantity which is divisible by 6 to a number of if statements, depending on the value of mod(6).

您也可以解决你如何总结所有商店的特定项目的数量，使用数据透视表和阅读的总和合计从那里比使用 SUMIFS 在宏

You could also address how you sum up the number of a particular item across all stores, using a pivot table and reading the sum totals from there is a lot quicker than using sumifs in a macro

您是对的，你可能有这样的计算一行一行，以及在即使你知道你到底有多少单位需要增加一个时间调整由一个单元的数量大量复制来自/删除mod（6）式。

You're correct that you could have huge amounts of replication doing the calculation row by row, as well as adjusting the quantity by a single unit at a time even though you know exactly how many units you need to add / remove from the mod(6) formula.

你能不能创建一个新的片材产品ID和存储的所有可能的组合。然后，您可以使用SUMIFS（）对所有这些独特的组合，并在最后一步四舍五入向上/向下在仓库的水平？

Could you not create a new sheet with all your possible combinations of product Id and store. You could then use sumifs() for each of these unique combinations and in a final step round up/down at a warehouse level?

这篇关于更快的计算来获得许多不同级别的倍数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！