Cách đếm số thứ tự sau khi lọc bằng Autofilter sử dụng hàm Subtotal

Khi sử dụng Excel, chúng ta thường thêm cột Số thứ tự để theo dõi thứ tự các hàng. Tuy nhiên, phương pháp thông thường này gặp hạn chế khi chúng ta áp dụng tính năng lọc dữ liệu Autofilter. Hãy cùng nhau xem xét một ví dụ để hiểu rõ hơn:

Dưới đây là một bảng dữ liệu có cột số thứ tự được nhập một cách thủ công:

Sau đó, khi ta lọc theo nhân viên tên Long sử dụng Autofilter tại cột D, kết quả thu được như sau:

Rõ ràng là số thứ tự trong cột STT không hề thay đổi sau khi lọc, khiến việc theo dõi trở nên khó khăn do không còn phản ánh đúng thứ tự xuất hiện của dữ liệu.

Để giải quyết vấn đề này và giữ cho cột STT phản ánh đúng khi sử dụng AutoFilter, chúng ta nên sử dụng hàm SUBTOTAL.

1. Khái niệm về hàm SUBTOTAL

Hàm SUBTOTAL có thể áp dụng cho nhiều hàm khác nhau như cộng, trung bình, đếm, và chỉ xét đến những giá trị hiển thị (không bị ẩn bởi tính năng Hide Row, Hide Column, hay do lọc dữ liệu).

Vì thế, chúng ta sẽ tận dụng hàm Subtotal để tạo công thức đánh số thứ tự theo ý muốn.

Công thức cơ bản của hàm SUBTOTAL có dạng (Function_num, ref1, [ref2],…)

Trong đó:

  • Function_num là mã số biểu diễn phép toán cần thực hiện, như tổng, đếm, hay trung bình (bắt buộc)
  • ref1 là tham chiếu ô dữ liệu đầu tiên (bắt buộc)
  • ref2 là tham chí dữ liệu thứ hai (nếu có, không bắt buộc) và có thể bao gồm nhiều tham chiếu khác.

2. Xây dựng công thức đánh số thứ tự

Nguyên tắc khi đánh số thứ tự dựa trên việc đếm hàng chứa dữ liệu. Mỗi hàng có dữ liệu sẽ được tính là một đơn vị, và hàng tiếp theo chứa dữ liệu sẽ có số thứ tự là kết quả của hàng trước cộng thêm một.

Nói đến việc đếm trong Excel, chúng ta thường nghĩ tới hàm COUNT.

Hàm Subtotal hỗ trợ tính đếm với function_num là 3.

Chúng ta chọn vùng tham chiếu luôn chắc chắn có dữ liệu (như cột D – cột Nhân viên trong ví dụ) để tránh trường hợp đếm sai. Đếm bắt đầu từ hàng 2, vì vậy ta sẽ bắt đầu ở ô D2 và tiếp tục đếm những ô phía trước đó:

  • Số thứ tự ở hàng 2 = COUNT(D2:D2)
  • Số thứ tự ở hàng 3 = COUNT(D2:D3)
  • Số thứ tự ở hàng 4 = COUNT(D2:D4)

Do đó, D2 cần được cố định trong công thức.

Vậy công thức đánh số thứ tự tại ô A2 sẽ như sau:

=> Ta chỉ việc sao chép công thức này từ A2 xuống các hàng dưới trong cột A.

3. Đối chiếu kết quả

Sau khi áp dụng công thức Subtotal vào cột STT và lọc theo Nhân viên Long, kết quả thu được như sau:

Qua đó, ta thấy số thứ tự đã được cập nhật tự động theo kết quả lọc.

Thật là nhiều kiến thức bổ ích phải không nào? Tất cả những thông tin này và nhiều hơn nữa bạn có thể tìm hiểu và học hỏi thông qua khóa học SM90 – Excel từ cơ bản đến chuyên gia do Siêu Marketing cung cấp. Khóa học này cung cấp cho bạn một hệ thống tri thức toàn diện, được trình bày chi tiết, và điều tốt nhất là bạn không hề bị giới hạn thời gian học, cho phép bạn linh hoạt nắm bắt kiến thức mọi lúc mọi nơi. Hiện tại hệ thống đang triển khai chương trình giảm giá lớn cho những ai đăng ký khóa học này.

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop