Chúng ta đã biết hàm SUM trong Excel chính là một hàm quen thuộc được sử dụng để tính tổng trong Excel. Nhưng có 1 hàm tốt hơn để làm việc đó, chính là hàm SUBTOTAL. Hãy cùng 123job tìm hiểu qua bài viết này.

Bạn đã biết hàm SUBTOTAL trong Excel có chức năng gì hay chưa? Công thức của hàm SUBTOTAL sẽ như thế nào? Ví dụ cho hàm SUBTOTAL ra sao và áp dụng của hàm SUBTOTAL trong cuộc sống như thế nào? Để có thể trả lời 4 câu hỏi trên, dưới đây 123job sẽ hướng dẫn các bạn cách sử dụng hàm này và các thủ thuật excel cụ thể như sau.

I. Hàm SUBTOTAL trong lĩnh vực kế toán 

1. Công thức tính của hàm SUBTOTAL 

Công thức tiếng Anh của hàm SUBTOTAL sẽ như sau: =SUBTOTAL (function_num, ref1, [ref2], …)

Đơn giản của công thức hàm Subtotal là: =SUBTOTAL(a,b,c,..)

Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc.

Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc.

Trong đó: 

  • Function_num: Một số chỉ định hàm nào sẽ dùng để thực hiện tính tổng phụ trong danh sách. 
  • Chức năng có sẵn
  • Hàm SUBTOTAL trong Excel được điều khiển bởi đối số function_num, sẽ được cung cấp dưới dạng giá trị số. 

2. Công dụng hàm SUBTOTAL trong kế toán 

Tính tổng dãy ô có điều kiện như sau:

  • Tính tổng phát sinh có trong kỳ
  • Tính tổng trong từng tài khoản là cấp 1
  • Tính tổng tiền tốn ở trong kỳ

II. Cách sử dụng hàm SUBTOTAL trong Excel 

1. Tính tổng các hàng được lọc 

Để hiểu rõ hơn về kiểu tính tổng này và thủ thuật excel bạn hãy tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel của 123job. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là: =SUBTOTAL(9,pham_vi)
Và pham_vi ở đây chính là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.

2. Đếm các ô được lọc không trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hay hàm SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn cần phải sử dụng SUBTOTAL 103 để đếm chính xác những ô không trống nhìn thấy.

Bảng dữ liệu
Chẳng hạn đối với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hay SUBTOTAL 103 sẽ cho ra hai kết quả khác nhau với công thức này.

Ẩn hàng dữ liệu
Chúng ta hãy nhập công thức và khi đó thủ thuật Excel sẽ tự động hiển thị bộ chức năng để bạn có thể chọn mà không cần phải ghi nhớ.

Công thức hàm SUBTOTAL

  • SUBTOTAL 3: Kết quả khi sử dụng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.
  • SUBTOTAL 103: Còn đối với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy sau đó bỏ qua hàng ẩn.

3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau

Chẳng hạn chúng ta hãy 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) rồi cho kết quả là 19.

SUBTOTAL 1
Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) ra kết quả là 38.

Tính trung bình
Nhưng 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 sẽ có công thức hàm Subtotal =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã được tính trước đó.

III. Ví dụ về công thức sử dụng hàm SUBTOTAL 

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

Như bạn đã biết, hàm SUBTOTAL được chấp nhận 2 bộ hàm số: 1-11 và 101-111. Cả hai sẽ đều bỏ qua các hàng được lọc, tuy nhiên 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 các ví dụ thủ thuật Excel sau.

Tuy nhiên nếu đã ẩn các mục không liên quan theo cách thủ công bằng cách dùng lệnh Hide Rows từ thẻ Home > Cells group > Format > Hide & Unhide hay bằng cách kích chuột phải vào các dòng, rồi chọn vào lệnh Hide.
Bây giờ nếu muốn tính 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:

Áp dụng ví dụ vào công thức hàm Subtotal

Áp dụng ví dụ vào công thức hàm Subtotal

Ví dụ: Để có thể đếm các ô được lọc không trống bạn có thể dùng công thức SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên 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 đượ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 những giá trị trong các hàng ẩn, tuy nhiên không ở các cột ẩn . Ví dụ: Nếu bạn sử dụng công thức như hàm SUBTOTAL(109,A1:E1) để tính tổng những số trong phạm vi ngang, việc ẩn một cột sẽ không ảnh hưởng đến hàm SUBTOTAL trong Excel.

2. 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 ra một báo cáo tóm tắt hay 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 tuy nhiên không có không gian cho tất cả mọi thứ, cách tiếp cận sau đây có thể chính 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ư là: Total, Max, Min …
  • Bước 2: Trong một ô bên cạnh của danh sách thả xuống, hãy nhập công thức hàm IF trong hàm Excel lồng nhau với các hàm SUBTOTAL được nhúng tương ứng đối với các tên hàm trong danh sách thả xuống.

Ví dụ: Giả sử những giá trị cho SUBTOTAL nằm trong các ô C2:C16 và danh sách thả xuống trong ô A17 sẽ 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ờ, còn tùy thuộc vào chức năng nào mà người sử 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

IV. Sự khác nhau cơ bản của hàm SUBTOTAL và SUM mà kế toán nên biết 

Như chúng ta đã biết hàm Subtotal và hàm Sum chính là hai hàm có thể nói là được sử dụng với tần suất cao nhất. Hàm sum thì chắc hẳn các bạn đều biết, tuy nhiên chỉ có hàm Subtotal nhiều bạn kế toán mới ra trường sẽ còn lạ lẫm nhưng nó lại là một trong những hàm được sử dụng vô cùng linh hoạt trong trong Excel nói chung và lĩnh vực kế toán nói riêng. 

Sự khác nhau

Hàm Sum 

Hàm Subtotal


Mục đích

  • Chỉ tính tổng cố định hoặc còn được hiểu đó là tính tổng chung cho tất cả các đối tượng mà không tách thành tổng các nhóm nhỏ (Giá trị này không thể thay đổi khi thực hiện lệnh Filter).
  • Khi lồng các hàm Sum với nhau thì số liệu sẽ bị tính là 2 lần.
  • Có thể tính tổng cố định và di động có nghĩa là tính tổng riêng trong 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 thì số liệu sẽ không bị tính 2 lần.

 


Cú pháp

hàm SUM = (number1;[number2];…) trong đó:

  • Giới hạn đối số đến 255 và đối số đầu tiên đó là bắt buộc.
  • Các đối số có thế là giá trị riêng lẻ, tham chiếu hoặc phạm vi ô hay kết hợp cả ba.
  • Nếu đối số là mảng hay tham chiếu, chỉ các các số trong mảng hay tham chiếu đó mới được tính. Các ô trống, giá trị lôgic, văn bản hoặc văn bản trong mảng hoặc tham chiếu bị bỏ qua.
  • Nếu bất kỳ đối số nào là giá trị lỗi, hoặc bất kỳ đối số nào là văn bản mà không thể diễn dịch thành số được, Excel sẽ hiển thị một lỗi.

hàm SUBTOTAL =(function_num;ref1;ref2;…) trong đó:

  • Function_num là (con số đại diện cho phép toán số liệu trong hàm subtotal): các con số thường sử dụng đó là từ 1 đến 11. Ngoài ra trong office 2003 và 2007 sẽ có thêm 101 đến 111. Số 9 được dùng để thay thế cho hàm Sum.

 + Đối số từ 1-11: hàm này sẽ được tính tất cả các giá trị ẩn bằng lệnh Hide ở trong vùng dữ liệu.
+ Đối số từ 101-111: hàm này không tính đến các giá trị bị ẩn bằng lênh Hide.
+ Ngoài ra, hàm Subtotal sẽ không tính các giá trị bị ẩn bằng lệnh Filter
+ Chỉ được dùng khi muốn tính toán giá trị theo cột.
+ Chỉ tính toán giá trị 2-D
+Ref1; ref2;…: vùng địa chỉ tham chiếu.

Trường hợp hay sử dụng

(*)
 

  • Tính tổng phát sinh nợ có trong sổ NKC
  • Tính khấu hao có trong bảng phân bổ 211, 142, 242…
  • Tính tổng những số đang phát sinh đầu kỳ, trong kỳ, cuối kỳ của Tài khoản cấp 1 trên bảng cân đối tài khoản, bảng cân đối kế toán…
  • Tính tổng số tiền tồn trong cuối ngày trong sổ tiền mặt, tiền gửi ngân hàng…

 


V. Kết luận 

Như vậy là 123job đã giới thiệu cho các bạn về cách sử dụng hàm SUBTOTAL trong Excel, mong rằng bài viết này sẽ giúp ích được nhiều cho các bạn trong lĩnh vực kế toán cũng như các thủ thuật Excel. Chúc các bạn có một buổi học Excel tin học văn phòng thú vị nhé.