Hàm SUBTOTAL – Hàm tính toán cho một nhóm trong danh sách trong Excel

Bạn đang xem: Hàm SUBTOTAL – Hàm tính toán cho một nhóm trong danh sách trong Excel tại vietabinhdinh.edu.vn

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

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

Bên trong:

– function_num: Số chỉ định hàm dùng để tính toán. Quy ước như sau: Đối với các phiên bản Office 2000 trở đi thì quy ước hàm từ 1 đến 11, với Office 2003, 2007 trở về sau thì đánh số từ 101 đến 111. Số tương ứng với hàm:

+ Hàm AVERAGE: 1 hoặc 101.

+ Hàm COUNT: 2 hoặc 102.

+ Hàm COUNTA: 3 hoặc 103.

+ Hàm MAX: 4 hoặc 104.

+ Hàm MIN: 5 hoặc 105.

+ Hàm SẢN PHẨM: 6 hoặc 106.

+ Hàm STDEV: 7 hoặc 107.

+ Hàm STDEVP: 8 hoặc 108.

+ Hàm tính tổng: 9 hoặc 109.

+ Chức năng VAR: 10 hoặc 110.

+ Chức năng VARP: 11 hoặc 111.

– ref1, ref2: Vùng dữ liệu muốn tham chiếu, có thể chứa từ 2 đến 254 vùng dữ liệu muốn tính toán.

Chú ý:

– Trường hợp nếu có nhiều hàm SUBTOTAL lồng nhau đặt tại các đối số ref1, ref2 => các hàm SUBTOTAL lồng nhau được bỏ qua để tránh tính toán kép.

– Đối số hàm_num nếu nằm trong tập giá trị từ 1 đến 11 => hàm thực hiện phép tính bao gồm 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 thực hiện phép tính Tính toán bỏ qua các giá trị ẩn.

– Hàm sẽ bỏ qua tất cả các hàng ẩn bằng cách sử dụng lệnh Auto Filter, lệnh 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 dọc, không thực hiện tính toán theo chiều ngang.

– Hàm chỉ tính cho tham số 2D, trường hợp tham số 3D chẳng hạn =Sum(Sheet1:Sheet5!A5 => hàm báo lỗi #VALUE!

– Với Excel 2000/XP, đối số Hàm chỉ nhận giá trị từ 1 đến 11.

– Trường hợp nếu vùng dữ liệu đã dùng Filter thì khi dùng hàm Subtotal cho kết quả không đúng.

Ví dụ 1: Cho một bảng dữ liệu.

Tính tổng số tiền đã bán, số tiền trung bình trên mỗi mặt hàng, tính số lượng mặt hàng đã bán dựa trên số lượng khách hàng.

– Tính tổng số tiền đã bán:

Hàm tính tổng là hàm Sum tương ứng với Function = 9 trong hàm Subtotal => nhập công thức vào ô cần tính như sau: =SUBTOTAL(9,D13:D17) => nhận được kết quả:

– Tính số tiền trung bình cho mỗi mặt hàng.

Hàm tính trung bình cộng tương ứng với function_num =1 trong hàm tổng phụ => nhập công thức SUBTOTAL(1, D13:D17) => được kết quả:

– Tính số lượng hàng bán được dựa trên số lượng người mua hàng.

Hàm Count tương ứng với Function_num = 2 trong hàm Subtotal => nhập công thức =SUBTOTAL(2, D13:D17) => lấy kết quả.

Như vậy bạn có thể sử dụng hàm Subtotal một cách linh hoạt trong nhiều phép tính mà không cần phải nhớ các hàm khác.

Ví dụ 2:

Sử dụng hàm Subtotal là công cụ có sẵn trong excel.

Cho bảng dữ liệu sau:

Tính tổng số tiền, số mặt hàng đã mua của từng khách hàng và tổng số tiền, tổng 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 (kể cả tiêu đề) -> Vào tab Data -> Outline -> Subtotal. (Nếu bạn không tùy chỉnh thanh công cụ thì vào tab Data -> Subtotal).

Bước 2: Hộp thoại Subtotal xuất hiện:

– Mục Tại mỗi lần thay đổi: chọn tiêu đề cột là cột Tên khách hàng vì ở đây tính tổng theo tên khách hàng.

– Mục Sử dụng hàm: Chọn Sum vì ở đây thực hiện tính tổng nên sử dụng hàm Sum.

– Mục Add subtotal to: check vào Số lượng và Tổng thành tiền vì ở đây tổng được tính theo số lượng và thành tiền.

Nhấn OK để có kết quả:

Như vậy, mỗi khách hàng được tính tổng số tiền và tổng số mặt hàng đã mua. Đồng thời tính tổng tiền và tổng số mặt hàng đã bán của cửa hàng. => Sử dụng thêm tính năng Sutotal và Subtotal rất phù hợp cho việc quản lý bán hàng.

Chúc may mắn!

Bạn thấy bài viết Hàm SUBTOTAL – Hàm tính toán cho một nhóm trong danh sách trong Excel có đáp ướng đươc vấn đề bạn tìm hiểu không?, nếu không hãy comment góp ý thêm về Hàm SUBTOTAL – Hàm tính toán cho một nhóm trong danh sách trong Excel bên dưới để vietabinhdinh.edu.vn có thể thay đổi & cải thiện nội dung tốt hơn cho các bạn nhé! Cám ơn bạn đã ghé thăm Website: vietabinhdinh.edu.vn

Nhớ để nguồn bài viết này: Hàm SUBTOTAL – Hàm tính toán cho một nhóm trong danh sách trong Excel của website vietabinhdinh.edu.vn

Chuyên mục: Kiến thức chung