create a new variable based on excel sheet values

조회 수: 24 (최근 30일)
Elysi Cochin
Elysi Cochin 2022년 3월 24일
댓글: Elysi Cochin 2022년 3월 26일
I wanted to import data from excel spread sheets from multiple sheets. So i used a loop
[~,sheet_name]=xlsfinfo('filename.xlsx')
for k=1:numel(sheet_name)
data{k}=xlsread('filename.xlsx',sheet_name{k})
end
Each sheets in the excel file has 5, 3, 4 column data (example)
I wanted to create two variables new_variable1 and new_variable2
When I read the excel sheets itself, how many columns the excel sheet has, I want to fill the new_variable1 with value 1, when reading the next sheet fill the new_variable1 with 2, and so on
So as per the example my new_variable1 will have values
new_variable1 = [ 1 1 1 1 1 2 2 2 3 3 3 3];
Similarly, when I read the excel sheets itself, how many columns the excel sheet has, I want to fill the new_variable2 with value 1 in the first row, when reading the next sheet fill the new_variable1 with 1 in the second row, and so on
So as per example, at last the values in new_variable2 will be
new_variable2 =
1 1 1 1 1 0 0 0 0 0 0 0
0 0 0 0 0 1 1 1 0 0 0 0
0 0 0 0 0 0 0 0 1 1 1 1
Please can someone help me write the code to get values in new_variable1 and new_variable2

채택된 답변

Stephen23
Stephen23 2022년 3월 24일
fnm = 'test.xlsx';
snm = sheetnames(fnm)
snm = 3×1 string array
"Sheet1" "Sheet2" "Sheet3"
dat = cell(size(snm));
for k = 1:numel(dat)
dat{k} = readmatrix(fnm,'Sheet',snm{k});
end
%
nmc = cellfun(@width,dat);
v1 = repelem(1:numel(dat),nmc)
v1 = 1×12
1 1 1 1 1 2 2 2 3 3 3 3
v2 = v1==(1:numel(dat)).'
v2 = 3×12 logical array
1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1
  댓글 수: 5
Stephen23
Stephen23 2022년 3월 26일
편집: Stephen23 2022년 3월 26일
After the loop:
mat = horzcat(dat{:})
Elysi Cochin
Elysi Cochin 2022년 3월 26일
thank you sir

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by