Hàm Subtotal đặc biệt có mức giá trị với kế toán tài chính khi thực hiện Excel. Đây là một hàm tính tổng linh hoạt. Hàm SUBTOTAL trong Excel dùng để làm tính tổng những số trong dãy số có điều kiện giúp chúng ta cũng 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ạn đang xem: Sử dụng hàm subtotal có điều kiện

Bài viết bên dưới đây, đội hình giảng viên khóa học tập kế toán thực hành tại Kế toán Lê Ánh đang hướng dẫn các bạn chi tiết ý nghĩa sâu sắc 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à 1 trong hàm giám sát tập hợp nhỏ của danh sách hoặc bảng tài liệu tùy trực thuộc vào phép tính được chọn vì chưng đối số thứ nhất với cú pháp:

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

Trong đó:

Function_num: một trong những từ 1 đến 11 hoặc 101 mang lại 111 khẳng định hàm sẽ thực hiện khi đo lường và thống kê tổng phụ của danh sách.Ref 1: phạm vi tham chiếu được lấy 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 tài chính trong Excel

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

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

Tính tổng đến từng tài khoản cấp 1Tính tổng số gây ra trong kỳ
Tính tổng chi phí 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 áp dụng của hàm Subtotal

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

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

4. Sự không giống nhau giữa hàm SUM và Subtotal

*Sự khác hoàn toàn giữa hai hàm này nằm ở mục tiêu 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ố thịnh hành là 1-11. Office 2003 với 2007 vẫn thêm 101-111. Số cửu được thực hiện thay mang đến hàm SUM.Đối số 1-11: Hàm này giám sát tất cả những 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 vứt qua những giá trị bị ẩn vì chưng lệnh hide.Ref1;ref2;...: add 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ừ bỏ cột hoặc do bạn nhập vào.

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

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

Đối với hàm SUM: Chỉ tính tổng toàn bộ các giá bán trị, bất kể bạn ẩn tốt lọc dữ liệu. Ví như 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ừ là một đến 11 (hay có thêm 101 mang đến 111 vào phiên phiên bản Excel 2003, 2007) hình thức hàm nào sẽ được dùng để giám sát và đo lường trong subtotal
Ref1, ref2,...là các vùng địa chỉ tham chiếu mà bạn muốn thực hiện nay phép tính trên đó.

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

 

*

Tức là nếu như đối số là 1 trong những thì hôm nay hàm subtotal chuyển động như hàm average, đối số là 2 hàm subtotal chuyển động như hàm count.

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

6. Cách cần sử dụng hàm Subtotal trong Excel

Cách sử dụng hàm subtotal để tính tổng

Bước 1: vào bảng dữ liệu được giám sát và đo lường trong Excel, hãy nhập hàm =SUBTOTAL(9,F4:F9) vào ô tham chiếu mà bạn có nhu cầu kết trái xuất hiện.

*

Giải thích:

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

Bước 2: thừa nhận Enter để xem kết quả.

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

Nhiều khi bạn đã xong việc đánh số hàng tài 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 nhầm lẫn khi update cột STT.

Hàm SUBTOTAL giúp xử lý vấn đề này trong quá trình sau.

Bước 1: Nhập bí quyết sau vào ô trước tiên mà bạn muốn đánh số.

Ví dụ: ô trước tiên là A2 và giá trị đối chiếu đánh số thứ nhất là B2. Nhập hàm vào ô A2 như sau:

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

Trong đó: Số 3 là đối số khớp ứ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: xào nấu công thức cùng dán vào ô tiếp theo. Tiết kiệm ngân sách và chi phí thời gian bằng cách nhấn với thả chuột.

Kết thích hợp hàm Subtotal với Vlookup

Phương pháp:

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

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

Hàm VLOOKUP: Trả về địa chỉ cửa hàng ô khớp cùng với khóa tra cứu tương ứng. Lấy ví dụ như minh họa: thêm một bảng tài liệu mới như vào hình để đối chiếu và tìm điểm toán cơ bạn dạng 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ề quý hiếm điểm mang đến 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 tức là hàm SUBTOTAL tìm giá trị tối đa trong số các hiệu 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ác thì những hàm lồng này sẽ bị bỏ qua không được xem để né trường hợp đo lường và thống kê 2 lần.Đối số function_num nếu từ 1 đến 11 thì hàm SUBTOTAL tính toán bao hàm cả những giá trị ẩn trong tập số liệu (hàng ẩn). Đối số function_num giả dụ từ 101 cho 111 thì hàm SUBTOTAL chỉ giám sát và đo lường cho những giá trị không ẩn trong tập số liệu (bỏ qua những giá trị ẩn).Hàm SUBTOTAL sẽ bỏ lỡ không đo lường và thống kê tất cả những hàng bị ẩn vày lệnh Filter (Auto Filter) không phụ thuộc 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, 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 áp dụng giá trị không giống 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 ô 3 chiều (nếu và một ô được tham chiếu trên những 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 được đưa vào (bất kể quý hiếm của đối số function_num). Đây là một hạn chế của công dụng SUBTOTAL và quan yếu khắc phục được.SUBTOTAL rất có thể được áp dụng như một hàm lồng nhau như một phần của các hàm khác, ví dụ điển hình như những hàm chứa hàm IF.Excel có thể chấp nhận được tối đa 254 phạm vi ô trong cách làm SUBTOTAL nhưng chỉ việc một phạm vi ô để phương pháp hoạt động.8. Một vài 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 tầm 1-11 hoặc 101-111 hoặc mã định tác dụng có tham chiếu (ref) là tham chiếu 3D.

#DIV/0!: xảy ra khi đề xuất chia một tổng ví dụ cho 0 (chẳng hạn như tính quý giá trung bình hoặc độ lệch chuẩn chỉnh của một dải ô không chứa số).

#Name: tên hàm SUBTOTAL bị sai bao gồm tả.

9. Yếu tố hoàn cảnh sử dụng hàm subtotal trong các bước thực tế

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

Thực tế áp dụng trong quá trình đa số chỉ áp dụng một vài hàm thường thì như: Tính tổng, tìm quý hiếm trung bình, nhỏ tuổi nhất, mập nhất,...Còn mấy hàm như var, sản phẩm thì ít sử dụng và lần khần đến, chỉ có những các bạn làm những quá trình liên quan mang đến phân tích xác suất hay toán học tinh vi sẽ buộc phải đến

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

Vậy sử dụng Subtotal có điều gì đặc sắc hơn các hàm đơn nhất kia?

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

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

Bình thường nếu như muốn tính tổng, giá trị nhỏ nhất hay mập nhất dễ dàng và đơ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 kia là sử dụng hàm subtotal với những function_num là 9 (sum), 5 (min), 4 (max)

*

Hàm SUBTOTAL trong Excel là một hàm rất thiêng hoạt nhưng cũng khá khó áp dụng trong Excel với việc sử dụng những hằng số function để triệu tập lệnh. Nhưng ưu thế của nó là chúng ta cũng có thể sửa lại phép tính một cách thuận tiện hơn bằng cách thay lại hằng số function mang đến nó. Và cùng 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ới các yêu cầu tinh vi hơn so với vấn đề sử dụng những phép đo lường và tính toán thông thường mà hàm Subtotal hỗ trợ. 

Hàm SUBTOTAL cũng chính là 1 trong số những hàm giám sát khá thông dụng cùng được sử dụng nhiều vào Excel, nó cũng là hàm khá thông dụng trong kế toán, kề bên đó, vấn đề kết hợp với các hàm như hàm Vlookup, hàm Hlookup hỗ trợ cho hàm Sub
Total trở lên linh hoạt hơn.

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

Kế Toán Lê Ánh - Nơi đào tạo kế toán thực hành thực tế uy tín tốt nhất hiện nay, đã tổ chức thành công không ít 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 tập kế toán thuế chuyên sâu, ... Và cung ứng kết nối tuyển chọn dụng cho hàng trăm học viên.

Em bao gồm 2 cột A cùng B A BS01 1S01 0S02 1S03 0E biết hàm subtotal là tính giá trị được hiện ra và bên cạnh giá trị ẩn.Vậy giờ e mong Count cột A tuy vậy thêm điều kiện cột B phải là 1 trong thì làm thế nào ạ?
Em cảm ơn!
Reactions:tranvananh
*

ndu96081631Huyền thoại GPE
*

Em có 2 cột A và B A BS01 1S01 0S02 1S03 0E biết hàm subtotal là tính quý giá được chỉ ra và quanh đó giá trị ẩn.Vậy giờ đồng hồ e mong muốn Count cột A dẫu vậy thêm điều kiện cột B phải là một thì làm như thế nào ạ?
Em cảm ơn!
Làm được với đk là các bạn đưa file thật lên đây(mất công làm cho xong, bạn ráp vào file thật sai kết quả lại la làng)
Reactions:tn06065
*

Làm được với điều kiện là bạn đưa file thật lên đây(mất công làm xong, bạn ráp vào file thật sai tác dụng lại la làng)
Em tất cả 2 cột A cùng B A BS01 1S01 0S02 1S03 0E biết hàm subtotal là tính cực hiếm được hiển thị và xung quanh giá trị ẩn.Vậy giờ e ý muốn Count cột A tuy nhiên thêm đk cột B phải là một trong những thì làm ra làm sao ạ?
Em cảm ơn!
Dạ em cám ơn ạ. Em demo ok rồi ạ. Nhưng hoàn toàn có thể giải thích mang đến e thêm một chút k ạ?
E k thấy đoạn hàm làm sao là để đk cột B=1 ạ
Em cám ơn thầy!
Thầy ơi E vừa sửa thử hàng đầu thành 2 thì nó nhảy thêm một thầy ạ. E đang phải đếm chiếc mã sp mà. Nếu điều kiện của e cơ mà >0 thì cột B có bao nhiêu số nó đếm hết thầy ạhình như nó tính tổng của cột B chứ k buộc phải đếm ạ
Thầy ơi E vừa sửa thử hàng đầu thành 2 thì nó nhảy thêm một thầy ạ. E đang đề nghị đếm loại mã sp mà. Nếu điều kiện của e mà >0 thì cột B bao gồm bao nhiêu số nó đếm không còn thầy ạhình như nó tính tổng của cột B chứ k nên đếm ạ
Muốn điều kiện B=1 thì sửa *$B$2:$B$12 thành *($B$2:$B$12=1)Muốn đk B0 thì sửa *$B$2:$B$12 thành *($B$2:$B$120)Muốn đk B>0 thì sửa *$B$2:$B$12 thành *($B$2:$B$12>0)vân vân... Và... Mây mây...
Muốn đk B=1 thì sửa *$B$2:$B$12 thành *($B$2:$B$12=1)Muốn điều kiện B0 thì sửa *$B$2:$B$12 thành *($B$2:$B$120)Muốn đk B>0 thì sửa *$B$2:$B$12 thành *($B$2:$B$12>0)vân vân... Và... Mây mây...
Xin chào cả nhà. Tôi có vấn đề cần được hỗ trợ với hàm tính tổng sau thời điểm Fillter. Rõ ràng là công thêm tổng các giá trị sinh sống cột "B" sau khoản thời gian Fillter sinh hoạt cột "C" theo những điều khiếu nại ở cột "A". Chi tiết cụ thể như file gắn kèm.Xin cảm ơn anh chị em trước, tôi đi lo chống bão số 9 (Usagi) dòng đã.

Xem thêm: Top 10 quán ăn rẻ ở hà nội ngon bổ rẻ, 17 địa chỉ ăn vặt ở hà nội ngon bổ rẻ


Xin xin chào cả nhà. Tôi có sự việc cần được trợ giúp với hàm tính tổng sau khoản thời gian Fillter. Cụ thể là sẽ tính tổng những giá trị ngơi nghỉ cột "B" sau khoản thời gian Fillter ở cột "C" theo những điều khiếu nại ở cột "A". Chi tiết cụ thể như file gắn thêm kèm.Xin cảm ơn cả nhà trước, tôi đi lo chống bão số chín (Usagi) loại đã.
Mình không hiểu biết công thức này lắm yêu cầu đã tách từng công thức bé dại ra xem thì thấy cách làm SUBTOTAL(3, OFFSET($A$2,ROW($1:$100),)) cho kết quả bằng 1 dẫu vậy khi COUNT trong subtotal thì tác dụng phải lớn hơn 1 chứ nhỉ? Bạn lý giải giúp bản thân với. Cảm ơn các bạn nhiều
Mình không hiểu công thức này lắm phải đã bóc từng công thức bé dại ra xem thì thấy bí quyết SUBTOTAL(3, OFFSET($A$2,ROW($1:$100),)) cho kết quả bằng 1 dẫu vậy khi COUNT vào subtotal thì công dụng phải to hơn 1 chứ nhỉ? Bạn giải thích giúp bản thân với.Cảm ơn bạn nhiều
Em vẫn chưa hiểu lắm ạ. Công thức SUBTOTAL(3, OFFSET($A$2,ROW($1:$100),)) có nghĩa là đếm số ô ko trống vào mảng A3:A100, vậy công dụng là 18 hoặc 1 số lớn hơn 1 nhưng tại sao nó chỉ ra công dụng 0, 1 vậy ạ. Em cảm ơn