在另一列中添加具有特定条件的一列,例如excel的求和 [英] addition of one column with certain condition in another colum, like sumifs of excel
问题描述
我有一个像这样的矩阵
A=[ 1 2; 2 3; 3 4; 4 5; 5 6; 6 8; 7 9; 8 5; 9 4]
现在,我想添加第二列,条件是如果limit = 0,interval = 3和limit = limit + interval,或者换句话说,当第1列的值的范围如下时,我必须对第2列求和0到3、3到6、6到9和9到12,我想要第2列的对应值之和.
Now I want to add a second column the condition is that if limit=0, and interval=3 and limit=limit+interval, or in other words, I have to sum column 2 when values of column 1, ranges like 0 to 3, 3 to 6, 6 to 9, and 9 to 12, and i want sum of corresponding values of column 2.
我的解决方案就是这样
range- sum
0 to 3 9
3 to 6 19
6 to 9 18
就像我有一个大约7000x2的矩阵.代替范围,也可能会给出序列号.
like that I have a matrix of around 7000x2. In place of range just serial no may also be given.
这只是一个例子.
推荐答案
This is a job for ACCUMARRAY. First, you construct an array of indices of the values that should be added together, then you call accumarray
:
%# create test data
A=[ 1 2; 2 3; 3 4; 4 5; 5 6; 6 8; 7 9; 8 5; 9 4];
%# create indices from first column
%# if you have indices already, you can use them directly
%# or you can convert them to consecutive indices via grp2idx
groupIdx = ceil(A(:,1)/3); %# 0+ to 3 is group 1, 3+ to 6 is group 2, etc
%# sum
result = accumarray(groupIdx,A(:,2),[],@sum)
result =
9
19
18
编辑
如果您需要对范围内的条目进行计数,则它仍然是accumarray
的工作,只是您不累积总和,而是累积直方图.
If you need instead to count entries within the ranges, it is still a job for accumarray
, only that you don't accumulate into a sum, but into a histogram.
%# use test data, groupIdx from above
A=[ 1 2; 2 3; 3 4; 4 5; 5 6; 6 8; 7 9; 8 5; 9 4];
groupIdx = ceil(A(:,1)/3); %# 0+ to 3 is group 1, 3+ to 6 is group 2, etc
%# find values to count
values2count = unique(A(:,2));
%# count the values
countsPerRange = accumarray(groupIdx,A(:,2),[],@(x){hist(x,values2count)})
%# inspect the counts for range #1
[values2count,countsPerRange{1}']
ans =
2 1
3 1
4 1
5 0
6 0
8 0
9 0
这篇关于在另一列中添加具有特定条件的一列,例如excel的求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!