Thông thường trong khi làm việc trên Excel chúng ta thường sử dụng cột Số thứ tự để đếm thứ tự các dòng nội dung trong bảng tính. Tuy nhiên việc đánh số thứ tự theo cách thông thường sẽ có hạn chế khi chúng ta sử dụng tính năng lọc dữ liệu bằng Autofilter. Để minh họa rõ hơn điều này chúng ta cùng xem ví dụ bên dưới:

Cho bảng dữ liệu như sau, trong đó cột số thứ tự được đánh số trực tiếp:

Khi lọc nội dung trong bảng dữ liệu trên theo nhân viên Long (Sử dụng AutoFilter và lọc ở cột D theo nhân viên Long) thì kết quả như sau:

Nhìn vào cột STT chúng ta thấy STT không thay đổi khi ta thực  hiện việc  lọc. Và với STT như vậy thì việc theo dõi kết quả lọc của chúng ta sẽ gặp nhiều khó khăn, do cột STT không còn thể hiện đúng vai trò là thứ tự các dòng nội dung phát sinh.

Để có thể giữ đúng bản chất của cột STT khi sử dụng AutoFilter, chúng ta sẽ áp dụng hàm SUBTOTAL trong cột số thứ tự.

Xem nhanh

Toggle

1. Đặc điểm của hàm SUBTOTAL

Hàm SUBTOTAL là hàm có thể sử dụng cho nhiều loại công thức: cộng, trung bình, đếm… mà chỉ tính với các giá trị không bị ẩn (bởi việc  Hide Row, Hide Column, Lọc bỏ bởi không phù hợp kết quả trong Filter…)

Do đó chúng ta có thể tận dụng tính năng của hàm Subtotal để xây dựng công thức đánh số thứ tự.

Cấu trúc hàm SUBTOTAL(Function_num, ref1, [ref2]…)

Trong  đó

• Function_num là số thể hiện thuật toán (tính tổng, đếm, tính trung  bình…) (bắt buộc)
• ref1 là vùng tham chiếu thứ 1 (bắt buộc)
• ref2 là 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.

2. Công thức đánh số thứ tự

Nguyên tắc của việc đánh số thứ tự 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 COUNT

Trong hàm Subtotal cũng hỗ trợ việc đếm: sử dụng function_num là 3

Vùng tham chiếu có thể chọn là vùng luôn chắc chắn có nội dung (để tránh việc trên cùng 1 dòng mà đếm ở vùng không có nội dung thì dòng đó sẽ không được tính). Ví dụ ở đây chúng ta chọn với cột D – cột Nhân viên. Đếm bắt đầu từ dòng thứ 2 nên sẽ bắt đầu với ô D2. Khi đếm các dòng tiếp theo thì sẽ đếm cả những ô trước đó:

• Số thứ tự tại dòng 2 = COUNT(D2:D2)
• Số thứ tự tại dòng 3 = COUNT(D2:D3)
• Số thứ tự tại dòng 4 = COUNT(D2:D4)

Do đó D2 luôn xuất hiện trong các công thức => Cố định điểm D2

Chúng ta có công thức đếm số thứ tự tại ô A2 như sau:

A2=SUBTOTALL(3, $D$2:D2)

=> Copy (filldown) công thức tại A2 xuống các dòng tiếp theo ở cột A

3. Đánh giá kết quả

Kết quả sau khi sử dụng công thức Subtotal ở cột STT và sử dụng Autofilter với Nhân viên Long như sau:

Như vậy số thứ tự đã được thay đổi 1 cách tự động dựa theo kết quả lọc.

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học này.