Одной из малоизвестных функций Excel является возможность импорта веб-страниц . Если вы можете получить доступ к данным на веб-сайте, легко преобразовать их в электронную таблицу Excel, если страница настроена правильно. Эта возможность импорта помогает вам анализировать веб-данные, используя знакомые формулы и интерфейсы Excel.
Инструкции в этой статье относятся к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel для Mac.
Импорт данных с веб-страницы
Excel — это приложение для работы с электронными таблицами, оптимизированное для оценки информации в двумерной сетке. Если вы собираетесь импортировать данные с веб-страницы в Excel, лучшим форматом является таблица. Excel импортирует каждую таблицу на веб-странице, только определенные таблицы или даже весь текст на странице.
When the imported web data is not structured, it requires restructuring before you can work with it.
Import the Data (Excel for PC)
After you’ve identified the website that contains the information you require, you can import the data directly into Excel using the From Web tool with only a few clicks, customizing import options along the way.
Here’s how to import a data table from the web on a PC:
-
Open Excel.
-
Select the Data tab and choose From Web in the Get & Transform Data group. The From Web dialog box will open.
-
Select Basic, type or paste the URL in the box, and select OK. If prompted, choose to Connect to the website.
-
In the Navigator box, select the tables to import. Excel isolates content blocks (text, tables, and graphics) if it knows how to parse them. To import more than one data asset, place a check mark next to Select multiple items.
-
After you select a table, a preview appears on the right side of the box. If it’s the table you want, select Load. The table appears in a new worksheet.
-
The right side of the screen displays the Queries & Connections pane. If you’ve imported multiple tables, select a table from the Queries & Connections pane to view it.
Edit Data Before Importing It
If the dataset you want is very large or not formatted to your expectations, modify it in the Query Editor before loading the data from the website into Excel.
In the Navigator box, select Transform Data instead of Load. Excel loads the table into the Query Editor instead of the spreadsheet. This tool opens the table in a specialized box that allows you to:
- Manage the query
- Choose or remove columns and rows in the table
- Sort data
- Split columns
- Group and replace values
- Combine the table with other data sources
- Adjust the parameters of the table
The Query Editor offers advanced functionality that’s more akin to a database environment (like Microsoft Access) than the familiar spreadsheet tools of Excel.
Work with Imported Data
After your web data loads into Excel, you’ll have access to the Query Tools ribbon. This new set of commands supports data-source editing (through the Query Editor), refreshing from the original data source, merging and appending with other queries in the workbook, and sharing the scraped data with other Excel users.
Import the Data (Excel for Mac)
You cannot import data from a website into Excel for Mac. You’ll need to save the website to your computer using your web browser’s Save As function. After you’ve saved the website, import the page’s HTML data into an Excel spreadsheet with the following method:
-
Open Excel.
-
Select Data > From HTML.
-
Navigate to the location of your saved HTML web page, select it, and select Open.
-
Excel automatically imports the entire web page into a new workbook. From here, clean up the chart to only include the needed data.
While the From HTML method for Mac isn’t as clean or controlled as the From Web option for PC, it still allows data from a web page to be imported into an Excel spreadsheet.