Google表格:如何从范围创建数组，在每行中添加一列具有恒定文字值的列? [英] Google Sheets: How do I create an array from a range, adding a column with a constant literal value in every row?

问题描述

我想创建一个包含几列的数组.第二列和后续列指定为从另一张纸拉出的范围.第一列是一个静态常量，也就是说，第一列中的每个单元格都应具有完全相同的文字字符串值，例如'foo'.我找不到正确的语法.我以为这样的事情会起作用:

= {'"foo"，'Other Sheet'！C2:F}

但我得到函数ARRAY_ROW参数2的行大小不匹配.预期:1.实际:999.显然，"foo"表示"foo".需要扩展"到具有很多行的列.我该怎么办?在哪里记录了这种技巧?

也许这个问题的答案是一个开始:我如何创建一个包含单个列的数组，每个单元格包含"foo"，并且列数由不同的范围指定?

以下是可编辑的表格，其中说明了问题和所需的解决方案:

附带说明:在 {} 之间，如果您输入逗号( {expr1，expr2} )，则该值为并排放置，如果您放置了分号( {expr1; expr2} )，则这些值将在另一个之上.

I want to make an array with several columns. The second and subsequent columns are specified as a range pulled from another sheet. The first column is a static constant, that is, every cell in the first column should have the very same literal string value, say 'foo'. I can't find the correct syntax. I'd have thought something like this would work:

={"foo", 'Other Sheet'!C2:F}

but I get "Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1. Actual: 999." Clearly "foo" needs to be "expanded" to a column with lots of rows. How do I do it, and where are tricks like this documented?

Maybe the answer to this question would give a start: How do I create an array containing a single column, every cell containing "foo", with the number of columns specified by a different range?

Here is an editable sheet illustrating the problem and the desired solution: https://docs.google.com/spreadsheets/d/17myzKVFN3SDQuubWNdP-dFAbdvdlRbZFkjRpLi2Fas8/edit?usp=sharing The exact question is this: what formula can I put in cell B9 of Sheet1 to get the current appearance of Sheet1? Notice that I don't know in advance how many rows there are in 'Other Sheet'. It's OK to assume that all rows of Other Sheet have a nonblank value in column C.

解决方案

You can loop with an arrayformula and assign them to the first column, ending the array with the same size:

={ARRAYFORMULA(if(len('Other Sheet'!C2:C),"foo",)),'Other Sheet'!C2:F}

Side note: that between the {}, if you put a comma ({expr1 , expr2}), the value will be side by side, and if you put a semicolon ({expr1 ; expr2}), the values will be one above the other.

这篇关于Google表格:如何从范围创建数组，在每行中添加一列具有恒定文字值的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！