Hàm SUBTOTAL trong Excel là một hàm trả về tổng phụ trong một danh sách hoặc cơ sở dữ liệu. Trong bài viết này sẽ hướng dẫn các bạn cách sử dụng hàm SUBTOTAL trong Excel để các bạn cùng tham khảo. Hàm SUBTOTAL được sử dụng rất linh hoạt trong Excel và khó sử dụng. Sau đây chúng ta hãy cùng tìm hiểu về cách sử dụng qua các ví dụ cụ thể, các bạn cùng tham khảo. Excel - Hàm SUBTOTAL, hàm tính toán cho một nhóm con trong danh sách Cú Pháp: =SUBTOTAL (function_num, ref1, [ref2], ...) Trong Đó: Function_num: Bắt buộc. Là một số từ 1 đến 11 hoặc 101 đến 111 để xác định dùng hàm nào trong tính toán tổng phụ trong danh sách. Ref1: Bắt buộc. Phạm vi vùng tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ cho nó. Ref2...: tùy chọn. Có thể từ 2 đến 254 phạm vi mà bạn muốn tính tổng phụ cho nó. Ghi Chú: Nếu có hàm SUBTOTAL khác lồng đặt tại các đối số ref1, ref2,... thì các hàm lồng này sẽ bị bỏ qua không được tính toán nhằm tránh trường hợp tính toán 2 lần. Đối với các hằng số function_num từ 1 đến 11, hàm SUBTOTAL tính toán cả các giá trị ẩn trong vùng dữ liệu (hàng ẩn). Đối với các hằng số từ 101 đến 111 chỉ tính toán cho các giá trị không ẩn trong vùng dữ liệu. Hàm SUBTOTAL sẽ bỏ qua bất kỳ hàng nào bị ẩn bởi lệnh Filter (Auto Filter). Hàm SUBTOTAL được dùng cho các cột dữ liệu. Nó không được dùng cho các hàng dữ liệu. Bất kỳ dữ liệu nào dưới dạng 3-D thì hàm SUBTOTAL sẽ báo lỗi #VALUE! Chức Năng: Tính toán một danh sách hoặc cơ sở dữ liệu. Ví dụ hàm SUBTOTAL trong Excel Cho một bảng dữ liệu như sau: Sử dụng hàm SUBTOTAL để tính tổng và trung bình cộng của cột dữ liệu D4:D9. Sử dụng hàm SUBTOTAL cho ô giá trị D10 với thông số Function_num là 9 để tính tổng. Công thức sẽ là: =SUBTOTAL (9, D4:D9) Sử dụng hàm SUBTOTAL cho ô giá trị D11 với thông số Function_num là 1 để tính trung bình cộng. Công thức sẽ là =SUBTOTAL (1, D4:D9) Hàm SUBTOTAL là một hàm rất linh hoạt những cũng khá khó sử dụng trong Excel với việc sử dụng các hằng số function để triệu tập lệnh. Nhưng ưu điểm của nó là bạn cũng có thể sửa lại phép tính một cách dễ dàng hơn bằng cách thay lại hằng số function cho nó. Và cùng với công cụ hỗ trợ tính toán SUBTOTAL, các bạn có thể dễ dàng tính toán với các yêu cầu phức tạp hơn so với việc sử dụng các phép tính toán thông thường mà hàm SUBTOTAL hỗ trợ. SUBTOTAL cũng là 1 trong những hàm tính toán thông dụng trong Excel, nó cũng là hàm khá phổ biến trong kế toán, bên cạnh đó, việc kết hợp với các hàm như hàm VLOOKUP, hàm HLOOKUP giúp cho hàm SUBTOTAL trở lên link hoạt hơn.
Hàm SUBTOTAL trong Excel – Cách dùng hàm SUBTOTAL qua ví dụ 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! 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ụ NguyenHungPlus.Com 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 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 NguyenHungPlus.Com để 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!
