1312, Tháp B, Tòa nhà The Light, Tố Hữu, Hà Nội

T2 - T7: 8:00 - 17:30

024 66 66 33 69

info@es-glocal.com

  • Ngôn ngữ tiếng anh

Hàm SUBTOTAL trong Excel và các ứng dụng

11:00 18/12/2020 Tin Tức Đặng Tiến Trung 0 bình luận

Hàm SUBTOTAL là gì? Cú pháp, ý nghĩa và ứng dụng của hàm SUBTOTAL trong Excel như thế nào? Hãng Kiểm toán ES-GLOCAL xin trân trọng gửi đến bạn đọc bài chia sẻ Hàm SUBTOTAL trong Excel, mời các bạn cùng theo dõi!

Dưới đây là nội dung chi tiết của bài viết, các bạn theo dõi có gì thắc mắc thì đặt câu hỏi theo hướng dẫn ở cuối bài nhé!

Hàm SUBTOTAL trong Excel
Hàm SUBTOTAL trong Excel

#1. Tổng quan về hàm SUBTOTAL

Hàm SUBTOTAL trong Excel có nhiệm vụ để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô (Hàm count), tính trung bình, tìm giá trị lớn nhất/nhỏ nhất (Hàm Max, hàm Min)...

Hàm SUBTOTAL có ở tất cả các phiên bản Excel từ 2016 đến 2007 và cả phiên bản thấp hơn.

#2. Cú pháp và cách sử dụng hàm SUBTOTAL

Cú pháp hàm SUBTOTAL:

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

Trong đó:

- Function_num: con số xác định chức năng thực hiện

- Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.

Số xác định chức năng thực hiện có 2 loại sau:

1 -11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công

101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.

Function_num Chức năng Mô tả
1 101 AVERAGE Tính trung bình các con số
2 102 COUNT Đếm số ô chứa giá trị số
3 103 COUNTA Đếm số ô không trống
4 104 MAX Tìm giá trị lớn nhất
5 105 MIN Tìm giá trị nhỏ nhất
6 106 PRODUCT Tính kết quả của các ô
7 107 STDEV Tính độ lệch chuẩn mẫu dựa trên mẫu
8 108 STDEVP Tính độ lệch chuẩn dựa trên toàn bộ số
9 109 SUM Cộng các số
10 110 VAR Ước tính độ dao động dựa trên mẫu
11 111 VARP Ước tính độ dao động dựa trên toàn bộ số

Khi sử dụng, người dùng không cần phải nhớ hết các con số chức năng vì khi nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ đưa ra danh sách gợi ý các con số để người dùng lựa chọn.

Ví dụ 1: Tính tổng, giá trị trung bình, giá trị max, min trong bảng dữ liệu sau:

Hàm SUBTOTAL trong Excel

Trong trường hợp này, thay vì sử dụng nhiều hàm, hàm SUBTOTAL có thể giúp người dùng giải quyết bài toán một cách nhanh chóng hơn.

Cách thực hiện:

Hàm SUBTOTAL trong Excel

Lưu ý: Khi bạn dùng công thức SUBTOTAL với chức năng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ tính các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.

#3. Ưu điểm của hàm SUBTOTAL so với các hàm truyền thống

#3.1. Tính giá trị trong các hàng được chọn

Hàm SUBTOTAL trong Excel bỏ qua các hàng đã được lọc ra, người dùng có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.

Ví dụ 2:

Vẫn dữ liệu ở ví dụ 1, trong trường hợp đề bài yêu cầu tính toán cho các doanh nghiệp thuộc khu vực miền Nam:

Sau khi tiến hành lọc cột "Khu vực" để lấy giá trị "Miền Nam", các giá trị sẽ tự động nhảy theo công thức, ta được kết quả:

#3.2. Tính các ô nhìn thấy

Công thức SUBTOTAL với số xác định chức năng 101-111 bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng chức năng Excel’s Hide để ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ các giá trị từ những hàng ẩn khỏi tổng phụ.

Ví dụ 3: SUBTOTAL 9 và SUBTOTAL 109

Cho bảng giá trị:

Hàm SUBTOTAL trong Excel

Với tổng các hàng đã được chọn, bạn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

Hàm SUBTOTAL trong Excel

Tuy nhiên, trong trường hợp lọc giá trị, nếu có các hàng không liên quan đã được ẩn thủ công bằng cách dùng lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide, và nhấn chuột phải vào các hàng, sau đó Hide; kết quả trả về sẽ cho các giá trị khác nhau:

Hàm SUBTOTAL trong Excel

Như vậy, trong trường hợp này, lựa chọn SUBTOTAL 109 thì sẽ đảm bảo tiêu chí thực hiện công việc hơn.

#3.3. Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép

Nếu dãy ô trong công thức SUBTOTAL có chứa công thức SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 2 lần.

#4. Hàm SUBTOTAL trong Excel không hoạt động và các lỗi thường gặp

Nếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân có thể là một trong những lý do sau đây:

a, VALUE! Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.

b, DIV/0! Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số)

d, NAME? tên hàm SUBTOTAL sai chính tả.

Mẹo nhỏ:

Nếu bạn chưa quen với hàm SUBTOTAL, bạn có thể dùng các chức năng cài đặt sẵn của hàm này và để công thức tự hoàn thiện.

#5. Một số câu hỏi liên quan đến hàm SUBTOTAL

Hỏi: Hàm SUBTOTAL dùng để làm gì?

Trả lời: Hàm SUBTOTAL trong Excel dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô (Hàm count), tính trung bình, tìm giá trị lớn nhất/nhỏ nhất (Hàm Max, hàm Min)...

Hỏi: Phân biệt các chức năng của hàm SUBTOTAL 1-11 và 101-111?

Trả lời: Công thức SUBTOTAL với số xác định chức năng 101-111 bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng chức năng Excel’s Hide để ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ các giá trị từ những hàng ẩn khỏi tổng phụ.

Hỏi: Cách sử dụng hàm SUBTOTAL trong Excel?

Trả lời: Các bạn tham khảo bài viết phía trên nhé.

Trên đây, Hãng Kiểm toán ES-GLOCAL vừa chia sẻ cho các bạn cú pháp và ứng dụng của hàm SUBTOTAL trong Excel. Nếu có câu hỏi hay vướng mắc gì trong quá trình thực hiện các bạn vui lòng đặt câu hỏi theo đường dẫn: https://es-glocal.com/hoi-dap/ hoặc để lại bình luận bên dưới bài viết về cách sử dụng hàm SUBTOTAL trong công việc. Cảm ơn các bạn, chúc các bạn thành công!

logo zalo

Hỏi đáp Hàm SUBTOTAL trong Excel và các ứng dụng

menu
024 66 66 33 69
Top