Hướng dẫn cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu với ví dụ minh họa

Trong quá trình 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 hợp trên dữ liệu với 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, 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, Mytour 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 ví dụ cụ thể, để 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à một hàm tính toán giá trị subtotal (tổng con) từ một phạm vi ô dữ liệu. Điểm đặc biệt của hàm này là khả năng tính toán giá trị subtotal trong khi bỏ qua các ô được ẩn hoặc được lọc trong dữ liệu. Nó cho phép bạn áp dụng các 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 toán tổng con dựa trên dữ liệu hiển thị.

Hàm SUBTOTAL là công cụ hiệu quả để tạo báo cáo và tổng hợp dữ liệu từ các tập dữ liệu lớn trong Excel, đặc biệt khi bạn làm việc với dữ liệu được lọc hoặc ẩn.

Bạn biết gì về hàm SUBTOTAL?

Hàm SUBTOTAL giúp bạn phân tích dữ liệu một cách dễ dàng mà không ảnh hưởng đến tổng số chung. Bạn có thể thêm công thức SUBTOTAL vào một phạm vi ô mà không thay đổi tổng số chung, vì nó sẽ tự động bỏ qua các ô khá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 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 hiển thị. Điều này giúp bạn dễ dàng nhận biết và loại bỏ các giá trị phụ thuộc vào bộ lọc. Hơn nữa, SUBTOTAL hữu ích khi bạn muốn bỏ qua các giá trị ẩn, điều mà các hàm SUM hay các hàm khác chưa thực hiện được.

Hàm SUBTOTAL trong Excel cho phép bạn kết hợp nhiều hàm khác nhau vào 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ó.

Việc sử dụng SUBTOTAL rất hữu ích khi bạn muốn một công thức như tổng SUM hoạt động trên các ô bị lọc và bỏ qua các công thức SUBTOTAL khác mà bạn không muốn tính vào kết quả. Tuy nhiên, bạn có thể thay thế SUBTOTAL bằng cách tạo một bảng tổng hợp, nhưng điều này sẽ yêu cầu nhiều công đoạn hơn.

Cách hàm SUBTOTAL hoạt động trong Microsoft Excel

Bạn có thể dễ dàng tạo công thức SUBTOTAL với cấu trúc sau đây:

• 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à một giá trị từ 1 đến 11 hoặc từ 101 đến 111. Số từ 1 đến 11 tính cả giá trị ẩn, trong khi số từ 101 đến 111 bỏ qua giá trị ẩn.
• ref1: Đây là phạm vi hoặc một tập hợp các giá trị bạn muốn thực hiện phép tính trên.
• [ref2], …: Bạn có thể cung cấp nhiều phạm vi hoặc tập hợp giá trị khác nhau để thực hiện cùng một phép tính cho nhiều phạm vi khác nhau. Những 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 điều này cụ thể hơn, bạn có thể thêm tới 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 bằng tổng số liệu trên tất cả các phạm vi ô bao gồm.

Ví dụ về cách hàm SUBTOTAL thực hiện

Có tổng cộng 11 hàm khác nhau có sẵn. Dưới đây là danh sách các số tương ứng:

• 1 hoặc 101: AVERAGE (Trung bình)
• 2 hoặc 102: COUNT (Chỉ đếm số ô có giá trị là số)
• 3 hoặc 103: COUNTA (Đếm số ô có giá trị vừa chữ và số)
• 4 hoặc 104: MAX (Giá trị lớn nhất)
• 5 hoặc 105: MIN (Giá trị nhỏ nhất)
• 6 hoặc 106: PRODUCT (Tính tích (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 hợ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: SUM (Tính tổng)
• 10 hoặc 110: VAR (Tính phương sai mẫu của một tập hợp dữ liệu)
• 11 hoặc 111: VARP (Tính phương sai dựa trên toàn bộ dữ liệu)

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

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

Tính số tiền trung bình:
=SUBTOTAL(1, F4:F9)

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

Đếm số ô chứa các chữ 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ó rất nhiều tính năng nổi bật, trước khi sử dụng chúng ta cần xem xét một số điểm sau:

• Việc sử dụng giá trị từ 1 đến 11 cho function_num đảm bảo rằng hàm SUBTOTAL sẽ tính cả các giá trị ẩn. Tuy nhiên, nếu sử dụng giá trị từ 101 đến 111, các giá trị ẩn sẽ được bỏ qua.
• Nếu bạn sử dụng một giá trị ngoài khoảng 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 tham chiếu đến ô 3D.
• Khi sử dụng các ô nằm ngang nhau, ví dụ từ A1 đến D1, các giá trị ẩn sẽ bị tính vào kết quả bất kể giá trị của function_num. Điều này là một hạn chế của SUBTOTAL mà hiện tại chưa có cách khắc phục.

• Sử dụng hàm SUBTOTAL trên dữ liệu đã lọc sẽ đảm bảo rằng các giá trị ẩn sẽ bị bỏ qua, không phụ thuộc vào 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 công thức chứa IF.
• Nếu 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ẽ tính kết quả từ công thức SUBTOTAL đầu tiên và bỏ qua các công thức sau đó.
• Bạn có thể thêm tối đa 254 phạm vi ô vào một công thức SUBTOTAL trong Excel, nhưng thường chỉ cần một phạm vi ô duy nhất là đủ cho công thức hoạt động.

Hướng dẫn chi tiết về cách sử dụng hàm SUBTOTAL trong Excel với các ví dụ minh họa.

Để tạo một công thức SUBTOTAL trong Microsoft Excel, bạn cần làm theo các bước sau đây.

Bước 1: Lựa 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, chuyển đến thanh công thức và đảm bảo con trỏ đang nhấp nháy.

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

Bước 2: Thêm function_num

Tiếp theo, bạn cần bắt đầu tạo công thức SUBTOTAL mới bằng cách sử dụng cú pháp và cấu trúc đã nêu ở trên. Gõ =SUBTOTAL( vào thanh công thức.

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

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

Khi nhắc đến giá trị ẩn, chúng ta đề cập đến các ô tồn tại trong các cột bị ẩn.

Điều này không bao gồm dữ liệu đã lọc (luôn ẩn) hoặc các SUBTOTAL sử dụng các phạm vi ô ngang (nơi các giá trị luôn được hiển thị).

Bước 3: Lựa chọn các phạm vi cho SUBTOTAL

Ở 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 cách sử dụng nhiều phạm vi ô, bạn có thể thêm chúng (liên tiếp) sau khi đã xác định phương pháp function_num.

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

Nếu phạm vi chứa dữ liệu không hợp lệ (ví dụ: một chuỗi văn bản), Excel sẽ hiển thị một thông báo lỗi. Bạn cần loại bỏ các 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 bạn để sửa 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 mà thường xảy ra khi sử dụng hàm SUBTOTAL trong Excel:

#VALUE!: Đây là lỗi xảy ra khi bạn sử dụng một mã số chức năng không nằm trong khoảng từ 1 đến 11 hoặc từ 101 đến 111. Lỗi này cũng xảy ra khi mã số chức năng có tham chiếu đến các ô trên nhiều bảng tính (3D).

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

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

Trong 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 thườ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. Thường chỉ 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 mới sử dụng chúng...

Mặc dù có thể sử dụng các hàm đơn lẻ như SUM, MAX, MIN để thực hiện các tính toán cơ bản như tính tổng hoặc tìm giá trị lớn nhất/nhỏ nhất. Hàm SUBTOTAL nổi bật khi được sử dụng cùng với bộ lọc tự động. 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, thì hàm SUBTOTAL với các mã số chức năng 9 (SUM), 5 (MIN) có thể trở nên hữu ích hơn.

Kết luận

Chúng ta đã tìm hiểu cách sử dụng hàm này thông qua ví dụ minh họa dễ hiểu. Từ việc tính tổng, tìm giá trị trung bình cho đến tìm giá trị lớn nhất hoặc nhỏ nhất,… Chức năng 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ểm đáng lưu ý là khi bạn sử dụng hàm SUBTOTAL trên dữ liệu bị 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 bộ dữ liệu lớn hoặc khi bạn đã áp dụng bộ lọc.

Nếu bạn hiểu rõ cách hoạt động của hàm này, nó sẽ giúp bạn tránh các lỗi một cách dễ dàng. Hãy thử áp dụng hàm SUBTOTAL trong các tình huống thực tế của bạn để 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!