在Google表格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表格Arrayformula中将递增的行号与字符串连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！