excel - 跨表搜索和条件替换

Question

我是一名普通的 Excel 用户，熟悉基本的单元格公式和一些内置函数，但不知道如何解决这个问题。我收到了一本有两张纸的作业簿。第一张表有超过 10K 行的数据，跨很多列。 D、G 和 H 列值得关注。

G 列和 H 列各有 3 个字符的符号，视为一对，例如 G12 和 H12、G56 和 H56。符号不是唯一的，但符号对却是唯一的。 D 列包含相同的单词 TEST。所以我们有这样的东西:

--D--   --G--   --H--  
TEST    ABC     LMN  
TEST    ABC     XYZ  
TEST    GHI     LMN  
TEST    JKL     QRS  
TEST    LMN     JKM  
TEST    LMN     XYZ  
TEST    UVW     AMB  

第二张纸有大约 2800 行，两列 A 和 B，包含相同类型的 3 字符符号对。它们没有排序，但我们确实知道 Sheet 2 上的每一对都在 Sheet 1 中的某个位置，例如

--A--   --B--  
GHI     LMN  
UVW     AMB  
ABC     XYZ  
LMN     XYZ  

对于工作表 1 上的每个符号对，我需要确定该对是否在工作表 2 上，如果是，则需要将 D 列中的相应 TEST 更改为 DONE。所以结果是:

--D--   --G--   --H--    
TEST    ABC     LMN  
DONE    ABC     XYZ  
DONE    GHI     LMN  
TEST    JKL     QRS  
TEST    LMN     JKM  
DONE    LMN     XYZ  
DONE    UVW     AMB  

有谁知道一种相当简单的方法来做到这一点，也许使用内置的 Excel 函数？这是一次性的事情，所以我不需要任何太花哨或“面向 future ”的东西。丹

Answer 1

为此，您可以使用如下公式:

=IF(SUMPRODUCT((--(Sheet2!$A$1:$A$4&Sheet2!$B$1:$B$4=Sheet1!$E1&Sheet1!$F1)))>0,"Done","Test")`

您需要将 Sheet1 和 Sheet2 更改为您的实际工作表名称，并扩大 $A$1:$A$4< 的范围 和 $B$1:$B$4 以匹配您的实际值。

这个公式的本质是连接(即组合)两列数据(Sheet2!$A$1:$A$4 和 Sheet2!$B$1:$B$4)并查找串联值(Sheet1!$E1 和 Sheet1!$F1)。如果找到，公式返回TRUE；否则，返回FALSE。

但是，您必须确保将 -- 放入其中，以便将其转换为 TRUE/分别将 FALSE 更改为 1/0。由于 SUMPRODUCT 无法识别字符串，因此需要数字才能进行计算。然后，通过将 SUMPRODUCT 包装在 IF 函数中，您可以在适当的情况下返回请求的 Done 值。

<小时/>

编辑:

正如 @CallumDA 指出的，您还可以使用以下公式来完成此操作:

=IF(COUNTIFS(Sheet2!$A$1:$A$4,Sheet1!$E1,Sheet2!$B$1:$B$4,Sheet1!$F1)>0,"完成","测试”)

这两个公式将执行完全相同的操作(这是 Excel 的最酷的部分!)，因此两者都应该可以满足您的需求。我同意 COUNTIFS 看起来更友好一些，但我相信了解 SUMPRODUCT 公式的工作原理也是值得的，即使只是为了将来的知识。