Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Hà Vân
Hà Vân
Nov 25 2022

Hàm SUBTOTAL trong Excel là một hàm khá đa năng, có nhiều ứng dụng cho việc làm báo cáo. Hãy cùng chúng mình tìm hiểu cách sử dụng hàm này để xử lý dữ liệu cho báo cáo Excel chuyên nghiệp nhé.

Hàm SUBTOTAL trong Excel là gì?

Định nghĩa và công thức hàm SUBTOTAL

Hàm SUBTOTAL là hàm có tác dụng tự động thay đổi kết quả của phương pháp tính toán, thống kê thông thường (SUM, COUNTA, AVERAGE,…) khi kết hợp việc lọc dữ liệu hoặc ẩn dòng.

Cú pháp của hàm SUBTOTAL có dạng như sau:

=SUBTOTAL(function_num,ref1,[ref2],...)

Trong đó bao gồm các thành phần là:

  • function_num: Tham số đại diện cho phép tính
  • ref1: Vùng tham chiếu thứ 1 cần tính toán
  • ref2: Vùng tham chiếu thứ 2 cần tính toán (không bắt buộc)

Các bạn có thể tham khảo bảng sau để mỗi function_num có thể thay thế cho hàm nào:

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Với function_num từ 1 tới 11: Hàm chỉ bỏ qua các giá trị bị ẩn khi sử dụng bộ lọc.

Với function_num từ 101 tới 111: Hàm chỉ bỏ qua các giá trị bị ẩn khi sử dụng bộ lọc hoặc ẩn dòng trong Excel bằng công cụ Hide.

Kết quả hàm SUBTOTAL

Ham SUBTOTAL sẽ trả về kết quả như sau:

  • Kết quả tương tự như các hàm chức năng được sử dụng (thông qua function_num) nhưng thêm đặc trưng của chức năng đó (như bỏ qua giá trị bị ẩn khi sử dụng bộ lọc).
  • Hàm bỏ qua các giá trị tạo bởi hàm SUBTOTAL có bên trong vùng tham chiếu (không tính 2 lần).

Các bạn có thể tham khảo bảng tính dưới đây để hiểu rõ hơn cách hoạt động của hàm SUBTOTAL:

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Trong hình ảnh trên các bạn có thể thấy kết quả tính tổng ở dòng cuối là kết quả của 2 dòng: Tổng bộ phận kinh doanh + Tổng bộ phận kế toán. Mặc dù trong cùng một cột nhưng những giá trị khác không bị cộng thêm vào tạo thành kết quả tính 2 lần như khi dùng hàm SUM.

Giả sử khi chúng mình ẩn đi 2 dòng trong bảng tính thì giá trị thuộc các dòng đó sẽ không được tính vào tổng nữa. Điều này sẽ mang lại nhiều tiện lợi cho bạn trong lúc trình bày báo cáo trên Excel..

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Xem thêm: Hướng dẫn đánh số thứ tự tự động trong Excel với hàm SUBTOTAL

Bài tập sử dụng hàm SUBTOTAL trong báo cáo

Bây giờ chúng ta hãy cùng thử sức với một bài tập về hàm SUBTOTAL để các bạn thấy được ứng dụng mạnh mẽ của nó khi dùng trong báo cáo Excel nhé.

Cho bảng dữ liệu bán hàng như sau:

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Yêu cầu đề bài: Dùng hàm SUBTOTAL để xác định kết quả cho:

  1. Dòng tổng cộng (vùng D23:E23) theo chức năng hàm SUM.
  2. Cột STT (số thứ tự - vùng A3:A22) theo chức năng của hàm COUNTA.

Cách thực hiện:

Để thực hiện yêu cầu 1, chúng ta sẽ tính tổng bằng công thức như sau:

=SUBTOTAL(9, D3:D22)

Trong lúc thao tác thực tế thì các bạn chỉ cần gõ SUBTOTAL là sẽ thấy thông tin của hàm hiện lên để lựa chọn dễ dàng mà không cần nhớ function_num.

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Kết quả chúng ta thu được với cột số lượng như sau:

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Tương tự như vậy, với cột Số tiền thì chúng ta dùng công thức như sau:

=SUBTOTAL(9, E3:E22)

Kết quả mà chúng ta thu được là:

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Vậy là đã làm xong yêu cầu tính tổng, bây giờ chúng ta sẽ đánh số thứ tự bằng hàm SUBTOTAL.

Tại ô A3, các bạn nhập công thức như sau:

=SUBTOTAL(3, $BS3:B3)

Lưu ý: Phần ref1 các bạn cần chọn ô trong một cột có dữ liệu ở tất cả các hàng, nếu bạn chọn ô có dòng trống thì kết quả ra sẽ không chính xác. 

Trong công thức trên các bạn có thấy chúng mình đang cố định công thức ở điểm đầu là vì để khi sao chép công thức thì điểm đầu sẽ cố định, điểm cuối thay đổi thì số thứ tự sẽ tăng dần.

Kết quả của thao tác này như sau:

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Bây giờ chúng mình sẽ thêm bộ lọc Filter vào bảng tính để các bạn hình dung rõ hơn chức năng của hàm SUBTOTAL.

Giả sử, chúng mình chỉ lọc ra các dòng dữ liệu liên quan đến Chi nhánh B thì hàm SUBTOTAL sẽ hiển thị kết quả chỉ theo chi nhánh B, không liên quan đến chi nhánh khác. Đồng thời số thứ tự cũng được hiển thị lại từ 1 đến 5, không bị ảnh hưởng bởi STT ban đầu.

Ứng dụng hàm SUBTOTAL để lập báo cáo Excel chuyên nghiệp

Xem thêm: Hướng dẫn cách đánh số thứ tự trong Excel nhanh-gọn-nhẹ

Kết luận

Qua bài chia sẻ của chúng mình, chắc hẳn các bạn đã hiểu rõ hơn về cách ứng dụng hàm SUBTOTAL trong công việc.

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội