Hướng dẫn cách tính trung bình của một số theo từng lần và sử dụng hàm Subtotal để đánh số thứ tự kết quả lọc Autofilter

1. Hướng dẫn cách tính trung bình của một số theo từng lần

1.1. Đặt vấn đề

Ví dụ chúng ta muốn thanh toán 1 khoản tiền là 14.654.000 theo 6 đợt (6 lần), vậy chúng ta sẽ tính là:

1. 2. Cách giải quyết

Quy định sẵn tổng số cần phải thanh toán luôn luôn bằng với số nợ ban đầu nên D11 = C1

Tại mỗi lần thanh toán, số phải thanh toán = Số tiền còn lại chưa thanh toán / Số lần  còn phải thanh toán

2. Hướng dẫn đánh số thứ tự kết quả lọc Autofilter với hàm Subtotal

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ự.

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.

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

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:

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

Như vậy bài viết đã hướng dẫn cho bạn  cách tính trung bình của một số theo từng lần và sử dụng hàm Subtotal để đánh số thứ tự kết quả lọc Autofilter. Chỉ cần vài thao tác đơn giản, bạn đã có thể tính trung bình của một số và đánh số thứ tự một cách nhanh chóng rồi. Chúc các bạn thực hiện thao tác thành công!