Excel Sheet Sorting and Data Analysis

3 次查看（过去 30 天）
Carolina Benavides
评论： Scott MacKenzie 2021-6-3
I have an excel sheet with about 100,000 rows and 30 columns. I am trying to get averages of certain columns for rows that fit a certain description. For example, the excel sheet has names, grades, and favorite colors, and I am trying to find out the average grade of only the people that have "blue" listed in the favorite colors column.
I normally wouldn't use Matlab for this, but given that the spreadsheet is so big, I figured it would be beneficial to use it rather than sorting on Excel.
Any advise is appreciated!! I have used Matlab very sparingly in the past so I am still a beginner. Youtube videos/web links/reddit posts/etc. are appreciated too!

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

采纳的回答

Scott MacKenzie
Scott MacKenzie 2021-6-2
编辑：Scott MacKenzie 2021-6-2
Assuming the data for the names, grades, and favorite colors are in columns 1, 2, and 3, respectively, then the following code outputs the mean of the grades for the people whose favorite color is blue:
T = readtable('testdata.xlsx');
blueLogical = strcmp(T{:,3}, 'blue');
blueGrades = T{blueLogical,2};
mean(blueGrades)
  2 个评论
Carolina Benavides
Carolina Benavides 2021-6-3
Follow up question. Let's say that instead of wanting to get the averages of the scores, I want to get a count of the number of students whose favorite color is blue. Additionally, I want to know the percentage of the number of students whose favorite color is blue out of all the students. Then I want to find out of the percentage of the number of students whose favorite color is blue out of all the students whose favorite color is blue and are in 10th grade.
Scott MacKenzie
Scott MacKenzie 2021-6-3
These questions are all fairly easy to answer in MATLAB. For the first two...
% count of number of students whose favorite color is blue
sum(blueLogical)
% percentage of students whose farorite color is blue
sum(blueLogical) / height(T) * 100
For your last question, you didn't mention anything about the grade levels of students in your original question. But, if we assume column 4 contains the grade the student is in, then...
% percentage of all students in 10th grade and blue as favorite color
blueTenLogical = blueLogical & (T{:,4} == 10);
sum(blueTenLogical) / height(T) * 100

请先登录，再进行评论。

更多回答（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!

Translated by