Trong khi làm việc trong Excel, chúng ta thường cần tạo hệ thống phân cấp nhiều cấp . Bằng cách sử dụng phân cấp nhiều cấp , chúng tôi có thể trình bày dữ liệu của mình ở định dạng đơn giản và ngắn gọn. Microsoft Excel là một phần mềm mạnh mẽ và linh hoạt. Sử dụng Excel, chúng tôi có thể tạo hệ thống phân cấp nhiều cấp bằng cách làm theo một số bước đơn giản. Trong bài viết này, chúng ta sẽ tìm hiểu về 2 các phương pháp đơn giản để tạo hệ thống phân cấp nhiều cấp trong Excel .
2 phương pháp đơn giản để tạo cấu trúc phân cấp đa cấp trong Excel
Trong số 2 , trong phương pháp đầu tiên, chúng tôi sẽ sử dụng Xác thực dữ liệu tính năng của Excel. Và trong phương pháp thứ hai, chúng tôi sẽ sử dụng Power Pivot Add-in của Excel.
Chưa kể rằng chúng tôi đã sử dụng Microsoft Excel 365 phiên bản cho bài viết này, bạn có thể sử dụng bất kỳ phiên bản nào khác tùy theo sự thuận tiện của bạn.
1. Sử dụng Tính năng xác thực dữ liệu để tạo cấu trúc phân cấp nhiều cấp
Sử dụng Xác thực dữ liệu tính năng là một trong những cách dễ nhất để tạo hệ thống phân cấp nhiều cấp trong Excel . Trong tập dữ liệu sau, chúng tôi có Tên phim , Hiển thị lịch biểu và Loại ghế cho một số chương trình liên tục của rạp chiếu phim. Mục đích của chúng tôi là tạo hệ thống phân cấp nhiều cấp sử dụng dữ liệu này. Hãy làm theo các bước được đề cập bên dưới để thực hiện việc này.
Bước 01: Chỉnh sửa tùy chọn xác thực dữ liệu cho cột tên phim
Trong Tên phim , chúng ta cần tạo một danh sách thả xuống để có thể chọn bất kỳ bộ phim nào mình thích. Nó khá đơn giản để làm. Chỉ cần làm theo.
- Trước tiên, hãy chọn ô B5 trong cột Tên phim .
- Sau đó, đi tới Dữ liệu từ Ruy-băng .
- Sau đó, nhấp vào Xác thực dữ liệu từ Công cụ dữ liệu nhóm.
Do đó, Xác thực dữ liệu hộp thoại sẽ mở ra như trong hình sau.
- Bây giờ, hãy nhấp vào biểu tượng thả xuống như được đánh dấu trong hình ảnh bên dưới.
- Sau đó, chọn Danh sách từ menu thả xuống.
- Tiếp theo, nhấp vào Nguồn và nhập công thức sau.
='Dataset 1 '!$B$5:$B$10 Đây, $ B $ 5:$ B $ 10 là phạm vi của các ô trong Tên phim cột của tập dữ liệu đã cho.
Lưu ý: Ở đây, chúng tôi đã sử dụng tham chiếu ô tuyệt đối bởi vì trong mỗi ô, chúng tôi cần hiển thị tất cả các tên phim để chọn một trong số chúng.
- Sau đó, nhấp vào OK .
- Sau đó, biểu tượng thả xuống sẽ có sẵn bên cạnh ô B5 như được đánh dấu trong hình sau. Nhấp vào đó.
Do đó, trong danh sách thả xuống, bạn có thể chọn bất kỳ bộ phim nào bạn thích.
Bước 02: Dán tính năng xác thực trong các ô còn lại trong cột tên phim
Ở đây, chúng tôi sẽ sử dụng Dán đặc biệt tính năng của Excel để dán Xác thực dữ liệu tính năng của ô B5 vào các ô khác của cột có tên Tên phim .
- Trước tiên, hãy chọn ô B5 và nhấn CTRL + C để sao chép nó.
- Sau đó, chọn các ô còn lại của cột Tên phim .
- Sau đó, truy cập Trang chủ từ Ruy-băng .
- Tiếp theo, nhấp vào Dán tùy chọn.
- Bây giờ, chọn Dán đặc biệt từ menu thả xuống.
- Sau đó, từ Dán đặc biệt hộp thoại, chọn Xác thực như được đánh dấu trong hình ảnh sau đây.
- Sau đó, nhấp vào OK .
Do đó, Xác thực dữ liệu tính năng sẽ được sao chép vào các ô đã chọn. Bạn có thể chọn bất kỳ Tên phim nào từ danh sách thả xuống bên cạnh mỗi ô và nhận được kết quả như hình sau.
Bước 03: Chỉnh sửa tùy chọn xác thực dữ liệu để hiển thị cột lịch biểu
Bây giờ, chúng tôi cần tạo danh sách thả xuống dựa trên lựa chọn trong Tên phim cột. Ví dụ:phim Into the Deep có các chương trình có sẵn tại 5 các thời điểm khác nhau trong ngày. Nhưng bộ phim Bullet Train chỉ có sẵn tại 3 các thời điểm khác nhau trong ngày. Vì vậy, tùy chọn sẽ có sẵn trong Lịch trình hiển thị phụ thuộc vào bộ phim được chọn trong Tên phim cột. Để làm điều này, chúng tôi sẽ sử dụng hàm OFFSET và chức năng MATCH . Hãy làm theo các bước được đề cập bên dưới.
- Trước tiên, hãy chọn ô C5 và thực hiện theo các bước được đề cập trong Bước 01 để có được kết quả sau.
- Sau đó, nhập công thức sau vào Hộp Nguồn .
=OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1) Tại đây, ô $ B $ 14 đề cập đến tiêu đề của Lịch trình chiếu trong tổng số Tập dữ liệu 1 , ô $ B5 đại diện cho bộ phim đã chọn dưới Tên phim và phạm vi $ B $ 14:$ G $ 14 mảng tên của phim trong trang tính là Tập dữ liệu 1 .
Phân tích Công thức
- MATCH ($ B5, ’Tập dữ liệu 1‘! $ B $ 14:$ G $ 14,0) → Nó trả về vị trí của giá trị tra cứu trong một hàng, cột hoặc bảng.
- $ B5 → là lookup_value tranh luận
- ‘Tập dữ liệu 1‘! $ B $ 14:$ G $ 14 → là lookup_array tranh luận
- 0 → là match_type tranh luận
- Đầu ra → 1
- OFFSET (‘Tập dữ liệu 1‘! $ B $ 14,1, MATCH ($ B5, ’Tập dữ liệu 1‘! $ B $ 14:$ G $ 14,0) -1,5,1) → nó trở thành OFFSET (‘Tập dữ liệu 1‘! $ B $ 14,1,1-1,5,1)
- ‘Tập dữ liệu 1‘! $ B $ 14 → là tài liệu tham khảo tranh luận
- 1 → là hàng tranh luận
- 1-1 → là cols tranh luận
- 5 → là chiều cao tranh luận
- 1 → là chiều rộng tranh luận
- Đầu ra → {11:00 sáng, 3:30 chiều, 6:00 tối, 9:30 tối, 11:00 tối}
- Sau đó, nhấp vào OK .
- Do đó, biểu tượng thả xuống sẽ hiển thị bên cạnh ô C5 như thể hiện trong hình ảnh dưới đây. Nhấp vào đó.
Do đó, bạn sẽ thấy Lịch trình hiển thị có sẵn cho phim Into the Deep .
Bước 04: Dán Tính năng xác thực vào các ô còn lại trong cột Hiển thị lịch biểu
Bây giờ, chúng tôi sẽ sao chép Xác thực dữ liệu thuộc tính của ô C5 vào các ô còn lại bằng cách sử dụng Dán đặc biệt tính năng của Excel.
- Bây giờ, chọn ô C5 và thực hiện theo các bước được đề cập trong Bước 02 .
- Sau đó, chọn phim Bullet Train , bạn có thể thấy rằng chỉ có 3 các tùy chọn có sẵn cho Lịch trình hiển thị giống như tập dữ liệu đã cho của chúng tôi.
- Tương tự, bạn có thể chọn Lịch trình hiển thị cho các bộ phim khác và nhận được đầu ra như hình ảnh minh họa bên dưới.
Bước 05: Chỉnh sửa tùy chọn xác thực dữ liệu cho cột loại chỗ ngồi
Bây giờ, chúng ta cần tạo một danh sách thả xuống khác trong Loại chỗ ngồi dựa trên lựa chọn của chúng tôi về Lịch trình hiển thị . Trong tập dữ liệu, chúng ta có thể thấy rằng tất cả Lịch trình hiển thị không có cùng Loại chỗ ngồi . Ví dụ:đối với chương trình lúc 11:00 sáng , tất cả 3 Các loại chỗ ngồi có sẵn, nhưng cho buổi biểu diễn lúc 6:00 tối , chỉ Thông thường và VIP Loại chỗ ngồi có sẵn. Để tạo danh sách thả xuống phụ thuộc này, chúng tôi sẽ sử dụng lại OFFSET và MATCH hàm số. Hãy sử dụng các bước được thảo luận bên dưới để thực hiện việc này.
- Trước tiên, hãy chọn ô D5 và thực hiện theo các bước được đề cập trong Bước 01 để có được kết quả sau.
- Sau đó, nhập công thức sau vào Hộp Nguồn trong Xác thực dữ liệu hộp thoại.
=OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1) Tại đây, ô $ B $ 23 đề cập đến tiêu đề trong số Loại ghế trong Tập dữ liệu 1 , ô $ C5 đại diện cho Lịch trình hiển thị đã chọn và phạm vi $ B $ 23:$ F $ 23 là mảng của S how Schedule trong trang tính Tập dữ liệu 1 .
Phân tích Công thức
- MATCH ($ C5, ’Tập dữ liệu 1‘! $ B $ 23:$ F $ 23,0) → Nó trả về vị trí của giá trị tra cứu trong một hàng, cột hoặc bảng.
- $ C5 → là lookup_value tranh luận
- ‘Tập dữ liệu 1‘! $ B $ 23:$ F $ 23 → là lookup_array tranh luận
- 0 → là match_type tranh luận
- Đầu ra → 3
- OFFSET (‘Tập dữ liệu 1‘! $ B $ 23,1, MATCH ($ C5, ’Tập dữ liệu 1‘! $ B $ 23:$ F $ 23,0) -1,3,1) → nó trở thành OFFSET (‘Tập dữ liệu 1‘! $ B $ 23,1,3-1,3,1) .
- ‘Tập dữ liệu 1‘! $ B $ 23 → là tài liệu tham khảo tranh luận
- 1 → là hàng tranh luận
- 3-1 → là cols tranh luận
- 3 → là chiều cao tranh luận
- 1 → là chiều rộng tranh luận
- Đầu ra → {Thông thường, VIP}
- Sau đó, nhấp vào OK .
Do đó, bạn sẽ thấy biểu tượng thả xuống bên cạnh ô D5 . Dưới dạng Lịch trình hiển thị đã chọn là 6:00 CH , Thông thường và VIP các tùy chọn có sẵn trong ô D5 .
Bước 06: Dán tính năng xác thực vào các ô còn lại trong cột loại ghế
Ở giai đoạn này, chúng tôi sẽ sao chép Xác thực dữ liệu thuộc tính của ô D5 vào các ô khác của cột Loại chỗ ngồi . Hãy làm theo các bước được thảo luận bên dưới.
- Trước hết, hãy chọn ô D5 và thực hiện theo các bước được đề cập trong Bước 02 .
- Bây giờ, hãy nhấp vào biểu tượng thả xuống bên cạnh ô C6 . Dưới dạng Lịch trình hiển thị đã chọn là 11:00 sáng , bạn có thể thấy rằng tất cả 3 Loại chỗ ngồi có sẵn.
- Tương tự, chọn Loại chỗ ngồi cho các ô khác và bạn sẽ nhận được đầu ra như hình sau.
Đọc thêm: Cách tạo cấu trúc phân cấp trong Excel (3 cách dễ dàng)
2. Sử dụng Power Pivot để tạo cấu trúc phân cấp nhiều cấp trong Excel
Sử dụng Power Pivot tính năng là một cách hiệu quả khác để tạo hệ thống phân cấp nhiều cấp trong Excel . Trong tập dữ liệu sau, chúng tôi có bảng Doanh số dữ liệu từ các Thành phố khác nhau ở nhiều Bang khác nhau của Hoa Kỳ . Chúng tôi sẽ tạo hệ thống phân cấp nhiều cấp bằng cách sử dụng bảng này. Hãy làm theo quy trình được thảo luận bên dưới.
Bước 01: Thêm Bảng vào Cửa sổ Power Pivot
Nếu bạn không có Power Pivot Add-in đã bật, hãy làm theo các bước được đề cập ở đây để kích hoạt nó. Sau khi bạn đã bật Phần bổ trợ Power Pivot sử dụng các bước được cung cấp bên dưới.
- Trước hết, hãy chọn toàn bộ bảng và sau đó nhấn CTRL + C để sao chép nó.
- Bây giờ, hãy chuyển đến Power Pivot từ Ruy-băng .
- Sau đó, nhấp vào Quản lý như được đánh dấu trong hình ảnh sau đây.
Do đó, một Cửa sổ Power Pivot trống sẽ mở ra như trong hình dưới đây.
- Tiếp theo, trong Cửa sổ Power Pivot nhấp vào Bảng tạm tùy chọn.
- Sau đó, chọn Dán từ menu thả xuống.
Do đó, Bản xem trước dán hộp thoại sẽ có sẵn.
- Sau đó, từ Dán bản xem trước hộp thoại, nhấp vào OK .
Do đó, bảng của bạn sẽ được thêm vào Cửa sổ Power Pivot như thể hiện trong hình ảnh sau đây.
Bước 02: Tạo hệ thống phân cấp trong Chế độ xem sơ đồ
Trong bước này, chúng tôi sẽ tạo một hệ thống phân cấp mô tả các thành phố trực thuộc tiểu bang và các tiểu bang trực thuộc quốc gia.
- Trước tiên, hãy nhấp vào Chế độ xem sơ đồ tùy chọn từ Chế độ xem tab cửa sổ Power Pivot .
Do đó, Chế độ xem sơ đồ sẽ hiển thị trên màn hình của bạn như được minh họa trong hình ảnh sau.
- Bây giờ, hãy nhấp vào phần được đánh dấu của hình ảnh được cung cấp bên dưới.
Do đó, một Hệ thống phân cấp sẽ được tạo.
- Bây giờ, hãy kéo cột có tên Quốc gia và thả nó vào Cấu trúc phân cấp mới tạo.
- Tương tự, hãy kéo và thả các cột khác tuần tự và bạn sẽ thấy kết quả sau.
Bước 03: Tạo Pivot Table
- Sau đó, nhấp vào Bảng tổng hợp tùy chọn.
- Sau đó, Tạo bảng tổng hợp hộp thoại sẽ khả dụng và nhấp vào OK .
Kết quả là một bảng tính mới sẽ được tạo như trong hình sau.
- Sau đó, từ Pivot Table Fileds hộp thoại, kéo và thả Hệ thống phân cấp1 vào Hàng và phần Bán hàng vào Tổng giá trị phần.
Do đó, bạn sẽ nhận được Bảng tổng hợp sau trong trang tính của bạn.
- Bây giờ, hãy nhấp vào dấu + như được đánh dấu trong hình sau.
Kết quả là bạn sẽ thấy Hoa, và Tổng doanh số của mỗi Bang như thể hiện trong hình ảnh sau đây.
- Sau đó, nhấp lại vào dấu + bên cạnh tên của Bang Arizona .
Điều này sẽ hiển thị Các thành phố ở Bang của Arizona cùng với Bán hàng của họ .
Tương tự, bằng cách nhấp vào biểu tượng + bên cạnh các Kỳ khác , bạn có thể nhận được kết quả sau.
Đọc thêm: Cách tạo cấu trúc phân cấp trong Excel Pivot Table (với các bước dễ dàng)
Phần thực hành
Trong Sổ làm việc Excel , chúng tôi đã cung cấp một Phần Thực hành ở bên phải của mỗi trang tính. Hãy thực hành nó một mình.
Kết luận
Đó là tất cả về phiên của ngày hôm nay. Tôi thực sự tin rằng bài viết này có thể hướng dẫn bạn tạo hệ thống phân cấp nhiều cấp trong Excel . Vui lòng để lại nhận xét nếu bạn có bất kỳ thắc mắc hoặc đề xuất nào để cải thiện chất lượng của bài viết. Để tìm hiểu thêm về Excel, bạn có thể truy cập trang web của chúng tôi, ExcelDemy . Chúc bạn học vui vẻ!
Các bài viết liên quan
- Cách Tạo Biểu đồ Phân cấp trong Excel (3 Cách Dễ dàng)
- Tạo cấu trúc phân cấp ngày trong bảng tổng hợp Excel (với các bước dễ dàng)
- Cách sử dụng Cấu trúc phân cấp SmartArt trong Excel (Với các bước Đơn giản)
- Thêm Thứ bậc Hàng trong Excel (2 Phương pháp Dễ dàng)