Nếu bạn là một người mới bắt đầu với Excel đặc biệt là những hàm của Excel thì chắc hẳn là bạn đã từng nghe qua Hàm Subtotal đây là một hàm rất thú vị. Hàm có chức này gì? Hàm Subtotal sử dụng khi nào? Hãy cùng Ez Lasso tìm hiểu qua bài viết sau đây nhé.
Ở bài viết này mình sử dụng Excel thuộc phiên bản Office 2016 các phiên bản office khác bạn có thể sử dụng tương tự nhé
Hàm Subtotal có chức năng gì?
Hàm Subtotal là một hàm thuộc nhóm hàm tính toán trong Excel. Đây là một hàm giúp bạn tính tổng phụ các con số trong 1 dãy ô đã được xác định.
Thông thường các hàm excel sẽ thực hiện 1 chức năng nhiệm vụ cụ thể nhưng đối với hàm Subtotal lại linh hoạt hơn có thể tính toán nhiều phép tính khác nhau.
Cú pháp của hàm Subtotal trong Excel
Cú pháp: Subtotal(funcion_num, ref1,ref2,…)
Trong đó:
- funcion_num: là những con xác định hàm thực hiện
- Ref1, Ref2: là các vị trí ô tính hãy chuổi ô cần tính toán.
*Các số Funcion_num sẽ bao gồm 2 loại sau:
- 1 – 11: nhận luôn các ô đã đã ẩn đi (Thủ công). Đối với công cụ Phễu lọc thì bỏ qua các ô bị ẩn đi
- 101 – 111: bỏ qua các ô đã ẩn đi (thủ công). Đối với công cụ Phễu lọc thì bỏ qua các ô bị ẩn đi
Chi tiết các bộ số của Funcion_num:
- 1 và 101: AVERAGE (Tính số trung bình)
- 2 và 102: COUNT (Đếm số ô chứa giá trị)
- 3 và 103: COUNTA (Đếm số ô không trống)
- 4 và 104: MAX (Tìm giá trị lớn nhất)
- 5 và 105: Min (Tìm giá trị nhỏ nhất)
- 6 và 106: PRODUCT (tính kết quả các ô)
- 7 và 107: STDEV (Tính độ lệch dựa trên mẫu)
- 8 và 108: STDEVP (Tính độ lệch chuẩn dựa trên bộ số)
- 9 và 109: SUM (Tính tổng các ô)
- 10 và 110: VAR (Ước tính độ dao động dựa trên mẫu)
- 11 và 110: VARP (Ước tính độ dao động dựa trên bộ số)
Vì sao phải dùng hàm Subtotal để tính toán?
Nếu sử dụng tính toán bạn có thể sử dụng các nhóm hàm tính toán như sum, Max, Min, average,… chứ không dùng Subtotal vì đều trả ra một kết quả y chang nhau. Như trong một số trường hợp sử dụng hàm Subtotal lại mang giá trị khác và vô cùng hữu hiệu không tốn thời gian cho người dùng. Những trường hợp đó là
Tính cách ô khi thuộc cột hay hàng khi sử dụng công cụ Phễu lọc
Công cụ Phễu lọc của Excel giúp bạn có thể ẩn đi những ô không thuộc điều kiện mà bạn yêu cầu. Vì thế khi bạn sử dụng 1 công thức tính toán vào khi dùng phễu thì sẽ ra kết quả khi sử dụng công thức lên 1 chuỗi dài.
VÌ thế hàm Subtotal sẽ giúp bạn thực hiện những tính toán cho những ô đang hiện diện cho dù đang sử dụng phễu.
VÍ dụ: Mình sẽ tính tổng danh số những nhân viên thuộc tổ A.
Mình chỉ việc lọc những nhân viên thuộc tổ A bằng công cụ Phễu lọc. Sau đó sử dụng hàm =Subtotal(9,D10:D15)
Sử dụng cho các ô bị ẩn đi
Trong vài trường hợp có một vài dòng bạn muốn ẩn chúng đi nhưng vô tình thực hiện một phép tính trên chuổi ô thì bạn sẽ vô tình tính phải ô đó. Nên bạn hãy sử dụng Funcion_num từ 101-111 để bo qua nhé.
Bỏ qua những khoảng lông ghép phép tính bằng Subtotal
Trong nhiều trường hợp bạn thường tính nhiều vị trí trong 1 bảng. Việc chọn lại từng chuổi ô rất lâu nếu như bạn sử phép tính trong Subtotal thì bạn sẽ bỏ qua các phép tính đã sử dụng ở các khoản cách của bạn.
Lưu ý: Các phép tính ở giữa bảng bạn cũng dùng hàm Subtotal để tính nhé.
Ví dụ ở bảng sau đây mình cần sử dụng 2 lần tính. 1 lần tính ở giữa và 1 lần ở cuối. Cuối cùng mình cần tính là tổng giá trị ở trong. Bây giờ bạn hãy dùng hàm Subtotal được mình demo phía dưới nhé.
Một số ví dụ khi dùng hàm Subtotal trong Excel
Thông thường bạn suy nghĩ hàm Subtotal là vô nghĩa vì đã có những hàm khác đáp ứng được nhu cầu rồi. Nhưng nhờ những lý do trên bạn dã có thể hiểu được lý do tại sao lại dùng hàm này chứ.
Vậy trong những ví dụ sau đây mình sẽ giúp bạn phân biệt thêm 1 số ứng dụng khác của hàm này.
Sử dụng Funcion_num 9 và 109
Nếu như bạn đã phân biệt được sự khác biệt của 2 cơ số từ 1->11 và 101 -> 111 thì ví dụ sau đây sẽ được minh hoạ rõ hơn nhé.
Đối với công cụ Phễu lọc: Thì kết quả đều cho giống nhau
Đối với việc ẩn thủ công một vài vị trí của bảng: THì kết quả có phần khác:
- Khi sử dụng Funcion_num 1->11: thì kết quả sẽ bao gồm các ô đã được ẩn thủ công đi
- Còn Funcion_num 101->111: thì kết quả sẽ bị bỏ đi những ô đã được ẩn
Sử dụng Subtotal chung với Hàm If
Thường thì Subtotal đã thể hiện 1 loạt kết quả của công thức tính tổng. Thì bạn vẫn có thể sử dụng chung với nó ở hàm If. Đặc biệt là khi bạn sử dụng công cụ Phễu lọc chỉ lọc được 1 điều kiện.
Ví dụ:
Bạn cần tính các số theo yêu cầu như Tổng, trung bình, danh số lớn nhất, danh số nhỏ nhất. ở bảng thì bạn chỉ cần điền vào các ô D16 -> D19 là: =IF(A16=”Tổng”,SUBTOTAL(9,$D$11:$D$15),IF(A16=”Trung bình”,SUBTOTAL(1,$D$11:$D$15),IF(A16=”Danh số lớn nhất”,SUBTOTAL(4,$D$11:$D$15),IF(A16=”Danh số thấp nhất”,SUBTOTAL(5,$D$11:$D$15)))))
Việc này cho thấy subtotal không chỉ đúng một mình mà còn có thể kết hợp được với nhiều hàm khác nhau nữa.
Xem thêm:
- Cách làm checklist trong Excel – Đơn giản mà ai cũng làm được.
- Hàm IFERROR trong excel, công thức và cách sử dụng
Những lỗi hay gặp ở hàm Subtotal trong Excel
Nếu như kết quả của hàm Subtotal trả về những giá trị lỗi thì nguyên nhân có thể là một vài lý do sau đây như:
- #Value!: Số không xác định được trong khoảng 1-11 hay 101-111 hoặc tham chiếu (ref)
- #DIV/0: Xảy ra khi 1 tổng cụ thể nào phải chia cho 0
- NAME? tên hàm Subtotal bị sai
Trên đây là phần tổng hợp và giới thiệu hàm Subtotal trên Excel của Ez Lasso. Hi vọng qua bài viết bạn đã hiểu hơn về hàm nay rồi và áp dụng vô bảng Excel của bạn. Chúc các bạn thành công
