Categories
Tổng Hợp

Cách Sử Dụng Hàm Subtotal Trong Excel Và Ví Dụ Chi Tiết

Hàm SUBTOTAL trong Excel dùng để tính tổng của một dãy số có điều kiện giúp bạn làm việc hiệu quả hơn nếu chưa biết cách sử dụng hàm này. Hãy cùng chúng tôi theo dõi bài viết dưới đây để hiểu rõ hơn về hàm SUBTOTAL cũng như cách sử dụng hàm này nhé.

Hướng dẫn Sử dụng phiên bản Excel 2013 trên máy tính xách tay Windows 10, bạn cũng có thể thực hiện tương tự trên các sản phẩm và máy tính xách tay Excel 2007, 2010, 2013, 2016, 2019 hoặc dòng MacOS cùng hệ điều hành.

1. Hàm Subtotal là gì? Ứng dụng của hàm SUBTOTAL trong Excel

Hàm SUBTOTAL được sử dụng để tính toán danh sách các số theo một số cách khác nhau, chẳng hạn như trung bình, tổng, lớn nhất hoặc nhỏ nhất, với cú pháp = SUBTOTAL (function_num, ref1, [ref2], …)

ở đó:

Hàm_num: Một số từ 1 đến 11 hoặc 101 và 111 xác định hàm nào sẽ được sử dụng khi tính toán các phần con trong danh sách.

Tham chiếu1: Phạm vi của phạm vi tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ.

Ref2…: Nó có thể là một phạm vi từ 2 đến 254 mà bạn muốn tính tổng phụ.

Xem thêm: 10 Bí Quyết Để Loại Bỏ “Vô Duyên” trong Giao Tiếp

Ghi chú:

Nếu các hàm SUBTOTAL lồng nhau khác được đặt trong các tham số ref1, ref2,…, các hàm lồng nhau này sẽ bị bỏ qua và không được đánh giá để tránh đánh giá kép.

Đối với hằng số function_num từ 1 đến 11. Hàm SUBTOTAL tính giá trị ẩn của vùng dữ liệu.

Đối với các hằng số từ 101 đến 111, chỉ các giá trị không ẩn trong vùng dữ liệu mới được tính toán.

Hàm SUBTOTAL sẽ bỏ qua bất kỳ dòng nào bị ẩn bởi lệnh bộ lọc (bộ lọc tự động).

Hàm SUBTOTAL được sử dụng cho các cột dữ liệu. Nó không hoạt động với các hàng dữ liệu.

Bất kỳ dữ liệu nào là 3-D, thì hàm SUBTOTAL sẽ đưa ra lỗi #VALUE !.

Bảng Function_num chứa mã để tính các trường hợp khác nhau trong hàm SUBTOTAL:

ham-subtotal-1-a-tabudec

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

Ví dụ 1: Tính tổng trong 1 bảng

Câu hỏi: Sử dụng hàm SUBTOTAL để tính tổng số tiền trong bảng dưới đây.

Bước 1: Trong bảng dữ liệu tính toán Excel, nhập hàm = SUBTOTAL (9, F4: F9) vào ô tham chiếu mà bạn muốn hiển thị kết quả.

Mô tả chức năng:

SUBTOTAL: là một lệnh hàm.

9: là Function_num. Số 9 là hàm SUM.

F4: F9: là vùng dữ liệu tham chiếu.

ham-subtotal-1-a2-tabudec

Tham khảo thêm: Kiêu Ngạo Là Gì – Cách Thay Đổi Tính Cách Kiêu Ngạo

Ví dụ minh họa hàm SUBTOTAL tính tổng.

Bước 2: Nhấn Enter để hiển thị kết quả.

Tham khảo: Cách sử dụng hàm SUM trong Excel với các ví dụ chi tiết

ham-subtotal-1-a3-tabudec

Ví dụ 2: Tính số tiền kiếm được trung bình

Bài toán: Sử dụng hàm tổng phụ để tính doanh thu trung bình sau khi bán các sản phẩm trong bảng dưới đây.

Bước 1: Trong bảng dữ liệu tính toán Excel, nhập hàm = SUBTOTAL (1, F4: F9) vào ô tham chiếu mà bạn muốn hiển thị kết quả.

Mô tả chức năng:

SUBTOTAL: là một lệnh hàm.

1: là Hàm_num. Số 1 là hàm AVERAGE.

F4: F9: là vùng dữ liệu tham chiếu.

ham-subtotal-1-a4-tabudec

Ví dụ này minh họa hàm SUBTOTAL tính giá trị trung bình.

Bước 2: Nhấn Enter để hiển thị kết quả.

Tham khảo: Cách sử dụng hàm AVERAGE trong Excel Tìm hiểu thêm

ham-subtotal-1-a5-tabudec

Nhấn Enter để hiển thị kết quả.

Ví dụ 3: Tìm giá trị lớn nhất trong bảng

Câu hỏi: Sử dụng hàm SUBTOTAL để tìm giá trị lớn nhất trong bảng sau.

Bước 1: Trong bảng dữ liệu tính toán Excel, nhập hàm = SUBTOTAL (4, F4: F9) vào ô tham chiếu mà bạn muốn hiển thị kết quả.

Mô tả chức năng:

SUBTOTAL: là một lệnh hàm.

4: là Function_num. Số 4 là hàm MAX.

F4: F9: là vùng dữ liệu tham chiếu.

ham-subtotal-1-a6-tabudec

Ví dụ này minh họa hàm SUBTOTAL tính giá trị lớn nhất.

Bước 2: Nhấn Enter để hiển thị kết quả.

Tham khảo: Cách sử dụng hàm MAX trong Excel để biết thêm chi tiết

ham-subtotal-1-a7-tabudec

Nhấn Enter để hiển thị kết quả.

Ví dụ 4: Đếm số ô chứa số

Đề bài: Sử dụng hàm SUBTOTAL để đếm số ô trong bảng dưới đây có chứa số.

Bước 1: Trong bảng dữ liệu tính toán Excel, nhập hàm = SUBTOTAL (2, F4: F9) vào ô tham chiếu mà bạn muốn hiển thị kết quả.

Mô tả chức năng:

SUBTOTAL: là một lệnh hàm.

2: là Function_num. Số 2 là hàm COUNT.

F4: F9: là vùng dữ liệu tham chiếu.

ham-subtotal-1-a8-tabudec

Ví dụ này minh họa hàm SUBTOTAL đếm số ô chứa giá trị số.

Bước 2: Nhấn Enter để hiển thị kết quả.

ham-subtotal-1-a9-tabudec

Nhấn Enter để hiển thị kết quả.

Tham khảo: Cách Sử Dụng Hàm Subtotal Trong Excel Rất Dễ Hiểu, Có Ví Dụ Minh Họa

Trên đây là cách sử dụng hàm SUBTOTAL trong Excel để tính tổng chi tiết kèm theo ví dụ dễ hiểu. Mình hi vọng bài viết này có thể giúp bạn thực hiện được hàm tổng phụ trong công việc và học tập của mình, nếu có góp ý hãy để lại lời nhắn bên dưới và đừng quên chia sẻ nếu bạn thấy hữu ích nhé.

Hãy thường xuyên truy cập website Chung cư Tabudec của chúng tôi để cập nhật nhiều thông tin hữu ích nhé!

Categories
Tổng Hợp

Cách Sử Dụng Hàm Subtotal Trong Excel Rất Dễ Hiểu, Có Ví Dụ Minh Họa

Hàm SUBTOTAL trong Excel có thể dùng để tính tổng, lọc thống kê các ô không trống trong một dải dữ liệu, đánh số tự động, … Mời các bạn cùng tìm hiểu công thức và cách sử dụng cụ thể trong bài viết dưới đây về hàm SUBTOTAL của chúng tôi nhé!

Các hướng dẫn sau đây được thực hiện trên máy tính xách tay hệ điều hành Windows, phiên bản Excel 2016. Bạn có thể làm tương tự trên Excel 2007, 2010, 2013 và 2019.

Bạn có thể tải xuống một số ví dụ về hàm SUBTOTAL trong Excel để làm quen với nó!

Công thức tính hàm Subtotal

Hàm SUBTOTAL trong Excel được sử dụng trong nhiều trường hợp, cụ thể là tính tổng, tính trung bình, đếm ô, tìm giá trị lớn nhất / nhỏ nhất của dữ liệu, đánh số tự động, v.v. ..

Công thức cho hàm SUBTOTAL như sau:

= SUBTOTAL (function_num, ref1, ref2, …)

ở đó:

Function_num: Nếu function_num từ 1 đến 11, thì hàm SUBTOTAL thực hiện các phép tính bao gồm cả giá trị ẩn trong vùng dữ liệu có chứa giá trị ẩn. Nếu function_num được chọn từ 101 – 111, thì hàm SUBTOTAL bỏ qua các giá trị hàng ẩn.

Ref1, Ref2,…: 1 hoặc nhiều ô hoặc phạm vi ô tính thành tổng phụ, tối đa 254.

Mã cho Function_num như sau:

ham-subtotal-5-a-tabudec

Xem thêm: Clorua Vôi Là Gì? Công Thức, Ứng Dụng Và Nguy Cơ Của Clorua Vôi

Ghi chú:

– Hàm SUBTOTAL được sử dụng cho các cột dữ liệu dọc.

– Nếu ref1, ref2, … có chứa hàm SUBTOTAL thì nó sẽ bị bỏ qua để tránh đếm hai lần.

– SUBTOTAL sẽ bỏ qua dữ liệu bị ẩn bởi bộ lọc (dữ liệu không thỏa mãn bộ lọc).

– Cần phân biệt rõ ràng giữa giá trị bị ẩn và giá trị bị từ chối do không thỏa mãn bộ lọc.

Giá trị ẩn là giá trị được ẩn chủ động khỏi hàng bạn chọn bằng cách thực hiện> Nhấp chuột phải và chọn Ẩn.

Các giá trị bị từ chối do không đáp ứng bộ lọc là các giá trị không phù hợp với yêu cầu tìm kiếm của bạn.

Ví dụ

Dưới đây là một số cách ứng dụng hàm SUBTOTAL phổ biến trong thực tế, mời các bạn tham khảo!

Tính tổng vùng bộ lọc

Giả sử bạn có bảng sau và cần lọc ra tổng KPI của Nhóm A. Bạn làm như sau:

Bước 1: Đánh dấu bảng chứa dữ liệu muốn tính toán> vào Trang chủ> Sắp xếp & Bộ lọc> Bộ lọc.

ham-subtotal-5-a2-tabudec

Bước 2: Nhấp vào mũi tên tam giác ngược trong tiêu đề cột “Đội”, đánh dấu “A” và nhấp OK.

ham-subtotal-5-a4-tabudec

Bước 3: Dùng Excel để lọc ra bảng dữ liệu của Đội A. Bây giờ nhận kết quả của đầu vào của bạn trong ô:

= Tổng phụ (9, D2: D11)

Giải thích công thức:

function_num = 9 là giá trị tham số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị tham số này được hiển thị khi bạn nhập công thức hàm SUBTOTAL. Ở đây vì cần tính tổng nên chúng ta sẽ chọn hàm SUM – tương ứng với số 9.

ref1 = D2: D11 là phạm vi được tính tổng.

Bạn sẽ nhận được kết quả sau:

ham-subtotal-5-a4-tabudec

Lưu ý: Bạn có thể tính tổng các hàng đã lọc bằng cách sử dụng các giá trị tham số là 9 (để tổng các giá trị ẩn) hoặc 109 (để bỏ qua các giá trị ẩn).

Tham khảo thêm: 10 Bí Quyết Để Loại Bỏ “Vô Duyên” trong Giao Tiếp

Đếm các ô không trống đã lọc

Giả sử bạn có một bảng dữ liệu như hình dưới đây. Nhiệm vụ của bạn là đếm xem có bao nhiêu sinh viên trong nhóm C có liên kết thực hành để phục vụ mục đích thống kê.

Đầu tiên, bạn tiếp tục lọc ra các sinh viên trong nhóm C bằng cách sử dụng thao tác lọc tương tự như đã giải thích trong phần trước.

Sau khi lọc dữ liệu, trong ô mà bạn nhận được kết quả, hãy nhập:

= Tổng phụ (3, D4: D14)

Giải thích công thức:

function_num = 3 là giá trị tham số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị tham số này được hiển thị khi bạn nhập công thức hàm SUBTOTAL. Ở đây vì cần đếm các ô không trống nên chúng ta sẽ chọn hàm COUNTA – tương ứng với số 3.

ref1 = D4: D14 là phạm vi được tính tổng.

Kết quả thu được như sau:

ham-subtotal-5-a5-tabudec

Hàm SUBTOTAL được sử dụng để đếm các ô không trống đã lọc

Lưu ý: Bạn có thể đếm các ô không trống trong các hàng được lọc bằng cách sử dụng giá trị tham số 3 (do tính năng Ẩn, các giá trị ẩn cũng có thể được tính theo cách thủ công) hoặc 103 (bị bỏ qua, các giá trị ẩn thủ công không được tính toán).

Bạn có thể xem cách hoạt động của hàm COUNTA và một số ví dụ tại đây.

Số nhanh

Nếu bạn chỉ cần đánh số một danh sách dữ liệu liền kề (không có hàng trống) như hình dưới đây, bạn có thể nhập một công thức đơn giản vào ô A2 như sau:

= Tổng phụ (3, $ B $ 2: B2)

Giải thích công thức:

function_num là 3: cách tính tương ứng là COUNTA – Đếm các ô không trống.

ref1 là $ B $ 2: B2: Đếm các ký tự trong phạm vi $ B $ 2: B2, trong đó ô B2 được cố định bằng “$” ở phía trước hàng và địa chỉ ô. Nếu bạn sao chép công thức xuống, các giá trị sẽ thay đổi thành: $ B $ 2: B3, $ B $ 2: B4, $ B $ 2: B5,… và hàm sẽ đếm chính xác, cho ra kết quả mong muốn. .

Sau đó, bạn kéo và sao chép công thức (bằng cách chọn ô chứa công thức bạn muốn sao chép> di chuột qua góc dưới cùng bên phải của ô và kéo dấu cộng vừa hiển thị đến cuối ô cuối cùng để lấy nét). Kết quả thu được như sau:

ham-subtotal-5-a6-tabudec

Tuy nhiên, nếu bạn gặp phải dữ liệu “cứng” với các dòng trống và không trống lộn xộn, bạn cần sử dụng SUBTOTAL kết hợp với hàm IF. Khi lọc dữ liệu, số thứ tự của giá trị đã chọn được nhập lại từ đầu, phương pháp này chỉ hoạt động khi sắp xếp theo cột.

Cú pháp chung

= IF (logic_test, value_if_true, SUBTOTAL (function_num, ref1, …))

Xem chi tiết: Cách sử dụng hàm SUBTOTAL và hàm IF để đánh số tự động trong Excel

Ví dụ, bạn có một bảng dữ liệu như hình dưới đây.

ham-subtotal-5-a7-tabudec

Bảng dữ liệu với các ô trống, không trống xen kẽ

Nhập công thức vào ô A2:

= IF (B2 = “”, “”, Tổng phụ (3, $ B $ 2: B2))

Hệ quả: Hàm chính cần sử dụng là IF. Nếu có ô trống, số lượng bị bỏ qua. Nếu các ô không trống, hãy sử dụng SUBTOTAL để đánh số.

Công thức cho hàm IF là: = IF (logic_test, value_if_true, value_if_false), trong đó:

logic_test: Điều kiện của hàm IF.

value_if_true: Trả về giá trị nếu điều kiện là đúng.

value_if_false: Trả về giá trị nếu điều kiện sai. Ở đây chúng tôi sử dụng hàm SUBTOTAL.

Các tham số được điền vào hàm SUBTOTAL là:

function_num = 3: Cách tính tương ứng là COUNTA – Đếm các ô không trống.

ref1 = $ B $ 2: B2: Đếm các ký tự trong phạm vi $ B $ 2: B2, trong đó ô B2 được cố định bằng “$” ở phía trước địa chỉ hàng và ô. Nếu bạn sao chép công thức xuống, các giá trị sẽ thay đổi thành: $ B $ 2: B3, $ B $ 2: B4, $ B $ 2: B5,… và hàm sẽ đếm chính xác, cho ra kết quả mong muốn. .

Sau khi nhập công thức, nhấn Enter, sau đó sao chép công thức từ ô A2 sang các ô còn lại. Kết quả như sau:

ham-subtotal-5-a8-tabudec

Cách sửa lỗi #VALUE! hàm SUBTOTAL

Lý do sai:

– Định danh hàm function_num không nằm trong khoảng 1 – 11 hoặc 101 – 111.

– Một ref tham chiếu đề cập đến một ô trong một trang tính khác (một trang tính khác), ví dụ: bạn sử dụng SUBTOTAL trong trang tính STT2, nhưng tham chiếu đến một ô hoặc phạm vi ô trong trang tính STT1.

ham-subtotal-5-a9-tabudec

Tham khảo: Kiêu Ngạo Là Gì – Cách Thay Đổi Tính Cách Kiêu Ngạo

Làm thế nào để khắc phục?

Nguyên nhân đầu tiên là do định nghĩa hàm bị sai, bạn cần kiểm tra lại xem có đúng số hàm được sử dụng hay không, hoặc có thừa ký tự trong quá trình nhập liệu hay không để sửa lại.

Đối với nguyên nhân thứ hai gây ra lỗi trích dẫn, hãy xóa tham chiếu bị lỗi và thay thế bằng phạm vi ô chính xác được đặt trong cùng một trang tính mà bạn muốn thực hiện với hàm SUBTOTAL.

Trên đây là hướng dẫn cách sử dụng hàm SUBTOTAL trong Excel đơn giản và dễ thực hiện. Hy vọng bài viết này sẽ giúp ích cho bạn khi làm việc với bảng tính!

Hãy thường xuyên truy cập website Chung cư Tabudec của chúng tôi để cập nhật nhiều thông tin hữu ích nhé!