Bạn cần thống kê doanh số theo nhân viên, số lượng sản phẩm theo khu vực, hay nhiệt độ trung bình theo tháng? Hàm SUBTOTAL trong Excel sẽ giúp bạn thực hiện điều này một cách dễ dàng. Bài viết này sẽ hướng dẫn chi tiết cách sử dụng hàm SUBTOTAL để thống kê dữ liệu theo điều kiện, kèm ví dụ minh họa cụ thể.
Trong ví dụ này, chúng ta sẽ sử dụng bảng thống kê nhiệt độ và độ ẩm tại Hà Nội trong 3 tháng đầu năm 2017.
Bảng dữ liệu nhiệt độ và độ ẩm
Mục tiêu của chúng ta là tính toán nhiệt độ và độ ẩm trung bình của từng tháng bằng hàm SUBTOTAL.
Tìm Hiểu Về Hàm SUBTOTAL
Hàm SUBTOTAL nằm trong thẻ Data, nhóm Outline. Bạn cần chọn vùng dữ liệu trước khi sử dụng hàm này.
Vị trí hàm SUBTOTAL
Sử dụng hàm SUBTOTAL
Chuẩn Bị Dữ Liệu cho Hàm 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 bảng dữ liệu lớn, việc tính toán thủ công sẽ rất mất thời gian. Hàm SUBTOTAL sẽ giúp bạn tự động tính toán giá trị trung bình theo từng tháng.
Bước 1: Chọn toàn bộ bảng dữ liệu. Nếu bạn chưa biết cách, chỉ cần chọn một ô bất kỳ trong bảng, sau đó chọn Subtotal, Excel sẽ tự động chọn toàn bộ bảng dữ liệu.
Bước 2: Chọn Subtotal. Hộp thoại Subtotal sẽ xuất hiện:
Hộp thoại Subtotal
Giải thích các thành phần trong hộp thoại:
At each change in: Điều kiện để tính toán. Ví dụ: Khi chuyển từ tháng 1 sang tháng 2, Excel sẽ thực hiện tính toán. Nói cách khác, At each change in trả lời cho câu hỏi: Thống kê theo tiêu chí nào?
Use function: Hàm được sử dụng để tính toán. Ví dụ: Hàm SUM tính tổng, hàm AVERAGE tính trung bình. Use function trả lời cho câu hỏi: Tính toán bằng hàm gì?
Add subtotal to: Cột dữ liệu được áp dụng hàm. Add subtotal to trả lời cho câu hỏi: Thống kê dữ liệu của cột nào?
Các tùy chọn khác:
- Replace current subtotal: Thay thế kết quả SUBTOTAL hiện tại bằng kết quả mới.
- Page break between groups: Ngắt trang giữa các nhóm dữ liệu.
- Summary below data: Hiển thị kết quả bên dưới dữ liệu. Nếu bỏ chọn, kết quả sẽ hiển thị bên trên.
Áp Dụng Hàm SUBTOTAL
Trong ví dụ này, chúng ta chọn Tháng cho At each change in, AVERAGE cho Use function, và chọn các cột Nhiệt độ ngày, Nhiệt độ đêm, Độ ẩm cho Add subtotal to.
Tuy nhiên, vì cột “Thời gian” chưa được phân chia theo tháng, kết quả sẽ chưa chính xác:
Kết quả chưa chính xác
Để khắc phục, chúng ta cần thêm cột Tháng.
- Nhấn Ctrl + Z để hoàn tác.
- Chèn một cột mới bên cạnh cột Thời gian và đặt tên là Tháng.
- Nhập công thức
=MONTH(A4)vào ô đầu tiên của cột Tháng (giả sử dữ liệu bắt đầu từ ô A4). - Kéo công thức xuống để áp dụng cho toàn bộ dữ liệu.
- Định dạng cột Tháng thành kiểu General.
Cột Tháng
Bây giờ, áp dụng lại hàm SUBTOTAL với At each change in là Tháng. Kết quả sẽ như sau:
Kết quả chính xác
Kết Luận
Hàm SUBTOTAL là một công cụ mạnh mẽ giúp thống kê dữ liệu theo điều kiện một cách nhanh chóng và hiệu quả. Hy vọng bài viết này đã giúp bạn hiểu rõ cách sử dụng hàm SUBTOTAL trong Excel. Hãy khám phá thêm các tính năng khác của Excel để tối ưu hóa công việc của bạn.
Discussion about this post