Reading specific cells values from spreadsheets
18 views (last 30 days)
Nicholas Brimacombe on 14 Mar 2022
I am trying to use a function that will allow me to read the values of different cells in an excel spreadsheet depending on the value of a given variable. Eg. If variable = x then read value in cell A1, but if variable = y then read value in cell B1. I know this can be done using an 'if' or 'for' statement but is there a better was to do it given that I have over 3000 potential values for the variable and each has a different value in a specific cell.
Ganesh Gudipati on 17 Mar 2022
As per my understanding, you are retrieving a particular cell based on the value of a variable.
If the values of variable used to retrieve a cell is contiguous then we can directly use that variable as index to locate and retrieve a cell. Let’s say we have some data in spreadsheet, and we read it into a table in MATLAB. Now, we have a linear contiguous vector that represents values associate with each cell in the table.
index=100, we will retrieve cell 0
index=101, we will retrieve cell 1
index=102, we will retrieve cell 2
index=103, we will retrieve cell 3 and so on…
The below code can help to retrieve a particular cell from 2D table
%retrive cell when the value of variable =105;
index = 105 - v(1); %Convert to 0 index
row = floor(index/3)+1 ; %no of rows in this table = 3
column = mod(index,3) + 1;
data(row,column) %accessing a cell
The corresponding output will be :
If the values of variable are non-contiguous then try to make the 2D table data as a column vector table and append the values of variable used for indexing as a column to the table. Now we can perform a query when we want to retrieve a cell. We are looking for a cell where the value of variable (column 2 in table) is 105.
i = (data.Col2==105); %getting the row no that has value of variable as 105
data(i,"Col1") %col2 has the variable and col1 is the actual cell we are looking for
For more information refer Accessing data in Tables.