Tự động điền số thứ tự theo cấp bậc trong Excel

Chào mọi người,

Trong quá trình làm việc với dữ liệu có cấu trúc phân cấp (ví dụ: danh sách phòng ban, bộ phận), mình thường gặp khó khăn trong việc điền số thứ tự theo từng cấp bậc. Nếu chỉ dùng hàm SUBTOTAL thông thường thì số thứ tự sẽ liên tục, không thể hiện được sự phân cấp. Hôm nay, mình xin chia sẻ một cách làm khá hay ho để giải quyết vấn đề này, hy vọng sẽ hữu ích cho các bạn.

Cách này kết hợp hàm SUBTOTAL và một chút mẹo nhỏ để tạo ra số thứ tự theo từng nhóm.

Giả sử dữ liệu của bạn có 2 cột: Cột A là cấp bậc (ví dụ: "Phòng Ban", "Bộ Phận") và Cột B là tên nhân viên. Bạn muốn điền số thứ tự theo Phòng Ban.

Tại ô C2 (giả sử bạn bắt đầu điền số thứ tự từ đây), bạn nhập công thức sau:

=IF(A2=A1,SUBTOTAL(3,OFFSET(C$1,ROW(C2)-ROW(C$1),0)),1)

Sau đó, kéo công thức xuống cho các dòng còn lại.

Giải thích công thức:

• IF(A2=A1, ..., 1): So sánh cấp bậc ở dòng hiện tại (A2) với dòng ngay phía trên (A1). Nếu giống nhau, tức là vẫn đang trong cùng một nhóm, thì thực hiện phần tính toán tiếp theo. Nếu khác nhau (hoặc là dòng đầu tiên), thì đặt lại số thứ tự là 1.
• SUBTOTAL(3,OFFSET(C$1,ROW(C2)-ROW(C$1),0)): Phần này hơi phức tạp một chút.
• OFFSET(C$1,ROW(C2)-ROW(C$1),0): Tạo ra một vùng tham chiếu động bắt đầu từ C$1 đến dòng hiện tại.
• SUBTOTAL(3, ...): Hàm SUBTOTAL với đối số thứ nhất là 3 (tương đương hàm COUNTA) sẽ đếm số lượng các ô không rỗng trong vùng tham chiếu động được tạo bởi OFFSET. Điều này giúp tạo ra số thứ tự tăng dần trong mỗi nhóm.

Cách này sẽ giúp bạn có được số thứ tự theo từng cấp bậc một cách tự động và dễ dàng hơn rất nhiều.

Chúc các bạn thành công!

Hay quá bạn ơi! Mình cũng hay gặp tình huống này, nhất là khi làm báo cáo tổ chức nhân sự. Cách bạn chia sẻ có vẻ rất thiết thực.

Cho mình hỏi thêm chút là nếu trong dữ liệu có những dòng trống hoặc không thuộc cấp bậc nào thì hàm xử lý thế nào vậy bạn?

Tuyệt vời! Mình cũng hay phải làm báo cáo với cấu trúc phân cấp và luôn tìm cách để số thứ tự hiển thị rõ ràng hơn.

Cách bạn dùng SUBTOTAL kết hợp mẹo nhỏ này rất sáng tạo. Mình có thắc mắc nhỏ là nếu mình muốn đánh số thứ tự theo 3 cấp hoặc nhiều hơn thì có thể áp dụng tương tự được không ạ? Hay có cách nào khác để mở rộng cho nhiều cấp hơn không? Cảm ơn bạn đã chia sẻ!