SUBTOTAL là gì? Hướng dẫn cách dùng hàm SUBTOTAL trong Excel

By CareerLinkĐăng ngày:

Không giống như các hàm Excel khác được thiết kế để chỉ thực hiện một việc cụ thể, SUBTOTAL cực kỳ linh hoạt – có thể thực hiện các phép toán số học và logic khác nhau như tính tổng, tính giá trị trung bình, tìm giá trị nhỏ nhất hoặc lớn nhất… Trong bài viết này, hãy cùng tìm hiểu SUBTOTAL là gì, cách sử dụng hàm SUBTOTAL và các thông tin thú vị khác nhé.

SUBTOTAL là gì? Hướng dẫn cách dùng hàm SUBTOTAL trong Excel

SUBTOTAL là gì?

“SUBTOTAL là một hàm trong Excel được sử dụng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu.”

Bạn cung cấp cho SUBTOTAL hai thứ: (1) phạm vi dữ liệu và (2) loại tổng phụ, đổi lại, SUBTOTAL sẽ cung cấp cho bạn tổng phụ của dữ liệu đó.

Không giống như SUM, AVERAGE, COUNT… chỉ thực hiện một việc, SUBTOTAL rất linh hoạt, bạn có thể sử dụng nó để tính tổng, tính trung bình, đếm một loạt ô. SUBTOTAL cũng bỏ qua các giá trị ẩn, điều mà các hàm khác (như SUM) không làm được.

Hàm SUBTOTAL có sẵn trong tất cả các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007 và các phiên bản cũ hơn.

Hàm SUBTOTAL có gì khác với hàm SUM?

Hàm SUM tính tổng tất cả các số trong một phạm vi, không có sự tùy chọn. Mặt khác, SUBTOTAL có tính chọn lọc, có thể thực hiện cùng một phép tính tổng trong khi tùy ý bỏ qua các hàng bạn đã ẩn hoặc lọc ra, khiến nó trở nên lý tưởng cho các tập dữ liệu động, thay đổi.

Hàm SUBTOTAL trong Microsoft Excel hoạt động như thế nào?

Hàm SUBTOTAL hoạt động bằng cách tính tổng giá trị của một phạm vi ô dựa trên một hàm toán học khác.

Cú pháp:

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

Trong đó:

Function_num: là số chỉ định hàm nào sẽ sử dụng cho tổng phụ.

Ref1, Ref2 …: Một hoặc nhiều ô hoặc phạm vi tính tổng phụ. Đối số Ref đầu tiên là bắt buộc, các đối số khác (tối đa là 254) là tùy chọn.

Đối số Function_num có thể thuộc về một trong các số sau:

1 – 11 bỏ qua các ô được lọc, nhưng bao gồm các hàng ẩn thủ công.

101 – 111 bỏ qua tất cả các ô ẩn – được lọc và ẩn thủ công.

SUBTOTAL sử dụng tới 11 hàm toán học khác nhau (từ AVERAGE đến VAR.P) để tìm giá trị tổng phù hợp, đồng thời làm việc trong các tham số của hàm (ví dụ, bỏ qua các công thức SUBTOTAL khác).

Hàm sốChức năngDiễn giải
1101AVERAGETrả về trung bình của các số
2102COUNTĐếm các ô chứa giá trị số
3103COUNTAĐếm các ô không trống
4104MAXTrả về giá trị lớn nhất
5105MINTrả về giá trị nhỏ nhất
6106PRODUCTTính kết quả của các ô
7107STDEVTrả về độ lệch chuẩn mẫu dựa trên mẫu
8108STDEVPTrả về độ lệch chuẩn dựa trên toàn bộ số
9109SUMCộng các số
10110VARƯớc tính độ dao động dựa trên mẫu
11111VARPƯớc tính độ dao động dựa trên toàn bộ số

Trên thực tế, không cần phải ghi nhớ tất cả các số hàm. Ngay khi bạn bắt đầu nhập công thức SUBTOTAL vào ô hoặc thanh công thức, Microsoft Excel sẽ hiển thị danh sách các số hàm khả dụng cho bạn.

Để thêm số hàm vào công thức, hãy nhấp đúp vào hàm bạn muốn tính (ví dụ hàm SUM số 9) sau đó nhập dấu phẩy, chỉ định phạm vi (C2:C8), nhập dấu ngoặc đơn đóng và nhấn Enter. Công thức hoàn chỉnh sẽ trông như thế này:

=SUBTOTAL(9,C2:C8)

Tương tự như vậy, bạn có thể viết công thức Tổng phụ 1 để tính giá trị trung bình, Tổng phụ 2 để đếm các ô có số, Tổng phụ 3 để đếm các ô không trống…

Lưu ý, khi bạn sử dụng công thức Tổng phụ với hàm tóm tắt như SUM hoặc AVERAGE, công thức này chỉ tính toán các ô có số, bỏ qua ô trống và các ô chứa giá trị không phải số.

Cách sử dụng hàm SUBTOTAL trong Excel: Hướng dẫn từng bước

Nếu bạn muốn tạo công thức SUBTOTAL trong Microsoft Excel, bạn sẽ cần làm theo các bước sau:

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 sẽ hiển thị kết quả. Sau khi chọn, hãy nhấn thanh công thức và đảm bảo rằng con trỏ nhấp nháy đang hoạt động.

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

Bước 2: Nhập phương thức function_num

Tiếp theo, bạn sẽ 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. Bắt đầu bằng cách nhập =SUBTOTAL vào thanh công thức.

Thành phần đầu tiên của công thức SUBTOTAL là phương thức function_num, tương quan với một hàm toán học Excel khác. Để thực hiện việc này, 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 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 trừ 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.

Bước 3: Chọn phạm vi SUBTOTAL

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

Đâ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 các phạm vi này (lần lượt) sau khi phương thức function_num được chỉ đị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), thì Excel sẽ trả về thông báo lỗi. Bạn sẽ 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 để giải quyết vấn đề này.

Hàm SUBTOTAL có gì đặc biệt?

Bây giờ bạn đã biết cách tạo công thức SUBTOTAL trong Excel nhưng bạn có biết lý do nên sử dụng SUBTOTAL là gì? Vì sao không sử dụng một hàm thông thường như SUM, COUNT, MAX…? Bạn sẽ tìm thấy câu trả lời ngay bên dưới đây.

Tính giá trị trong các hàng đã lọc

Vì hàm SUBTOTAL của Excel bỏ qua các giá trị trong các hàng đã lọc, bạn có thể sử dụng hàm này để tạo bản tóm tắt dữ liệu động, trong đó các giá trị tổng phụ được tính toán lại tự động theo bộ lọc.

Ví dụ, nếu lọc bảng để chỉ hiển thị doanh số bán hàng cho khu vực phía Nam, công thức Tổng phụ sẽ tự động điều chỉnh để xóa tất cả các khu vực khác khỏi tổng số.

Bỏ qua các giá trị trong các công thức SUBTOTAL lồng nhau

Nếu phạm vi được cung cấp cho công thức Tổng phụ Excel của bạn chứa bất kỳ công thức Tổng phụ nào khác, các tổng phụ lồng nhau đó sẽ bị bỏ qua, do đó các số giống nhau sẽ không được tính hai lần.

Chỉ tính các ô có thể nhìn thấy

Công thức Subtotal với function_num từ 101 đến 111 sẽ bỏ qua tất cả các ô ẩn – được lọc ra và ẩn thủ công. Vì vậy, khi bạn sử dụng tính năng Hide của Excel để xóa dữ liệu không liên quan khỏi chế độ xem, hãy sử dụng hàm số 101-111 để loại trừ các giá trị trong các hàng ẩn khỏi tổng phụ.

Cách xử lý các lỗi thường gặp khi sử dụng hàm SUBTOTAL

#NAME?: Nếu các ô xuất hiện lỗi này, điều đó có nghĩa là bạn có thể đã viết sai chính tả “SUBTOTAL”. Để sửa lỗi, hãy kiểm tra chính tả nhanh và sửa tên hàm.

#VALUE!: Lỗi này cho thấy số hàm của bạn không nằm trong phạm vi 1-11 hoặc 101-111, hoặc khi có tham chiếu 3D. Để khắc phục lỗi này, hãy kiểm tra lại đối số function_num của bạn và thu hẹp các tham chiếu của bạn chỉ đến chiều thứ hai.

#DIV/0!: Lỗi này xảy ra khi một hàm cụ thể, chẳng hạn như tính toán độ lệch chuẩn hoặc trung bình, chia cho số 0 trong một phạm vi ô không chứa bất kỳ giá trị số nào. Để tránh lỗi này, hãy kiểm tra dữ liệu và đảm bảo không có ô nào chứa giá trị 0 trong quá trình tính toán.

Trên đây là nội dung chia sẻ SUBTOTAL là gì trong Excel cùng với ưu điểm khi sử dụng. Để tìm hiểu thêm về nhiều thuật ngữ khác hữu ích đối với công việc của bạn, đừng quên truy cập vào CareerLink.vn nhé. Ở đây cũng có rất nhiều việc làm mới hấp dẫn đang chờ bạn khám phá.

Huỳnh Trâm

Về Tác Giả

CareerLink
Excel

Sao chép thành công