從網頁取得外部資料

您可以建立或執行 Web 查詢,從網頁擷取文字或資料,網頁經常會包含極適合在 Excel 中進行分析的資訊。視您的需求而定,您可以擷取可更新的資料。 也就是說,您可以使用網頁上的最新資料,更新 Excel 中的資料; 也可以從網頁擷取資料,並讓資料在工作表上保持為靜態。

您想要做什麼?

深入了解關於從網頁取得外部資料的情形

建立及編輯 Web 查詢

建立 Web 查詢參數查詢

從網頁複製資料

執行已儲存的 Web 查詢檔案

讓網頁將 Web 查詢重新導向至其他資料來源

問題:我從 Web 查詢排序、篩選或搜尋外部資料時發生問題

深入了解關於從網頁取得外部資料的情形

您可以使用 Web 查詢,擷取儲存在您的內部網路或網際網路中可更新的資料,例如網頁上的單一表格、多個表格,或所有文字;然後使用 Excel 中的工具和功能來分析資料。例如,您可以從公開網頁擷取並更新股票報價,也可以從公司網頁擷取並更新銷售資訊表。

從表格或預設格式區域 (表格是以 HTML <TABLE> 標籤定義; 預設格式區域則通常是以 HTML <PRE> 標籤定義) 中擷取資料時,Web 查詢尤其好用。已擷取的資料不包含 .gif 圖像之類的圖片,也不包含指令碼的內容。

若要建立 Web 查詢,您需要透過公司內部網路或透過電腦或網路上的數據機存取全球資訊網 (WWW)。或者,您可以建立本機 HTML 或 XML 檔案的查詢。

範例 Web 查詢

安裝 Microsoft Office Excel 2007 時會附隨一些範例 Web 查詢。 依預設,這些查詢是位於硬碟上的 \Program Files\Microsoft Office\Office12\QUERIES 資料夾中:

  • MSN MoneyCentral 投資人主要指數.iqy

  • MSN MoneyCentral 投資人貨幣匯率.iqy

  • MSN MoneyCentral 投資人股票報價.iqy (Web 查詢參數查詢)

取得外部資料的方法

如果是使用 Excel,您可以使用 [新增 Web 查詢] 對話方塊,匯入來自網頁的資料 (在 [資料] 索引標籤的 [取得外部資料] 群組中,按一下 [從 Web])。

如果是使用 Internet Explorer,有兩種方法可以取得外部資料放入 Office Excel 中:

  • 使用熟悉的複製和貼上命令,將資料從網頁貼到 Excel 工作表。當您將網頁資料貼到 Excel 時,您可以按一下 [貼上選項] 按鈕圖像 ,然後按一下 [建立可更新的 Web 查詢],讓資料保持靜態或可更新。

  • 用滑鼠右鍵按一下網頁,然後在捷徑功能表上按一下 [匯出至 Microsoft Excel],就會顯示 [新增 Web 查詢] 對話方塊。

附註: 您也可以在 Excel 開啟任何 HTML 檔案、MHTML 檔案或語式正確的 XML 檔案。當您開啟 HTML 或 MHTML 檔案時,可獲得整個網頁,但可能會遺失部分格式設定、指令碼、圖像檔案 (僅 HTML) 或單一儲存格資料清單。當您開啟 XML 檔案時,可以選擇套用一或多個參照樣式表。無論是何種情況,資料都無法更新。

建立 Web 查詢參數查詢

參數查詢是每次執行時都會提示您輸入項目的查詢。您可以透過編輯已儲存的 Web 查詢檔案 (.iqy) 建立 Web 查詢參數查詢。例如,每次您執行 Web 查詢參數查詢時,從網頁取得股市行情的 Web 查詢都可提示您輸入參數值,像是股票代號。

Web 查詢參數查詢是以 URL 查詢字串為依據,也就是附加至 URL 結尾的一或多個名稱/值對。有些網站會使用 URL 查詢字串變更網頁的內容。URL 查詢字串有下列格式,如下列範例所示︰1 2 3 4 5 -----------------------------URL------------------------------?<Name>=<Val> http://moneycentral.msn.com/investor/external/excel/quotes.asp?quote=MSFT

1

URL

網頁的 URL

2

?

開始 URL 查詢字串的問號字元 (?)。

3

<名稱>

在此範例中,名稱是 Quote

4

=

等號字元 (=) 用來分隔名稱和值。

5

<值>

在此範例中,值是 MSFT

如果您要傳遞兩個以上的名稱/值對,使用 & 符號字元加以區隔。例如:1 2 3 ...?<Name>=<Val>&<Name>=<Val> ...?Quote=MSFT&Quote=IBM

1

<名稱>=<值>

第一組名稱/值對是 Quote=MSFT

2

&

& 符號字元用來分隔各組名稱/值對。

3

<名稱>=<值>

第二組名稱/值對是 Quote=IBM

使用下列格式,讓 URL 查詢字串成為 Web 查詢參數查詢的依據。例如:1 2 3 4 5 6 ...?<Name>=["Parameter","Prompt"] ...?Symbol=["StockSymbol","Enter stock symbol:"]

1

<Name>

在此範例中,名稱是 Symbol

2

=

等號字元 (=) 用來分隔參數和提示字串的名稱。

3

"Parameter"

參數的名稱,以引號括住,Excel 用來識別 [參數] 對話方塊中的參數,在此範例為 StockSymbol

4

,

逗號字元 (,) 用來分隔參數和提示。

5

"Prompt"

提示會以引號括住,Excel 會顯示在 [輸入股票代號︰] 對話方塊中,在此範例為 Enter stock symbol:

6

[...]

左中括號 ([) 和右中括號 (]) 字元用來分隔提示字串。

重新導向 Web 查詢至其他資料來源

如果您是網頁作者,您可以將 Excel 的 Web 查詢重新導向至資料來源 (例如 XML 或文字檔),以增加使用者從您頁面擷取之資料的可靠性。

例如,如果 HTML 網頁包含股票報價表,即使資料是從其他資料來源中取出來的,該表格資料也可以用在 Web 查詢中。或者,如果您是指向 Windows SharePoint Services 網站上的表格,其中摘要總結專案狀態,您可以建立 Web 查詢,從主複本狀態清單取出資料。

這種重新導向作業有兩大優勢。第一,即使資料是最佳化為可供分析,您正在匯入的 HTML 網頁也可以設定為供檢視的格式 (例如 XML 格式);第二,資料的 HTML 檢視可以分割成資料頁面 (例如,一次 20 項結果),但是 Web 查詢可以重新導向至整組資料集。

您可以使用 HTML 屬性搭配 TABLE、PRE、XMP、LISTING 或 PLAINTEXT 標籤,以識別基礎資料的儲存位置。網頁作者可以使用這個重新導向屬性 o:WebQuerySourceHRef 列出資料來源 URL,使 Web 查詢可以連結至基礎來源資料。這個重新導向屬性定義在 Microsoft Office 命名空間中,可避免與其他重新導向屬性發生衝突。資料來源可以是任何支援的 Web 查詢資料類型,例如 HTML、文字或 XML。使用這個屬性時,您不必擔心重新整理時無法取得正確的內容。Web 查詢包含資料來源路徑的相關資訊,可以直接從該來源擷取更新的資料。

頁面頂端​​

建立及編輯 Web 查詢

  1. 執行以下其中一項操作,以建立新 Web 查詢,或編輯現有 Web 查詢:

    建立新 Web 查詢   

    1. 請執行下列其中一項操作:

      • 在 Office Excel 中的 [資料] 索引標籤上,按一下 [取得外部資料] 群組中的 [從 Web]

      • 在瀏覽器中,瀏覽到您要從中查詢資料的網頁,用滑鼠右鍵按一下網頁,然後按一下快顯功能表上的 [匯出至 Microsoft Excel]。

        [新增 Web 查詢] 對話方塊就會出現。

    2. 在 [新增 Web 查詢] 對話方塊中,輸入您要取得資料的網頁 URL。您可以鍵入 URL、貼上複製的位址,或按一下 [位址] 清單旁的箭號,然後選取最近使用過的位址。

      附註: URL 最長的長度是 255 個字元。

    3. 按一下 [移至]。

      編輯現有的 Web 查詢   

    4. 在 [資料] 索引標籤的 [連線] 群組中,按一下 [連線]。

      Excel 功能區影像

    5. 在 [活頁簿連線] 對話方塊中選取 Web 查詢,然後按一下 [內容]。

    6. 在 [連線內容] 對話方塊中,按一下 [定義] 索引標籤,然後按 [編輯查詢]。

  2. 按一下您要匯入表格旁邊的 按鈕圖像 按鈕,或按一下網頁左上角的按鈕 按鈕圖像 ,以匯入整個網頁。

    提示: 如果頁面的表格旁沒有 按鈕圖像 按鈕,按一下對話方塊上方的 [顯示圖示] 按鈕圖像 ,以顯示這些按鈕。

  3. 若要設定傳回資料方式的格式設定和匯入選項,請按一下 [選項],然後在 [Web 查詢選項] 對話方塊中執行下列其中一項或多項操作:

    1. 在 [格式設定​​] 底下,按一下其中一個選項:

      •     這個選項不會傳回您所查詢網頁上的任何格式,而僅傳回文字。格式是套用工作表上已有的格式。

      • 僅保存 RTF 的格式     這個選項會傳回 Office Excel 可以重新產生的最接近格式類型,如字型樣式,其中不包含 HTML 格式,如超連結格式。

      • 整個 HTML 格式     這個選項會傳回 Excel 支援的所有 HTML 格式,如超連結格式。這個選項會清除 [外部資料範圍內容] 對話方塊中的 [自動套用格式] 核取方塊。

    2. 在 [為預設格式的 <PRE> 區塊匯入設定] 下,選取下列其中一項或多項:

      • 匯入 <PRE> 區塊至欄      由 <PRE> 標籤包圍的資料區塊將分別匯入工作表上不同的欄,清除這個核取方塊會將資料傳回到單一欄中。

      • 連續分隔符號視為單一處理     如果具有定義或分隔您 <PRE> 區段中各欄文字的字元 (例如逗號),可以指定在 Excel 同時遇到多個這些分隔符號時,將視為一個,因此,不會在每個連續分隔符號之間放置空白欄。只有在選取 [匯入 <PRE> 區塊至欄] 核取方塊時,才能使用這個核取方塊。

      • 所有段落皆使用相同的設定     選取這個核取方塊,在網頁上所有預設格式區段使用 [連續分隔符號視為單一處理] 設定。清除這個核取方塊,則只有第一個預設格式區段使用您的設定;或者如果您要 Excel 決定最佳設定,也請清除這個核取方塊。只有在選取 [匯入 <PRE> 區塊至欄] 核取方塊時,才能使用這個核取方塊。

    3. 在 [其他匯入設定] 下,選取下列其中一項或多項:

      • 關閉日期識別     這個選項可確保網頁上看起來像日期的數字在工作表上顯示為數字,例如,除非選取這個選項,否則 Excel 會將運動名次得分 03-07 識別為日期 3 月 7 日。

      • 停用 Web 查詢重新導向     這個選項可確保 Web 查詢不會重新導向至與您在查詢的網頁上所看到不同的資料來源。選取這個核取方塊,可保持與舊版 Excel 中建立的查詢相容。

  4. 按一下 [確定],Web 查詢就會與活頁簿一起儲存。

  5. 您也可以選擇,將 Web 查詢儲存至 Web 查詢檔案 (.iqy),以便讓您在其他活頁簿中執行查詢,以建立參數查詢。

    如需詳細資訊,請參閱建立 Web 查詢參數查詢

    將 Web 查詢儲存至 Web 查詢檔案

    1. 在 [編輯 Web 查詢] 對話方塊中,按一下 [儲存查詢] 按鈕圖像

    2. 尋找您要儲存查詢的資料夾。

      根據預設,儲存的查詢位於硬碟上的 \Documents and Settings\<username>\Application Data\Microsoft\Queries 資料夾。

    3. 在 [檔案名稱] 方塊中輸入檔案名稱。

    4. 按一下 [儲存]。

      查詢就會儲存為文字檔,而以 .iqy 為檔案副檔名。

  6. 按一下 [匯入]。

    在 [匯入資料] 對話方塊中,執行下列其中一項操作:

    1. 若要從網頁傳回資料至選取的工作表,請按一下 [已經存在的工作表]。按一下您要放置外部資料範圍左上角的儲存格,然後按一下 [確定]。

    2. 若要傳回資料至新工作表中,請按一下 [新工作表],然後按一下 [確定]。Excel 會將新工作表加入活頁簿中,並自動以新工作表的左上角做為外部資料範圍的開始。

      按一下 [確定] 之後,[幕後執行更新作業] 圖示 更新指示器 就會出現在狀態列上,指出正在執行查詢。若要檢查查詢的狀態,請按兩下更新圖示。

頁面頂端​​

建立 Web 查詢參數查詢

  1. 在 Windows 檔案總管中,尋找電腦上的查詢檔案。

    根據預設,儲存的查詢位於硬碟上的 \Documents and Settings\<username>\Application Data\Microsoft\Queries 資料夾。

  2. 用滑鼠右鍵按一下檔案,然後按 [用記事本編輯]。查詢會在 Microsoft 記事本中開啟,您可以在其中對檔案做任何變更。

  3. 在檔案的文字中,找出匯入資料來源網址的 URL,舉例來說:

http://moneycentral.msn.com/investor/external/excel/quotes.asp

在 URL 結尾,鍵入下列各項 (請勿在 URL 結尾插入任何空格):

?Symbol=["Quote", "Enter one or more financial symbols separated by commas."]

  1. 在 [檔案] 功能表按一下 [儲存],然後結束記事本。

頁面頂端​​

從網頁複製資料

  1. 在您的網頁瀏覽器中選取您要複製的資料。

  2. 複製資料。

    附註: 如需有關複製作業的資訊,請參閱您的瀏覽器說明。

  3. 切換到 Excel。

  4. 按一下您要複製資料出現的工作表區域左上角。

  5. 在 [常用] 索引標籤的 [剪貼簿] 群組中,按一下 [貼上]。

    [常用] 索引標籤中的 [剪貼簿] 群組

  6. 如果資料未如預期顯示,請按一下 [貼上選項] 按鈕圖像 ,然後按一下其中一個選項︰

    • [保留來源格式設定],不做任何變更。

    • [符合目的格式設定],以符合現有的儲存格格式設定。

    • [建立可更新的 Web 查詢],以建立查詢至您複製資料的網頁。 如果稍後變更網頁,您可以更新資料。

頁面頂端​​

執行已儲存的 Web 查詢檔案

Web 查詢是以 .iqy 副檔名儲存在文字檔中。已儲存的查詢在不同活頁簿中共用相同 Web 查詢,或與他人共用 Web 查詢,以及建立 Web 查詢參數查詢時很有用。

  1. 按一下 [Microsoft Office 按鈕] Office 按鈕影像 ,然後按一下 [開啟]。

  2. 在 [查詢] 方塊中,找出您要儲存查詢檔案的資料夾。

  3. 選取您要執行的 Web 查詢。

  4. 按一下 [開啟]。

  5. 在 [匯入資料] 對話方塊中,執行下列其中一項操作:

    • 若要從網頁傳回資料至選取的工作表,請按一下 [已經存在的工作表]。按一下您要放置外部資料範圍左上角的儲存格,然後按一下 [確定]。

    • 若要傳回資料至新工作表中,請按一下 [新工作表],然後按一下 [確定]。Excel 會將新工作表加入活頁簿中,並自動以新工作表的左上角做為外部資料範圍的開始。

  6. 如果 Web 查詢是參數查詢,[輸入參數值] 對話方塊會提示您輸入參數,除非您已經設定參數查詢傳回特定值。

    若要編輯參數的設定值,請在步驟 5 中按一下 [匯入資料] 對話方塊中的 [參數]。

    如需詳細資訊,請參閱自訂參數查詢

    Excel 執行查詢。[幕後執行更新作業] 圖示 更新指示器 就會出現在狀態列上,指明正在執行該查詢。若要檢查查詢的狀態,請按兩下更新圖示。

頁面頂端​​

讓網頁將 Web 查詢重新導向至其他資料來源

  1. 在網頁的開頭 HTML 標籤中包括 Microsoft Office 命名空間宣告:

    <HTML xmlns:o="urn:schemas-microsoft-com:office:office”>

  2. 在網頁的本文中,將下列屬性加入開頭的 TABLE、PRE、XMP、LISTING 或 PLAINTEXT 標籤,舉例來說:

    <TABLE ... o:WebQuerySourceHRef="URL">

    其中值是您要重新導向 Web 查詢的 URL (相對或絕對)。

附註: 使用者可以選取 [Web 查詢選項] 對話方塊中的 [停用 Web 查詢重新導向] 核取方塊,略過網頁上的重新導向。您可以從 [新增 Web 查詢] 對話方塊存取這個核取方塊。

頁面頂端​​

問題:我從 Web 查詢排序、篩選或搜尋外部資料時發生問題

Web 查詢的外部資料可能包含前置、結尾,或多個內嵌 Unicode 空格字元 (值 160)。這些空格字元在排序或篩選資料,或執行搜尋作業時,有時可能會造成未預期的結果。

如需如何處理這些和其他字元的詳細資訊,請參閱從文字移除空格及非列印字元

頁面頂端​​

這項資訊有幫助嗎?

我們應該如何改進?

我們應該如何改進?

若要保護您的隱私,請不要在意見反應中包含連絡資訊。 檢閱我們的 隱私權原則

感謝您的意見反應!