Giải thích và ứng dụng hàm SUBTOTAL trong Excel dễ dàng

/ tinhocdaiduong / 14/10/2023

Hàm SUBTOTAL là một hàm rất hữu ích trong Excel, cho phép bạn tính toán và thống kê các con số dựa trên các tiêu chí nhất định. Trong bài viết này, chúng ta sẽ tìm hiểu kỹ hơn về cách sử dụng hàm SUBTOTAL và ứng dụng của nó trong Excel nhé!

Nội dung bài viết ẩn

Giới thiệu chung về hàm SUBTOTAL

Cách sử dụng các mã số trong hàm SUBTOTAL

Ví dụ về cách sử dụng hàm SUBTOTAL trong Excel

Một số lưu ý khi sử dụng hàm SUBTOTAL trong Excel

Kết luận

Giới thiệu chung về hàm SUBTOTAL

Hàm SUBTOTAL có cú pháp như sau:

=SUBTOTAL(number, range)

Trong đó:

number: Mã số chỉ định loại tính toán và cách lọc dữ liệu. Mã số này có thể là từ 1 đến 11.
range: Dãy ô chứa giá trị cần tính toán.

Ưu điểm của hàm SUBTOTAL so với các hàm tổng hợp khác như SUM là nó cho phép loại trừ các ô có chứa công thức từ kết quả tính toán. Điều này rất hữu ích khi bạn muốn tính toán một nhóm các ô nhưng không muốn kết quả bị ảnh hưởng bởi các công thức trung gian.

Ví dụ: Nếu bạn có một bảng dữ liệu với các ô chứa công thức tính tổng cộng, tổng phụ, và muốn tính tổng tất cả các giá trị đó lại, bạn có thể sử dụng hàm SUBTOTAL và chỉ định loại mã số phù hợp để loại trừ các công thức tổng cộng, tổng phụ.

Như vậy, hàm SUBTOTAL giúp bạn:

Tính toán các giá trị dựa trên các tiêu chí lọc mà bạn chỉ định
Loại trừ các ô chứa công thức khỏi kết quả tính toán
Tổng hợp dữ liệu một cách linh hoạt và chính xác hơn

Cách sử dụng các mã số trong hàm SUBTOTAL

Để sử dụng đúng hàm SUBTOTAL, bạn cần hiểu ý nghĩa của các mã số có thể đi kèm với hàm này. Các mã số phổ biến nhất bao gồm:

1 – Tính tổng các giá trị (tương đương hàm SUM)
2 – Tính trung bình cộng các giá trị (tương đương hàm AVERAGE)
3 – Tính số lượng giá trị (tương đương hàm COUNT)
4 – Tìm giá trị lớn nhất (tương đương hàm MAX)
5 – Tìm giá trị nhỏ nhất (tương đương hàm MIN)
6 – Tính tích các giá trị (tương đương hàm PRODUCT)
7 – Tính trung vị của các giá trị (tương đương hàm MEDIAN)
101 – Tính tổng các giá trị, loại trừ các dòng bị ẩn
102 – Tính trung bình cộng các giá trị, loại trừ các dòng bị ẩn
103 – Tính số lượng giá trị, loại trừ các dòng bị ẩn
104-111 – Tương tự như 1-9 nhưng loại trừ cả các dòng và cột bị ẩn

Như vậy, tùy vào mục đích sử dụng, bạn có thể lựa chọn mã số phù hợp để thực hiện phép tính mong muốn và loại bỏ các giá trị không cần thiết.

Ví dụ về cách sử dụng hàm SUBTOTAL trong Excel

Giả sử bạn có bảng dữ liệu sau và muốn tính tổng số lượng / số lượng nhỏ nhất / số lượng lớn nhất theo các tiêu chí riêng biệt.

Ở cột bên trái, chúng ta sẽ sử dụng các hàm thông thường như SUM, MIN, MAX

=SUM (D3:D11)

=MIN (D3:D11)

=MAX (D3:D11)

Ở cột bên phải, chúng ta sẽ sử dụng hàm SUBTOTAL

=SUBTOTAL (9, D3:D11)

=SUBTOTAL (5, D3:D11)

=SUBTOTAL (4, D3:D11)

Khi bảng dữ liệu giữ nguyên, thì việc sử dụng các hàm SUM / MIN / MAX với sử dụng hàm SUBTOTAL sẽ cho ra các kết quả giống hệt nhau

Tuy nhiên khi chúng ta cần lọc dữ liệu từ bảng dữ liệu gốc, ví dụ cần lọc số lượng Item của kho Nhà Bè

Kết quả: Công thức của hàm SUBTOTAL sẽ cho ra kết quả tính thay đổi dựa trên dữ liệu sau khi lọc, nhưng kết quả tính bằng hàm SUM / MIN / MAX vẫn giữ nguyên

Tương tự, bạn có thể sử dụng hàm SUBTOTAL với các mã số khác để thực hiện các phép tính và lọc dữ liệu phù hợp với nhu cầu phân tích của mình.

Một số lưu ý khi sử dụng hàm SUBTOTAL trong Excel

Hàm SUBTOTAL chỉ hoạt động khi các dòng hoặc cột được ẩn bằng cách sử dụng menu Outline của Excel. Nó không loại trừ các dòng/cột bị ẩn theo cách thủ công.
Khi thay đổi trạng thái ẩn/hiện của các dòng/cột, bạn cần cập nhật lại các công thức SUBTOTAL để đảm bảo chúng tính toán chính xác.
Nếu ô chứa công thức SUBTOTAL cũng bị ẩn, kết quả sẽ hiển thị là 0.
Các mã số 101-111 chỉ hoạt động trên Excel 2007 trở lên. Trong các phiên bản cũ hơn, bạn chỉ có thể sử dụng các mã 1-11.
Hàm SUBTOTAL không thể được dùng để tham chiếu đến chính nó. Ví dụ công thức =SUBTOTAL(1,A1:C3) trong vùng A1:C3 sẽ báo lỗi.

Như vậy, với những lưu ý trên, bạn có thể sử dụng hàm SUBTOTAL một cách hiệu quả để tổng hợp, tính toán và phân tích dữ liệu trong Excel.

Kết luận

Hàm SUBTOTAL là công cụ hữu ích giúp tổng hợp, tính toán dữ liệu một cách chính xác và linh hoạt trong Excel. Hi vọng bài viết đã giúp bạn hiểu rõ hơn về cách sử dụng cũng như ứng dụng thực tế của hàm này và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé! Chúc bạn thành công!

Ngoài ra bạn cũng có thể tham khảo thêm các kiến thức tin học khác tại mục Kiến thức của Tin Học Đại Dương.

> Bài viết liên quan: Nên học tin học văn phòng ở đâu tốt TPHCM 2023

⏩Tham khảo Khoá học Tin học từ cơ bản đến nâng cao tại đây. Inbox Tin Học Đại Dương để được tư vấn và đăng ký học ngay trong tháng này để nhận ưu đãi lên đến 40%!

—————————————————

Tin Học Đại Dương