change in xlrange of xlsread while sing loop

17 views (last 30 days)
Honey
Honey on 13 Nov 2021
Commented: dpb on 15 Nov 2021
Hi
I am using xlsread with different ranges. I am trying to read excel data which the ranges differ in each iteration of loop according to the number of days in a month and in each year. How can I define these changes in xlRange? My code snippet is as follows that can show my intention. My excel file is also attached.
Note: I am puting these data to a cell file with struct of the years.
years= 20
Observation = cell(years);
N=31
for yy=1:years
year=2000+yy-1
for month=1:12
for day=1:N
if month==4| month==6| month==9| month==11
N=30
elseif month==2
N=28
else year==2000& month==2| year==2004& month==2| year==2008& month==2| year==2012& month==2| year==2016& month==2
N=29
end
Data= xlsread( Data , 'sheet1' , [range???? ':' range???]); % my question is here?
Observation{yy}.Observation{month, day} =Data
end
end
end
  2 Comments
Honey
Honey on 14 Nov 2021
@Jan thanks a lot for your suggestion about the determination of the number of days. It is also better than my code's appearance. And yes it is what I want: cell(years, 1). But I have another cell(20,20) with the content. How can I change the size of it to cell(20,1) that the content moved to the new cell?
I need this cell to compare with that one. So if I produce this cell(20,1), the other is cell(20,20). How to convert it with content? I have just one column in that cell(20,20).

Sign in to comment.

Answers (2)

dpb
dpb on 13 Nov 2021
That's the hard way to go at it...just read the spreadsheet into a table and operate over the regions of interest -- simple example
>> tdData=readtable('Data.xlsx'); % read the spreadsheet to the table
>> head(tData) % sample of what get...
ans =
8×4 table
amount year month day
______ ____ _____ ___
0 2000 6 1
0 2000 6 2
0 2000 6 3
0 2000 6 4
0 2000 6 5
0 2000 6 6
0 2000 6 7
0 2000 6 8
>> format bank, format compact % show two decimal places
>> groupsummary(tData,{'year'},"sum",{'amount'}) % total amounts by year
ans =
18×3 table
year GroupCount sum_amount
____ __________ __________
2000.00 214.00 507.00
2001.00 365.00 1739.00
2002.00 365.00 1113.50
2003.00 365.00 1460.50
2004.00 366.00 1537.00
2005.00 365.00 862.00
2006.00 365.00 1411.00
2007.00 365.00 1054.50
2008.00 366.00 1089.00
2009.00 365.00 1128.50
2010.00 365.00 839.00
2011.00 365.00 1474.50
2012.00 366.00 1297.00
2013.00 365.00 1043.00
2014.00 365.00 1781.00
2015.00 365.00 1592.80
2016.00 366.00 854.30
2017.00 79.00 0.00
>>
I just did by year so output would be relatively short; you can do monthly or daily or combinations at will.
The other is doable although I don't recomend it as the way to deal with the data you have -- the logic can be simplified significantly with builtin MATLAB functions...
>> cumDays=0;
for yy=1:3
for m=1:4
cumDays=cumDays+eomday(yy+1999,m)
end,end
cumDays =
31.00
cumDays =
60.00
cumDays =
91.00
cumDays =
121.00
cumDays =
152.00
cumDays =
180.00
cumDays =
211.00
cumDays =
241.00
cumDays =
272.00
cumDays =
300.00
cumDays =
331.00
cumDays =
361.00
>>
shows how to accumulate the days in a year accounting for leap years and days in each month.
The problem is your file doesn't begin on Jan 1 of a given year and so unless you read it first, you don't know where to begin counting.
It's far easier and better to just use the data itself.
NB: xlsread has been deprecated in favor or readtable and friends; they peform much faster and in general are far better to use with new code.
  3 Comments
dpb
dpb on 15 Nov 2021
And I have no idea what am supposed to make of the empty image you attached -- explain/illustrate what you think that represents in the data file if you want somebody to try to build whatever it is from the given data.
Just looking at your original code, I would submit that almost any use I can imagine of data by year/month/day can't be far more easily processed by a table with rowfun, groupsummary and friends or as a timetable and retime and grouping variables if there are other commensurate variables besides just the one observation in the attached data set.
Will be MUCH easier than iterating through a nested structure array...

Sign in to comment.


Jan
Jan on 13 Nov 2021
Why do you want to import ranges at all? Import the complete file as a matrix or table and filter out the needed values by something like:
year = AllData(:, 2);
month = AllData(:, 3);
thisAmount = AllData(year == 2008 & month == 2, 1);

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!