Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu kèm ví dụ minh họa

Khi làm việc với Microsoft Excel, bạn có thể dễ dàng thực hiện các phép tính tổng trên dữ liệu bằng hàm SUBTOTAL. Hàm này không chỉ giúp bạn tính tổng mà còn có thể thực hiện nhiều loại phép tính khác nhau, chẳng hạn như tính giá trị trung bình, tìm giá trị lớn nhất hoặc nhỏ nhất. Trong bài viết này, tuyengiaothudo.vn sẽ hướng dẫn bạn cách sử dụng hàm SUBTOTAL một cách dễ hiểu và minh họa cụ thể bằng các ví dụ cụ thể, nhằm nâng cao hiệu suất và hiệu quả trong việc xử lý dữ liệu.

Hàm SUBTOTAL là gì?

Hàm SUBTOTAL trong Microsoft Excel là hàm tính giá trị tổng phụ từ một phạm vi ô dữ liệu. Tính năng độc đáo của hàm này là khả năng tính giá trị tổng phụ trong khi bỏ qua các ô ẩn hoặc được lọc trong dữ liệu. Nó cho phép bạn áp dụng nhiều hàm toán học khác nhau, chẳng hạn như SUM, AVERAGE, COUNT, MAX, MIN và nhiều hàm khác, để tính tổng phụ dựa trên dữ liệu được hiển thị.

Hàm SUBTOTAL cung cấp chức năng mạnh mẽ để tạo báo cáo và tóm tắt dữ liệu từ các tập dữ liệu lớn, đặc biệt là khi bạn làm việc với dữ liệu được lọc hoặc ẩn. Đây là công cụ hữu ích để phân tích dữ liệu trong Excel.

Hàm SUBTOTAL được sử dụng để làm gì?

Hàm SUBTOTAL giúp phân tích dữ liệu dễ dàng mà không ảnh hưởng đến tổng số. Bạn có thể thêm công thức SUBTOTAL vào một phạm vi ô mà không làm thay đổi tổng số, vì SUBTOTAL tự động bỏ qua các ô khác có chứa công thức SUBTOTAL.

Hàm SUBTOTAL cũng tương tác tốt với các tính năng khác của Excel, chẳng hạn như bộ lọc ô. Nếu bạn sử dụng bộ lọc để hiển thị một phạm vi giá trị cụ thể, công thức SUBTOTAL sẽ tự động cập nhật để tính toán dựa trên phạm vi được hiển thị. Điều này giúp dễ dàng xác định và loại bỏ các giá trị phụ thuộc vào bộ lọc. Ngoài ra, SUBTOTAL hữu ích khi bạn muốn bỏ qua các giá trị ẩn, đây là điều mà SUM và các hàm khác không thể làm được.

Hàm SUBTOTAL cho phép bạn kết hợp nhiều hàm Excel thành một công thức duy nhất. Có tổng cộng 11 phương pháp SUBTOTAL khác nhau, mỗi phương pháp liên quan đến một hàm Excel hiện có.

Sử dụng SUBTOTAL hữu ích khi bạn muốn một công thức, chẳng hạn như SUM, hoạt động trên các ô đã lọc và cũng bỏ qua các công thức SUBTOTAL khác mà bạn không muốn đưa vào kết quả. Tuy nhiên, bạn có thể thay thế SUBTOTAL bằng cách tạo bảng xoay, nhưng điều đó đòi hỏi nhiều công sức hơn.

Cách thức hoạt động của hàm SUBTOTAL trong Microsoft Excel

Công thức SUBTOTAL có thể dễ dàng được tạo bằng cấu trúc sau:

=SUBTOTAL(hàm_số, ref1, ref2, …)

• function_num: Đây là số xác định loại phép tính bạn muốn thực hiện. Hàm SUBTOTAL có thể thực hiện nhiều loại phép tính khác nhau và bạn chọn số tương ứng với loại phép tính bạn muốn. Số này có thể là giá trị từ 1 đến 11 hoặc từ 101 đến 111. Các số từ 1 đến 11 bao gồm các giá trị ẩn, trong khi các số từ 101 đến 111 bỏ qua các giá trị ẩn.
• ref1: Đây là phạm vi hoặc tập hợp các giá trị mà bạn muốn thực hiện phép tính.
• [ref2]…: Bạn có thể cung cấp nhiều phạm vi hoặc bộ giá trị để thực hiện cùng một phép tính cho nhiều phạm vi. Các phạm vi này là tùy chọn.

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

Ví dụ về cách thức hoạt động của hàm SUBTOTAL

Có 11 chức năng khác nhau. Sau đây là danh sách các số tương ứng:

• 1 hoặc 101: TRUNG BÌNH
• 2 hoặc 102: ĐẾM (Chỉ đếm các ô có giá trị số)
• 3 hoặc 103: COUNTA (Đếm số ô có giá trị chứa cả chữ và số)
• 4 hoặc 104: MAX (Giá trị tối đa)
• 5 hoặc 105: MIN (Giá trị tối thiểu)
• 6 hoặc 106: TÍCH (Tính tích (phép nhân) của các giá trị)
• 7 hoặc 107: STDEV (Tính độ lệch chuẩn mẫu của một tập dữ liệu)
• 8 hoặc 108: STDEVP (Tính độ lệch chuẩn dựa trên toàn bộ dữ liệu)
• 9 hoặc 109: TỔNG
• 10 hoặc 110: VAR (Tính toán phương sai mẫu của một tập dữ liệu)
• 11 hoặc 111: VARP (Hồi quy dựa trên phương sai)

Sau đây là một số ví dụ về cách sử dụng hàm SUBTOTAL trong Excel trong phạm vi từ F4 đến F9:

Tính tổng trong bảng:=SUBTOTAL(9, F4:F9)

Tính số tiền trung bình: = TỔNG PHỤ (1, F4: F9)

Tìm giá trị lớn nhất trong bảng:=SUBTOTAL(4, F4:F9)

Đếm số ô chứa số:=SUBTOTAL(2, F4:F9)

Những điều cần biết trước khi sử dụng hàm SUBTOTAL

Mặc dù hàm SUBTOTAL có nhiều điểm nổi bật riêng, nhưng trước khi chúng ta bắt đầu sử dụng hàm này, chúng ta cũng cần cân nhắc những điểm sau:

• Sử dụng giá trị từ 1 đến 11 cho function_num sẽ đảm bảo rằng hàm SUBTOTAL sẽ bao gồm các giá trị ẩn, tuy nhiên, giá trị từ 101 đến 111 sẽ bỏ qua chúng.
• Nếu chúng ta sử dụng giá trị khác ngoài phạm vi từ 1 đến 11 hoặc từ 101 đến 111. Excel sẽ hiển thị lỗi #VALUE!. Lỗi này cũng có thể xảy ra khi chúng ta tham chiếu đến ô 3D.
• Khi bạn sử dụng các ô nằm ngang, ví dụ từ A1 đến D1. Các giá trị ẩn sẽ được chèn bất kể giá trị của function_num). Đây là hạn chế của SUBTOTAL và hiện tại không có cách nào để khắc phục.

• Sử dụng hàm SUBTOTAL trên dữ liệu đã lọc đảm bảo rằng các giá trị ẩn bị bỏ qua. Nó không phụ thuộc vào giá trị của đối số function_num.
• Hàm SUBTOTAL có thể được sử dụng như một phần của các công thức khác, bao gồm các công thức có chứa hàm IF.
• Nếu bạn có nhiều công thức SUBTOTAL trong cùng một phạm vi dữ liệu tham chiếu (ref1), Excel sẽ chỉ tính toán kết quả từ công thức SUBTOTAL đầu tiên và bỏ qua các công thức SUBTOTAL tiếp theo.
• Trong Excel, bạn có thể thêm tối đa 254 ô vào công thức SUBTOTAL, nhưng thông thường chỉ cần một ô là đủ để công thức hoạt động.

Hướng dẫn chi tiết, dễ hiểu về cách sử dụng hàm SUBTOTAL, kèm ví dụ

Nếu bạn muốn tạo công thức SUBTOTAL trong Microsoft Excel, bạn cần làm theo các bước chi tiết dưới đây.

Bước 1: Chọn một ô trống

Bắt đầu bằng cách mở bảng tính Excel (chứa dữ liệu của bạn) và chọn một ô trống. Sau khi chọn, hãy nhấp vào thanh công thức và đảm bảo con trỏ đang nhấp nháy.

Từ đây bạn có thể bắt đầu tạo công thức SUBTOTAL mới của mình.

Bước 2: Chèn function_num

Tiếp theo, bạn cần bắt đầu tạo công thức SUBTOTAL mới bằng cú pháp và cấu trúc được nêu ở trên. Nhập =SUBTOTAL( vào thanh công thức.

Bước đầu tiên của công thức SUBTOTAL là phương pháp function_num, tương ứng với một hàm toán học khác trong Excel. Để thực hiện, hãy nhập một số từ 1 đến 11 để tạo công thức SUBTOTAL bao gồm các giá trị ẩn hoặc nhập từ 101 đến 111 để tạo công thức bỏ qua chúng.

Ví dụ, =SUBTOTAL(101) sẽ sử dụng hàm AVERAGE để hiển thị giá trị trung bình từ phạm vi ô đã chọn, loại bỏ mọi giá trị ẩn. Khi bạn bắt đầu nhập công thức SUBTOTAL, một menu tùy chọn sẽ xuất hiện bên dưới công thức, cho phép bạn tự động chọn một trong các phương pháp này.

Khi chúng ta nói về các giá trị ẩn, chúng ta đang nói về các ô tồn tại trong các cột ẩn.

Điều này không bao gồm dữ liệu đã lọc (luôn bị ẩn) hoặc TỔNG PHỤ sử dụng phạm vi ô ngang (trong đó các giá trị luôn hiển thị).

Bước 3: Chọn phạm vi TỔNG PHỤ

Ở bước tiếp theo, bạn cần chỉ định phạm vi ô ref2 cần thiết.

Đây là dữ liệu mà công thức SUBTOTAL sẽ sử dụng để tính tổng phụ. Nếu bạn muốn tạo tổng phụ bằng nhiều phạm vi ô. Bạn có thể thêm chúng (liên tiếp) sau khi phương thức function_num đã được xác định.

Ví dụ, nếu bạn muốn đếm số ô trong phạm vi A2:A9 và B2:B9, hãy sử dụng công thức =SUBTOTAL(102,A2:A9,B2:B9), đảm bảo đóng công thức bằng dấu ngoặc đơn đóng.

Nếu phạm vi chứa dữ liệu không hợp lệ (ví dụ: chuỗi văn bản), Excel sẽ trả về thông báo lỗi. Bạn cần xóa dữ liệu xung đột hoặc thay đổi phạm vi dữ liệu được sử dụng trong công thức SUBTOTAL của mình để khắc phục lỗi này.

Một số lỗi thường gặp khi sử dụng hàm SUBTOTAL

Có một số lỗi cơ bản thường xảy ra khi sử dụng hàm SUBTOTAL trong Excel:

#VALUE!: Lỗi này xảy ra khi bạn sử dụng mã hàm không nằm trong phạm vi từ 1 đến 11 hoặc từ 101 đến 111. Lỗi này cũng xảy ra khi mã hàm có tham chiếu 3D, nghĩa là tham chiếu đến các ô trên nhiều trang tính.

#DIV/0!: Lỗi này xảy ra khi bạn cố chia một số cho 0. Ví dụ, khi bạn tính giá trị trung bình hoặc độ lệch chuẩn của một phạm vi ô không chứa giá trị số.

#Name: Lỗi này xuất hiện khi tên hàm SUBTOTAL bị viết sai chính tả hoặc không được nhận dạng đúng.

Trên thực tế, khi sử dụng hàm SUBTOTAL trong công việc, hầu hết mọi người chỉ sử dụng một số hàm thông dụng như tính tổng, tìm giá trị trung bình, giá trị nhỏ nhất hoặc lớn nhất. Các hàm như Var và Product ít được sử dụng. Nó thường chỉ được áp dụng bởi những người làm công việc liên quan đến phân tích xác suất hoặc toán học,…

Trong khi các hàm đơn lẻ như SUM, MAX, MIN có thể được sử dụng để thực hiện các phép tính cơ bản như tính tổng hoặc tìm giá trị lớn nhất/nhỏ nhất. Hàm SUBTOTAL sẽ tuyệt vời hơn khi được sử dụng kết hợp với AutoFilter. Ví dụ, khi bạn muốn tính tổng, tìm giá trị nhỏ nhất hoặc lớn nhất sau khi áp dụng bộ lọc trên dữ liệu, hàm SUBTOTAL với mã hàm 9 (SUM), 5 (MIN) có thể hữu ích hơn.

Kết luận

Chúng ta đã học cách sử dụng hàm này thông qua các ví dụ dễ hiểu. Từ tính tổng, tìm giá trị trung bình đến tìm giá trị lớn nhất hoặc nhỏ nhất, v.v. Hàm function_num trong SUBTOTAL cho phép bạn thực hiện nhiều loại phép tính khác nhau trên dữ liệu của mình.

Một điều cần lưu ý là khi bạn sử dụng hàm SUBTOTAL trên dữ liệu đã lọc, nó sẽ luôn bỏ qua các giá trị ẩn. Điều này làm cho SUBTOTAL trở thành một công cụ hữu ích khi bạn cần tính toán trên các tập dữ liệu lớn hoặc khi bạn đã áp dụng bộ lọc.

Nếu chúng ta hiểu cách thức hoạt động của hàm này, nó sẽ giúp bạn tránh được lỗi một cách dễ dàng. Hãy thử áp dụng hàm SUBTOTAL vào các tình huống thực tế của bạn. Từ đó, hãy tận dụng tối đa tiềm năng của Excel trong việc xử lý và phân tích dữ liệu của bạn!

Xem thêm: