Cách Sử Dụng Hàm SUBTOTAL Trong Excel Dễ Hiểu, Có Ví Dụ Minh Họa

Trang chủ » Câu Lệnh Subtotal » Cách Sử Dụng Hàm SUBTOTAL Trong Excel Dễ Hiểu, Có Ví Dụ Minh Họa

Có thể bạn quan tâm

Hàm SUBTOTAL trong Excel có thể dùng trong việc tính tổng, đếm các ô không trống trong một vùng dữ liệu được lọc, đánh số thứ tự tự động,... Mời bạn cùng bài viết sau của Điện máy XANH tìm hiểu về công thức hàm SUBTOTAL và cách sử dụng cụ thể nhé!

  • Hướng dẫn dưới đây được thực hiện trên laptop hệ điều hành Windows, phiên bản Excel 2016. Bạn có thể thực hiện tương tự trên các phiên bản Excel 2007, 2010, 2013 và 2019.
  • Bạn có thể tải một số ví dụ về hàm SUBTOTAL trong Excel để làm quen nhé!

1Công thức hàm SUBTOTAL

Hàm SUBTOTAL trong Excel được ứng dụng trong nhiều trường hợp, cụ thể có thể kể đến là tính tổng, trung bình cộng, đếm số ô, tìm giá trị lớn nhất/nhỏ nhất của dữ liệu, đánh số thứ tự tự động,...

Công thức hàm SUBTOTAL như sau:

=SUBTOTAL(function_num, ref1, ref2,…)

Trong đó:

  • Function_num: Nếu function_num từ 1 - 11 thì hàm SUBTOTAL thực hiện tính toán bao gồm cả các giá trị ẩn trong vùng dữ liệu chứa giá trị ẩn đó. Nếu function_num được chọn từ 101 - 111 thì hàm SUBTOTAL sẽ bỏ qua, không tính các giá trị ở hàng ẩn.
  • Ref1, Ref2,…: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.

Mã số của Function_num như sau:

Function_num(bao gồm giá trị ẩn)

Function_num(bỏ qua giá trị ẩn)

Tương đương

Hàm

1

101

AVERAGE

2

102

COUNT

3

103

COUNTA

4

104

MAX

5

105

MIN

6

106

PRODUCT

7

107

STDEV

8

108

STDEVP

9

109

SUM

10

110

VAR

11

111

VARP

Lưu ý:

- Hàm SUBTOTAL được sử dụng cho các cột số liệu theo chiều dọc.

- Trường hợp ref1, ref2,… có chứa hàm SUBTOTAL thì sẽ được bỏ qua để tránh tính trùng 2 lần.

- SUBTOTAL sẽ bỏ qua phần dữ liệu bị ẩn bởi Filter (dữ liệu không thỏa mãn bộ lọc).

- Cần phân biệt rõ giá trị bị ẩn và giá trị bị loại do không thỏa mãn bộ lọc.

  • Giá trị bị ẩn là giá trị mà chính bạn đã chủ động ẩn đi bằng thao tác bạn chọn hàng > Nhấn chuột phải chọn Hide.
  • Giá trị bị loại bỏ do không thỏa mãn bộ lọc là các giá trị không khớp với yêu cầu tìm kiếm của bạn.

2 Ví dụ hàm SUBTOTAL

Dưới đây là một số cách ứng dụng hàm SUBTOTAL phổ biến trong thực tế, mời bạn cùng tham khảo nhé!

Tính tổng vùng được lọc 

Giả sử bạn có bảng sau và cần lọc ra tổng KPI của đội A. Bạn thực hiện thao tác như sau:

Bước 1: Bôi đen bảng chứa dữ liệu cần tính > Vào Home > Sort and Filter > Filter.

Bôi đen bảng chứa dữ liệu cần tính Vào Home Sort and Filter Filter

Bước 2: Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột "Đội", tick chọn "A" và nhấn OK.

Lọc dữ liệu theo đội

Bước 3: Bảng dữ liệu của đội A được Excel lọc ra. Lúc này, tại ô cần lấy kết quả bạn nhập:

=SUBTOTAL(9,D2:D11)

Giải thích công thức: 

  • function_num = 9 là giá trị đối số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này hiển thị khi nhập công thức hàm SUBTOTAL. Ở đây do chúng ta cần tính tổng nên sẽ chọn hàm SUM - tương ứng với số 9.
  • ref1 = D2:D11 là phạm vi cần tính tổng.

Bạn sẽ thu được kết quả như sau:

Kết quả thu được

Lưu ý: Bạn có thể dùng giá trị đối số là 9 (tính tổng các giá trị đã bị ẩn) hoặc 109 (bỏ qua các giá trị đã bị ẩn) để tính tổng các hàng đã lọc.

Đếm các ô được lọc không trống

Giả sử bạn có bảng dữ liệu như minh họa bên dưới. Nhiệm vụ của bạn là đếm số lượng sinh viên của nhóm C đã có link bài tập để tiện thống kê.

Đầu tiên, bạn tiến hành lọc ra các sinh viên thuộc nhóm C bằng thao tác lọc tương tự như hướng dẫn ở mục trước. 

Sau khi lọc dữ liệu xong, tại ô cần lấy kết quả, bạn nhập:

=SUBTOTAL(3,D4:D14)

Giải thích công thức: 

  • function_num = 3 là giá trị đối số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này hiển thị khi nhập công thức hàm SUBTOTAL. Ở đây do chúng ta cần đếm các ô không trống nên sẽ chọn hàm COUNTA - tương ứng với số 3.
  • ref1 = D4:D14 là phạm vi cần tính tổng.

Kết quả thu được sẽ như sau:

Hàm SUBTOTAL được dùng trong việc đếm các ô không trống sau khi lọc

Lưu ý: Bạn có thể dùng giá trị đối số là 3 (đếm cả các giá trị đã bị ẩn thủ công do tính năng Hide) hoặc 103 (bỏ qua, không đếm các giá trị đã bị ẩn thủ công) để tính đếm các ô không trống trong các hàng đã lọc.

Bạn có thể xem cách hoạt động của hàm COUNTA và một số ví dụ TẠI ĐÂY.

Đánh số thứ tự nhanh

Nếu chỉ cần đánh số thứ tự cho một danh sách dữ liệu liên tiếp (không chứa các hàng trống) như hình dưới thì bạn có thể nhập công thức đơn giản vào ô A2 là:

=SUBTOTAL(3,$B$2:B2)

Giải thích công thức: 

  • function_num là 3: Ứng với cách tính là COUNTA - đếm ô không trống.
  • ref1 là $B$2:B2: Đếm ký tự phát sinh trong khoảng $B$2:B2, trong đó ô B2 được cố định bằng dấu"$" ở trước địa chỉ hàng và ô. Nếu sao chép công thức xuống dưới, giá trị sẽ thay đổi theo kiểu: $B$2:B3, $B$2:B4, $B$2:B5,... và hàm sẽ đếm đúng, mang về kết quả như mong muốn.

Sau đó, bạn kéo sao chép công thức (bằng cách chọn vào ô chứa công thức cần sao chép > di chuột đến góc dưới, bên phải ô và kéo thả chuột dấu cộng vừa hiển thị đến hết ô cuối cùng cần điền). Kết quả thu được như sau:

Dùng SUBTOTAL để đánh số thứ tự tự động

Tuy nhiên, nếu gặp phải một dữ liệu "khó nhằn" với các hàng trống và không trống lộn xộn, bạn sẽ cần kết hợp SUBTOTAL với hàm IF. Số thứ tự của giá trị được chọn sẽ được đánh lại từ đầu khi thực hiện lọc dữ liệu và cách này chỉ áp dụng được khi đánh thứ tự theo cột.

Cú pháp tổng quát:

=IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,...))

Xem chi tiết: Cách dùng hàm SUBTOTAL và hàm IF đánh số thứ tự tự động trong Excel

Ví dụ, bạn có bảng dữ liệu như hình dưới.

Bảng dữ liệu với các ô trống, không trống xen kẽ

Tại ô A2 bạn nhập công thức:

=IF(B2="","",SUBTOTAL(3,$B$2:B2))

Suy luận: Hàm chính cần dùng sẽ là IF. Nếu như có một ô rỗng thì bỏ qua không đếm. Nếu ô không rỗng thì dùng SUBTOTAL để đánh số thứ tự.

Công thức của hàm IF là: =IF(logical_test, value_if_true, value_if_false), với:

  • logical_test: Điều kiện của hàm IF.
  • value_if_true: Giá trị trả về nếu điều kiện đúng.
  • value_if_false: Giá trị trả về nếu điều kiện sai. Ở đây chúng ta dùng hàm SUBTOTAL.

Các đối số cần điền trong hàm SUBTOTAL là:

  • function_num = 3: Ứng với cách tính là COUNTA - đếm ô không trống.
  • ref1 = $B$2:B2: Đếm ký tự phát sinh trong khoảng $B$2:B2, trong đó ô B2 được cố định bằng dấu"$" ở trước địa chỉ hàng và ô. Nếu sao chép công thức xuống dưới, giá trị sẽ thay đổi theo kiểu: $B$2:B3, $B$2:B4, $B$2:B5,... và hàm sẽ đếm đúng, mang về kết quả như mong muốn.

Sau khi nhập công thức, bạn nhấn Enter rồi tiến hành sao chép công thức từ ô A2 đến các ô còn lại. Kết quả ta được bảng sau:

Tại ô A2 nhập công thức hàm SUBTOTAL lồng với IF

3 Cách fix lỗi #VALUE! hàm SUBTOTAL

Nguyên nhân lỗi:

- Số xác định chức năng function_num không nằm trong khoảng 1 - 11 hoặc 101 - 111.

- Tham chiếu ref tham chiếu đến các ô trong một trang tính (sheet khác), ví dụ bạn đang dùng SUBTOTAL ở sheet STT2 nhưng tham chiếu đến một ô, một dãy ô nào đó ở sheet STT1.

Lỗi #VALUE! do tham chiếu đến trang tính khác

Cách fix:

Đối với nguyên nhân đầu tiên do sai số xác định chức năng, bạn cần kiểm tra lại xem mình đã dùng đúng số chức năng chưa hoặc trong quá trình nhập có dư ký tự nào không để chỉnh lại cho đúng.

Đối với nguyên nhân thứ 2 do lỗi tham chiếu, hãy xóa phần tham chiếu sai đi, thế lại bằng dãy ô đúng được đặt trong cùng một trang tính mà bạn muốn thực hiện với hàm SUBTOTAL.

Xem thêm:

  • Cách sử dụng hàm đếm COUNT, COUNTIF, COUNTA trong Excel
  • Hàm IF Excel: Công thức hàm và ví dụ cụ thể
  • Cách dùng hàm SUM trong Excel để tính tổng

Trên đây là hướng dẫn cách dùng hàm SUBTOTAL trong Excel đơn giản, dễ thực hiện. Hy vọng bài viết sẽ giúp ích cho bạn khi làm việc với bảng tính nhé!

Từ khóa » Câu Lệnh Subtotal