Hàm SUBTOTAL trong Google Sheets có chức năng chính là làm việc với dữ liệu đã được lọc và nhóm.
Nó có thể thay thế các hàm AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR và VARP khi bạn chỉ muốn tính toán trên các hàng hiển thị. Mười một hàm tổng hợp này không phân biệt giữa các hàng hiển thị và các hàng ẩn, nhưng SUBTOTAL có thể.
Dùng mã hàm để thay thế cho 11 hàm khác nhau?
Bằng cách sử dụng các mã hàm.
Vì SUBTOTAL có thể xử lý 11 hàm tổng hợp, bạn có thể sử dụng nó trong các bảng điều khiển động để chuyển từ tổng sang trung bình hoặc các tổ hợp khác bằng cách sử dụng danh sách thả xuống.
Hàm SUBTOTAL trong Google Sheets còn có một tính năng khác mà bạn có thể thích nếu bạn xử lý một 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à nhiều tổng nhóm sử dụng hàm SUM. Đặt tổng cộng tổ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 mỗi hàng tổng phụ và thêm chúng bằng cách sử dụng toán tử +.
Tuy nhiên, SUBTOTAL làm cho công việc này trở nên đơn giản. Đây là tính năng mà tôi đã sử dụng rất nhiều.
Một tính năng khác của 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, mà chúng ta sau đó có thể sử dụng 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 chính của hàm SUBTOTAL ngắn gọn:
- Tính tổng phụ và tổng chính 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 hàm 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 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ã_hàm, phạm_vi1, [phạm_vi2, …])
Đối số:
mã_hàm: Mã số chỉ định loại phép tính bạn muốn thực hiện.
Mỗi hàm có hai mã và bạn có thể sử dụng bất kỳ mã nào nếu không có hàng ẩn. Nhưng chúng khác nhau trong cách xử lý các hàng ẩn.
phạm_vi1: Phạm vi đầu tiên để tính toán tổng phụ.
phạm_vi2, …: (tùy chọn) Các phạm vi bổ sung để tính toán tổng phụ.
Liên quan: Google Sheets Function Numbers: Hướng dẫn toàn diện
Cách sử dụng hàm SUBTOTAL để tính toán tổng phụ và tổng chính trong một cột
Thường thì chúng ta thêm 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áo cá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 của 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 của việc sử dụng hàm SUBTOTAL thay vì SUM trong ví dụ này là hàm SUBTOTAL sẽ loại trừ kết quả của các công thức SUBTOTAL trong C6, C14 và C21 khi tính tổng chính (tổng của khách hàng A, B và C) trong C23.
=SUBTOTAL(9,C2:C21)
Nếu bạn sử dụng hàm SUM, bạn cần sử dụng một trong hai công thức sau đây để tính tổng chính, điều này có thể dễ gây lỗi và khó chịu 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ã hàm 2 và 102 để thực hiện bài kiểm tra. Đây là các số mã hàm đạ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 phạm vi B2:B5 không rỗng và không có giá trị.
Bây giờ, hãy xem công thức này phản ứng thế nào với các hàng hiển thị.
Bảng kiểm tra
1. Bài kiểm tra “Tạo bộ lọc”
Chọn phạm vi A1:A5, sau đó đi đến Data > Filter. Đ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” và 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. Bài kiểm tra “Thêm một bộ lọc”
Khi sử dụng Slicers, các công thức SUBTOTAL và COUNT 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 phạm vi A2:B5 và sau đó đi đến Data > Add a Slicer.
Trong hộp thoại về thanh bên, trong “Cột”, chọn “Team”.
Sau đó, nhấp vào menu thả xuống trên Slicer, bỏ chọn “C” và 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. Bài kiểm tra “Nhấp chuột phải và ẩn hàng”
Ở đây, bạn sẽ thấy sự khác biệt giữa mã hàm 2 và mã hàm 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ố mã hàm 2, và công thức COUNT trong ô C8 sẽ không có tác dụng gì, trong khi công thức SUBTOTAL trong ô C7, sử dụng mã hàm 102, chỉ đếm các hàng hiển thị.
4. Bài kiểm tra “Nhóm hàng”
Các giá trị trong các hàng nhóm được bao gồm hoặc không được bao gồm trong hàm SUBTOTAL tương tự như giá trị trong các hàng ẩn. Mã hàm từ 1 đến 11 sẽ bao gồm chúng (các hàng ẩn) trong phép tính, trong khi các mã hàm từ 101 đến 111 sẽ loại trừ chúng.
Để kiểm tra điều này, hã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 View > Groups > Group rows 2 – 3. Nhấp vào nút trừ để thu gọn nhóm.
Công thức =SUBTOTAL(102, B2:B5) sẽ trả về số hàng hiển thị, trong khi công thức =SUBTOTAL(2, B2:B5) sẽ trả về số tất cả các hàng.
Hàm SUBTOTAL để chuyển đổi giữa các hàm trong bảng điều khiển động
Trước khi chúng ta đi vào phần quan trọng của chủ đề này, hãy tạo một menu thả xuống Validation dữ liệu trong ô E2.
Trước tiên, điền tất cả các tên hàm được hỗ trợ trong SUBTOTAL vào phạm vi I1:I11 để chúng ta có thể nhanh chóng tạo menu thả xuống.
Chọn ô E2 và sau đó đi đến Insert > Data validation. Trong hộp thoại Data validation, ở phần Tiêu chí, chọn “Danh sách từ một phạm vi”. Ở trường tiếp theo, chọn phạm vi dữ liệu I1:I11 và sau đó nhấp OK.
Bây giờ đến dữ liệu mẫu cho bài kiểm tra.
Chúng ta có ngày họp trong cột A và số người tham dự trong cột B.
Để có tổng, trung bình, min, max và đếm số người tham dự 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 để chuyển đổi động từ một hàm sang hàm 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 hàm chúng ta muốn sử dụng.
Bây giờ sử dụng công thức SWITCH sau đây để trả về mã hàm dựa trên hàm đượ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)
Bây giờ sử dụng công thức này làm đối số mã_hàm trong hàm SUBTOTAL và phạm_vi1 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 hàm dựa trên tên hàm được chọn trong ô E2.
Công thức 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? Đúng. Ví dụ, khi chúng ta muốn tổng các cột con, chúng ta có thể cần sử dụng nhiều hàm SUBTOTAL.
Trong ví dụ dưới đây, tôi đã sao chép công thức SUBTOTAL trong ô B9 vào ô 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 sang 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 mỗi cột trong phạm vi B3:E8.
Hàm BYCOL có hai đối số:
- Đối số thứ nhất là phạm vi các ô để áp dụng hàm. Trong trường hợp này, phạm vi là B3:E8.
- Đối số thứ hai là hàm nhận một cột làm đầu vào 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 mỗi cột trong phạm vi B3:E8 và trả về một mảng ngang 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. Đâ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
Trước 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ái.
Trong ví dụ trên, làm sao chúng ta 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 đây:
=COUNTIFS(B3:B8,”>400″)
Tuy nhiên, công thức này sẽ không trả về kết quả đúng nếu các hàng trong phạm vi bị ẩn.
Để làm cho công thức phản ứng với các hàng đã được ẩ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 một công thức SUBTOTAL xuống, không phải ngang, nên chúng ta sẽ sử dụng hàm lambda MAP.
Dữ liệu cho Q1 nằm trong phạm vi B3:B8, vì vậy cột trợ giúp ảo phải dựa trên nó. Sử dụng số mã hàm 3 hoặc 103, tương đương với COUNTA, trỏ vào ô đầu tiên trong phạm vi 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 lambda MAP như sau:
=MAP(B3:B8, LAMBDA(row,SUBTOTAL(3,row)))
Công thức trên sẽ trả về 1 trong các hàng hiển thị và 0 trong các hàng ẩn và hàng trống.
Bây giờ, hãy sử dụng nó như cột trợ giúp ảo 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 các 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 thế cho hàm MAP lambda để tạo một cột trợ giúp ảo SUBTOTAL.
Tương tự: COUNTIF | COUNTIFS Loại trừ Các hàng ẩn trong Google Sheets
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ụ vào các cột, loại trừ hoặc bao gồm các hàng ẩn và tính tổng chính.
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 toán tổng phụ và tổng chính trong một hàng, nhưng nó sẽ không phản ứng với các cột ẩn.
