Hàm subtotal trong excel là hàm gì? cách sử dụng hàm này như thế nào? là câu hỏi mà rất nhiều bạn đang quan tâm và tìm hiểu, để phục vụ tốt cho việc học tập cũng như công việc được hoàn thành nhanh chóng và chính xác nhất.
Để tính tổng của một dãy số trong excel, thông thường các bạn sẽ nghĩ ngay tới hàm Sum vì hàm sum rất đơn giản, nhanh gọn và dễ dùng. Ngoài ra trong excel còn có tính năng Autosum cho nên chỉ bằng một cú click chuột trên góc trái màn hình bạn đã tính tổng một dãy số rất nhanh.
Nhưng yêu cầu của công việc không đơn thuần là chỉ tính tổng mà đòi nâng cao hơn, linh hoạt hơn đòi hỏi chúng ta cũng phải linh hoạt trong cách dùng hàm để có kết quả như mong muốn.
Trong bài viết hôm nay EVBN sẽ hướng dẫn các bạn sử dụng hàm subtotal. Hàm này sẽ khắc phục được các nhược điểm của hàm Sum, cách dùng đa dạng và rất linh hoạt với nhiều biến số. Bởi những ưu điểm vượt trội đó mà hàm nay được dùng gần như để thay thế cho hàm sum.
Chúng ta cùng đi tìm hiểu về cấu trúc và cách sử dụng hàm mới này nhé!
Hàm SUBTOTAL trong excel là gì?
Hàm subtotal trong excel là hàm tổng hợp được nhiều hàm, đối số đầu tiên sẽ quyết định ý nghĩa và kết quả.
Tùy thuộc vào đặc điểm của công việc, mục đích của công việc sẽ quyết định xem đối số đầu tiên mà ta sẽ sử dụng trong hàm subtotal là đối số nào.
Ví dụ: có các đối số : 1- average (Tính tổng trung bình) ; 2- Count(Đếm trung bình) ; 3- Counta (đếm trung bình); 4- Max(Tính tổng lớn nhất); 5- Min (Tính tổng nhỏ nhất); 9– Sum(Tính tổng); 109-(Tính tổng có điều kiện)….
Còn rất nhiều các đối số trong hàm Subtotal nữa…
Trong bài viết này tôi sẽ đề cập tới cách dùng hàm này để tính tổng, chỉ ra cho các bạn thấy được dùng hàm subtotal sẽ vượt trội hơn hàm sum như thế nào, sự khác nhau trong kết quả mà hai hàm này trả về.
Cách sử dụng và cấu trúc Hàm Subtotal như thế nào?
Hàm Subtotal có cấu trúc như sau:
Cú pháp 1 = Subtotal (Functinon _Num; Ref1; Ref2;…)
Giải thích cấu trúc hàm:
Function_Num là: Đối số ta sử dụng tùy theo mục đích; Trường hợp các bạn dùng hàm Subtotal để tính tổng ta dùng đối số 9 và 109
Ref1: Vùng tính tổng thứ nhất
Ref 2: Vùng tính tổng thứ 2….
Như vậy sử dụng hàm subtotal ta có thể tính tổng nhiều cột hoặc nhiều hàng khác nhau.
Sau đây mời các bạn đi vào thực hành cùng với mình một số ví dụ mà ta có thể gặp trong thực tế về cách vận dụng hàm Subtotal để giải quyết công việc nhé.
Sử dụng hàm subtotal đối số 9 để tính tổng
Cấu trúc như sau:
= Subtotal(9;Ref1; Ref2;…)
Ví dụ: Ta có dữ liệu về lương của các phòng ban như bảng dưới đây. Trường hợp bình thường khi ta tính tổng thì hàm sum và subtotal trả về kết quả như nhau:
Trong ví dụ trên, tại ô E19; F19; G19 sẽ trả về kết quả giống nhau khi ta gõ công thức subtotal và sum.
Trường hợp khi ta lọc dữ liệu chỉ lấy lương của từng phòng ban, ví dụ lương BAN GĐ thì dòng tổng cộng của hàm sum trả về kết quả như sau:
Như vậy kết quả trả về ở dòng tổng cộng là không đúng; tại ô E19; F19;G19 vẫn trả về kết quả là tổng cộng lương của toàn công ty, không phải là kết quả mà ta cần. Tức là lúc này hàm sum không hề bỏ qua các giá trị mà chúng ta đã lọc.
vậy nếu lúc này ta thay công thức sum bằng subtotal và dùng đối số 9 – tính tổng thông thường ta được kết quả như bảng sau:
C19 = SUBTOTAL(9;E4:E18)
Trong đó:
SUBTOTAL:: là hàm tính tổng
9 : là đối số mà ta dùng
E4:E18 : Là vùng ta cần tính tổng
Tương tự ta cũng có Công thức tại ô F19 và G19
Như vậy khi ta lọc dữ liệu lương của từng phòng ban thì hàng tổng cộng cũng sẽ thay đổi theo từng phòng ban đó và cho ta kết quả chính xác. (Nếu bỏ lọc thì hàng tổng cộng sẽ trả về giá trị tổng cộng của tất cả các phòng ban). Hàm subtotal đối số 9 sẽ cho ta giá trị tổng của các cột mà ta hiển thị, bỏ qua giá trị cột mà ta đã lọc.
Qua ví dụ này hi vọng bạn đã hiểu cách sử dụng hàm subtotal để tính tổng đơn thuần và biết được sự khác nhau giữa cách sử dụng hàm sum và subtotal.
Sử dụng hàm subtotal đôi số 109 để tính tổng
Sử dụng hàm subtotal để tính tổng, ngoài đối số 9 thường gặp ta cũng hay sử dụng đối số 109. Vậy hai đối số này cách khác nhau như thế nào, ta sẽ đi phân tích và làm ví dụ thực tế để thấy được sự khác nhau này nhé!
Cú pháp 1 = Subtotal(9; ref1; ref2…)
Cú pháp 2 = Subtotal(109; ref 1; ref2…)
Như vậy cú pháp sử dụng không thay đổi với hàm subtotal khi ta thay đổi các đối số sử dụng.
Về ý nghĩa hàm:
Hàm subtotal đối số 9 sẽ tính tổng các giá trị cột mà chúng ta đã hide (ẩn cột, ẩn hàng)
Hàm subtotal đối số 109 sẽ tính tổng giá trị các cột mà chúng ta để hiển thị (Bỏ qua giá trị cột , hàng đã ẩn)
Quay lại ví dụ trên, lúc này tại bảng dữ liệu ta ẩn hàng số 6 tới hàng số 13 thì kết quả ở dòng tổng cộng khi ta dùng hàm subtotal đối số 9 như sau:
Dữ liệu tại cột tổng cộng trả về cho ta thấy kết quả tổng vẫn là giá trị của toàn công ty (Bao gồm cả giá trị của các hàng chúng ta đã ẩn)
Tuy nhiên khi thay công thức tại cột tổng cộng bằng hàng subtotal đối số 109 ta có kết quả như sau:
Giá trị tại cột tổng cộng lúc này chỉ bao gồm các hàng mà chúng ta để hiển thị (Không bao gồm các hàng chúng ta đã hide đi)
Đây chính là sự khác biệt giữa hàm subtotal đối số 9 và đối số 109.
Bài viết trên đây đã mô tả rất chi tiết về cấu trúc cũng như cách sử dụng rất cụ thể và rõ ràng về hàm Subtotal để tính tổng cũng như sự khác biệt của hàm này với hàm sum. Hi vọng bài viết này hữu ích với các bạn.
Chúc các bạn thực hành vận dụng hàm thuần thục và linh hoạt.
Các bạn có thể tải file bài tập hướng dẫn: Tại Đây