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.

cach-su-dung-ham-subtotal-trong-excel (1)

#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.
Function_num

(gồm giá trị ẩn)

Function_num

(không gồm giá trị ẩn)

Hàm Mô tả
1 101 AVERAGE Tính trung bình cộng
2 102 COUNT Đếm ô chứa giá trị số
3 103 COUNTA Đếm ô chứa giá trị khác rỗng
4 104 MAX Giá trị lớn nhất
5 105 MIN Giá trị nhỏ nhất
6 106 PRODUCT Tính tích các giá trị
7 107 STDEV Ước tính độ lệnh chuẩn dựa trên mẫu
8 108 STDEVP Ước tính độ lệnh chuẩn dựa trên toàn bộ tổng thể được cung cấp ở dạng đối số
9 109 SUM Tính tổng các giá trị
10 110 VAR Ước tính phương sai cho một tập hợp mẫu
11 111 VARP Ước tính phương sai cho một tập hợp
  • 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:

cach-su-dung-ham-subtotal-trong-excel (2)

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

cach-su-dung-ham-subtotal-trong-excel (3)

    • 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

cach-su-dung-ham-subtotal-trong-excel (5)

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)

cach-su-dung-ham-subtotal-trong-excel (6)

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

Ngoài cách 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 của bài viết này mình chỉ hướng dẫn ngắn gọn và không giải thích gì về PivotTable. Nếu bạn chưa biết gì về hàm này thì có thể tìm hiểu thêm về nó tại đây nha.

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

cach-su-dung-ham-subtotal-trong-excel (7)

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

cach-su-dung-ham-subtotal-trong-excel (8)

+ 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/RangeData!$A$1:$E$11
  • LocationData!$A$14

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

cach-su-dung-ham-subtotal-trong-excel (9)

+ 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”

cach-su-dung-ham-subtotal-trong-excel (10)

+ 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.

cach-su-dung-ham-subtotal-trong-excel (11)

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.

cach-su-dung-ham-subtotal-trong-excel (12)

#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

Note: Bài viết này hữu ích với bạn chứ? Đừng quên đánh giá bài viết, like và chia sẻ cho bạn bè và người thân của bạn nhé !

Administrator: Kiên Nguyễn Blog

Có một câu nói của người Nhật mà mình rất thích đó là " Người khác làm được thì mình cũng làm được ". Chính vì thế mà hãy theo đuổi đam mê, thành công sẽ theo đuổi bạn ! Nếu như bạn đang gặp khó khăn và cần sự trợ giúp thì hãy comment phía bên dưới mỗi bài viết để nhận được sự hỗ trợ từ cộng đồng Blog Chia Sẻ Kiến Thức nhé.

Một vài lưu ý trước khi comment :

Thư điện tử của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *
Tất cả các comment của các bạn sẽ được giải đáp trong vòng 48h (thường vào buổi tối)!
Không được sử dụng từ khóa trong ô 'Name', bạn hãy dùng tên thật hoặc Nickname của bạn !
Không dẫn link sang trang web/blog khác. Xem quy định comment tại đây. Thank All!

Leave a Reply

Your email address will not be published. Required fields are marked *