Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu, có ví dụ kèm theo

Hàm Subtotal trong Excel thường được sử dụng vào việc tính tổng dãy số có điều kiện hoặc dùng để đếm các ô không trống trong một vùng dữ liệu. Đây là một trong những hàm quan trọng đối với những người làm kế toán bởi khả năng làm việc linh hoạt. Trong nội dung bài viết này, Unica sẽ cùng bạn tìm hiểu cách dùng hàm Subtotal để thực hiện các tác vụ công việc trong Excel một cách đơn giản, nhanh chóng.

Hàm SUBTOTAL là gì?

Hàm Subtotal là một hàm tính toán tập hợp con của bảng dữ liệu hoặc danh sách. Nhiệm vụ chính của hàm Subtotal đó chính là để tính tổng phụ trong bảng dữ liệu hoặc danh sách. Nó có khả năng tính toán các giá trị subtotal trong khi bỏ qua các ô được ẩn hoặc đã được lọc trong Excel. Ngoài ra, bạn có thể sử dụng kết hợp các hàm khác như hàm Sum, hàm Min, hàm Max,...

Với tính năng độc đáo của mình, hàm Subtotal là một hàm được ứng dụng nhiều trong việc tạo và tổng hợp các báo cáo, dữ liệu từ các tệp dữ liệu lớn. Đặc biệt, khi còn phải làm việc với các dữ liệu, dữ liệu đã lọc thì nó là một công cụ hữu ích trong Excel.

Hàm SUBTOTAL là một hàm tính tổng

>>> Xem thêm: Hướng dẫn cách tạo ghi chú trong excel cho người mới

Công thức tính của hàm SUBTOTAL

Hàm Subtotal có công thức như sau:

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

Trong đó:

• function_num: Số xác định loại phép tính cần thực hiện. Hàm Subtotal thực hiện được nhiều các loại phép tính khác nhau, bạn có thể chọn các số tương ứng với các phép tính bạn muốn. Các số này là một giá trị từ 1 đến 11 (tính cả các giá trị ẩn), hoặc từ 101 đến 111 (bỏ qua các giá trị ẩn).

• ref1: Phạm vi hoặc tập hợp các giá trị muốn thực hiện phép tính trên.

• [ref2]: Cung cấp nhiều phạm vi hoặc các tập hợp giá trị khác để cùng thực hiện phép tính.

Công thức hàm Subtotal trong Excel

Đối sổ ref1 là bắt buộc bởi vì đât là phạm vi ban đầu cần phải tính tổng bằng 1 trong 11 hàm đó. Bạn có thể thêm tới 254 phạm vi ô khác nhau. Nó sẽ tạo ra một tổng hợp kết hợp bằng tổng số liệu trên tất cả các phạm vi ô bao gồm.

Trong hàm SUBTOTAL có sẵn 11 hàm khác nhau, tương ứng như sau:

• 1 hoặc 101: Hàm Average (Trung bình).

• 2 hoặc 202: Hàm Count (Đếm số ô có giá trị là số).

• 3 hoặc 103: Hàm Counta (Đếm số ô có giá trị là số và chữ).

• 4 hoặc 104: Hàm Max (Giá trị lớn nhất).

• 5 hoặc 105: Hàm Min (Giá trị nhỏ nhất).

• 6 hoặc 106: Hàm Product (Tính tích nhân của các giá trị).

• 7 hoặc 107: Hàm Stdev (Tính độ lệch chuẩn mẫu của một tập hợp các dữ liệu).

• 8 hoặc 108: Hàm Stdevp (Tính độ lệch chuẩn dựa trên toàn bộ dữ liệu).

• 9 hoặc 109: Hàm Sum (Tính tổng).

• 10 hoặc 110: Hàm Var (Tính phương sai mẫu của tập hợp dữ liệu).

• 11 hoặc 111: Hàm Varp (Tính phương sai dựa trên toàn bộ dữ liệu).

Hàm SUBTOTAL thường được ứng dụng nhiều trong ngành kế toán

Để bạn có thể hiểu rõ hơn về hàm Subtotal, dưới đây là một số ví dụ bạn có thể ứng dụng trong Excel:

• Tính tổng trong 1 bảng: =SUBTOTAL(9,D4:D9).

• Tính số tiền trung bình: =SUBTOTAL(1,D4:D19).

• Tìm giá trị nhỏ nhất trong bảng: =SUBTOTAL(5,D5:D10)

>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

Cách dùng SUBTOTAL trong Excel

Như đã chia sẻ ở phần trên, hàm SUBTOTAL trong excel có thể làm được rất nhiều việc, tiêu biểu nhất là để tính tổng, đếm các ô không khoảng trống và đánh số thứ tự. Cụ thể cách dùng hàm SUBTOTAL trong các trường hợp như sau:

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

Ví dụ: Quan sát bảng sau, yêu cầu lọc ra KPI cho đội A.

Bước 1: Dùng chuột bôi đen bảng Excel chứa dữ liệu. Trên thanh công cụ, chọn Home > Chọn Sort and Filter > Chọn Filter.

Thao tác trên thanh công cụ

Bước 2: Trong cột C, bạn nhấn vào biểu tượng tam giác lộn ngược rồi tích chọn A và nhấn OK. 

Nhấn vào biểu tượng tam giác lộn ngược

Bước 3: Nhập công thức =SUBTOTAL(9,D2:D11) tại ô bạn cần lấy kết quả.

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

• function_num là 9 tại vì đây là giá trị tương ứng mà hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này được hiển thị ngay khi nhập công thức Subtotal. Vậy nên để tính tổng thì sẽ tương ứng với hàm Sum và là số 9.

• ref1 = D2:D11 là xác định phạm vi cần tính tổng.

Kết quả sau khi hoàn thành như sau:

Nhập hàm rồi nhấn enter để có kết quả

Lưu ý: Tùy vào nhu cầu sử dụng, bạn có thể sử dụng 9 để tính tổng cả các giá trị bị ẩn, hoặc sử dụng 109 để bỏ qua các giá trị bị ẩn/đã lọc.

>>> Xem thêm: Học tin học văn phòng online trọn đời chỉ từ 99K

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

Tương tự như các cách dùng SUBTOTAL trong excel chia sẻ như trên, cách dùng hàm SUBTOTAL để đếm các ô được lọc không trống cũng tương đối đơn giản, không có gì quá phức tạp cả. Sau đây là hướng dẫn sử dụng hàm SUBTOTAL trong excel đếm các ô được lọc không trống chi tiết cho bạn tham khảo.

Ví dụ: Quan sát bảng dữ liệu, yêu cầu đếm số lượng sinh viên nhóm C đã có link bài tập để thống kê. 

Bước 1: Lọc ra các sinh viên nhóm C như các thao tác lọc đã hướng dẫn phía trên.

Bước 2: Nhập công thức =SUBTOTAL(3,D4:D14) vào ô muốn lấy kết quả. 

Trong đó:

• function_num = 3: Gía trị đối số tương ứng với hàm cần sử dụng, trong ví dụ này chúng ta 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.

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

Kết quả sau khi hoàn thành như sau:

Kết quả sau khi hoàn thành

Đánh số thứ tự nhanh 

Cách subtotal trong excel vô cùng đơn giản, bạn chỉ cần nhập công thức =SUBTOTAL(3,$B$2:B2) vào ô A2. 

Trong đó:

• Funcation_num là 3: Ứng với hàm 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à ô. Khi bạn sao chép công thức suống dưới, các giá trị sẽ lần lượt thay đổi như kiểu $B$2:B3, $B$2:B4,... và hàm sẽ đếm đúng, tạo ra kết quả như mong muốn.

Kết quả sau khi hoàn thành như sau:

Kết quả sau khi hoàn thành

Cách kết hợp hàm Subtotal với các hàm khác trong Excel

Hàm Subtotal với hàm Vlookup

Để kết hết hàm Subtotal với hà Vlookup trong Excel, bạn cần áp dụng công thức sau:

=SUBTOTAL(function_num, VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

Hàm Vlookup được ứng dụng khi bạn muốn tìm dữ liệu trong một bảng và sẽ trả về dữ liệu khớp với các hàng ngang tương ứng. Vì vậy, khi kết hợp hai hàm này với nhau, bạn sẽ nhanh chóng phân tích được dữ liệu mà không cần phải tổng hợp một bảng khác phức tạp trước đó.

Để bạn hiểu rõ hơn về cách kết hợp hai ví dụ này, dưới đây là ví dụ:

Cho hai bảng dữ liệu DANH SÁCH NHÂN VIÊN NHÓM và ĐIỂM KPI CỦA NHÓM. Để tìm được điểm KPI cao nhất tháng của những nhân viên thuộc nhóm 2 bạn có thể làm theo các bước sau:

Bước 1: Nhập công thức tại ô cần trả kết quả trong Excel:

=SUBTOTAL(4;VLOOKUP(F5;A3:D7;3;false);VLOOKUP(F7;A3:D7;3;false))

Trong đó đối số 4 là mã hàm tương ứng với hàm Max.

Hướng dẫn kết hợp hàm Subtotal với hàm Vlookup

Bước 2: Nhấn Enter để nhận được kết quả.

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

Như vậy, hàm Vlookup đã nhanh chóng tìm được điểm KPI cho nhóm nhân viên 2.

Hàm Subtotal kết hợp với hàm If

Trong trường hợp cần lọc các dữ diệu có điều kiện hay đánh số thứ tự theo cột, bạn có thể kết hợp sử dụng hàm Subtotal với hàm If để nhanh chóng thực hiện yêu cầu của đề bài. Khi kết hợp hai hàm này với nhau, bạn áp dụng công thức sau đây:

=IF(logical_test, value_if_true, SUBTOTAL(function_num, fef1,...))

Để bạn có thể dễ dàng hiểu hơn về các áp dụng này, chúng ta có một ví dụ dưới đây.

Bạn có sẵn một bảng dữ liệu cần đánh số thứ tự, nhưng nó lại lộn xộn và có nhiều hàng trống. Bạn có thể kết hợp hàm If và hàm Subtotal để xử lý các tác vụ như sau:

Bước 1: Ở cột STT, bạn nhập công thức 

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

Hướng dẫn kết hợp hàm Subtotal và hàm If

Trong đó:

• logical_test: Điều kiện của hàm If sẽ lấy các giá trị ở ô B2 bằng khoảng trắng. Nếu ô trắng sẽ trả về kết quả đúng.

• value_if_true: Giá trị trả về trống nếu điều kiện If đúng.

• value_if_false: Giá trị trả về nếu điều kiện hàm IF sai. Lúc này, bạn sẽ dùng hàm Subtotal để đánh số thứ tự.

• function_num là 3 tương ứng với hàm Counta - hàm đếm số ô không trống.

Bước 2: Ấn Enter, rồi di chuyển chuột tới góc phải bên dưới của ô dữ liệu cho đến khi xuất hiện dấu cộng > ấn và kéo để áp dụng cho tất cả các ô còn lại.

Kết quả thu được như sau.

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

Một số lỗi cơ bản thường xảy ra khi tính hàm SUBTOTAL trong Excel

Trong quá trình sử dụng hàm SUBTOTAL để làm việc với excel chắc chắn sẽ có lúc gặp lỗi. Một số lỗi cơ bản thường xảy ra khi tính hàm SUBTOTAL trong Excel có thể kể đến như sau:

#DIV/0!

Lỗi #DIV/0! trong Excel xảy ra khi cần chia một tổng cụ thể cho 0 (chẳng hạn như tính giá trị trung bình hoặc độ lệch chuẩn của một dải ô không chứa số). Ngoài ra, lỗi này cũng có thể xuất hiện khi tham chiếu đến vùng dữ liệu chứa các ô trống hoặc ô không chứa giá trị số.

Để khắc phục lỗi này, bạn cần đảm bảo rằng số chia trong hàm SUBTOTAL không phải là 0. Bên cạnh đó cũng cần đảm bảo rằng tham chiếu đến vùng dữ liệu chỉ chứa các ô chứa giá trị số.

Lỗi #DIV/0!

#VALUE!

Lỗi #VALUE xảy ra khi số xác định chức năng funcation_num không nằm trong khoảng 1-11 hoặc101-111. Hoặc tham chiếu ref tham chiếu đến các ô trong một trang tính. 

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.

Fix lỗi#VALUE! trong hàm SUBTOTAL

Cách fix lỗi như sau:

• Nếu sai số xác định chức năng, điều đầu tiên bạn cần làm là 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.
• Nếu nguyên nhân là do lỗi tham chiếu, bạn hãy xóa phần tham chiếu sai đi, thay 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. 

#NAME

Khi bạn viết tên hàm SUBTOTAL bị sai chính tả thì sẽ xuất hiện lỗi #NAME. Để khắc phục lỗi này bạn hãy chú ý viết thật đúng tên hàm nhé.

Kết luận

Trên đây Unica đã chia sẻ với bạn tất tần tật các thông tin có liên quan tới hàm SUBTOTAL trong excel. Hy vọng rằng những thông tin này hữu ích giúp bạn có quá trình làm việc với excel được chủ động và mang lại hiệu quả cao hơn. Chúc các bạn có một buổi học Excel online hiệu quả.