Google Sheets两工作表数据匹配比对及第三表生成规则求助

需求规则 #

• 若工作表2对应单元格为“Completed”但工作表1中不是，第三表对应单元格显示“Completed”
• 若工作表2对应单元格不为“Completed”，第三表显示工作表1对应单元格内容

示例数据 #

工作表1数据 #

Institution name    Management  Radiology   Nuclear number  Pathology   Laboratorie Systemic    Pediatric   Radiotherapy    Surgery Palliative
Institute 1           Log in    Log in      Not Completed   Log in      Log in      Log in      Log in      Completed       Log in
Institute 2       Not Completed Completed   Not Completed   Not Completed   Log in  Log in      Log in     Not Completed  Completed Completed
Institute 3     Not Completed   Not Completed   Log in      Completed   Completed   Log in      Log in     Completed

工作表2数据 #

Institution name    Management  Radiology   Nuclear number  Pathology   Laboratorie Systemic    Pediatric   Radiotherapy    Surgery Palliative
Institute 1       Not Completed Not Completed   Completed   Not Completed   Not Completed   Not Completed       Not Completed   Completed   Not Completed
Institute 2       Not Completed Completed   Completed   Not Completed    Not Completed   Not Completed  Not Completed   Completed   Not Completed   Not Completed
Institute 3    Not Completed    Not Completed   Not Completed   Not Completed   Not Completed   Not Completed       Not Completed   Not Completed

实现方法 #

假设三个工作表分别命名为Sheet1、Sheet2、doublecheck（预期结果表），按以下步骤操作：

单个单元格公式（手动填充） #

在doublecheck的A1单元格输入公式，然后向右、向下拖动填充至所有数据区域：

=IF(AND(Sheet2!A1="Completed", Sheet1!A1<>"Completed"), "Completed", Sheet1!A1)

数组公式（自动覆盖全区域） #

如果需要一次性处理所有数据，在doublecheck的A1单元格输入数组公式（需确保目标区域为空）：

=ARRAYFORMULA(IF((Sheet2!A1:K="Completed")*(Sheet1!A1:K<>"Completed"), "Completed", Sheet1!A1:K))

公式说明 #

• (Sheet2!A1:K="Completed")*(Sheet1!A1:K<>"Completed")：判断是否满足规则1的双重条件
• 满足条件时返回"Completed"，否则直接引用Sheet1的对应单元格内容，匹配规则2

内容的提问来源于stack exchange，提问作者user16239103