Split data based on column value to write in different sheets in excel

2 次查看（过去 30 天）
MATHANA GOPAL A
MATHANA GOPAL A 2023-10-6
评论： Dyuman Joshi 2023-10-6
1 45 45
1 34 46
2 53 57
3 34 83
2 34 86
3 23 46
need to write the above data in Sheet 1,2 and 3 in a excel by matching the value of first column.
Sheet 1
45 45
34 46
Sheet 2
53 57
34 86
sheet 3
34 83
23 46

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

采纳的回答

Jon
Jon 2023-10-6
编辑：Jon 2023-10-6
You could do it like this
% Parameters
filename = "myfile.xlsx"
% Example data
A = [
1 45 45
1 34 46
2 53 57
3 34 83
2 34 86
3 23 46]
% Split data to different workbook sheets
for k = 1:max(A(:,1))
% Get submatrix for rows that match current index
Asplit = A(A(:,1)==k,2:end);
sheetName = "Sheet - " + num2str(k);
writematrix(Asplit,filename,'Sheet',sheetName)
end
  4 个评论
显示 2更早的评论
Jon
Jon 2023-10-6
@Dyuman Joshi Ahh, good to know, about building strings using numbers directly. I hadn't seen that before, thanks!

请先登录，再进行评论。

更多回答（2 个）

Mathieu NOE
Mathieu NOE 2023-10-6
hello
try this :
data = [1 45 45;
1 34 46;
2 53 57;
3 34 83;
2 34 86;
3 23 46];
for k =1:3
ind = data(:,1) == k; % check col number
data2export = data(ind,2:end); % select appropriate rows
% export to excel in separate sheets
%xlswrite('Result.xlsx',data2export,k); % option 1
writematrix(data2export,'Result.xlsx','Sheet',k); % option 2
end
Star Strider
Star Strider 2023-10-6
Ran in:
Using accumarray, one line to segment the matrix, then one loop to write the file sheets (and a second loop to verify the results).
Try this —
M = [1 45 45
1 34 46
2 53 57
3 34 83
2 34 86
3 23 46];
tic
Ms = accumarray(M(:,1), (1:size(M,1)), [], @(x){M(x,:)}); % Segment The Matrix By The First Column
toc
Elapsed time is 0.003118 seconds.
filename = 'Array.xlsx';
for k = 1:numel(Ms)
writematrix(Ms{k}(:,[2 3]), filename, 'Sheet',string(k))
end
for k = 1:numel(Ms)
A = readmatrix(filename, 'Sheet',string(k))
end
A = 2×2
45 45 34 46
A = 2×2
53 57 34 86
A = 2×2
34 83 23 46
.

类别

在 Help Center 和 File Exchange 中查找有关 Matrix Indexing 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by