Cách sử dụng hàm SUBTOTAL trong Excel đơn giản nhất

Hàm SUBTOTAL là một trong những hàm tính tổng của Excel nhưng công dụng không hề đơn giản như hàm SUM. Vậy SUBTOTAL có gì đặc biệt và cách sử dụng như thế nào? Hãy cùng tìm hiểu trong bài viết sau nhé!

SUBTOTAL là hàm tính tổng tập hợp con từ một bảng tính dữ liệu trong Excel. Cụ thể, bạn có thể sử dụng SUBTOTAL để tính tổng, trung bình, tìm giá trị lớn nhất/ nhỏ nhất, đánh số thứ tự,...

Hàm SUBTOTAL mang đến nhiều lợi ích, điển hình như:

• Tính tổng giá trị các hàng nhanh chóng.

• Tính toán các giá trị ẩn trong bảng dữ liệu dễ dàng.

• Nếu các giá trị tham chiếu đã chứa hàm SUBTOTAL thì công thức sẽ tự động bỏ qua.

Công thức hàm SUBTOTAL:

Trong đó: 

• Function_num: Là số nằm trong dãy 1-11 hoặc 101-111. Cụ thể 1-11 dùng để tính cả các giá trị ẩn và 101-111 dùng để tính toán cho các giá trị không ẩn.

• ref1: Là tham chiếu hoặc phạm vi vùng dữ liệu đầu tiên cần tính tổng phụ.

• ref2: Là tham chiếu hoặc phạm vi vùng dữ liệu tiếp theo cần tính tổng phụ, tối đa 254 tham chiếu.

Dưới đây là bảng Function_num dùng để áp dụng trong công thức SUBTOTAL:

Để hiểu rõ hơn về công thức SUBTOTAL, bạn có thể tham khảo các ví dụ sau đây:

Bảng dữ liệu tính hoa hồng tháng 2 gồm cột Nhóm và cột Phí hoa hồng. Để tính tổng phí hoa hồng của Nhóm 1, bạn tiến hành như sau:

Bước 1: Bôi đen toàn bộ vùng dữ liệu cần tính. Tại thẻ Home, bạn nhấn vào “Sort and Filter” để tiếp tục.

Bạn bôi đen vùng dữ liệu rồi chọn “Sort and Filter” trong thẻ Home.

Bước 2: Tại mục Sort and Filter, bạn tiếp tục nhấn vào “Filter”.

Bạn tiếp tục nhấn chọn “Filter” trong mục “Sort and Filter”.

Bước 3: Tại cột Nhóm, bạn nhấn vào mũi tên hình tam giác ngược rồi tích chọn “1”, sau đó click “OK”.

Bạn nhấn vào mũi tên tam giác ngược bên cạnh cột Nhóm rồi tích chọn “1”.

Bước 4: Lúc này, bảng dữ liệu đã lọc các giá trị Phí hoa hồng của nhóm 1. Tại ô muốn hiển thị kết quả tổng phí hoa hồng của Nhóm 1, bạn nhập công thức =SUBTOTAL(9,D3:D9).

Bạn nhập công thức SUBTOTAL vào ô muốn hiển thị kết quả tổng phí hoa hồng của Nhóm 1.

Trong công thức SUBTOTAL bên trên, ta có:

• function_num: Số 9 tương ứng với hàm SUM, dùng để tính tổng các số.

• ref 1: D3:D9 là phạm vi dữ liệu cần tính tổng.

Sau đó, kết quả cho ra sẽ hiển thị như hình bên dưới:

Kết quả công thức SUBTOTAL cho ra tổng Phí hoa hồng trong tháng của Nhóm 1 là 1400.

Bảng danh sách sản phẩm cần mua có cột Tên, Danh mục và Link sản phẩm. Để đếm số lượng các mặt hàng đồ gia dụng đã có link sản phẩm, bạn có thể dùng hàm SUBTOTAL trong Excel theo các bước dưới đây:

Bước 1: Bạn bôi đen toàn bộ vùng dữ liệu rồi nhấn “Sort and Filter” sau đó chọn “Filter”.

Bạn chọn vùng dữ liệu rồi nhấn “Sort and Filter” và chọn “Filter”.

Bước 2: Bạn nhấn vào mũi tên tam giác ngược ở cột Danh mục rồi tích chọn mục “Đồ gia dụng” sau đó click “OK”.

Bạn nhấn vào mũi tên bên cạnh Danh mục rồi chọn “Đồ gia dụng” .

Bước 3: Tại ô muốn hiển thị kết quả số lượng các mặt hàng đồ gia dụng đã có link sản phẩm, bạn nhập công thức =SUBTOTAL(3,D3:D10).

Trong đó:

• function_num: Là số 3 tương ứng với hàm COUNTA, dùng để đếm các ô không trống.

• ref1: Là phạm vi cần đếm từ D3:D10.

Bạn nhập công thức SUBTOTAL vào ô muốn hiển thị kết quả.

Bước 4: Bạn nhấn Enter để hiển thị kết quả số lượng các mặt hàng Đồ gia dụng đã có link sản phẩm.

Màn hình cho ra kết quả có 3 mặt hàng Đồ gia dụng đã có link sản phẩm.

Trong bảng danh sách nhân viên nhóm, bạn có thể dùng công thức SUBTOTAL để đánh số thứ tự. Cụ thể, bạn áp dụng SUBTOTAL theo các bước sau:

Bước 1: Tại cột STT, bạn nhập công thức =SUBTOTAL(3,$B$3:B3) vào ô A3 rồi nhấn Enter.

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

• ref1: Là phạm vi đếm $B$3:B3. Để cố định ô B3, bạn chỉ cần nhấn phím F4 phía sau.

Bạn nhập công thức hàm SUBTOTAL vào ô A3 rồi nhấn Enter.

Bước 2: Khi ô A3 hiển thị kết quả bằng 1, bạn nhấp chuột vào dấu cộng bên phải ô B3 rồi kéo thẳng xuống để sao chép công thức SUBTOTAL.

Bạn nhấn vào dấu cộng bên trái ô B3 rồi kéo xuống đến ô A9 để sao chép công thức.

Kết quả đánh số thứ tự sẽ hiển thị như hình dưới:

SUBTOTAL giúp đánh số thứ tự trong Excel một cách nhanh chóng.

Bảng dữ liệu DANH SÁCH ĐỒ DÙNG chứa cột Sản phẩm và Thành tiền. Nếu muốn tìm sản phẩm có giá trị cao nhất, bạn dùng công thức SUBTOTAL theo các bước sau đây:

Bước 1: Tại vị trí muốn hiển thị kết quả, bạn nhập công thức =SUBTOTAL(4,C3:C7).

Trong đó:

• function_num: Là số 4 tương ứng với hàm MAX, dùng để tìm giá trị lớn nhất.

• ref1: Là vùng dữ liệu đếm C3:C7.

Bạn nhập công thức SUBTOTAL vào ô muốn hiển thị giá trị lớn nhất.

Bước 2: Bạn nhấn Enter để hiển thị kết quả giá trị thành tiền lớn nhất.

Kết quả hiển thị giá trị thành tiền cao nhất là 8900.

Ngoài các công dụng trên, bạn cần biết cách sử dụng hàm SUBTOTAL kết hợp với hàm khác để áp dụng trong nhiều trường hợp phức tạp hơn. Điển hình như:

Khi kết hợp SUBTOTAL với VLOOKUP, ta có công thức sau:

Cụ thể, hàm VLOOKUP dùng để tìm dữ liệu trong bảng và cho ra kết quả theo hàng ngang tương ứng. Việc kết hợp SUBTOTAL với VLOOKUP giúp phân tích dữ liệu trong bảng một cách nhanh chóng.

Ví dụ: Dưới đây là bảng dữ liệu “ĐIỂM KPI CỦA CÁC NHÓM” và “DANH SÁCH NHÂN VIÊN NHÓM”. Nếu muốn tìm điểm KPI cao nhất tháng 11 của nhân viên thuộc nhóm 2, bạn tiến hành theo các bước sau đây:

Bước 1: Tại ô cần trả kết quả, bạn nhập công thức sau:

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

Lúc này, function_num của hàm SUBTOTAL là 4, tương ứng với hàm MAX (tìm giá trị lớn nhất).

Bạn nhập công thức SUBTOTAL có function_num là 4 tương ứng với hàm MAX rồi kết hợp với hàm VLOOKUP.

Bước 2: Bạn nhấn Enter để hiển thị kết quả điểm KPI tháng 11 cao nhất của nhân viên nhóm 2.

Hàm SUBTOTAL kết hợp với VLOOKUP tìm ra điểm KPI cao nhất của nhân viên nhóm 2 là 600.

Cách sử dụng hàm SUBTOTAL kết hợp với IF được áp dụng trong trường hợp lọc dữ liệu có điều kiện hoặc đánh số thứ tự theo cột.

Công thức SUBTOTAL kết hợp với IF như sau:

Ví dụ: Bảng dữ liệu DANH SÁCH NHÂN VIÊN NHÓM chứa các hàng trống và không trống. Nếu muốn đánh số thứ tự, bạn tiến hành theo các bước sau:

Bước 1: Tại cột STT, bạn nhập công thức =IF(B3='','',SUBTOTAL(3,$B$3:B3)) vào ô A3.

Trong đó:

• logical_test: Điều kiện của hàm IF dùng để lấy giá trị B3 là khoảng trắng, nếu ô B3 trống thì trả về giá trị đúng.

• value_if_true: Nếu hàm IF đúng (B3 là khoảng trắng) sẽ trả về trống (“”)

• value_if_false: Nếu hàm IF sai (B3 không trống) sẽ trả về giá trị khác trống. Trong trường hợp này, công thức sử dụng hàm SUBTOTAL(3,$B$3:B3) có giá trị số đối là 3, tương ứng với hàm COUNTA (đếm số ô không trống). Do đó, hàm IF sai thì kết quả sẽ đánh số thứ tự.

Bạn nhập công thức hàm IF kết hợp SUBTOTAL vào ô A3.

Bước 2: Bạn nhấn Enter rồi kéo dấu cộng ở bên trái ô B3 xuống A12 để sao chép công thức. Hiểu một cách đơn giản, nếu ô trong cột STT trống thì hàm IF sẽ không đánh số thứ tự. Còn nếu ô trong cột STT không trống thì hàm SUBTOTAL sẽ đánh số thứ tự tương ứng.

Bạn nhấn Enter rồi sao chép công thức xuống đến ô B12 để hiển thị kết quả đánh số thứ tự.

Quá trình sử dụng công thức SUBTOTAL có thể xuất hiện một số lỗi phổ biến như:

- Lỗi #VALUE!

Lỗi xuất hiện do các nguyên nhân như:

• function_num không thuộc dãy 1-11 hoặc 101-111.

• Phạm vi dữ liệu tham chiếu thuộc trang tính khác.

Lúc này, bạn kiểm tra lại function_num và phạm vi dữ liệu tham chiếu trong hàm SUBTOTAL.

- Lỗi #DIV/0! 

Lỗi xuất hiện khi bạn dùng SUBTOTAL để chia một giá trị cho 0. Lỗi này thường do tính độ lệch chuẩn hoặc giá trị trung bình cộng của một dãy ô không chứa số. Do đó, bạn cần kiểm tra lại dữ liệu đã nhập trong bảng tính.

- Lỗi #NAME? 

Lỗi xuất hiện do bạn nhập sai tên hàm SUBTOTAL. Bởi vậy, bạn chỉ cần kiểm tra lại tên hàm để đảm bảo tính chính xác. Khi nhập công thức trong Excel, bạn chỉ cần nhập =S sau đó hệ thống sẽ hiển thị gợi ý các hàm tương ứng. Bạn chỉ cần click chọn SUBTOTAL là được.

Hàm SUBTOTAL giúp tính tổng trong nhiều trường hợp nên bạn cần biết cách sử dụng để áp dụng hiệu quả. Hy vọng những ví dụ trong bài viết trên sẽ giúp bạn biết cách áp dụng hàm SUBTOTAL để tính toán nhanh chóng và chính xác nhất!