Google Sheets多表数据合并公式报错及格式问题求助

核心需求回顾 #

• 从「Raw Data」工作表按顺序提取E、U、D、C、I列
• 获取「Comments」工作表中与「Raw Data」D列（landowner）关联的所有评论（带日期格式）
• 从「Statuses」工作表获取对应landowner的最大状态值（MAX[status]）
• 提取「Raw Data」的F、W、G、K、A、B列，仅保留在「Statuses」和「Comments」中有数据的landowner，生成指定输出格式

原公式的关键错误 #

1. 语法错误：IF(c_id,F13 HSTACK(...)) 中缺少逗号分隔条件与结果，且F13为无效引用
2. 错误的行索引引用：INDEX(col_f, ROWS(HSTACK(substation))) 会返回固定行数的结果，无法匹配当前行数据
3. 无效的范围引用：table[[Column1]:[Column1]] 在MAP函数中无法正确识别结构化引用
4. 未实现筛选逻辑：未添加「仅保留Statuses和Comments有数据的landowner」的过滤条件
5. 评论空格问题：未对评论内容做去空格处理，导致合并后出现随机空格

修正后的公式 #

=LET(
    // 定义Comments表数据，提前处理评论格式与去空格
    comment_landowner, Comments!A2:A,
    comment_text, TRIM(Comments!B2:B),
    comment_date, Comments!C2:C,
    // 构建带日期的评论组合，过滤空landowner
    comment_map, FILTER(HSTACK(comment_landowner, TEXT(comment_date, "m/d/yy : ") & comment_text), comment_landowner<>""),
    
    // 定义Statuses表数据，构建landowner到最大状态的映射
    status_data, FILTER('Statuses'!A2:B, 'Statuses'!A2:A<>""),
    status_max_map, MAP(status_data[Column1], LAMBDA(x, MAX(FILTER(status_data[Column2], status_data[Column1]=x)))),
    status_lookup, HSTACK(status_data[Column1], status_max_map),
    
    // 定义Raw Data表核心数据，先过滤空landowner
    raw_data, FILTER('Raw Data'!A2:W, 'Raw Data'!D2:D<>""),
    raw_substation, INDEX(raw_data,0,5),   // E列
    raw_parcel, INDEX(raw_data,0,21),      // U列
    raw_landowner, INDEX(raw_data,0,4),    // D列
    raw_address, INDEX(raw_data,0,3),      // C列
    raw_acres, INDEX(raw_data,0,9),        // I列
    raw_dist_hub, INDEX(raw_data,0,6),     // F列
    raw_agent, INDEX(raw_data,0,23),       // W列
    raw_utility, INDEX(raw_data,0,7),      // G列
    raw_county, INDEX(raw_data,0,11),      // K列
    raw_lat, INDEX(raw_data,0,1),          // A列
    raw_lng, INDEX(raw_data,0,2),          // B列
    
    // 过滤仅在Statuses和Comments中存在的landowner
    valid_landowners, UNIQUE(VSTACK(comment_landowner, status_data[Column1])),
    filtered_raw, FILTER(raw_data, COUNTIF(valid_landowners, raw_landowner)>0),
    
    // 为过滤后的每行数据匹配评论和最大状态
    final_output, BYROW(filtered_raw, LAMBDA(row,
        LET(
            owner, INDEX(row,4),
            // 合并对应landowner的所有评论
            combined_comments, IFERROR(TEXTJOIN(CHAR(10), TRUE, FILTER(comment_map[Column2], comment_map[Column1]=owner)), ""),
            // 获取对应landowner的最大状态
            max_status, IFERROR(INDEX(status_lookup, MATCH(owner, status_lookup[Column1],0),2), ""),
            // 组装最终行数据
            HSTACK(
                INDEX(row,5), INDEX(row,21), owner, INDEX(row,3), INDEX(row,9),
                combined_comments, max_status,
                INDEX(row,6), INDEX(row,23), INDEX(row,7), INDEX(row,11), INDEX(row,1), INDEX(row,2)
            )
        )
    )),
    
    // 添加表头并输出结果
    VSTACK(
        {"Substation", "Parcel ID", "Landowner", "Address", "Acres", "Comments", "Max Status", "Distance from Hub", "Agent", "Utility", "County", "Latitude", "Longitude"},
        final_output
    )
)

修正说明 #

1. 修复语法错误：移除无效的F13引用，修正IF函数的语法结构
2. 解决空格问题：用TRIM(Comments!B2:B)去除评论内容前后的多余空格
3. 正确的行匹配：改用BYROW逐行处理数据，确保每行索引正确对应
4. 实现筛选逻辑：通过valid_landowners获取Statuses和Comments中所有存在的landowner，再过滤Raw Data中的对应行
5. 优化引用方式：用INDEX(raw_data,0,列号)替代单独引用整列，提升公式效率
6. 增强鲁棒性：添加IFERROR处理无匹配数据的情况，避免错误值显示

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