Tôi rất nhớ tính năng nhóm và tổng cộng giống Excel trong Google Sheets. Tôi chắc chắn rằng sớm muộn Google sẽ triển khai tính năng này trong Google Spreadsheets.
Trong thời gian chờ đợi, chúng ta nên tìm cách thay thế để sao chép tính năng nhóm và tổng cộng giống Excel trong Google Sheets.
Tôi có một giải pháp sử dụng công thức SUMIF và tính năng nhóm dòng mà Google vừa mới thêm vào.
Giới thiệu về Nhóm và Tổng cộng trong Bảng tính
Trong Excel, tính năng nhóm và tổng cộng này được tích hợp sẵn và được gọi là Nhóm phụ (không phải là hàm SUBTOTAL).
Nó giúp người dùng tổng hợp một số dòng dữ liệu liên quan (nhóm dữ liệu) bằng cách tự động chèn tổng phụ.
Trong loại tổng cộng này trong Excel, bạn có quyền lựa chọn tổng để xuất hiện ở mỗi thay đổi trong cột ưa thích của bạn, và cũng là hàm tổ hợp để sử dụng (tổng, trung bình, đếm, v.v.).
Hãy xem cách nhóm và tổng cộng trong Excel trước. Điều này sẽ giúp bạn hiểu rõ hơn về chủ đề này.
Mẫu dữ liệu minh họa cho Tổng cộng và Nhóm trong Google Sheets và Excel
Dưới đây là dữ liệu mẫu mà tôi sử dụng trong các ví dụ dưới đây cả trong Excel và Google Sheets.
Đây là dữ liệu mẫu của tôi để nhóm và tổng cộng dòng. Dữ liệu đã được sắp xếp theo Cột A, và điều đó là bắt buộc. Điều này vì việc nhóm dữ liệu của tôi dựa trên Cột A, tức là theo tên lục địa.
Ví dụ về Nhóm và Tổng cộng trong Excel
Trong phần này, tôi sẽ tập trung vào Excel để giúp bạn hiểu rõ hơn về nhóm dòng và tổng cộng.
Các bước:
- Chọn dữ liệu trong phạm vi A1:C21.
- Nhấp vào menu Dữ liệu và Chọn Tổng cộng trong nhóm Đường viền.
- Theo các thiết lập dưới đây và nhấp OK.
Đó là tất cả những gì bạn cần làm trong Excel. Bây giờ bạn có thể thu gọn nhóm hàng và nó sẽ trông như sau:
Dữ liệu đã nhóm trong Excel (mở rộng xem mặc định):
Dữ liệu Excel đã thu gọn:
Các tính năng tổng cộng và nhóm dễ dàng triển khai trong Excel vì chúng được tích hợp sẵn. Vậy còn Google Sheets?
Nhóm và Tổng cộng trong Google Sheets
Tôi sẽ hướng dẫn bạn cách nhóm và tổng cộng trong Google Sheets, bao gồm một số biện pháp khắc phục.
Như đã đề cập, tôi sẽ sử dụng công thức SUMIF, sau đó sử dụng tính năng nhóm dòng mới được thêm vào để đạt được điều này.
Đầu tiên, hãy xem cách nhóm và tổng cộng (biện pháp khắc phục của tôi) hoạt động trong Google Sheets.
Hãy bắt đầu.
Các bước sau đây bao gồm việc chèn hàng mới và các cột trợ giúp. Bạn có thể bỏ qua việc chèn hàng nếu sử dụng một trong những Hàm Được Đặt Tên của tôi. Chúng ta sẽ đến vấn đề đó sau trong bài hướng dẫn này.
Lưu ý: Dữ liệu mẫu giống như tôi đã sử dụng trong ví dụ Excel.
Các bước liên quan đến Tổng cộng và Nhóm trong Google Sheets
1. Chèn các hàng mới dưới mỗi nhóm. Bạn có thể xem các hàng vừa được chèn trong hình ảnh bên dưới (hàng #3, 12, 19, 22 và 26).
2. Sau đó, chèn một cột trợ giúp sau cột B và gõ các chuỗi văn bản theo hình ảnh bên dưới (cột C) trong những hàng đó.
3. Đây là công thức mảng SUMIF có thể điền vào các tổng phụ trong Google Sheets. Bạn nên áp dụng công thức này chỉ trong ô E3.
Công thức Nhóm và Tổng cộng:
=ArrayFormula(if(len(A2:A),sumif(A2:A&" Tổng",C3:C,D2:D),))
Không kéo hoặc sao chép công thức xuống. Nó sẽ tự động mở rộng. Bạn chỉ cần làm trống phạm vi E3:E trước khi chèn công thức SUMIF này.
Trong cột E, công thức SUMIF trả về tổng số sản xuất táo (trái cây) theo nhóm (theo lục địa). Tuy nhiên, có các ô trống và ô chứa giá trị 0 (không). Để loại bỏ những số không mong muốn đó, chúng ta có thể áp dụng một quy tắc định dạng có điều kiện.
4. Chọn phạm vi E3:E và sau đó trong menu Định dạng > Định dạng có điều kiện, đặt quy tắc như sau:
Nếu bạn gặp khó khăn với điều này, đây là hướng dẫn chi tiết: Hai cách để Xóa các Giá trị 0 trong Google Sheets.
Chúng ta đã đạt đến phần cuối của việc nhóm và tổng cộng trong Google Sheets. Tức là, chúng ta đã có tổng cộng cho mỗi nhóm. Công việc cuối cùng còn lại là nhóm dòng. Dưới đây là cách thực hiện.
5. Chọn toàn bộ dòng A2 bằng cách nhấp chuột phải vào số hàng. Từ menu ngắn được mở ra, chọn “Xem thêm thao tác hàng” > “Nhóm hàng”.
Sau đó, chọn các hàng 4 đến 11. Để làm điều này, di chuyển đến bất kỳ ô nào trong hàng số 4, sau đó nhấn và giữ phím Shift trong khi nhấp chuột vào hàng số 11. Cuối cùng, nhấp chuột phải để mở menu ngắn và áp dụng nhóm.
Làm theo quy tắc này cho tất cả các nhóm, tức là từ hàng 13 đến 18, từ hàng 20 đến 21 và từ hàng 23 đến 25.
Hãy kiểm tra: Làm thế nào để Nhóm Hàng và Cột trong Google Sheets.
Đó là tất cả! Bạn đã hoàn thành việc nhóm và tổng cộng trong Google Sheets.
Lưu ý: Đừng quên kiểm tra Bảng Mẫu Của Tôi.
Kết luận
Tôi biết đây không phải là một giải pháp hoàn hảo, vì nó yêu cầu một số hàng chèn và một cột trợ giúp bổ sung. Tuy nhiên, kết quả đầu ra có phần giống với Tổng cộng Excel.
Nếu bạn chỉ muốn đặt tổng cộng ở hàng đầu hoặc cuối cùng của mỗi nhóm, bạn có thể sử dụng đơn giản AT_EACH_CHANGE named function của tôi. Một trong những lợi ích của hàm tùy chỉnh này là bạn có thể chèn trung bình, đếm, min, max, v.v., bên cạnh tổng.
Tôi hy vọng Google sẽ bao gồm một tùy chọn trên menu dữ liệu Tổng cộng trong Sheets trong tương lai gần.
Tài nguyên liên quan:
- Hàm Tổng cộng trong Google Sheets: Hướng dẫn đầy đủ.
- Công thức để Chèn các Hàng Tổng cộng vào Google Sheets.
- Ô Trống giữa Hai Tổng trong Hàng Tổng của Truy vấn trong Google Sheets.
- Chèn các Hàng Tổng phụ vào Bảng Truy vấn trong Google Sheets.
- Làm thế nào để Tổng cộng Đến Ô Trống Đầu tiên trong Một Cột trong Google Sheets.
