Giúp quản lý số liệu được lọc và nhóm, hàm SUBTOTAL trong Google Sheets là một công cụ không thể thiếu. Khác với 11 hàm tổng hợp khác như AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR và VARP, hàm SUBTOTAL cho phép bạn chỉ tính toán dựa trên các hàng hiển thị. Điều này rất hữu ích khi bạn cần phân biệt giữa các hàng ẩn và hiển thị.
Vậy làm thế nào một hàm đơn lẻ có thể thay thế 11 hàm khác nhau? Đó là nhờ các mã chức năng. Hàm SUBTOTAL có thể chứa 11 mã chức năng tổ hợp, giúp bạn tạo bảng điều khiển động để chuyển đổi từ tổng thành trung bình hoặc các chức năng tổng hợp khác bằng cách sử dụng danh sách thả xuống.
Ngoài ra, hàm SUBTOTAL trong Google Sheets còn có tính năng khác mà bạn sẽ thích nếu phải làm việc với lượng dữ liệu lớn.
Giả sử bạn có một cột với nhiều hàng dữ liệu và một số tổng nhóm sử dụng hàm SUM. Đặt một tổng cộng cuối cùng ở hàng cuối cùng trong cột đó có thể tốn thời gian vì bạn cần đi đến từng hàng tổng nhóm và thêm chúng lại bằng toán tử +.
Tuy nhiên, hàm SUBTOTAL giúp công việc này trở nên đơn giản hơn. Đây là một tính năng mà tôi đã sử dụng rất nhiều.
Một tính năng khác của hàm SUBTOTAL là chúng ta có thể sử dụng nó với các hàm LAMBDA để tạo một cột trợ giúp ảo, sau đó sử dụng nó trong các hàm khác như COUNTIF và QUERY để làm cho chúng hoạt động với các hàng hiển thị.
Bốn mục đích của hàm SUBTOTAL một cách ngắn gọn:
- Tính tổng phụ và tổng cộng trong một danh sách.
- Làm việc với dữ liệu đã được lọc và nhóm.
- Chuyển đổi giữa các chức năng trong bảng điều khiển động.
- Tạo một cột trợ giúp ảo để xử lý các hàng đã được lọc trong các hàm khác bằng cách sử dụng các hàm LAMBDA.
Cú pháp và đối số
Cú pháp của hàm SUBTOTAL trong Google Sheets:
SUBTOTAL(mã_chức_năng, dải1, [dải2, …])
Đối số:
- mã_chức_năng: Mã số dùng để chỉ định loại tính toán bạn muốn thực hiện.
- dải1: Dải đầu tiên trên đó tính tổng phụ..
Cách sử dụng hàm SUBTOTAL để tính tổng phụ và tổng cộng trong một cột
Thường thì chúng ta sẽ chèn tổng phụ khi có nhiều danh mục được sắp xếp trong một bảng.
Ví dụ, giả sử bạn có một bảng khai báo thanh toán được sắp xếp theo tên khách hàng trong cột B. Có ba khách hàng có tên A, B và C.
Để tính tổng số tiền hóa đơn cho mỗi khách hàng trong cột C, bạn có thể sử dụng hàm SUBTOTAL thay vì SUM.
=SUBTOTAL(9,C2:C5) // tổng số tiền hóa đơn cho khách hàng A
=SUBTOTAL(9,C8:C12) // tổng số tiền hóa đơn cho khách hàng B
=SUBTOTAL(9,C15:C19) // tổng số tiền hóa đơn cho khách hàng C
Lợi ích khi sử dụng hàm SUBTOTAL thay vì SUM trong ví dụ này là hàm SUBTOTAL sẽ loại bỏ kết quả của các công thức SUBTOTAL ở C6, C14 và C21 khi tính tổng cộng (tổng của khách hàng A, B và C) ở C23.
=SUBTOTAL(9,C2:C21)
Nếu bạn sử dụng hàm SUM, bạn sẽ cần sử dụng một trong hai hàm sau để tính tổng cộng, điều này có thể gây lỗi và phiền toái nếu bạn có một lượng dữ liệu lớn:
=SUM(C6,C14,C21)
=C6+C14+C21
Hàm SUBTOTAL trong Google Sheets để tính tổng phụ cho các hàng hiển thị
Trong bài kiểm tra này, chúng ta sẽ sử dụng Filter, Slicer, Right-Click Hide và Grouping để xem cách hàm SUBTOTAL hoạt động với dữ liệu hiển thị.
Chúng ta sẽ sử dụng các mã chức năng 2 và 102 cho bài kiểm tra. Đây là các số chức năng đại diện cho hàm COUNT.
Trong ô C6, chèn công thức sau:
=SUBTOTAL(2,B2:B5)
Trong ô C7, chèn công thức sau:
=SUBTOTAL(102,B2:B5)
Và chèn công thức COUNT sau vào ô C8:
=COUNT(B2:B5)
Tất cả các công thức này sẽ trả về 4 nếu tất cả các ô trong dải B2:B5 không trống và không rỗng.
Bây giờ, hãy xem các công thức này phản ứng với các hàng hiển thị.
Bài kiểm tra
1. “Tạo bộ lọc” Kiểm tra
Chọn dải A1:A5, sau đó điều hướng đến Data > Bộ lọc. Điều này sẽ đặt một menu thả xuống bộ lọc vào ô A1.
Nhấp vào biểu tượng menu thả xuống bộ lọc trong ô A1, bỏ chọn “C”, sau đó nhấp OK.
Các công thức SUBTOTAL trong các ô C6 và C7 sẽ trả về 3, trong khi hàm COUNT trong ô C8 vẫn trả về 4.
2. “Thêm Slicer” Kiểm tra
Khi sử dụng Slicer, các công thức SUBTOTAL và COUNT cũng sẽ hoạt động tương tự như bài kiểm tra “Tạo bộ lọc” ở trên.
Để tạo một Slicer, hãy chọn dải A2:B5, sau đó điều hướng đến Data >Thêm Slicer.
Trong hộp thoại bên thanh bên, chọn “Cột” trong trường “Cột”.
Sau đó, nhấp vào menu thả xuống bộ lọc trên Slicer, bỏ chọn “C”, sau đó nhấp OK.
Các công thức SUBTOTAL trong các ô C6 và C7 sẽ trả về 3, trong khi hàm COUNT trong ô C8 vẫn trả về 4.
3. Kiểm tra “Click phải và ẩn hàng”
Ở đây, bạn sẽ thấy sự khác biệt giữa mã chức năng 2 và mã chức năng 102.
Nhấp chuột phải vào hàng 4 và chọn “Ẩn hàng”.
Công thức SUBTOTAL trong ô C6, sử dụng số chức năng 2, và công thức COUNT trong ô C8 sẽ không có hiệu lực, trong khi công thức SUBTOTAL trong ô C7, sử dụng mã chức năng 102, chỉ đếm các hàng hiển thị.
4. Kiểm tra “Nhóm hàng”
Các giá trị trong các hàng đã được nhóm sẽ được bao gồm hoặc loại trừ trong hàm SUBTOTAL tương tự như các giá trị trong các hàng đã được ẩn. Mã chức năng từ 1 đến 11 sẽ bao gồm chúng (các hàng ẩn) trong tính toán, trong khi mã chức năng từ 101 đến 111 sẽ loại trừ chúng.
Để kiểm tra điều này, chọn số hàng 2 và 3 để nhóm chúng. Để làm điều này, nhấp vào hàng 2, nhấn phím Shift và nhấp vào hàng 3. Sau đó, nhấp vào Hiển thị > Nhóm hàng 2 – 3. Nhấp vào nút trừ để tổng hợp nhóm.
Công thức =SUBTOTAL(102, B2:B5) sẽ trả về số lượng các hàng hiển thị, trong khi công thức =SUBTOTAL(2, B2:B5) sẽ trả về số lượng tất cả các hàng.
Hàm SUBTOTAL để chuyển đổi giữa các chức năng trong bảng điều khiển động
Trước khi chúng ta tiếp cận phần quan trọng của chủ đề này, hãy tạo một menu thả xuống xác nhận dữ liệu trong ô E2.
Trước hết, nhập tất cả các tên chức năng được hỗ trợ trong SUBTOTAL vào dải I1:I11 để chúng ta có thể nhanh chóng tạo menu thả xuống.
Chọn ô E2, sau đó vào Insert > Xác nhận dữ liệu. Trong hộp thoại xác nhận dữ liệu, trong tiêu chí, chọn “Danh sách từ một dải”. Trong trường tiếp theo, chọn dải dữ liệu I1:I11 và nhấp OK.
Giờ đến dữ liệu mẫu cho bài kiểm tra.
Chúng ta có các ngày biên bản cuộc họp trong cột A và số lượng người tham gia trong cột B.
Để lấy tổng, trung bình, giá trị nhỏ nhất, giá trị lớn nhất và số lượng người tham gia mà không sử dụng hàm SUBTOTAL trong Google Sheets, chúng ta sẽ cần sử dụng năm công thức:
=SUM(B2:B)
=AVERAGE(B2:B)
=MIN(B2:B)
=MAX(B2:B)
=COUNT(B2:B)
Làm thế nào để chúng ta chuyển đổi động từ một chức năng sang chức năng khác bằng cách sử dụng hàm SUBTOTAL trong Google Sheets?
Chúng ta có một menu thả xuống trong ô E2 chứa tên của tất cả các chức năng chúng ta muốn sử dụng.
Bây giờ sử dụng công thức SWITCH sau để trả về mã chức năng dựa trên chức năng được chọn trong ô E2:
=SWITCH(E2,”AVERAGE”, 1, “COUNT”, 2, “COUNTA”, 3, “MAX”, 4, “MIN”, 5, “PRODUCT”, 6, “STDEV”, 7, “STDEVP”, 8, “SUM”, 9, “VAR”, 10,”VARP”,11)
Sau đó, sử dụng công thức này làm đối số mã_chức_năng trong hàm SUBTOTAL và dải1 là B2:B:
=SUBTOTAL(SWITCH(E2,”AVERAGE”, 1, “COUNT”, 2, “COUNTA”, 3, “MAX”, 4, “MIN”, 5, “PRODUCT”, 6, “STDEV”, 7, “STDEVP”, 8, “SUM”, 9, “VAR”, 10,”VARP”,11),B2:B)
Điều này sẽ chuyển đổi các chức năng dựa trên tên chức năng được chọn trong ô E2.
Hàm mảng SUBTOTAL trong Google Sheets
Chúng ta có bao giờ cần mở rộng hàm SUBTOTAL hoặc bất kỳ hàm tổng hợp nào khác không? Có. Ví dụ, khi chúng ta muốn tổng hợp nhiều cột, chúng ta có thể cần sử dụng nhiều công thức SUBTOTAL.
Trong ví dụ sau, tôi đã sao chép công thức SUBTOTAL ở ô B9 sang các ô C9, D9 và E9.
Sử dụng hàm BYCOL, chúng ta có thể mở rộng công thức SUBTOTAL trong ô B9 thành các ô C9, D9 và E9.
=BYCOL(B3:E8,LAMBDA(col,SUBTOTAL(109,col)))
Công thức trên sử dụng hàm BYCOL để áp dụng hàm SUBTOTAL cho từng cột trong dải B3:E8.
Hàm BYCOL có hai đối số:
- Đối số đầu tiên là dải các ô để áp dụng hàm. Trong trường hợp này, dải là B3:E8.
- Đối số thứ hai là một hàm nhận vào một cột và trả về một giá trị. Trong trường hợp này, hàm là LAMBDA(col,SUBTOTAL(109,col)).
Hàm BYCOL sẽ áp dụng hàm SUBTOTAL cho từng cột trong dải B3:E8 và trả về một mảng ngang của các kết quả.
Chúng ta có thể sử dụng tính năng này của hàm SUBTOTAL để tạo một cột trợ giúp ảo. Dưới đây là cách thực hiện.
Cột trợ giúp ảo bằng cách sử dụng hàm SUBTOTAL trong Google Sheets
Đầu tiên, tôi sẽ giải thích mục đích của việc tạo một cột trợ giúp ảo bằng cách sử dụng hàm SUBTOTAL trong Google Sheets. Sau đó, chúng ta sẽ tạo một cột trợ giúp ảo.
Trong ví dụ trên, bạn sẽ làm thế nào để trả về số lượng giá trị trong ô Q1 lớn hơn 400?
Bạn sẽ sử dụng công thức COUNTIFS sau:
=COUNTIFS(B3:B8,”>400″)
Tuy nhiên, công thức này sẽ không trả về kết quả chính xác nếu các hàng trong dải bị ẩn.
Để công thức phản ứng với các hàng ẩn, chúng ta có thể sử dụng một cột trợ giúp SUBTOTAL. Chúng ta cần mở rộng công thức SUBTOTAL xuống, chứ không phải ngang, vì vậy chúng ta sẽ sử dụng hàm MAP lambda.
Dữ liệu cho Q1 nằm trong dải B3:B8, vì vậy cột trợ giúp ảo phải dựa trên nó. Sử dụng số chức năng 3 hoặc 103, tương đương với COUNTA, trỏ đến ô đầu tiên trong dải này, tức là B3.
=SUBTOTAL(3,B3:B9)
Mở rộng công thức này xuống bằng cách sử dụng hàm MAP lambda như sau:
=MAP(B3:B8, LAMBDA(row,SUBTOTAL(3,row)))
Công thức này sẽ trả về 1 trong các hàng hiển thị và 0 trong các hàng ẩn hoặc trống.
Bây giờ, hãy sử dụng cột trợ giúp ảo này trong công thức COUNTIFS trên:
=COUNTIFS(B3:B8,”>400″,MAP(B3:B8, LAMBDA(row,SUBTOTAL(3,row))),1)
Điều này sẽ đếm số giá trị trong các hàng hiển thị trong Q1 lớn hơn 400.
Lưu ý: Hàm BYROW lambda có thể được sử dụng thay cho hàm MAP lambda để tạo một cột trợ giúp SUBTOTAL.
Kết luận
Như bạn đã thấy, hàm SUBTOTAL là một trong những hàm linh hoạt nhất trong Google Sheets. Nó được thiết kế để làm việc với các tập dữ liệu dọc. Do đó, bạn có thể sử dụng nó để chèn tổng phụ trong các cột, loại trừ hoặc bao gồm các hàng ẩn và tính toán tổng cộng.
Tuy nhiên, khả năng của hàm SUBTOTAL bị giới hạn khi áp dụng cho các tập dữ liệu ngang. Bạn vẫn có thể sử dụng nó để tính tổng phụ và tổng cộng trong một hàng, nhưng nó sẽ không phản ứng với các cột ẩn.
Hãy thử sử dụng hàm SUBTOTAL trong bảng tính của bạn và khám phá thêm tính năng hữu ích của nó!
