☰

重复带有标题和日期的复杂模式（同一天 6x）。标题，递增日期（同一天 6x）

tag-icon • tag-icon microsoft-excel
重复带有标题和日期的复杂模式（同一天 6x）。标题，递增日期（同一天 6x）

在 OneNote 中创建一个显示外部 Excel 布局的膳食跟踪器。由于 One Note 中没有锁定单元格，为了便于查看，我尝试在 Excel 中创建以下模式：

饮食追踪器

模式为：

文本行
日期行 [6 行日期相同]
日期行
日期行 日期
行
日期行
日期
行 文本行
日期 [从上面设置的日期开始递增] [6 行日期相同]
日期行
日期行
日期
行
日期行
文本行

答案1

公式

=LET(
  _start, $G$1,    _days, $G$2,
  _text, {"Date","Time","Meal","Food Served"},
  _meals, {"Breakfast","Snack","Lunch","Snack","Dinner","Snack"},
  _col1, TEXTSPLIT(TEXTJOIN(",",FALSE, INDEX(_text, 1)&","&
          (LAMBDA(x, SUBSTITUTE(REPT(TEXT(_start+x,"mm/dd/yyyy")&",",
          COUNTA(_meals))&",",",,",""))(SEQUENCE(_days,1,0,1)))),,","),
  _rows, _days*(COUNTA(_meals)+1),
  _col2, TEXTSPLIT(SUBSTITUTE(REPT(INDEX(_text,2)&REPT(",",COUNTA(_meals)+1),_days)&";",",;",""),,",",FALSE),
  _col3, TEXTSPLIT(SUBSTITUTE(REPT(TEXTJOIN(",",TRUE, INDEX(_text, 3),_meals)&",",_days)&",",",,",""),";",",",FALSE),
  _col4, TEXTSPLIT(SUBSTITUTE(REPT(INDEX(_text,4)&REPT(",",COUNTA(_meals)+1),_days)&";",",;",""),,",",FALSE),
  HSTACK( _col1, _col2,_col3,_col4 )
  )

# of Days set to:    6

# of Days set to:    3

Conditional Formatting: Rules

Conditional Formatting: Custom Formula for Headings Rows

Conditional Formatting: Custom Formula Non-Headings Rows

使用的 Excel 函数

让 文本分割 拉姆达 指数
报告 文本连接 康塔 堆栈
文本 代替 顺序

相关内容