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

216 lượt xem
Share

Làm thế nào để 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 trong Excel. Hãy đọc bài viết dưới đây do eLib biên soạn để tìm cho mình được câu trả lời chính xác nhất nhé.

Mục lục nội dung

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 đề

Thông thường bạn vẫn hiểu rằng muốn tính trung  bình của 1 số thì sẽ lấy số đó chia cho tổng số lần. Thế nhưng nếu như việc chia từng lần đó gây ra số lẻ thì sao? hoặc nếu chúng ta buộc phải làm tròn số trung bình của từng lần thì liệu việc làm tròn đó còn chính xác hay không?

Xét ví dụ sau:

Tính trung bình của một số theo từng lầ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à:

14.654.000 / 6 = 2.442.333,33333333

hẳn bạn không muốn tính lẻ tới như vậy, do đó bạn muốn làm tròn tới hàng nghìn cho dễ trả

=> Theo nguyên tắc làm tròn thì số 2.442.333,3333 sẽ làm tròn thành 2.442.000

(Hàm ROUND(Số cần làm tròn, -3)

Như vậy phần số lẻ sẽ bị thiếu, do đó làm tổng số tiền của 6 đợt sẽ còn 14.652.000 => Kết quả không đúng

Sai lệch sẽ trở nên lớn hơn nhiều với việc chia thành nhiều lần thanh toán hơn.

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

Bù trừ phần thiếu vào lần cuối

Bù trừ phần thiếu vào lần cuối

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 các lần thanh toán từ 1 đến 5 thì tính trung bình như thông thường

Tại lần thanh toán cuối (lần thứ 6) thì sẽ bằng Tổng số phải thanh toán – Tổng những lần thanh toán trước đó

D10 = D11 – SUM(D5:D9)

Như vậy với cách này toàn bộ phần sai lệch do làm tròn ở những lần trước đó sẽ được cộng vào lần cuối cùng.

Ưu điểm:

Đảm bảo kết quả cuối cùng luôn đúng

Nhược điểm:

Không đồng đều giữa các lần. Tại lần cuối có thể phát  sinh lớn hơn hẳn so với các lần trước đó.

Cách tính trung bình sau mỗi lần thanh toán

Để khắc phục nhược điểm của cách trên, chúng ta tìm hiểu về cách tính trung bình sau mỗi lần thanh toán.

Cách này được hiểu như sau:

Tính trung bình sau mỗi lần thanh toán.

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

Số tiền còn lại chưa  thanh toán = Số tiền ban đầu – Số đã thanh toán ở những lần trước đó

Số tiền ban đầu = C1

Số tiền đã thanh toán ở những lần trước đó:

  • Lần 1: Sum(E4:E4) là lần 0
  • Lần 2: Sum(E4:E5) là của lần 0 + lần 1
  • Lần 3: Sum(E4:E6) là của Lần 0 + lần 1 + lần 2
  • tương tự cho những lần tiếp theo

Khi đó ta có thể thấy ô E4 luôn xuất hiện trong công thức tính tổng => Cố định điểm E4

Số lần còn phải thanh toán = Đếm số dòng của các lần  còn lại. Sử dụng hàm ROWS để đếm cột Lần thanh toán, từ lần thực hiện đến lần 6 => khi đó ô B10 luôn xuất hiện trong các công thức ROWS => Cố định điểm B10

Để làm tròn số => Đặt  toàn bộ công thức trong hàm ROUND

Như vậy chúng ta có thể thấy kết quả đã dồn những phần sai lệch do làm tròn ngay thời điểm số sai lệch vừa đủ để thành 1 số nguyên (ở ô E7 và E9) => Kết quả chính xác hơn tại các lầ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:

Bảng số liệu

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

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

2.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:

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!

216 lượt xem
Share
Ngày:13/08/2020 Chia sẻ bởi:
TẢI VỀ XEM ONLINE

CÓ THỂ BẠN QUAN TÂM