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 nhằm thực hiện một hoặc một số chức năng nào đó. Mục đích là để giúp người dùng dễ dàng hơn trong việc sử dụng, tiết kiệm thời gian và công sức hơn.

Đến nay, số lượng hàm của Excel khá là lớn, được phân chia thành nhiều nhóm khác nhau như là: tài chính, lô-gic, chuỗi, ngày tháng & thời gian, dò tìm & tra cứu, toán học & lượng giác, …

Trong bài viết này, chúng ta sẽ cùng nhau tìm hiểu về hàm SUBTOTAL. 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.

#1. Giới thiệu một chút về hàm SUBTOTAL

Hàm SUBTOTAL thực hiện các phép thống kê như tổng, trung bình, lớn nhất, nhỏ nhất, … dựa vào kết quả lọc được từ bảng dữ liệu.

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

=SUBTOTAL(function_num, ref1,…)

Trong đó:

• Function_num là số đại diện cho hàm, chẳng hạn số 9 sẽ đại diện cho hàm tính tổng, số 6 sẽ đại diện cho hàm tính tích, … Chi tiết và đầy đủ hơn thì bạn xem bảng bên dưới nha.

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

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

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

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

Cho bảng số liệu thống kê như hình bên dưới:

Câu hỏi:

• a) Trích lọc dữ liệu theo cột chức vụ.
• b) Thống kê tổng lương, lương trung bình, lương cao nhất và lương thấp nhất theo kết quả trích lọc dữ liệu của câu a).

Đáp án:

a) Cách lọc dữ liệu theo cột chức vụ

• • 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: Chọn vào tại cột chức vụ rồi lần lượt chọn từng chức vụ => chọn OK

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

• =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ự

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

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

+ Bước 3: Hộp thoại Create PivotTable xuất hiện, bạn hãy thực hiện các khai báo:

• Table/Range là Data!$A$1:$E$11
• Location là Data!$A$14

Chú ý rằng Data ở đây là tên của Sheet hiện hành nha các bạn.

+ Bước 4: Tại PivotTable Fields khai báo:

• Filters “Chức vụ”
• Values “Sum of Lương”, “Sum of Lương2”, “Sum of Lương3” và “Sum of Lương4”

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

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

Thực hiện như trên cho Sum of Lương 3, 4 với hàm Max, Min. Ngoài ra, tại Custom Name bạn có thể đặt tên lại theo ý muốn.

Kết quả như hình bên dưới, khi chọn vào để lọc thì tổng lương, lương trung bình, lương cao nhất và lương thấp nhất sẽ xuất hiện tương ứng.

#5. Lời kết

Vâng, như vậy là mình đã hướng dẫn các bạn cách sử dụng hàm SUBTOTAL để 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ê, tính toán kết quả lọc.

Ngoài hàm SUBTOTAL ra thì bạn cũng nên tìm hiểu về PivotTable. PivotTable là tính năng cho phép chúng ta thực hiện 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 – Blogchiasekienthuc.com