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 được đánh giá là một hàm đặc biệt 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 và nhanh chóng. Trong nội dung bài viết này, Unica sẽ giới thiệu cho bạn hàm subtotal trong excel là gì? Cách dùng hàm subtotal như thế nào? Hãy tham khảo để có thêm cho bản thân những thông tin hữu ích về hàm này nhé.

1. 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. Hàm SUBTOTAL trong Excel trả về kết quả tổng hợp cho các giá trị được cung cấp. SUBTOTAL có thể trả về SUM, AVERAGE, COUNT, MAX và các thứ khác (xem bảng bên dưới) và hàm SUBTOTAL có thể bao gồm hoặc loại trừ các giá trị trong các hàng ẩn.

Mục đích của hàm SUBTOTAL là nhận một tổng số phụ trong danh sách hoặc cơ sở dữ liệu.

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

2. Ý nghĩa hàm SUBTOTAL

Hàm SUBTOTAL được sử dụng để tính tổng, trung bình, giá trị lớn nhất, giá trị nhỏ nhất, v.v. cho các nhóm dữ liệu con. Kế toán là ngành sử dụng hàm SUBTOTAL nhiều nhất.

Hàm SUBTOTAL có thể được sử dụng trong các trường hợp sau:

- Tính tổng phụ cho một danh sách hoặc cơ sở dữ liệu.

- Tính tổng phụ cho các nhóm dữ liệu con.

- Loại bỏ các giá trị trùng lặp khi tính tổng phụ.

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

3. Những ứng dụng của hàm SUBTOTAL

Hàm SUBTOTAL trên thành công cụ thường được ứng dụng để:

- Trong kế toán: Hàm giúp tính tổng các số phát sinh đầu kỳ, trong và cuối kỳ của bảng cân đối kế toán,...

- Trong ngân hàng: Sử dụng để tính tổng số dư cuối ngày cho sổ quỹ tiền mặt và tiền gửi vào trong ngân hàng.

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

4. Sự khác nhau giữa hàm SUM và SUBTOTAL

Hàm SUM và hàm SUBTOTAL là hai hàm tính toán trong Excel được sử dụng để tính tổng các số trong một dãy ô. Tuy nhiên, giữa hai hàm này có một số điểm khác biệt cơ bản như sau:

Khái niệm

- Hàm SUM: Hàm SUM là một hàm đơn giản, có thể tính tổng các số trong một dãy ô.

- Hàm SUBTOTAL: Hàm SUBTOTAL là một hàm phức tạp hơn, có thể tính tổng phụ cho một danh sách hoặc cơ sở dữ liệu.

Cú pháp

- Hàm SUM: =SUM(range)

Trong đó, range: tham chiếu đến vùng dữ liệu cần tính tổng.

- Hàm SUBTOTAL: =SUBTOTAL (function_num, range1, [range2],...)

Trong đó:

- function_num: hằng số xác định phép tính cần thực hiện.

- range1: tham chiếu đến vùng dữ liệu cần tính toán.

- range2: tham chiếu đến vùng dữ liệu con tiếp theo.

So sánh hàm SUM và hàm SUBTOTAL

Phép tính

- Hàm SUM: Hàm SUM chỉ thực hiện phép tính tổng.

- Hàm Subtotal: Hàm Subtotal có thể thực hiện nhiều phép tính khác nhau, bao gồm tổng, trung bình, giá trị lớn nhất, giá trị nhỏ nhất, v.v.

Ví dụ

- Hàm SUM: =SUM(A1:A10). Hàm này sẽ tính tổng các số trong ô A1:A10.

- Hàm Subtotal: =SUBTOTAL(101, A1:A10, B1:B10). Hàm này sẽ tính tổng các số trong ô A1:A10 theo nhóm, tính tổng cho các nhóm dữ liệu con.

>>> 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

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

Công thức SUBTOTAL Excel bằng tiếng Anh như sau: 

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

Đơn giản hơn lệnh Subtotal : =SUBTOTAL(a,b,c,..)

Trong đó: 

- Function_num: Một số chỉ định hàm nào sẽ sử dụng để tính tổng phụ trong danh sách. Xem bảng dưới đây để biết danh sách đầy đủ.

- a,b,c: là các giá trị đi kèm với SUBTOTAL ví dụ như các giá trị có trong bảng sau:

Công thức sử dụng hàm subtotal trong Excel

6. 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:

6.1. Cách dùng hàm SUBTOTAL để tính tổng

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ả.

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

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

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

6.2. Sử dụng hàm SUBTOTAL để đánh số thứ tự

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à ô.

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

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

6.3. Đế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.

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

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

7. Kết hợp hàm SUBTOTAL và VLOOKUP

Hàm SUBTOTAL và hàm VLOOKUP là hai hàm tính toán trong Excel được sử dụng thường xuyên. Hàm SUBTOTAL có thể được sử dụng để tính tổng phụ cho một danh sách hoặc cơ sở dữ liệu, còn hàm VLOOKUP trong Excel thì dùng để tìm giá trị trong một bảng hoặc phạm vi ô.

Công thức kết hợp của hai hàm này đó là: =SUBTOTAL(mã_hàm; VLOOKUP(khóa_tìm_kiếm; dải_ô; chỉ_mục; [được_sắp_xếp]))

Để hiểu rõ hơn về sự kết hợp này, mời bạn tham khảo ví dụ dưới đây:

- Tạo một bảng dữ liệu như hình

Bảng dữ liệu ban đầu

- Để so sánh và tính điểm tin học cơ sở cao nhất của nhóm sinh viên do giảng viên Nguyễn A phụ trách bạn nhập vào đó hàm:

=SUBTOTAL(4; VLOOKUP(H2;B2:E7;4;FALSE); VLOOKUP(H5;B2:E7;4;FALSE); VLOOKUP(H6;B2:E7;4;FALSE))

Kết quả nhận được như hình:

Thao tác tính tổng kết hợp giữa 2 hàm

8. 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:

8.1. #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!

8.2. #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. 

8.3. #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é.

9. 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ả.