Làm cách nào để sao chép danh sách tên trang tính?

Khi sổ làm việc Microsoft Excel chứa nhiều trang tính, tab trang tính bạn cần thường bị khuất tầm nhìn, điều này gây khó chịu và làm bạn chậm lại một chút. Có nhiều cách giải quyết, nhưng danh sách tên trang tính có thể hữu ích, đặc biệt nếu người dùng không có kỹ năng Excel cơ bản. Khi bạn có danh sách tên trang tính, người dùng có thể sử dụng chúng để điều hướng sổ làm việc. Một cách dễ dàng để tạo danh sách tên trang tính có sẵn thông qua Microsoft Power Query

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách sử dụng Power Query để tạo danh sách tên trang tính mà sau đó bạn có thể tải vào Excel và sử dụng để điều hướng sổ làm việc. Kỹ thuật trang tính này rất thú vị, nhưng trọng tâm thực sự là hiển thị siêu dữ liệu của sổ làm việc bằng cách sử dụng Power Query

XEM. Google Workspace so với. Microsoft 365. Phân tích song song với danh sách kiểm tra (TechRepublic Premium)

Tôi đang sử dụng Microsoft 365 trên hệ thống Windows 10 64 bit nhưng bạn có thể sử dụng các phiên bản Excel cũ hơn cho đến Excel 2010. Excel dành cho web không hỗ trợ Power Query, mặc dù nó hỗ trợ các truy vấn và kết nối hiện có

Bạn có thể tải xuống tệp demo Microsoft Excel cho hướng dẫn này

Cách tắt OneDrive

Trước khi làm bất cứ điều gì, bạn phải tắt OneDrive. Nếu không, bạn sẽ gặp lỗi rằng người khác đang sử dụng sổ làm việc nên bạn không thể truy cập sổ làm việc đó. Thủ phạm là OneDrive

Để tạm thời tắt OneDrive, hãy tìm nó trong Thanh tác vụ hoặc vùng tràn (Hình A). Nhấp vào biểu tượng đám mây màu xanh và sau đó nhấp vào Cài đặt - bánh răng ở góc trên cùng bên phải. Trong danh sách kết quả, chọn Thoát OneDrive. Xác nhận hành động bằng cách chọn Đóng OneDrive khi được nhắc. Nếu bạn là người đăng ký 365, bạn đang sử dụng OneDrive cho dù bạn có nhận ra hay không

Hình A

đây là một tình huống tạm thời. Khi hoàn tất, bạn sẽ muốn bật lại OneDrive, tôi sẽ hướng dẫn bạn cách thực hiện sau

Cách khởi chạy Power Query

Chúng ta sẽ làm việc với một sổ làm việc Excel đơn giản, như trong Hình B, với sáu trang tính trống. Một trong các trang tính cũng có một phạm vi được đặt tên, được biểu thị bằng các đường viền. Đối với mục đích trình diễn, chúng tôi không cần bất kỳ dữ liệu nào vì chúng tôi đang theo dõi siêu dữ liệu của sổ làm việc, bao gồm tên trang tính

Hình B

Khi bạn đã tắt OneDrive, bạn đã sẵn sàng khởi chạy Power Query

1. Từ bên trong sổ làm việc Excel với tất cả các tab, bấm vào tab Dữ liệu

2. Trong nhóm Get & Transform Data, nhấp vào menu thả xuống Get Data và chọn From File

3. Tiếp theo, chọn Từ sổ làm việc Excel

4. Khi được nhắc, hãy tìm và chỉ định cùng một sổ làm việc rồi bấm Nhập. Đây là nơi bạn sẽ gặp rắc rối nếu trước tiên bạn không tắt OneDrive. Nếu điều này xảy ra với bạn, hãy đóng mọi thứ và tắt OneDrive, như minh họa ở trên

5. Trong khung kết quả, chọn một sheet bất kỳ (Hình C). Bạn chỉ cần một. Lưu ý rằng danh sách cũng chứa một phạm vi được đặt tên, SheetANamedRange. Chúng tôi sẽ không sử dụng nó, nhưng tôi muốn bạn thấy rằng quá trình tải này cung cấp nhiều hơn các trang tính. Nó cũng sẽ hiển thị tên Bảng

Hình C

6. Nhấp vào Chuyển đổi dữ liệu để khởi chạy Power Query

Hiện tại, Power Query có vẻ trống

Cách hiển thị siêu dữ liệu của tệp Excel trong Power Query

Không có dữ liệu trong Power Query, nhưng đó là điều chúng tôi muốn. Điều này cho phép chúng tôi hiển thị siêu dữ liệu của tệp

Ở bên phải, danh sách Các bước đã áp dụng có ba bước, nhưng chúng ta chỉ cần giữ lại bước đầu tiên, đó là Nguồn. Xóa Điều hướng và Loại đã thay đổi bằng cách nhấp vào X ở bên trái của mỗi bước. Làm như vậy sẽ hiển thị siêu dữ liệu về sổ làm việc, như thể hiện trong Hình D

Hình dung

Cột Loại hiển thị loại mặt hàng. Có sáu trang tính và một tên được xác định. Chúng tôi chỉ cần sáu trang tính, vì vậy bước đầu tiên của chúng tôi là lọc ra hàng tên đã xác định. Khi áp dụng kỹ thuật này cho dữ liệu của riêng bạn, bạn có thể không có bất kỳ tên nào được xác định, nhưng tôi khuyên bạn vẫn nên áp dụng bộ lọc nếu bạn định sử dụng lại truy vấn này. Khi bạn cập nhật danh sách sau này, bạn có thể có một dải ô đã đặt tên hoặc một Bảng

Để áp dụng bộ lọc để chỉ hiển thị các trang tính, hãy làm như sau

1. Nhấp vào danh sách thả xuống của cột Loại

2. Trong danh sách kết quả, bỏ chọn mọi thứ trừ Trang tính (Hình E)

Hình E

3. Nhấn OK để áp dụng bộ lọc

Hình F hiển thị các bản ghi kết quả. Không có hàng nào cho tên đã xác định. Khi áp dụng điều này cho công việc của riêng bạn, siêu dữ liệu có thể có các thành phần khác ngoài tên trang tính. Đảm bảo lọc mọi thứ trừ trang tính

Hình F

Tại thời điểm này, chúng ta phải xóa tất cả các cột trừ cột Tên. Làm như vậy,

1. Nhấp chuột phải vào tiêu đề Tên để chọn cột đó

2. Chọn Xóa các cột khác từ menu con kết quả

Sau khi xóa mọi thứ trừ cột tên trang tính, bấm Tải & Đóng để tải danh sách vào Excel. Kết quả là một Table trong sheet mới có tên là Apr (2)

Cách dùng danh sách để điều hướng sheet trong Excel

Cách bạn sử dụng Bảng tên trang tính tùy thuộc vào bạn, nhưng chúng tôi sẽ tạo một cột chứa các siêu liên kết để điều hướng nhanh. Để biến tên trang tính thành siêu liên kết, hãy nhập hàm sau vào B2 và sao chép sang các ô còn lại, như trong Hình G

=HYPERLINK("[PQSheetHyperlinks_Demo.xlsx]" & A2 & "!A1",A2)

Hình G

Hàm này yêu cầu tên trang tính hiện tại, “[PQSheetHyperlinks_Demo. xlsx]”, ngay cả khi di chuyển trong cùng một sổ làm việc. Thành phần &A2& nối tên sheet trong A2. Các ". Thành phần A1” chọn A1 của trang tính tương ứng. Đối số cuối cùng, A2, chỉ định văn bản được hiển thị, trong trường hợp này, là tên trang tính

Cách làm mới tên sheet Bảng trong Excel

Khi bạn thêm, xóa hoặc đổi tên trang tính, bạn sẽ cần cập nhật danh sách. Đầu tiên, lưu sổ làm việc. Sau đó, nhấp chuột phải vào danh sách và chọn Làm mới từ menu con kết quả

Nếu bạn có Microsoft 365 và OneDrive, bạn phải bật lại OneDrive trước khi tính năng này hoạt động. Cách dễ nhất để làm như vậy là đăng nhập vào tài khoản Microsoft 365 của bạn, rồi bấm Đồng bộ trên menu

Có một điều hơi kỳ lạ là bạn phải tắt OneDrive để tạo danh sách và sau đó kích hoạt nó để làm mới danh sách. Điều này có thể thay đổi với bất kỳ bản cập nhật nào

Bản tin hàng tuần của Microsoft

Hãy trở thành người trong Microsoft của công ty bạn bằng cách đọc các mẹo, thủ thuật và cheat sheet dành cho Windows và Office này