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, Hoàng Hà Mobile 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 cung cấp tính năng hiệu quả trong việc tạo các báo cáo và tổng hợp dữ liệu từ tập dữ liệu lớn, đặc biệt khi bạn làm việc với dữ liệu được lọc hoặc ẩn. Nó là một công cụ hữu ích trong việc phân tích dữ liệu trong Excel.
Hàm SUBTOTAL dùng để làm gì?
Hàm SUBTOTAL giúp bạn dễ dàng phân tích dữ liệu mà không ảnh hưởng đến tổng số chung. Bạn có thể thêm một công thức SUBTOTAL vào một phạm vi ô mà không làm thay đổi tổng số chung, vì SUBTOTAL 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 đều chưa thực hiện được.
Hàm SUBTOTAL cho phép bạn kết hợp nhiều hàm Excel 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ó.
Sử dụng SUBTOTAL rất hữu ích khi bạn muốn một công thức, ví dụ như tổng SUM, hoạt động trên các ô bị lọc và đồng thời 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 việc tạo một bảng tổng hợp, nhưng việc đó sẽ yêu cầu nhiều công đoạn hơn
Cách hàm SUBTOTAL hoạt động trong Microsoft Excel
Công thức SUBTOTAL có thể được tạo dễ dàng bằng cấu trúc sau:
- 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ụ cách hàm SUBTOTAL thực hiện
Có 11 hàm khác nhau có sẵn. Đâ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 điểm nổi bật của riêng nó, trước khi đi vào sử dụng. Chúng ta cũng 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 sẽ giúp ta chắc chắn rằng hàm SUBTOTAL sẽ gồm cả các giá trị ẩn, tuy nhiên, giá trị từ 101 đến 111 sẽ bỏ qua chúng.
- Nếu chúng ta dùng một giá trị khác 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 ta tham chiếu ô 3D.
- Khi bạn sử dụng các ô nằm ngang, ví dụ từ A1 đến D1. Các giá trị ẩn sẽ tự đưa bất kể giá trị nào của function_num). Điều này là một hạn chế của SUBTOTAL và hiện chưa có cách nào để khắc phục.
- Sử dụng hàm SUBTOTAL trên data đã lọc (filtered data) sẽ giúp ta chắc rằng các giá trị ẩn sẽ bị bỏ qua. Nó sẽ 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ông thứ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 kết quả từ công thức SUBTOTAL đầu tiên và bỏ qua các công thức SUBTOTAL sau đó.
- Trong Excel, bạn có thể thêm tối đa 254 phạm vi ô vào một công thức SUBTOTAL, nhưng thường chỉ cần một phạm vi ô duy nhất là đủ để công thức có thể dùng được.
Hướng dẫn dùng hàm SUBTOTAL chi tiết, dễ hiểu, có ví dụ
Nếu bạn muốn tạo một công thức SUBTOTAL trong Microsoft Excel, bạn cần thực hiện chi tiết theo các bước sau nhé.
Bước 1: Chọn một ô trống
Bắt đầu bằng việc mở bảng tính Excel của bạn (chứa dữ liệu) và chọn một ô trống. Sau khi đã chọn, hãy nhấn vào thanh công thức và đảm bảo rằng 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á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à 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 điều này, hãy gõ một số từ 1 đến 11 để tạo công thức SUBTOTAL bao gồm các giá trị ẩn hoặc gõ 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ỏ bất kỳ giá trị ẩn nào. Khi bạn bắt đầu gõ một công thức SUBTOTAL, một menu tùy chọn sẽ xuất hiện dưới công thức, cho phép bạn chọn một trong những phương pháp này tự động.
Khi nói về giá trị ẩn, chúng ta đang nói đế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: Chọn các phạm vi 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 phương pháp function_num đã được xác định.
Ví dụ, nếu bạn muốn đếm số ô trong các 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 đó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ẽ trả về một thông báo lỗi. Bạn cần loại bỏ 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 để khắc phục điều này.
Một số lỗi thường gặp khi 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 xuất hiện khi bạn sử dụng một mã số chức năng không thuộc 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 3D, tức là tham chiếu đến các ô trên nhiều bảng tính.
#DIV/0!: Lỗi này xảy ra khi bạn cố gắng chia một số cho 0. Chẳng hạn, khi bạn 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, đa số người chỉ sử dụng một vài 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. 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,…
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 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, 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 ta 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ừ đó, 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 bạn nhé!
Xem thêm:
