make calculations with excel file data

Question

Joaquim Monteiro 2015-9-9

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/241696-make-calculations-with-excel-file-data

评论： dpb 2015-9-26

采纳的回答： dpb

在 MATLAB Online 中打开

I'm a newbie in matlab, so can anyone help me in the following question?

I want to use the data of a excel file in a matlab script.

I already import data with the following code (weather data file):

[fileName,pathname] = uigetfile({'*.xlsx'},'Select Location');
  nomeficheiro=strcat(pathname,fileName); 
  [a,b,c]=xlsread(nomeficheiro, 'A2:AJ8762'); 
  location = c(1,2);
  set(handles.location_text,'String',location);

In this example I get the location of the weather data using the value store in row 1 and column 2.

In this file in the column 8, we have 8760 hourly values of ambient temperature.

I need to do a calculation with all the values.

For example, import value row 1 and column 8, make calculation in matlab script, next import value row 2 and column 8, make calculation in matlab script, next import value row 3 and column 8, make calculation in matlab script,... and so on....

I want to automate this calculation

I want to store the results of the calculations in textbox in gui.

Thanks

2 个评论
显示无隐藏无

dpb 2015-9-9

编辑：dpb 2015-9-10

All the numeric data are in the numeric array a; in Matlab one generally tries to write code to make use of the fact that it can handle full arrays at a time.

What are the calculations you wish to perform on the data in column 8? If it is the same for each, then it will be quite simple; if there's something different for various ones, you'll have to have a way to know which and what do do for each...

Joaquim Monteiro 2015-9-10

Is the same calculation for all. This is hourly data (8760 values) of a year. I've to do the calculation, then store the result and then go to the next row of the column 8.

请先登录，再进行评论。

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

Answer 1

dpb 2015-9-10

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/241696-make-calculations-with-excel-file-data#answer_191922

在 MATLAB Online 中打开

"_Is the same calculation for all...of the column 8."_

Tf=a(:,8)*1.8+32; % convert column 8 (assume T in C) to F

Done for the whole year in one swell foop...that's the power of Matlab; no loops needed most of the time and are to be avoided when not.

Above gives you a new variable; if you don't need to keep the original but want to overwrite, then simply

a(:,8)=a(:,8)*1.8+32; % convert column 8 (assume T in C) to F

Since you still haven't made us privy to the actual calculation, I just made up an example; simply replace the specific with whatever yours is.

19 个评论
显示 17更早的评论隐藏 17更早的评论

dpb 2015-9-15

编辑：dpb 2015-9-15

在 MATLAB Online 中打开

After rereading your original question to refresh my memory on what you had done so far, I noticed the last comment of "store the results of the calculations in textbox in gui." That's likely going to be a difficult user interface -- a text box box with 8760 or even the 365 lines is just too much data to look at conveniently that way. Is there really any reason someone will want to see all those individual values, even? I'd suggest plotting them instead or summarizing even further to a set of descriptive statistics or the like such as [mean, mode, min, max, ...] along with the associated date or similar. That is succinct enough to show and likely will have all the info someone will need on the process interactively.

However, whether you choose the latter or think you must go with the full monte, use a table not a text box.

doc uitable

But, "I don't do windows" so I'm not the best person to ask about gui components in depth altho I think the general precept above is definitely true that large lists of values in a gui are, generally speaking, useless. Doing such is the result of the unfortunate ubiquitousness of the spreadsheet paradigm...

Joaquim Monteiro 2015-9-16

PORTO.xlsx

I attached the weather Data file. Previously I tell it wrong, I want to use the column 16 and not the column 10.

As you can see in the file, I've the hourly data for the 8760 hours of the year. I'm trying to calculate the hourly power consumption of a heat pump. For that I've to start the calculation in the first row and store the result (later I want to sum the hourly values to get monthly totals and show in a specific textbox in gui. I've one for every month). Until here everything is fine, because already you explain how to do this and I understand.

My problem know is that after the first calculation (1st row), the result is use as input for the 2nd row, and after the result from the 2nd row is use in the 3rd row and so on... In other words I'm trying to do a dynamic simulation, so that if I change the weather data the program automatically calculates new power consumption values.

From what I read maybe I've to use the loop capabilities of matlab, but I'm not certain if that is the right way and how to do that.

Joaquim Monteiro 2015-9-23

编辑：dpb 2015-9-24

在 MATLAB Online 中打开

There's the data.

2011   1   1  10  60 ...  7    3.8  80 100201  409 1413  301  251  625   69 32803 78625  9914
2011   1   1  11  60  ... 8.3  4.1  75 100201  536 1413  319  356  697   90 46892 89034 12932
2011   1   1  12  60 ...  9.5  4.5  71 100201  602 1413  325  416  736  101 55111 94819 14512
2011   1   1  13  60 ... 10.3  4.7  68 100201  602 1413  329  416  736  101 55111 94819 14512
2011   1   1  14  60 ... 10.6  4.7  67 100201  536 1413  330  356  697   90 46892 89034 12932

I want to do

Pc1 = value column8 + 253,2 + column16

This result is use as a input to the 2nd row

Pc2 = Pc1*((value column8 + 253,2 + column16 )-(value column20)
Pc3 = Pc2*((value column8 + 253,2 + column16 )-(value column20)

Joaquim Monteiro 2015-9-24

在 MATLAB Online 中打开

PORTO =
 1.0e+05 *
Columns 1 through 12
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0008    1.0020    0.0041
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0008    1.0020    0.0054
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0007    1.0020    0.0060
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0007    1.0020    0.0060
Columns 13 through 20
  0.0141    0.0030    0.0025    0.0063    0.0007    0.3280    0.7863    0.0991
  0.0141    0.0032    0.0036    0.0070    0.0009    0.4689    0.8903    0.1293
  0.0141    0.0032    0.0042    0.0074    0.0010    0.5511    0.9482    0.1451
  0.0141    0.0033    0.0042    0.0074    0.0010    0.5511    0.9482    0.1451
>> Pc1=PORTO(1,8)+253.2+PORTO(1,16)
Pc1 =
885.2000
>> Pc2=Pc1*(PORTO(2,8)+253.2+PORTO(2,16))-PORTO(2,20)
Pc2 =
 8.3553e+05
>> Pc3=Pc2*(PORTO(3,8)+253.2+PORTO(3,16))-PORTO(3,20)
Pc3 =
 8.3443e+08

and so on until Pc8760

请先登录，再进行评论。

Answer 2

dpb 2015-9-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/241696-make-calculations-with-excel-file-data#answer_193581

编辑：dpb 2015-9-24

在 MATLAB Online 中打开

OK, that's one interpretation but seems unlikely that that's what you really, really intend...that's going to "blow up" to an extremely large number in the end...

But, for the above as written simplest coding is in a loop although there are ways to vectorize it, will leave those for more advanced lesson particularly as I don't expect you're going to like the result as it will overflow in all likelihood by the time you reach the end...

>> pc=d(:,8)+253.2+d(:,16);
>> for i=2:length(pc),pc(i)=pc(i-1).*pc(i)-d(i,20),end
pc =
0e+05 *
0089
3553
0100
0100
0096
pc =
0e+08 *
0000
0084
3443
0000
0000
pc =
0e+11 *
0000
0000
0083
3401
0000
pc =
0e+14 *
0000
0000
0000
0083
0132
>>

Above shows the same results as your calculation with the entry of each loop in range of the display each iteration.

I don't understand what it is you're actually physically trying to compute so can't judge the appropriateness of the formulation for the purpose. BUT, note that pc(1) has units of whatever d(8) and d(20) are but you've then squared that value and added another term in only units(d). Unless these are dimensionless, that's clearly in error for a physical system computation.

4 个评论
显示 2更早的评论隐藏 2更早的评论

Joaquim Monteiro 2015-9-26

Dpb, you are right, the modelling that i need at this moment are not complete, but is sufficient for now to move forward.

dpb 2015-9-26

OK, just so's you recognize the issue...

请先登录，再进行评论。

make calculations with excel file data

2 个评论
显示无隐藏无

采纳的回答

19 个评论
显示 17更早的评论隐藏 17更早的评论

更多回答（1 个）

4 个评论
显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

Community Treasure Hunt

make calculations with excel file data

2 个评论 显示 无隐藏 无

采纳的回答

19 个评论 显示 17更早的评论隐藏 17更早的评论

更多回答（1 个）

4 个评论 显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

Community Treasure Hunt

2 个评论
显示无隐藏无

19 个评论
显示 17更早的评论隐藏 17更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论