Hướng dẫn chi tiết cách sử dụng hàm SUBTOTAL trong Excel

Posted on by mtoplistChuyên mục:Top List

So với các hàm khác trong Excel thì hàm SUBTOTAL linh hoạt hơn và ứng dụng rộng rãi hơn trong tính toán. Trong bài viết này, Gitiho sẽ cùng bạn tìm hiểu cách sử dụng cũng như ứng dụng của hàm SUBTOTAL trong công việc nhé.

Hàm SUBTOTAL trong Excel là gì?

Microsoft định nghĩa hàm SUBTOTAL trong Excel là hàm trả về tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này “tổng phụ” không chỉ là tổng số trong một phạm vi ô xác định.

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ể, hàm SUBTOTAL linh hoạt hơn khi có thể thực hiện các phép toán số học và logic khác nhau như đếm ô, tính trung bình, tìm giá trị tối thiểu hoặc tối đa ….

Hàm SUBTOTAL hỗ trợ tất cả các phiên bản Excel và cú pháp trong Excel như sau:

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

Trong đó:

  • Function_num – Một 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 thà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.

Hàm số

Chức năng

Diễ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ống4104MAXTrả về giá trị lớn nhất5105MINTrả về giá trị nhỏ nhất6106PRODUCTTính kết quả của các ô7107STDEVTrả về độ lệch chuẩn mẫu dựa trên mẫu8108STDEVPTrả 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ẫu11111VARPƯớc tính độ dao động dựa trên toàn bộ số

Trong thực tế, không cần phải ghi nhớ tất cả các số chức năng ở trên. Ngay khi bạn bắt đầu nhập công thức hàm SUBTOTAL trong một ô hoặc trong thanh công thức, Microsoft Excel sẽ hiển thị danh sách các hàm số có sẵn cho bạn.

Xem thêm: Hướng dẫn cách tính tổng bằng hàm SUBTOTAL thay cho hàm SUM

Ví dụ: Đây là cách bạn có thể tạo công thức hàm SUBTOTAL 9 để tổng hợp các giá trị trong các ô từ C2 đến C8:

Để thêm số hàm vào công thức, bấm đúp vào nó, sau đó nhập dấu phẩy, chỉ định một phạm vi, nhập dấu ngoặc đơn đóng và nhấn Enter. Công thức đầy đủ sẽ như sau:

=SUBTOTAL(9,C2:C8)

Theo cách tương tự, bạn có thể viết công thức hàm SUBTOTAL 1 để tính trung bình, hàm SUBTOTAL 2 để đếm các ô có số, hàm SUBTOTAL 3 để đếm các khoảng trống,…

Như hình ảnh dưới đây cho thấy một vài công thức khác của hàm SUBTOTAL đã thực hiện:

Ghi chú. Khi bạn sử dụng công thức hàm SUBTOTAL với các hàm tóm tắt như hàm SUM hoặc hàm AVERAGE, nó chỉ tính toán các ô có số bỏ qua khoảng trắng và các ô chứa giá trị không phải là số.

Như vậy bạn đã biết cách tạo công thức hàm SUBTOTAL trong Excel, nhưng thực tế vẫn có rất nhiều người dùng lại gặp rắc rối khi sử dụng hàm này. Tại sao không chỉ đơn giản là sử dụng một hàm thông thường như SUM, COUNT, MAX …? Câu trả lời bạn sẽ tìm thấy ở ngay dưới đây.

Xem thêm: Hướng dẫn cách tính tổng bằng hàm SUBTOTAL thay cho hàm SUM

3 lý do để sử dụng hàm SUBTOTAL trong Excel

So với các hàm Excel truyền thống khác, hàm SUBTOTAL trong Excel có những lợi thế quan trọng sau.

Hàm SUBTOTAL tính giá trị trong các hàng được lọc

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

Ví dụ: Nếu lọc bảng để chỉ hiển thị doanh số cho khu vực phía Đông (East), công thức hàm SUBTOTAL phụ sẽ tự động điều chỉnh để tất cả các khu vực khác được xóa khỏi tổng số:

Ghi chú: Vì cả hai bộ hàm số (1-11 và 101-111) đều bỏ qua các ô được lọc, bạn có thể sử dụng công thức SUBTOTAL 9 hoặc SUBTOTAL 109 trong trường hợp này đều được.

Hàm SUBTOTAL chỉ tính các ô có thể nhìn thấy

Các công thức hàm SUBTOTAL với Function_num 101 đến 111 bỏ qua tất cả các ô ẩn – được lọc và ẩn thủ công.

Vì vậy, khi bạn sử dụng tính năng Hide (ẩn) 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 Subtotal.

Xem thêm: Hướng dẫn đánh số thứ tự kết quả lọc Autofilter với hàm Subtotal

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 Subtotal trong Excel của bạn chứa bất kỳ công thức Subtotal nào khác thì các hàm Subtotal được lồng vào sẽ bị bỏ qua, do đó các số tương tự sẽ không được tính hai lần.

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

Xem thêm: Hướng dẫn các cách tính trung bình trong Excel đơn giản nhất

Sử dụng hàm SUBTOTAL trong Excel – Ví dụ về công thức

Khi lần đầu tiên sử dụng hàm SUBTOTAL, nó có vẻ rất phức tạp và khó khăn. Nhưng khi tìm hiểu từ những ví dụ đơn giản nhất, bạn sẽ thấy nó không khó để làm chủ. Các ví dụ dưới đây sẽ cho bạn thấy một vài lời khuyên hữu ích và ý tưởng để sử dụng.

Ví dụ 1: SUBTOTAL 9 với SUBTOTAL 109

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

Để tính tổng các hàng được lọc, bạn có thể sử dụng công thức SUBTOTAL 9 hoặc SUBTOTAL 109 như hiển thị trong hình ảnh dưới đây:

Nhưng nếu đã ẩn các mục không liên quan theo cách thủ công bằng cách sử dụng lệnh Hide Rows từ thẻ Home > Cells group > Format > Hide & Unhide hoặc bằng cách kích chuột phải vào các dòng, sau đó chọn lệnh Hide.

Bây giờ nếu muốn tổng giá trị trong các hàng hiển thị thì hàm SUBTOTAL 109 là lựa chọn duy nhất:

Ví dụ: Để đếm các ô được lọc không trống bạn có thể sử dụng công thức SUBTOTAL 3 hoặc SUBTOTAL 103.

Nhưng chỉ hàm SUBTOTAL 103 mới có thể đếm chính xác các khoảng trống có thể nhìn thấy nếu có bất kỳ hàng nào được ẩn nào trong phạm vi tính toán:

Ghi chú: Hàm SUBTOTAL của Excel với Function_num từ 101-111 sẽ bỏ qua các giá trị trong các hàng ẩn, nhưng không ở các cột ẩn .

Ví dụ: Nếu bạn sử dụng công thức như SUBTOTAL(109,A1:E1) để tính tổng các số trong phạm vi ngang, việc ẩn một cột sẽ không ảnh hưởng đến SUBTOTAL.

Xem thêm: Hướng dẫn 5 cách tính tổng một cột trong Excel

Ví dụ 2. Sử dụng hàm IF + SUBTOTAL để tự động tóm tắt dữ liệu

Nếu bạn đang tạo một báo cáo tóm tắt hoặc bảng điều khiển phải hiển thị các bản tóm tắt dữ liệu khác nhau nhưng không có không gian cho tất cả mọi thứ, cách tiếp cận sau đây có thể là một giải pháp:

  • Bước 1: Trong một ô, tạo danh sách thả xuống có chứa các tên hàm như Total, Max, Min …
  • Bước 2: Trong một ô bên cạnh danh sách thả xuống, hãy nhập công thức IF lồng nhau với các hàm SUBTOTAL được nhúng tương ứng với các tên hàm trong danh sách thả xuống.

Ví dụ: Giả sử các giá trị cho SUBTOTAL nằm trong các ô C2:C16 và danh sách thả xuống trong ô A17 chứa các mục Total, Average, Max và Min. Vậy thì công thức SUBTOTAL “động” 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),””))))

Bây giờ, tùy thuộc vào chức năng nào mà người dùng chọn từ danh sách thả xuống, hàm SUBTOTAL tương ứng sẽ tính toán các giá trị trong các hàng được lọc:

Xem thêm: Làm thế nào để viết nhiều hàm IF lồng nhau dễ hiểu dễ thực hiện

Các lỗi SUBTOTAL phổ biến trong Excel

Nếu công thức SUBTOTAL của bạn trả về lỗi, có thể do một trong những lý do sau:

  • VALUE! – Đối số Function_num không phải là số nguyên trong khoảng 1 – 11 hoặc 101 – 111; hoặc bất kỳ đối số Ref nào chứa tham chiếu 3-D.
  • DIV/0!

    – Nếu một hàm tóm tắt được chỉ định phải thực hiện phép chia cho 0.

  • NAME?

    – Tên của

    hàm SUBTOTAL

    bị sai chính tả.

Tổng kết

Trên đây là cách sử dụng hàm SUBTOTAL với những ví dụ cụ thể. Quá trình sử dụng nếu gặp khó khăn gì trong việc chấm công hãy chia sẻ và bình luận ngay dưới bài viết này để Gitiho sẽ giúp bạn giải quyết mọi thắc mắc nhé. Để được học kiến thức Excel theo lộ trình bài bản, chuyên nghiệp, các bạn hãy tham gia khóa học Tuyệt đỉnh Excel:

Tuyệt đỉnh Excel là khóa học được xây dựng bởi các chuyên gia đi đầu trong việc ứng dụng Excel vào công việc thực tế, có nhiều năm kinh nghiệm giảng dạy cho nhân viên các tập đoàn lớn: Vietinbank, VP Bank, FPT Software, VNPT, Mobifone,… Bạn sẽ được học tất tần tật các kỹ năng cần thiết về Excel cho công việc văn phòng, biết cách dùng 150+ hàm thông dụng trong Excel, biết cách trực quan hóa những số liệu khô khan trở nên sinh động, dễ hiểu hơn.

Bạn sẽ có thêm cơ hội được thăng tiến trong công việc hoặc mở ra những hướng phát triển mới cho bản thân khi làm chủ được phần mềm Excel. Khóa học phù hợp với tất cả các bạn cần đến Excel trong công việc của các ngành như tài chính ngân hàng, kế toán, phân tích dữ liệu,… Trong quá trình học, bạn có thể đặt câu hỏi cho giảng viên dưới mỗi video bài giảng và sẽ được giải đáp trong vòng 24h. Bạn có thể học mọi lúc, mọi nơi mà không bị giới hạn về thời gian hay số lần học. Chúc các bạn học tập hiệu quả!

Đánh giá bài viết này

Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

mtoplist