我手头有一些数据来自源站点，格式看起来像PDF，但实际上是CSV形式。

如你所见，每个学生都有一个“头部信息块”，其中包含多列，用来概括姓名、地址、年级等信息。学生的姓名总是在第一个课程名称上方的五行位置。

我想把它整理成一个比较合理的格式，如下所示：

本质上，问题是这样的：

如何从头部提取学生姓名和学年，然后将数据重新格式化为上面的示例？

以下是一个示例电子表格 。Tab 1是导出时CSV文件的原始输出，Tab 2是我希望它的样子（完全通过复制粘贴实现，我不想对超过1,000条记录都这么做）。

解决方案

将数据按 NAME: 行分成若干部分，对每个部分分别通过 reduce() 处理，再将当前部分展开为行，并在每一行添加学生姓名和学年，如下所示：

=let( 
  unpivot_, lambda(data, numFixedCols, numColsPerGroup, let(k,n(numFixedCols),d,if(k,data,hstack(sequence(rows(data)),data)),f,if(k,k,1),g,numColsPerGroup,s,lambda(r,c,h,w,chooserows(choosecols(d,sequence(1,w,c)),sequence(h,1,r))),h,hstack(s(1,1,1,f),,),i,sequence(1,(columns(d)-f)/g,f+1,g),a,reduce(h,sequence(rows(d)-1,1,2),lambda(a,r,let(x,s(r,1,1,f),b,reduce(tocol(æ,2),i,lambda(y,c,let(z,s(r,c,1,g),if(""=+z,y,vstack(y,hstack(x,s(1,c,1,1),z)))))),vstack(a,b)))),if(k,a,choosecols(a,sequence(1,columns(a)-1,2))))), 

  raw, Sheet1!A4:F,
  col.1, choosecols(raw, 1),
  rowIndices, sequence(rows(col.1)),
  rowStarts, filter(rowIndices, regexmatch(col.1, "^NAME:")),
  reduce(tocol(æ, 2), sequence(rows(rowStarts)), lambda(a, i, let(
    rowStart, index(rowStarts, i),
    rowEnd, iferror(index(rowStarts, i + 1) - 1, rows(raw)),
    data, filter(raw, len(col.1), isbetween(rowIndices, rowStart + 0, rowEnd)),
    labels, choosecols(data, 1),
    name, regexextract(labels, ": (.+)"),
    year, +filter(labels, regexmatch(labels, "\d\d\d\d - \d\d\d\d")),
    weeded, filter(data, sequence(rows(data)) > 4),
    unpivoted, unpivot_(weeded, 2, 1), 
    filtered, filter(unpivoted, choosecols(unpivoted, 3) <> "Final"),
    matrix, byrow(filtered, lambda(row, hstack(name, year, row))), 
    table, query(query(matrix, "order by Col5", 1), "offset 1", 0),
    vstack(a, table)
  )))
)

最终的表格只会包含已分配了成绩的行。

参见 let(), choosecols(), chooserows(), sequence(), filter(), byrow(), vstack(), hstack(), iferror(), reduce() 和 query() 。