Find a value in excel file
36 次查看(过去 30 天)
显示 更早的评论
Hi everybody! Hope that somebody can help me, I'm relative new to matlab and are wondering if I have an excel file with a table containing both names an numbers, and I want to use a number related to one of the names in a function when the name is an input argument, am I supposed to use a loop or what?
EX:
Name Bought Sold
Eric 2 13
Linda 7 4
Jasmine 3 8
Fredrik 10 9
I want to have the name as an argument ex:
Function CalculateTotal (name)
The function should then "find" the right name and use the values related to that person so that for exampel;
CalculateTotal (Eric)
Should be 2+13=15
Any one who can help me?
0 个评论
采纳的回答
Geoff Hayes
2014-8-15
编辑:Geoff Hayes
2014-8-15
Viktoria - you can use xlsread to read the data from the file, either dividing it up into numeric and text data, or all data into one cell array. Since you have a header column, the latter might be the way to go
[~,~,rawData] = xlsread('myFile.xls);
In your example, rawData should be a 5x3 cell array.
Now, use find to determine where in the first column of rawData is there a match to the name. If there is a match, find will return an index of the row in which that name is found. If there is no match, then an empty matrix is returned
name = 'Eric';
% look at all row elements in the first column returning
% the first match only
idx = find(strcmp(rawData(:,1),name),1);
if ~isempty(idx)
total = sum(cell2mat(rawData(idx,2:end)));
else
total = 0;
end
In the above, if our index is not empty, then we grab all elements in the row idx from the second column onwards (using rawData(idx,2:end)). This returns a cell array so we need to convert it to a matrix which we do with cell2mat. Then we can easily sum the elements giving us our total.
Note that if you wish to use a function, your signature should be something like
function [total] = CalculateTotal (name)
Rather than opening the Excel file each time you call this function, you may just want to pass the rawData as an input
function [total] = CalculateTotal (rawData,name)
Try the above and see what happens!
0 个评论
更多回答(2 个)
Image Analyst
2014-8-15
I think the simplest, cleanest, and most elegant way is to use a table. This works great but only if you have version R2013b or later:
function test2
% The main routine.
clc;
t = readtable('D:\Temporary stuff\Book1.xlsx')
% Call the function
[numberBought, numberSold] = CalculateTotal('Eric', t)
% The function definition.
function [bought, sold] = CalculateTotal(personName, t)
row = ismember(personName, t.Name) % Find row where this person is stored.
if row > 0
bought = t.Bought(row);
sold = t.Sold(row);
else
% Name was not found.
sold = 0;
bought = 0;
end
Of course you can make it more robust by calling lower() to make it case insensitive, using try catch, alerting user with warndlg() if the name is not found, handling the case where the same name shows up in multiple rows, etc.
0 个评论
Bereketab Gulai
2020-5-27
Alternative with actxserver:
excelApp = actxserver("excel.Application");
excelApp.Visible = false;
book1 = excelApp.Workbooks.Open('D:\Temporary stuff\Book1.xlsx');
% Sheet item 1 ...
sheetOne = book1.Sheets.Item(1);
foundInterfaceObj = sheetOne.Range("A:A").Find('Eric');
The returned Interface provides lots functions you may need. Row will give the row number, Value for the cell...
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
产品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!