如何在Google SpreadSheet中多次重复一次范围 [英] How to make a range repeat n-times in Google SpreadSheet

问题描述

我使用 ArrayFormula（）来简化我创建报告的方式。

不必在每个单元格中创建引用（例如 = C1 ， = C2 ， = C3 ， = C4 ，我只是使用 = arrayformula（C1：C4）在一个单独的单元格中，它完成相同的工作，但更简单，它使事情更有条理，因为我只需要查看一个单元格可能的错误。

当我需要将一个范围引用到另一个范围，比如将C1：C4的值放入A1：A4范围时，只会写 = arrayformula（C1：C4），它会发挥它的魔力。

它确实有点棘手例如，如果我想将两个或更多范围链接C1：C4堆叠在B1：B3上，在单元格A1上，我可以写入 = arrayformula（{C1：C4; B1：B3}）。

我的问题是使用 arrayFormula / code>来复制重复模式，例如，如果我想复制单元格C的内容1 4次使用 = arrayformula（{C1; C1; C1; C1}）。

工作，并会达到预期的效果。但是，我想知道是否有更好的方法来做到这一点。像 = arrayformula（{C1} * 12）这样的模式会重复12次。这也可以让我有一个动态公式，例如 = arrayformula（{C1} * count（D：D）），其中模式会根据某个变量重复。

你对如何使用原生公式（无javascript）达到目标有任何想法吗？

解决方案

我将使用split（）函数而不是arrayformula（）和rept（）函数来重复单元格位置。例如，如果您的n = 4，公式将如下所示：

  = split（rept（C1&;， 4），;）
  

rept（）重复单元格位置C1 +分号四次创建一个字符串和split（）函数将创建的字符串用分号分隔为水平单元格。

您可以使用transpose（）函数将结果水平表旋转到垂直表：

  = transpose（split（rept（C1&;，4，;））
  

是的，您可以使用它在arrayformula（）函数的帮助下创建动态公式：
$ b

  = arrayformula（ count（D：D）* split（rept（C1&;，4），;））
  

I use ArrayFormula() to simplify the way I create my reports.

Instead of having to create a reference in each cell (eg. =C1,=C2,=C3,=C4 in each cell, I just use =arrayformula(C1:C4) in one single cell. It does exactly same job, but is much simpler and it keeps things more organized, because I just need to look in one cell for possible errors.

It works great when I have to reference a range into another like take the values of C1:C4 into the A1:A4 range. In the A1 cell I would just write =arrayformula(C1:C4) and it does its magic.

It does get a bit trickier when the ranges are not the same length, but it is feasible nonetheless. For instance, if I want to stack two or more range link C1:C4 on top of B1:B3, on cell A1 I can write =arrayformula({C1:C4;B1:B3}).

My problem is using arrayFormula() to copy a repeating pattern. For instance, if I want to copy the content of cell C1 4 times I would use =arrayformula({C1;C1;C1;C1}).

This would work and would achieve the desired effect. However, I was wondering if there is a better way to do that. Something like =arrayformula({C1}*12) were this pattern would repeat 12 times. This would also enable me to have a dynamic formula, such as =arrayformula({C1}*count(D:D)) where the pattern would repeat according to some variable.

Do you have any ideia on how to achieve that using only native formula (no javascript)?

解决方案

I would use split() function instead of arrayformula() and rept() function to repeat cell positions. For example, if your n=4 the formula will look like this:

=split(rept(C1&";",4),";")

rept() repeats cell position C1+semicolon four times creating a string and split() function divides created string by semicolons to horizontal cells.

You can rotate resulted horizontal table to vertical table using transpose() function:

=transpose(split(rept(C1&";",4),";"))

And yes, you can use it to create dynamic formulas with help of arrayformula() function:

=arrayformula(count(D:D)*split(rept(C1&";",4), ";"))

这篇关于如何在Google SpreadSheet中多次重复一次范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！