Dùng hàm subtotal như thế nào
|
Việc tìm hiểu về hàm Subtotal sẽ giúp công việc của người dùng trở nên thuận lợi hơn. Người dùng có thể dùng hàm này để tính toán, làm phép logic, đếm số ô, tính trung bình,….Nếu bạn muốn tìm hiểu thêm về hàm này, hãy liên hệ ngay với Sforum nhé! - Hàm Subtotal là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất.
Show
Cú pháp: =SUBTOTAL(function_num,ref1,ref2,...) Các tham số: - 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 đó. - Đối số thứ nhất của hàm SUBTOTAL bắt buộc bạn phải nhớ con số đại diện cho phép tính cần thực hiện trên tập số liệu. Đối số đó được xác định hàm thực sự nào sẽ được sử dụng khi tính toán trong danh sách bên dưới.  Ví dụ: - Nếu đối số là 1 thì hàm SUBTOTAL hoạt động giống như hàm AVERAGE, nếu đối số thứ nhất là 9 thì hàm hàm SUBTOTAL hoạt động giống như hàm SUM. Chú ý: Tuy có nhiều đối số là từ 1 – 11 nhưng trong kế toán chúng ta thường sử dụng đối số 9 và thường sử dụng trong việc tính tổng cho từng tài khoản, tính tổng phát sinh bên Nợ, Có, tỉnh tổng số tiền cuối ngày. - Cú pháp hàm: = SUBTOTAL(9; dãy ô cần tính tổng) ( Số 9 là cú pháp mặc định của hàm cho việc tính tổng). Ví dụ 1: Muốn cộng Tổng số tiền thu được trong tháng trên sổ Qũy tiền mặt, ta dùng hàm subtotal như sau: --------------------------------------------------------------------- Ví dụ 2: Muốn cộng Tổng số phát sinh bên Nợ trên sổ Nhật ký chung:
Công thức tính số tồn cuối ngày: = Tồn ngày hôm trước + Thu - Chi: =$I$14+SUBTOTAL(9;$G$15:G15)-SUBTOTAL(9;$H$15:H15) Cách thực hiện chi tiết: - Sau khi bấm vào Ô số tồn ngày hôm trước (bên trên) tức là ô: =I14 -> Các bạn bấm phím F4 (1 lần) để cố định -> Kết quả sẽ là: =$I$14 - Tiếp đó sau khi gõ hàm subtotal và bấm vào ô G15 (tức là bên Cột Thu): =$I$14+SUBTOTAL(9;G15 -> Các bạn cũng bấm phím F4 (1 lần) để có định ô G15. -> kết quả: =$I$14+SUBTOTAL(9;$G$15 -> Tiếp đó các bạn bấm tiếp 1 lần nữa vào ô G15 -> Kết quả: =$I$14+SUBTOTAL(9;$G$15:G15) Bạn đã biết hàm SUBTOTAL có chức năng gì hay chưa? Công thức của hàm SUBTOTAL như thế nào? Ví dụ cho hàm SUBTOTAL ra sao? Ứng dụng của hàm SUBTOTAL trong công việc và học tập như thế nào? Để trả lời 4 câu hỏi trên, sau đây mình sẽ hướng dẫn các bạn cách sử dụng hàm SUBTOTAL trong Excel này cụ thể như sau. 1. Hàm SUBTOTAL là gì?Hàm SUBTOTAL của Excel trả về kết quả tổng hợp cho các giá trị được cung cấp. SUBTOTAL có thể trả về SUM, AVERAGE, COUNT, MAX và các thứ khác (xem bảng bên dưới) và hàm SUBTOTAL có thể bao gồm hoặc loại trừ các giá trị trong các hàng ẩn. Mục đích của hàm SUBTOTAL là nhận một tổng số phụ trong danh sách hoặc cơ sở dữ liệu. 2. Công thức tính của hàm SUBTOTALCông thức SUBTOTAL Excel bằng tiếng Anh như sau: =SUBTOTAL (function_num, ref1, [ref2], …) Đơn giản hơn lệnh Subtotal : =SUBTOTAL(a,b,c,..) Trong đó: - Function_num: Một số chỉ định hàm nào sẽ sử dụng để tính tổng phụ trong danh sách. Xem bảng dưới đây để biết danh sách đầy đủ. - a,b,c: là các giá trị đi kèm với SUBTOTAL ví dụ như các giá trị có trong bảng sau: Công thức sử dụng hàm subtotal trong Excel >> Xem thêm: Hướng dẫn chi tiết cách dùng hàm IFS trong Excel 3. Cách sử dụng hàm SUBTOTALSử dụng hàm SUBTOTAL để lấy tổng phụ trong danh sách hoặc cơ sở dữ liệu. SUBTOTAL có khả năng sử dụng nhiều chức năng khác nhau khi tổng phụ, bao gồm AVERAGE, COUNT, MAX và các chức năng khác (xem bảng bên trên để biết danh sách đầy đủ). Theo mặc định, SUBTOTAL loại trừ các giá trị trong các hàng được ẩn bởi bộ lọc, như được giải thích bên dưới. Điều này làm cho SUBTOTAL rất hữu ích trong Bảng Excel. Để tìm hiểu chi tiết về cách sử dụng hàm SUBTOTAL trong Excel, bạn đọc có thể tham khảo nội dung Video dưới đây ở 02 phút 45 giây nhé. Hướng dẫn cách sử dụng hàm SUBTOTAL trong Excel 4. Ví dụ về cách sử dụng hàm SUBTOTALDưới đây là một vài ví dụ về cách dùng hàm SUBTOTAL được định cấu hình thành SUM, COUNT và AVERAGE các giá trị trong một phạm vi. Lưu ý sự khác biệt duy nhất là giá trị được sử dụng cho đối số function_num: = SUBTOTAL (109, phạm vi) // SUM = SUBTOTAL (103, phạm vi) // COUNT = SUBTOTAL (101, phạm vi) // AVERAGE Giá trị ẩn thủ công:Lưu ý các giá trị được sử dụng cho đối số function_num là "được ghép nối" (ví dụ: 1-101, 2-102, 3-103, ...). Điều này có liên quan đến cách SUBTOTAL xử lý các hàng ẩn thủ công. Khi function_num nằm trong khoảng 1-11, SUBTOTAL bao gồm các ô đã được ẩn thủ công. Khi function_num nằm trong khoảng 101-111, SUBTOTAL bỏ qua các giá trị trong các hàng được ẩn thủ công. Ví dụ cách sử dụng hàm subtotal trong Execl SUBTOTAL luôn bỏ qua các giá trị trong các ô được ẩn bằng bộ lọc. Các giá trị trong các hàng "được lọc ra" không được bao gồm, bất kể function_num. Ghi chú: - Khi function_num nằm trong khoảng 1-11, SUBTOTAL bao gồm các giá trị bị ẩn - Khi function_num nằm trong khoảng 101-111, SUBTOTAL loại trừ các giá trị bị ẩn - Trong danh sách được lọc, SUBTOTAL luôn bỏ qua các giá trị trong các hàng ẩn, bất kể function_num. SUBTOTAL bỏ qua các tổng phụ khác tồn tại trong tài liệu tham khảo được bỏ qua để ngăn chặn tính hai lần Ví dụ: đây là cách bạn có thể tạo công thức Tổng phụ 9 để tổng hợp các giá trị trong các ô từ C2 đến C8: Mô tả cách sử dụng hàm subtotal trong Excel. Hình 3 Để thêm số hàm vào công thức, bấm đúp vào nó, sau đó nhập dấu phẩy, chỉ định một phạm vi, nhập dấu ngoặc đơn đóng và nhấn Enter. Công thức hoàn thành sẽ như thế này: = SUBTOTAL (9, C2: C8) Tính tổng vùng được lọcVí dụ: Quan sát bảng sau, yêu cầu lọc ra KPI cho đội A. - Bước 1: Dùng chuột bôi đen bảng Excel chứa dữ liệu. Trên thanh công cụ, chọn Home -> Sort and Filter -> Filter. Tính tổng vùng được lọc - Hình 1 - Bước 2: Trong cột C, bạn nhấn vào biểu tượng tam giác lộn ngược rồi tích chọn A và nhấn OK. Tính tổng vùng được lọc - Hình 2 - Bước 3: Nhập công thức =SUBTOTAL(9,D2:D11) tại ô bạn cần lấy kết quả. Kết quả sau khi hoàn thành như sau: Tính tổng vùng được lọc - Hình 3 Đếm các ô được lọc không trốngVí dụ: Quan sát bảng dữ liệu, yêu cầu đếm số lượng sinh viên nhóm C đã có link bài tập để thống kê. - Bước 1: Lọc ra các sinh viên nhóm C như các thao tác lọc đã hướng dẫn phía trên. - Bước 2: Nhập công thức =SUBTOTAL(3,D4:D14) vào ô muốn lấy kết quả. Trong đó: - function_num = 3: Gía trị đối số tương ứng với hàm cần sử dụng, trong ví dụ này chúng ta chọn hàm COUNTA - tương ứng với số 3. - ref1 = D4:D14: là phạm vi cần tính tổng. Kết quả sau khi hoàn thành như sau: Xem thêm: Học tin học văn phòng online trọn đời chỉ từ 99K Kết quả sau khi hoàn thành Đánh số thứ tự nhanhCách thực hiện vô cùng đơn giản, bạn chỉ cần nhập công thức =SUBTOTAL(3,$B$2:B2) vào ô A2. Trong đó: - Funcation_num là 3: Ứng với hàm COUNTA - đếm ô không trống. - ref1 là $B$2:B2: Đếm ký tự phát sinh trong khoảng $B$2:B2, trong đó ô B2 được cố định bằng dấu"$" ở trước địa chỉ hàng và ô. Kết quả sau khi hoàn thành như sau: Kết quả sau khi hoàn thành 5. Cách fix lỗi #VALUE! hàm SUBTOTAL- Lỗi #VALUE xảy ra khi số xác định chức năng funcation_num không nằm trong khoảng 1-11 hoặc101-111. Hoặc tham chiếu ref tham chiếu đến các ô trong một trang tính. Ví dụ: bạn đang dùng SUBTOTAL ở sheet STT2 nhưng tham chiếu đến một ô, một dãy nào đó ở sheet STT1. Fix lỗi#VALUE! trong hàm SUBTOTAL - Cách fix lỗi như sau: + Nếu sai số xác định chức năng, điều đầu tiên bạn cần làm là kiểm tra lại xem mình đã dùng đúng số chức năng chưa hoặc trong quá trình nhập có dư ký tự nào không. + Nếu nguyên nhân là do lỗi tham chiếu, bạn hãy xóa phần tham chiếu sai đi, thay thế lại bằng dãy ô đúng được đặt trong cùng một trang tính mà bạn muốn thực hiện với hàm SUBTOTAL. Như vậy là mình đã giới thiệu cho các bạn về cách sử dụng hàm SUBTOTAL, mong rằng bài viết này sẽ giúp ích được nhiều cho các bạn. Chúc các bạn có một buổi học Excel online hiệu quả. >> Xem thêm: Hướng dẫn cách dùng hàm Sumif trong Excel Chia sẻ Đánh giá : Tags: Excel Bài liên quan
Khóa học nổi bật
Bài viết nổi bật
liên quan Tin học văn phòng Hướng dẫn cách copy 1 sheet trong Excel chi tiết nhất Cách copy 1 sheet trong Excel mà Unica chia sẻ chi tiết trong bài viết trên giúp bạn tiết kiệm thời gian khi cần sao chép các nội dung trùng lặp. Mời bạn đọc cùng theo dõi. Read moreTin học văn phòng Hướng dẫn chi tiết cách sử dụng hàm DSUM và ví dụ Hàm DSUM tính tổng các giá trị trong một cột hoặc cơ sở dữ liệu đáp ứng điều kiện nhất định, cùng Unica tìm cách sử dụng hàm Dsum thông qua ví dụ thực tế Read moreTin học văn phòng Cách dùng hàm Averageif tính trung bình có điều kiện trong Excel Hướng dẫn chi tiết cách sử dụng hàm Averageif trong Excel và cách tính trung bình bằng hàm Averageif, tham khảo nội dung mà Unica cung cấp qua bài viết Read moreTin học văn phòng Hàm LEFT trong Excel - Hàm cắt chuỗi bạn nhất định phải biết Hàm LEFT trong Excel được sử dụng để cắt chuỗi ký tự bên trái, cùng Unica tìm hiểu chi tiết về cách sử dụng của hàm này qua nội dung bài viết Read moreTin học văn phòng Hướng dẫn cách dùng hàm OR trong Excel Hàm Or trong Excel là gì? Sử dụng hàm Or giúp bạn kiểm tra tổ hợp các điều kiện trong Excel một cách dễ dàng, nhanh chóng Read moreTin học văn phòng Tổng hợp 6 cách đánh số thứ tự hàng trong Excel chính xác nhất Nắm được cách đánh số thự tự hàng trong Excel giúp bạn làm việc nhanh hơn, tiết kiệm được nhiều thời gian khi làm việc với trang tính Excel. Mời bạn đọc cùng theo dõi. Hàm subtotal trống Excel dùng để làm gì?- Hàm Subtotal là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất. - 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 đó.
Hàm subtotal ở đâu?Chức năng SUBTOTAL sẽ giúp bạn tính được kết quả trung bình theo từng tháng một cách nhanh chóng. Bước 1: Chọn toàn bộ bảng tính (trong trường hợp bạn chưa biết, chỉ cần chọn một ô bất kì trong bảng tính rồi chọn Subtotal, Excel sẽ tự tính toán và chọn tất cả những ô còn lại). Bước 2: Chọn Subtotal.
Hàm Round dùng để làm gì?Hàm ROUND là hàm làm tròn số thập phân, những số nằm sau dấu chấm hoặc dấu phẩy (tùy cấu hình Excel của bạn). Dùng để làm tròn kết quả tính trung bình.
Subtotal trống hóa đơn là gì?Subtotal: Tổng giá trị hàng hóa.
|
