Google 表格(Google Sheets)中的CONCATENATE函数将多个数据块连接在一起。此功能在管理需要类似处理的大量信息时很有帮助。
例如,
如果电子表格有一列用于名字,另一列用于姓氏,则您可以使用CONCATENATE函数,但您希望将它们连接在一起以形成具有两个名称的单个单元格。(CONCATENATE)您可以通过键入每个名称手动执行此操作,也可以使用CONCATENATE
自动执行此操作。
可以给出许多其他CONCATENATE函数的示例,因此我们将在下面看一些。
一个简单的例子
CONCATENATE函数最简单的形式是将两组数据拼凑在一起,没有任何其他选项。使用这种简单的形式可以做到这一点:
=CONCATENATE(A1,B1)
当然,在这个例子中,我们假设第一个名字在单元格 A1 中,第二个在单元格 B1 中。您可以通过将这些引用替换为您自己的引用来适应您自己的电子表格。
在这个特定示例中按Enter将生成MaryTruman。如您所见,名字与姓氏直接对接。CONCATENATE函数在这种情况下完成了它的工作,但是您可以在其中包含其他选项来扩展其功能,例如添加空间或来自其他单元格的数据。
在CONCATENATE 公式(CONCATENATE Formula)中使用空格
知道如何在CONCATENATE中使用空格很重要,因为数据集通常不会完全按照您希望的方式设置。就像我们上面的例子一样,我们希望通过在两个单元格之间添加一个空格来使名称看起来像样。
空格
使用双引号包含在此Google 表格函数中:(Google Sheets)
=CONCATENATE(A1,” ”,B1)
如果您在此处看不到,则这些引号中有一个空格。使用引号背后的想法是您手动输入数据而不是选择电子表格数据。
换句话说,A1 和 B1 显然已经是电子表格的一部分,因此您通过按原样输入它们(单元格字母加上单元格编号)来引用它们。但是,要将您自己的数据包含在公式中,您需要将其括在引号中。
将文本(Text)添加到CONCATENATE 公式(CONCATENATE Formula)
CONCATENATE函数可以做的不仅仅是连接几个单元格并在它们之间放置一个空格。下面(Below)是一个如何使用CONCATENATE使用单元格数据形成整个句子的示例。
在这个CONCATENATE函数的例子中,我们将县和它的排名数字串在一起,但我们没有把它留在那里,而是使用空格和我们自己手动输入的数据来创建一个完整的句子:
=CONCATENATE(A2, " is", " ranked ", C2, " compared to these other ceremonial counties.")
为了使公式像普通英语(English)一样工作,不要忘记在必要的地方放置空格。您不能在单元格引用之后添加空格(如上面的C2),但在使用双引号时可以。正如您在上面看到的,我们在引号中多次使用空格来使句子正常阅读。
在其他地方应用CONCATENATE 公式(CONCATENATE Formula Elsewhere)
最后, CONCATENATE(CONCATENATE)函数的唯一真正用途
是处理足够的数据以节省时间,而不是手动输入数据。因此,要使公式与其他单元格一起使用,您需要做的就是将其向下拖动。
单击(Click)单元格一次,使其突出显示。您应该会在单元格的右下角看到一个小框,如下所示:
单击(Click)并按住该框,同时将其向下拖动以将其应用于数据集。到达要应用公式的最后一项后停止拖动。(Stop)如果以后需要包含更多单元格,您可以随时从那里再次拖动它。
有趣的是,
Google 表格(Google Sheets)有一个类似的功能,叫做SPLIT。但是,它不是连接单元格,而是根据您选择标记为拆分点的字符将一个单元格拆分为多个单元格。
How to Use the CONCATENATE Function in Google Sheets
The CONCATENATE function іn Google Shеets joinѕ
together multiple chunks of data. This funсtion is helpful when managing large
sets of infоrmation that each neеd a similar treаtment.
For example, you might use the CONCATENATE
function if the spreadsheet has one column for a first name and another for a
last name, but you want them joined together to form a single cell with both
names. You could this manually by typing each name, or you can use CONCATENATE
to automate it.
Many other examples of the CONCATENATE function could
be given, so we’ll look at a few below.
A Simple Example
At its simplest form, the CONCATENATE function
pieces together two sets of data without any other options. That’s possible
with this simple form:
=CONCATENATE(A1,B1)
Of course, in this example, we’re assuming the
first name is in cell A1 and the second in cell B1. You can adapt this to your
own spreadsheet by replacing those references with your own.
Pressing Enter with this particular
example would produce MaryTruman. As you can see, the first name is
butted right up against the last name. The CONCATENATE function did its job in
this scenario, but there are other options you can include in it to expand its
capabilities, like to add a space or data from other cells.
Using a Space in the CONCATENATE Formula
Knowing how to use spaces with CONCATENATE is
important because datasets often aren’t set up exactly how you want them to be.
Like in our example above, we want the name to look presentable by adding a
space between the two cells.
Spaces are included in this Google Sheets
function using double quotes:
=CONCATENATE(A1,” ”,B1)
If you can’t see here, there’s a space within
those quotes. The idea behind using the quotes is that you’re entering data
manually and not choosing spreadsheet data.
In other words, A1 and B1 are
clearly part of the spreadsheet already, so you’re referencing them by entering
them as they are (the cell letter plus the cell number). However, to include
your own data within the formula, you need to surround it in quotes.
Adding Text to a CONCATENATE Formula
The CONCATENATE function can do more than just
join a couple cells and put a space between them. Below is an example of how to
use CONCATENATE to form an entire sentence using cell data.
In this example of the CONCATENATE function, we’re stringing together the county and its rank number, but instead of leaving it at that, we’re using spaces and our own manually-entered data to create a full sentence:
=CONCATENATE(A2, " is", " ranked ", C2, " compared to these other ceremonial counties.")
To make the formula work like regular English,
don’t forget to put spaces where necessary. You can’t add a space right after a
cell reference (like C2 above), but you can when using double quotes. As
you can see above, we used a space multiple times in our quotes to make the
sentence read normally.
Applying the CONCATENATE Formula Elsewhere
Lastly, the only real use of the CONCATENATE
function is when dealing with enough data that time is being saved versus
entering the data manually. So, all you need to do to make the formula work
with the other cells is drag it downward.
Click the cell once so that it’s highlighted.
You should see a small box on the bottom right-hand corner of the cell, like
this:
Click and hold that box while
dragging it downward to apply it to the dataset. Stop dragging once you’ve
reached the last item you want the formula to be applied to. You can always
drag it again from there should you need to include more cells later.
Interestingly,
Google Sheets has a similar function called SPLIT. However, instead of joining
the cells, it splits one cell into multiple cells depending on which character
you choose to mark as the split-off point.