Google Sheets多值查找问题：Distribution标签页数据展示异常求助

标签背景说明 #

• Reward Tables：手动维护奖励/徽章（A列）、佩戴位置（B列）、颜色（C列）
• Roster：手动维护成员年级/身份（A列）、姓名（B列）
• Reward Logs：从网站复制的奖励日志，含获奖者姓名（A列）、奖励（B列），可能存在未定义条目
• Master List：整合数据，通过IMPORTRANGE()获取Reward Logs数据，用XLOOKUP()匹配年级、佩戴位置、颜色
• Distribution：目标展示页，需按年级/身份列出所有成员，显示各颜色徽章拥有情况，当前存在三个问题

问题解决方法 #

1. 解决数据透视表遗漏无奖励成员的问题 #

数据透视表默认只显示有匹配数据的行，要包含所有Roster成员，需先构建包含全部成员的基础数据集：

1. 用QUERY函数关联Roster和Master List，保留所有Roster成员：

   =QUERY({Roster!A:B, IFERROR(VLOOKUP(Roster!B:B, 'Master List'!A:E, {3,4,5}, FALSE), {"?", "N/A", "N/A"})}, "SELECT * WHERE Col2 IS NOT NULL", 1)
   

2. 基于这个新数据集创建数据透视表，即可显示所有成员，包括无奖励记录的（如Name5）。

2. 解决无获奖奖励不显示表头的问题 #

要强制显示所有Reward Tables中的颜色/奖励类型，需预先定义所有列，再用函数填充：

1. 从Reward Tables提取所有唯一颜色，作为Distribution的表头：

   =UNIQUE('Reward Tables'!C2:C)
   

2. 用COUNTIFS函数逐个单元格填充成员的徽章拥有情况，比如在B7单元格（对应Name1，红色徽章）：

   =COUNTIFS('Master List'!$A:$A, $A7, 'Master List'!$E:$E, B$2)
   

   下拉填充所有行和列，即使某颜色无获奖者，表头也会保留。

3. 解决COUNTIF+XLOOKUP嵌套公式错误的问题 #

原公式逻辑错误，无法同时匹配姓名和颜色两个条件，直接用COUNTIFS实现多条件计数即可：

• 替换原错误公式为：

  =COUNTIFS('Master List'!$A:$A, $A7, 'Master List'!$E:$E, B$2)
  

  其中$A7是当前行的成员姓名，B$2是当前列的徽章颜色，该公式会统计对应姓名获得对应颜色徽章的次数，返回1表示拥有，0表示未拥有。

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