Thống kê kết quả lọc dữ liệu trong Excel bằng hàm SUBTOTAL

Nguồn bài viết: Thống kê kết quả lọc dữ liệu trong Excel bằng hàm SUBTOTAL

Các hàm trong Excel được Microsoft phát triển để thực hiện một hoặc một số hàm. Mục đích giúp người dùng sử dụng dễ dàng hơn, tiết kiệm thời gian và công sức.

Tính đến thời điểm hiện tại, số lượng hàm trong Excel khá lớn, được chia thành nhiều nhóm khác nhau như: tài chính, logic, chuỗi, ngày & giờ, tìm kiếm & tra cứu, toán & lượng giác,…

Trong bài viết này, chúng ta sẽ cùng nhau tìm hiểu về SUBTOTAL. hàm số. Hàm này thuộc nhóm hàm Math & Trig và chức năng chính của nó là thống kê kết quả lọc dữ liệu.

#đầu tiên. Giới thiệu một chút về SUBTOTAL. hàm số

Hàm SUBTOTAL thực hiện các thống kê như tổng, trung bình, tối đa, nhỏ nhất, … dựa trên kết quả đã lọc từ bảng dữ liệu.

Cú pháp của hàm SUBTOTAL:

= SUBTOTAL(function_num, ref1,…)

Bên trong:

• Function_num là số đại diện cho hàm, chẳng hạn như số 9 sẽ biểu thị hàm tổng, số 6 sẽ biểu thị hàm sản phẩm, … Để biết thêm chi tiết và đầy đủ, vui lòng xem bảng bên dưới.

Function_num (bao gồm cả giá trị ẩn)	Function_num (không bao gồm giá trị ẩn)	Hàm	Sự miêu tả
đầu tiên	101	TRUNG BÌNH	Tính giá trị trung bình
2	102	ĐẾM	Đếm các ô chứa giá trị số
3	103	COUNTA	Đếm các ô có chứa các giá trị không rỗng
4	104	TỐI ĐA	Giá trị lớn nhất
5	105	MIN	Giá trị nhỏ nhất
6	106	SẢN PHẨM	Tính tích các giá trị
7	107	STDEV	Ước tính độ lệch chuẩn dựa trên mẫu
số 8	108	STDEVP	Ước tính độ lệch chuẩn dựa trên toàn bộ tập hợp được cung cấp như một đối số
9	109	TÓM LƯỢC	Tính tổng các giá trị
mười	110	VAR	Ước tính phương sai cho tập mẫu
11	111	VARP	Ước tính phương sai cho một tập hợp

• ref1 phạm vi của thống kê đầu tiên.
• [ref2] phạm vi thực hiện thống kê thứ hai ..
• [ref3] phạm vi cho thống kê thứ ba…
• [ref254] phạm vi thực hiện của thống kê thứ hai trăm năm mươi tư ..

# 2. Một số lưu ý khi sử dụng SUBTOTAL. hàm số

• [ref2] đến [ref254] là các đối số tùy chọn.
• Phạm vi thống kê của chức năng này không thể thực hiện được với các tham chiếu 3-D. Nếu vậy, Excel sẽ báo lỗi #VALUE!
• Nếu như fuction_num từ 1 đến 11, sau đó SUBTOTAL thực hiện thống kê bao gồm giá trị của các hàng bị ẩn bởi lệnh Hide Rows.
• Nếu như fuction_num từ 101 đến 111 rồi SUBTOTAL thực hiện thống kê loại trừ giá trị của các hàng bị ẩn bởi lệnh Hide Rows.
• Giá trị của các cột đều được thống kê bởi SUBTOTAL mặc dù nó bị ẩn bởi lệnh Hide Colums.

# 3. Ví dụ cụ thể về SUBTOTAL. hàm số

Cho bảng thống kê như hình dưới đây:

Câu hỏi:

• Một) Trích xuất dữ liệu theo cột tiêu đề.
• NS) Thống kê tổng lương, lương bình quân, lương cao nhất và lương thấp nhất theo kết quả trích xuất dữ liệu của câu a).

Bài giải:

a) Cách lọc dữ liệu theo cột tiêu đề

• • Bước 1: Chọn một ô bất kỳ hoặc toàn bộ bảng dữ liệu => chọn thẻ Data => trong nhóm Sort & Filter => chọn Filter

• • Bước 2: Lựa chọn tại cột vị trí rồi chọn lần lượt từng vị trí => chọn OK

NS) Tạo bảng thống kê và thiết lập công thức tính toán:

• = SUBTOTAL (9, D2: D11)
• = SUBTOTAL (1, D2: D11)
• = SUBTOTAL (4, D2: D11)
• = SUBTOTAL (5, D2: D11)

#4. Sử dụng tính năng PivotTable cũng cho kết quả tương tự

Ngoài việc sử dụng hàm SUBTOTAL, chúng ta cũng có thể sử dụng tính năng PivotTable để giải quyết câu hỏi trên một cách dễ dàng.

Trong phạm vi bài viết này, tôi chỉ hướng dẫn sơ qua và không giải thích gì về PivotTables. Nếu bạn chưa biết gì về chức năng này, bạn có thể tìm hiểu thêm về nó tại đây.

+ Bước 1: Chọn bất kỳ ô nào trong bảng hoặc chọn toàn bộ bảng:

+ Bước 2: Chọn thẻ Insert => trong nhóm Table lựa chọn PivotTable.

+ Bước 3: Hộp thoại Create PivotTable xuất hiện, vui lòng khai báo:

• Bảng / Phạm vi được Data!$A$1:$E$11
• Vị trí được Data!$A$14

Thông báo rằng Data Đây là tên của Trang tính hiện tại.

+ Bước 4: Trong PivotTable Fields tuyên bố:

• Bộ lọc “Chức vụ”
• Giá trị “Tổng Lương”, “Tổng Lương2”, “Tổng Lương 3” và “Tổng Lương 4”

+ Bước 5: Chọn “tam giác đen” tại Sum of Lương 2 => chọn Value Field Settings...

Trong hộp thoại Value Field Settings => chọn chức năng tính toán / thống kê (ở đây là chức năng Trung bình) => chọn OK.

Làm như trên cho Sum of Lương 3, 4 với chức năng Max, Min. Ngoài ra, tại Custom Name bạn có thể đổi tên nó như bạn muốn.

Kết quả như hình bên dưới, khi chọn

để lọc thì tổng mức lương, mức lương trung bình, mức lương cao nhất và mức lương thấp nhất sẽ xuất hiện tương ứng.

# 5. Phần kết

Vâng, đó là cách tôi đã hướng dẫn bạn cách sử dụng SUBTOTAL. hàm số để thực hiện lọc dữ liệu từ bảng tính Excel rồi ha.

Hàm SUBTOTAL thường được sử dụng kết hợp với tính năng Filter hoặc Avanced Fiter trong Excel để thống kê và tính toán kết quả lọc.

Ngoài chức năng SUBTOTAL, bạn cũng nên tìm hiểu về PivotTables. PivotTable là tính năng cho phép chúng ta thực hiện các thống kê, tính toán, tổng hợp, phân tích dữ liệu tương tự như SUBTOTAL nhưng ở mức độ chuyên nghiệp hơn.

Xin chào tạm biệt và hẹn gặp lại các bạn trong những bài viết tiếp theo!

CTV: Nhựt Nguyễn – congthucexcel.com

Ghi chú: Bài viết này có hữu ích cho bạn không? Đừng quên đánh giá bài viết, thích và chia sẻ nó với bạn bè và gia đình của bạn!

Những bài viết liên quan : Thủ Thuật Excel

Nguồn : congthucexcel.com

Mọi sự sao chép, trích dẫn vui lòng ghi rõ nguồn: congthucexcel.com

from congthucexcel.com https://ift.tt/3l5GqD3
via Congthucexcel.com