Top 5 # Xem Nhiều Nhất Xóa Subtotal Trong Excel Mới Nhất 3/2023 # Top Like

Trong bài viết này, Excel Online sẽ giải thích chi tiết hàm SUBTOTAL và hướng dẫn các bạn cách sử dụng công thức SUBTOTAL để cộng dữ liệu trong các ô nhìn thấy.

HÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNG

Hàm SUBTOTAL trong Excel có nhiệm vụ để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất…

Hàm SUBTOTAL có ở tất cả các phiên bản Excel từ 2016 đến 2007 và cả phiên bản thấp hơn.

Cú pháp hàm SUBTOTAL: SUBTOTAL(function_num, ref1, [ref2],…)

Trong đó:

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

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.

Số xác định chức năng thực hiện có 2 loại sau:

1 -11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công.

101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.

Bạn không cần phải nhớ hết các con số chức năng. Ngay khi bạn nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ đưa ra danh sách các con số cho bạn.

Ví dụ, đây là cách bạn dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 đến C8:

=SUBTOTAL(9,C2:C8)

Tương tự, bạn có thể viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm ô chứa số, SUBTOTAL 3 để đếm ô không trống. Trong hình dưới, 3 chức năng khác đang được dùng.

3 LÝ DO ĐỂ DÙNG HÀM SUBTOTAL

So sánh với các hàm Excel truyền thống, SUBTOTAL cho bạn những lợi thế sau:

Tính giá trị trong các hàng được chọn

Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, bạn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.

Ví dụ, nếu chúng ta lọc bảng doanh số bán hàng chỉ của vùng Miền Đông, công thức SUBTOTAL sẽ tự động điều chỉnh để bỏ qua tất cả vùng khác khi tính tổng.

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

Nếu dãy ô trong công thức SUBTOTAL có chứa công thức SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 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.

Khi bạn mới biết hàm SUBTOTAL, có thể thấy hàm này phức tạp, rắc rối vô nghĩa. Nhưng khi bạn dùng nó để giải quyết công việc, bạn sẽ nhận ra thuần thục hàm SUBTOTAL không khó. Ví dụ sau đây sẽ giúp bạn với một số mẹo nhỏ khi dùng hàm SUBTOTAL.

VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109

Như bạn đã biết, Excel SUBTOTAL chấp nhận 2 bộ số xác định chức năng 1-11 và 101-111. Cả 2 bộ số đều bỏ qua các hàng đã được lọc ra, nhưng 1-11 bao gồm các hàng được ẩn thủ công còn 101-111 thì loại trừ. Để hiểu rõ hơn về điểm khác biệt này, xem ví dụ sau:

Với tổng các hàng đã được chọn, bạn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆU

Nếu bạn đang lập báo cáo tổng kết, cần phải đưa ra các dữ liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho tất cả số liệu, thì cách sau có thể là giải pháp:

Trong 1 ô, tạo 1 danh sách dạng thả xuống (drop-down) có chứa tên các hàm như Total, Max, Min…

Trong ô kế ô danh sách, thêm công thức hàm IF lồng ghép có chứa công thức SUBTOTAL tương ứng với các hàm trong danh sách.

Ví dụ, giả sử giá trị để tính tổng phụ trong ô C12:C16, và danh sách trong ô A17 chứa Total, Average, Max, Min, công thức hàm SUBTOTAL sẽ như sau:

=IF(A17=”total”, SUBTOTAL(9,C2:C16), IF(A17=”average”, SUBTOTAL(1,C2:C16), IF(A17=”min”, SUBTOTAL(5,C2:C16), IF(A17=”max”, SUBTOTAL(4,C2:C16),””))))

Và tiếp theo, tuỳ vào hàm mà người sử dụng chọn trong danh sách, công thức SUBTOTAL tương ứng sẽ tính giá trị trong các hàng được chọn.

HÀM EXCEL SUBTOTAL KHÔNG HOẠT ĐỘNG – NHỮNG LỖI THƯỜNG GẶP

Nếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân có thể là một trong những lý do sau đây

#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? tên hàm SUBTOTAL sai chính tả.

Mẹo nhỏ:

Nếu bạn chưa quen với hàm SUBTOTAL, bạn có thể dùng các chức năng cài đặt sẵn của hàm này và để công thức tự hoàn thiện giúp bạn hoặc theo dõi video sau đây:

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.

Một số hàm cơ bản thường gặp như:

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện

COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện

Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…

Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

Định dạng theo điều kiện với Conditional formatting

Thiết lập điều kiện nhập dữ liệu với Data Validation

Cách đặt Name và sử dụng Name trong công thức

Lập báo cáo với Pivot Table…

Web Tin Học Trường Tín có bài: Cách dùng hàm SUBTOTAL trong Excel Hàm Subtotal trong Excel được dùng ở rất nhiều trường hợp khác nhau, giúp bạn tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, khác với hàm SUM trong Excel

Hàm SUBTOTAL trong Excel

Hàm SUBTOTAL có cú pháp =SUBTOTAL(function_num, ref1, [ref2],…) . Trong đó:

Function_num: Các số lượng từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được sử dụng để tính toán trong SUBTOTAL.

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

Lưu ý:

Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo phía dọc.

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

Nếu function_num từ 1 đến 11 thì hàm SUBTOTAL tính toán cho dù là cả những giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho những giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).

Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL sẽ bỏ qua.

Ví dụ sử dụng hàm SUBTOTAL

1. Tính tổng các hàng được lọc

Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel.

2. Đếm các ô được lọc không trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chuẩn xác các ô không trống nhìn thấy.

Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.

Chúng ta nhập công thức và lúc đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà chẳng càng phải nhớ.

Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.

Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ lỡ hàng ẩn.

3. Bỏ qua các giá trị trong số công thức Subtotal lồng nhau

Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.

Công thức tính bình quân cho kho A2= SUBTOTAL(1,C2:C4) rồi cho kết quả 19.

Công thức tính bình quân cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.

Tuy nhiên khi tính trung bình tổng cộng vải ở 2 kho thì sẽ bỏ qua kết quả tính bình quân trước đó.. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả bình quân đã lường trước đó.

Cách lỗi khi thực hành hàm SUBTOTAL trong Excel

Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:

#VALUE! Số xác định chức năng không nằm ở phía 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 bình quân cộng hoặc độ lệch chuẩn của một dãy ô không chứa giá trị số)

#NAME? tên hàm SUBTOTAL sai chính tả.

Hàm SUMPRODUCT trong Excel: Tính tổng tích các giá trị tương ứng

Cách gộp 2 cột Họ và Tên trong Excel không mất nội dung

SUBTOTAL Excel, cách dùng SUBTOTAL Excel, hàm SUBTOTAL Excel, sử dụng hàm SUBTOTAL Excel, tính tổng hàng được lọc excel, hàm cơ bản excel

Nội dung Cách dùng hàm SUBTOTAL trong Excel được tổng hợp sưu tầm biên tập bởi: Tin Học Trường Tín. Mọi ý kiến vui lòng gửi Liên Hệ cho chúng tôi để điều chỉnh. chúng tôi tks.

Lesson 21: Groups and Subtotals

/en/excel2016/filtering-data/content/

Introduction

Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data into groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet.

Optional: Download our practice workbook.

Watch the video below to learn more about groups and subtotals in Excel.

To group rows or columns:

To hide and show groups:

Creating subtotals

The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data. For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. It will create a hierarchy of groups, known as an outline, to help organize your worksheet.

Your data must be correctly sorted before using the Subtotal command, so you may want to review our lesson on Sorting Data to learn more.

To create a subtotal:

In our example, we’ll use the Subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group.

To view groups by level:

You can also use the Show and Hide Detail buttons to show and hide the groups within the outline.

To remove subtotals:

Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotaling, you’ll need remove it from your worksheet.

Challenge!

/en/excel2016/tables/content/

Hàm Subtotal trong excel là một trong những hàm excel thường dùng trong kế toán và được sử dụng để tính tổng trong bảng Cân đối kế toán, cân đối phát sinh tháng/năm.. Subtotal là hàm excel 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. Hàm này là hàm hơi khó sử dụng vì cấu trúc của nó không hề đơn giản, ở bài viết này Trung tâm kế toán Hà Nội sẽ hướng dẫn cách sử dụng hàm Subtotal trong cả excel 2007 và excel 2003.

Công thức sử dụng hàm Subtotal: = SUBTOTAL(function_num,ref1,ref2,…)

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 đó.

Ghi chú:

Nếu có hàm subtotal khác lồng đặt tại các đối số ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua không được tính nhằm 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 được thiết kế để tính toán cho các cột số liệu theo chiều dọc, nó không được thiết kế để tính theo chiều ngang.

Hàm này chỉ tính toán cho dữ liệu 2-D do vậy nếu dữ liệu tham chiếu dạng 3-D (Ví dụ về tham chiếu 3-D: =SUM(Sheet2:Sheet13!B5) thì hàm SUBTOTAL báo lỗi #VALUE!.

Ví dụ về cách sử dụng hàm Subtotal như sau:

Dùng hàm Subtotal để tính tổng các khoản nợ ngắn hạn phải trả trên bảng CĐKT

Các bài viết mới

Các tin cũ hơn