单元格在范围外的范围（范围=行中最左边的值+下一个11列）R或Excel？ [英] Sum column if cells are outside range (range = leftmost value in row + next 11 columns) R or Excel?
问题描述
所以请看图片。在所示的矩阵中，从最左边的值+下一个11列（总共12个月）中，一个条目被认为是新业务。我以黄色突出显示了这个窗口。该窗口右侧的任何一个都是返回业务。对于每列/月，我需要计算新&退货业务我需要一个公式或一些方法从一列中得出这两个和。真的我只需要能够得到其中的一个，因为我可以从其他值的总列中减去它。
So 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. Really 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.
感谢您的帮助！
推荐答案
我添加了一个帮手umn 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和
=SUM(D2:D4)-D5
这篇关于单元格在范围外的范围（范围=行中最左边的值+下一个11列）R或Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
