将递增的行号与 Google Sheets Arrayformula 中的字符串连接起来 [英] Concatenate incrementing row number with string in Google Sheets Arrayformula

问题描述

我试图让一列根据 Google 表格中 ARRAYFORMULA 中的行号自动填充递增的字符串.通过将 =ARRAYFORMULA(ROW(A:A)-1) 放在顶部单元格中，我可以轻松地获得一列以显示移动的行号.然而，尝试在 CONCATENATE 内使用这个 ROW 调用并没有给我想要的结果，我已经尝试过使用和不使用 TO_TEXT.

在图像中，用于不同列的公式是:

A1: =ARRAYFORMULA(ROW(A:A)-1)

B1: =ARRAYFORMULA(IF(ROW(B1:B)>1,CONCATENATE("Hello World: ",(ROW()-1)),"你好世界！"))

C1: =ARRAYFORMULA(IF(ROW(C1:C)>1,CONCATENATE("Hello World:",TO_TEXT(ROW(C:C)-1)),世界你好！"))

D1:D16: 手动输入所需的字符串.

电子表格屏幕截图

如您所见，B1 不会增加数字，只会使用给出公式的单元格中的 ROW 值.C1 将所有行数连接到每一行中.

关于如何在不填写或不使用脚本的情况下实现预期结果的任何想法?

解决方案

有一个不成文的规定，除非你真的需要它，否则永远不要使用 CONCATENATE，这也永远不会.加入你所需要的只是&

=ARRAYFORMULA({"Hello there world!"; "Hello World: "&ROW(A2:A)-1})

I'm trying to get a column to automatically fill up with incrementing strings depending on the row number in an ARRAYFORMULA in Google sheets. I can easily get a column to show a shifted row number by putting =ARRAYFORMULA(ROW(A:A)-1) in the top cell. However trying to use this ROW call inside CONCATENATE does not give me the result I want, I've tried with and without TO_TEXT.

In the image the formula used for the different columns are:

A1: =ARRAYFORMULA(ROW(A:A)-1)

B1: =ARRAYFORMULA(IF(ROW(B1:B)>1,CONCATENATE("Hello World: ",(ROW()-1)),"Hello there world!"))

C1: =ARRAYFORMULA(IF(ROW(C1:C)>1,CONCATENATE("Hello World: ",TO_TEXT(ROW(C:C)-1)),"Hello there world!"))

D1:D16: Manually entered the desired strings.

Spreadsheet screen shot

As you can see, B1 doesn't increment the number, only uses the ROW value from the cell where the formula is given. And C1 concatenates all the numbers of the rows into every line.

Any ideas on how I can accomplish the intended result without filling or using scripts?

解决方案

there is an unwritten rule that says to never use CONCATENATE unless you really need it which is also always never. to join stuff all you need is &

=ARRAYFORMULA({"Hello there world!"; "Hello World: "&ROW(A2:A)-1})

这篇关于将递增的行号与 Google Sheets Arrayformula 中的字符串连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！