Sử dụng SUBTOTAL trong Excel

Tác giả: Minh Hiền - Ngày đăng: 08-10-2021

Hàm SUBTOTAL trong Excel là hàm có thể giúp bạn thực hiện đếm số ô, tính giá trị trung bình hay tìm giá trị lớn nhất nhỏ nhất. Mọi công việc đều chỉ cần ghi nhớ một hàm, cực kì thuận tiện! Vậy nên hãy cùng ThuthuatOffice tìm hiểu ngay về hàm này!

Khi bạn làm việc với các bảng tính chứa nhiều con số, bạn sẽ rất cần đến hàm SUBTOTAL trong Excel. Liệu bạn đã biết đây là hàm gì và nó có chức năng thế nào trong công việc của bạn chưa? Nếu chưa thì còn chần chừ gì mà chưa đọc ngay bài viết này của ThuthuatOffice?

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 11101 đế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:

Function_num
(bao gồm giá trị ẩn)

Function_num
(bỏ qua giá trị ẩn)
Hàm
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP
  • 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.

SUBTOTAL trong Excel

Kết quả:

SUBTOTAL trong Excel

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:

SUBTOTAL trong Excel

Kết quả:

SUBTOTAL trong Excel

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)

SUBTOTAL trong Excel

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)

SUBTOTAL trong Excel

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.

SUBTOTAL trong Excel

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:

SUBTOTAL-trong-Excel

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

SUBTOTAL trong Excel

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

SUBTOTAL trong Excel

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

SUBTOTAL trong Excel

Kết quả:

SUBTOTAL trong Excel

Để 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.

ThuthuatOffice cũng có rất nhiều bài đăng khác về những mẹo trong Excel mà bạn có thể tham khảo:

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 ThuthuatOffice thường xuyên nhé!

Tham khảo: Microsoft

Đánh giá bài viết
Bài viết này hữu ích chứ? Yes No
× Vui lòng cho chúng tôi biết lỗi gì trong bài viết trên

Bài viết chứa thông tin sai

Bài viết không có thông tin mà tôi cần

× Chúng tôi nên cải thiện nội dung trên như thế nào?

× Cảm ơn sự phản hồi của bạn!

Mong bạn tiếp tục ủng hộ ThuThuatOffice. Cảm ơn phản hồi của bạn

Theo dõi chúng tôi tại:: Facebook Pinterest
Chuyên mục: Excel