How can I add multiple charts in excel with activex
조회 수: 7 (최근 30일)
이전 댓글 표시
I've been writing a script that needs to add up to a hundred or so excel charts in one worksheet... I'm using activex to access excel through matlab and I'm using the Excel.ActiveSheet.ChartObjects.Add(Left,Top,Width,Height) function. One problem though, it won't seem to let me add sheets when either the 'left' or 'top' position is greater than a certain number, around 250-350. Is there anyway to fix this?
Many thanks, Mirage
댓글 수: 7
Eric
2012년 10월 26일
편집: Eric
2012년 10월 26일
I wonder if Matlab or Excel is choking on the superscript "2" character somehow. I was surprised to see that print out.
You can see my answer for how to easily calculate R^2 for the linear fit without the need for reading from the trendline label. The polynomial fit is a bit trickier.
-Eric
채택된 답변
Eric
2012년 10월 26일
편집: Eric
2012년 10월 26일
Here's how you can get the linear fit parameters (in Excel) without reading text strings from the trendline labels:
K>> V = Excel.WorksheetFunction.LinEst(GetXLSheet.Range(YseriesRange),GetXLSheet.Range(XseriesRange),true,true)
V =
[ 3.0000] [ 2.0000]
[9.8546e-17] [6.1146e-16]
[ 1] [8.9509e-16]
[9.2675e+32] [ 8]
[ 742.5000] [6.4095e-30]
The slope is the (1,1) element, the offset is the (1,2) term, and the R^2 value is the (3,1) term. For my case the equation is y = 3x+2 and the R^2 is 1.
You actually can use linest() to perform polynomial fits as well, but you would probably need to have the x^2, x^3, etc. data in the worksheet as well. You might be able to create VBA arrays of these values somehow and use them, but I'm not sure.
Alternatively, you can read the values directly into Matlab and perform calculations there. You can use
Yvals = cell2mat(GetXLSheet.Range(YseriesRange).Value);
Xvals = cell2mat(GetXLSheet.Range(XseriesRange).Value);
You can then use Matlab's polyfit to perform the fitting for you. To calculate the R^2 parameter, see http://en.wikipedia.org/wiki/Coefficient_of_determination. The following code implements this. In this case y is the data vector and fit_vec is a vector of fit values.
ybar = mean(y); %Mean of the data
sst = sum( (y - ybar).^2 ); %Total sum of squares
gof.sse = sum( (y - fit_vec).^2 ); %Residual sum of squares
if sst ~= 0
gof.rsquare = 1 - gof.sse/sst;
else
gof.rsquare = NaN;
end
gof.rmse = sqrt(mean((fit_vec-y).^2));
You could then also use Matlab to create the equation strings for you as well. You could borrow code from the disp() function from http://www.mathworks.com/help/matlab/matlab_oop/a-polynomial-class.html to do that.
To summarize, I would say the best solution is to use the existing code you've got for creating the plots. This seems to work well. Then read the data into Matlab and calculate fit parameters there.
-Eric
댓글 수: 3
추가 답변 (0개)
참고 항목
카테고리
Help Center 및 File Exchange에서 Data Import from MATLAB에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!