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

Bạn có thể thay đổi các thuộc tính của một phạm vi dữ liệu ngoài để điều khiển dữ liệu đã được nhập vào một trang tính thông qua kết nối với một nguồn dữ liệu ngoài, chẳng hạn như cơ sở dữ liệu, truy vấn web hoặc tệp văn bản.

Phạm vi dữ liệu ngoài (còn được gọi là bảng truy vấn) 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 một trang tính. Khi bạn kết nối với dữ liệu ngoài, Excel sẽ tự động tạo ra một phạm vi dữ liệu ngoài. Ngoại lệ duy nhất cho đâ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à đặt ra một phạm vi dữ liệu ngoài hoặc dùng nó trong các phép tính, như với bất kỳ dữ liệu nào khác.

Thuộc tính phạm vi 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 kết nối) mà bạn có thể dùng để điều khiển 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 phạm vi dữ liệu ngoài này bằng cách bấm vào thuộc tính trong nhóm kết nối trên tab dữ liệu . Sau đó thực hiện các thay đổi của bạn bằng cách thực hiện các thao tác sau:

  • Đối với các 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 các phạm vi dữ liệu ngoài được tạo ra từ một tệp văn bản đã nhập hoặc truy vấn web lấy dữ liệu HTML, hãy dùng hộp thoại thuộc tính phạm vi dữ liệu ngoài .

  • Đối với các phạm vi dữ liệu ngoài được tạo ra từ một truy vấn web lấy dữ liệu XML, hãy dùng hộp thoại thuộc tính ánh xạ XML .

Sử dụng mẫu với phạm vi dữ liệu ngoài

Nếu bạn muốn chia sẻ tóm tắt hoặc báo cáo dựa trên dữ liệu ngoài, bạn có thể cung cấp cho những người dùng khác một sổ làm việc có chứa phạm vi dữ liệu ngoài, hoặc bạn có thể tạo mẫu báo cáo. mẫu báo cáo cho phép bạn lưu tóm tắt hoặc báo cáo mà không cần lưu dữ liệu bên ngoài để tệp nhỏ hơn. Dữ liệu bên ngoài được truy xuất khi người dùng mở mẫu báo cáo.

  1. Chọn trang tính mà bạn muốn tìm kiếm 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 phạm vi dữ liệu ngoài mà bạn muốn.

  1. Trên tab Công thức, trong nhóm Tên đã Xác định, bấm Trình quản lý Tên.

    Nhóm Tên đã Xác định trên tab Công thức

  2. Trong hộp thoại trình quản lý tên , hãy bấm vào tên của phạm vi dữ liệu ngoài, sau đó 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 , hãy nhập tên mới cho tham chiếu trong hộp tên .

Lưu ý: Nút đóng chỉ đóng hộp thoại trình quản lý tên . Bạn không cần phải bấm đóng để cam kết các thay đổi mà bạn đã thực hiện.

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

  1. Bấm vào một ô trong phạm vi dữ liệu ngoài mà bạn muốn thay đổi truy vấn bên dưới.

  2. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  3. Trong hộp thoại thuộc tính dữ liệu bên ngoài , bấm thuộc tính kết nối thuộc tính .

  4. Trong hộp thoại thuộc tính kết nối , hãy bấm vào tab định nghĩa , rồi bấm sửa truy vấn.

Để biết thêm thông tin về việc xây dựng và chỉnh sửa các truy vấn trong Microsoft query, hãy xem trợ giúp truy vấn của Microsoft.

  1. Bấm vào một ô trong phạm vi dữ liệu ngoài mà bạn muốn thay đổi truy vấn bên dưới.

  2. Trên tab Dữ liệu, trong nhóm Kết nối, hãy bấm Kết nối.

    Ảnh Ribbon Excel

  3. Trong hộp thoại kết nối sổ làm việc, hãy bấm thuộc tính.

  4. Trong hộp thoại thuộc tính kết nối , hãy bấm vào tab định nghĩa , rồi bấm sửa truy vấn.

  5. Trong hộp thoại nhập tệp văn bản , hãy bấm nhập.

  6. Thực hiện thay đổi đối với tệp văn bản đã nhập trong trình hướng dẫn nhập văn bản, rồi bấm kết thúc.

    Để biết thêm thông tin về việc nhập tệp văn bản, hãy xem nhập hoặc xuất các tệp văn bản.

  1. Bấm vào một ô trong phạm vi dữ liệu ngoài mà bạn muốn thay đổi truy vấn bên dưới.

  2. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  3. Trong hộp thoại kết nối sổ làm việc, hãy bấm thuộc tính.

  4. Trong hộp thoại thuộc tính kết nối , hãy bấm vào tab định nghĩa , rồi bấm sửa truy vấn.

  5. Thực hiện các thay đổi đối với truy vấn web trong hộp thoại chỉnh sửa truy vấn web , rồi bấm kết thúc.

Để biết thêm thông tin về cách tạo và chỉnh sửa các truy vấn web, hãy xem kết nối với trang web.

  1. Bấm vào một ô trong phạm vi dữ liệu ngoài mà bạn muốn thay đổi truy vấn bên dưới.

  2. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  3. Trong hộp thoại thuộc tính dữ liệu bên ngoài , bấm thuộc tính kết nối thuộc tính .

  4. Trong hộp thoại thuộc tính kết nối , bấm vào tab định nghĩa .

  5. Thực hiện một trong những thao tác sau:

    • Trong hộp loại lệnh , hãy bấm bảng , rồi trong hộp văn bản lệnh , thay đổi giá trị thành tên của bảng, dạng xem hoặc truy vấn thích hợp.

    • Trong hộp loại lệnh , hãy bấm SQL hoặc mặc định , rồi trong hộp văn bản lệnh , sửa câu lệnh SQL.

      Lưu ý: Tùy thuộc vào cách thức xác định kết nối, hộp loại lệnh có thể không sẵn dùng (nó xuất hiện mờ đi).

  1. Trên thanh công thức, hãy bấm vào mũi tên bên cạnh hộp tênvà 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 phạm vi, 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, rồi 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. Ở tab Trang đầu, trong nhóm Bảng tạm, bấm Sao chép.

    Nhóm Bảng tạm trên tab Nhà

  3. Chuyển sang 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 bên dưới hoặc bên phải ô bạn bấm.

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

Lưu ý: Nếu bạn chỉ sao chép một phần của một phạm vi dữ liệu ngoài, các truy vấn cơ bản không được sao chép và không thể làm mới dữ liệu được sao chép.

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ề Excel khi dữ liệu được làm mới.

  1. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  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 những thao tác sau:

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

      Lưu ý: 

      • Khi một hoặc nhiều hàng được thêm vào trong nguồn dữ liệu, các ô trực tiếp dưới phạm vi dữ liệu ngoài, di chuyển xuống dưới, 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 dải 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 ô chưa sử dụng

      Lưu ý: 

      • Khi thêm một hoặc nhiều hàng được thêm vào trong 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 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 ô trực tiếp bên dưới và bên phải phạm vi dữ liệu ngoài không di chuyển.

    • Ghi đè các ô hiện có với dữ liệu mới, xóa các ô chưa sử dụng

      Lưu ý: 

      • Khi một hoặc nhiều hàng được thêm vào trong nguồn dữ liệu, các ô trực tiếp dưới phạm vi dữ liệu ngoài sẽ được 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 ô trực tiếp bên dưới và bên phải phạm vi dữ liệu ngoài không di chuyển.

  1. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  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 đây:

    • Để bao gồm tên trường như 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 một phạm vi dữ liệu ngoài được chuyển đổi từ chương trình Microsoft Office 2003 hoặc đến phạm vi dữ liệu ngoài được tạo lập trình.

    • Để thêm một cột số hàng, 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 đối với 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ữ định dạng ô .

      Lưu ý: Đối với truy vấn web, tùy chọn này sẽ tự động bị 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 cột, lọc và bố trí mà bạn áp dụng, hãy chọn hộp kiểm giữ gì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 lọc cột mà bạn áp dụng, hãy chọn hộp kiểm bảo lưu bộ lọc cột .

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

Lưu ý: Thủ tục sau đây chỉ áp dụng cho một phạm vi dữ liệu ngoài được chuyển đổi từ chương trình Microsoft Office 2003 hoặc đến một phạm vi dữ liệu ngoài được tạo lập trình. Tất cả các phạm vi dữ liệu ngoài được tạo ra bằng cách sử dụng giao diện người dùng sẽ được tạo như bảng Excel. Các bảng này tự động bung rộng khi các hàng mới được thêm vào và điền vào các cột được tính toán.

  1. Nhập một công thức vào một ô nằm kế bên hàng đầu tiên của dữ liệu trong phạm vi dữ liệu ngoài.

    Lưu ý: Hàng đầu tiên của dữ liệu 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 đề.

  2. Chọn ô và bấm đúp vào 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. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  5. Trong hộp thoại thuộc tính phạm vi dữ liệu ngoài , hãy chọn hộp kiểm điền công thức xuống trong cột bên cạnh dữ liệu .

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

Cố định phạm vi dữ liệu ngoài không thể làm mới dữ liệu nhưng không được truy vấn cơ sở của nó, do đó, một phạm vi dữ liệu ngoài đông cứng không được làm mới.

  1. Bấm vào trang tính có chứa bảng Excel mà bạn muốn loại bỏ kết nối dữ liệu.

  2. Trên thanh công thức, hãy 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 loại bỏ kết nối dữ liệu. Sau đó, phạm vi dữ liệu được chọn.

  3. Trên tab công cụ , trong nhóm dữ liệu bên ngoài , bấm vào hủyliên kết. Phạm vi dữ liệu vẫn giữ nguyên và vẫn mang cùng tên nhưng kết nối sẽ bị xóa.

Lưu ý: Thủ tục sau đây chỉ áp dụng cho một phạm vi dữ liệu ngoài được chuyển đổi từ chương trình Microsoft Office 2003 hoặc đến một phạm vi dữ liệu ngoài được tạo lập trình. Tất cả các phạm vi dữ liệu ngoài được tạo ra bằng cách sử dụng giao diện người dùng sẽ được tạo như bảng Excel. Các bảng này tự động bung rộng khi các hàng mới được thêm vào và điền vào các cột được tính toán.

  1. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  2. Bên dưới định nghĩa truy vấn, bỏ chọn hộp kiểm lưu định nghĩa truy vấn .

Lưu ý:  Khi bạn lưu sổ làm việc của mình, truy vấn tiềm ẩn sẽ bị xóa khỏi sổ làm việc. Tuy nhiên, các tệp truy vấn cơ sở dữ liệu đã lưu (. tqy hoặc. ODC) sẽ không bị xóa. Nếu bạn đã lưu truy vấn khi tạo trong trình hướng dẫn truy vấn hoặc trong Microsoft query, tệp truy vấn sẽ được lưu trên máy tính của bạn và bạn có thể sử dụng truy vấn lần nữa để truy xuất dữ liệu bên ngoài. Để biết thêm thông tin, hãy xem trợ giúp truy vấn Microsoft.

Lưu ý: Quy trình sau đây chỉ áp dụng cho một phạm vi dữ liệu ngoài được tạo bằng cách sử dụng trình hướng dẫn kết nối dữ liệu hoặc truy vấn Microsoft (chứ không phải là một tệp văn bản được nhập hoặc truy vấn web), phạm vi dữ liệu ngoài được chuyển đổi từ chương trình Microsoft Office 2003, hoặc bên ngoài phạm vi dữ liệu được tạo theo lập trình. Tất cả các phạm vi dữ liệu ngoài được tạo ra bằng cách sử dụng giao diện người dùng sẽ được tạo như bảng Excel.

  1. Chèn hàng trống phía 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 ô bên trên 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àngtrang tính. Vì bạn đã chọn một ô, một hàng mới được chèn vào bên 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 một hàng mới. Sau đó, trên tab trang đầu, trong nhóm ô , hãy bấm chèn. Vì bạn đã chọn một hàng toàn bộ, một hàng mới được chèn vào bên 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. Trên tab dữ liệu , trong nhóm kết nối , hãy bấm thuộc tính.

    Ảnh Ribbon Excel

  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, hãy xóa 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, sau đó bấm làm mới dữ liệu Ảnh nút .

Lưu ý: Khi bạn truy xuất dữ liệu từ cơ sở dữ liệu, mọi thay đổi đối với tên cột trong Microsoft query sẽ được giữ lại trong phạm vi dữ liệu ngoài. Để biết thông tin về việc thay đổi tên cột trong truy vấn, hãy xem trợ giúp truy vấn của Microsoft.

  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 bỏ.

  2. Trên thanh công thức, hãy 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 bỏ.

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

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

  4. Để xóa bỏ truy vấn tiềm ẩn, hãy bấm khi Excel nhắc bạn.

Bạn cần thêm trợ giúp?

Bạn luôn có thể yêu cầu một chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm hỗ trợ trong Cộng đồng Giải đáp hay đề xuất tính năng hoặc cải tiến mới ở Excel User Voice.

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×