Trong quá trình làm việc với Excel, đặc biệt là khi xử lý dữ liệu có tính tổng hợp hoặc lọc theo nhóm, hàm SUBTOTAL là một công cụ vô cùng hữu ích mà dân văn phòng không thể bỏ qua. Khác với các hàm SUM hay AVERAGE thông thường, hàm SUBTOTAL trong Excel giúp bạn tính toán linh hoạt và thông minh hơn nhiều, đặc biệt khi dữ liệu thay đổi thường xuyên hoặc cần lọc nhanh theo điều kiện.

1. Hàm SUBTOTAL Trong Excel Là Gì?

hàm SUBTOTAL trong Excel - image 1

Hàm SUBTOTAL trong Excel là gì?

Hàm SUBTOTAL trong Excel là một hàm đa năng, được thiết kế để thực hiện các phép tính như tổng, trung bình, đếm hoặc tìm giá trị lớn nhất/nhỏ nhất trên một phạm vi dữ liệu. Điểm đặc biệt của hàm này là khả năng tự động bỏ qua các hàng ẩn khi dữ liệu được lọc bằng bộ lọc (Filter), đảm bảo kết quả chính xác mà không cần điều chỉnh thủ công. Hàm SUBTOTAL thường được sử dụng trong các bảng dữ liệu lớn, nơi người dùng cần phân tích nhanh mà vẫn giữ được tính linh hoạt.

Hàm SUBTOTAL không chỉ hỗ trợ các phép tính cơ bản mà còn tương thích với nhiều tình huống thực tế, từ báo cáo doanh thu đến quản lý danh sách nhân viên. Với sự hỗ trợ của hàm, bạn có thể tiết kiệm thời gian và giảm thiểu sai sót khi xử lý dữ liệu.

2. Ứng Dụng Của Hàm Subtotal Trong Excel

Hàm SUBTOTAL được ứng dụng rộng rãi trong nhiều lĩnh vực nhờ tính linh hoạt và hiệu quả. Dưới đây là một số ứng dụng nổi bật:

  • Phân tích dữ liệu được lọc: Hàm SUBTOTAL cho phép tính toán chỉ trên các hàng hiển thị sau khi áp dụng bộ lọc, rất hữu ích khi làm việc với bảng dữ liệu lớn như thống kê doanh số theo khu vực hoặc danh sách ứng viên đạt yêu cầu.
  • Tạo báo cáo động: Trong các báo cáo tài chính hoặc nhân sự, hàm SUBTOTAL giúp cập nhật kết quả tự động khi dữ liệu thay đổi, chẳng hạn như tính tổng lương nhân viên theo phòng ban.
  • Kiểm soát dữ liệu phức tạp: Hàm SUBTOTAL hỗ trợ phân tích nhanh dữ liệu mà không cần tạo thêm bảng phụ, giúp tối ưu hóa quy trình làm việc.
  • Hỗ trợ quản lý dự án: Dùng để tính tổng thời gian hoàn thành nhiệm vụ hoặc chi phí dự án dựa trên các hạng mục đã lọc.

Nhờ các ứng dụng này, hàm SUBTOTAL trên thanh công cụ không thể thiếu cho những ai thường xuyên làm việc với Excel.

3. Công Thức Hàm SUBTOTAL Trong Excel

Hiểu rõ cú pháp của hàm SUBTOTAL là bước đầu tiên để sử dụng nó hiệu quả. Công thức của hàm SUBTOTAL được xây dựng như sau:

text
=SUBTOTAL(function_num, ref1, [ref2], …)
  • function_num: Số đại diện cho loại phép tính mà bạn muốn thực hiện. Các giá trị phổ biến bao gồm:
    • 1: AVERAGE (tính trung bình)
    • 2: COUNT (đếm số ô chứa số)
    • 3: COUNTA (đếm số ô không trống)
    • 4: MAX (tìm giá trị lớn nhất)
    • 5: MIN (tìm giá trị nhỏ nhất)
    • 9: SUM (tính tổng)
    • 101-109: Tương tự 1-9 nhưng bỏ qua cả các hàng ẩn thủ công và hàng ẩn bởi bộ lọc.
  • ref1, [ref2], …: Các phạm vi dữ liệu mà bạn muốn áp dụng phép tính. Có thể chọn một hoặc nhiều vùng dữ liệu.

Ví dụ: =SUBTOTAL(9, A2:A10) sẽ tính tổng các giá trị trong phạm vi A2:A10, chỉ tính các hàng hiển thị nếu có bộ lọc được áp dụng.

4. Cách Dùng Subtotal Trong Excel

hàm SUBTOTAL trong Excel - image 2

Cách dùng hàm SUBTOTAL để tính tổng

Để sử dụng hàm SUBTOTAL hiệu quả, bạn cần nắm rõ quy trình cơ bản. Dưới đây là hướng dẫn từng bước để áp dụng hàm SUBTOTAL trong Excel:

  • Bước 1: Chuẩn bị dữ liệu: Đảm bảo bảng dữ liệu của bạn có cấu trúc rõ ràng, với các tiêu đề cột và dữ liệu được sắp xếp phù hợp. Ví dụ, một bảng doanh thu với các cột “Khu vực”, “Doanh thu”, “Tháng”.
  • Bước 2: Áp dụng bộ lọc: Chọn bảng dữ liệu, vào tab Data, nhấp vào Filter để thêm bộ lọc cho các cột. Điều này cho phép bạn lọc dữ liệu theo tiêu chí, chẳng hạn như chỉ hiển thị doanh thu của tháng 6.
  • Bước 3: Nhập hàm SUBTOTAL: Chọn ô mà bạn muốn hiển thị kết quả, nhập công thức SUBTOTAL. Ví dụ: =SUBTOTAL(9, B2:B100) để tính tổng doanh thu trong cột B.
  • Bước 4: Kiểm tra kết quả: Sau khi lọc dữ liệu (ví dụ, chỉ chọn khu vực miền Bắc), hàm SUBTOTAL sẽ tự động cập nhật kết quả dựa trên các hàng hiển thị.
  • Bước 5: Lưu và kiểm tra: Lưu tệp Excel và kiểm tra lại kết quả khi thay đổi bộ lọc để đảm bảo tính chính xác.

5. Kết Hợp Hàm SUBTOTAL Và Một Số Hàm Khác Trong Excel

hàm SUBTOTAL trong Excel - image 3

Hàm SUBTOTAL trở thành công cụ mạnh mẽ hơn khi kết hợp với hàm khác

Hàm SUBTOTAL trở nên mạnh mẽ hơn khi kết hợp với các hàm khác, giúp giải quyết các bài toán phức tạp. Dưới đây sẽ hướng dẫn cách kết hợp hàm SUBTOTAL với VLOOKUP và IF.

5.1. Kết Hợp Hàm SUBTOTAL Và VLOOKUP

Sự kết hợp giữa SUBTOTAL và VLOOKUP rất hữu ích khi bạn cần tính toán trên dữ liệu được lọc và tra cứu thông tin từ bảng khác. Dưới đây là cách thực hiện:

  • Ứng dụng: Tính tổng doanh thu của một sản phẩm cụ thể sau khi lọc theo khu vực, đồng thời tra cứu giá sản phẩm từ bảng giá.
  • Ví dụ:
    • Bảng 1: Danh sách doanh thu với cột “Mã sản phẩm”, “Số lượng bán”, “Doanh thu”.
    • Bảng 2: Bảng giá với cột “Mã sản phẩm”, “Giá bán”.
    • Bạn muốn tính tổng doanh thu của sản phẩm “SP01” sau khi lọc theo khu vực.
  • Cách làm:
    • Dùng VLOOKUP để tra cứu giá sản phẩm: =VLOOKUP(A2, Sheet2!A:B, 2, FALSE) tra giá của mã sản phẩm ở ô A2 từ bảng giá.
    • Tính doanh thu từng sản phẩm: Tạo cột phụ tính doanh thu bằng cách nhân giá (từ VLOOKUP) với số lượng.
    • Dùng SUBTOTAL để tính tổng doanh thu: =SUBTOTAL(9, C2:C100) để tính tổng doanh thu trong cột phụ, chỉ tính các hàng hiển thị sau khi lọc.

Lưu ý: Đảm bảo dữ liệu trong bảng tra cứu không bị ảnh hưởng bởi bộ lọc để VLOOKUP hoạt động chính xác.

5.2. Kết Hợp Hàm SUBTOTAL Và Hàm IF

Kết hợp SUBTOTAL với IF cho phép bạn thực hiện tính toán có điều kiện trên dữ liệu được lọc. Dưới đây là cách thực hiện:

  • Ứng dụng: Tính tổng doanh thu chỉ cho các giao dịch trên 1 triệu đồng sau khi lọc theo tháng.
  • Ví dụ:
    • Bảng dữ liệu với cột “Doanh thu”, “Tháng”.
    • Bạn muốn tính tổng doanh thu của các giao dịch trên 1 triệu đồng trong tháng 6.
  • Cách làm:
    • Tạo cột phụ với công thức IF để kiểm tra điều kiện: =IF(B2>1000000,B2,0″) (nếu doanh thu ở cột B lớn hơn 1 triệu, trả về giá trị, nếu không trả về 0).
    • Dùng SUBTOTAL để tính tổng cột phụ: =SUBTOTAL(9, D2:D100) để tính tổng các giá trị hợp lệ, chỉ tính các hàng hiển thị sau khi lọc theo tháng 6.

Lưu ý: Kết hợp IF và SUBTOTAL cần cột phụ để đảm bảo SUBTOTAL chỉ tính trên dữ liệu hiển thị.

5.3. Kết Hợp Hàm SUBTOTAL Và SUMIF

Trong Excel, việc kết hợp SUBTOTAL và SUMIF không thể thực hiện trực tiếp trong cùng một hàm như cách ta vẫn ghép các hàm thông thường. Tuy nhiên, bạn có thể sử dụng SUMIF để tính tổng có điều kiện, sau đó kết hợp với tính năng lọc (Filter) của Excel và áp dụng SUBTOTAL để chỉ tính các giá trị hiển thị sau khi lọc.

Một cách tiếp cận khác là dùng hàm mảng hoặc công thức nâng cao như SUMPRODUCT kết hợp với hàm SUBTOTAL ẩn để giả lập điều kiện lọc. Đây là cách giúp bạn kiểm soát dữ liệu tổng hợp có điều kiện hiệu quả hơn trong bảng tính lớn.

5.4. Kết Hợp Hàm SUBTOTAL Và COUNTIF

Tương tự như với SUMIF, hàm COUNTIF cũng không thể kết hợp trực tiếp với SUBTOTAL trong một công thức duy nhất. Thay vào đó, bạn có thể lọc dữ liệu trước bằng tính năng AutoFilter hoặc Advanced Filter, sau đó dùng SUBTOTAL với mã hàm 103 để đếm các ô hiển thị sau khi lọc. Nếu muốn đếm theo điều kiện cụ thể, bạn cần kết hợp thêm hàm IF và SUBTOTAL trong công thức mảng, ví dụ: {=SUM(IF(condition, SUBTOTAL(103, range), 0))}. Dù phức tạp hơn, nhưng phương pháp này giúp bạn đếm dữ liệu thỏa mãn điều kiện mà vẫn tuân thủ vùng lọc hiện tại.

6. Cách Fix Lỗi #VALUE! Trong Hàm SUBTOTAL

Lỗi #VALUE! trong hàm SUBTOTAL thường xuất hiện khi dữ liệu đầu vào không hợp lệ. Dưới đây là cách khắc phục lỗi này:

  • Nguyên nhân phổ biến:
    • Phạm vi dữ liệu chứa giá trị văn bản hoặc ô trống trong khi hàm yêu cầu số liệu.
    • Sử dụng sai số function_num (ví dụ, chọn số không hợp lệ như 0 hoặc – Công thức tham chiếu đến ô chứa lỗi (#DIV/0!, #N/A).
  • Cách sửa:
    • Kiểm tra dữ liệu: Đảm bảo phạm vi (ref1, ref2,…) chỉ chứa số hoặc ô trống. Sử dụng hàm ISNUMBER để kiểm tra: =ISNUMBER(A1) để xác định ô có phải số không.
    • Xóa văn bản: Lọc hoặc thay thế các giá trị văn bản bằng số, hoặc loại bỏ các ô chứa ký tự đặc biệt.
    • Kiểm tra function_num: Đảm bảo sử dụng đúng mã số từ 1-11 hoặc 101-111. Ví dụ, dùng 9 cho SUM, không dùng số ngoài phạm vi.
    • Sửa lỗi tham chiếu: Nếu ô tham chiếu chứa lỗi như #N/A, sử dụng hàm IFERROR để xử lý: =IFERROR(B2,0) để thay lỗi bằng 0.

7. Một Số Lỗi Thường Gặp Khi Dùng Hàm SUBTOTAL Trong Excel

Ngoài lỗi #VALUE!, người dùng có thể gặp một số vấn đề khác khi sử dụng hàm SUBTOTAL. Dưới đây là các lỗi phổ biến và cách xử lý:

  • Kết quả không cập nhật khi lọc dữ liệu: Nguyên nhân là do hàm SUBTOTAL không được đặt ngoài vùng lọc hoặc dữ liệu không được lọc đúng cách.
  • Hàm tính cả hàng ẩn thủ công: Do sử dụng mã function_num từ 1-11, vốn chỉ bỏ qua hàng ẩn bởi bộ lọc, không bỏ qua hàng ẩn thủ công.
  • Kết quả sai hàm lệ thuộc: Khi kết hợp với các hàm như SUM hoặc AVERAGE bên ngoài, kết quả có thể bị trùng lặp.
  • Phạm vi dữ liệu không đồng nhất: Nguyên nhân là do phạm vi ref1, ref2 chứa các loại dữ liệu khác nhau (số, văn bản, lỗi).

Hàm SUBTOTAL trong Excel không chỉ đơn thuần là công cụ tính toán, mà còn là trợ thủ đắc lực giúp bạn tiết kiệm thời gian và tối ưu hiệu quả xử lý dữ liệu. Nếu bạn đang muốn tìm kiếm một công việc văn phòng phù hợp với kỹ năng Excel của mình, đừng quên ghé qua JobsGO – nền tảng tuyển dụng đáng tin cậy, nơi kết nối bạn với hàng ngàn cơ hội nghề nghiệp hấp dẫn trong lĩnh vực kế toán, hành chính – nhân sự và nhiều ngành khác.

Câu hỏi thường gặp

1. Có Nên Dùng Hàm SUBTOTAL 9 Và 109 Để Tính Tổng Không?

Nếu bạn không dùng bộ lọc và muốn tính toàn bộ dữ liệu, dùng SUBTOTAL 9. Nếu bạn có sử dụng lọc dữ liệu. nên dùng SUBTOTAL 109.

2. Làm Sao Để Dùng Hàm SUBTOTAL Có Điều Kiện Để Tính Tổng?

Bạn cần kết hợp SUBTOTAL với các công thức như SUMPRODUCT + OFFSET + IF để giả lập chức năng tính tổng có điều kiện mà vẫn tương thích

3. Cách Dùng Hàm SUBTOTAL 9 Có Khó Không?

Không hề khó! Hàm SUBTOTAL với mã 9 là cách đơn giản để tính tổng cho một cột dữ liệu, ngay cả khi có hàng bị ẩn thủ công.

(Theo JobsGO - Nền tảng tìm việc làm, tuyển dụng, tạo CV xin việc)