Google 表格和Microsoft Excel都是功能强大的电子表格软件。虽然在Excel 中(Excel)您可以离线工作,但Google 表格(Google Sheets)可让您在线创建和管理电子表格。Google 表格(Google Sheets)还会自动保存每一个更改。使用Google 表格(Google Sheets)的好处是,您只需登录您的Google帐户,即可在任何设备上的任何位置管理电子表格。
您可以下载xlsx和其他格式的(xlsx)Google 表格(Google Sheets)并在您的 PC 上创建备份(backup on your PC)。但是每次您对Google 表格(Google Sheets)进行更改时,您都必须下载它并替换以前的备份文件。现在,想象一下,如果您在组织中担任更高职位,您必须管理大量数据。在这种情况下,手动保持所有备份文件与Google 表格同步会变得很忙。(Google Sheets)是否有任何方法可以自动更新所有下载的文件?就在这里。在本文中,我们将看到将Google Sheets与MS Excel连接或同步的方法(MS Excel)这样在Google 表格(Google Sheets)中所做的每一项更改都会自动反映在相应的MS Excel文件中。
阅读(Read):如何在 Google 文档、表格和幻灯片中使用深色主题(How to use Dark theme in Google Docs, Sheets, and Slides)。
如何将Google 表格(Google Sheets)与Excel连接起来(Excel)
1]在Google 表格中(Google Sheets)打开(Open)要与MS Excel同步的文件。我准备了印度(India)不同州温度的样本数据。
2]现在转到Google表格中的“File > Publish to the web”。
3]在“整个文档(Entire Document)”下拉菜单中,您可以选择发布整个文档或仅发布文档的特定工作表。
单击“已发布的内容和设置(Published content & settings)”部分将其展开,并查看“进行更改时自动重新发布(Automatically republish when the changes are made)”部分是否启用。如果没有,请启用它。现在单击“发布(Publish)”按钮并在弹出窗口中选择“确定”。
阅读(Read):如何在安全模式下启动 Word、Excel、PowerPoint(How to start Word, Excel, PowerPoint in Safe Mode)。
4]发布文档后,您将获得一个链接。您必须将此链接粘贴到要与Google 表格连接的(Google Sheets)MS Excel文档中。如果您将此链接粘贴到您的网络浏览器中,您将看到它可以作为网页使用。
现在,启动MS Excel并在其中创建一个新的空白文档。转到“Data > New Query > From Other Sources > From Web ”。
5]您将看到一个弹出窗口,您必须在其中粘贴复制的链接,然后单击确定。如果您在此处获得两个选项Basic和Advanced,请选择Basic。
6] 整个Google 表格(Google Sheet)数据将以表格的形式在Excel中提供。现在,在“导航器(Navigator)”窗口中,首先单击“选择多个项目(Select multiple items)”复选框,然后选择“表 0(Table 0) ”。右侧面板上提供了所选项目的预览。
您可以在下面的屏幕截图中看到第 1 列是空的,第 2 列列出了序列号,而我们的实际数据列在第 3 列和第 4 列中。因为我们不需要第 1 列和第 2 列,所以我们必须删除它们。为此,单击导航器(Navigator)窗口底部的“编辑”按钮。(Edit)根据您的MS Excel版本,有些人可能会使用“转换数据(Transform Data)”代替“编辑”选项。(Edit)它将打开“查询编辑器(Query Editor)”。
阅读(Read):节省时间和加快工作速度的 Excel 提示。
7] 我们想让我们的第一行成为标题。为此,单击左上角并选择“使用第一行作为标题(Use first row as headers)”。
8]要删除不需要的列,请单击左上角并选择“选择列(Choose columns)”。之后,取消选中不需要的列的框,然后单击确定。
9]现在,单击“关闭并加载(Close & Load)”以在Excel中加载(Excel)Google Sheet的数据。
您已将Google 表格(Google Sheet)与Excel连接起来。每当您对Google Sheet(Google Sheet)进行任何更改时,您都必须等待 5 分钟才能将这些更改发布到网络上。之后,您必须刷新 Excel 文件才能使这些更改反映在Excel文件中。为此,请转到“Data > Refresh All。
阅读(Read):箭头键在 Excel 中不起作用。
如果您希望 Excel 在您对Google Sheet(Google Sheet)进行更改时自行更新,则必须执行更多步骤。
1]选择表格并转到“Data > Connections”。这将打开一个窗口,您必须在其中选择“属性(Properties)”。
2]现在,单击“刷新间隔(Refresh every)”复选框并输入您希望Excel自动更新的时间。我已经进入了 5 分钟。如果您启用“打开文件时刷新数据(Refresh data when opening the file)”选项,Excel将在您每次打开时检查更新。完成后,单击确定并关闭上一个窗口。
就是这样。这就是您可以将Google 表格(Google Sheets)与MS Excel连接起来的方法。
阅读下一篇(Read next):高级 Excel 提示和技巧。
How to connect Google Sheets with Excel
Both Google Sheets and Microsoft Excel are powerful spreadsheet software. While in Excel you can work offline, Google Sheets lets you create and manage spreadsheets online. Google Sheets also saves every single change automatically. The benefit of using Google Sheets is that you can manage spreadsheets from anywhere on any device simply by signing into your Google account.
You can download Google Sheets in xlsx and other formats and create a backup on your PC. But every time you make a change to the Google Sheets, you have to download it and replace the previous backup file. Now, imagine if you at a higher post in an organization, you have to manage a significant amount of data. In such a condition, it will become hectic for you to keep all the backup files in synchronization with Google Sheets manually. Is there any method by which all the downloaded files will be updated automatically? Yes, there is. In this article, we will see the method of connecting or synchronizing Google Sheets with MS Excel so that every change made in Google Sheets will be reflected automatically in the respective MS Excel file.
Read: How to use Dark theme in Google Docs, Sheets, and Slides.
How to connect Google Sheets with Excel
1] Open the file in Google Sheets which you want to synchronize with MS Excel. I have prepared sample data of temperatures of different states of India.
2] Now go to “File > Publish to the web” in Google Sheets.
3] In the “Entire Document” drop-down menu, you have an option to publish either the entire document or only the particular sheet of the document.
Click “Published content & settings” part to expand it and see whether the “Automatically republish when the changes are made” part is enabled or not. If not, enable it. Now click on the “Publish” button and select OK in the popup.
Read: How to start Word, Excel, PowerPoint in Safe Mode.
4] After publishing the document, you will get a link. You have to paste this link in the MS Excel document which you want to connect with Google Sheets. If you paste this link in your web browser, you will see that it is available as a web page.
Now, launch MS Excel and create a new blank document in it. Go to “Data > New Query > From Other Sources > From Web.”
5] You will get a popup window, where you have to paste the copied link and click OK. If you get here two options, Basic and Advanced, select Basic.
6] The entire Google Sheet data will be available in Excel in the form of a table. Now, in the “Navigator” window, first, click “Select multiple items” check box and then select “Table 0.” A preview of the selected item is available on the right panel.
You can see in the below screenshot that column 1 is empty and column 2 lists serial numbers, and our actual data is listed in columns 3 and 4. Because we do not need columns 1 and 2, we have to delete them. For this, click on the “Edit” button at the bottom of the Navigator window. Some of you may have “Transform Data” in place of the “Edit” option depending on your MS Excel version. It will open the “Query Editor.”
Read: Excel Tips to Save Time and Work Faster.
7] We want to make our first row the header. For this, click on the top left corner and select “Use first row as headers.”
8] To delete unwanted columns, click the top left corner and select “Choose columns.” After that, uncheck the boxes of the columns that you do not want and click OK.
9] Now, click “Close & Load” to load the data of Google Sheet in Excel.
You have connected your Google Sheet with Excel. Whenever you make any changes to Google Sheet, you have to wait for 5 minutes to get those changes publish on the web. After that, you can have to refresh the excel file to get these changes reflected in the Excel file. For this, go to “Data > Refresh All.”
Read: Arrow keys not working in Excel.
If you want Excel to update by itself whenever you make changes to the Google Sheet, you have to do some more steps.
1] Select the table and go to “Data > Connections.” This will open a window in which you have to select “Properties.”
2] Now, click the “Refresh every” checkbox and enter the time after which you want the Excel to update automatically. I have entered 5 minutes. If you enable the “Refresh data when opening the file” option, Excel will check for updates every time you open it. When you are done, click OK and close the previous window.
That’s it. This is how you can connect Google Sheets with MS Excel.
Read next: Advanced Excel Tips and Tricks.