Cách sử dụng hàm SUBTOTAL trong Excel

Trong bài viết này, chúng ta sẽ tìm hiểu Cách sử dụng hàm SUBTOTAL trong Excel.

Nhận kết quả trên các hàng đã lọc

Vấn đề lớn nhất với các hàm SUM, AVERAGE, COUNT và các hàm SỐ khác là chúng bao gồm các hàng bị bộ lọc ẩn. Chúng không hoạt động chính xác khi chúng ta muốn tính tổng, đếm hoặc thực hiện bất kỳ tác vụ số nào khác chỉ trên một ô hiển thị. Dưới đây là các bước trong hàm SUBTOTAL của Excel.

SUBTOTAL Cú pháp hàm :

=SUBTOTAL (function_number, ref1, [ref2]…)

Hàm_số: Tổng phụ hỗ trợ các hàm số khác nhau. Để dễ dàng truy cập chúng, nó sử dụng một số chức năng. Dưới đây là bảng hàm số với các chức năng tương ứng.

Số chức năng để bao gồm các hàng ẩn thủ công	Số chức năng để bỏ qua hàng ẩn thủ công	Chức năng
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
số 8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Ref1 : Phạm vi được đặt tên hoặc tham chiếu của các số.

[ref2] : phạm vi được đặt tên tùy chọn hoặc phạm vi tham chiếu đến số.

Ví dụ :

Tất cả những điều này có thể gây nhầm lẫn để hiểu. Hãy hiểu cách sử dụng chức năng bằng một ví dụ. Ở đây chúng tôi có bảng này của bất động sản. Bây giờ tôi muốn thêm một hàng tổng nhưng nó sẽ chỉ hiển thị cho tôi tổng các ô hiển thị khi tôi áp dụng bộ lọc.

Viết công thức này vào cuối bảng hoặc đầu bảng. Lựa chọn của bạn…

=SUBTOTAL(9,C2:C16)

Bây giờ, bất cứ khi nào bạn áp dụng bộ lọc, chỉ tổng số ô hiển thị mới được hiển thị.

Như bạn có thể thấy từ hình trên, tổng diện tích của các hàng được lọc (1250 + 530 + 590) là 2370. Điều này thật ấn tượng phải không.

COUNT giá trị trên các hàng đã lọc

Đối với bài viết này, chúng ta sẽ được yêu cầu sử dụng hàm SUBTOTAL . Bây giờ chúng ta sẽ tạo một công thức từ hàm. Ở đây chúng tôi được cung cấp một số giá trị trong một phạm vi và giá trị văn bản cụ thể làm tiêu chí. Chúng ta cần đếm các giá trị trong đó công thức bao gồm tất cả các giá trị kết thúc bằng văn bản hoặc mẫu đã cho

Công thức chung: –

= SUBTOTAL ( fun_num, range )

fun_num : số, num tương ứng với phép toán

phạm vi : phạm vi áp dụng hoạt động.

Ví dụ:

Tất cả những điều này có thể gây nhầm lẫn để hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ hiển thị bên dưới.

Ở đây chúng ta có dữ liệu nhập đơn hàng. Và chúng tôi đã áp dụng tiêu chí cho Khu vực, chỉ khu vực phía Đông được lọc.

Ở đây để tìm số lượng các giá trị được lọc. Chọn đối số phù hợp là fun_num. Fun_num là thao tác bạn muốn áp dụng. Ở đây để đếm các ô chúng ta sử dụng phép toán COUNTA, num là 3.

Sử dụng công thức:

= SUBTOTAL ( 3, B2:B14)

Như bạn có thể thấy tổng số hàng hiển thị là 11. Bây giờ chúng ta sẽ sử dụng một ví dụ nữa để trích xuất Trung bình của số lượng hiển thị hoặc được lọc.

Sử dụng công thức:

= SUBTOTAL ( 1 , C2:C14 )

Như bạn có thể thấy, số lượng đặt hàng trung bình nhận được từ khu vực phía Đông là khoảng 60. Bạn có thể sử dụng các phép toán khác nhau như trung bình, đếm, tối đa, tối thiểu, sản phẩm, độ lệch chuẩn, tổng hoặc biến thể theo kết quả được yêu cầu trên dữ liệu được lọc .

Dưới đây là một số ghi chú quan sát được hiển thị bên dưới.

Ghi chú:

1. Chọn đúng fun_num để có được kết quả mong muốn.
2. Công thức chỉ hoạt động với cả số và văn bản
3. Các toán tử như bằng ( = ), nhỏ hơn bằng ( <= ), lớn hơn ( > ) hoặc không bằng ( <> ) chỉ có thể được thực hiện trong các hàm chỉ áp dụng cho các số.