Cách sử dụng hàm Subtotal trong Excel

Hàm Subtotal đặc biệt có giá trị với Kế toán khi sử dụng Excel. Đây là một hàm tính tổng linh hoạt. Hàm SUBTOTAL trong Excel dùng để tính tổng các số trong dãy số có điều kiện giúp bạn có thể làm việc hiệu quả nếu chưa biết cách sử dụng hàm này.

Bài viết dưới đây, đội ngũ giảng viên khóa học kế toán thực hành tại Kế toán Lê Ánh sẽ hướng dẫn các bạn chi tiết ý nghĩa và cách sử dụng hàm Subtotal vận dụng vào kế toán trong Excel.

1. Hàm Subtotal là gì?

Hàm SUBTOTAL là một hàm tính toán tập hợp con của danh sách hoặc bảng dữ liệu tùy thuộc vào phép tính được chọn bởi đối số đầu tiên với cú pháp:

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

Trong đó:

• Function_num: Một số từ 1 đến 11 hoặc 101 đến 111 xác định hàm sẽ sử dụng khi tính toán tổng phụ của danh sách.
• Ref 1: phạm vi tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ.
• Ref2…: Phạm vi bạn muốn bao gồm là 2-254.

2. Ý nghĩa hàm Subtotal vận dụng vào kế toán trong Excel

Hàm Subtotal là hàm tính tổng cho một nhóm trong danh sách tùy theo đối số.

Trong kế toán, có thể vận dụng hàm Subtotal để tính các tổng sau:

• Tính tổng cho từng tài khoản cấp 1
• Tính tổng số phát sinh trong kỳ
• Tính tổng tiền tồn cuối ngày

Tham khảo: Khóa Học Kế Toán Tổng Hợp Thực Hành

3. Những ứng dụng của hàm Subtotal

- Tính tổng các số phát sinh đầu kỳ, trong kì và cuối kỳ của bảng cân đối kế toán, các tài khoản cấp 1 trên bảng cân đối kế toán...

- Tính tổng số dư cuối ngày cho sổ quỹ tiền mặt và tiền gửi ngân hàng.

4. Sự khác nhau giữa hàm SUM và Subtotal

*Sự khác biệt giữa hai hàm này nằm ở mục đích và cấu trúc của chúng.

Hàm SUBTOTAL

Cú pháp hàm SUBTOTAL: =SUBTOTAL(function_num;ref1;ref2;…)

Trong đó:

• Function_num là (một số điển hình cho các phép toán SUBTOTAL): Các số phổ biến là 1-11. Office 2003 và 2007 đã thêm 101-111. Số 9 được sử dụng thay cho hàm SUM.
• Đối số 1-11: Hàm này tính toán tất cả các giá trị ẩn bằng cách sử dụng các lệnh ẩn trong phạm vi dữ liệu.
• Đối số 101-111: Hàm bỏ qua các giá trị bị ẩn bởi lệnh hide.
• Ref1;ref2;...: địa chỉ tham chiếu.

Hàm SUM

Cú pháp hàm SUM: =SUM(Number1;[Number2];...)

Trong đó: Number1;[Number2]... Number N là Các giá trị được tham chiếu từ cột hoặc do bạn nhập vào.

*Mục đích sử dụng

Đối với hàm SUBTOTAL: khi tính tổng hoặc giá trị ẩn (ẩn) theo nhóm. Ngoài ra, nếu bạn sử dụng lệnh lọc trên cột này, bạn phải sử dụng chức năng này.

Đối với hàm SUM: Chỉ tính tổng tất cả các giá trị, bất kể bạn ẩn hay lọc dữ liệu. Nếu hàm SUM được lồng vào nhau, dữ liệu sẽ được tính hai lần.

5. Cú pháp hàm Subtotal

= SUBTOTAL(function_num; ref1;...)

Cấu trúc hàm subtotal trong excel

Trong đó:

• Function_num: Là các con số từ 1 đến 11 (hay có thêm 101 đến 111 trong phiên bản Excel 2003, 2007) qui định hàm nào sẽ được dùng để tính toán trong subtotal
• Ref1, ref2,...là các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó.

Bảng giá trị các đối số:

Tức là nếu đối số là 1 thì lúc này hàm subtotal hoạt động như hàm average, đối số là 2 hàm subtotal hoạt động như hàm count.

Trong kế toán, thường dùng đối số 9 để tính tổng.

6. Cách dùng hàm Subtotal trong Excel

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

Bước 1: Trong bảng Dữ liệu được tính toán trong Excel, hãy nhập hàm =SUBTOTAL(9,F4:F9) vào ô tham chiếu mà bạn muốn kết quả xuất hiện.

Giải thích:

• SUBTOTAL: Lệnh hàm tính.
• 9: Là Function_num. Số 9 là hàm SUM.
• F4:F9: Phạm vi dữ liệu tham chiếu.

Bước 2: Nhấn Enter để xem kết quả.

Sử dụng hàm subtotal để đánh số thứ tự

Nhiều khi bạn đã hoàn thành việc đánh số hàng dữ liệu (STT) nhưng lại muốn sửa, thêm bớt hàng.

Trong trường hợp đó, có thể xảy ra nhầm lẫn khi cập nhật cột STT.

Hàm SUBTOTAL giúp giải quyết vấn đề này trong các bước sau.

Bước 1: Nhập công thức sau vào ô đầu tiên mà bạn muốn đánh số.

Ví dụ: ô đầu tiên là A2 và giá trị so sánh đánh số đầu tiên là B2. Nhập hàm vào ô A2 như sau:

=SUBTOTAL(3,$B$2:B2)

Trong đó: Số 3 là đối số tương ứng với hàm COUNTA trong hàm SUBTOTAL. $B$2:B2 là phạm vi của hàm COUNTA và vùng chứa STT.

Bước 2: Sao chép công thức và dán vào ô tiếp theo. Tiết kiệm thời gian bằng cách nhấn và thả chuột.

Kết hợp hàm Subtotal và Vlookup

Phương pháp:

SUBTOTAL(mã hàm, VLOOKUP(khóa tìm kiếm, phạm vi, chỉ mục, [đã sắp xếp]))

Giới thiệu giá trị.

Hàm VLOOKUP: Trả về địa chỉ ô khớp với khóa tra cứu tương ứng. Ví dụ minh họa: Thêm một bảng dữ liệu mới như trong hình để so sánh và tìm điểm toán cơ bản cao nhất của nhóm học sinh do Giảng viên Nguyễn Á phụ trách.

=SUBTOTAL(4, VLOOKUP(H2, B2:E7, 4, FALSE), VLOOKUP(H5, B2:E7, 4, FALSE), VLOOKUP(H6, B2:E7, 4, FALSE))

Mô tả: Hàm VLOOKUP trả về giá trị điểm cho môn Tin Học Căn Bản của giảng viên Nguyễn Á. Mã hàm cho hàm SUBTOTAL là 4. Điều này có nghĩa là hàm SUBTOTAL tìm giá trị cao nhất trong số các kết quả trả về.

7. Chú ý khi sử dụng hàm Subtotal trong excel

• Nếu trong các đối số ref1, ref2,….có hàm subtotal khác thì các hàm lồng này sẽ bị bỏ qua không được tính để tránh trường hợp tính toán 2 lần.
• Đối số function_num nếu từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu (hàng ẩn). Đối số function_num nếu từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (bỏ qua các giá trị ẩn).
• Hàm SUBTOTAL sẽ bỏ qua không tính toán tất cả các hàng bị ẩn bởi lệnh Filter (Auto Filter) không phụ thuộc vào đối số function_num được dùng.
• Hàm SUBTOTAL chỉ để tính toán cho các cột số liệu theo chiều dọc, không tính theo chiều ngang.
• Nếu dữ liệu tham chiếu dạng 3-D thì hàm SUBTOTAL báo lỗi #VALUE!.
• Nếu bạn sử dụng giá trị khác 1-11 hoặc 101-111, Excel sẽ trả về lỗi #VALUE! Điều này cũng áp dụng cho các tham chiếu ô 3D (nếu cùng một ô được tham chiếu trên nhiều trang tính trong phạm vi).
• Nếu phạm vi ô nằm ngang như A1:D1 được sử dụng, các giá trị ẩn sẽ tự động được đưa vào (bất kể giá trị của đối số function_num). Đây là một hạn chế của chức năng SUBTOTAL và không thể khắc phục được.
• SUBTOTAL có thể được sử dụng như một hàm lồng nhau như một phần của các hàm khác, chẳng hạn như các hàm chứa hàm IF.
• Excel cho phép tối đa 254 phạm vi ô trong công thức SUBTOTAL nhưng chỉ cần một phạm vi ô để công thức hoạt động.

8. Một số lỗi cơ bản thường xảy ra khi tính hàm SUBTOTAL trong Excel.

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

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

#Name: Tên hàm SUBTOTAL bị sai chính tả.

Xem thêm: Hàm Vlookup trong Excel - Ý nghĩa và cú pháp

9. Thực trạng sử dụng hàm subtotal trong công việc thực tế

Hàm Subtotal trong excel gồm tổng hợp chức năng của tới hơn chục function_num

Thực tế áp dụng trong công việc đa số chỉ sử dụng một vài hàm thông thường như: Tính tổng, tìm giá trị trung bình, nhỏ nhất, lớn nhất,...Còn mấy hàm như var, product thì ít dùng và không biết đến, chỉ có những bạn làm những công việc liên quan đến phân tích xác suất hay toán học phức tạp sẽ cần đến

Nếu chỉ đơn thuần để tính tổng, tìm giá trị nhỏ nhất, lớn nhất thì người ta thường sử dụng các hàm đơn lẻ như SUM, MAX, MIN dễ sử dụng và ít đối số

Vậy sử dụng Subtotal có điều gì nổi trội hơn các hàm đơn lẻ kia?

Điểm nổi trội của hàm Subtotal khi dùng với Autofilter

Dưới đây là danh sách 2 nhà cung cấp A, B với số lượng khác nhau:

• Bình thường nếu muốn tính tổng, giá trị nhỏ nhất hay lớn nhất đơn giản ta chỉ cần dùng hàm SUM, MIN, MAX
• Nhưng ta vẫn còn sự lựa chọn khác đó là dùng hàm subtotal với các function_num là 9 (sum), 5 (min), 4 (max)

Hàm SUBTOTAL trong Excel là một hàm rất linh hoạt nhưng cũng khá khó sử dụng trong Excel với việc sử dụng các hằng số function để triệu tập lệnh. Nhưng ưu điểm của nó là bạn cũng có thể sửa lại phép tính một cách dễ dàng hơn bằng cách thay lại hằng số function cho nó. Và cùng với công cụ hỗ trợ tính toán Subtotal, các bạn có thể dễ dàng tính toán với các yêu cầu phức tạp hơn so với việc sử dụng các phép tính toán thông thường mà hàm Subtotal hỗ trợ. 

Hàm SUBTOTAL cũng là 1 trong những hàm tính toán khá thông dụng và được sử dụng nhiều trong Excel, nó cũng là hàm khá phổ biến trong kế toán, bên cạnh đó, việc kết hợp với các hàm như hàm Vlookup, hàm Hlookup giúp cho hàm SubTotal trở lên linh hoạt hơn.

Hàm SUBTOTAL cho phép bạn nhanh chóng phân tích các tập dữ liệu mà không cần dựa vào các bảng tổng hợp phức tạp hơn. Hy vọng những chia sẻ từ bài viết trên đây của Kế Toán Lê Ánh sẽ giúp ích cho công việc của bạn.

Tags: Subtotal excel 2010, hàm subtotal 103, hàm subtotal bị lỗi, cách sử dụng data subtotal, hàm subtotal khác gì hàm sum, hàm subtotal nâng cao, bỏ subtotal trong excel, lọc và tính tổng trong excel

Kế Toán Lê Ánh - Nơi đào tạo kế toán thực hành uy tín nhất hiện nay, đã tổ chức thành công rất nhiều khóa học nguyên lý kế toán, khóa học kế toán tổng hợp online/ offline, khóa học kế toán thuế chuyên sâu, ... và hỗ trợ kết nối tuyển dụng cho hàng nghìn học viên.

Nếu như bạn muốn tham gia các khóa học kế toán của trung tâm Lê Ánh thì có thể liên hệ hotline tư vấn: 0904.84.8855/ Mrs Lê Ánh