Computer >> Hướng Dẫn Máy Tính >  >> Phần Mềm >> Office

Tạo hệ thống phân cấp đa cấp trong Excel: 2 phương pháp đơn giản

2 phương pháp đơn giản để tạo hệ thống phân cấp đa cấp trong Excel

Khi làm việc trong Excel, chúng ta thường cần tạo hệ thống phân cấp đa cấp (multi-level hierarchy). Điều này giúp trình bày dữ liệu gọn gàng, súc tích. Excel cung cấp nhiều cách để làm điều này. Bài viết giới thiệu 2 phương pháp: sử dụng Data Validation (Kiểm tra dữ liệu) và Power Pivot Add-in.

Lưu ý: Bài viết sử dụng Microsoft Excel 365, nhưng các phiên bản khác cũng áp dụng được.

1. Sử dụng tính năng Data Validation để tạo phân cấp đa cấp

Cách này là một trong những cách dễ nhất. Ví dụ: tập dữ liệu có Tên phim, Lịch chiếu, Loại ghế của một rạp chiếu phim. Mục tiêu: tạo danh sách thả xuống phân cấp: chọn Phim -> Lịch chiếu của phim đó -> Loại ghế của suất chiếu đó.

Bước 01: Thiết lập Data Validation cho cột Tên phim

  • Chọn ô B5 (cột Movie Name).
  • Tab Data > Data Validation (nhóm Data Tools).
  • Trong hộp thoại, tab Settings: Allow > chọn List.
  • Ô Source: nhập công thức ='Dataset 1 '!$B$5:$B$10 (vùng chứa tên phim). Dùng tham chiếu tuyệt đối ($).
  • Nhấn OK. Mũi tên thả xuống xuất hiện tại B5.

Bước 02: Sao chép Data Validation cho các ô còn lại cột Tên phim

  • Chọn B5 > Ctrl+C.
  • Chọn các ô còn lại cột Movie Name.
  • Tab Home > Paste > Paste Special > chọn Validation > OK.

Bước 03: Thiết lập Data Validation cho cột Lịch chiếu (phụ thuộc vào Phim)

  • Chọn ô C5, mở Data Validation, chọn List.
  • Ô Source nhập công thức: =OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1).
  • Giải thích: MATCH tìm vị trí phim được chọn trong hàng tiêu đề (B14:G14). OFFSET trả về vùng 5 hàng (5 suất chiếu) bắt đầu từ ô dưới tiêu đề phim đó.
  • Nhấn OK. Thả xuống C5 sẽ chỉ hiện lịch chiếu của phim được chọn ở B5.

Bước 04: Sao chép Validation cho các ô còn lại cột Lịch chiếu

  • Làm tương tự Bước 02: Copy C5 > Paste Special > Validation cho các ô dưới.
  • Thử chọn phim khác (ví dụ: Bullet Train) -> Lịch chiếu chỉ hiện 3 suất.

Bước 05: Thiết lập Data Validation cho cột Loại ghế (phụ thuộc vào Lịch chiếu)

  • Chọn ô D5, mở Data Validation, chọn List.
  • Ô Source: =OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1).
  • Giải thích: MATCH tìm vị trí suất chiếu (C5) trong hàng tiêu đề suất chiếu (B23:F23). OFFSET trả về vùng 3 hàng (3 loại ghế) tương ứng.
  • Nhấn OK. Thả xuống D5 hiển thị loại ghế phù hợp với suất chiếu đã chọn.

Bước 06: Sao chép Validation cho các ô còn lại cột Loại ghế

  • Copy D5 > Paste Special > Validation cho các ô dưới.
  • Kết quả: Hệ thống phân cấp 3 cấp hoàn chỉnh: Phim -> Lịch chiếu -> Loại ghế.

2. Sử dụng Power Pivot để tạo phân cấp đa cấp

Power Pivot là công cụ mạnh để tạo mô hình dữ liệu và phân cấp. Ví dụ: dữ liệu Bán hàng theo Thành phố, Tiểu bang, Quốc gia (USA).

Bước 01: Thêm bảng vào cửa sổ Power Pivot

  • Chọn toàn bảng dữ liệu > Ctrl+C.
  • Tab Power Pivot > Manage để mở cửa sổ Power Pivot.
  • Trong cửa sổ Power Pivot: tab Home > Paste > Paste (hoặc Clipboard > Paste).
  • Hộp thoại Paste Preview > OK. Bảng được nạp vào Power Pivot.

Bước 02: Tạo phân cấp trong Diagram View

  • Trong Power Pivot: tab View > Diagram View.
  • Nhấp vào vùng trống để tạo Hierarchy mới.
  • Kéo thả các cột theo thứ tự: Country -> State -> City vào Hierarchy vừa tạo.

Bước 03: Tạo Bảng Pivot từ mô hình dữ liệu

  • Tab Home > PivotTable.
  • Hộp thoại Create PivotTable > chọn New Worksheet > OK.
  • Trong khung PivotTable Fields: kéo Hierarchy1 vào vùng Rows, kéo Sales vào Values.
  • Kết quả: Bảng Pivot hiển thị phân cấp. Nhấp dấu + bên cạnh Quốc gia -> hiện Tiểu bang và doanh số. Nhấp + bên Tiểu bang -> hiện Thành phố và doanh số.

Phần thực hành

Trong Excel Workbook đính kèm, có phần Practice Section bên phải mỗi sheet. Hãy tự thực hành.

Kết luận

Bài viết đã hướng dẫn chi tiết 2 phương pháp tạo hệ thống phân cấp đa cấp trong Excel: Data Validation (cho danh sách thả xuống phụ thuộc) và Power Pivot (cho mô hình dữ liệu phân tích). Hy vọng hữu ích. Hãy để lại bình luận nếu có thắc mắc. Truy cập ExcelDemy để học thêm.

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 phân cấp ngày trong Pivot Table Excel (các bước dễ dàng)
  • Cách sử dụng SmartArt Hierarchy trong Excel (với các bước dễ dàng)
  • Thêm phân cấp hàng trong Excel (2 phương pháp dễ dàng)