Google Sheets中如何关联orderdate与item拆分多列数据为多行

问题场景 #

现有表格结构（宽表）：

firstname1, address1, orderdate1, item1, orderdate2, item2, orderdate3, item3
firstname2, address2, orderdate1, item1, orderdate2, item2
firstname3, address3, orderdate1, item1

期望转换为（窄表，每对日期+商品占一行）：

firstname1, address1, orderdate1, item1
firstname1, address1, orderdate2, item2
firstname1, address1, orderdate3, item3
firstname2, address2, orderdate1, item1
firstname2, address2, orderdate2, item2
firstname3, address3, orderdate1, item1

原公式错误地将日期和商品拆分为单独行，现在需要实现日期与商品的配对同行。

解决方案公式 #

方案1（动态适配列数） #

=LET(
    // 获取有效数据范围
    last_row, MATCH(,0/(Sheet1!A:A<>""),1),
    last_col, MAX(COLUMN(Sheet1!2:2)*(Sheet1!2:2<>"")),
    data_range, Sheet1!A2:INDEX(Sheet1:A:Z, last_row, last_col),
    // 提取固定列（姓名、地址）
    names, INDEX(data_range,,1),
    addresses, INDEX(data_range,,2),
    // 提取所有日期列（从第3列开始，每2列取1列）
    date_cols, SEQUENCE(ROUNDUP((COLUMNS(data_range)-2)/2),1,3),
    dates, TOCOL(INDEX(data_range,,date_cols),1),
    // 提取所有商品列（从第4列开始，每2列取1列）
    item_cols, SEQUENCE(ROUNDUP((COLUMNS(data_range)-2)/2),1,4),
    items, TOCOL(INDEX(data_range,,item_cols),1),
    // 组合并过滤空行
    FILTER(HSTACK(names, addresses, dates, items), dates<>"")
)

方案2（用REDUCE逐行处理） #

=LET(
    empty, TOCOL(,1),
    last_row, MATCH(,0/(Sheet1!A:A<>""),1),
    last_col, MAX(COLUMN(Sheet1!2:2)*(Sheet1!2:2<>"")),
    data_range, Sheet1!A2:INDEX(Sheet1:A:Z, last_row, last_col),
    REDUCE(empty, SEQUENCE(ROWS(data_range)), LAMBDA(acc, row_num,
        LET(
            fixed_vals, INDEX(data_range, row_num, 1):INDEX(data_range, row_num, 2),
            // 计算当前行有多少对日期+商品
            pair_count, (COLUMNS(data_range)-2)/2,
            // 提取当前行的所有日期+商品对
            pairs, IF(pair_count>0, INDEX(data_range, row_num, SEQUENCE(pair_count,2,3)), ""),
            // 固定值与每对数据组合
            expanded_rows, IFERROR(HSTACK(fixed_vals, pairs), fixed_vals),
            // 追加到结果中
            VSTACK(acc, expanded_rows)
        )
    ))
)

原公式问题分析 #

你之前的公式遍历了C2开始的每个单元格，每个单元格单独生成一行，导致日期和商品被拆分成独立行。而我们需要将每两列（日期+商品）作为一组，整体与姓名、地址组合生成一行。

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