在Google Sheets中,如何根据范围中每个项目的指定值重复一个项目列表?

huangapple go评论64阅读模式
英文:

How to repeat a list of items in a range based on a specified value for each item in the range ing google sheets?

问题

=ARRAYFORMULA(FLATTEN(SPLIT(JOIN(",", REPT(A2:A4&",", B2:B4)), ",")))

英文:

I would like to repeat a list in google sheets where each item is repeated a different number of times based on a value in a different cell.

What formula can I use to achieve this?

List:
Apple
Banana
Orange

Desired Output:
Apple
Apple
Banana
Banana
Banana
Banana
Orange
Orange
Orange

Items # of rept
Apple 2
Banana 4
Orange 3

=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(ROWS(D2:D42),10),D2:D42)))

This is all I can think of but the 10 needs to be a custom value depending on the value associated with the unique cell in D2:D42.

答案1

得分: 1

=ARRAYFORMULA(TOCOL(SPLIT(JOIN(,REPT(A2:A&"ζ",B2:B)),"ζ")))
=ARRAYFORMULA(TOCOL(MAP(A2:A,B2:B,LAMBDA(a,b,IF(SEQUENCE(1,b),a))),3))
=TOCOL(MAP(A2:A,B2:B,LAMBDA(a,b,CHOOSECOLS(a,SEQUENCE(1,b,1,0)))),3)
英文:

Here are some options:

=ARRAYFORMULA(TOCOL(SPLIT(JOIN(,REPT(A2:A&"ζ",B2:B)),"ζ")))
=ARRAYFORMULA(TOCOL(MAP(A2:A,B2:B,LAMBDA(a,b,IF(SEQUENCE(1,b),a))),3))
=TOCOL(MAP(A2:A,B2:B,LAMBDA(a,b,CHOOSECOLS(a,SEQUENCE(1,b,1,0)))),3)

huangapple
  • 本文由 发表于 2023年5月21日 04:49:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76297305.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定