在所有G Suite 网络应用程序(G Suite web applications)中,Google 表格(Google Sheets)可能是最令人印象深刻的。它于 2006 年发布,迅速成为与Microsoft Excel(Microsoft Excel)抗衡的有力竞争者,它是一个崭露头角的电子表格编辑器。
如今,Google 表格(Google Sheets)包含大量的编辑和协作功能,数以百万计的学生、工作人员和业余爱好者每天都在使用这些功能。
Google 表格最受赞赏的功能之一是其强大的功能集。功能是使电子表格如此强大的核心组件,每个电子表格编辑平台通常都有几个自己独特的功能。
Google Sheet 的IMPORTRANGE功能允许在两个或多个电子表格之间实现一定程度的无缝连接,从而实现许多有趣的可能性。它允许您将数据导入Google 表格(Google Sheets)。
什么是(What Is )进口(IMPORTRANGE)?
IMPORTRANGE 是Google 表格支持(functions supported by Google Sheets)的众多功能之一。函数用于创建可以操作电子表格数据、进行计算等的公式。
100 多个受支持的函数中的一些包括DATE,将字符串转换为日期,COS,返回辐射中提供的角度的余弦,以及ROUND,允许将小数四舍五入到某个小数位。
包含在这个长长的函数列表中的是IMPORTRANGE。IMPORTRANGE启用了一种跨电子表格集成形式,允许导入来自另一个电子表格(或同一工作表中的工作表)的一系列单元格。
这允许Google 表格(Google Sheets)用户将他们的数据拆分为多个不同的表格,同时仍然可以使用简单的公式查看它。独特的是,它还允许一定程度的协作,您可以将数据从第三方工作表(如果允许)导入到您自己的工作表中。
如何使用(How To Use )IMPORTRANGE
使用这个强大功能的第一步是拥有一个电子表格,您可以将数据从该电子表格导入到Google 表格(Google Sheets)中。要么找到一个,要么像我在本例中所做的那样,创建一个包含几行数据的虚拟表。
在这里,我们有一个简单的两列三行工作表。我们的目标是获取这些数据并将其导入到我们正在使用的另一个电子表格中。创建一个新工作表或进入现有工作表并进行设置。
您将从与使用任何函数时相同的过程开始 - 单击空白单元格,以便您可以访问函数栏。在其中输入=IMPORTRANGE。这是我们可以用来导入工作表数据的函数关键字。
IMPORTRANGE函数在其基本语法中使用两个参数:IMPORTRANGE ((IMPORTRANGE) spreadsheet_url, range_string)。让我们回顾一下。
spreadsheet_url 正是它听起来的样子——您尝试从中导入一系列数据的电子表格的URL 。您只需在此处复制并粘贴电子表格的URL。更简单的是,您可以选择只使用电子表格的标识符字符串,也可以在URL中找到。
此标识符是工作表URL(URL)中“spreadsheets/d/”和“/edit”之间的一长串文本。在这个例子中,它是“1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”。
range_string 参数同样简单。您可以返回特定范围,而不是从另一个工作表打印所有电子表格数据。要导入我们示例的整个工作表中显示的数据,范围将是A1:B4。
如果我们可以从这些列中导入所有未来数据,这可以简化为A:B 。如果我们想导入没有标题的数据,那就是A2:B4。
让我们把完整的公式放在一起: =IMPORTRANGE(“1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”, “A:B”)
您会注意到,假设您已正确替换了电子表格 URL,则尝试使用此公式最初会显示参考错误。然后,您需要单击单元格以连接这两个工作表。
如果一切都已正确完成,您现在应该会看到导入当前工作表的数据。
很简单,对吧?值得注意的是,在此导入过程中不会保留格式,如您在上面看到的,但所有纯文本数据都会保留。
为什么使用(Why Use )IMPORTRANGE ?
既然您已经看到使用IMPORTRANGE(IMPORTRANGE)是多么容易,那么您为什么还要使用它呢?让我们看一些简单的示例用例。
更好的组织(Better Organization)
您可能会发现自己卷入了一个非常复杂的工作表,其中包含移动变量,您希望将这些变量与数据的其他部分完全分开。IMPORTRANGE非常适合这一点,因为它允许您这样做。
由于IMPORTRANGE可以轻松地让您从同一个电子表格中的另一个工作表导入数据,因此您可以创建一个“变量”工作表,您可以在其中存储任何带有移动部件的内容。然后可以使用IMPORTRANGE、QUERY和CONCATENATE的组合将所有内容组合在一起。
与朋友合作(Collaborating With a Friend)
两个头比一个好,IMPORTRANGE甚至可以让您连接到您的帐户不拥有的工作表,只要它与您共享。如果您正在处理一个协作项目,则可以使用IMPORTRANGE(IMPORTRANGE)将两个或更多人的工作动态合并到一个工作表中。
隐藏敏感数据(Hiding Sensitive Data)
如果您有一个包含要公开显示的行或列的私人电子表格,那么IMPORTRANGE非常适合。
一个例子是,如果您要使用Google Forms创建一个表单。在其中,您可能会要求提供一些受访者的个人信息-您显然不想透露,对吗?但是,该表单也可能会询问您希望在公共共享链接中显示的较少个人问题。通过相应地设置 range_string 并连接表单的响应表,可以实现这一点。
IMPORTRANGE是一款功能强大的Google 表格(Google Sheets)工具,可在您需要将数据导入Google 表格(Google Sheets)的各种不同情况下很好地发挥作用。这是我选择的 Google Sheets 和 Microsoft Excel 之间最重要的区别(differences between Google Sheets and Microsoft Excel)之一。
您对如何使用此功能有任何疑问,或者想与我们分享一个喜欢的功能吗?在下面发表评论,我们会检查出来!
How To Import Data Into Google Sheets From Another Sheet
Of all of the G Suite web applications, Google Sheets may be the most impressive. Released in 2006, it quickly became a fierce competitor to stand up against Microsoft Excel as an up-and-coming spreadsheet editor.
Today, Google Sheets includes a wealth of editing and collaboration features that millions of students, workers, and hobbyists use on a daily basis.
One of Google Sheets’ most appreciated features is its robust set of functions. Functions are a core component to what makes spreadsheets so powerful, and each spreadsheet editing platform usually has several of its own unique functions.
Google Sheet’s IMPORTRANGE function allows a level of seamless connectivity between two or more spreadsheets, enabling many interesting possibilities. It allows you to import data into Google Sheets.
What Is IMPORTRANGE?
IMPORTRANGE is one of the many functions supported by Google Sheets. Functions are used to create formulas that can manipulate your spreadsheet data, make calculations, and more.
Some of the more than 100 supported functions include DATE, to convert a string into a date, COS, to return the cosine of an angle provided in radiances, and ROUND, allowing decimals to be rounded to a certain decimal place.
Included in this long list of functions is IMPORTRANGE. IMPORTRANGE enables a form of cross-spreadsheet integration, allowing a range of cells from another spreadsheet (or worksheet within the same sheet) to be imported.
This allows Google Sheets users to split up their data into multiple different sheets while still being able to view it using a simple formula. Uniquely, it also allows a level of collaboration where you can import data from a third-party sheet (if permitted) into your own.
How To Use IMPORTRANGE
The first step to making use of this powerful function is to have a spreadsheet that you want to import data from into Google Sheets. Either locate one or, as I’ll do in this example, create a dummy sheet with a few rows of data.
Here, we have a simple sheet of two columns and three rows. Our goal is to take this data and import it into another spreadsheet that we’re using. Create a new sheet or go into an existing sheet and let’s set it up.
You’ll begin with the same process as you would when using any function—click a blank cell so that you can access the function bar. In it, type =IMPORTRANGE. This is the function keyword we can use for importing sheet data.
The IMPORTRANGE function uses two parameters in its basic syntax: IMPORTRANGE(spreadsheet_url, range_string). Let’s go over both.
spreadsheet_url is exactly what it sounds like—the URL of the spreadsheet that you’re attempting to import a range of data from. You simply copy and paste the spreadsheet’s URL and here. Even easier, you can optionally just use the spreadsheet’s identifier string, also found in the URL.
This identifier is the long string of text found between “spreadsheets/d/” and “/edit” in the sheet’s URL. In this example, it’s “1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”.
The range_string parameter is just as simple. Instead of printing all of the spreadsheet data from another sheet, you can return a specific range. To import the data shown in the entire sheet of our example, the range would be A1:B4.
This can be simplified to just A:B if we’re fine with importing all future data from these columns. If we wanted to import the data without headers, that’d be A2:B4.
Let’s put together our full formula: =IMPORTRANGE(“1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”, “A:B”)
You’ll notice that attempting to use this formula, assuming you’ve properly replaced spreadsheet_url, will initially show a reference error. You’ll need to then click on the cell to connect these two sheets.
If everything has been done correctly, you should now see the data imported into your current sheet.
Simple, right? It’s worth noting that formatting will not be preserved during this import, as you can see above, but all of the plaintext data will be.
Why Use IMPORTRANGE?
Now that you see how easy it is to use IMPORTRANGE, why would you ever use it? Let’s go over a few quick example use cases.
Better Organization
You may find yourself involved in a very complex sheet that has moving variables that you want to separate completely from other parts of your data. IMPORTRANGE is perfect for this, as it allows you to do just that.
Since IMPORTRANGE easily allows you to import data from another worksheet within the same spreadsheet, you can create a “Variables” worksheet where you can store anything with moving parts. A combination of IMPORTRANGE, QUERY, and CONCATENATE could then be used to bring everything together.
Collaborating With a Friend
Two heads are better than one, and IMPORTRANGE will even allow you to connect to sheets that your account doesn’t own, as long as it’s shared with you. If you’re working on a collaborative project, the work of two or more people can be dynamically consolidated into a single sheet using IMPORTRANGE.
Hiding Sensitive Data
If you have a private spreadsheet with rows or columns that you want to show publicly, IMPORTRANGE is great for that.
One example would be if you were to create a form with Google Forms. In it, you may ask for some of the respondents’ personal information—you obviously don’t want to give that out, right? However, the form may also ask less personal questions that you want to display in a public shared link. By setting the range_string accordingly and connecting the form’s response sheet, this can be achieved.
IMPORTRANGE is a powerful Google Sheets tool that serves well in a variety of different situations where you need to import data into Google Sheets. It’s one of my picks for the most important differences between Google Sheets and Microsoft Excel.
Do you have any questions on how you can use this function, or want to share one like it with us? Drop a comment below and we’ll check it out!