Hàm SUBTOTAL trong Excel là gì? Công thức SUBTOTAL trong Excel như thế nào? Hãy cùng Quantrimang.com tìm hiểu nhé!
Microsoft Excel có rất nhiều hàm dùng để tính tổng, và SUBTOTAL là một trong số đó. Tuy nhiên, Subtotal khác với hàm SUM. Nó không phải tính tổng thông thường. Thay vào đó, nó được dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, tính số trung bình và nhiều hơn thế nữa.
Thoạt nhìn, bạn có thể tự hỏi: Mục đích của hàm SUBTOTAL là gì? Xét cho cùng, thật dễ dàng để tìm tổng phụ từ một dải ô bằng cách sử dụng các hàm hiện có như SUMIF hoặc AVERAGE.
Mặc dù đúng là bạn không nhất thiết phải sử dụng SUBTOTAL, nhưng nó giúp việc phân tích dữ liệu được nhắm mục tiêu trở nên dễ dàng hơn rất nhiều. Bạn có thể chèn công thức bằng SUBTOTAL vào một dải ô mà không ảnh hưởng đến tổng số chung, vì SUBTOTAL bỏ qua các ô khác có chứa công thức SUBTOTAL.
SUBTOTAL hoạt động với các tính năng Excel khác, chẳng hạn như lọc ô. Nếu bạn lọc một bảng bằng một giá trị, công thức SUBTOTAL sẽ cập nhật, nhận dạng bộ lọc và loại trừ các ô tương ứng. SUBTOTAL cũng hữu ích để bỏ qua các giá trị ẩn, điều mà những hàm khác (như SUM) không thể làm được.
Rất may, bạn có thể sử dụng các hàm khác như một phần của công thức SUBTOTAL. Có sẵn 11 phương pháp tổng phụ khác nhau, mỗi phương pháp phù hợp với một hàm Excel hiện có. Nếu bạn muốn một công thức SUM hoạt động với các ô được lọc và bỏ qua các công thức SUBTOTAL khác, thì tốt nhất là sử dụng SUBTOTAL, mặc dù một pivot table có thể hoạt động theo cách tương tự.
Hàm SUBTOTAL hoạt động bằng cách tính toán 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. SUBTOTAL sử dụng tới 11 hàm toán học khác nhau (từ AVERAGE đến VAR.P) để tìm tổng giá trị 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 SUBTOTAL có cú pháp =SUBTOTAL(function_num, ref1, [ref2],…). Trong đó:
Lưu ý:
Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là:
=SUBTOTAL(9,pham_vi)
pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.
Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.
Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.
Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ.
Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.
Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.
Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.
Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.
Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.
Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.
Mặc dù SUBTOTAL có những ưu điểm, nhưng cũng có một số điều bạn cần cân nhắc trước khi bắt đầu sử dụng hàm này, bao gồm những điểm sau:
Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:
Mẹo 1: Giả sử bạn muốn đảm bảo tất cả màu áo phông đều có sẵn ở mọi kích thước của một trong hai nhà kho. Bạn có thể làm theo những bước sau:
Bước 1: Click Subtotal. Nhớ rằng đây là bước thêm nhiều tiêu chí cho dữ liệu tổng phụ hiện tại.
Bước 2: Chọn COUNT từ menu thả xuống và Size từ trường “Add subtotal field to”. Sau đó, bỏ tích Replace current subtotals. Khi click OK, bạn sẽ nhận được bảng dữ liệu sau:
Điều này giúp đảm bảo rằng bạn có đủ số lượng các kích thước khác nhau và có thể phân loại dữ liệu theo cách không cần phải lặp lại thao tác.
Mẹo 2: Luôn hữu ích khi cần phân loại dữ liệu theo cột.
Mẹo 3: Giúp bạn bao gồm một nhãn trong hàng đầu tiên.
Mẹo 4: Nếu muốn tóm tắt dữ liệu, bỏ tích ô “Summary below the data when inserting Subtotal.” - Tóm tắt bên dưới dữ liệu khi chèn hàm SUBTOTAL.
Tóm lại, hàm Subtotal trong Excel là gì?
Đúng như tên gọi, hàm SUBTOTAL trong Excel được dùng để tính tổng phụ. Hàm Excel SUBTOTAL trả về kết quả tổng hợp cho các giá trị được cung cấp. SUBTOTAL có thể tính tổng, số trung bình, số lượng, tối đa… Excel này cũng có thể bao gồm hoặc loại bỏ giá trị trong các hàng ẩn.
function_num
- Một số chỉ định sử dụng hàm nào để tính tổng phụ trong danh sách.ref1
- Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.ref2
- [tùy chọn] Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.Sử dụng hàm SUBTOTAL có thể giúp bạn nhanh chóng phân tích tập dữ liệu mà không cần dựa vào các bảng tổng hợp phức tạp hơn. Nếu gặp khó khăn, bạn cũng có thể sử dụng tính năng Subtotal, được tìm thấy trong tab Data trên thanh ribbon, để tự động tạo công thức SUBTOTAL mà sau đó bạn có thể chỉnh sửa hoặc sao chép ở nơi khác.
Xem thêm:
Ý kiến bạn đọc
Những tin mới hơn
Những tin cũ hơn