Google Sheets：如何用公式生成日期与用户名的笛卡尔积表格？

基础数组公式方案（兼容所有版本） #

在C1单元格输入以下公式，自动生成所有日期的重复序列（每个日期对应14个用户名）：

=ARRAYFORMULA(INDEX(A:A, QUOTIENT(ROW(A1:A5110)-1, 14)+1))

对应D1单元格的用户名循环序列公式：

=ARRAYFORMULA(INDEX(B:B, MOD(ROW(A1:A5110)-1, 14)+1))

• 公式逻辑：
  • ROW(A1:A5110) 生成1到5110的行号，匹配目标总行数（365×14）
  • QUOTIENT(ROW()-1,14)+1：每14行重复一次A列的日期，确保每个日期对应所有用户名
  • MOD(ROW()-1,14)+1：循环提取B列的14个用户名，每一行切换一个用户

新版函数简化方案（仅支持新版Google Sheets） #

如果你的Sheet已启用MAKEARRAY函数，C1可以用更直观的写法：

=MAKEARRAY(365*14, 1, LAMBDA(r, c, INDEX(A:A, ROUNDUP(r/14, 0))))

D1对应的用户名公式：

=MAKEARRAY(365*14, 1, LAMBDA(r, c, INDEX(B:B, MOD(r-1, 14)+1)))

后续打卡记录查询 #

生成笛卡尔积后，假设打卡记录存储在E:G列（E=日期，F=用户名，G=打卡时间），可以在新列用以下公式批量查询：

=ARRAYFORMULA(IFERROR(INDEX(G:G, MATCH(C:C&D:D, E:E&F:F, 0)), "-"))

• 逻辑：通过C列日期&D列用户名的组合匹配打卡记录，无记录时返回-

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