如何在Google Sheets/Excel中按列名字符串转换cohort数据至目标格式

数据说明 #

原始表tableA #

cohort   q1   value_JAMES  value_PETER value_JOHN
     A    1          col1   col1, col2     
     A    2    col1, col2   col1, col2       col2     
     B    1                 col1, col2 col1, col2    

目标表tableB（预设空结构） #

q1  NAME    col1    col2
 1 JAMES
 1 PETER
 1  JOHN
 2 JAMES
 2 PETER
 2  JOHN
 3 JAMES
 3 PETER
 3  JOHN

转换规则 #

• 仅处理tableA中cohort为A的记录
• 按q1+NAME匹配tableA中对应value_XXX列（如NAME=JAMES则匹配value_JAMES）
• 检查col1/col2是否在匹配到的单元格内容中，存在填1，不存在填0，无匹配数据则留空

实现方案 #

Google Sheets 公式 #

在tableB的C2单元格（对应q1=1、JAMES的col1）输入以下公式，然后横向、下拉填充至所有需要填充的单元格：

=IFERROR(IF(REGEXMATCH(INDEX(FILTER(tableA!$C:$E, tableA!$A:$A="A", tableA!$B:$B=$A2), MATCH("value_"&$B2, tableA!$C$1:$E$1, 0)), "col"&RIGHT(CELL("address", C2),1)), 1, 0), "")

核心逻辑：

1. 筛选tableA中符合cohort=A且q1匹配的行
2. 定位当前NAME对应的value_列
3. 正则匹配检查col1/col2是否存在，返回对应二进制值

Excel 公式（365/2021+版本） #

在tableB的C2单元格输入：

=IFERROR(IF(ISNUMBER(SEARCH("col"&RIGHT(CELL("address",C2),1), INDEX(FILTER(tableA!$C:$E, (tableA!$A:$A="A")*(tableA!$B:$B=$A2)), MATCH("value_"&$B2, tableA!$C$1:$E$1, 0)))), 1, 0), "")

若使用旧版Excel（无FILTER函数），改用数组公式：

=IFERROR(IF(ISNUMBER(SEARCH("col"&RIGHT(CELL("address",C2),1), INDEX(tableA!$C:$E, SMALL(IF((tableA!$A:$A="A")*(tableA!$B:$B=$A2), ROW(tableA!$A:$A)), 1), MATCH("value_"&$B2, tableA!$C$1:$E$1, 0)))), 1, 0), "")

输入后按Ctrl+Shift+Enter执行（数组公式需此操作）

最终结果 #

q1  NAME    col1    col2
 1 JAMES       1       0
 1 PETER       1       1
 1  JOHN       0       0
 2 JAMES       1       1
 2 PETER       1       1
 2  JOHN       0       1 
 3 JAMES
 3 PETER
 3  JOHN

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