谷歌表格_您应该知道的5种Google表格功能

谷歌表格

You’re probably familiar with the basics of Google Sheets, but Google’s spreadsheet offering boasts loads of features that aren’t obvious at first glance. Here are some of our favorites.

您可能已经熟悉Google表格的基础知识，但是Google的电子表格产品拥有许多功能，这些功能乍一看并不明显。 这里有一些我们的最爱。

Of course, you probably are already familiar with some basic formulas, like SUM and AVERAGE. And it’s likely you’ve gotten to know the toolbar fairly well, but it’s pretty amazing just how deep it all goes. I love spreadsheets, but even to this day I’m still discovering new tricks within Google Sheets.

当然，您可能已经熟悉一些基本公式，例如SUM和AVERAGE。 您可能已经相当了解工具栏，但是它的深度是如此令人惊讶。 我喜欢电子表格，但直到今天，我仍然在Google表格中发现新的技巧。

导入数据表 (Import Data Tables)

This sounds super boring, but it’s actually really neat. If a website has a table or a list of information that you want to keep track of, you can use the ImportHTML function to essentially scrape that data and paste it into a spreadsheet. From there, the data automatically updates whenever you open the spreadsheet (if changes to the original table were made, of course). The function would look like this:

这听起来很无聊，但实际上确实很整洁。 如果网站上有要跟踪的表或信息列表，则可以使用ImportHTML函数从本质上抓取该数据并将其粘贴到电子表格中。 从那里开始，只要打开电子表格，数据就会自动更新(当然，如果对原始表进行了更改)。 该函数将如下所示：

=ImportHTML(“URL”, "table", 0)

The URL is the web page where the data is located, “table” is how the data is shown on the webpage (you can also use “list” if it’s a list), and “0” represents which table it is you want to import if there are multiple tables on the web page (0 is the first one, 1 is the second one, and so on).

URL是数据所在的网页，“表”是数据在网页上的显示方式(如果是列表，您也可以使用“列表”)，“ 0”代表您要使用的表如果网页上有多个表(0是第一个表，1是第二个表，依此类推)，则导入。

An example of this would be keeping track of sports stats for a fantasy league. You can import various stats from a site like Baseball Reference into a spreadsheet. Of course, you could just bookmark the site, but with ImportHTML, you can customize things like exactly which stats appear (by adding Col1, Col4, etc. after the “0”), as well as fetch data from other tables on a different webpage and have it all show up in a single spreadsheet.

一个例子是跟踪幻想联盟的体育统计数据。 您可以从“ 棒球参考”等网站将各种统计信息导入电子表格。 当然，您可以将站点添加为书签，但是使用ImportHTML，您可以自定义内容，例如确切显示哪些统计信息(通过在“ 0”之后添加Col1，Col4等)，以及从其他表中的数据中获取数据。网页并将其全部显示在单个电子表格中。

其他电子表格的参考数据 (Reference Data From Other Spreadsheets)

If you have multiple spreadsheets (or multiple sheets within a spreadsheet) that all relate to each other in some way, you may find yourself going back and forth between them often. There’s a way to make all that a bit easier.

如果您有多个以某种方式相互关联的电子表格(或一个电子表格中的多个工作表)，您可能会发现自己经常在它们之间来回移动。 有一种方法可以使这一切变得容易。

You can reference cells from other sheets (or another spreadsheet entirely). For example, say that keep records of everything you spend on groceries in one sheet and that sheet also contains a total amount spent for the month. And, say you have another sheet that gives you a summary of what you spend each month on various categories. In your summary sheet, you could reference that grocery sheet and the specific cell that contains the total. Whenever you update the original sheet, the value in the summary sheet would update automatically.

您可以引用其他工作表(或整个其他电子表格)中的单元格。 例如，假设一张纸上记录了您在杂货上花费的所有费用，并且该纸还包含当月的总支出。 而且，假设您还有另一张工作表，它汇总了您每月在各种类别上的花费。 在摘要表中，您可以引用该杂货表和包含总数的特定单元格。 每当您更新原始工作表时，摘要工作表中的值都会自动更新。

The function would look like this:

该函数将如下所示：

=sheet1!B5

“Sheet1” would be the name of the sheet with the data you want to reference, and “B5” is the cell that you want to reference. The exclamation point goes in between. If you want to reference data from an entirely different spreadsheet, you would use the IMPORTRANGE function, like so:

“ Sheet1”将是包含您要引用的数据的工作表的名称，而“ B5”是您要引用的单元格。 感叹号介于两者之间。 如果要引用完全不同的电子表格中的数据，则可以使用IMPORTRANGE函数，如下所示：

=IMPORTRANGE("URL", "sheet1!B5")

The URL is the link to the other spreadsheet. This links the cell in that spreadsheet to the cell that you enter the above formula in. Whenever the cell gets updated with a different value, the other cell updates along with it. As the name of function suggests, you can also reference a range of cells, like B5:C10.

URL是指向其他电子表格的链接。 这会将电子表格中的单元格链接到您在其中输入上述公式的单元格。每当该单元格使用不同的值更新时，其他单元格就会随之更新。 顾名思义，您还可以引用一系列单元格，例如B5：C10。

条件格式 (Conditional Formatting)

This feature is a bit more well known than some of the others I’ve mentioned, but I feel like it’s still not as popular as it should be.

该功能比我提到的其他功能知名度更高，但我觉得它仍然没有应有的受欢迎。

Conditional Formatting lets you change a cell’s appearance based on the data that cell contains. You can access the feature by clicking “Format” in the toolbar and then selecting the “Conditional Formatting” command. In the pane that opens to the right, you can set up your parameters. For example, you might want to turn a cell (or cells) green if the number they contain is greater than zero.

通过条件格式设置，您可以基于单元格包含的数据来更改单元格的外观。 您可以通过单击工具栏中的“格式”，然后选择“条件格式”命令来访问该功能。 在右侧打开的窗格中，您可以设置参数。 例如，如果一个或多个单元格包含的数字大于零，则可能需要将其变为绿色。

There’s also the IF function, which isn’t technically a part of the Conditional Formatting feature, but it can take it to the next level in a way. This lets you do things like add a certain value to a separate cell whenever the value in the active cell is a particular number:

还有IF函数，从技术上讲，它不是条件格式设置功能的一部分，但可以通过某种方式将其提升到新的水平。 这使您可以执行以下操作，例如，只要活动单元格中的值是特定数字，便将特定值添加到单独的单元格中：

=IF(B4>=63,"35","0")

So in that example, if cell B4’s value is 63 or greater, you could automatically make the current cell’s value 35. And then show a 0 if not. Of course, that’s just an example, as there’s a lot more you can do with it.

因此，在该示例中，如果单元格B4的值等于或大于63，则可以自动将当前单元格的值设为35。如果不是，则显示0。 当然，这只是一个例子，您可以做很多事情。

将电子表格嵌入网站 (Embed Spreadsheets on a Website)

If you created a schedule or a list in Google Sheets that you want to share with others, you could just share the actual document with them by sending them an email invite to view it. However, if you need to supplement it with other info that’s on your blog or website, you can actually embed spreadsheets onto webpages.

如果您在Google表格中创建了要与其他人共享的日程表或列表，则可以通过向他们发送电子邮件邀请来与他们共享实际文档来查看它们。 但是，如果您需要用博客或网站上的其他信息来补充它，则实际上可以将电子表格嵌入到网页中。

All you need to do is navigate to File > Publish to the Web. From there, click on the “Embed” tab and then choose whether to publish the whole spreadsheet or just a specific sheet. After that, just copy and paste the iFrame code into your webpage.

您所需要做的只是导航至“文件”>“发布到Web”。 从那里，单击“嵌入”选项卡，然后选择是发布整个电子表格还是仅发布特定工作表。 之后，只需将iFrame代码复制并粘贴到您的网页中即可。

试玩脚本 (Play Around with Scripts)

For anything that Google Sheets can’t do out of the box, there’s usually a Google Apps Script that you can use alongside your spreadsheet to make pretty much anything happen.

对于Google表格无法立即使用的任何功能，通常都有一个Google Apps脚本，您可以将其与电子表格一起使用以使几乎所有事情发生。

We’ve talked about Google Apps Scripts before, and there’s a lot you can do with this kind of capability. You explore available add-ons by going to Tools > Add-Ons, or you can write your own scripts by selecting the Script Editor in the Tools menu.

之前 ，我们已经讨论过Google Apps脚本 ，并且您可以使用这种功能做很多事情。 您可以通过转到工具>附加组件来浏览可用的附加组件，或者可以通过在工具菜单中选择脚本编辑器来编写自己的脚本。

For example, I have a custom script that allows me to press a single button to instantly add specific values to the existing values in a handful of cells. You can’t do this with Google Sheets out of the box, so having the script editor here gives Google Sheets a good dose of steroids.

例如，我有一个自定义脚本，允许我按一个按钮立即将特定值添加到少数单元格中的现有值。 您无法立即使用Google表格进行此操作，因此在此处使用脚本编辑器可以为Google表格提供大量的类固醇。

翻译自: https://www.howtogeek.com/350261/5-google-sheets-features-you-should-know/

谷歌表格