管理外部資料範圍及其內容

您可以變更外部資料範圍的屬性,以控制透過外部資料源的連線(例如資料庫、Web 查詢或文字檔)匯入至工作表中的資料。

外部資料範圍(又稱為「查詢表」)是定義的名稱或資料表名稱,可定義工作表中的資料位置。 當您連線至外部資料時,Excel 會自動建立外部資料範圍。 唯一的例外是連線至資料來源的樞紐分析表,樞紐分析表不使用外部資料範圍。 在 Excel 中,您可以格式化及配置外部資料範圍,或在計算中使用它,就像任何其他資料一樣。

外部資料範圍屬性

外部資料範圍有額外的屬性(不會與連接屬性混淆),您可以用來控制資料,例如保留儲存格格式設定和欄寬。 您可以按一下 [資料] 索引標籤上 [連線]群組中的 [屬性],變更這些外部資料範圍屬性。 然後執行下列動作進行變更:

  • 針對從 Microsoft Query 和資料連線嚮導建立的外部資料範圍,請使用 [外部資料屬性] 對話方塊。

  • 針對從匯入的文字檔或可檢索 HTML 資料的 Web 查詢所建立的外部資料範圍,請使用 [外部資料範圍屬性] 對話方塊。

  • 針對從檢索 XML 資料的 Web 查詢所建立的外部資料範圍,請使用 [ XML 對應屬性] 對話方塊。

使用範本與外部資料範圍

如果您想要共用摘要或以外部資料為基礎的報表,您可以為其他使用者提供包含外部資料範圍的活頁簿,或者您可以建立報表範本。 報表範本 可讓您儲存摘要或報表,而不儲存外部資料,因此檔案較小。 當使用者開啟報表範本時,就會檢索外部資料。

  1. 選取您要在其中搜尋 外部資料範圍的工作表。

  2. 在 資料編輯列上,按一下 [名稱] 方塊旁的箭號,然後按一下您要的外部資料範圍的名稱。

  1. [公式] 索引標籤的 [已定義之名稱] 群組中,按一下 [名稱管理員]

    Excel 功能區圖像

  2. 在 [名稱管理員] 對話方塊中,按一下外部資料範圍的名稱,然後按一下 [編輯]。 您也可以按兩下名稱。

  3. 在 [編輯名稱] 對話方塊的 [名稱] 方塊中,輸入參照的新名稱。

附註: [關閉] 按鈕只會關閉 [名稱管理員] 對話方塊。 您不需要按一下 [關閉],就能確認您已做的變更。

您可以變更由 Microsoft Query 所建立之外部資料範圍的基礎查詢、匯入的文字檔、Web 查詢或資料連線嚮導。

  1. 在您要變更基礎查詢的外部資料範圍中,按一下其中一個儲存格。

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

    Excel 功能區影像

  3. 在 [外部資料屬性] 對話方塊中,按一下 [連接屬性] 屬性

  4. 在 [連接屬性] 對話方塊中,按一下 [定義] 索引標籤,然後按一下 [編輯查詢]。

如需在 Microsoft Query 中建立及編輯查詢的詳細資訊,請參閱 Microsoft Query 說明。

  1. 在您要變更基礎查詢的外部資料範圍中,按一下其中一個儲存格。

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

    Excel 功能區影像

  3. 在 [活頁簿連線] 對話方塊中,按一下 [屬性]。

  4. 在 [連接屬性] 對話方塊中,按一下 [定義] 索引標籤,然後按一下 [編輯查詢]。

  5. 在 [匯入文字檔] 對話方塊中,按一下 [匯]。

  6. 在 [文字彙入嚮導] 中變更已匯入的文字檔,然後按一下[完成]

    如需有關匯入文字檔的詳細資訊,請參閱匯入或匯出文字檔

  1. 在您要變更基礎查詢的外部資料範圍中,按一下其中一個儲存格。

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

    Excel 功能區影像

  3. 在 [活頁簿連線] 對話方塊中,按一下 [屬性]。

  4. 在 [連接屬性] 對話方塊中,按一下 [定義] 索引標籤,然後按一下 [編輯查詢]。

  5. 在 [編輯 Web 查詢] 對話方塊中,對 Web 查詢進行變更,然後按一下[完成]

如需建立及編輯 Web 查詢的詳細資訊,請參閱連線至網頁

  1. 在您要變更基礎查詢的外部資料範圍中,按一下其中一個儲存格。

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

    Excel 功能區影像

  3. 在 [外部資料屬性] 對話方塊中,按一下 [連接屬性] 屬性

  4. 在 [連接屬性] 對話方塊中,按一下 [定義] 索引標籤。

  5. 執行下列其中一個動作:

    • 按一下 [命令類型] 方塊中的 [表格],然後在 [命令] 文字方塊中,將值變更為適當資料表、視圖或查詢的名稱。

    • 按一下 [命令類型] 方塊中的[SQL ] 或 [預設值],然後在 [命令] 文字方塊中編輯 SQL 語句。

      附註: 視連線的定義方式而定,[命令類型] 方塊可能無法使用(顯示為灰色)。

  1. 在資料編輯列上,按一下 [名稱] 方塊旁的箭號,然後選取您要複製之外部資料範圍的名稱。

    若是 Excel 表格,請選取該範圍的名稱,然後按 CTRL + A 以選取表格標題。

    如果您想要包含外部資料範圍以外的欄標籤或公式,請選取包含您要複製之欄標籤或公式的儲存格。 按一下 [資料編輯列] 的 [名稱] 方塊旁的箭號,然後按一下您要複製之外部資料範圍的名稱。

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

    Excel 功能區圖像

  3. 切換到您要貼上外部資料範圍的活頁簿。

  4. 按一下 貼上區左上角的儲存格。

    若要確保 外部資料 不會取代現有的資料,請務必在您按一下的儲存格右邊或右側,確認工作表中沒有資料。

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

附註: 如果您只複製外部資料範圍的一部分,則不會複製基礎 查詢 ,且無法刷新複製的資料。

您可以控制在資料重新整理時,如何處理傳回給 Excel 的較小或較大的資料集。

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

    Excel 功能區影像

  2. 在 [外部資料範圍屬性] 對話方塊中的 [如果資料範圍中的列數變更時,請按一下下列其中一項:

    • 插入新資料的儲存格、刪除未使用的儲存格

      附註: 

      • 在資料來源中新增一或多個資料列時,直接在外部資料範圍下方的儲存格向下移動,但外部資料範圍右側的儲存格不會移動。

      • 在資料來源中刪除一或多個資料列時,直接在外部資料範圍底下的儲存格會上移,但外部資料範圍右側的儲存格不會移動。

    • 為新資料插入整列,清除未使用的儲存格

      附註: 

      • 在資料來源中新增一或多個列時,[外部資料範圍] 的正下方和右側的儲存格會向下移動。

      • 在資料來源中刪除一或多個資料列時,直接位於外部資料範圍右側和右側的儲存格不會移動。

    • 使用新的資料覆寫現有的儲存格,清除未使用的儲存格

      附註: 

      • 在資料來源中新增一或多個資料列時,會覆寫外部資料範圍下方的儲存格,但外部資料範圍右側的儲存格不會移動。

      • 在資料來源中刪除一或多個資料列時,直接位於外部資料範圍右側和右側的儲存格不會移動。

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

    Excel 功能區影像

  2. 在 [資料格式設定及版面配置] 底下,執行下列其中一項或多項操作:

    • 若要將功能變數名稱納入第一列,請選取 [包含功能變數名稱] 核取方塊。

      附註: 此核取方塊僅適用于從 Microsoft Office 2003 程式轉換的外部資料範圍,或以程式設計方式建立的外部資料範圍。

    • 若要新增列數的資料行,請選取 [包含列號] 核取方塊。

      附註: 已匯入的文字檔、XML 檔案或 Web 查詢無法使用此核取方塊。

    • 若要保留您套用的儲存格格式設定,請選取 [保留儲存格格式設定] 核取方塊。

      附註: 針對 Web 查詢,當您在 [選項] 對話方塊中選取 [完整 HTML 格式設定] 時,會自動清除此選項。 您可以從 [編輯查詢] 對話方塊存取 [選項] 對話方塊。

    • 若要保留您設定的欄寬,請選取 [調整欄寬] 核取方塊。

    • 若要保留您套用的欄排序、篩選及版面配置,請選取 [保留欄排序]/[篩選/版面配置] 核取方塊。

      附註: 此核取方塊不適用於 Web 查詢。

    • 若要保留您套用的欄篩選,請選取 [保留欄篩選] 核取方塊。

      附註: 此核取方塊僅適用于以 XML 資料為基礎的 Web 查詢。

附註: 下列程式僅適用于從 Microsoft Office 2003 程式轉換的外部資料範圍,或只適用于以程式設計方式建立的外部資料範圍。 使用使用者介面建立的所有外部資料範圍都是以 Excel 表格的方式建立。 新增資料列時,這些資料表會自動展開,並填滿計算結果欄。

  1. 在與外部資料範圍中第一列資料連續的儲存格中輸入公式。

    附註: 根據第一列是否包含標題,資料的第一列可能是外部資料範圍中的第一列或第二列。

  2. 選取儲存格,然後按兩下 填滿控點 ,將公式複製到外部資料範圍中的所有列。

  3. 按一下外部資料範圍中的儲存格。

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

    Excel 功能區影像

  5. 在 [外部資料範圍屬性] 對話方塊中,選取 [資料列與資料相鄰] 核取方塊中的 [向下填滿公式]。

    附註: 如果在您 重新整理時,外部資料範圍會展開,Excel 只會複製緊鄰或位於外部資料範圍內的公式。

凍結外部資料範圍會保留資料,但不保留其基礎查詢,因此凍結的外部資料範圍無法進行重新整理。

  1. 按一下包含您要從中移除資料連線之 Excel 表格的工作表。

  2. 在資料編輯列上,按一下 [名稱] 方塊旁的箭號,然後按一下您要從中移除資料連線的外部資料範圍的名稱。 然後選取資料範圍。

  3. 在 [工具] 索引標籤的 [外部表格資料] 群組中,按一下 [取消連結]。 資料範圍仍會保留,但仍會有相同的名稱,但會刪除該連接。

附註: 下列程式僅適用于從 Microsoft Office 2003 程式轉換的外部資料範圍,或只適用于以程式設計方式建立的外部資料範圍。 使用使用者介面建立的所有外部資料範圍都是以 Excel 表格的方式建立。 新增資料列時,這些資料表會自動展開,並填滿計算結果欄。

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

    Excel 功能區影像

  2. 在 [查詢定義] 底下,清除 [儲存查詢定義] 核取方塊。

附註:  當您儲存活頁簿時,基礎查詢會從活頁簿中刪除。 不過,已儲存的資料庫查詢檔案(.dqy 或 .odc 檔案)不會被刪除。 如果您在 [查詢嚮導] 或 [Microsoft Query] 中建立查詢時,查詢檔案會儲存在您的電腦上,您可以再次使用該查詢來取得外部資料。 如需詳細資訊,請參閱 Microsoft Query 說明。

附註: 下列程式僅適用于使用 [資料連線嚮導] 或 [Microsoft Query] (而非匯入的文字檔或 Web 查詢)所建立的外部資料範圍、從 Microsoft Office 2003 程式轉換的外部資料範圍,或外部以程式設計方式建立的資料範圍。 使用使用者介面建立的所有外部資料範圍都是以 Excel 表格的方式建立。

  1. 執行下列其中一項動作,在外部資料範圍上方插入空白列:

    • 選取您要插入新列的上方儲存格。 接著,在 [常用] 索引標籤的 [儲存格] 群組中,按一下 [插入] 旁的箭號,然後按一下 [插入工作表列]。 因為您已選取一個儲存格,所以會在目前資料列上方插入一個新列。

    • 按一下您要插入新列之列的列選取器。 接著,在 [常用] 索引標籤的 [儲存格] 群組中,按一下 [插入]。 因為您已選取一個整列,所以會在選取的資料列上方插入一個新列。

  2. 在空白列的儲存格中輸入您想要的標籤。

  3. 按一下外部資料範圍中的儲存格。

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

    Excel 功能區影像

  5. 在 [外部資料範圍屬性] 對話方塊中的 [資料格式設定與版面配置] 底下,清除 [包含功能變數名稱] 核取方塊,然後按一下[確定]

  6. 若要移除現有的功能變數名稱並重新整理外部資料範圍,請按一下外部資料範圍中的儲存格,然後按一下 [重新整理資料 按鈕影像 ]。

附註: 當您從資料庫中取得資料時,對 Microsoft Query 中的欄名稱所做的任何變更,都會保留在外部資料範圍中。 如需變更查詢中資料行名稱的相關資訊,請參閱 Microsoft Query 說明。

  1. 按一下包含您要刪除之外部資料範圍的工作表。

  2. 在資料編輯列上,按一下 [名稱] 方塊旁的箭號,然後按一下您要刪除之外部資料範圍的名稱。

    如果外部資料範圍是 Excel 表格,請按 CTRL + A 以選取整個表格。

  3. 若要刪除外部資料範圍,請按 DELETE。

  4. 若要刪除基礎查詢,請在 Excel 提示您時,按一下[是]

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×