Khi sử dụng tính năng AutoFilter, bạn sẽ gặp khó khăn khi đánh số thứ tự trong Excel. Bài viết này, Gitiho sẽ đem đến một số cách đánh số thứ tự trong Excel khi ẩn dòng hoặc lọc dữ liệu nhé.
Xem thêm: 3 cách đánh số thứ tự trong Excel cực nhanh và chuẩn
Hạn chế của tính năng AutoFilter với việc đánh số thứ tự trong Excel
Hãy xem ví dụ minh họa dưới đây để thấy được những điểm chưa thể khắc phục của AutoFilter
Giả sử bạn có bảng dữ liệu được đánh số thứ tự trực tiếp:
Khi sử dụng AutoFilter và lọc nội dung trong cột D theo điều kiện là nhân viên ”Long” thì thu được kết quả sau:
Nhìn vào hình ảnh kết quả trên, bạn có thể thấy STT được đánh số thứ tự khi filter không thay đổi khi thực hiện việc lọc dữ liệu. Nếu để nguyên STT như vậy, sẽ gây khá nhiều khó khăn trong việc theo dõi và sắp xếp thông tin sau khi lọc. Đây chính là điểm hạn chế của tính năng này.
Vậy phải làm thế nào để đánh số thứ tự khi ẩn dòng trong tình huống này? Để giữ đúng bản chất của cột STT khi sử dụng AutoFilter, cũng như để đánh số thứ tự bỏ qua dòng ẩn, chúng ta sẽ áp dụng hàm SUBTOTAL trong cột số thứ tự theo hướng dẫn dưới đây.
Xem thêm: Hướng dẫn chi tiết cách sử dụng hàm SUBTOTAL trong Excel
Cách đánh số thứ tự trong Excel khi ẩn dòng bằng hàm SUBTOTAL
Cách đánh số thứ tự khi bị ẩn dòng bằng hàm SUBTOTAL
Hàm SUBTOTAL được sử dụng để tính toán các hàm tổng hợp (summarize) cho một tập hợp các dòng dữ liệu trong một phạm vi (như tính tổng, trung bình, lớn nhất, nhỏ nhất, v.v.)
Một điểm đặc biệt của hàm SUBTOTAL là nó thường loại bỏ các giá trị ẩn (bằng cách sử dụng bộ lọc) khi tính toán. Điều này có nghĩa rằng, nếu bạn ẩn một số dòng dữ liệu trong phạm vi, hàm SUBTOTAL sẽ không tính toán chúng trong kết quả.
Hàm SUBTOTAL có công thức như sau:
=SUBTOTAL(Function_num, ref1, [ref2]…)
Trong đó,
- Function_num: Số thể hiện thuật toán (tính tổng, đếm, tính trung bình…) (bắt buộc)
- ref1: Vùng tham chiếu thứ 1 (bắt buộc)
- ref2: Vùng tham chiếu thứ 2 (nếu có, không bắt buộc) và có thể có nhiều vùng tham chiếu hơn nữa.
Công thức đánh số thứ tự khi lọc
Nguyên tắc: Cách đánh số thứ tự trong Excel khi ẩn dòng hoặc khi lọc có nguyên tắc chính là đếm những dòng có nội dung. Với mỗi dòng có nội dung phát sinh thì sẽ tính là 1 đơn vị, dòng tiếp theo có chứa nội dung thì sẽ bằng số thứ tự ở dòng trước cộng thêm 1.
Khi nhắc tới hàm đếm trong Excel, chúng ta nghĩ tới hàm COUNTA. Và chúng ta dùng hàm SUBTOTAL để hỗ trợ việc đếm bằng cách sử dụng function_num là 3.
Chọn vùng tham chiếu là một vùng luôn chứa dữ liệu (để tránh đếm dòng trống). Ví dụ, chọn cột D (cột Nhân viên) và bắt đầu đếm từ dòng thứ 2, nên bắt đầu từ ô D2. Khi đếm các dòng tiếp theo, cũng tính các ô trước đó.
Số thứ tự tại dòng 2: “=COUNTA(D2:D2)”
Số thứ tự tại dòng 3: ”=COUNTA(D2:D3)”
Số thứ tự tại dòng 4: ”=COUNTA(D2:D4)”
Khi hiểu được nguyên lý đếm số thứ tự bằng hàm trên, ta có công thức đếm số thứ tự tại ô A2 như sau:
=SUBTOTAL(3,$D$2:D2)
Lưu ý: Do D2 luôn xuất hiện trong công thức, nên bạn đọc cần cố định điểm D2.
Sau đó, Fill Down để sao chép công thức tại A2 xuống các dòng tiếp theo ở cột A.
Xem thêm: Hướng dẫn cách đánh số thứ tự ngày tháng bỏ qua các ngày cuối tuần
Kết quả hiển thị
Sau khi sử dụng cách đánh số thứ tự khi lọc dữ liệu với hàm SUBTOTAL ở cột STT và tính năng AutoFilter theo điều kiện là Nhân viên ”Long”, ta thu được kết quả như sau:
Bây giờ bạn có thể thấy cột STT đã được thay đổi tự động theo kết quả lọc.
Kết luận
Hy vọng qua bài viết này, bạn đọc đã phần nào biết được cách đánh số thứ tự trong Excel khi ẩn dòng hoặc khi lọc dữ liệu. Đừng quên tìm hiểu các hàm như hàm VLOOKUP, hàm IF, AND,… và luyện tập các bài tập Excel kèm đáp án của Gitiho nhé.
