Hàm Subtotal được reviews là linh hoạt với ứng dụng rộng rãi hơn trong đo lường so với những hàm khác trong Excel. Vậy chúng ta đã biết hàm Subtotal có chức năng gì và được sử dụng thế nào chưa? Theo dõi nội dung bài viết sau của Tinhocmos để nắm 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 vào Excel

Hàm SUBTOTAL trong Excel là gì?

Trong giờ Anh, SUBTOTAL được gọi là tính tổng phụ. Trong Excel cũng vậy, SUBTOTAL được sử dụng với chân thành và ý nghĩa là một hàm nhằm tính tổng phụ của một bảng, một cơ sở dữ liệu. 

Sự thành lập của hàm SUBTOTAL này đem về rất những lợi ích cho những người sử dụng, độc nhất là đầy đủ người tiếp tục phải thao tá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 trách nhiệm 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 những 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,… gồm chứa hàm SUBTOTAL thì sẽ tự động được bỏ qua để né tính trùng 2 lần.

Trong hình dưới, bí quyết tính trung bình bao gồm SUBTOTAL(1, C2:C10) quăng quật qua kết quả của bí quyết SUBTOTAL trong ô C3 và C10.

Hàm SUBTOTAL rất linh thiêng hoạt

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

Hàm SUBTOTAL hoàn toàn có thể tính toán bao hàm cả những giá trị bị ẩn cùng 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 hàng ô để tính tổng phụ. 

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

Function_num tất cả 2 các loại sau:

1 -11: hàm Subtotal tính toán bao hà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ị ko ẩn vào tập số liệu (không tính các giá trị ở mặt hàng ẩn).

Một số ví dụ về kiểu 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 & Filter > Filter.

*

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

Bước 3: Bảng dữ liệu của tổ đội kế hoạch được Excel thanh lọc ra. Lúc này, tại ô nên lấy tác dụng 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ởi công thức SUBTOTAL(3, ref1, ,…)

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

Bước 1: Bôi đen bảng chứa tài 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 sống tiêu đề cột “Tổ đội”, tick lựa chọn “Chiến lược” với nhấn OK.

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

Cùng xem tác dụng nào!

*

Các lỗi thường gặp mặt khi thực hiện hàm SUBTOTAL

Dưới đây là một số lỗi và tại sao lỗi khi thực hiện hàm SUBTOTAL vào Excel:

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

*

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

*

#NAME?: yêu cầu kiểm tra lại lỗi sai thiết yếu tả.

*

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

Như vậy, Tinhocmos đang nêu ra đa số điều cơ bản nhất nhưng mà bạn cần biết về hàm Subtotal vào Excel. Đây là 1 trong hàm rất linh thiêng hoạt, bao gồm tính vận dụng cao và tiện nghi hơn so với những hàm có tác dụ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 phải thiết, quan trọng đối với phần đa bạn thao tác làm việc với bảng tính tài liệu lớn. Chúc các bạn áp dụng thành công!

Bên cạnh đó, nếu khách hàng đang xem xét những năng lực tin học tập văn phòng cần thiết và những khóa học tập 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 công sở MOS – Viện Đào chế tạo ra và hỗ trợ tư vấn doanh nghiệp – Đại học tập Ngoại Thương

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

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


1. Hàm Subtotal là gì?

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

=SUBTOTAL(function_num, ref1, , ...).

Trong đó:

Function_num: một số từ 1 cho 11 hoặc 101 mang lại 111 xác minh hàm sẽ thực hiện khi tính toán tổng phụ của danh sách.Ref 1: phạm vi tham chiếu chọn cái tên đầu tiên mà bạn có nhu cầu tính tổng phụ.Ref2…: Phạm vi bạn muốn bao bao gồm là 2-254.

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

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

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

Tính tổng mang đến từng thông tin tài khoản cấp 1Tính tổng số tạo nên trong kỳ
Tính tổng chi phí tồn cuối ngày

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

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

- Tính tổng các số gây ra đầu kỳ, trong kì và thời điểm cuối kỳ của bảng bằng phẳng kế toán, các tài khoản cung cấp 1 bên trên bảng bằng phẳng kế toán...

- Tính tổng số dư cuối ngày mang đến sổ quỹ tiền mặt với 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 nhị hàm này nằm ở mục tiêu và cấu tạo 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 những phép toán SUBTOTAL): các số thông dụng là 1-11. Office 2003 với 2007 vẫn thêm 101-111. Số cửu được áp dụng thay mang lại hàm SUM.Đối số 1-11: Hàm này giám sát và đo lường tất cả những giá trị ẩn bằng phương pháp sử dụng những lệnh ẩn trong phạm vi dữ liệu.Đối số 101-111: Hàm quăng quật qua các giá trị bị ẩn bởi vì 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à những 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: lúc tính tổng hoặc quý hiếm ẩn (ẩn) theo nhóm. Ngoài ra, nếu bạn sử dụng lệnh thanh lọc trên cột này, chúng ta phải sử dụng chức năng này.

Đối cùng với hàm SUM: Chỉ tính tổng toàn bộ các giá chỉ trị, bất cứ bạn ẩn xuất xắc lọc dữ liệu. Ví như hàm SUM được lồng vào nhau, dữ liệu sẽ tiến hành tính hai lần.

5. Cú pháp hàm Subtotal

= SUBTOTAL(function_num; ref1;...)


*

Cấu trúc hàm subtotal vào excel


Trong đó:

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

Bảng giá chỉ trị những đối số:

 

*

Tức là nếu đối số là 1 thì bây giờ hàm subtotal vận độ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 sử dụng hàm Subtotal trong Excel

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

Bước 1: vào bảng tài liệu được đo lường và thống kê 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ố cửu là hàm SUM.F4:F9: Phạm vi dữ liệu tham chiếu.

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

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

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

Trong trường thích hợp đó, hoàn toàn có thể xảy ra lầm lẫn khi update cột STT.

Hàm SUBTOTAL giúp giải quyết vấn đề này trong quá trình sau.

Bước 1: Nhập cách làm sau vào ô thứ nhất mà bạn muốn đánh số.

Ví dụ: ô trước tiên là A2 và giá trị đối chiếu đánh số trước 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 xứng với hàm COUNTA trong hàm SUBTOTAL. $B$2:B2 là phạm vi của hàm COUNTA với vùng cất STT.

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

Kết đúng theo hàm Subtotal với Vlookup

Phương pháp:

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

Giới thiệu giá chỉ trị.

Hàm VLOOKUP: Trả về add ô khớp với khóa tra cứu vãn tương ứng. Lấy một ví dụ minh họa: thêm một bảng tài liệu mới như trong hình để so sánh và tìm điểm toán cơ bạn dạng cao nhất của group học sinh vì 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 đến môn Tin học tập Căn bản của giảng viên Nguyễn Á. Mã hàm mang lại hàm SUBTOTAL là 4. Điều này tức là hàm SUBTOTAL tìm giá chỉ trị cao nhất trong số các kết quả trả về.

7. để ý khi sử dụng hàm Subtotal vào excel
Nếu trong số đối số ref1, ref2,….có hàm subtotal không giống thì những hàm lồng này sẽ bị bỏ qua không được xem để né trường hợp giám sát và đo lường 2 lần.Đối số function_num nếu từ là 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả những giá trị ẩn trong tập số liệu (hàng ẩn). Đối số function_num giả dụ từ 101 mang lại 111 thì hàm SUBTOTAL chỉ đo lường và tính toán cho những giá trị ko ẩn trong tập số liệu (bỏ qua những giá trị ẩn).Hàm SUBTOTAL sẽ bỏ qua không tính toán tất cả những hàng bị ẩn bởi vì lệnh Filter (Auto Filter) không nhờ vào vào đối số function_num được dùng.Hàm SUBTOTAL chỉ để đo lường cho những cột số liệu theo hướng dọc, ko kể theo chiều ngang.Nếu tài liệu tham chiếu dạng 3-D thì hàm SUBTOTAL báo lỗi #VALUE!.Nếu bạn thực hiện giá trị khác 1-11 hoặc 101-111, Excel đã 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 theo chiều ngang như A1:D1 được sử dụng, các giá trị ẩn sẽ tự động hóa được chuyển vào (bất kể giá trị của đối số function_num). Đây là một trong những hạn chế của công dụng SUBTOTAL và quan trọng khắc phục được.SUBTOTAL rất có thể được sử dụng như một hàm lồng nhau như 1 phần của những 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 phương pháp SUBTOTAL nhưng chỉ cần một phạm vi ô để cách làm 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 tác dụ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 buộc phải 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 chỉnh của một dải ô không đựng số).

#Name: thương hiệu hàm SUBTOTAL bị sai thiết yếu tả.

9. Yếu tố hoàn cảnh sử dụng hàm subtotal trong công việc thực tế

Hàm Subtotal vào excel có tổng hợp tác dụng của tới hơn chục function_num

Thực tế vận dụng trong công việc đa số chỉ thực hiện một vài hàm thông thường như: Tính tổng, tìm quý giá trung bình, bé dại nhất, béo nhất,...Còn mấy hàm như var, sản phẩm thì ít dùng và do dự đến, chỉ tất cả những chúng ta làm những các bước liên quan mang đến phân tích xác suất hay toán học tinh vi sẽ đề nghị đến

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

Vậy thực hiện Subtotal gồm điều gì đặc sắc hơn những hàm riêng lẻ kia?

Điểm đặc biệt của hàm Subtotal khi sử dụng với Autofilter

Dưới đó 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 như muốn tính tổng, giá chỉ trị nhỏ tuổi nhất hay béo nhất dễ dàng ta chỉ việc dùng hàm SUM, MIN, MAXNhưng ta vẫn còn đó sự chắt lọc khác kia là cần sử 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 thiêng hoạt nhưng cũng rất khó áp dụng trong Excel với câu hỏi sử dụng những hằng số function để triệu tập lệnh. Nhưng ưu điểm của nó là chúng ta 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 đến nó. Và với công cụ cung cấp tính toán Subtotal, các bạn cũng có thể dễ dàng đo lường và tính toán với những yêu cầu phức tạp hơn so với việc sử dụng những phép thống kê giám sát thông thường nhưng mà hàm Subtotal hỗ trợ. 

Hàm SUBTOTAL cũng chính là 1 trong số những hàm đo lường và tính toán khá thông dụng với được sử dụng nhiều trong Excel, nó cũng là hàm khá thịnh hành trong kế toán, sát bên đó, việc kết phù 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 hoạt bát hơn.

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

Xem thêm: Linh Hon Cho Vu Dieu - Nguyên Nhân Gây Ho, Dấu Hiệu Và Cách Điều Trị

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