How to make a 2-layer Subtotal from a table
显示 更早的评论
Hi, I am trying to make a function for getting a subtotal from a Table. I attached an excel file. First sheet are inputs, second sheet is the desired out. My data consists on three columns: the first layer (financial assets codes), the second layer (fund names where the asset is located) and third column (the amount invested). Any help please... would be deeply appreciated. Regards, Edson.
采纳的回答
Peter Perkins
2015-10-15
Edson, I think this does the trick:
>> question = readtable('question.xlsx')
question =
First Second Amount
________ _______ ______
'XS0001' 'Fund1' 20
'XS0002' 'Fund1' 30
'XS0003' 'Fund2' 40
'XS0004' 'Fund1' 50
'XS0005' 'Fund2' 60
'XS0006' 'Fund1' 70
'XS0001' 'Fund2' 80
'XS0002' 'Fund2' 90
'XS0003' 'Fund2' 20
'XS0004' 'Fund1' 30
'XS0005' 'Fund2' 40
'XS0006' 'Fund1' 50
'XS0001' 'Fund2' 60
'XS0002' 'Fund1' 70
'XS0003' 'Fund1' 80
'XS0004' 'Fund2' 90
'XS0005' 'Fund1' 20
'XS0006' 'Fund2' 30
'XS0001' 'Fund2' 40
'XS0002' 'Fund1' 50
'XS0003' 'Fund2' 60
'XS0004' 'Fund1' 70
'XS0005' 'Fund2' 80
'XS0006' 'Fund1' 90
>> varfun(@sum,question,'GroupingVariable',{'First' 'Second'},'InputVariable','Amount')
ans =
First Second GroupCount sum_Amount
________ _______ __________ __________
XS0001_Fund1 'XS0001' 'Fund1' 1 20
XS0001_Fund2 'XS0001' 'Fund2' 3 180
XS0002_Fund1 'XS0002' 'Fund1' 3 150
XS0002_Fund2 'XS0002' 'Fund2' 1 90
XS0003_Fund1 'XS0003' 'Fund1' 1 80
XS0003_Fund2 'XS0003' 'Fund2' 3 120
XS0004_Fund1 'XS0004' 'Fund1' 3 150
XS0004_Fund2 'XS0004' 'Fund2' 1 90
XS0005_Fund1 'XS0005' 'Fund1' 1 20
XS0005_Fund2 'XS0005' 'Fund2' 3 180
XS0006_Fund1 'XS0006' 'Fund1' 3 210
XS0006_Fund2 'XS0006' 'Fund2' 1 30
You might also consider converting First and Second to categorical, something like
question.First = categorical(question.First);
Hope this helps.
更多回答（1 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Electromagnetics 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
