Quản lý phạm vi dữ liệu ngoài và thuộc tính của chúng

Phạm vi dữ liệu ngoài là tên đã xác định hoặc tên bảng xác định vị trí của dữ liệu được đưa vào trang tính. Khi bạn kết nối với dữ liệu ngoài, Excel sẽ tự động tạo phạm vi dữ liệu ngoài. Ngoại lệ duy nhất cho điều này là báo cáo PivotTable được kết nối với nguồn dữ liệu — báo cáo PivotTable không sử dụng phạm vi dữ liệu ngoài. Trong Excel, bạn có thể định dạng và bố trí phạm vi dữ liệu ngoài hoặc sử dụng dải dữ liệu đó trong các phép tính, như với bất kỳ dữ liệu nào khác.

Thuộc tính dải dữ liệu ngoài

Phạm vi dữ liệu ngoài có các thuộc tính bổ sung (không nên nhầm lẫn với thuộc tính truy vấn hoặc kết nối) mà bạn có thể sử dụng để kiểm soát dữ liệu, chẳng hạn như bảo toàn định dạng ô và độ rộng cột. Bạn có thể thay đổi các thuộc tính dải dữ liệu ngoài này bằng cách bấm vào Thuộc Connections trong nhóm Dữ liệu trên tab Dữ liệu. Sau đó thực hiện các thay đổi của bạn bằng cách làm như sau:

• Đối với phạm vi dữ liệu ngoài được tạo từ Microsoft Query và Trình hướng dẫn Kết nối Dữ liệu, hãy sử dụng hộp thoại Thuộc tính Dữ liệu Bên ngoài.

• Đối với phạm vi dữ liệu ngoài được tạo từ tệp văn bản hoặc truy vấn Web được nhập có truy xuất dữ liệu HTML, hãy sử dụng hộp thoại Thuộc tính Phạm vi Dữ liệu Ngoài.

• Đối với phạm vi dữ liệu ngoài được tạo từ truy vấn Web truy xuất dữ liệu XML, hãy sử dụng hộp thoại Thuộc tính Ánh xạ XML .

1. Chọn trang tính mà bạn muốn tìm kiếm một trang tính miền dữ liệu ngoài.

2. Trên thanh công thức, bấm vào mũi tên bên cạnh Hộp Tên, rồi bấm vào tên của dải dữ liệu ngoài mà bạn muốn.

1. Chọn Công thức >Quản lý Tên.

2. Trong hộp thoại Trình quản lý Tên, bấm vào tên của phạm vi dữ liệu ngoài, rồi bấm sửa. Bạn cũng có thể bấm đúp vào tên.

3. Trong hộp thoại Sửa Tên, nhập tên mới cho tham chiếu trong hộp Tên.

Bạn có thể thay đổi truy vấn cơ sở cho phạm vi dữ liệu ngoài được tạo từ Microsoft Query, tệp văn bản đã nhập, truy vấn Web hoặc Trình hướng dẫn Kết nối Dữ liệu.

1. Trên thanh công thức, bấm vào mũi tên bên cạnh Hộp Tên, rồi chọn tên của phạm vi dữ liệu ngoài mà bạn muốn sao chép.

   Đối với bảng Excel, hãy chọn tên của dải ô, rồi nhấn CTRL+A để chọn tiêu đề bảng.

   Nếu bạn muốn bao gồm các nhãn cột hoặc công thức không phải là một phần của phạm vi dữ liệu ngoài, hãy chọn các ô có chứa nhãn cột hoặc công thức mà bạn muốn sao chép. Bấm vào mũi tên bên cạnh Hộp Tên trên thanh công thức và bấm vào tên của phạm vi dữ liệu ngoài mà bạn muốn sao chép.

2. Chọn Trang đầu > Sao chép.

3. Chuyển đến sổ làm việc mà bạn muốn dán phạm vi dữ liệu ngoài.

4. Bấm vào ô phía trên bên trái của vùng dán.

   Để đảm bảo rằng dữ liệu ngoài không thay thế dữ liệu hiện có, hãy đảm bảo rằng trang tính không có dữ liệu nào bên dưới hoặc bên phải ô mà bạn bấm vào.

5. Trên tab Trang đầu, trong nhóm Bảng tạm, bấm Dán.

Bạn có thể kiểm soát cách xử lý tập dữ liệu nhỏ hơn hoặc lớn hơn được trả về cho Excel khi dữ liệu được làm mới.

1. Chọn Dữ liệu > tính.

2. Trong hộp thoại Thuộc tính Phạm vi Dữ liệu Ngoài, bên dưới Nếu số hàng trong phạm vi dữ liệu thay đổi khi làm mới, hãy bấm vào một trong các mục sau:

   • Chèn ô cho dữ liệu mới, xóa các ô không sử dụng đến

     Lưu ý: 

     • Khi một hoặc nhiều hàng được thêm vào nguồn dữ liệu, các ô trực tiếp bên dưới phạm vi dữ liệu ngoài di chuyển xuống, nhưng các ô ở bên phải phạm vi dữ liệu ngoài không di chuyển.

     • Khi một hoặc nhiều hàng bị xóa trong nguồn dữ liệu, các ô trực tiếp bên dưới phạm vi dữ liệu ngoài di chuyển lên, nhưng các ô ở bên phải của phạm vi dữ liệu ngoài không di chuyển.

   • Chèn toàn bộ hàng cho dữ liệu mới, xóa các ô không sử dụng

     Lưu ý: 

     • Khi một hoặc nhiều hàng được thêm vào nguồn dữ liệu, các ô trực tiếp bên dưới và bên phải của phạm vi dữ liệu ngoài sẽ di chuyển xuống.

     • Khi một hoặc nhiều hàng bị xóa trong nguồn dữ liệu, các ô ngay bên dưới và bên phải phạm vi dữ liệu ngoài không di chuyển.

   • Ghi đè lên các ô hiện có bằng dữ liệu mới, xóa các ô không sử dụng

     Lưu ý: 

     • Khi một hoặc nhiều hàng được thêm vào nguồn dữ liệu, các ô trực tiếp bên dưới phạm vi dữ liệu ngoài sẽ bị ghi đè, nhưng các ô ở bên phải phạm vi dữ liệu ngoài không di chuyển.

     • Khi một hoặc nhiều hàng bị xóa trong nguồn dữ liệu, các ô ngay bên dưới và bên phải phạm vi dữ liệu ngoài không di chuyển.

1. Chọn Dữ liệu > tính.

2. Bên dưới Định dạng và bố trí dữ liệu, hãy thực hiện một hoặc nhiều thao tác sau:

   • Để bao gồm tên trường làm hàng đầu tiên, hãy chọn hộp kiểm Bao gồm tên trường.

     Lưu ý: Hộp kiểm này chỉ sẵn dùng cho phạm vi dữ liệu ngoài được chuyển đổi từ chương trình Microsoft Office 2003 hoặc phạm vi dữ liệu ngoài được tạo theo chương trình.

   • Để thêm một cột chứa số hàng, hãy chọn hộp kiểm Bao gồm số hàng.

     Lưu ý: Hộp kiểm này không sẵn dùng cho tệp văn bản đã nhập, tệp XML hoặc truy vấn Web.

   • Để giữ nguyên định dạng ô mà bạn áp dụng, hãy chọn hộp kiểm Giữ nguyên định dạng ô.

     Lưu ý: Đối với truy vấn Web, tùy chọn này được tự động xóa khi bạn chọn Định dạng HTML Đầy đủ trong hộp thoại Tùy chọn. Bạn truy nhập hộp thoại Tùy chọn từ hộp thoại Sửa Truy vấn.

   • Để giữ nguyên độ rộng cột mà bạn đã đặt, hãy chọn hộp kiểm Điều chỉnh độ rộng cột.

   • Để giữ nguyên sắp xếp, lọc và bố trí cột mà bạn áp dụng, hãy chọn hộp kiểm Giữ nguyên sắp xếp /lọc/bố trí cột.

     Lưu ý: Hộp kiểm này không sẵn dùng cho truy vấn Web.

   • Để giữ nguyên bộ lọc cột mà bạn áp dụng, hãy chọn hộp kiểm Giữ nguyên bộ lọc cột.

     Lưu ý: Hộp kiểm này chỉ sẵn dùng cho truy vấn Web dựa trên dữ liệu XML.

1. Nhập công thức vào ô liền kề với hàng dữ liệu đầu tiên trong phạm vi dữ liệu ngoài.

   Lưu ý: Hàng dữ liệu đầu tiên có thể là hàng đầu tiên hoặc thứ hai trong phạm vi dữ liệu ngoài, tùy thuộc vào việc hàng đầu tiên có chứa tiêu đề hay không.

2. Chọn ô và bấm đúp vào biểu mẫu núm điều khiển điền sao chép công thức vào tất cả các hàng trong phạm vi dữ liệu ngoài.

3. Bấm vào một ô trong phạm vi dữ liệu ngoài.

4. Chọn Dữ liệu > tính.

5. Trong hộp thoại Thuộc tính Phạm vi Dữ liệu Ngoài, chọn hộp kiểm Điền công thức vào các cột liền kề với dữ liệu.

   Lưu ý: Nếu phạm vi dữ liệu ngoài bung rộng khi bạn làm mới, Excel sẽ chỉ sao chép những công thức liền kề hoặc ngay bên trong phạm vi dữ liệu ngoài.

Việc đóng băng phạm vi dữ liệu ngoài sẽ giữ lại dữ liệu nhưng không phải là truy vấn cơ sở của nó, vì vậy không thể làm mới phạm vi dữ liệu ngoài cố định.

1. Chèn hàng trống bên trên phạm vi dữ liệu ngoài bằng cách thực hiện một trong các thao tác sau:

   • Chọn một ô ở trên mà bạn muốn chèn hàng mới. Sau đó, trên tab Trang đầu, trong nhóm Ô, bấm vào mũi tên bên cạnh Chèn, rồi bấm Chèn Hàng Trang tính. Vì bạn đã chọn một ô, một hàng mới sẽ được chèn phía trên hàng hiện tại.

   • Bấm vào bộ chọn hàng của hàng ở trên mà bạn muốn chèn hàng mới. Sau đó, trên tab Trang đầu, trong nhóm Ô , bấm Chèn. Vì bạn đã chọn toàn bộ một hàng, một hàng mới sẽ được chèn phía trên hàng đã chọn.

2. Nhập nhãn bạn muốn trong các ô trong hàng trống.

3. Bấm vào một ô trong phạm vi dữ liệu ngoài.

4. Chọn Dữ liệu > tính.

5. Trong hộp thoại Thuộc tính Phạm vi Dữ liệu Ngoài, bên dưới Định dạng và bố trí dữ liệu, bỏchọn hộp kiểm Bao gồm tên trường, rồi bấm OK.

6. Để loại bỏ tên trường hiện có và làm mới phạm vi dữ liệu ngoài, hãy bấm vào một ô trong phạm vi dữ liệu ngoài, rồi bấm Làm mới Dữ liệu .

1. Bấm vào trang tính có chứa phạm vi dữ liệu ngoài mà bạn muốn xóa.

2. Trên thanh công thức, bấm vào mũi tên bên cạnh Hộp Tên, rồi bấm vào tên của phạm vi dữ liệu ngoài mà bạn muốn xóa.

   Nếu phạm vi dữ liệu ngoài là bảng Excel, nhấn CTRL+A để chọn toàn bộ bảng.

3. Để xóa phạm vi dữ liệu ngoài, nhấn DELETE.

4. Để xóa bỏ truy vấn cơ sở, hãy bấm Có khi Excel nhắc bạn.