英文:
Given a table and some fixed values in Excel, how to create a repeated pattern? (such as in picture)
问题
我有一个包含一些项目名称和它们在不同月份的值的表格。我想创建一个重复的模式，其中名称保持不变，但值根据月份变化。基本上，有12个月的数据，名称表格和月份表格的行数会发生变化。
我已经使用了不同的公式来达到结果：使用VSTACK将数据堆叠在第二个范围内，使用REPEAT重复给定的代码，使用增量函数在每n行中递增月份编号，使用LET函数重复n次名称。但我认为有一种更有效的方法来做到这一点，即将所有内容合并为一个公式，从而最终给出结果。有什么方法吗？
英文:
I have table with some item names and their values across months. I want to create a repeated pattern where names remain but values change according to month. Basically, there is a data for 12 months and the number of rows in the Names table and Months table changes.
I have used different formulas for every column to reach the result: VSTACK to stack data in the 2nd range, REPEAT to repeat the given Code, increment function to increment month no. every n row and LET function to repeat n times the Names. But I think there is a more effective way to do it, i.e. by combining all of it into one formula so that it eventually gives the result. Are there any ways?
答案1
得分: 2
=LET(a,B5:D8,
b,F5:H8,
HSTACK(
REDUCE(a,SEQUENCE(,COLUMNS(b)-1),LAMBDA(r,x,VSTACK(r,a))),
INT(SEQUENCE(ROWS(a)*COLUMNS(b),,1,1/ROWS(a))),
TOCOL(b,,1)))
Reduce 部分将 a 堆叠到 b 中列的数量上（起始值是范围，所以堆叠列数减1）。
这个结果与 b 的 tocol 堆叠在一起。
编辑：我忽略了第一列：
=LET(a,B5:D9,
b,F5:H9,
c,B2,
IFERROR(
HSTACK(c,
REDUCE(a,SEQUENCE(,COLUMNS(b)-1),LAMBDA(r,x,VSTACK(r,a))),
INT(SEQUENCE(ROWS(a)*COLUMNS(b),,1,1/ROWS(a))),
TOCOL(b,,1)),
c))
<details>
<summary>英文:</summary>
=LET(a,B5:D8,
b,F5:H8,
HSTACK(
REDUCE(a,SEQUENCE(,COLUMNS(b)-1),LAMBDA(r,x,VSTACK(r,a))),
INT(SEQUENCE(ROWS(a)*COLUMNS(b),,1,1/ROWS(a))),
TOCOL(b,,1)))
The `Reduce` part stacks `a` the number of columns present in `b` (start value is the range, so stack columns -1).
This gets stacked with a `tocol` of `b`.
Edit: I missed out on the first column:
=LET(a,B5:D9,
b,F5:H9,
c,B2,
IFERROR(
HSTACK(c,
REDUCE(a,SEQUENCE(,COLUMNS(b)-1),LAMBDA(r,x,VSTACK(r,a))),
INT(SEQUENCE(ROWS(a)*COLUMNS(b),,1,1/ROWS(a))),
TOCOL(b,,1)),
c))
[![enter image description here][1]][1]
[1]: https://i.stack.imgur.com/J9WjM.jpg
</details>
# 答案2
**得分**: 1
生成范围重复
```plaintext
=LET(cValue,B2,nData,B5:D8,mData,F5:H8,
ncc,COLUMNS(nData),mcc,COLUMNS(mData),
rc,ROWS(nData),tr,rc*mcc,
c,INDEX(cValue,SEQUENCE(tr,,1,0)),
s,SEQUENCE(tr)-1,rs,MOD(s,rc)+1,
ncs,SEQUENCE(,ncc),
n,INDEX(nData,rs,ncs),
mcs,INT(s/rc)+1,
m,INDEX(mData,rs,mcs),
dr,HSTACK(c,n,mcs,m),
dr)
-
如果将数据添加到Excel表格中，可以使用结构化引用：
... nData,Table1,mData,Table2,...并且可以添加更多的行和列，公式仍然有效。
英文:
Generate Range Repeats
=LET(cValue,B2,nData,B5:D8,mData,F5:H8,
ncc,COLUMNS(nData),mcc,COLUMNS(mData),
rc,ROWS(nData),tr,rc*mcc,
c,INDEX(cValue,SEQUENCE(tr,,1,0)),
s,SEQUENCE(tr)-1,rs,MOD(s,rc)+1,
ncs,SEQUENCE(,ncc),
n,INDEX(nData,rs,ncs),
mcs,INT(s/rc)+1,
m,INDEX(mData,rs,mcs),
dr,HSTACK(c,n,mcs,m),
dr)
-
If you'd add the data to Excel tables, you could use structured references:
... nData,Table1,mData,Table2,...and add more rows and columns and the formula would still work.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论