Top 8 # Xóa Subtotal Trong Excel Xem Nhiều Nhất, Mới Nhất 6/2023 # Top Trend

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…

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 cuộc sống 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 này cụ thể như sau.

1. Chức năng của 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 SUBTOTAL

Công thức tiếng Anh của hàm SUBTOTAL như sau: =SUBTOTAL (function_num, ref1, [ref2], …) Đơn giản hơn: =SUBTOTAL(a,b,c,..)

Mô tả cách sử dụng hàm subtotal trong Excel. Hình 1

3. Cách sử dụng hàm SUBTOTAL

Sử 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.

4. Ví dụ về cách sử dụng hàm SUBTOTAL

Mô tả cách sử dụng hàm subtotal trong Execl. Hình 2

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 SUBTOTAL được thiết kế để làm việc với các giá trị dữ liệu dọc được sắp xếp theo chiều dọc. Trong phạm vi Ngang, giá trị trong các cột ẩn luôn được bao gồm. 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)

Ngoài ra, các bạn có thể tham khảo cách sử dụng các hàm trong Excel từ cơ bản đến nâng cao qua khóa học Trở thành cao thủ hàm Excel trong 10 giờ, giúp bạn nắm trọn hệ thống hàm Excel thông dụng và quan trọng nhất, từ đó thành công xử lý, lưu trữ và thống kê số liệu một cách nhanh chóng, chính xác nhất chỉ với 60 bài giảng cùng giảng viên hàng đầu.

Hàm SUBTOTAL là gì? Cú pháp, ý nghĩa và ứng dụng của hàm SUBTOTAL trong Excel như thế nào? Hãng Kiểm toán ES-GLOCAL xin trân trọng gửi đến bạn đọc bài chia sẻ Hàm SUBTOTAL trong Excel, mời các bạn cùng theo dõi!

Hàm SUBTOTAL trong Excel

#1. Tổng quan về hàm SUBTOTAL

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ố ô (Hàm count), tính trung bình, tìm giá trị lớn nhất/nhỏ nhất ( Hàm Max, hàm Min)…

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.

#2. Cú pháp và cách sử dụng hàm SUBTOTAL

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.

Khi sử dụng, người dùng không cần phải nhớ hết các con số chức năng vì khi nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ đưa ra danh sách gợi ý các con số để người dùng lựa chọn.

Ví dụ 1: Tính tổng, giá trị trung bình, giá trị max, min trong bảng dữ liệu sau:

Trong trường hợp này, thay vì sử dụng nhiều hàm, hàm SUBTOTAL có thể giúp người dùng giải quyết bài toán một cách nhanh chóng hơn.

Cách thực hiện:

Lưu ý: Khi bạn dùng công thức SUBTOTAL với chức năng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ tính các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.

#3. Ưu điểm của hàm SUBTOTAL so với các hàm truyền thống

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

Hàm SUBTOTAL trong Excel bỏ qua các hàng đã được lọc ra, người dùng 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ụ 2:

Vẫn dữ liệu ở ví dụ 1, trong trường hợp đề bài yêu cầu tính toán cho các doanh nghiệp thuộc khu vực miền Nam:

Sau khi tiến hành lọc cột “Khu vực” để lấy giá trị “Miền Nam”, các giá trị sẽ tự động nhảy theo công thức, ta được kết quả:

#3.2. Tính các ô nhìn thấy

Ví dụ 3: SUBTOTAL 9 và SUBTOTAL 109

Cho bảng giá trị:

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:

Như vậy, trong trường hợp này, lựa chọn SUBTOTAL 109 thì sẽ đảm bảo tiêu chí thực hiện công việc hơn.

#3.3. 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.

#4. Hàm SUBTOTAL trong Excel không hoạt động và các 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:

a, 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.

b, 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ố)

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

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.

Hỏi: Hàm SUBTOTAL dùng để làm gì?

Trả lời: Hàm SUBTOTAL trong Excel dùng để 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ố ô (Hàm count), tính trung bình, tìm giá trị lớn nhất/nhỏ nhất ( Hàm Max, hàm Min)…

Hỏi: Phân biệt các chức năng của hàm SUBTOTAL 1-11 và 101-111?

Hỏi: Cách sử dụng hàm SUBTOTAL trong Excel?

Công thức SUBTOTAL trong Excel

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

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

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

Lưu ý:

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ếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì sẽ được bỏ qua để tránh tính trùng 2 lần.

Nếu function_num 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 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 các 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.

Cách sử dụng hàm SUBTOTAL trong Excel

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. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là:

=SUBTOTAL(9,pham_vi)

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 ô đượ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 chính 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à khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần 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ỏ qua hàng ẩn.

3. Bỏ qua các giá trị trong các 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 trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.

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

Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. 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ả trung bình đã tính trước đó.

Cách lỗi khi thực hiện 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 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ả.