如果单元格超出范围(范围 = 行中最左边的值 + 接下来的 11 列)，则求和列是 R 还是 Excel? [英] Sum column if cells are outside range (range = leftmost value in row + next 11 columns) R or Excel?

问题描述

请看图.在图中的矩阵中，从最左边的值 + 接下来的 11 列(总共 12 个月)开始，一个条目被视为新业务".我用黄色突出显示了这个窗口".该窗口右侧的任何内容都是退货业务".对于每一列/月，我需要计算 New &返回业务.我需要一个公式或某种方法来从一列中得出这两个总和.我只需要能够获得其中一个，因为这样我就可以从另一个值的总列总和中减去它.

Please see the picture. In the matrix pictured, an entry is considered "New Business" from the leftmost value + the next 11 columns (so 12 months total). I've highlighted this "window" in yellow. Anything to the right of that window is "Return Business". For each column/month, I need to calculate both the New & Return Business. I need a formula or some method to derive these two sums from one column. I only need to be able to get one of these, because then I could just subtract it from the total column sum for the other value.

在单元格 O6 中，每个单元格的结果仅为 25 美元，因为第 5 行最左边的值不会在 O 列之前扩展整整 12 个月/列，因此该 12 个月窗口内的任何内容仍被视为新"(我做了这个窗口蓝色，以便您可以看到它超出了我正在尝试获取结果的单元格).可以填充一行中第一个值右侧的所有 11 列，但它们仍然是新的.只有从第 13 列开始，才会被视为返回".

In cell O6 the result is only $25 for each because the leftmost value on row 5 does not extend a full 12 months/columns before column O, so anything within that 12 month window is still considered "New" (I made this window blue so you could see it extends beyond the cell I'm trying to get a result for). All 11 columns to the right of the first value in a row could be populated, but they would all still be new. Only from the 13th column on would anything be considered "Return".

如您所见，每行中的第一个值并不总是出现在同一位置，而是会移动.

As you can see, the first value in each row does not always occur in the same spot, it moves.

我使用的电子表格有 2,000 多行.我实际上经历了并手动完成了此操作，但这并不有趣！我手动找到每一行中最左边的值，在它之后数出 11 个单元格，然后将行的其余部分涂成黄色.然后我使用了一个自定义函数，允许我根据单元格颜色进行求和.因此，如果有一种方法可以有条件地格式化这些值，那也可以，我可以使用我的颜色总和函数.

The spreadsheet I am working with is 2,000+ rows. I actually went through and did this manually, but that was NOT fun! I manually found the leftmost value in each row, counted out 11 cells after it, and then colored the rest of the row yellow. Then I used a custom function that allowed me to sum based on cell color. So if there is a way to conditionally format the values, that would work too, I could use my color sum function.

另外，如果在 R 中有更好的方法可以做到这一点，请告诉我.我是 R 的新手，但我认为矩阵数学更容易/更快，但我只玩了一点，我从来没有在其中做这样的事情.

Additionally, if there is a better way to do this in R, please let me know. I am new to R, but I imagine matrix math is easier/faster to do, but I've only played around with it a bit and I've never had to do anything like this in it.

推荐答案

我添加了一个辅助列 C，用于查找行中的第一个非空白(我的数据从列 D 到列 AZ)

I added a helper column C that finds the first non blank in the row (my data went from column D to column AZ)

=MATCH(TRUE,INDEX((D2:AZ2<>0),0),0)

这是我在单元格 D5 中放入并复制的公式

This was then the formula I put in cell D5 and copied it across

=SUMIFS(D2:D4,$C2:$C4,">"&COLUMN(D1)-12-3)

12 代表 12 个月，3 代表数据开始的 C 列.

The 12 is for 12 months and the 3 is for Column C where the data starts.

退货业务在 D6 及以上

The return business is in D6 and across

=SUM(D2:D4)-D5

这篇关于如果单元格超出范围(范围 = 行中最左边的值 + 接下来的 11 列)，则求和列是 R 还是 Excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！