Build charts from Excel.

Hi everyone, I have a problem. Basically I have to work on an Excel sheet with many values. I have to create 8 charts with 8 different data sets inside. The ranges that I have to use are equally spaced in every series of data and in every scatter plot that I have to create so I had thought of using a code with for loops that would allow me to automate this procedure. But I can't build it. Initially I thought of creating a macro on VBA. I attach a txt (test) with the macro that I created on VBA which however does not work. Each series of data is made up of 7 values ​​(range from 2 to 9 then from 10 to 17 and so on up to 58-65) the one relating to the attachment is only the first of the 8 graphics that I have to build. The second scatter plot will consist of the same data series number as the first with the same names only that the ranges will be out of phase by 64 for example if in the first chart the ranges of the first data series is 2-9 in the second chart it will be 66 -73, the second series 74-81 and so on for the other series of this second graph up to 122-129. This procedure, as I said, must be automated for 8 different graphs each consisting of 8 data series, the data series of each graph have a step of 7 and from one graph to another of 64 up to the last series of the eighth chart whose range will be 506-513. I hope I was clear, for a person who has practice in these things I don't think it's something very complicated but unfortunately I can't. If someone could help me I would be infinitely grateful because it would greatly facilitate the work I have to do. Thank you.

I also attach the excel sheet on which I have to work.

The macro that I attack have many problems:

1. I have to enter the names of the data series by hand and this is very inconvenient since the series in all the graphs are called the same way. The series names are always in order: 2.5 kW, 5 kW, 7.5 kW, 10 kW, 12.5 kW, 15 kW, 17.5 kW, 20 kW.

2. Create an initial curve in the extra chart which calls "series1"

3. It doesn't always work, sometimes it creates as many data series as the columns of the excel file that I have attached previously.

But the index update criteria are correct

[Image Analyst]

Can you mock up and attach a screenshot of what you'd like to obtain?

[Rocco De Luca]

I have to get 8 graphs each made up of 8 data series. In each graph the data series have a range of 7 values (for example in the first graph the first series which is called 2.5 kW will be x: E2: E9 and on the y: Q2: Q9. The second series which is called 5 kW will be : E10: E17, Q10: Q17 and so on up to the eighth series which will be called 20 kW: E58: E65, Q58: Q65). The second chart will have the same data series with the same name as the first chart, but 64 values will be offset, for example the first E66: E73, Q66: Q73, and the eighth data series E122: E129, Q122: Q129 . Everything is repeated for all the graphs so as to obtain the eighth series of the eighth graph E506: E513, Q506: Q513.

In the "Medio" sheet of this Excel file you can see the first graph that I will have to get. Thank you. If you prefer, I'll send you a screenshot of the chart.

[Rocco De Luca]

Answer 1

[Mohammad Sami]

You can use two for loops.

The outer for loop can iterate over the column C, and subset the data and create new figure.

The inner for loop can interate over column D in the subset of the data to plot line.

t = readtable('ScenarioA4-20km.xlsx');
uniq_col_c = unique(t.Pw_kW_);
for i = 1:length(uniq_col_c)
    Pw_kW = uniq_col_c(i);
    t_sub = t(t.Pw_kW_ == Pw_kW,:); % subset the data by C
    figure; %create new figure
    hold on;
    uniq_col_d = unique(t_sub.Ppv_kW_);
    for j = 1:length(uniq_col_d)
        % plot lines for each Ppv_kW
        Ppv_kW = uniq_col_d(j);
        t_sub_sub = t_sub(t_sub.Ppv_kW_ == Ppv_kW,:); % subset the data again by D
        plot(t_sub_sub.Pb_kWh_,t_sub_sub.PowerToBa,'DisplayName',num2str(Ppv_kW)); % plot e vs q
    end
    hold off;
end

[Rocco De Luca]

Thank you. The code runs, however, I do not understand in which part of the graph I tell him what values ​​to take to make the graph and where I can change the name of the data series as the kW is missing. Another thing, I have to work on a sheet called "Medium" of the excel file ScenarioA4-20km, so how do I take only the values ​​of this sheet? Thank you.

[Mohammad Sami]

The readtable function will import the data as a table and use the names in first row to create the header.

You can select which variable to plot by changing the script to another variable name in your table. (table.varname)

t = readtable('ScenarioA4-20km.xlsx');
uniq_col_c = unique(t.Pw_kW_);
for i = 1:length(uniq_col_c)
    Pw_kW = uniq_col_c(i);
    t_sub = t(t.Pw_kW_ == Pw_kW,:); % subset the data by C
    figure; %create new figure
    hold on;
    uniq_col_d = unique(t_sub.Ppv_kW_);
    for j = 1:length(uniq_col_d)
        % plot lines for each Ppv_kW
        Ppv_kW = uniq_col_d(j);
        t_sub_sub = t_sub(t_sub.Ppv_kW_ == Ppv_kW,:); % subset the data again by D
        % the following plot Pb_kWh_ against PowerToBa
        % you can change to other variable names to plot something else
        plot(t_sub_sub.Pb_kWh_,t_sub_sub.PowerToBa,'DisplayName',num2str(Ppv_kW)); % plot e vs q
    end
    hold off;
end

To select the sheet, you can run readtable with import options

opts = detectImportOptions('ScenarioA4-20km.xlsx','Sheet','Medio'); % the sheet to import
% you can also set many other options, check the documentation
t = readtable('ScenarioA4-20km.xlsx',opts);

[Rocco De Luca]

Hi, thank you. I have Matlab 2016a and unfortunately detectImportOptions does not work. I attach the code I used, the error that is indicated to me is:

Undefined function or variable 'detectImportOptions'.

Error in grafico (line 1)

opts = detectImportOptions('ScenarioA4-20km.xlsx','Sheet','Medio'); % the sheet to import

[Mohammad Sami]

detectImportOptions was introduced in R2016b. Hence the error in R2016a.

For R2016a, you can directly specify the sheet as a name value pair in readtable function

https://www.mathworks.com/help/releases/R2016a/matlab/ref/readtable.html#namevaluepairarguments

'Sheet' — Worksheet to read1 (default) | positive integer indicating worksheet index | string containing worksheet name

Worksheet to read, specified as the comma-separated pair consisting of 'Sheet' and a positive integer indicating the worksheet index or a string containing the worksheet name. The worksheet name string cannot contain a colon (:). To determine the names of sheets in a spreadsheet file, use [status,sheets] = xlsfinfo(filename). For more information, see xlsfinfo.

Example: 'Sheet', 2