SUBTOTAL - Trả về một Tổng phụ trong danh sách cơ sở dữ liệu
Đây là hàm có tính tùy biến cao về chức năng và công năng sử dụng.
Nó là sự tổng hợp của nhiều hàm đơn lẻ khác nhau và việc gọi để dụng tính năng của những hàm đơn lẻ này là rất dễ dàng. Nó được ứng dụng rất nhiều trong lĩnh vực xây dựng nên việc của bạn là hãy chú ý đến nó để sử dụng được tốt nhất!
1. Để làm gì?
- Nếu bạn muốn tính tổng, tính giá trị trung bình, đếm số ô, tìm giá trị lớn nhất hay nhỏ nhất… thì SUBTOTAL là lựa chọn thứ hai cho bạn thay cho việc sử dụng những hàm Sum, Min, Max … thông thường bạn vẫn hay dùng.
- Nếu trong một bảng dữ liệu có hàng ẩn, khi bạn chỉ muốn tính những giá trị hiển thị và loại trừ tổng phụ thì SUBTOTAL là lựa chọn đầu tiên bạn sẽ phải nghĩ đến.
- Nếu bảng dữ liệu có vô vàn loại dữ liệu khác nhau, khi đó bạn dùng tính năng Filter lọc dữ liệu. Lọc xong bạn còn muốn tính toán với dữ liệu hiện thị thì SUBTOTAL thực sự phát huy tối đa tác dụng mà những hàm chức năng chính như hàm SUM, MIN, MAX… không làm được.
2. Dùng như thế nào?
2.1. Ví dụ minh họa
Đề bài:
- Dữ liệu: Bảng diễn giải khối lượng đào đất hữu cơ từ Km0+000,00 ÷ Km0+180,00
- Yêu cầu: Tính khối lượng đào đất hữu cơ Đoạn lý trình này với hai trường hợp:
TH1: Tính khối lượng đào đất hữu cơ cả đoạn lý trình trên.
TH2: Tôi ẩn đi cọc 3 và cọc 4 (không tính khối lượng hai cọc này) và bạn hãy tính khối lượng đoạn lý trình trên.
Hướng dẫn:
Phân tích yêu cầu:
- Khối lượng giữa các cọc lý trình thể hiện ở mảng dữ liệu G7÷G16
- Tổng khối lượng đào đất hữu cơ của cả đoạn lý trình là tổng của mảng dữ liệu G7÷G16
Giải quyết yêu cầu:
TH1: Tính khối lượng đào đất hữu cơ cả đoạn lý trình trên
- Thông thường chúng ta sẽ sử dụng hàm Sum hoặc cộng thủ công các giá trị đó với nhau.
- Ở trường hợp này chúng ta sẽ sử dụng tính năng thay thế tuyệt vời của SUBTOTAL để tính tổng giá trị đó. Để sử dụng tính năng tính tổng này với hàm SUBTOTAL dựa vào giá trị Function_num (Mỗi một Function_num đại diện cho một chức năng khác nhau). Với chức năng tính tổng thì Function_num = 9 hoặc 109.
- Tại ô K7 gõ công thức =SUBTOTAL(9;G7:G16)
Thực hiện tương tự với ô K8, gõ công thức =SUBTOTAL(109;G7:G16)
Kết quả: Ta thấy Kết quả tính tổng với hai Function_num là 9 và 109 là bằng nhau. Vậy điểm khác nhau của hai Function_num này là gì? Điều này sẽ được thể hiện rõ hơn trong TH2.
TH2: Tôi ẩn đi cọc 3 và cọc 4 (không tính khối lượng hai cọc này) và bạn hãy tính khối lượng đoạn lý trình trên.
- Ẩn đi khối lượng của hai cọc 3 và 4.
- Vẫn giữ nguyên công thức ở trên để tính tổng khối lượng:
Tại ô K7 =SUBTOTAL(9;G7:G16)
Tại ô K8 =SUBTOTAL(109;G7:G16)
Kết luận:
- Với Function_num bằng 9 thì hàm SUBTOTAL sẽ tính tổng giá trị trong vùng dữ liệu – Bao gồm cả dữ liệu hiển thị và dữ liệu bị ẩn đi (Ngoại trừ Filter).
- Với Function_num bằng 109 thì hàm SUBTOTAL sẽ tính tổng giá trị trong vùng dữ liệu – Chỉ dữ liệu được hiển thị.
2.2. Cấu trúc
2.3. Khái niệm
- Là một hàm tổng hợp, có sức mạnh tính toán tương đương với 11 hàm khác trong Excel.
- Là hàm liên quan đến giá trị ẩn và việc loại trừ giá trị ẩn khỏi vùng tính toán (Bạn để ý Ví dụ trên với hai giá trị Function_num 9 và 109).
- Là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu – Tức là những tổng phụ đã được tính toán bằng hàm SUBTOTAL thì khi tính toán tổng chính sẽ loại bỏ giá trị này.
3. Cần lưu ý gì không
Với hàm SUBTOTAL thì bạn cần chú ý đến 4 vấn đề chính: Giá trị ẩn thông thường – Giá trị ẩn bởi Filter – Tổng phụ và Trường hợp tính toán hàng ngang, hàng dọc.
- Đối với giá trị được ẩn bởi cách thông thường (Hide) thì tùy thuộc vào mục đích tính toán của bạn để lựa chọn giá trị Function_num với việc tính hay không tính giá trị ẩn.
- Đối với giá trị được ẩn bởi việc lọc dữ liệu (Filter) thì hàm SUBTOTAL sử dụng với hai loại giá trị Function_num 1÷ 11 hoặc 101 ÷ 111 là như nhau.
- SUBTOTAL không tính lặp lại các giá trị đã được tính bởi SUBTOTAL.
- Khi dùng SUBTOTAL tính toán dữ liệu phạm vi hàng ngang thì tính năng giá trị Function_num từ 109 đến 111 trong trường hợp ẩn cột trong phạm vị tính toán không còn hữu dụng. Nên SUBTOTAL sử dụng tốt nhất trọng phạm vi hàng dọc (tính toán theo cột).
- SUBTOTAL chỉ phát huy việc tính toán của nó đới với Dữ liệu được tổ chức dạng Cột.
4. Ví dụ áp dụng?
4.1. Ví dụ 1: Hàm SUBTOTAL với hai trường hợp tính tổng và tổng phụ
Đề bài:
- Dữ liệu: Bảng tổng hợp giá trị thanh toán.
- Yêu cầu: Tính tổng các giá trị Dầm chủ – Dầm ngang – Kết cấu phần trên.
Hướng dẫn:
Phân tích yêu cầu:
- Hạng mục Dầm chủ được cấu thành bởi ba công việc và giá trị từng công việc được hiển thị rõ ràng trong các ô: G8, G9, G10
- Việc tính tổng giá trị Dầm chủ là cộng các giá trị đơn lẻ đó với nhau : G8+ G9+ G10
Giải quyết yêu cầu:
- Với yêu cầu này thì nhiều anh em sẽ dùng hàm Sum hoặc cộng thủ công nhưng khi đã biết Subtotal thì bạn sẽ muốn đi theo hướng khác
- Dựa vào yêu cầu ở trên bằng việc sử dụng hàm SUBTOTAL chúng ta sẽ dùng giá trị Function_num nào để quyết định chức năng tính toán của hàm: 9 hay 109.
- Thực tế tôi hay dùng Dải giá trị Function_num thứ hai từ 101 ÷ 111, tính giá trị không bao gồm giá trị ẩn sẽ là trường hợp tổng quan nhất kể cả khi ai đó vô tình ẩn một dự liệu nào đó.
- Tính tổng Dầm chủ:
Tại ô G7 gõ công thức tính tổng, không bao gồm giá trị ẩn của Dầm chủ =SUBTOTAL(109;G8:G10)
Tính tổng Dầm ngang:
Tương tự Dầm chủ ta có kết quả tính tổng:
Tính tổng Kết cấu phần trên:
- Bạn để ý rằng ở phần hàm Sum nếu ta muốn tỉnh tổng Kết cầu phần trên có hai cách:
- Một là: Ʃ Kết cấu phần trên = Ʃ Dầm chủ + Ʃ Dầm ngang = G7+G11 (Cộng các địa chỉ ô lại với nhau)
- Hai là: Ʃ Kết cấu phần trên = Sum(G8:G10) + Sum(G12:G14) (Dùng hàm tính tổng tính tổng từng vùng chứa dữ liệu Dầm chủ và Dầm ngang rồi cộng lại với nhau)
- Khá bất tiện đúng không các bạn? Nếu để tính tổng Kết cấu phần trên bằng cách kéo từ G7:G14 thì bạn thấy sao nhỉ? Nếu theo hàm Sum thì nó sẽ tiến hành cộng tất cả các ô có chứa dữ liệu lại với nhau, còn theo hàm SUBTOTAL thì nó sẽ bỏ qua giá trị nào mà đã dùng hàm Subtotal cộng rồi (Bỏ qua giá trị Ʃ Dầm chủ và Ʃ Dầm ngang đã tính toán rồi).
- Tại ô G6 gõ công thức =SUBTOTAL(109;G7:G14)
