Đôi khi ta cần thể hiện doanh số bán hàng theo từng nhân viên, hoặc số lượng thực phẩm theo vùng miền, hay nhiệt độ trung bình của từng tháng… Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn làm điều trên thông qua chức năng SUBTOTAL thống kê dữ liệu theo điều kiện.

Chức năng SUBTOTAL thống kê dữ liệu theo điều kiện

Trong ví dụ lần này, ta sẽ mô phỏng đơn giản nhất bảng thống kê nhiệt độ và độ ẩm của Hà Nội 3 tháng đầu năm 2017.

Công việc của ta bây giờ là thống kê nhiệt độ và độ ẩm trung bình từng tháng. Và ta sẽ thực hiện bằng công cụ SUBTOTAL.

Công cụ SUBTOTAL

Subtotal nằm ở thẻ Data, nhóm Outline. Bạn cần chọn bảng dữ liệu trước khi click vào chức năng này.

Bây giờ, hãy quay trở lại với bảng tính của chúng ta nhé

Thêm các đối tượng thích hợp để sử dụng công cụ SUBTOTAL

[iframe width=”523″ height=”900″ frameborder=”0″ scrolling=”no” src=”https://onedrive.live.com/embed?resid=B21F9331F704AFCF%21119&authkey=%21AA5epCWh2HsqEVM&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’Task’!A1%3AD130&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True”]

Với một bảng dài ngoằng như này, hẳn bạn sẽ không muốn thêm những dòng tính trung bình vào dưới mỗi khi kết thúc tháng đâu nhỉ? Hãy đến với SUBTOTAL.

Chức năng SUBTOTAL sẽ giúp bạn tính được kết quả trung bình theo từng tháng một cách nhanh chóng.

Bước 1: Chọn toàn bộ bảng tính (trong trường hợp bạn chưa biết, chỉ cần chọn một ô bất kì trong bảng tính rồi chọn Subtotal, Excel sẽ tự tính toán và chọn tất cả những ô còn lại).

Bước 2: Chọn Subtotal. Một hộp thoại sẽ hiện ra:

Hãy giải thích về từng phần trong này nhé:

1. At each change in: Đối với mỗi sự thay đổi của đối tượng được chọn, Excel sẽ trả về kết quả. VD: Hết tháng 1 đến tháng 2 -> đã có sự thay đổi -> tính toán. Nói cách khác, At each change in trả lời cho câu hỏi: Điều kiện gì?
2. Use function: Hàm được sử dụng để tính. Với hàm SUM, Excel sẽ trả về tổng, hàm AVERAGE trả về trung bình,… trả lời cho câu hỏi: Phương thức thống kê?
3. Add subtotal to: Mục được áp dụng hàm ở trên. Chọn các cột cần thiết và Excel sẽ tính toán giá trị ở đó. Trả lời câu hỏi: Thống kê dữ liệu gì?

Ngoài ra ta có:

• Replace current subtotal: Nếu đã tồn tại trước đó một subtotal, nó sẽ thay thế bằng subtotal mới.
• Page break between groups: Bạn biết page break (ngắt trang) chứ? Tùy chọn này giúp cho mỗi nhóm của at each change in được nằm trong một trang khác nhau.
• Summary below data: Đưa function xuống dưới các nhóm. Nếu bỏ chọn ô này, kết quả sẽ không mất, mà đưa lên đầu.

Ok, ấn đi các bạn!

At each change in thời gian, use function AVERAGE, Add subtotal to Nhiệt độ ngày, Nhiệt độ đêm, Độ ẩm.

Mình biết điều đang xảy ra với trang tính của các bạn.

Bởi vì ta chưa tìm ra được đối tượng chính xác của at each change in. Thứ ta cần là tháng, chứ không phải thời gian cụ thể.

Để khắc phục, đầu tiên là Undo đi nào (Ctrl+Z).

Sau đó, click vào cột B, chọn Insert để thêm cột mới nằm giữa Thời gian và Nhiệt độ ngày. Đặt tên là Tháng.

Tại cột Tháng, nhập công thức vào như sau:

=MONTH(A4)

Sau đó định dạng lại về General nếu kết quả đang ở dạng Date.

Sử dụng autofill áp dụng kết quả cho các ô còn lại. Trong trường hợp bạn chưa biết autofill, hãy xem bài viết này.

Kết quả:

Rồi, giờ bạn hãy áp dụng lại SUBTOTAL, với at each change in Tháng. Kết quả ra sao, bạn cũng đã ra rồi phải không nào?

Ngoài ra, bạn có thể tham khảo thêm một số bài viết liên quan tới SUBTOTAL và ứng dụng:

–Ứng dụng SUBTOTAL kết hợp COUNTIF thiết lập số thứ tự thành phần.

–Tổng quan về công cụ SUBTOTAL