change in xlrange of xlsread while sing loop

2 次查看（过去 30 天）
Honey
Honey 2021-11-13
评论： dpb 2021-11-15
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 个评论
Jan
Jan 2021-11-13
The determination of the number of days per month is not working corretly: The Februrary gets 28 days in all cases: "if month==2" catchs all Februraries and the following if branch is not entered. The 31 is set in the first iteration only.
Better:
if any(month == [4, 6, 9, 11))
N = 30;
elseif month == 2
N = 28 + (~mod(year, 4) & (mod(year, 100) | ~mod(year, 400)));
else
N = 31;
end
The command cell(years) creates a 20x20 cell matrix. I guess you want cell(1, years).
Honey
Honey 2021-11-14
@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).

请先登录，再进行评论。

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

回答（2 个）

dpb
dpb 2021-11-13
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 个评论
显示 1更早的评论
dpb
dpb 2021-11-14
Well, you can have your data however you want it, but rearrange it after you read it, not while you're trying to read it.
It's far more complicated to build those range variables than to use the data itself to select from/with what you want and how you want it, particularly given the problem that you don't know a priori from whence to start counting because the data begin at an arbitrary point. Unless, of course, you manually enter the start location every time which pretty-much defeats the point of having a programming tool to help.
dpb
dpb 2021-11-15
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...

请先登录，再进行评论。

Jan
Jan 2021-11-13
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);

类别

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

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by