Sử dụng SUBTOTAL trong Excel

SUBTOTAL trong Excel là gì?

SUBTOTAL là một hàm được thiết kế sẵn trong Excel. Nếu bảng tính của bạn làm việc với những con số thì đây chính là hàm bạn thường xuyên cần đến. Hàm SUBTOTAL có rất nhiều chức năng, giúp ích rất nhiều cho bạn trong mọi công việc với bảng tính.

Chức năng của hàm SUBTOTAL trong Excel

Hàm SUBTOTAL là một trong các hàm cơ bản của Excel. Nó được dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, đế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. Nó ẩn chứa rất nhiều chức năng nên nếu bạn biết cách sử dụng thì công việc của bạn sẽ được thực hiện nhanh chóng vô cùng.

Cú pháp và cách sử dụng hàm SUBTOTAL trong Excel

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

Công thức của hàm SUBTOTAL Excel là:

= SUBTOTAL (function_num; ref1, [ref2], …)

Trong đó:

• function_num: bạn sẽ thay bằng các con số từ 1 đến 11 và 101 đến 111. Nó sẽ quyết định hàm nào được dùng để tính toán. Bạn cần nhớ theo bảng sau:

• Ref1, Ref2, …: pham vi để tính toán.

Sử dụng hàm SUBTOTAL trong Excel để tính tổng các hàng được lọc

Để tính tổng các hàng đã được lọc sử dụng hàm SUBTOTAL trong Excel, bạn sử dụng công thức sau:

= SUBTOTAL (9; phạm vi)

Với 9 là số thứ tự của hàm SUM khi sử dụng SUBTOTAL.

Kết quả:

Sử dụng hàm SUBTOTAL trong Excel để đếm các ô được lọc không trống

Để đếm các ô tính không trống, bạn sẽ cần dùng đến hàm mang số 3, tức là hàm COUNTA – hàm đếm các ô không trống.

= SUBTOTAL (3, phạm vi)

Ví dụ bên dưới là để đếm các ô không trống trong cột Số ngày công:

Kết quả:

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

Chẳng hạn, trong một bảng tính, bạn cần tính nhiều công thức trung bình cộng khác nhau trong cùng một sheet. Nếu bạn đã tính trung bình cộng cho những thành phần nhỏ hơn, thì Excel sẽ tự động bỏ qua dòng tính trung bình cộng đó mà chỉ tính ở các thành phần.

Ví dụ bên dưới sẽ giúp bạn hiểu hơn về chức năng này của SUBTOTAL.

Đầu tiên, người tạo bảng cần tính số lượng đậu xanh trung bình (cả loại I và loại II) nên công thức sẽ là:

= SUBTOTAL(1;B2:B3)

Tiếp đó, ở dòng TRUNG BÌNH SỐ LƯỢNG ĐẬU, dòng này người tạo bảng cần tính trung bình của tất cả các loại đậu nên công thức sẽ là:

= SUBTOTAL(1;B2:B7)

Kết quả thu được ở dòng TRUNG BÌNH SỐ LƯỢNG ĐẬU là 6, tức là bằng tổng số kg (5+4+10+4+7) chia cho 5 loại đậu. Excel đã bỏ qua dòng TRUNG BÌNH SỐ LƯỢNG ĐẬU XANH dù phần phạm vi người tạo bảng có chọn cả dòng này.

Ví dụ dùng SUBTOTAL trong Excel

Dưới đây là ví dụ cụ thể về việc sử dụng hàm SUBTOTAL trong Excel ở những công việc phổ biến:

Đầu tiên là tính tổng số lượng đậu:

Tiếp theo là tính trung bình số lượng đậu:

Tìm số lượng đậu cao nhất (MAX):

Tìm số lượng đậu thấp nhất (MIN):

Kết quả:

Để sử dụng hàm SUBTOTAL một cách thật thuận tiện, bạn chỉ cần nhớ được số thứ tự của các hàm phổ biến. Chẳng hạn như để tính tổng (sử dụng hàm SUM) thì sẽ là số 9, để tính trung bình (sử dụng hàm AVERAGE) thì sẽ là số 1. Tuy nhiên, Excel cũng sẽ hiện gợi ý bảng các số thứ tự này cho bạn khi bạn nhập đúng tên hàm SUBTOTAL, nên bạn không cần quá lo lắng.

Hàm SUBTOTAL không ra kết quả – Một số lỗi thường gặp

Lỗi #value trong hàm SUBTOTAL

Khi sử dụng hàm SUBTOTAL trong Excel, bạn đôi khi sẽ gặp lỗi #value. Đây là lỗi xảy ra với 1 trong 2 nguyên nhân sau:

• Số được nhập ở phần function_num là số không nằm trong phạm vi 1-11 hoặc 101-111.
• Phạm vi bạn lựa chọn (phần sau dấu chấm phẩy) là tham chiếu dạng 3D.

Để khắc phục, bạn chỉ cần sửa đổi lại thông tin và kiểm tra chắc chắn dữ liệu bạn nhập vào không bị dính một trong hai lỗi trên.

Lỗi #DIV/0! trong hàm SUBTOTAL

Đây là lỗi xảy ra khi bạn sử dụng hàm để chia một số nào đó hoặc một tổng nào đó cho 0. Thông thường, lỗi này sẽ xảy ra khi bạn 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ố.

Việc bạn cần làm để fix lỗi này là kiểm tra lại thông tin dữ liệu bạn đã nhập cho ô tính để xem bạn có nhầm lẫn hay không.

Lỗi #NAME? trong hàm SUBTOTAL

Lỗi #NAME? sẽ hiện lên trong ô tính của bạn nếu bạn nhập sai tên hàm. Vì thế, bạn cần nhập thật chính xác tên hàm SUBTOTAL để không bị hiện lỗi này bạn nhé. Một mẹo để bạn biết mình nhập đúng hay chưa là khi nhập trong ô tính, bạn chỉ cần nhập dấu ‘=’ và đúng tên hàm, sau đó nhập tiếp dấu ‘(‘ là Excel sẽ hiện ra một bảng gợi ý để giúp bạn lựa chọn đúng các phần tử của hàm. Nếu bạn nhập sai tên hàm thì bảng chọn này sẽ không hiện ra.

Hy vọng sau bài đăng này, bạn đã hiểu cách sử dụng hàm SUBTOTAL trong Excel. Đây là một hàm cực kì tiện lợi đối với những bạn dân văn phòng đúng không? Nếu thấy bài viết bổ ích, đừng quên Like, Share cũng như ghé thăm Phanmemgoc.vn thường xuyên nhé!

Tham khảo: Microsoft