Microsoft Excel

Sử dụng SUBTOTAL trong Excel

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 Phanmemgoc.vn?

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
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
  • 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.

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

Phần Mềm Gốc

Phần Mềm Gốc Là Website uy tín chuyên chia sẻ miễn phí các phần mềm máy tính, game offline, office, windows, android và những thủ thuật máy tính.

Related Articles

Back to top button