Making a logical calculation
1 次查看(过去 30 天)
显示 更早的评论
Hello everyone,
I have the following code that outputs an xlsx multiple sheets file,
%% Create Tables to export :
t1 = datetime(2021,6,21,1,0,0);
t2 = datetime(2022,6,21,24,0,0);
dt = (t1:hours(1):t2)'
[months,~,jj] = unique([year(dt) month(dt)],'rows');
N = size(months,1);
sheet_names = datetime(months(:,1),months(:,2),ones(N,1),'Format','MMMMyyyy');
ss=0;
for ii = 1:N
idx = jj == ii;
DateTIME = dt(idx,1);
MV_Tariff_IEco = DateNtime(idx,2);
LV_Tariff_BIG = DateNtime(idx,3);
Import_TR2kWh = ImportedData(idx,1);
Import_TR1kWh = ImportedData(idx,2);
Export_TR2kWh = ImportedData(idx,3);
DayOfWeek = DateNtime(idx,4);
Total_Import_Excluding_ShuffersalkWh = CalcBigQShmona(idx,4);
newTable = table(DateTIME,MV_Tariff_IEco,LV_Tariff_BIG,Import_TR2kWh,Import_TR1kWh,Export_TR2kWh,Total_Import_Excluding_ShuffersalkWh,DayOfWeek);
writetable(newTable,'OutPutTable.xlsx','Sheet',char(sheet_names(ii)));
end
sheetnames('OutPutTable.xlsx')
I want to make a simple calculation and store it reuslt in a certain cell in every sheet, the calculation well be related for every sheet.
The calculations for every single sheet is :
Formula1 = (The number of weekdays of the current sheet) * 1000 * 0.84913 ---> to be stored somewhere in the sheet as shown in the picture, in my example it's stored in column J.
Formula2 = (Sum of ' ImportedData(:,3) ' ) * 0.84913to be stored somewhere in the sheet as shown in the picture.
note = importedData(:,3) is just the column called "Export_TR2kWh", it's summed and then multiplied by 0.84913.
Thanks !
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1051720/image.png)
0 个评论
回答(1 个)
Jon
2022-6-30
You can do the calculation for each sheet within your main loop. Then output it to the location you want in the sheet using writematrix with the Range parameter, e.g.
x = ... % your calculated value
writematrix(x,'OutPutTable.xlsx','Sheet',char(sheet_names(ii)),'Range','J5')
2 个评论
Jon
2022-7-1
编辑:Jon
2022-7-1
I'm not totally clear on what you mean by "number of work days in specific month", but here are some ideas:
I assume here that table, T, (maybe you call this table newTable) has a column DateTime that has the days and times e.g. 6/22/2021 3:00
If you want to count all of the rows on a sheet that correspond to weekdays you could do the following:
numWeekDays = sum(~isweekend(T.DateTime)); % count the weekdays (not weekend)
If you want to count just the number of weekdays that are included on the whole sheet you could use:
[~,idx] = unique(day(T.DateTime)); % find the days that are included on the sheet
numWeekDays = sum(~isweekend(T.DateTime(idx))) % count the ones that are weekdays (not weekend)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!