excel - 如何使用 Matlab 或 Excel 从两个表中获取行的交集？

Question

我一直在谷歌搜索，但到目前为止发现结果并不完全符合我的需要。如果有人发布了相同问题的答案，有人可以告诉我吗？非常感谢。

我有两个来自两个 excel 文件的表，一个只包含数值，另一个包含 txt 和数值。例如 file1.xlsx 只有一个列向量 studentid 是整数，例如，

1  
3    
5     
11     
20 ....    

并且 file2.xlsx 包含一个工作表:

 studentID   Name     score    email        
 1           Cassie   60       test@gmail.com     
 2           John     100      john@gmail.com      
 3           Marry    80       ...    
 4           Bob      70       ...     
 5           Kevin    99       ....     

我想查找学生 ID 在 file1.xlsx 中的学生行，因此最终结果表应如下所示:

studentID  Name    score  email       
1          Cassie  60      test@gmail.com...     
3          Marry   80      Marry@gmail.com    
5          Kevin   99     kevin@gmail.com    
...    

我没有 SQL 或 Perl 等脚本语言。我知道在 matlab 中有一个 interset 函数，但是，它只适用于数值矩阵。对于我的 file2.xlsx，它包含数值和 txt 值。我不想把它们分开。而且我也不知道如何将 excel 文件读入单元格数组，因为该表同时具有数字和 txt 部分。如果有人知道如何通过使用 Matlab 或 Excel 来解决这个问题，我只需要得到最终结果表。

非常感谢你，

Answer 1

在 Excel 中，您使用公式在 file2.xlsx 中创建一个新列:

=MATCH(A2,[\path\file1.xlsx]Sheet1!$A$2:$A$20,0)

其中 $A$2:$A$20 是 file1.xlsx 中包含学生 ID 的范围。最后 0表示完全匹配。将此公式分发到所有行。如果 ID 不在 file1 中，该函数将在行中返回 #N/A。您可以使用此列过滤掉这些行。

为了更容易过滤，您可以添加 ISERROR 函数:

=NOT(ISERROR(MATCH(A2,[\path\file1.xlsx]Sheet1!$A$2:$A$20,0)))

那么 TRUE 将对应于现有的 ID，而 FALSE 将对应于缺失的 ID。

如果您在 file1.xlsx 中有附加列，并且想将其与 file2 中的数据结合起来，您可以使用 INDEX 函数:

=INDEX([\path\file1.xlsx]Sheet1!B$2:B$20,MATCH($A2,[\path\file1.xlsx]Sheet1!$A$2:$A$20,0),1)

注意 MATCH/INDEX 组合不需要像 VLOOKUP 那样对搜索列中的数据进行排序。

在 MATLAB 中，您可以使用 ISMEMBER 函数。

假设您已将这两个文件都导入 MATLAB，并且您有元胞数组 file1 和 file2。然后你可以这样做:

[~,~,file1] = xlsread('file1.xlsx');
[~,~,file2] = xlsread('file2.xlsx');
[idx2, idx1] = ismember(file2(:,1), file1(:,1));
filtered2 = file2(idx2,:);

要查找 file2.xlsx 中存在的 file1.xlsx 中的行并按相同顺序对它们进行排序:

idx1(idx1==0) = [];
filtered1 = file1(idx1,:);

如果要保持 file1.xlsx 中的行顺序，只需在 ismember 语句中切换 file2 和 file1 即可。