Hàm Subtotal được đánh giá là linh hoạt và ứng dụng rộng rãi hơn trong tính toán so với các hàm khác trong Excel. Vậy bạn đã biết hàm Subtotal có chức năng gì và được sử dụng như thế nào chưa? Theo dõi bài viết sau của Tinhocmos để hiểu rõ hơn nhé.

Bạn đang xem: Cách sử dụng hàm subtotal trong excel 2010


Tất tần tật về hàm Subtotal trong Excel

Hàm SUBTOTAL trong Excel là gì?

Trong tiếng Anh, SUBTOTAL được hiểu là tính tổng phụ. Trong Excel cũng vậy, SUBTOTAL được dùng với ý nghĩa là một hàm để tính tổng phụ của một bảng, một cơ sở dữ liệu. 

Sự ra đời của hàm SUBTOTAL này mang đến rất nhiều lợi ích cho người sử dụng, nhất là những người thường xuyên phải làm việc với các bảng tính toán. Điểm đặc biệt là thay vì được thiết kế với một nhiệm vụ riêng biệt, hàm này lại rất linh hoạt, có thể trả về SUM, AVERAGE, COUNT, MAX,…

Hàm tính tổng phụ được hỗ trợ trên các phiên bản Excel từ 2003 đến 2016.

Ưu điểm của hàm SUBTOTAL trong Excel

Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép

Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì sẽ tự động được bỏ qua để tránh tính trùng 2 lần.

Trong hình dưới, công thức tính trung bình chính SUBTOTAL(1, C2:C10) bỏ qua kết quả của công thức SUBTOTAL trong ô C3 và C10.

Hàm SUBTOTAL rất linh hoạt

Hàm SUBTOTALcó thể trả về nhiều kết quả như SUM, AVERAGE, COUNT, MAX,…

Hàm SUBTOTAL có thể tính toán bao gồm cả các giá trị bị ẩn và không ẩn dựa vào function_num được chọn

Công thức hàm SUBTOTAL

SUBTOTAL(function_num, ref1, ,…)

Trong đó:

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. 

Function_num: con số xác định chức năng thực hiện

Function_num có 2 loại sau:

1 -11: hàm Subtotal tính toán bao gồm cả các giá trị ẩn trong tập số liệu.101 – 111: hàm Subtotal chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).

Một số ví dụ về cách sử dụng hàm SUBTOTAL

Ví dụ 1: Tính tổng bằng công thức SUBTOTAL(9, ref1, ,…)

Bước 1: Bôi đen bảng chứa dữ liệu cần tính > Vào Home > Sort and Filter > Filter.

*

Bước 2: Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột “Tổ đội”, tick chọn “Chiến lược” và nhấn OK.

Bước 3: Bảng dữ liệu của tổ đội chiến lược được Excel lọc ra. Lúc này, tại ô cần lấy kết quả bạn nhập: “=SUBTOTAL(9,D2:D11)”

Cùng xem kết quả nào!

Ví dụ 2: Đếm ô không trống bằng công thức SUBTOTAL(3, ref1, ,…)

Giả sử công ty bạn có bảng dữ liệu thống kê như minh họa bên dưới. Nhiệm vụ của bạn là đếm số lượng nhân viên của tổ đội chiến lược đã có link công việc để tiện thống kê.

Bước 1: Bôi đen bảng chứa dữ liệu cần tính > Vào Home > Sort and Filter > Filter.

*

Bước 2: Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột “Tổ đội”, tick chọn “Chiến lược” và nhấn OK.

Bước 3: Bảng dữ liệu của tổ đội chiến lược được Excel lọc ra. Lúc này, tại ô cần lấy kết quả bạn nhập: “=SUBTOTAL(3,D4:D14)”

Cùng xem kết quả nào!

*

Các lỗi thường gặp khi sử dụng hàm SUBTOTAL

Dưới đây là một số lỗi và nguyên nhân lỗi khi sử dụng hàm SUBTOTAL trong Excel:

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

*

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

*

#NAME?: Cần kiểm tra lại lỗi sai chính tả.

*

Lời kết về hàm SUBTOTAL trong Excel

Như vậy, Tinhocmos đã nêu ra những điều cơ bản nhất mà bạn cần biết về hàm Subtotal trong Excel. Đây là một hàm rất linh hoạt, có tính ứng dụng cao và tiện lợi hơn so với các hàm có chức năng cụ thể. Vì vậy, nắm rõ thông tin và cách sử dụng hàm Subtotal là vô cùng cần thiết, đặc biệt đối với những bạn làm việc với bảng tính dữ liệu lớn. Chúc các bạn áp dụng thành công!

Bên cạnh đó, nếu bạn đang quan tâm đến những kỹ năng tin học văn phòng cần thiết và các khóa học tin học văn phòng như WordExcelPower

Tham khảo khóa học MOS Excel tại đây.

Thông tin liên hệ 

Trung tâm tin học văn phòng MOS – Viện Đào tạo và Tư vấn doanh nghiệp – Đại học Ngoại Thương

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, , ...).

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 1Tí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;;...)

Trong đó: Number1;... 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ả.

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, MAXNhư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 Sub
Total 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.

Xem thêm:

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.