function plot_excel_data                        %  © J. M. Hill S2011
% read data from excel file into a matrix, plot designated columns as
% discrete points, overlay plot of curve fit
% user must edit code for correct values of filename, xcol, ycol, n
% and plot title and labels
clc;                %clear command window
filename = 'test.xlsx';
xcol = 2;           %column xcol in the a matrix will be read into vector x
ycol = 4;           %column ycol in the a matrix will be read into vector y
[x,y] = read_excel_cols(filename,xcol,ycol);  %read colmuns from excel file
plot_data(x,y)
%--------------------------------------------------------------------------
function [x,y] = read_excel_cols(filename, xcol, ycol)
c = xlsread(filename);  % read all the data in the file into array a
x = c(:,xcol);          % assign all numbers in col xcol to vector x
y = c(:,ycol);          % assign all numbers in col ycol to vector y
[n,m] = size(c);        %determine size, n rows, m columns
b = c(:,m);             %strip b vector out of c matrix
for i = 1:n
    for j = 1:n
        a(i,j) = c(i,j);%strip a matrix out of c matrix
    end
end                     %alternatively: a = c(:,1:n) can replace the loop;
write_matrix_screen(n,a,b)
%--------------------------------------------------------------------------
function write_matrix_screen(n,a,b)
for i = 1:n
    for j = 1:n
        fprintf(1,'%3.2g ',a(i,j)); %write coefficient matrix to screen
    end
    fprintf(1,'%3.2g\n',b(i));      %write right-hand-side vector to screen
end
%--------------------------------------------------------------------------
function plot_data(x,y) %plot x-y pairs, overlay plot of curve fit
plot(x,y,'k^')
hold on                     % overlay plots
n = 2;                      % order of polynomial regression (max = 3)
c = polyfit(x,y,n);         % fit x-y pairs with polynomial of order n
ypred = polyval(c,x);       % predictions
dev = y - mean(y);          % deviations - measure of spread
SST = sum(dev.^2);          % total variation to be accounted for
resid = y - ypred;          % residuals - measure of mismatch
SSE = sum(resid.^2);        % variation NOT accounted for
Rsq = 1 - SSE/SST;          % percent of error explained (r-squared)
fprintf(1,'r-squared = %g\n',Rsq)
for i = 1:n+1
    fprintf('c(%d)= %6.4g\n',i,c(i));  %print polynomial coefficients
end
m = numel(x);          %determine the number of elements in vector x
dx = (x(m)-x(1))/100;  %calculate a delta-x equal to (last_x - first_x)/100
xx = x(1):dx:x(m);     %divide x scale into 100 ptsto make a good plot
switch n
    case 1                                                %linear fit
        line = c(1).*xx + c(2);
    case 2                                                %second-order fit
        line = c(1).*xx.^2 + c(2).*xx + c(3);
    case 3                                                %cubic fit
        line = c(1).*xx.^3 + c(2).*xx.^2 + c(3).*xx + c(4);
    otherwise
        disp('Cannot do fit greater than 3 with this code');
end
plot(xx,line,'k')
ylabel('y')
xlabel('x')
title('testdata')
hold off                %quit overlaying plots

  6 1.2   5 1.1   1   1 NaN
  7 4.4   6 2.5   2   2 NaN
  6 7.8   8 3.8   3   3 NaN
  7  15   5   4   4   4 NaN
  6  24   3 5.1   5   5 NaN
  7  37   1   6   6   6 NaN
r-squared = 0.935904
c(1)= -0.003445
c(2)= 0.2515
c(3)=  1.296

Published with MATLAB® 7.6