Hàm Subtotal, các ứng dụng của Subtotal trong Excel Cực Hay

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 lập công dụng triển khai 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.

Function_num
Chức năng
Mô tả

1
101
AVERAGE
Tính trung bình các con số

2
102
COUNT
Đếm số ô chứa giá trị số

3
103
COUNTA
Đếm số ô không trống

4
104
MAX
Tìm giá trị lớn nhất

5
105
MIN
Tìm giá trị nhỏ nhất

6
106
PRODUCT
Tính kết quả của các ô

7
107
STDEV
Tính độ lệch chuẩn mẫu dựa trên mẫu

8
108
STDEVP
Tính độ lệch chuẩn dựa trên toàn bộ số

9
109
SUM
Cộng các số

10
110
VAR
Ước tính độ dao động dựa trên mẫu

11
111
VARP
Ước tính độ dao động dựa trên toàn bộ số

Bạn không cần phải nhớ hết những số lượng công dụng. Ngay khi bạn nhập hàm SUBTOTAL Excel vào 1 ô hoặc trên thanh công thức, Excel sẽ đưa ra list những số lượng cho bạn .
Ví dụ, đây là cách bạn dùng công thức SUBTOTAL 9 để cộng tổng những giá trị trong ô từ C2 đến C8 :

Để thêm 1 số xác lập tính năng vào công thức, nhấn đúp chuột, đánh dấu phẩy, xác lập dãy ô, đóng ngoặc và nhấn Enter. Công thức hoàn hảo sẽ như sau :

=SUBTOTAL(9,C2:C8)

Tương tự, bạn hoàn toà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 tính năng khác đang được dùng .

Lưu ý:

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

Bạn đã biết cách lập công thức SUBTOTAL trong Excel, câu hỏi chính là tại sao lại phải học hàm này? Sao không dùng các hàm đơn giản, bình thường như SUM, COUNT, MAX? Lý do sẽ được trình bày dưới đây.

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

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

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

Vì hàm Excel SUBTOTAL bỏ lỡ những hàng đã được lọc ra, bạn hoàn toàn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, những giá trị trong tổng phụ được tự động hóa tính lại theo bộ lọc .
Ví dụ, nếu tất cả chúng ta lọc bảng doanh thu bán hàng chỉ của vùng Miền Đông, công thức SUBTOTAL sẽ tự động hóa kiểm soát và điều chỉnh để bỏ lỡ toàn bộ vùng khác khi tính tổng .

Lưu ý:

Vì những 2 bộ số xác lập công dụng ( 1-11 và 101 – 111 ) đều bỏ lỡ những ô đã được lọc ra, bạn hoàn toàn có thể dùng công thức SUBTOTAL 9 hoặc SUBTOTAL 109 .

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

Công thức SUBTOTAL với số xác lập tính năng 101 – 111 bỏ lỡ những ô đã ẩn, đã lọc ra và ẩn bằng tay thủ công. Vì thế, khi bạn sử dụng công dụng Excel’s Hide để ẩn dữ liệu không tương quan, dùng số công dụng 101 – 111 để vô hiệu những giá trị từ những hàng ẩn khỏi tổng phụ .

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ỏ lỡ. Vậy nên số lượng 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ỏ lỡ hiệu quả của công thức SUBTOTAL trong ô C3 và C10 .

VÍ DỤ DÙNG SUBTOTAL TRONG EXCEL

Khi bạn mới biết hàm SUBTOTAL, hoàn toàn có thể thấy hàm này phức tạp, rắc rối không có ý nghĩa. Nhưng khi bạn dùng nó để xử lý việc làm, 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 1 số ít mẹo nhỏ khi dùng hàm SUBTOTAL .

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

Như bạn đã biết, Excel SUBTOTAL đồng ý 2 bộ số xác lập tính năng 1-11 và 101 – 111. Cả 2 bộ số đều bỏ lỡ những hàng đã được lọc ra, nhưng 1-11 gồm có những hàng được ẩn bằng tay thủ công còn 101 – 111 thì loại trừ. Để hiểu rõ hơn về điểm độc lạ này, xem ví dụ sau :
Với tổng những hàng đã được chọn, bạn hoàn toàn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới :

Nhưng nếu có các hàng không liên quan đã ẩn thủ công bằng cách dùng lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide, và nhấn chuột phải vào các hàng, sau đó Hide, mà giờ bạn chỉ cần tổng giá trị trong các hàng nhìn thấy, lựa chọn SUBTOTAL 109:

Các số công dụng khác cũng làm theo cách tựa như. Ví dụ, để đếm ô không trống đã chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 hoàn toàn có thể đếm những ô không trống nhìn thấy đúng chuẩn nếu có bất kể hàng ẩn nào trong dãy .

Lưu ý: Hàm Excel SUBTOTAL với số chức năng 101-111 bỏ qua giá trị trong hàng bị ẩn, không phải cột bị ẩn. Ví dụ, nếu bạn dùng công thức như SUBTOTAL(109, A1:E1) để tính tổng các số trong hàng ngang, cột bị ẩn sẽ không ảnh hưởng đến tổng phụ.

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

Nếu bạn đang lập báo cáo giải trình tổng kết, cần phải đưa ra những tài liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho toàn bộ số liệu, thì cách sau hoàn toàn 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à list 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 list, công thức SUBTOTAL tương ứng sẽ tính giá trị trong những hàng được chọn .

Mẹo nhỏ:

Nếu đùng một cái list và ô công thức biến mất khỏi bảng tính, thì bạn hãy chọn lại chúng trong list lọc .

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 do hoàn toàn có thể là một trong những nguyên 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 hoàn toàn có thể dùng những tính năng thiết lập sẵn của hàm này và để công thức tự hoàn thành xong 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…

Rất nhiều kỹ năng và kiến thức phải không nào ? Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Đây là khóa học giúp bạn mạng lưới hệ thống kỹ năng và kiến thức một cách khá đầy đủ, cụ thể. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên bạn hoàn toàn có thể tự do học bất kỳ khi nào, thuận tiện tra cứu lại kỹ năng và kiến thức khi cần. Hiện nay mạng lưới hệ thống đang có tặng thêm rất lớn cho bạn khi ĐK tham gia khóa học. Chi tiết xem tại : HocExcel. Online

Source: http://wp.ftn61.com
Category: Hỏi Đáp