Chúng ta đã biết rằng hàm SUM trong Excel là một hàm quen thuộc được sử dụng để tính tổng trong Excel. Nhưng có một chức năng tốt hơn để làm điều đó, chức năng SUBTOTAL. Hãy cùng 123job tìm hiểu qua bài viết này.
Bạn có biết hàm SUBTOTAL trong Excel có chức năng gì không? Công thức của hàm SUBTOTAL sẽ như thế nào? Lấy ví dụ cho hàm SUBTOTAL và hàm SUBTOTAL được ứng dụng như thế nào trong cuộc sống? Để có thể trả lời được 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. Chức năng SUBTOTAL trong lĩnh vực kế toán
1. Công thức tính PHỤ TÙNG. hàm số
Công thức tiếng Anh của hàm SUBTOTAL sẽ giống như sau: = SUBTOTAL (function_num, ref1, [ref2]…)
Tính đơn giản của công thức hàm Tổng phụ là: = SUBTOTAL (a, b, c, ..)
Hàm SUBTOTAL được thiết kế để tính toán các cột dữ liệu theo chiều dọc.
Trong đó:
Function_num: Một số chỉ định hàm nào sẽ sử dụng để tính tổng phụ của danh sách.
Các chức năng có sẵn
Hàm SUBTOTAL trong Excel được điều khiển bởi đối số function_num, đối số này sẽ được cung cấp dưới dạng giá trị số.
2. Công dụng của hàm SUBTOTAL trong kế toán
Tính tổng của dải ô có điều kiện như sau:
Tính tổng số phát sinh trong kỳ
Tổng trong mỗi tài khoản là cấp 1
Tính tổng chi phí trong kỳ
II. Cách sử dụng hàm SUBTOTAL trong Excel
1. Tính tổng các hàng đã lọc
Để hiểu rõ hơn về kiểu tính tổng này và các thủ thuật excel, mời các bạn tham khảo bài viết Tính tổng giá trị của một 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 là vùng bạn muốn tính tổng sau khi lọc dữ liệu.
2. Đếm các ô đã lọc không trống
Chúng ta sẽ sử dụng hàm SUBTOTAL 3 hoặc hàm SUBTOTAL 103. Tuy nhiên, trong trường hợp có các hàng bị ẩn, bạn cần sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống có thể nhìn thấy.
Bảng dữ liệu Ví dụ bảng dữ liệu trên sẽ ẩn 2 hàng là 4 và 5. Khi sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho hai kết quả khác nhau với công thức này.
Ẩn các hàng dữ liệu Hãy nhập công thức và sau đó trình hướng dẫn Excel sẽ tự động hiển thị một tập hợp các hàm để bạn chọn mà không cần phải nhớ.
TIỂU THỤ. công thức hàm
Ô TÔ 3: Kết quả khi sử dụng Ô TÔ 3 sẽ là 3 ô bị ẩn trong hàng.
CẦU ĐỀ 103: Còn với MÃ ĐỀ 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy sau đó bỏ qua hàng bị ẩn.
3. Bỏ qua các giá trị trong công thức Tổng phụ lồng nhau
Ví dụ, chúng ta hãy tính trung bình tổng số kg vải trong các kho A1 và A2.
Công thức tính giá trị trung bình cho kho A2 = SUBTOTAL (1, C2: C4) và cho kết quả 19.
SUBTOTAL 1 Công thức trung bình của kho A1 = SUBTOTAL (1, C5: C7) cho kết quả 38.
Trung bình Nhưng khi lấy trung bình cộng tổng số vải ở 2 kho thì sẽ bỏ qua kết quả 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ỏ giá trị trung bình đã tính trước đó.
III. Ví dụ về công thức sử dụng SUBTOTAL. hàm số
1. Ví dụ 1: CHỦ ĐỀ 9 với CHỦ ĐỀ 109
Như bạn đã biết, hàm SUBTOTAL chấp nhận hai bộ hàm: 1-11 và 101-111. Cả hai đều sẽ bỏ qua các hàng đã lọc, tuy nhiên số 1-11 bao gồm các hàng ẩn theo cách thủ công, trong khi 101-111 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 đây.
Tuy nhiên, nếu bạn đã ẩn các mục không liên quan theo cách thủ công bằng cách sử dụng lệnh Ẩn Hàng từ tab Trang đầu> Nhóm ô> Định dạng> Ẩn & Bỏ ẩn hoặc bằng cách bấm chuột phải vào các hàng, rồi chọn lệnh Ẩn. Bây giờ nếu bạn muốn tính tổng các giá trị trong các hàng được hiển thị, hàm SUBTOTAL 109 là tùy chọn duy nhất:
Áp dụng ví dụ cho Tổng phụ. công thức hàm
Ví dụ: Để có thể đếm các ô đã lọc không trống, bạn có thể sử 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. nên được ẩn trong phạm vi tính toán.
Lưu ý: Hàm SUBTOTAL của Excel với Function_num từ 101-111 sẽ bỏ qua các giá trị trong các hàng ẩn, nhưng không bỏ qua 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 các số trong một 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 báo cáo tóm tắt hoặc trang tổng quan phải hiển thị các bản tóm tắt dữ liệu khác nhau nhưng không có đủ dung lượng cho mọi thứ, thì phương pháp sau có thể là một giải pháp:
Bước 1: Trong một ô, tạo một danh sách thả xuống chứa các tên hàm như: Tổng, Max, Min …
Bước 2: Trong một ô bên của danh sách thả xuống, 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 cho các tên hàm trong danh sách thả xuống.
Ví dụ: Giả sử các 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 Tổng, Trung bình, Tối đa và Tối thiểu. Sau đó, công thức SUBTOTAL “động” như sau:
= IF (A17 = ”tổng”, SUBTOTAL (9, C2: C16), IF (A17 = ”trung bình”, SUBTOTAL (1, C2: C16), IF (A17 = ”min”, SUBTOTAL (5, C2: C16 )), IF (A17 = “max”, SUBTOTAL (4, C2: C16), “”))))
Bây giờ, tùy thuộc vào chức năng mà người 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 biệt cơ bản của hàm SUBTOTAL và hàm SUM mà kế toán cần biết
Như chúng ta đã biết, hàm Tổng và hàm Tổng 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 các bạn cũng biết rồi, chỉ riêng hàm Subtotal nhiều bạn kế toán mới ra trường sẽ lạ lẫm nhưng nó là một trong những hàm được sử dụng vô cùng linh hoạt trong Excel nói chung và lĩnh vực này. kế toán nói riêng. Sự khác biệt
Tổng . hàm số
Hàm tổng phụ
Mục đích
Cú pháp
Trường hợp sử dụng tốt
Chỉ tính một tổng cố định hay còn được hiểu là tổng cộng cho tất cả các đối tượng mà không tách thành nhóm con (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 lại với nhau, dữ liệu sẽ được tính là 2 lần.
Có thể tính tổng cố định và di chuyển nghĩa là các tổng riêng biệt 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 Tổng phụ với nhau, dữ liệu sẽ không được tính hai lần.
hàm SUM = (number1;[number2];…) trong đó:
Giới hạn đối số ở 255 và đối số đầu tiên là bắt buộc.
Các đối số có thể là các giá trị, tham chiếu hoặc phạm vi ô riêng lẻ hoặc kết hợp cả ba.
Nếu đối số là một mảng hoặc tham chiếu, thì chỉ các số trong mảng hoặc 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 không thể được hiểu là số, thì Excel sẽ hiển thị lỗi.
function SUBTOTAL = (function_num; ref1; ref2;…) trong đó:
Function_num is (số đại diện cho các phép toán số trong hàm tổng phụ): các số thường 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ẽ tính tất cả các giá trị ẩn bằng lệnh Ẩn trong vùng dữ liệu. + Đối số từ 101-111: chức năng này không tính đến các giá trị bị ẩn bởi lệnh Hide. + Ngoài ra, hàm Subtotal sẽ không tính giá trị ẩn bằng lệnh Filter. + Chỉ dùng khi muốn tính giá trị theo cột. + Chỉ tính các giá trị 2-D + Ref1; ref2;…: không gian địa chỉ tham chiếu.
Tính tổng công nợ phát sinh trong NKC. sách
Cách tính khấu hao ở bảng phân bổ 211, 142, 242…
Tính tổng các số 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 kế toán, Bảng cân đối kế toán …
Tính tổng số dư cuối ngày trên sổ quỹ 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 đến bạn 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ư 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ật thú vị.
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
Comments