Trang thông tin điện tử Công an Bắc Kạn

https://conganbackan.vn

Cách dùng hàm SUBTOTAL trong Excel

Hàm Subtotal trong Excel được dùng ở rất nhiều trường hợp khác nhau, giúp bạn tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, khác với hàm SUM trong Excel.

Hàm Subtotal trong các hàm cơ bản Excel được dùng ở rất nhiều trường hợp khác nhau, giúp bạn tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, khác với hàm SUM trong Excel, như đếm số ô, tính trung bình trong Excel, tìm giá trị lớn nhất/nhỏ nhất hay tính tổng giá trị danh sách đã lọc trong Excel mà Quản trị mạng đã hướng dẫn bạn đọc. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm SUBTOTAL trong Excel.

Hàm SUBTOTAL trong Excel

Hàm SUBTOTAL có cú pháp =SUBTOTAL(function_num, ref1, [ref2],…). Trong đó:

  • Function_num: Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.
  • Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.

Lưu ý:

  • Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc.
  • Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì sẽ được bỏ qua để tránh tính trùng 2 lần.
  • Nếu function_num từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).
  • Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL sẽ bỏ qua.

Cách dùng hàm SUBTOTAL trong Excel

Ví dụ sử dụng hàm SUBTOTAL

1. Tính tổng các hàng được lọc 

Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel.

2. Đếm các ô được lọc không trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 2

Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 3

Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 4

Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 5

Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 6

3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau

Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.

Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 7

Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 8

Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 9

Cách lỗi khi thực hiện hàm SUBTOTAL trong Excel

Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:

  • #VALUE!: Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.
  • #DIV/0!: Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số).
  • #NAME? tên hàm SUBTOTAL sai chính tả.

Cách dùng hàm SUBTOTAL trong Excel - Ảnh minh hoạ 10

Xem thêm:

Nguồn tin: quantrimang.com

Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây