Tìm hiểu và khám phá Hàm SUBTOTAL trong Excel mới nhất 2023

Bạn có biết cách sử dụng hàm SUBTOTAL trong Excel để tính toán cho một nhóm trong danh sách không? Bằng cách sử dụng hàm này, bạn có thể lựa chọn các loại tính toán khác nhau chỉ trong một công thức duy nhất. Điều này giúp bạn tiết kiệm thời gian và nâng cao hiệu suất làm việc trong Excel. Hàm SUBTOTAL là một công cụ hữu ích cho việc phân tích dữ liệu và tính toán trong Excel. Dựa trên thông tin trên, mình sẽ tổng hợp dưới bài viết qua aptech nhé!

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

Hàm SUBTOTAL là một công cụ tính toán cho một nhóm con trong một tập dữ liệu hoặc bảng tùy thuộc vào phép tính liên quan đến tham số thứ nhất.

Cú pháp: = SUBTOTAL(function_num,ref1,ref2,…).

Trong đó:

• function_num: Số xác định hàm được sử dụng để tính toán. Các quy ước như sau: Với phiên bản Office từ 2000 trở đi, các hàm được quy ước từ số 1 đến 11, và với Office 2003, 2007 trở đi, các hàm được quy ước từ số 101 đến 111. Các số tương ứng với các hàm là:

• Hàm Trung Bình (AVERAGE): 1 hoặc 101.
• Hàm Đếm (COUNT): 2 hoặc 102.
• Hàm Đếm không rỗng (COUNTA): 3 hoặc 103.
• Hàm lớn nhất (MAX): 4 hoặc 104.
• Hàm nhỏ nhất (MIN): 5 hoặc 105.
• Hàm tích (PRODUCT): 6 hoặc 106.
• Hàm độ đo đường biểu đồ (STDEV): 7 hoặc 107.
• Hàm độ đo đường biểu đồ (STDEVP): 8 hoặc 108.
• Hàm tổng (SUM): 9 hoặc 109.
• Hàm phương sai (VAR): 10 hoặc 110.
• Hàm phương sai (VARP): 11 hoặc 111.

• ref1, ref2: Phạm vi dữ liệu mà bạn muốn tham chiếu, có thể chứa từ 2 đến 254 phạm vi dữ liệu.

Chú ý:

• Trong trường hợp có nhiều hàm SUBTOTAL lồng nhau trong các đối số ref1, ref2 => các hàm SUBTOTAL lồng nhau sẽ được bỏ qua để tránh tính toán hai lần.
• Nếu đối số Function_num nằm trong tập giá trị từ 1 đến 11 => hàm sẽ tính toán cả các giá trị ẩn trong tập dữ liệu, nếu nằm trong tập giá trị từ 101 đến 111 => hàm sẽ bỏ qua các giá trị ẩn.
• Hàm sẽ bỏ qua tính toán cho tất cả các hàng bị ẩn bởi lệnh Lọc Tự động mà hàm này không phụ thuộc vào tham số Function_num.
• Hàm được thiết lập để tính toán theo cột theo chiều dọc, không thực hiện tính toán theo chiều ngang.
• Hàm chỉ thực hiện tính toán cho các tham số 2D, trong trường hợp tham số 3D ví dụ =Sum (Sheet1:Sheet5!A5 => hàm sẽ báo lỗi #VALUE!
• Với Excel 2000/XP, đối số Function chỉ chấp nhận các giá trị từ 1 đến 11. Trong trường hợp dữ liệu đã sử dụng Bộ lọc, khi sử dụng hàm SUBTOTAL, kết quả có thể không chính xác.

Ví dụ 1: Cho bảng số liệu.

• Để tính tổng số tiền đã bán được:

Chúng ta có thể sử dụng hàm Tổng (Sum) với Function = 9 trong hàm Subtotal. Nhập công thức sau vào ô cần tính: =SUBTOTAL(9, D13: D17) và sau đó sẽ nhận được kết quả.

• Để tính số tiền trung bình trên mỗi mặt hàng:

Chúng ta sử dụng hàm Trung bình (Average) với Function = 1 trong hàm Subtotal. Nhập công thức tương ứng vào ô cần tính và sau đó nhận được kết quả.

• Để tính số mặt hàng đã bán được dựa vào số khách hàng:

Sử dụng hàm Đếm (Count) với Function = 2 trong hàm Subtotal. Nhập công thức tương ứng vào ô cần tính và sau đó nhận được kết quả.

• Tính giá trị trung bình cho mỗi mặt hàng.
• Sử dụng hàm Trung bình (Average) với function_num = 1 trong hàm subtotal => nhập công thức = SUBTOTAL(1, D13: D17) => sẽ nhận được kết quả:

• Để tính số mặt hàng đã bán dựa trên số lượng người mua hàng.
• Ta sử dụng hàm đếm Count với Function_num = 2 trong hàm Subtotal => nhập công thức =SUBTOTAL(2, D13: D17) => sẽ nhận được kết quả.

Với hàm Subtotal, bạn có thể linh hoạt sử dụng trong nhiều phép tính mà không cần nhớ các hàm khác.

Ví dụ 2: Hàm Subtotal là một công cụ tích hợp sẵn trong Excel. Dưới đây là một bảng số liệu:

Tính tổng số tiền và số mặt hàng đã mua của mỗi khách hàng, cũng như tổng số tiền và số mặt hàng đã bán của cửa hàng.

Bước 1: Chọn toàn bộ bảng dữ liệu (bao gồm cả tiêu đề) 

Truy cập vào tab Dữ liệu -> Nhóm -> Subtotal. (Trong trường hợp không có tùy chọn nhóm trên thanh công cụ, bạn hãy vào tab Dữ liệu -> Subtotal).

Bước 2: Hộp thoại Subtotal hiển thị

• Trong mục At each change in: chọn tiêu đề cột Tên KH vì chúng ta muốn tính tổng theo tên khách hàng.
• Trong mục Use function: Chọn Sum vì chúng ta muốn thực hiện tính tổng.
• Trong mục Add subtotal to: Đánh dấu chọn Số lượng và Số tiền vì chúng ta muốn tính tổng theo số lượng và số tiền.

• Hãy nhấp vào nút OK để xem kết quả.

• Việc tính tổng tiền và tổng số mặt hàng đã mua cho mỗi khách hàng là rất quan trọng. Ngoài ra, tính tổng tiền và tổng số mặt hàng đã bán của cửa hàng cũng là điều cần thiết. Để quản lý mua bán hàng một cách hiệu quả, việc sử dụng tính năng Subtotal và hàm Subtotal là rất phù hợp.

FAQ thường gặp

Câu hỏi 1: Hàm SUBTOTAL có thể tính toán cho nhiều loại hàm tính toán khác nhau không?

• Hàm SUBTOTAL không chỉ tính tổng, mà còn tính trung bình, số lượng, giá trị lớn nhất, giá trị nhỏ nhất và nhiều hơn nữa. Bạn chỉ cần chọn loại tính toán mà bạn muốn trong công thức.

Câu hỏi 2: Hàm SUBTOTAL có thể sử dụng cho phạm vi không liên tục trong danh sách không?

• Không, hàm SUBTOTAL chỉ hoạt động cho các dòng liên tục trong danh sách. Nếu danh sách của bạn có các dòng không liên tục, bạn cần sắp xếp lại danh sách để các dòng cần tính toán đối mặt với các dòng được tính toán liên tiếp.

Câu hỏi 3: Tôi có thể thêm vào hàm SUBTOTAL để tính toán theo điều kiện không?

• Bạn có thể sử dụng hàm IF hoặc hàm COUNTIF cùng với hàm SUBTOTAL để tính toán theo điều kiện. Bạn chỉ cần đặt hàm IF hoặc COUNTIF bên trong hàm SUBTOTAL để chọn những dòng bạn muốn tính toán và loại trừ những dòng không phù hợp trong danh sách.

Kết luận

Hy vọng rằng bài viết này đã giúp bạn hiểu về hàm SUBTOTAL trong Excel và cách sử dụng nó để tính toán cho một nhóm trong danh sách. Với hàm này, bạn có thể dễ dàng tính tổng, trung bình, số lượng và nhiều hơn nữa cho những dòng bạn muốn trong Excel. Nếu bạn có bất kỳ câu hỏi hoặc ý kiến nào, hãy để lại comment để chúng ta có thể trao đổi thêm. Hãy chia sẻ bài viết này qua aptech với những người khác nếu bạn nghĩ nó hữu ích!