Công việc của bạn cần phải sử dụng nhiều đến Excel nhưng bạn lại không phải là một chuyên gia trong lĩnh vực này. Đặc biệt lại càng khó khăn khi phải sử dụng đến các hàm trong khi tính toán. Bởi các hàm công thức là một phần rất quan trọng của Excel, nó sẽ giúp cho công việc của bạn trôi chảy và hiệu quả hơn rất nhiều. Một trong những công thức được sử dụng nhiều nhất phải kể đến là hàm SUBTOTAL. Vậy ứng dụng hàm này như thế nào thì hãy cùng tìm hiểu ngay nhé!
Hàm SUBTOTAL trong Excel
Hàm SUBTOTAL là một hàm tổng hợp các phép tính, dùng để tính tổng phụ của một danh sách hoặc một cơ sở dữ liệu. Hàm SUBTOTAL rất linh hoạt – có thể tính toán hoặc thực hiện các phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất, nhỏ nhất…
Khi sử dụng hàm SUBTOTAL ưu điểm lớn nhất chính là hiệu quả và chính xác. Trong công việc, chúng ta không đơn thuần chỉ cần tính ra kết quả, mà kết quả đó còn phải dễ dàng tùy biến và đúng với nhiều trường hợp khác nhau và hàm Subtotal sẽ giúp giải quyết vấn đề này.
Cách dùng hàm SUBTOTAL
Có rất nhiều người thường xuyên sử dụng Excel nhưng lại không biết đến hàm SUBTOTAL cũng như cách sử dụng hàm này. Dưới đây là một số cách sử dụng hàm SUBTOTAL đơn giản và thông dụng nhất.
Cú pháp hàm SUBTOTAL:
SUBTOTAL(function_num, ref1, [ref2],…)
Trong đó:
-
Function_num: mã số chức năng sử dụng hàm
-
Ref1, Ref2,...: là các giá trị dùng để áp dụng các chức năng của hàm.
Số xác định chức năng thực hiện có 2 loại sau:
-
1– 11 các hàng được ẩn thủ công
-
101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.
Để hiểu rõ hơn, chúng ta sẽ áp dụng hàm này vào các ví dụ dưới đây:
Ví dụ 1: Sử dụng hàm SUBTOTAL để tính tổng số lượng sản phẩm bán được.
Lúc này, bạn chọn ô tổng và đánh “= Subtotal” thì hàm sẽ hiện ra. Tiếp theo bạn chọn số 9 là tính tổng và đánh dấu phẩy sau chữ số, tiếp tục xác định dãy ô cần tính, đóng ngoặc và nhấn Enter. Công thức hoàn chỉnh sẽ như sau: =SUBTOTAL(9,D4:D20).
Sử dụng hàm subtotal (Nguồn: Internet)
Và đây là kết quả:
Sử dụng hàm subtotal tính tổng (Nguồn: Internet)
Ví dụ 2: Sử dụng hàm SUBTOTAL để tính số tiền trung bình của mỗi sản phẩm bán được.
Bạn thực hiện thao tác giống ví dụ trên, thay vì chọn số 9 bạn nhấn đúp chuột vào số 1 là tính trung bình và tiếp tục thực hiện chọn ô cần tính, đóng ngoặc và nhấn Enter. Công thức sẽ hiện ra như sau: = SUBTOTAL(1,E2:E20).
Ứng dụng hàm subtotal tính tổng (Nguồn: Internet)
Một số ví dụ ứng dụng hàm SUBTOTAL
Ví dụ 1: Yêu cầu tính tổng sản phẩm và tìm ra số lượng sản phẩm bán được ít nhất là bao nhiêu và nhiều nhất là bao nhiêu?
Cách sử dụng subtotal (Nguồn: Internet)
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 các giá trị yêu cầu phía trên. Công thức thực hiện như sau:
- Tại ô D21 công thức tính tổng là =SUBTOTAL(9,D2:D20)
- Tại ô D22 công thức tính giá trị nhỏ nhất là =SUBTOTAL(5,D2:D20)
- Tại ô D23 công thức tính giá trị lớn nhất là =SUBTOTAL(4,D2:D20)
Giải quyết vấn đề bằng hàm subtotal (Nguồn: Internet)
Ví dụ 2: Tính số lượng sản phẩm bán ra của hãng A ít nhất là bao nhiêu và nhiều nhất là bao nhiêu?
Trong bảng dữ liệu chúng ta sẽ dùng lệnh Filter (nhấp chuột phải) - Filter By Selected Cells Value để ẩn số liệu sản phẩm của các hãng còn lại.
Ẩn các hãng bằng lệnh Filter (Nguồn: Internet)
Sau đó nhập hàm SUBTOTAL như ở ví dụ 1 chúng ta sẽ có kết quả như sau:
Ví dụ về hàm subtotal (Nguồn: Internet)
Như vậy, với lệnh Filter kết hợp hàm SUBTOTAL chúng ta dễ dàng tính được các giá trị không bị ẩn.
Ví dụ 3: Ví dụ này chúng ta sẽ thực hiện ở cấp độ phức tạp hơn.
Yêu cầu tính tổng các sản phẩm của mỗi nhà cung cấp. Lưu ý khi thực hiện ví dụ này là các nhà cung cấp cần đứng liền kề nhau nhé. Sau khi sắp xếp vị trí các hãng xong bạn chọn vùng dữ liệu cần tính toán rồi vào tab Data chọn Subtotal.
Lúc này hộp thoại Subtotal sẽ hiện lên như sau:
Khả năng vượt trội của hàm subtotal (Nguồn: Internet)
Trong đó chúng ta sẽ có các thông số:
Mục At each change in: Chọn Nhà cung cấp vì chúng ta đang thực hiện tính tổng số lượng sản phẩm cho từng Nhà cung cấp.
Mục Use function: Chọn hàm cần để tính. Ở đây chúng ta sẽ chọn hàm tổng SUM.
Mục Add subtotal to: Chọn cột cần tính toán. Chúng ta cần tính số lượng nên chọn cột số lượng nhé.
Sau đó nhấn OK là sẽ ra kết quả.
Tính tổng lượng sản phẩm theo hãng bằng hàm subtotal (Nguồn: Internet)
Với những kiến thức chúng ta vừa tìm hiểu về hàm SUBTOTAL ở trên hy vọng các bạn có thể vận dụng hiệu quả vào học tập cũng như trong công việc.
