How can I take the average of certain columns in each excel sheets

Question

Marion 2023-7-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1998658-how-can-i-take-the-average-of-certain-columns-in-each-excel-sheets

回答： Mathieu NOE 2023-7-21

I have an excel file with multiple sheets. In sheet # 1, I want to take the average of column 1 and the average of column 5, then store each result in a new file. Iwant to repeat this process to all the remaining sheets in my excel file.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Dyuman Joshi 2023-7-20

Read the excel sheet using readmatrix or readtable, use indexing to take the mean of the columns 1 and 5, and use writematrix or writetable to store the result in a new file.

If you have any more questions, show what you have attempted and ask a specific question (where you are having trouble).

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Mathieu NOE 2023-7-21

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1998658-how-can-i-take-the-average-of-certain-columns-in-each-excel-sheets#answer_1276758

在 MATLAB Online 中打开

Classeur1.xlsx

hello Marion

see my example below (the dummy excel file is attached)

hope it helps

% Importing Data from excel across multiple sheets.
filename = 'Classeur1.xlsx';
[~,sheet_name]=xlsfinfo(filename);
nsheets = numel(sheet_name);
% retrieve and process data
for k=1:nsheets 
  T = readmatrix(filename,"Sheet",sheet_name{k}); %  readtable or readmatrix, readcell
  col1_averaged(k,:) = mean(T(:,1)); % average of column 1
  col5_averaged(k,:) = mean(T(:,5)); % average of column 5
end
%% export results as table
out_table = array2table([col1_averaged col5_averaged],'VariableNames',{'col 1 averaged' 'col 5 averaged'});
writetable(out_table,'out.xlsx',"Sheet",1);
    