Như các bạn đã biết, hàm SUM là hàm cơ bản nhất trong Excel dùng để tính tổng các giá trị. Tuy nhiên, để làm tốt hơn những gì hàm SUM có thể, bạn hãy sử dụng hàm SUBTOTA. Vậy Hàm SUBTOTAL là gì? Hãy cùng Trang thủ thuật tìm hiểu qua bài viết này nhé.
Hàm SUBTOTAL là gì?
Định nghĩa hàm SUBTOTAL
Hàm SUBTOTAL là một hàm của Excel có chức năng dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu.
Đa số các hàm trong Excel được thiết kế với một nhiệm vụ riêng biệt, nhưng hàm SUBTOTAL lại rất linh hoạt, bạn có thể sử dụng hàm này để tính toán hoặc làm phép logic, đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất,..
Bạn có thể sử dụng hàm SUBTOTAL trên các phiên bản Excel từ 2007 đến 2016.
Ứng dụng của hàm SUBTOTAL trong kế toán
Hàm SUBTOTAL được sử dụng rất nhiều bởi dân văn phòng, đặc biệt là kế toán.
- Tính tổng phát sinh trong kỳ
- Tính tổng cho từng loại tài khoản cấp 1
- Tính tổng tiền tồn cuối ngày
Cú pháp và cách sử dụng của hàm SUBTOTAL
Cú pháp
=SUBTOTAL(function_num, ref1, [ref2],…)Trong đó:
- Function_num: Thể hiện các con số từ 1 đến 11 và 101 đến 111 xác định những chức năng thực hiện của SUBTOTAL
- Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254 và bắt buộc phải có Ref1.
Lưu ý:
- Hàm SUBTOTAL chỉ có thể tính toán cho các cột số liệu theo chiều dọc.
- Nếu các đối số ref1, ref2,… đã có sẵn hàm SUBTOTAL thì sẽ được bỏ qua để tránh trường hợp tính trùng 2 lần.
- Nếu function_num từ 1 đến 11 hàm SUBTOTAL sẽ tính luôn các giá trị ẩn trong hàng của vùng dữ liệu. Ngược lại, nếu function_num từ 101 đến 111, hàm SUBTOTAL chỉ tính toán cho các giá trị đang được hiển thị và không tính cho các giá trị ở hàng ẩn.
Ngoài ra, bạn không cần phải nhớ các con số chức năng. Khi bạn nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ tự động đưa ra 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
Cú pháp:
=SUBTOTAL(9,C2:C8)Lưu ý: Khi bạn dùng SUBTOTAL với các chức năng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ thực hiện trên các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.
Lý do tại sao nên sử dụng hàm SUBTOTAL trong Excel?
Như đã chia sẻ, hàm SUBTOTAL sở hữu hai bộ số xác định chức năng từ 1-11 và từ 101-111. Cả hai đều bỏ qua các hàng đã được lọc ra, nhưng các chức năng từ 1-11 sẽ 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ề sự khác biệt này, bạn hãy xem ví dụ sau đây:
Để tính 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:
Giả sử các hàng không liên quan trong bản đã được ẩn thủ công bằng cách dùng lệnh Hide Rows, nhưng bạn chỉ muốn tính tổng giá trị trong các hàng nhìn thấy, hãy lựa chọn SUBTOTAL 109:
Tiếp theo, để đếm ô không trống đã chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 có thể đếm các ô không trống nhìn thấy nếu có bất kỳ hàng ẩn nào trong dãy.
Ngoài ra, nếu dãy ô có chứa hai công thức SUBTOTAL, SUBTOTAL được lồng vào sau sẽ bị bỏ qua. Do đó, con số trong bảng sẽ không cần phải tính 2 lần.
Để dễ hình dung hãy nhìn hình dưới đây, công thức tính trung bình chính SUBTOTAL(1, C2:C10) bỏ qua kết quả của các SUBTOTAL khác.
Sự khác biệt giữa hàm SUBTOTAL và hàm SUM
Trong Excel, hàm SUM được định nghĩa là một phép tính tổng. Nhiệm vụ cơ bản của hàm SUM là tính tổng số những con số và tham số có trong các ô và dãy ô.
Để tìm hiểu sự khác biệt giữa hàm SUBTOTAL và hàm SUM, bạn hãy xem qua ví dụ dưới đây:
Trong trường hợp trên, nếu chúng ta thêm thao tác gì thì kết quả của hàm SUM là đúng.
Tuy nhiên, nếu chúng ta chỉ cần lọc những người ở bộ phận Kinh doanh. Khi đó kết quả của bảng tính vẫn sẽ là:
Vì vậy, chúng ta sẽ không thể sử dụng hàm SUM nữa mà thay vào đó là hàm SUBTOTAL với cú pháp:
=SUBTOTAL(9,C2;C6)
Mặc dù hàm tham chiếu số liệu từ C2:C6 nhưng kết quả chỉ được tính cho các ô C2, C3, C4 và các ô đã bị ẩn gồm C5, C6 sẽ không được tính. Đây chính là sự khác biệt rất lớn giữa SUM và SUBTOTAL
Cụ thể, sự khác biệt giữa hai hàm là:
Mục đích sử dụng:
- Hàm SUM: Chỉ tính tổng cố định hay còn được hiểu là tính tổng chung cho tất cả các đối tượng mà không phải tách thành tổng các nhóm nhỏ (Giá trị này không thay đổi khi thực hiện lệnh Filter). Khi lồng các hàm SUM với nhau sẽ bị tính hai lần.
- Hàm SUBTOTAL: Có thể tính tổng cố định và di động nghĩa là tính tổng riêng cho từng nhóm, giá trị sẽ thay đổi khi thực hiện lệnh Filter. Khi lồng các hàm SUBTOTAL với nhau sẽ không bị tính.
Cú pháp:
- Hàm SUM: = SUM (giá trị 1;giá trị 2;…)
- Hàm SUBTOTAL: =SUBTOTAL(function_num;ref1;ref2;…)
Trường hợp hay sử dụng trong kế toán:
- Hàm SUM: Dùng để tính tổng phát sinh nợ có trong sổ NKC hoặc tính khấu hao trong bảng phân bổ 211, 142,242…
- Hàm SUBTOTAL: Tính tổng giá trị các phát sinh đầu kỳ, trong kỳ, cuối kỳ của những tài khoản cấp 1 theo bảng cân đối tài khoản hay bảng cân đối kế toán… Ngoài ra còn được dùng để tính tổng số tiền tồn cuối ngày bao gồm tiền mặt, tiền gửi ngân hàng…
Trên đây là những gì cơ bản nhất về hàm SUBTOTAL bao gồm cú pháp và các ứng dụng thực tế. Hy vọng bạn có thể tận dụng tối đa những tính năng hữu ích mà hàm này mang lại để công việc được diễn ra hiệu quả hơn.