复制Excel SUMIFS公式 [英] Replicating an Excel SUMIFS formula
本文介绍了复制Excel SUMIFS公式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要为Excel中的SUMIFS函数复制(或至少找到替代解决方案)。
I need to replicate - or at least find an alternative solution - for a SUMIFS function I have in Excel.
我有一个事务数据库:
SegNbr Index Revenue SUMIF
A 1 10 30
A 1 20 30
A 2 30 100
A 2 40 100
B 1 50 110
B 1 60 110
B 3 70 260
B 3 80 260
,我需要创建另一个列,该列将等于或小于该行中Index的所有索引按SegmentNumber的总和计入收入。这是一种扭曲的滚动收入,因为每个SegmentNumber / Index键都相同。这是这个公式:
and I need to create another column that sums the Revenue, by SegmentNumber, for all indexes that are equal or less the Index in that row. It is a distorted rolling revenue as it will be the same for each SegmentNumber/Index key. This is the formula is this one:
=SUMIFS([Revenue],[SegNbr],[@SegNbr],[Index],"<="&[@Index])
推荐答案
假设您有此示例数据。
dd<-read.table(text="SegNbr Index Revenue
A 1 10
A 1 20
A 2 30
A 2 40
B 1 50
B 1 60
B 3 70
B 3 80", header=T)
现在,如果我们确定数据的排序依据段和索引,我们可以这样做
Now if we make sure the data is ordered by segment and index, we can do
dd<-dd[order(dd$SegNbr, dd$Index), ] #sort data
dd$OUT<-with(dd,
ave(
ave(Revenue, SegNbr, FUN=cumsum), #get running sum per seg
interaction(SegNbr, Index, drop=T),
FUN=max, na.rm=T) #find largest sum per index per seg
)
dd
这给出了
SegNbr Index Revenue OUT
1 A 1 10 30
2 A 1 20 30
3 A 2 30 100
4 A 2 40 100
5 B 1 50 110
6 B 1 60 110
7 B 3 70 260
8 B 3 80 260
根据需要。
这篇关于复制Excel SUMIFS公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文