自动更改所有日历小时的公式，如果我更改1以保持相同的总时数 [英] Formula to auto change all calendar hours if I change 1 to keep the same total hours

问题描述

这个问题与我的其他主题有关：
将数据从一个单元格均匀分配到多个单元格的公式

这是我正在尝试的实现：
如果我要手动更改一个等分数字，那么可以使日历中的值自动更改。

Ex。

• 总时数400


• 从1630开始


• 结束周1630

的36s成为100，其他36s自动更改为30，以便总共停留400.
这是我的excel表如下所示： http://imgur.com/9RUNTGV
左边的3列是

AQ4 = COUNTIF（L4： AP4; $ I4 /（$ K4- $ J4 + 1））##这是分割总数的单元格数量 b

AR4 = SUM（L4：AP4） ##这是我用来与手动更改值之前所需的总时数进行比较的总值。

AS4 = COUNTIF（L4：AP4;< >&$ I4 /（$ K4- $ J4 + 1）） - COUNTIF（L4：AP4;）##这是不是$ I4 /（$ K4- $ J4 + 1）或空白。

这是我已经提出了这么远，但它不工作：

  = IF（AND（AC $ 2  - ; = $ J4; AC $ 2'= $ K4）; $ I4 /（$ K4- $ J4 + 1）;和（$ AS4大于0;（2 * $ I4- $ AR $ 4）/（$ AQ4- $ AS4）;（$ K4- $ J4 + 1）; $ I4 /（$ K4- $ J4 + 1））
  

解决方案

从这个答案，我们在 L7 中有这个公式，可以复制到其他单元格：

  = IF（AND（L $ 2> = $ J7; L $ 2 <= $ K7）; $ I7 /（$ K7- $ J7 + 1） ）
  

现在的挑战是使该公式知道同一行中的其他值，如它们可能已被手动更改。这导致循环引用的问题：单元格 L7 必须考虑到对单元格 M7 所做的更改，而且 M7 应考虑更改制作到单元格 L7 。如果在这两个单元格中有（最初）没有手动编码的值，我们有一个循环引用。所以这是不能完成的。

然而，可以做什么可以使每个单元格中的公式仅在左边看，进行基于此的计算。这样就不会有循环引用。

这导致了 L7 的以下公式：

  = IF（L $ 2 = $ J7; 
 $ I7 /（$ K7- $ J7 + 1）; 
 IF（AND（L $ 2 （$ 7，L $ 2，= $ K7）; 
（$ I7-SUM（OFFSET（L7; 0; $ J7-L $ 2; 1; L $ 2- $ J7）））/（$ K7 -L $ 2 + 1）; 
））
  

这意味着：

如果这个单元格是期间的第一个（ L $ 2 = $ J7 ）然后像以前一样进行计算均匀分布的价值（ $ I7 /（$ K7- $ J7 + 1））。如果该单元格是期间内的另一个单元格，则取上一个单元格的总和（ SUM（OFFSET（L7; 0; $ J7-L $ 2; 1; L $ 2- $ J7） code>），从总要求的总和（ $ I7-SUM（...））中减去，并将其除以剩余在对，计数这个单元格（ ... /（$ K7-L $ 2 + 1））。在所有其他情况下，输出空字符串。

复制此公式。

一旦你用固定的数字覆盖了一个值，你显然会破坏那里的公式。但是看看它如何使得右边的单元格重新计算。总和保持不变。请注意，在这段时间之外的单元格中的更改不会被考虑。

最后，确保从左到右进行手动更改。如果您更改了已更改的另一个单元格左侧的单元格，那么事情将不会再加起来。在这种情况下，您首先必须在更细胞中恢复公式，然后在左侧单元格中进行更改，然后再次按照从左到右的顺序进行更改。

This question is related to my other thread: Formula to divide data from one cell equally into multiple cells

This is what I am trying to achieve: Would it be possible to have the values in the calendar automatically change if I were to manually change one of the equally divided numbers.

Ex.

• Total hours 400
• Start week 1630
• end week 1630

Divided into 36 each I change 1 of the 36s into 100 and the other 36s automatically change to 30 so that the total stays 400. This is how my excel sheet looks like: http://imgur.com/9RUNTGV The 3 columns to the left are

AQ4 =COUNTIF(L4:AP4;$I4/($K4-$J4+1)) ## This is the number of cells with the divided total

AR4 =SUM(L4:AP4) ## this is the total value that I use to compare with the total hours it should take before I changed values manually

AS4 =COUNTIF(L4:AP4;"<>"&$I4/($K4-$J4+1))-COUNTIF(L4:AP4;"") ## This is the number of cells that are not $I4/($K4-$J4+1) or blank.

This is what i've come up with this far but it is not working:

=IF(AND(AC$2>=$J4;AC$2<=$K4);$I4/($K4-$J4+1);AND($AS4>0;(2*$I4-$AR$4)/($AQ4-$AS4);($K4-$J4+1);$I4/($K4-$J4+1)))

解决方案

From this answer in your previous question, we have this formula in L7, which can be copied to other cells:

=IF( AND(L$2>=$J7; L$2<=$K7); $I7/($K7-$J7+1); "") 

Now the challenge is to make that formula aware of the other values on the same row, as they might have been changed manually. This leads to a problem of circular reference: cell L7 must take into account changes made to cell M7, but also M7 should take into account changes made to cell L7. If there are (initially) no manually encoded values in either of these two cells, we have a circular reference. So this cannot be done.

However, what can be done, is to make the formula in each cell only look to its left side and make the calculation based on that. This way there will not be circular references.

This leads to the following formula for L7:

=IF(L$2=$J7; 
    $I7/($K7-$J7+1); 
    IF(AND(L$2>$J7; L$2<=$K7); 
       ($I7-SUM(OFFSET(L7;0;$J7-L$2;1;L$2-$J7)))/($K7-L$2+1); 
       ""))

This means:

If this cell is the first one of the period (L$2=$J7) then do as before, and calculate the evenly distributed value ($I7/($K7-$J7+1)). If this cell is another cell within the period, then take the sum of the previous cells (SUM(OFFSET(L7;0;$J7-L$2;1;L$2-$J7))), subtract that from the total required sum ($I7-SUM(...)) and divide that by the number of cells that remain on the right, counting this cell as well (.../($K7-L$2+1)). In all other cases output the empty string.

Copy this formula around.

Once you overwrite a value with a fixed number, you obviously destroy the formula that was there. But see how it makes the cells on the right of it recalculate. The sum remains constant. Be aware that changes in cells outside of the period are not taken into account.

Finally, make sure to make manual changes from left to right. If you make a change to a cell at the left of another cell that was already changed, things will no longer add up nicely. In that case, you first have to restore the formula in the righter cells, then make the change in the left cell, and then make again the changes in left-to-right order.

这篇关于自动更改所有日历小时的公式，如果我更改1以保持相同的总时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！