Using Excel files in function

2019 2 7
2 个回答
回答已采纳
更新时间：2019 7 27
1 次查看（30 天）

0 个投票

I have a spreadsheet of list of cities and distances between them such as
New York Washington DC Los Angeles
New York 0 500 . 1000
Washington DC 500 0 . 1500
Los Angeles 1000 1500 0
how do i create a function which takes 2 cities as input and gives the distance as output, given that invalid city names give 0 as distance?
I tried a few things but honestly I don't know how to proceed with this problem.

2 个评论
显示 无 隐藏 无

Bob Thompson
Bob Thompson 2019-2-7
Could you expand a little on what things you tried, and why they didn't work? That will help us give you a more clear answer, rather than something super general.
Perturabo
Perturabo 2019-2-7
编辑：Perturabo 2019-2-7
I tried reading the excel raw data into a matrix and returning the element at m,n where m and n are the cities. But it doesn't work and it returns the whole cell array itself. But honestly I didn't understand file I/O in MATLAB enough to handle functioning with excel.

请先登录，再进行评论。

请先登录，再回答此问题。

 采纳的回答

Bob Thompson
Bob Thompson 2019-2-7
编辑：Bob Thompson 2019-2-7

2 个投票

This is a quick first cut, so you will likely need to do some fine tuning of your own. I am assuming that your city distance data is one large square array, with city names in first row and first column.
function [distance] = city_distance(city1,city2);
% Read in the city data, keeping all data in cells
[~,~,data] = xlsread('mycityfile.xlsx');
% Check for first city
c1 = cellfun(@(x) strcmp(x,city1),data(:,1)); % Edit
if c1 == 0; % Edit
error('No match was found for the first city.')
end
% Check for the second city
c2 = cellfun(@(x) strcmp(x,city2),data(1,:)); % Edit
if c2 == 0; % Edit
error('No match was found for the second city.')
end
% Distance determination
distance = data{c1,c2};
end

11 个评论
显示 9更早的评论 隐藏 9更早的评论

Perturabo
Perturabo 2019-2-7
编辑：Perturabo 2019-2-7
I more or less understand the code, but this doesn't give error for invalid cities. The cities in the spreadsheet are all US cities, but when I run it with input London, it still provides an answer. Also, it is providing answers that aren't correct, or even in nearby cells so that I can use
distance = data{c1+something,c2+something}
Also, the first cell in the spreadsheet is empty if that is the reason for error.
Thanks for your help, in any case.
Bob Thompson
Bob Thompson 2019-2-7
How comprehensive is the list of cities? There is a London, Ohio, but I imagine it's not particularly large.
What do you mean by 'answers that aren't correct'? Is it picking the wrong city, or not locating the correct distance cell?
What 'error' are you receiving? Can you post the exact error message?
Bob Thompson
Bob Thompson 2019-2-7
Please note edits to my answer, regarding finding the names.
Perturabo
Perturabo 2019-2-7
The function isn't giving any error. But the values are not as expected. For example, it gives cell 530 for Seattle and Miami, while the actual distance in that cell is 3700. And as for the size of the spreadsheet, it is 340×340.
And since it is such a huge cell, I can't calculate how far off the output is from the targeted cell.
Bob Thompson
Bob Thompson 2019-2-7
Can you post your spread sheet?
Bob Thompson
Bob Thompson 2019-2-7
Using your excel sheet with the editted code above yeilds the correct answers for every case I tested. Have you copied my edits in yet? I did change the finding function to increase its accuracy.
Perturabo
Perturabo 2019-2-8
编辑：Perturabo 2019-2-8
Works perfectly. Thank you very much.
the problem was that I didn't know how to do this:
c1 = cellfun(@(x) strcmp(x,city1),data(:,1));
Ajai Kannan
Ajai Kannan 2019-2-17
编辑：Ajai Kannan 2019-2-17
Hello!
I was looking for a way to solve the same problem. This question is from a coursera course and the thing is we were not taught about the cellfun() function and when i tried the help command, I could understand nothing. Can you please explain how that works?
Meanwhile, the following was the code I tried to solve and it gave me errors in the if statement.
function distance = get_distance(a,b)
[~,text,raw] = xlsread('Distances.xlsx');
for i = 2 : size(text,1)
if text{i,1} == a
for j = 1 : size(text,2)
if text{2,j} == b
distance = raw{i,j};
else
distance = -1;
end
end
else
distance = -1;
end
end
Priyamvada Shankar
Priyamvada Shankar 2019-3-23
Have you got that where you went wrong,if yes then help me also... I'm too stuck in this question.
Mahesh Sharma
Mahesh Sharma 2019-7-27
hey
try this one , this works.
function distance = get_distance(A,B)
[~,~,raw] = xlsread('Distances.xlsx');
distance = -1;
for i = 2 : size(raw,1)
if strcmp(A,raw{1,i})
for j = 2 : size(raw,2)
if strcmp(B,raw{j,1})
distance = raw{j,i};
return
end
end
else
distance = -1;
end
end
end

请先登录，再进行评论。

更多回答（1 个）

Ana Guerreiro
Ana Guerreiro 2019-4-27

0 个投票

Hi Bob Nbob
Your code does not work for "non-existent city" that needs to be equal to -1 (Distance= -1).
Can you help me, showing how to do it, please?
Best regards

类别

在 帮助中心 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

产品

版本

R2018a

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by