Hướng dẫn chi tiết cách sử dụng hàm Subtotal trong Excel

Hướng dẫn cách sử dụng hàm Subtotal trong Excel giúp bạn xử lý các phép tính nhanh chóng và tối ưu nhất, tham khảo chi tiết tại đây.

Hàm Subtotal là một hàm giúp bạn xử lý nhanh chóng các phép tính tổng, trung bình đếm số…một cách hiệu quả hơn. Sử dụng hàm Subtotal trong Excel giúp bạn tiết kiệm thời gian không cần phải thực hiện thủ công từng thông tin một. Trong bài viết sau đây, hãy cùng Techtuts khám phá hàm Subtotal trong Excel nhé.

HÀM SUBTOTAL trong Excel là gì?

Hàm SUBTOTAL trong Excel là một hàm chuyên được dùng để thực hiện các phép tính như tính tổng, tính trung bình, đếm,tìm giá trị lớn nhất, nhỏ nhất... trên một tập hợp dữ liệu trong Excel đã được lọc hoặc chưa. Điểm đặc biệt của hàm này là nó chỉ tính toán trên các dữ liệu hiển thị sau khi lọc, giúp bạn tránh tính những giá trị đã bị ẩn hoặc loại bỏ.

Định nghĩa hàm Subtotal

Lợi ích khi sử dụng hàm Subtotal

• Bỏ qua dữ liệu đã lọc hoặc ẩn: Hàm SUBTOTAL chỉ tính trên các giá trị hiển thị, giúp bạn tránh các dữ liệu đã bị lọc hoặc ẩn, đảm bảo kết quả chính xác hơn so với các hàm tính tổng thông thường.
• Hỗ trợ nhiều phép tính: tính tổng, trung bình, đếm, lớn nhất, nhỏ nhất... trên cùng một tập dữ liệu
• Linh hoạt với hàng ẩn và không ẩn: Có thể tùy chọn bỏ qua hoặc bao gồm các hàng bị ẩn thủ công (bằng cách dùng function_num từ 101 trở lên), phù hợp với nhu cầu xử lý dữ liệu linh hoạt.
• Phân tích dữ liệu dễ dàng hơn khi dùng bộ lọc: Khi bạn áp dụng bộ lọc trong Excel, SUBTOTAL tự động điều chỉnh theo kết quả lọc mà không cần thay đổi công thức, giúp việc phân tích dữ liệu trở nên tiện lợi.
• Giảm thiểu sai sót khi tính toán: Bằng cách tự động loại bỏ dữ liệu ẩn hoặc đã lọc, hàm SUBTOTAL giúp bạn giảm thiểu sai sót trong các phép tính, đặc biệt là khi làm việc với những tập dữ liệu lớn và phức tạp.

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

Trong Excel, Google Sheet, hàm Subtotal có cú pháp:

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

Trong đó:

• function_num: Một con số đại diện cho phép toán mà bạn muốn thực hiện.

Đối với các phép toán này, nếu bạn muốn bỏ qua cả các hàng đã bị ẩn, thì chọn function_num từ 101 trở lên (101-111).

• ref1, [ref2]...: Các vùng dữ liệu, phạm v mà bạn muốn thực hiện phép toán.

Dưới đây là bảng Function_num gồm các mã số dùng để tính trong hàm SUBTOTAL:

Các mã số dùng để tính trong hàm SUBTOTAL

Cách dùng hàm Subtotal trong Excel

Cách tính tổng bằng hàm Subtotal

Để tính tổng bằng hàm SUBTOTAL trong Excel, bạn cần sử dụng mã phép toán (function_num) là 9 (SUM) hoặc 109 (SUM và bỏ qua các hàng ẩn).

Cú pháp: =SUBTOTAL(9, ref1, [ref2],...)

Trong đó:

• 9: Đại diện cho phép tính tổng (SUM).
• ref1, ref2,...: Các vùng dữ liệu bạn muốn tính tổng sau khi lọc dữ liệu

Nếu bạn muốn bỏ qua các hàng đã bị ẩn thủ công, sử dụng function_num là 109.

Ví dụ:

• Tính tổng các giá trị trong vùng A:

Nếu bạn chỉ cần tính tổng cơ bản, dùng công thức sau: =SUBTOTAL(9,A1:A10)

Tính tổng, bỏ qua các hàng bị ẩn thủ công: Trong trường hợp bạn muốn bỏ qua các hàng đã bị ẩn (không phải do bộ lọc), sử dụng mã phép toán là 109:

• =SUBTOTAL(109,A1:A10)
• Tính tổng với dữ liệu đã được lọc: Khi bạn áp dụng bộ lọc trên vùng dữ liệu, SUBTOTAL tự động chỉ tính tổng các ô hiển thị. Ví dụ:

=SUBTOTAL(9,A1:A10)

Công thức này sẽ bỏ qua các ô đã bị ẩn do bộ lọc và chỉ tính tổng những ô còn lại.

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

Để đếm một cách chính xác bạn sẽ sử dụng Subtotal 3 hoặc Subtotal 103. Tuy nhiên để đếm các ô không trống nhìn thấy thì cần phải sử dụng Subtotal 103 sẽ chính xác nhất.

Ví dụ: Đếm số lượng sản phẩm đã có link mua hàng của nhóm đồ gia dụng trong bảng Excel cho tiện thống kê

• Bước 1: Lọc sản phẩm bằng lệnh Auto Filter trên thanh công cụ.

Sử dụng lệnh Auto Filter

• Bước 2: Tại cột danh sách, kích chuột vào dấu mũi tên hình tam giác nằm ngang để chọn mục Đồ gia dụng rồi nhấn “ OK ”

Lọc sản phẩm

• Bước 3: Nhập công thức ở ô kết quả: =SUBTOTAL(3,D3:D10)

Sử dụng công thức Subtotal

• Bước 4: Trên bàn phím, nhấn Enter là ra kết quả

Kết quả sau khi sử dụng hàm Subtotal

Cách đánh số thứ tự bằng hàm Subtotal

Bạn cần đánh số thứ tự nhanh chóng và liên tiếp, bạn có thể sử dụng hàm Subtotal trong Excel, Google Sheet như sau:

• Bước 1: Nhập cú pháp: =SUBTOTAL(3,$B$2:B2) ngay tại cột đầu tiên của STT rồi sau đó nhấn Enter

Sử dụng cú pháp hàm Subtotal

• Bước 2: Di chuyển chuột vào góc dưới bên phải ô B2 nhằm xuất hiện dấu cộng, sau đó nhấp chuột rồi kéo thẳng xuống các ô dưới, kéo cho đến ô cuối cùng cần đánh số thứ tự nhằm sao chép công thức.

Thực hiện một loạt thao tác

• Bước 3: Nhận kết quả sau khi thực hiện hai bước trên

Nhận kết quả sau khi sử dụng hàm

Các lỗi thường gặp khi dùng hàm Subtotal

#VALUE! Error (Lỗi giá trị)

• Nguyên nhân: Lỗi này thường xuất hiện khi bạn nhập sai function_num hoặc phạm vi dữ liệu không hợp lệ.
• Cách khắc phục:
  • Kiểm tra xem bạn đã nhập đúng function_num chưa (các giá trị hợp lệ là từ 1 đến 11 hoặc từ 101 đến 111).
  • Đảm bảo rằng các tham chiếu vùng (ref1, ref2,...) chứa dữ liệu hợp lệ (không phải là văn bản nếu bạn đang tính tổng, trung bình,...).

#NAME? Error

• Nguyên nhân: Hàm SUBTOTAL không được nhận diện, thường do bạn gõ sai tên hàm.
• Cách khắc phục: Đảm bảo rằng bạn đã gõ đúng tên hàm là SUBTOTAL, không nhầm lẫn với các hàm khác như SUM.

Kết luận

Trên đây là cú pháp và những cách sử dụng hàm Subtotal trong Excel vô cùng đơn giản và dễ hiểu. Hy vọng rằng từ những thông tin về hàm Subtotal và ví dụ minh hoạ cụ thể, bạn có thể hiểu cách dùng hàm công thức này vào dữ liệu Excel

Nếu bạn muốn tìm hiểu thêm về cách dùng các hàm khác trong Excel, hãy tham khảo các bài viết hướng dẫn chi tiết trên Techtuts.net - nơi cung cấp nguồn tài liệu uy tín, đáng tin cậy về Excel.