您可以變更外部數據範圍的屬性,以控制已透過連線至外部數據源,例如資料庫、Web 查詢或文本文件匯入到工作表的數據。
外部數據範圍是定義的名稱或數據表名稱,可定義帶入工作表的數據位置。 當您連線至外部數據時,Excel 會自動建立外部數據範圍。 唯一的例外是連線至數據源的數據透視表,數據透視表不會使用外部數據範圍。 在 Excel 中,您可以設定外部資料範圍的格式和配置,或將它用於計算,就像使用任何其他數據一樣。
外部數據範圍屬性
外部數據範圍有額外的屬性, (不必混淆查詢或連線屬性) 您可以用來控制數據,例如保留單元格格式設定和欄寬。 您可以按下 [數據] 索引標籤上 [Connections] 群組中的 [內容],來變更這些外部數據範圍屬性。 然後執行下列動作來進行變更:
-
對於從 Microsoft Query 和數據連線精靈建立的外部數據範圍,請使用 [ 外部數據屬性 ] 對話方塊。
-
對於從匯入的文本檔或擷取 HTML 數據的 Web 查詢所建立的外部數據範圍,請使用 [ 外部數據範圍屬性 ] 對話方塊。
-
對於從擷取 XML 數據的 Web 查詢所建立的外部數據範圍,請使用 [XML 對應內容 ] 對話方塊。
-
選取您要在其中搜尋 外部資料範圍的工作表。
-
在 資料編輯列上,按兩下 [ 名稱方塊] 旁的箭號,然後按下所要的外部數據範圍名稱。
-
選取 [名稱管理員]> 公式。
-
在 [ 名稱管理員 ] 對話框中,按兩下外部數據範圍的名稱,然後按兩下 [ 編輯]。 您也可以按兩下名稱。
-
在 [編輯名稱] 對話框中,在 [名稱] 方塊中輸入參照的新名稱。
附註: [關閉] 按鈕只會關閉 [名稱管理員] 對話框。 您不需要按下 [ 關閉 ] 就能認可您已進行的變更。
您可以變更從 Microsoft Query 建立的外部數據範圍、匯入的文字檔、Web 查詢或數據連線精靈的基礎查詢。
-
按下您要變更基礎查詢之外部數據範圍中的儲存格。
-
選 取 [數據 > 屬性]。
-
在 [ 外部資料屬性 ] 對話框中,按兩下 [ 連線內容 ] 。
-
在 [ 連線屬性 ] 對話框中,按兩下 [ 定義] 索引 標籤,然後按兩下 [ 編輯查詢]。
如需有關在 Microsoft Query 中建構和編輯查詢的詳細資訊,請參閱 Microsoft 查詢說明。
-
按下您要變更基礎查詢之外部數據範圍中的儲存格。
-
選取 [數據 > Connections & 查詢 ]> Connections 索引標籤,以滑鼠右鍵按下您要的連線,然後選取 [內容]。
-
在 [ 連線屬性 ] 對話框中,按兩下 [ 定義] 索引 標籤,然後按兩下 [ 編輯查詢]。
-
在 [ 匯入文本檔] 對話框中,按兩下 [ 匯入]。
-
在 [文字匯入精靈] 中變更匯入的文本文件,然後按兩下 [ 完成]。
如需匯入文字文件的詳細資訊,請參閱 匯入或匯出文字檔。
-
按下您要變更基礎查詢之外部數據範圍中的儲存格。
-
選取 [數據 > 內容]。
-
選取 [數據 > Connections & 查詢 ]> Connections 索引標籤,以滑鼠右鍵按下您要的連線,然後選取 [內容]。
-
在 [ 連線屬性 ] 對話框中,按兩下 [ 定義] 索引 標籤,然後按兩下 [ 編輯查詢]。
-
在 [ 編輯 Web 查詢 ] 對話框中變更 Web 查詢,然後按兩下 [ 完成]。
如需建立及編輯 Web 查詢的詳細資訊,請參閱 連線至網頁。
-
按下您要變更基礎查詢之外部數據範圍中的儲存格。
-
選取 [數據 > 內容]。
-
在 [ 外部資料屬性 ] 對話框中,按兩下 [ 連線內容 ] 。
-
在 [ 連線內容 ] 對話框中,按兩下 [ 定義] 索引 標籤。
-
執行下列其中一個動作:
-
在 [ 命令類型] 方 塊中,按兩下 [ 資料表 ],然後在 [ 命令] 文字 框中,將值變更為適當的數據表、檢視或查詢名稱。
-
在 [ 命令類型] 方 塊中,按兩下 [SQL ] 或 [ 預設 值],然後在 [ 命令] 文本 框中編輯 SQL 語句。
附註: 視連線的定義方式而定, [命令類型 ] 方塊可能無法使用, (顯示為灰色) 。
-
-
在數據編輯列上,按兩下 [ 名稱方塊] 旁的箭號,然後選取您要複製的外部數據範圍名稱。
若是 Excel 表格,請選取範圍的名稱,然後按 CTRL+A 以選取表格標題。
如果您想要包含不屬於外部數據範圍的欄卷標或公式,請選取包含您要複製之欄卷標或公式的單元格。 按兩下資料編輯列上 [名稱方 塊] 旁的箭號,然後按下您要複製的外部資料範圍名稱。
-
選取 [首頁 > 複製]。
-
切換到您要貼上外部數據範圍的活頁簿。
-
按兩下 貼上區左上方的儲存格。
為確保 外部資料 不會取代現有數據,請確定工作表在您按兩下的儲存格底下或右側沒有數據。
-
在 [常用] 索引標籤上,按一下 [剪貼簿] 群組中的 [貼上]。
附註: 如果您只複製外部數據範圍的一部分,則不會複製基礎 查詢 ,且無法重新整理複製的數據。
您可以控制如何處理數據重新整理時傳回 Excel 的較小或較大的數據集。
-
選取 [數據 > 內容]。
-
在 [ 外部數據範圍屬性 ] 對話框中, 如果重新整理時數據範圍中的列數變更,請按下列其中一項:
-
插入新數據的儲存格,刪除未使用的儲存格
附註:
-
當數據源中新增一或多個列時,直接在外部數據範圍下方的儲存格會向下移動,但外部數據範圍右邊的單元格不會移動。
-
當數據源中刪除一或多個列時,直接在外部數據範圍下方的儲存格會向上移動,但外部數據範圍右邊的單元格不會移動。
-
-
為新數據插入整列,清除未使用的儲存格
附註:
-
當數據源中新增一或多個列時,外部數據範圍下方和右側的儲存格會向下移動。
-
當數據源中刪除一或多個列時,外部數據範圍下方和右側的儲存格不會移動。
-
-
使用新數據覆寫現有儲存格,清除未使用的儲存格
附註:
-
當數據源中新增一或多個列時,會覆寫直接位於外部數據範圍底下的單元格,但外部數據範圍右邊的單元格不會移動。
-
當數據源中刪除一或多個列時,外部數據範圍下方和右側的儲存格不會移動。
-
-
-
選取 [數據 > 內容]。
-
在 [數據格式設定與版面配置] 底下,執行下列一或多項動作:
-
若要將域名作為第一列,請選取 [ 包含功能變數名稱 ] 複選框。
附註: 此複選框僅適用於從 Microsoft Office 2003 程式轉換的外部數據範圍,或以程式設計方式建立的外部數據範圍。
-
若要新增列號欄,請選取 [ 包含列號 ] 複選框。
附註: 此複選框不適用於匯入的文字檔、XML 檔案或 Web 查詢。
-
若要保留您套用的儲存格格式設定,請選取 [ 保留儲存格格式設定] 複 選框。
附註: 對於 Web 查詢,當您在 [選項] 對話框中選取 [ 完整 HTML 格式 設定] 時,會 自動清除此選項。 您可以從 [編輯查詢] 對話框存取 [選項] 對話框。
-
若要保留您設定的欄寬,請選取 [ 調整欄寬 ] 複選框。
-
若要保留您套用的欄排序、篩選和版面配置,請選取 [保留欄排序/篩選/版面配置 ] 複選框。
附註: 此複選框不適用於 Web 查詢。
-
若要保留您套用的欄篩選,請選取 [ 保留欄篩選] 複 選框。
附註: 此複選框僅適用於以 XML 資料為基礎的 Web 查詢。
-
附註: 下列程式僅適用於從 Microsoft Office 2003 程式轉換的外部數據範圍,或以程式設計方式建立的外部數據範圍。 使用使用者介面建立的所有外部數據範圍都會建立為 Excel 表格。 當新增列並向下填滿計算結果列時,這些表格會自動展開。
-
在外部數據範圍中第一列數據連續的儲存格中輸入公式。
附註: 視第一列是否包含標題而定,第一列的數據可能是外部數據範圍中的第一列或第二列。
-
選取單元格,然後按兩下 填滿控點 ,將公式複製到外部數據範圍中的所有列。
-
按一下外部資料範圍中的儲存格。
-
選取 [數據 > 內容]。
-
在 [ 外部數據範圍屬性 ] 對話框中,選取 [ 在數據相鄰的欄中向下填入公式] 複選框。
附註: 如果外部數據範圍會在您 重新整理時展開,Excel 只會複製緊鄰或外部數據範圍內的公式。
凍結外部數據範圍會保留數據,而不會保留其基礎查詢,因此無法重新整理凍結的外部數據範圍。
-
按兩下包含要從中移除資料連線之 Excel 表格的工作表。
-
在數據編輯列上,按兩下 [ 名稱方塊] 旁的箭號,然後按下您要從中移除資料連線的外部數據範圍名稱。 然後選取數據範圍。
-
在 [ 工具] 索引 標籤的 [ 外部表格數據 ] 群組中,按兩下 [取消連結]。 數據範圍會保留,而且仍然具有相同的名稱,但會刪除連線。
附註: 下列程式僅適用於從 Microsoft Office 2003 程式轉換的外部數據範圍,或以程式設計方式建立的外部數據範圍。 使用使用者介面建立的所有外部數據範圍都會建立為 Excel 表格。 當新增列並向下填滿計算結果列時,這些表格會自動展開。
-
選取 [數據 > 內容]。
-
在 [查詢定義] 底下,清除 [ 儲存查詢定義 ] 複選框。
附註: 當您儲存活頁簿時,基礎查詢會從活頁簿中刪除。 不過, (.dqy 或 .odc 檔案) 儲存的資料庫查詢檔案並不會刪除。 如果您在 [查詢精靈] 或 Microsoft Query 中建立查詢時儲存查詢,查詢檔案會儲存在您的電腦上,您可以再次使用查詢來擷取外部數據。 如需詳細資訊,請參閱 Microsoft 查詢說明。
附註: 下列程式僅適用於使用數據聯機精靈或 Microsoft Query (建立的外部數據範圍,不適用於匯入的文本檔或 Web 查詢) 、從 Microsoft Office 2003 程式轉換的外部數據範圍,或以程式設計方式建立的外部數據範圍。 使用使用者介面建立的所有外部數據範圍都會建立為 Excel 表格。
-
執行下列其中一項動作,在外部資料範圍上方插入空白列:
-
選取要在其中插入新列的儲存格。 然後在 [常 用] 索引 卷標的 [單元格 ] 群組 中,按兩下 [ 插入] 旁的箭號,然後按兩下 [ 插入工作表列]。 由於您已選取一個儲存格,因此會在目前列的上方插入一個新列。
-
按下要在其中插入新列之列的列選取器。 然後在 [常 用] 索引 標籤上,按兩下 [單元格 ] 群組 中的 [ 插入]。 由於您已選取一整列,因此會在選取的列上方插入一個新列。
-
-
在空白列的儲存格中輸入您要的標籤。
-
按一下外部資料範圍中的儲存格。
-
選取 [數據 > 內容]。
-
在 [ 外部數據範圍屬性 ] 對話方塊的 [ 資料格式設定與版面配置] 底下,清除 [ 包含功能變數名稱 ] 複選框,然後按兩下 [ 確定]。
-
若要移除現有的功能變數名稱並重新整理外部資料範圍,請按下外部資料範圍中的儲存格,然後按下[ 重新整理資料 ] 。
附註: 當您從資料庫擷取數據時,對 Microsoft Query 中欄名所做的任何變更都會保留在外部數據範圍中。 如需變更查詢中欄名的相關信息,請參閱 Microsoft 查詢說明。
-
按兩下包含您要刪除之外部資料範圍的工作表。
-
在數據編輯列上,按兩下 [ 名稱方塊] 旁的箭號,然後按下您要刪除的外部資料範圍名稱。
如果外部數據範圍是 Excel 表格,請按 CTRL+A 以選取整個表格。
-
若要刪除外部數據範圍,請按 DELETE。
-
若要刪除基礎查詢,請在 Excel 提示您時按兩下 [ 是 ]。