Hàm SUBTOTAL trong Excel là hàm tính toán tổng phụ tổng hợp vì trong hàm còn hỗ trợ thêm 11 function giúp nó có sức mạnh tính toán tương đương các hàm SUM, AVERAGE, COUNTA, PRODUCT,…Nếu xét theo 1 khía cạnh khác thì hàm này có khi còn mạnh hơn các hàm trên rất nhiều ví nó rất linh hoạt. Để hiểu rõ được cách dùng hàm SUBTOTAL trong Excel chúng ta đi vào tìm hiểu ở nội dung dưới đây nhé.
Hướng dẫn cách dùng hàm SUBTOTAL trong Excel
Cú pháp: =SUBTOTAL(function_num,ref1,[ref2],…)
Giải thích các giá trị
- function_num: Nhập vào một số để chỉ định hàm sử dụng trong phép tính. Giá trị nhập vào là một số từ 1 đến 11 (tính toán bao gồm các giá trị ẩn) hoặc từ 101 đến 111 (tính toán không bao gồm các giá trị ẩn). Đây là giá trị bắt buộc. Các số tương ứng với các hàm như sau:
- ref1: Phạm vi vùng chọn tham chiếu đầu tiên mà bạn muốn tính toán. Đây là giá trị bắt buộc.
- ref2: Phạm vi hoặc chuỗi từ 2 đến 254 mà bạn muốn tính.
Lưu ý:
- Nếu có hàm SUBTOTAL khác trong giá trị ref1, ref2,…thì các hàm lồng vào này sẽ được bỏ qua để tránh bị tính 2 lần.
- Lựa chọn các function_num cho hàm SUBTOTAL sao cho phù hợp. Các hàm từ 1 đến 11 sẽ tính toán bao gồm các giá trị ẩn trong vùng dữ liệu được chọn. Các hàm từ 101 đến 111 sẽ loại trừ các giá trị ẩn trong vùng dữ liệu (chỉ tính toán với các giá trị hiển thị).
- Hàm SUBTOTAL với function_num bất kỳ sẽ không được áp dụng nếu hàng bị ẩn bởi lệnh Filter (Auto Filter).
- Hàm SUBTOTAL chỉ dùng cho cột dữ liệu hoặc phạm vi dọc mà không dùng cho hàng hay phạm vi ngang. Vì thế khi bạn ẩn cột sẽ không ảnh hưởng tới giá trị tính toán, còn ẩn hàng thì có ảnh hưởng.
- Các tham chiếu nào là tham chiếu 3-D thì hàm SUBTOTAL sẽ trả về giá trị #VALUE!
Tham khảo thêm
- Cách sử dụng hàm VLOOKUP trong Excel qua ví dụ
- Hướng dẫn cách dùng hàm Find, hàm FindB trong Excel qua ví dụ
- Cách dùng hàm SUMIF – Hàm tính tổng có điều kiện
Ví dụ về cách dùng hàm SUBTOTAL trong Excel
Giả sử chúng ta có bảng dữ liệu sau:
Yêu cầu ở đây là tính tổng số sản phẩm và tìm ra lượng sản phẩm còn ít nhất là bao nhiêu và nhiều nhất là bao nhiêu.
Chúng ta sẽ sử dụng hàm SUBTOTAL và dựa vào bảng giá trị function_num ở trên để chọn hàm và tính được lần lượt các giá trị như sau:
- Tại ô D15 công thức tính tổng là =SUBTOTAL(9;D5:D13)
- Tại ô D16 công thức tính giá trị nhỏ nhất là =SUBTOTAL(5;D5:D13)
- Tại ô D17 công thức tính giá trị lớn nhất là =SUBTOTAL(4;D5:D13)
Ta có kết quả như sau
Như vậy qua ví dụ trên bạn đã hiểu được cách thức sử dụng hàm SUBTOTAL rồi chứ. Cũng dễ đúng không nào. Tiếp dưới đây mình là thêm ví dụ nữa để các bạn thấy được điểm mạnh của hàm SUBTOTAL là như nào nhé 🙂
Ví dụ về điểm nổi bật của hàm SUBTOTAL trong Excel
Cũng trong ví dụ trên bên cột E chúng ta sử dụng các hàm SUM, MIN, MAX để tính và có kết quả như sau
Kết quả tính toán là giống nhau đúng không nào. Bây giờ mình sẽ làm như sau và bạn sẽ thấy hàm SUBTOTAL phát huy được điểm mạnh của nó như nào.
Ví dụ trong bảng dữ liệu chúng ta dùng Filter để ẩn số liệu của nhà cung cấp Lazada khi đó chúng ta sẽ có kết quả như sau:
Kết quả đã có sự khác biệt khi sử dụng hàm SUBTOTAL
- Với hàm SUBTOTAL tổng là 34 vì hàm SUBTOTAL chỉ tính tổng của các sản phẩm hiển thị của nhà cung cấp Adayroi, còn hàm SUM vẫn tính cả tổng các mặt hàng đã bị ẩn.
- Giá trị nhỏ nhất thì cả 2 hàm giống nhau vì giá trị trong bảng lúc hiển thị và lúc ẩn đều là 7.
- Giá trị lớn nhất của hàm SUBTOTAL là 10 vì hàm SUBTOTAL chỉ dò tìm các giá trị hiển thị còn hàm MAX vẫn dò tìm cả các giá trị bị ẩn.
Có 1 điểm đặc biệt trong kết quả này nếu bạn chú ý kỹ sẽ thấy đó là trong các công thức trên chúng ta dùng các giá trị function_num là 9,5,4 (các function_num sẽ tính toán bao gồm cả các giá trị ẩn), nhưng trong này nó lại không tính (có gì sai ư?).
=> Không sai đâu nhé. Nếu các bạn chưa hiểu thì đọc lại mục lưu ý ở trên sẽ rõ (Hàm SUBTOTAL với function_num bất kỳ sẽ không được áp dụng nếu hàng bị ẩn bởi lệnh Filter (Auto Filter).)
Bạn có thể kiểm tra kết quả bằng cách ẩn các hàng của nhà cung cấp Lazada bằng cách chọn các hàng đó và click phải chuột chọn Hide.
Ví dụ về cách dùng công cụ SUBTOTAL
Như bạn đã thấy qua các ví dụ trên thì hàm SUBTOTAL mạnh mẽ và hữu dụng như nào rồi. Mình sẽ thêm ví dụ sau đây bạn thấy công cụ SUBTOTAL trong Excel tiện lợi và hữu dụng đến mức nào nhé.
Cũng với ví dụ NguyenHung.Net dùng ở trên mình sẽ dùng công cụ SUBTOTAL để tính tổng các sản phẩm của mỗi nhà cung cấp.
Yêu cầu cần thiết là bạn phải sắp xếp tên nhà cung cấp trùng nhau ở gần nhau. Ở ví dụ này mình đã sắp xếp trước rồi nên mình không cần làm việc này nữa.
Sau khi sắp xếp xong bạn chọn vùng dữ liệu cần tính toán rồi vào tab Data ->Subtotal
Hộp thoại Subtotal hiện lên như sau:
Trong đó chúng ta có các thông số như sau:
- Mục At each change in: Bạn chọn Nhà cung cấp vì chúng ta sẽ tính tổng cho từng Nhà cung cấp.
- Mục Use function: Bạn chọn hàm cần để tính. Ở đây mình tính tổng nên sẽ chọn là SUM.
- Mục Add subtotal to: Bạn chọn cột cần tính toán. Ở đây mình muốn tính số lượng nên chọn cột Số lượng.
Ấn OK và chúng ta có kết quả thu được như sau
Download file ví dụ về hàm SUBTOTAL trong Excel
Bạn hãy download file ví dụ dưới đây về để tham khảo cách sử dụng hàm SUBTOTAL trong Excel nhé. Tải về và thực hành nhiều hơn nữa để hiểu và nắm rõ hơn về hàm SUBTOTAL.
Lời kết
Với những kiến thức về hàm SUBTOTAL ở trên hi vọng các bạn có thể hiểu được cách dùng hàm SUBTOTAL và vận dụng nó tốt hơn trong học tập và trong công việc. Hàm SUBTOTAL là 1 hàm rất linh hoạt, với những ưu điểm của hàm này bạn có thể dùng để tính toán hiệu quả.
Nếu thấy bài viết hay hãy like và share ngay cho mọi người nhé. Nếu có thắc mắc gì cần hỏi đáp hãy để lại ý kiến dưới khung bình luận nhé. Nhớ theo dõi chuyên mục thủ thuật Excel trên NguyenHung.Net để học nhiều hơn các kiến thức về Excel nhé. Chúc các bạn thành công!
