Đôi khi ta cần thể hiện doanh số bán hàng theo từng nhân viên, hoặc số lượng thực phẩm theo vùng miền, hay nhiệt độ trung bình của từng tháng… Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn làm điều trên thông qua chức năng SUBTOTAL thống kê dữ liệu theo điều kiện.

Xem nhanh

Chức năng SUBTOTAL thống kê dữ liệu theo điều kiện

Trong ví dụ lần này, ta sẽ mô phỏng đơn giản nhất bảng thống kê nhiệt độ và độ ẩm của Hà Nội 3 tháng đầu năm 2017.

Công việc của ta bây giờ là thống kê nhiệt độ và độ ẩm trung bình từng tháng. Và ta sẽ thực hiện bằng công cụ SUBTOTAL.

Công cụ SUBTOTAL

Subtotal nằm ở thẻ Data, nhóm Outline. Bạn cần chọn bảng dữ liệu trước khi click vào chức năng này.

Bây giờ, hãy quay trở lại với bảng tính của chúng ta nhé

Thêm các đối tượng thích hợp để sử dụng công cụ SUBTOTAL

[iframe width=”523″ height=”900″ frameborder=”0″ scrolling=”no” src=”https://onedrive.live.com/embed?resid=B21F9331F704AFCF%21119&authkey=%21AA5epCWh2HsqEVM&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’Task’!A1%3AD130&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True”]

Với một bảng dài ngoằng như này, hẳn bạn sẽ không muốn thêm những dòng tính trung bình vào dưới mỗi khi kết thúc tháng đâu nhỉ? Hãy đến với SUBTOTAL.

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. Một hộp thoại sẽ hiện ra:

Hãy giải thích về từng phần trong này nhé:

1. At each change in: Đối với mỗi sự thay đổi của đối tượng được chọn, Excel sẽ trả về kết quả. VD: Hết tháng 1 đến tháng 2 -> đã có sự thay đổi -> tính toán. Nói cách khác, At each change in trả lời cho câu hỏi: Điều kiện gì?
2. Use function: Hàm được sử dụng để tính. Với hàm SUM, Excel sẽ trả về tổng, hàm AVERAGE trả về trung bình,… trả lời cho câu hỏi: Phương thức thống kê?
3. Add subtotal to: Mục được áp dụng hàm ở trên. Chọn các cột cần thiết và Excel sẽ tính toán giá trị ở đó. Trả lời câu hỏi: Thống kê dữ liệu gì?

Ngoài ra ta có:

• Replace current subtotal: Nếu đã tồn tại trước đó một subtotal, nó sẽ thay thế bằng subtotal mới.
• Page break between groups: Bạn biết page break (ngắt trang) chứ? Tùy chọn này giúp cho mỗi nhóm của at each change in được nằm trong một trang khác nhau.
• Summary below data: Đưa function xuống dưới các nhóm. Nếu bỏ chọn ô này, kết quả sẽ không mất, mà đưa lên đầu.

Ok, ấn đi các bạn!

At each change in thời gian, use function AVERAGE, Add subtotal to Nhiệt độ ngày, Nhiệt độ đêm, Độ ẩm.

Mình biết điều đang xảy ra với trang tính của các bạn.

Bởi vì ta chưa tìm ra được đối tượng chính xác của at each change in. Thứ ta cần là tháng, chứ không phải thời gian cụ thể.

Để khắc phục, đầu tiên là Undo đi nào (Ctrl+Z).

Sau đó, click vào cột B, chọn Insert để thêm cột mới nằm giữa Thời gian và Nhiệt độ ngày. Đặt tên là Tháng.

Tại cột Tháng, nhập công thức vào như sau:

\=MONTH(A4)

Sau đó định dạng lại về General nếu kết quả đang ở dạng Date.

Sử dụng autofill áp dụng kết quả cho các ô còn lại. Trong trường hợp bạn chưa biết autofill, hãy xem bài viết này.

Kết quả:

Rồi, giờ bạn hãy áp dụng lại SUBTOTAL, với at each change in Tháng. Kết quả ra sao, bạn cũng đã ra rồi phải không nào?

Hàm SUBTOTAL trong Excel là gì? Công thức SUBTOTAL trong Excel như thế nào? Hãy cùng Quantrimang.com tìm hiểu nhé!

Microsoft Excel có rất nhiều hàm dùng để tính tổng, và SUBTOTAL là một trong số đó. Tuy nhiên, Subtotal khác với hàm SUM. Nó không phải tính tổng thông thường. Thay vào đó, nó được dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, tính số trung bình và nhiều hơn thế nữa.

Nội dung chính về hàm SUBTOTAL

Hàm SUBTOTAL là gì và nó được sử dụng để làm gì?

Thoạt nhìn, bạn có thể tự hỏi: Mục đích của hàm SUBTOTAL là gì? Xét cho cùng, thật dễ dàng để tìm tổng phụ từ một dải ô bằng cách sử dụng các hàm hiện có như SUMIF hoặc AVERAGE.

Mặc dù đúng là bạn không nhất thiết phải sử dụng SUBTOTAL, nhưng nó giúp việc phân tích dữ liệu được nhắm mục tiêu trở nên dễ dàng hơn rất nhiều. Bạn có thể chèn công thức bằng SUBTOTAL vào một dải ô mà không ảnh hưởng đến tổng số chung, vì SUBTOTAL bỏ qua các ô khác có chứa công thức SUBTOTAL.

SUBTOTAL hoạt động với các tính năng Excel khác, chẳng hạn như lọc ô. Nếu bạn lọc một bảng bằng một giá trị, công thức SUBTOTAL sẽ cập nhật, nhận dạng bộ lọc và loại trừ các ô tương ứng. SUBTOTAL cũng hữu ích để bỏ qua các giá trị ẩn, điều mà những hàm khác (như SUM) không thể làm được.

Rất may, bạn có thể sử dụng các hàm khác như một phần của công thức SUBTOTAL. Có sẵn 11 phương pháp tổng phụ khác nhau, mỗi phương pháp phù hợp với một hàm Excel hiện có. Nếu bạn muốn một công thức SUM hoạt động với các ô được lọc và bỏ qua các công thức SUBTOTAL khác, thì tốt nhất là sử dụng SUBTOTAL, mặc dù một pivot table có thể hoạt động theo cách tương tự.

Công thức SUBTOTAL trong Excel

Hàm SUBTOTAL hoạt động bằng cách tính toán tổng giá trị của một phạm vi ô, dựa trên một hàm toán học khác. SUBTOTAL sử dụng tới 11 hàm toán học khác nhau (từ AVERAGE đến VAR.P) để tìm tổng giá trị phù hợp, đồng thời làm việc trong các tham số của hàm (ví dụ, bỏ qua các công thức SUBTOTAL khác).

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

Những điều cần cân nhắc trước khi sử dụng hàm SUBTOTAL trong Excel

Mặc dù SUBTOTAL có những ưu điểm, nhưng cũng có một số điều bạn cần cân nhắc trước khi bắt đầu sử dụng hàm này, bao gồm những điểm sau:

• Như đã đề cập, sử dụng 1-11 cho đối số function_num sẽ đảm bảo rằng SUBTOTAL bao gồm các giá trị ẩn, trong khi 101-111 bỏ qua chúng.
• Sử dụng giá trị khác 1-11 hoặc 101-111 sẽ khiến Excel trả về lỗi

  VALUE!. Điều này cũng sẽ xảy ra đối với tham chiếu ô 3D (trong đó cùng một ô, trên nhiều trang tính, được tham chiếu trong một phạm vi).

• Khi các phạm vi ô ngang như A1:D1 được sử dụng, các giá trị ẩn sẽ tự động được đưa vào (bất kể giá trị đối số function_num). Đây là một hạn chế của hàm SUBTOTAL và không thể khắc phục được.
• Sử dụng SUBTOTAL trên dữ liệu được lọc sẽ đảm bảo rằng các giá trị ẩn luôn bị bỏ qua, bất kể đối số function_num được sử dụng.
• SUBTOTAL có thể được sử dụng dưới dạng hàm lồng nhau như một phần của các hàm khác, bao gồm những công thức có chứa hàm IF.
• Nếu một công thức SUBTOTAL khác nằm trong phạm vi dữ liệu ref1, thì SUBTOTAL sẽ bỏ qua và loại trừ nó khỏi phép tính tổng thể.
• Excel cho phép sử dụng tối đa 254 phạm vi ô trong công thức SUBTOTAL, mặc dù chỉ cần 1 phạm vi ô để công thức hoạt động.

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ả.

Một số mẹo về cách dùng hàm SUBTOTAL trong Microsoft Excel có thể bạn chưa biết

Mẹo 1: Giả sử bạn muốn đảm bảo tất cả màu áo phông đều có sẵn ở mọi kích thước của một trong hai nhà kho. Bạn có thể làm theo những bước sau:

Bước 1: Click Subtotal. Nhớ rằng đây là bước thêm nhiều tiêu chí cho dữ liệu tổng phụ hiện tại.

Bước 2: Chọn COUNT từ menu thả xuống và Size từ trường “Add subtotal field to”. Sau đó, bỏ tích Replace current subtotals. Khi click OK, bạn sẽ nhận được bảng dữ liệu sau:

Điều này giúp đảm bảo rằng bạn có đủ số lượng các kích thước khác nhau và có thể phân loại dữ liệu theo cách không cần phải lặp lại thao tác.

Mẹo 2: Luôn hữu ích khi cần phân loại dữ liệu theo cột.

Mẹo 3: Giúp bạn bao gồm một nhãn trong hàng đầu tiên.

Mẹo 4: Nếu muốn tóm tắt dữ liệu, bỏ tích ô “Summary below the data when inserting Subtotal.” - Tóm tắt bên dưới dữ liệu khi chèn hàm SUBTOTAL.

Tóm lại, hàm Subtotal trong Excel là gì?

Đúng như tên gọi, hàm SUBTOTAL trong Excel được dùng để tính tổng phụ. Hàm Excel SUBTOTAL trả về kết quả tổng hợp cho các giá trị được cung cấp. SUBTOTAL có thể tính tổng, số trung bình, số lượng, tối đa… Excel này cũng có thể bao gồm hoặc loại bỏ giá trị trong các hàng ẩn.

• Mục đích: Tính tổng số phụ trong một danh sách hoặc database.
• Giá trị trả về: Một số đại diện cho một kiểu tổng phụ.
• Đối số:
  • function_num - Một số chỉ định sử dụng hàm nào để tính tổng phụ trong danh sách.
  • ref1 - Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.
  • ref2 - [tùy chọn] Một phạm vi được đặt tên hoặc tham chiếu đến tổng phụ.

Sử dụng hàm SUBTOTAL có thể giúp bạn nhanh chóng phân tích tập dữ liệu mà không cần dựa vào các bảng tổng hợp phức tạp hơn. Nếu gặp khó khăn, bạn cũng có thể sử dụng tính năng Subtotal, được tìm thấy trong tab Data trên thanh ribbon, để tự động tạo công thức SUBTOTAL mà sau đó bạn có thể chỉnh sửa hoặc sao chép ở nơi khác.