[09/2023] CÁCH SỬ DỤNG HÀM SUBTOTAL TRONG EXCEL

Rate this post

Trong bài viết trước, chúng ta đã thảo luận về cách chèn Subtotal tự động trong Excel bằng cách sử dụng Subtotal Feature.. Hôm nay, Học Excel Online sẽ chỉ cho các bạn cách tự viết một công thức Subtotal như thế nào nhé, chắc chắn sẽ rất thú vị đấy!

Hàm SUBTOTAL trong Excel có nhiệm vụ để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải là tổng các con số trong 1 dãy ô xác định. Không giống như các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, hàm SUBTOTAL lại vô cùng linh hoạt, có thể tính toán hoặc làm các phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất…

Hàm SUBTOTAL có ở tất cả các phiên bản Excel từ 2016 đến 2007 và cả phiên bản thấp hơn.

Cú pháp của hàm SUBTOTAL trong Excel như sau:

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

Trong đó:

Function_num: con số xác định chức năng thực hiện

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.

Function_num có thể là 1 trong 2 loại sau:

Function_numChức năngMô tả1101AVERAGETính trung bình các con số2102COUNTĐếm số ô chứa giá trị số3103COUNTAĐếm số ô không trống4104MAXTìm giá trị lớn nhất5105MINTìm giá trị nhỏ nhất6106PRODUCTTính kết quả của các ô7107STDEVTính độ lệch chuẩn mẫu dựa trên mẫu8108STDEVPTính độ 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ẫu11111VARPƯớc tính độ dao động dựa trên toàn bộ số

Thực tế, bạn không cần phải nhớ hết các con số chức năng. Ngay khi bạn nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ đưa ra ngay danh sách các con số cho bạn.

Ví dụ, đây là cách bạn dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 đến C8:

Để thêm 1 số xác định chức năng(function_num) vào công thức, nhấn đúp chuột, đánh dấu phẩy, xác định dãy ô, đóng ngoặc và nhấn Enter. Công thức hoàn chỉnh sẽ như sau:

Read More:   [09/2023] Giờ AM Và PM Là Gì, Là Buổi Sáng Hay Là Buổi Tối?

=SUBTOTAL(9,C2:C8)

Tương tự, bạn có thể viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm ô chứa số, SUBTOTAL 3 để đếm ô không trống. Trong hình dưới đây, mình đã sử dụng 3 chức năng:

Lưu ý:

Khi bạn dùng công thức SUBTOTAL với chức năng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ tính các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.

Bây giờ thì bạn đã biết cách lập công thức SUBTOTAL trong Excel rồi, câu hỏi đặt ra là tại sao lại phải học hàm này? Sao không dùng các hàm đơn giản, bình thường như SUM, COUNT, MAX? Lý do sẽ có ngay dưới đây thôi, đọc tiếp nhé!

So sánh với các hàm Excel truyền thống, SUBTOTAL cho bạn những lợi thế sau:

Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, bạn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.

Ví dụ, nếu chúng ta lọc bảng doanh số bán hàng chỉ của khu vực Miền Đông, công thức SUBTOTAL sẽ tự động điều chỉnh để bỏ qua tất cả vùng khác khi tính tổng.

Lưu ý:

Vì các 2 bộ số xác định chức năng (1-11 và 101-111) đều bỏ qua các ô đã được lọc ra nên bạn có thể dùng công thức SUBTOTAL 9 hoặc SUBTOTAL 109 trong trường hợp này.

Công thức SUBTOTAL với số xác định chức năng 101-111 sẽ bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng chức năng ẩn của Excel để ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ các giá trị từ những hàng ẩn khỏi tổng phụ.

Nếu dãy ô trong hàm SUBTOTAL có chứa hàm SUBTOTAL khác, hàm SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 2 lần. Tuyệt vời phải không?

Read More:   [10/2023] Hướng Dẫn Cách Xua đuổi Mèo Hiệu Quả

Trong hình dưới, công thức tính tổng trung bình SUBTOTAL(1, C2:C10) bỏ qua kết quả của công thức SUBTOTAL trong ô C3 và C10, như thể bạn đã sử dụng công thức AVERAGE với 2 phạm vi riêng biệt AVERAGE (C2:C5, C7:C9).

Nếu mới học qua hàm SUBTOTAL, bạn có thể thấy hàm này khá phức tạp, rắc rối, thậm chí là vô nghĩa. Nhưng khi bạn dùng nó để giải quyết công việc, bạn sẽ nhận ra hàm Subtotal này cực kỳ hữu ích và việc thuần thục hàm SUBTOTAL không hề khó chút nào. Ví dụ sau đây mình sẽ mách bạn một số mẹo nhỏ khi dùng hàm SUBTOTAL nè!

Như bạn đã biết, Excel SUBTOTAL chấp nhận 2 bộ số xác định chức năng 1-11 và 101-111. Cả 2 bộ số đều bỏ qua các hàng đã được lọc ra, nhưng 1-11 bao gồm các hàng được ẩn thủ công còn 101-111 thì loại trừ. Để hiểu rõ hơn về điểm khác biệt này, xem ví dụ sau:

Với tổng các hàng đã được chọn, bạn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

Nhưng nếu có các hàng không liên quan đã ẩn thủ công bằng cách dùng lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide, và nhấn chuột phải vào các hàng, sau đó Hide, mà giờ bạn chỉ cần tổng giá trị trong các hàng nhìn thấy, lựa chọn SUBTOTAL 109:

Với các số chức năng khác bạn cũng làm theo cách tương tự. Ví dụ, để đếm ô đã lọc không trống, bạn dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng chỉ SUBTOTAL 103 mới có thể đếm chính xác các ô không trống nếu có bất kì hàng ẩn nào trong dãy.

Lưu ý: Hàm SUBTOTAL trong Excel với số chức năng 101-111 sẽ bỏ qua giá trị trong hàng bị ẩn, không phải cột bị ẩn. Ví dụ, nếu bạn dùng công thức như SUBTOTAL(109, A1:E1) để tính tổng các số trong hàng ngang, cột bị ẩn sẽ không ảnh hưởng đến tổng phụ.

Read More:   [10/2023] Mã Số CVV/CVC Là Gì? Tôi đã Mất Tiền Dù Không Tiết Lộ Số Này

Nếu bạn đang lập báo cáo tổng kết, cần phải đưa ra các dữ liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho tất cả số liệu, thì cách sau có thể là giải pháp:

Ví dụ, giả sử giá trị để tính tổng phụ trong ô C12:C16, và danh sách trong ô A17 chứa Total, Average, Max, Min, công thức hàm SUBTOTAL sẽ như sau:

=IF(A17=”total”, SUBTOTAL(9,C2:C16), IF(A17=”average”, SUBTOTAL(1,C2:C16), IF(A17=”min”, SUBTOTAL(5,C2:C16), IF(A17=”max”, SUBTOTAL(4,C2:C16),””))))

Mẹo nhỏ:

Nếu đột nhiên danh sách và ô công thức biến mất khỏi bảng tính, thì bạn hãy chọn lại chúng trong danh sách lọc.

Nếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân có thể là một trong những lý do sau đây

#VALUE! Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.

#DIV/0! Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số)

#NAME? tên hàm SUBTOTAL sai chính tả.

Mẹo nhỏ:

Nếu bạn chưa quen với hàm SUBTOTAL, bạn có thể dùng SUBTOTAL Feature được tích hợp sẵn và tự động chèn công thức cho bạn.

Cuối cùng, để hiểu hơn về cách sử dụng hàm Subtotal này, hãy mở ngay Excel ra và thực hành theo các ví dụ của mình! Cảm ơn bạn đã đồng hành, tiếp tục theo dõi để cập nhật những kiến thức mới nhất nhé!

Click đăng ký học tại:

HKD Học Excel Online
Số ĐK: 17A80048102

© Học Excel Online. All rights reserved.

Với những thông tin mà Lavanthur.com chia sẻ, chúng tôi hy vọng với thông qua bài viết về “[09/2023] CÁCH SỬ DỤNG HÀM SUBTOTAL TRONG EXCEL❤️️”.có thể giúp bạn có thêm nhiều thông tin cũng như hiểu rõ hơn về chủ đề “[09/2023] CÁCH SỬ DỤNG HÀM SUBTOTAL TRONG EXCEL” [ ❤️️❤️️ ]”.

Back to top button