How can I sequentially reference variables (P1, P2, P3...) from separate Excel sheets in MATLAB?

I have an Excel spreadsheet (named Filtered Data 22nd Nov) which contains 25 separate sheets within it, each being sequentially named P1, P2, P3, P4... up to P25. Each sheet contains two columns of data (of equal column lengths) which I would like to reference in MATLAB. Not all the sheets have the same number of entries (i.e. P1 may have 15339 entries but P2 has 15347 entries).

For reference, each of the 25 sets of data correspond to a curve, of which i am trying to work out the area bound between the red dashed y=max value line and the blue curve profile.

I currently have the following code for one sheet (P1):

P1DataPoints = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P1','Range','A1:A15339'); 
P1Depths = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P1','Range','B1:B15339'); 
P1MaxDepth = max(P1Depths); 
P1LHS = P1DataPoints(1,1); 
P1RHS = P1DataPoints(15339,1); 
P1CurveArea = trapz(P1DataPoints, P1Depths);
P1RectangularArea = (P1RHS - P1LHS)*(P1MaxDepth);
P1WearArea = P1RectangularArea - P1CurveArea;
P1WearVolume = (P1WearArea * 170)*1e-9;

%line 1: reads 1st column of excel data (x data)
%line 2: reads 2nd column of excel data (y data)
%line 3: creates a new variable at the maximum y value
%line 4: references the beginning x value (value in cell A1)
%line 5: references the end x value (value in cell A15339)
%line 6: uses the trapz function to calulate area bound UNDER the curve
%line 7: calculates rectangular area bound by [y=0 to y=data max value]*[x=0 to x=end value(15339)]
%line 8: negates the trapz area from rectangular area to get area ABOVE curve
%line 9: multiplication of the CSA over the distance between passes

Is there an easier way of renaming all of these variables and column lengths by hand? As in, how can I avoid referencing each sheet from P1 to P25 manually and having to change each variable name (shown below) each time? Could I use a for loop to do this (i.e. i=1:25)?

P1DataPoints = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P1','Range','A1:A15339'); 
P1Depths = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P1','Range','B1:B15339'); 
P1MaxDepth = max(P1Depths); 
P1LHS = P1DataPoints(1,1); 
P1RHS = P1DataPoints(15339,1); 
P1CurveArea = trapz(P1DataPoints, P1Depths);
P1RectangularArea = (P1RHS - P1LHS)*(P1MaxDepth);
P1WearArea = P1RectangularArea - P1CurveArea;
P1WearVolume = (P1WearArea * 170)*1e-9;

P2DataPoints = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P2','Range','A1:A15347'); 
P2Depths = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P2','Range','B1:B15347'); 
P2MaxDepth = max(P2Depths); 
P2LHS = P2DataPoints(1,1); 
P2RHS = P2DataPoints(15347,1); 
P2CurveArea = trapz(P2DataPoints, P2Depths);
P2RectangularArea = (P2RHS - P2LHS)*(P2MaxDepth);
P2WearArea = P2RectangularArea - P2CurveArea;
P2WearVolume = (P2WearArea * 170)*1e-9;

...
    
P25DataPoints = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P25','Range','A1:A15337'); 
P25Depths = readmatrix('Filtered Data 22nd Nov.xlsx','Sheet','P25','Range','B1:B15337'); 
P25MaxDepth = max(P25Depths); 
P25LHS = P25DataPoints(1,1); 
P25RHS = P25DataPoints(15337,1); 
P25CurveArea = trapz(P25DataPoints, P25Depths);
P25RectangularArea = (P25RHS - P25LHS)*(P25MaxDepth);
P25WearArea = P25RectangularArea - P25CurveArea;
P25WearVolume = (P25WearArea * 170)*1e-9;

I am relitavely inexperienced with MATLAB, so any help would be greatly appreciated!

[Stephen23]

"Is there an easier way of renaming all of these variables and column lengths by hand?"

Yes, there is a much easier way: use indexing.

"As in, how can I avoid referencing each sheet from P1 to P25 manually and having to change each variable name (shown below) each time? Could I use a for loop to do this (i.e. i=1:25)?"

The most important step is to use indexing, and not number the variable names like you show (i.e. pseudo-indices).

Once you use indexing, then looping over the data is easy and efficient (exactly as Sugandhi shows).

How to use indexing is explained here:

https://www.mathworks.com/help/matlab/getting-started-with-matlab.html

https://www.mathworks.com/company/newsletters/articles/matrix-indexing-in-matlab.html

Answer 1

[Sugandhi]

Hi Sophie Lord,

I understand that you want to avoid referencing each sheet and change each variable name each time manually.

To avoid manually referencing each sheet and renaming variables, you can indeed use a for loop in MATLAB. Here's an example of how you can modify your code to achieve this:

% Initialize variables
numSheets = 25;
dataPoints = cell(numSheets, 1);
depths = cell(numSheets, 1);
maxDepths = zeros(numSheets, 1);
LHS = zeros(numSheets, 1);
RHS = zeros(numSheets, 1);
curveAreas = zeros(numSheets, 1);
rectangularAreas = zeros(numSheets, 1);
wearAreas = zeros(numSheets, 1);
wearVolumes = zeros(numSheets, 1);

% Loop through each sheet
for i = 1:numSheets
    % Generate the sheet name (e.g., P1, P2, P3...)
    sheetName = ['P', num2str(i)];
    
    % Read data from Excel sheet
    dataPoints{i} = readmatrix('Filtered Data 22nd Nov.xlsx', 'Sheet', sheetName, 'Range', 'A1:A15339');
    depths{i} = readmatrix('Filtered Data 22nd Nov.xlsx', 'Sheet', sheetName, 'Range', 'B1:B15339');
    
    % Calculate other variables
    maxDepths(i) = max(depths{i});
    LHS(i) = dataPoints{i}(1);
    RHS(i) = dataPoints{i}(end);
    curveAreas(i) = trapz(dataPoints{i}, depths{i});
    rectangularAreas(i) = (RHS(i) - LHS(i)) * maxDepths(i);
    wearAreas(i) = rectangularAreas(i) - curveAreas(i);
    wearVolumes(i) = (wearAreas(i) * 170) * 1e-9;
end

In this modified code, an array of cells (`dataPoints` and `depths`) is used to store the data from each sheet, and the other variables (`maxDepths`, `LHS`, `RHS`, `curveAreas`, `rectangularAreas`, `wearAreas`, `wearVolumes`) are stored in arrays. The loop iterates over the sheet numbers (from 1 to 25) and performs the necessary calculations for each sheet.

After the loop finishes executing, you can access the data for each sheet using the respective arrays (`dataPoints`, `depths`, etc.). For example, `dataPoints{1}` will give you the data from the first sheet (P1), `dataPoints{2}` will give you the data from the second sheet (P2), and so on.

Note: Make sure that the number of entries (15339, 15347, etc.) for each sheet is consistent with your data. Adjust those values in the loop if necessary.