Hướng dẫn - Cập nhật lần cuối 08/04/2022

SUBTOTAL là một tính năng trong Google Trang tính có thể cực kỳ linh hoạt. Với các mẹo và công cụ phù hợp, nó có thể năng động và mạnh mẽ, cho phép bạn tăng chức năng của trang tính theo cấp số nhân.

Khi sử dụng tính năng SUBTOTAL trong Google Trang tính, bạn sẽ tìm thấy nhiều cách khác nhau để sử dụng nó và một số ứng dụng báo cáo.

Ba cách chính để sử dụng nó:

  • Áp dụng các hàm cơ bản khác nhau (ví dụ: Tổng, Đếm, Trung bình) cho danh sách dữ liệu.
  • Tạo bộ chọn báo cáo để xem các thống kê khác nhau từ cùng một tập dữ liệu.
  • Tính toán dữ liệu có hoặc không có hàng ẩn.

Tính năng này có thể đáng sợ đối với người dùng mới hoặc chưa có kinh nghiệm vì nó yêu cầu mã tính năng để hoạt động, nhưng như bạn sẽ thấy, có nhiều cách để sử dụng nó mà không cần nhớ danh sách mã tính năng.

Sau khi hoàn thành hướng dẫn này, bạn sẽ không có bất kỳ lý do gì để lo lắng về việc sử dụng tổng phụ trong Google Trang tính, vì bạn sẽ hiểu đầy đủ về tính linh hoạt và tính đơn giản của chúng.

Hãy bắt đầu.

Google Trang tính – Chức năng SUBTOTAL

Cú pháp

Chúng ta sẽ bắt đầu với cú pháp SUBTOTAL trong Google Trang tính.

Hàm SUBTOTAL yêu cầu hai đối số trở lên.

  • Mã chức năng.
  • Ít nhất một dải ô để thực hiện chức năng này.

Mã tính năng được hiển thị bên dưới và có sẵn trong Google Trang tính bất kỳ lúc nào. Để thực hiện việc này, trước tiên bạn cần bắt đầu nhập hàm = SUBTOTAL.

Sau đó, trong phần trợ giúp dành cho công thức, ở góc dưới bên trái, bạn sẽ thấy nút “Thông tin khác”. Chọn nó.

Lưu ý rằng nếu phần trợ giúp công thức không được hiển thị, một hộp có dấu chấm hỏi màu xanh lam sẽ xuất hiện; nhấp vào nó hoặc nhấn F1 để mở chủ đề trợ giúp về công thức.

Mã chức năng

Khi bạn đã nhấp vào nút “Tìm hiểu thêm”, thanh bên phải sẽ xuất hiện; trong đó bạn sẽ thấy giải thích chi tiết về từng mã chức năng. Điều này có thể hữu ích nếu bạn quên.

Các mã chức năng như sau:

1 = AVERAGE
2 = COUNT
3 = COUNTA
4 = TỐI ĐA
5 = PHÚT
6 = SẢN PHẨM
7 = STDEV
8 = STDEVP
9 = SUM
10 = VAR
11 = WARP

Như bạn có thể thấy, có 11 chức năng được tích hợp trong chức năng SUBTOTAL trong Google Trang tính. Ngoài ra, bạn có thể yêu cầu hàm bỏ qua các ô ẩn bằng cách thay đổi mã hàm. Bạn sử dụng cùng một mã, nhưng cho một trăm. Để chạy các chức năng bỏ qua các ô ẩn, bạn có thể sử dụng các mã sau:

101 = AVERAGE
102 = COUNT
103 = COUNTA
104 = TỐI ĐA
105 = PHÚT
106 = SẢN PHẨM
107 = STDEV
108 = STDEVP
109 = SUM
110 = VAR
111 = VARP

Đây là một ví dụ về hàm SUBTOTAL được sử dụng để tính tổng một dải dữ liệu. Như bạn sẽ thấy, mã hàm 9 yêu cầu nó tính tổng phạm vi.

= SUBTOTAL (9; A1: A10)

Cách NHẬN SUBTOTAL trong Google Trang tính

Người ta có thể hỏi, tại sao lại sử dụng hàm SUBTOTAL để thực hiện hàm SUM? Sử dụng hàm SUM sẽ không dễ dàng hơn phải không? Những câu hỏi này khá dễ hiểu và logic. Tuy nhiên, có những tình huống mà hàm SUBTOTAL có thể có giá trị và hiệu quả hơn.

Giả sử bạn có tập dữ liệu sau và bạn quyết định sử dụng hàm SUM thay vì hàm SUBTOTAL:

Trong biểu đồ bên dưới, bạn sẽ thấy rằng hàm SUM nằm trong mỗi ô tổng và tổng ô tổng nằm ở cuối bảng.
Nếu bạn phải cộng tất cả các tổng theo cách thủ công, bạn sẽ thấy rằng tổng của dự báo trung bình và doanh số bán hàng thực tế không khớp với các tổng được liệt kê. Tuy nhiên, mỗi quý là chính xác. Bởi vì hàm SUM cộng tất cả các số trong phạm vi đã cho. Kết quả là, tổng hàng quý được thêm vào các số liệu, làm cho phương pháp này không hiệu quả và có vấn đề.

Để tránh điều này, chúng ta cần thay đổi tất cả các hàm SUM thành hàm SUBTOTAL. Hàm SUBTOTAL không thêm bất kỳ hàm tổng phụ nào khác; bằng cách đó bạn có được tổng số chính xác.

Google Trang tính SUBTOTAL cho dữ liệu được lọc hoặc ẩn

Giả sử chúng ta có một tập dữ liệu lớn được phân loại và định cấu hình để lọc theo tháng. Chúng tôi có các ô tóm tắt để xem dữ liệu báo cáo của chúng tôi. Mục tiêu là có tổng số ô được điều chỉnh dựa trên bộ lọc. Vì vậy, nếu chúng tôi đang xem xét dữ liệu cho tháng 1, thì tổng số ô sẽ phản ánh điều đó.

Ngoài ra, nếu chúng ta ẩn một hàng, chúng ta cũng muốn các ô dữ liệu phản ánh điều đó. Điều đầu tiên cần lưu ý là, như bạn sẽ thấy trong ví dụ, khi hàm SUM được sử dụng, nó không được điều chỉnh khi dữ liệu được lọc hoặc ẩn. Với hàm SUBTOTAL thì ngược lại.

Bạn sẽ thấy bảng tương ứng ở cuối trang tính.
Ban đầu mọi thứ trông giống nhau với kết quả chính xác. Tuy nhiên, nếu chúng tôi lọc kết quả theo tháng, chúng tôi bắt đầu thấy sự khác biệt.

Để thực hiện việc này, hãy chọn danh sách thả xuống bộ lọc màu xanh lá cây ở đầu cột Tháng. Chọn tháng được đề cập và nhấp vào OK.

Bây giờ bạn sẽ thấy rằng dữ liệu đã thay đổi và nhiều hàng đã được lọc ra.

Bạn cũng sẽ nhận thấy rằng các ô SUM trong bảng dưới đây không thay đổi.

Tuy nhiên, các ô được lọc sử dụng hàm tổng phụ chỉ được điều chỉnh để hiển thị các ô chưa được lọc.

Ngoài ra, nếu có một hàng hoặc dòng dữ liệu khác mà bạn muốn loại trừ, bạn có thể ẩn hàng dữ liệu đó và chức năng SUBTOTAL loại bỏ các hàng bị ẩn hiện được cập nhật nhiều hơn nữa.

Cách sử dụng hàm SUBTOTAL để tạo bộ chọn chức năng báo cáo động

Phương pháp linh hoạt nhất để sử dụng hàm SUBTOTAL trong Google Trang tính là tạo bộ chọn hàm báo cáo. Cho phép bạn nhanh chóng thay đổi các số liệu hiển thị trong báo cáo và có thể được thực hiện bằng cách kết hợp các chức năng và hoạt động khác nhau với chức năng SUBTOTAL. Nó tương đối đơn giản và các bước được mô tả dưới đây.

  • Bước đầu tiên trong quá trình này là tạo một bảng tính năng cho các tính năng SUBTOTAL có sẵn.
    • Chúng tôi sẽ sử dụng danh sách này để xác thực dữ liệu (danh sách thả xuống) và hàm VLOOKUP trong các bước tiếp theo.
  • Sau đó, bạn sẽ tạo xác thực dữ liệu tham chiếu danh sách này trên ô mà bạn muốn đặt bộ chọn của mình thả xuống.
    • Để thực hiện việc này, hãy bấm chuột phải vào ô mong muốn, tìm Xác thực Dữ liệu và bấm vào đó.
    • Bây giờ bạn sẽ thấy cửa sổ xác minh dữ liệu. Chọn danh sách từ phạm vi và nhập phạm vi của cột tổng hợp từ danh sách mà chúng tôi đã tạo ở bước 1.
    • Bây giờ bạn đã tạo menu thả xuống xác thực dữ liệu của mình, bạn sẽ thấy một mũi tên trong ô cho phép bạn nhấp đúp và chọn tùy chọn bạn muốn.

  • Phần còn lại của các bước liên quan đến việc viết các hàm để có được kết quả mà chúng ta muốn.
    • Chúng ta sẽ lồng hàm VLOOKUP vào bên trong hàm SUBTOTAL. Làm tổ là việc sử dụng đồng thời một số chức năng để thu được kết quả mong muốn. Để làm điều này, bạn viết như sau vào một ô.

= SUBTOTAL (VLOOKUP (E54, AH4: AI14,2, FALSE), F4: F44)

  • VLOOKUP xem bảng chúng ta đã tạo ở bước 1 trong phạm vi AH4: AI14. Nó tìm kiếm nội dung trong ô thả xuống mà chúng tôi đã tạo trong E54. Sau khi tìm thấy hàng chính xác, nó sẽ tìm kiếm kết quả trong cột thứ hai của phạm vi. Sau đó, nó sẽ chèn số đó vào hàm Vlookup cho mã hàm.
  • Bây giờ, hãy tạo một ô cho tính năng “Bỏ qua các hàng bị ẩn” và nhập vào bên dưới. Nó giống như ở trên, ngoại trừ chúng tôi đã thêm “100+” vào phần Vlookup, điều này sẽ buộc nó sử dụng mã tính năng bỏ qua các ô ẩn.

= SUBTOTAL (100 + VLOOKUP (E54, AH4: AI14,2, FALSE), F4: F44)

  • Bộ chọn của bạn hiện đang hoạt động và sẵn sàng sử dụng. Tuy nhiên, nếu bạn muốn nâng cao hơn, bạn có thể tạo hộp kiểm để kiểm soát tính năng bỏ qua ô ẩn.
  • Sau đó, trong ô kết quả, bạn sẽ cần nhập hàm bên dưới. Bạn sử dụng các chức năng tương tự như trên, nhưng điều khiển câu lệnh IF kiểm tra hộp kiểm và sau đó quyết định kết quả nào sẽ hiển thị. Cho phép người dùng chuyển đổi giữa hiển thị hoặc bỏ qua các ô này.

= SUBTOTAL (IF (I58 = TRUE, 100 + VLOOKUP (E58, AH4: AI14,2, FALSE), VLOOKUP (E58, AH4: AI14,2, FALSE)), F4: F44)

Phần Tổng kết

Google Trang tính có nhiều tính năng hữu ích. Một trong số đó là chức năng SUBTOTAL. Nó rất năng động và có thể mang lại lợi ích cho bất kỳ hệ thống báo cáo Google Trang tính nào.

Nội dung tương tự:

Cách gỡ cài đặt hoàn toàn chương trình bảo mật máy tính của bên thứ ba

Hướng dẫn • 22/04/2022

Ngày nay, máy tính của chúng ta giống như phần mở rộng của con người – chúng không chỉ giúp chúng ta hoàn thành ...

Đánh giá Combin Scheduler: Tự động hóa các bài đăng trên Instagram một cách dễ dàng

Hướng dẫn • 17/04/2022

Các nền tảng truyền thông xã hội, đặc biệt là Instagram, có thể giúp doanh nghiệp của bạn phát triển mạnh nếu bạn sử ...

Cách khắc phục sự cố đăng nhập ứng dụng Paramount Plus trên thiết bị Roku

Hướng dẫn • 08/05/2022

CBS Không giới hạn đã được đổi tên thành một cái mới Paramount Plus. Nếu bạn đã đăng ký CBS Không giới hạn, bạn ...

Nuôi dạy trẻ thông minh: Cách trò chuyện với trẻ về tiền

Hướng dẫn • 26/04/2022

Ai đã dạy bạn về tiền bạc và tài chính cá nhân? Nếu câu trả lời là không, bạn đã hiểu những gì bạn ...