创建表数据

• 需要手动设置日期列的格式。

• 可能（很可能）用单元格引用替换输入。

对于Events和Things，每个值使用一个单元格。

=LET(FirstDate,DATEVALUE("26-6-2023"),dDays,5,
    Events,VSTACK("Morning","Noon","Night"),
    Things,HSTACK("x","y","z"),
    ec,TOCOL(Events),tr,TOROW(Things),
    eRows,ROWS(ec),totRows,eRows*dDays,tCols,COLUMNS(tr),
    d,FirstDate+INT(SEQUENCE(totRows,,0)/eRows),
    e,INDEX(ec,MOD(SEQUENCE(totRows,,0),eRows)+1),
    t,INDEX(tr,SEQUENCE(totRows,,1,0),SEQUENCE(,tCols)),
HSTACK(d,e,t))

• 如果你想保留硬编码的值，这可能是一个更好的方法：

=LET(FirstDate,DATEVALUE("26-6-2023"),dDays,5,
    Events,"Morning,Noon,Night",Things,"x,y,z",
    ec,TOCOL(TEXTSPLIT(Events,",")),tr,TOROW(TEXTSPLIT(Things,",")),
    eRows,ROWS(ec),totRows,eRows*dDays,tCols,COLUMNS(tr),
    d,FirstDate+INT(SEQUENCE(totRows,,0)/eRows),
    e,INDEX(ec,MOD(SEQUENCE(totRows,,0),eRows)+1),
    t,INDEX(tr,SEQUENCE(totRows,,1,0),SEQUENCE(,tCols)),
HSTACK(d,e,t))

=MAKEARRAY(B2*3,5,
    LAMBDA(r,c,
CHOOSE(IF(c>3,3,c),
       INT(B1+(r-1)/3),
       CHOOSE(MOD(r-1,3)+1,"morning","noon","night"),
       CHOOSE(c-2,"x","y","z"))))

其中：B1是开始日期，B2是要列出的不同天数。

公式方法：
在单元格A1中输入起始日期
在单元格B1 ="Morning"中
在单元格B2 =IF(B1="Morning","Noon",IF(B1="Noon","Night",IF(B1="Night","Morning","Morning")))中
在单元格A2 =IF(B2="Morning",A1+1,A1)中
拖动可向下自动填充公式。

就像这样：

IF(COUNTIFS($B$1:B1,"="&B1)=3,B1+1,B1)

然后你可以在早上、中午和晚上这样做：

=IF(COUNTIFS($B$1:B4,"="&B4)=1,"Morning",IF(COUNTIFS($B$1:B4,"="&B4)=2,"Noon",IF(COUNTIFS($B$1:B4,"="&B4)=3,"Night","Error")))

但是一个简单的vlookup（）更容易：

你也可以试试这个：

·单元格B5中使用的公式

=LET(
      _nOfDays,E3,
      _timeOfDay,{"Morning","Noon","Night"},
      _todCols,COLUMNS(_timeOfDay),
      _rowsNeeded,_nOfDays*_todCols,
      _firstDate,DATE(D3,B3,C3),
      _dateIncrement,_firstDate+INT(SEQUENCE(_rowsNeeded,,0,1/_todCols)),
      _timeOfDayfill,INDEX(_timeOfDay,MOD(SEQUENCE(_rowsNeeded,,0),_todCols)+1),
      _things,{"x","y","z"},
      _header,HSTACK("Date","Time Of Day","Things"&SEQUENCE(,COLUMNS(_things))),
       IFERROR(VSTACK(_header,
        HSTACK(_dateIncrement,_timeOfDayfill,IFNA(_things,SEQUENCE(ROWS(_timeOfDayfill))))),
       "Please enter the Month,Day,Year and #ofDays in respective cells"))

不使用LAMBDA（）的简单方法

=LET(
        a,H1,
        b,I1,
        c,SEQUENCE(b*3,,0),
        d,INT(a+c/3),
        e,INDEX({"Morning","Noon","Night"},MOD(c,3)+1),
        f,IFNA({"x","y","z"},SEQUENCE(b*3)),
        HSTACK(d,e,f))

或，使用SWITCH（）代替INDEX（）

=LET(
        a,H1,
        b,I1,
        c,SEQUENCE(b*3,,0),
        d,INT(a+c/3),
        e,SWITCH(MOD(c,3)+1,1,"Morning",2,"Noon","Night"),
        f,IFNA({"x","y","z"},SEQUENCE(b*3)),
        HSTACK(d,e,f))