Khám phá cách sử dụng hàm Subtotal trong Excel như một chuyên gia
Hướng dẫn sử dụng hàm Subtotal trong Excel để tính tổng phụ và thực hiện các tính toán khác dễ dàng hơn Tìm hiểu về cách sử dụng công thức hàm, lưu ý khi sử dụng và các ví dụ thực tế
Hàm Subtotal đang được sử dụng rộng rãi trong Excel hiện nay. Việc sử dụng hàm này sẽ giúp người dùng làm việc hiệu quả hơn. Đặc biệt, cách tính hàm này cũng rất đơn giản với các bước cơ bản. Hãy tìm hiểu ngay cách sử dụng hàm Subtotal trong Excel để tính toán nhanh chóng.
Hàm Subtotal là gì?
Hàm Subtotal trong Excel là một công cụ linh hoạt dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trái ngược với các hàm khác trong Excel có công dụng cụ thể, hàm Subtotal có thể được sử dụng để thực hiện nhiều loại tính toán khác nhau. Bên cạnh việc tính tổng phụ, người dùng cũng có thể sử dụng hàm này để đếm số ô, tính trung bình và tìm giá trị lớn nhất/ nhỏ nhất,..
Hàm Subtotal là một hàm trong Excel sử dụng để tính tổng phụ.
Công thức hàm tính Subtotal
Hàm Subtotal trên các phiên bản Excel từ 2007 đến 2016 được sử dụng thường xuyên bởi người dùng. Hàm này giúp tính toán danh sách số theo nhiều cách khác nhau, bao gồm tính trung bình, tổng, giá trị lớn nhất và nhỏ nhất. Công thức sử dụng hàm Subtotal có dạng như sau:=SUBTOTAL (function_num, ref1, [ref2], …)
Trong đó:
Function_numlà một số từ 1 đến 11 hay 101 đến 111. Từ đó giúp xác định dùng hàm nào trong cách tính toán tổng phụ trong danh sách.
Phiên bản mới: Ref1 là phạm vi vùng tham chiếu đầu tiên mà người dùng mong muốn tính tổng phụ.
Phiên bản mới: Ref2... có thể là 2 đến 254 phạm vi mà người dùng muốn tính tổng phụ.
Những lưu ý khi sử dụng hàm Subtotal
Hiện tại, hàm Subtotal đem lại nhiều ưu điểm hấp dẫn cho người dùng. Tuy nhiên, cũng không thể bỏ qua một số lưu ý quan trọng cần nhớ trước khi sử dụng hàm Subtotal như sau:Người dùng sẽ gặp lỗi #VALUE! khi sử dụng các giá trị từ 1-11 hoặc 101-111 trong Excel. Tình huống này cũng áp dụng khi tham chiếu ô 3D, trong đó một ô cụ thể được tham chiếu từ nhiều trang tính trong cùng một phạm vi.
Khi sử dụng các phạm vi ô ngang như A1:D1, giá trị ẩn sẽ tự động đưa vào. Điều này là một hạn chế không thể khắc phục được của Subtotal.
Người dùng khi sử dụng Subtotal trên dữ liệu nhằm đảm bảo rằng các giá trị ẩn luôn được bỏ qua, ngay cả khi đối số function_num được sử dụng.
Hàm Subtotal có thể được sử dụng trong hình thức hàm lồng nhau, bao gồm các công thức chứa hàm IF.
Nếu Subtotal không nằm trong dữ liệu Refl, nó sẽ không được tính. Như vậy, nó được loại trừ khỏi tổng cộng.
Excel cho phép sử dụng tối đa 254 ô trong công thức Subtotal.
Những điều cần lưu ý quan trọng trước khi sử dụng hàm Subtotal trong Excel.
Cách sử dụng hàm Subtotal trong Excel để tính tổng phụ và ví dụ
Thông tin về việc sử dụng hàm Subtotal trong Excel để tính tổng phụ nhận được sự quan tâm đặc biệt từ người dùng. Dưới đây là các bước bạn cần thực hiện:Tính tổng vùng được lọc
Để lọc ra tổng KPI của đội A, hãy thực hiện theo các bước sau:Bước 1: Bạn hãy làm việc trên bảng chứa dữ liệu cần tính bằng cách bôi đen nó và sau đó nhấn vào “Home”. Tiếp theo, hãy chọn “Sort and Filter” và nhấn vào “Filter”.
Bôi đen bảng chứa dữ liệu cần tính
Bước 2: Bấm chọn
ở tiêu đề cột “Đội” để hiển thị dấu mũi tên hình tam giác ngược. Sau đó chọn “A” và nhấn OK.
Bước 3: Excel sẽ lọc bảng dữ liệu cho đội A. Tại ô cần lấy kết quả, nhập =SUBTOTAL(9,D2:D11). Trong đó: function_num = 9 là giá trị đối số tương ứng với hàm cần sử dụng. Khi nhập công thức hàm, người dùng sẽ thấy giá trị đối số này được hiển thị.
Bảng dữ liệu của đội A được lọc bằng Excel
Đếm các ô được lọc không trống
Người dùng cần đếm số lượng sinh viên trong nhóm C có link bài tập để tiện thống kê. Trước tiên, người dùng sẽ lọc ra các sinh viên thuộc nhóm C. Sau đó, sau khi đã lọc dữ liệu xong, tại ô cần lấy kết quả, người dùng nhập: =SUBTOTAL(3,D4:D14). Trong đó:Hàm sử dụng có giá trị đối số là 3. Người dùng sẽ thấy giá trị đối số khi nhập công thức Subtotal. Sau đó, hãy đếm các ô không trống và chọn hàm COUNTA với giá trị đối số là 3.
Phạm vi cần tính tổng là D4:D14.
Đếm số ô được lọc không trống được thực hiện bởi người dùng. Đối số có thể sử dụng là 3. Ngoài ra, cần đếm cả các giá trị đã bị ẩn thủ công do chức năng "Hide". Đối số có thể là 103 và bỏ qua việc đếm các giá trị đã bị ẩn thủ công. Điều này giúp tính số ô trống trong các hàng đã bị lọc.
Đánh số thứ tự nhanh
Nếu người dùng chỉ cần đánh số thứ tự cho danh sách dữ liệu mà không có các hàng trống, họ có thể sử dụng công thức =SUBTOTAL(3,$B$2:B2). Trong đó:function_num có giá trị là 3 và được sử dụng để tính toán bằng COUNTA, có nghĩa là đếm số ô không trống.
ref1 được sử dụng để đếm số ký tự phát sinh trong khoảng từ ô B2 đến ô B2. Trong đó, ô B2 được cố định bằng cách đặt ký tự "$" ở trước địa chỉ hàng và cột. Khi sao chép công thức, giá trị sẽ thay đổi theo ô B2:B3, B2:B4, B2:B5,... Điều này đảm bảo hàm sẽ đếm đúng và mang về kết quả như mong muốn.
Đánh số thứ tự nhanh
Điểm khác biệt giữa hàm Subtotal và hàm SUM
Hiện nay, sự khác biệt giữa hàm Subtotal và hàm SUM đang là điều được nhiều người dùng quan tâm đặc biệt. Điều này sẽ được trình bày dựa trên ba phương diện sau đây:| Hàm SUM | Hàm Subtotal | |
| Mục đích | Tính tổng cố định hay tính tổng chung cho tất cả các đối tượng. - Những đối tượng này không tách thành tổng các nhóm nhỏ và giá trị không bị thay đổi. Khi người dùng lồng các hàm SUM lại với nhau, số liệu sẽ mặc định tính hai lần | Tính tổng cố định và không cố định - Tính tổng riêng cho từng nhóm, giá trị sẽ thay đổi khi thực hiện lệnh Filter. Khi người dùng lồng ghép các hàm Subtotal với nhau, số liệu sẽ không bị tính hai lần. |
| Tình huống | Được sử dụng trong trường hợp tính tổng phát sinh nợ có trong sổ nhật ký chung | Được dùng để tính tổng các phát sinh đầu, trong và cuối kỳ của khoản bất kỳ Hàm này còn tính tổng số tiền tồn cuối ngày trong số tiền mặt, gửi ngân hàng. |
Điểm khác biệt giữa hàm Subtotal và hàm SUM
