Bạn có thể tự động thêm tổng vào dữ liệu đã được lọc trong Google Sheets. Điều này sẽ mang đến sự đa dạng và linh hoạt hơn cho bảng dữ liệu lọc của bạn. Tôi đang nói về việc lọc dữ liệu bằng cách sử dụng các hàm trong Google Sheets thay vì sử dụng lệnh trên menu.
Trong Google Sheets, bạn có thể sử dụng các hàm như FILTER hoặc QUERY để lọc dữ liệu. Cả hai hàm này đều có thể cho kết quả tương tự. Bảng dữ liệu lọc thông thường có thể không chứa tổng ở cuối bảng. Liệu chúng ta có thể thêm một dòng mới ở cuối bảng dữ liệu lọc để hiển thị tổng (như SUM, AVERAGE) không? Có, chúng ta có thể làm điều đó bằng cách sử dụng hàm QUERY.
Lưu ý nhỏ, khi bạn có một dữ liệu được lọc bằng Lệnh Lọc dữ liệu trên Google Sheets, hãy tránh sử dụng hàm SUM để tính tổng. Thay vào đó, hãy sử dụng hàm SUBTOTAL như được mô tả ở đây để tránh các lỗi có thể xảy ra.
Hàm Query trong Google Sheets và Lọc dữ liệu
Khi chúng ta sử dụng hàm QUERY để lọc dữ liệu, chúng ta có thể tự động thêm tổng vào cuối hàng. Đó là tổng động.
Nó không chỉ điều chỉnh tổng khi bạn thay đổi dữ liệu mà còn di chuyển cùng dữ liệu đã lọc.
Trên đây là dữ liệu mẫu của chúng tôi. Ở đây, tôi đang lọc Cột D theo giá trị “Safety Helmet” và sau đó tính tổng cột H dựa trên lọc. Kết quả lọc của chúng tôi sẽ như dưới đây. Bạn có thể thấy, ở cuối hàng đã lọc, chúng tôi đã thêm một dòng mới để hiển thị tổng.
Vậy làm thế nào để tự động thêm tổng vào dữ liệu đã lọc trong Google Sheets như trên. Chúng ta sẽ tìm hiểu điều đó ở đây.
Các bước để tự động thêm tổng vào dữ liệu đã lọc
Như bạn đã biết, chúng ta đang sử dụng hàm Query rất hữu ích ở đây, không phải hàm FILTER.
Có hai bước liên quan. Một là lọc dữ liệu bình thường và hai là thêm một dòng tổng vào cuối.
Công thức #1
=query(A1:H12,"Select * where D='Safety Helmet'")
Công thức trên lọc cột D với giá trị “Safety Helmet”. Kết quả sẽ như sau.
Đây là dữ liệu được lọc dựa trên giá trị cột D = “Safety Helmet”. Bạn cũng có thể làm điều này bằng cách sử dụng hàm FILTER trong Google Sheets.
Bây giờ chúng ta đã hoàn thành phần lọc. Nếu bạn muốn tìm hiểu về hàm Query chi tiết, hãy chuyển sang bài hướng dẫn của chúng tôi.
Làm thế nào để có được dòng tổng ở cuối bảng dữ liệu đã lọc này? Có hai lựa chọn.
- Tự động thêm một dòng mới vào cuối chứa tổng bằng cách sử dụng hàm QUERY khác.
- Thêm tổng bằng tay bằng cách sử dụng hàm SUBTOTAL.
Chúng tôi đang theo phương pháp đầu tiên, đây là lựa chọn lý tưởng ở đây. Tại sao? Bởi vì nếu bạn thêm tổng vào cuối dữ liệu đã lọc của bạn bằng tay, hầu hết thời gian, bạn có thể gặp lỗi như sau khi bạn thay đổi dữ liệu nguồn.
“Array result was not expanded because it would overwrite data in …”
Công thức #2
=query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")
Công thức Query trên tính tổng cột H khi giá trị cột D phù hợp với “Safety Helmet”. Ở phần cuối của công thức QUERY trên, bạn có thể thấy “label Sum(H) ””, nếu không có phần này, bạn sẽ nhận được kết quả như dưới đây.
Phần nhãn trong công thức loại bỏ nhãn “sum Amount”. Vì vậy, chúng tôi có giá trị là 90 là kết quả.
Chúng ta có hai công thức ở trên. Bây giờ chúng ta muốn làm gì là, thêm kết quả của công thức thứ hai vào cuối kết quả của công thức đầu tiên. Chúng ta có thể làm điều này bằng cách sử dụng Dấu ngoặc nhọn. Hãy xem cách tôi kết hợp hoặc lồng hai công thức Query ở trên.
Cách lồng công thức Query trong Google Sheets
Chúng ta có hai công thức Query ở trên. Làm thế nào để kết hợp chúng? Đầu tiên, hãy xem công thức kết hợp dưới đây.
={query(A1:H12,"Select * where D='Safety Helmet'"); query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")}
Công thức trên có thể không hoạt động! Tôi sẽ giải thích sau đây. Trước tiên, hãy tôi giải thích cho bạn cách lồng hai công thức Query hoặc cách tôi làm điều đó trong ví dụ trên.
Ở đầu công thức thứ nhất, hãy đặt một Dấu ngoặc nhọn mở. Sau đó, ở cuối công thức thứ nhất, sử dụng dấu chấm phẩy và thêm công thức thứ hai. Sau đó, ở cuối công thức thứ hai, đặt một Dấu ngoặc nhọn đóng. Đây chính là cách tôi đã làm ở trên mặc dù có thể không hoạt động.
Dấu chấm phẩy được sử dụng để đặt kết quả của công thức thứ hai vào cuối kết quả của công thức thứ nhất. Nếu bạn sử dụng dấu phẩy thay vì dấu chấm phẩy, kết quả của công thức thứ hai sẽ được thêm vào bên phải của kết quả của công thức thứ nhất, không phải dưới cùng.
Bạn có thể tìm hiểu thêm về việc sử dụng Dấu ngoặc nhọn để tạo mảng trong Google Sheets trong bài hướng dẫn của chúng tôi.
Sử dụng Dấu ngoặc nhọn để tạo Mảng trong Google Sheets
Như đã đề cập, công thức kết hợp trên có thể không hoạt động! Tại sao? Xem ảnh chụp màn hình dưới đây.
Theo kết hợp trên, chúng tôi đang cố đặt tổng như sau. Hàm Query có thể không chấp nhận điều này vì tổng là của cột H, đó là cột có Nhãn cột “Amount”. Vì vậy, chúng ta cần di chuyển tổng đến cột đó. Làm cách nào để làm điều đó.
={"Total","","","","","","",90}
Khi bạn áp dụng công thức Mảng Google Sheets trên vào bất kỳ ô nào, nó sẽ đặt chuỗi văn bản “Total” vào ô áp dụng công thức. Sau đó, nó bỏ qua 6 cột sang phải và trên cột thứ 7, nó sẽ đặt giá trị 90.
Chúng ta có thể thay thế giá trị 90 trên bằng công thức truy vấn thứ hai ở trên và sau đó chúng ta có thể kết hợp sự kết hợp mới này với công thức truy vấn đầu tiên. Bây giờ sự kết hợp cuối cùng hoạt động sẽ trông như sau.
={query(A1:H12,"Select * where D='Safety Helmet'");{"Total","","","","","","", query(A1:H12,"Select Sum(H) where D='Safety Helmet' label Sum(H) ''")}}
Kết luận
Theo ý kiến của tôi, bạn nên dành thời gian để học về Query và lồng Query. Điều này có thể là một tài sản quý giá trong tương lai của bạn, vì bạn có thể tiết kiệm nhiều thời gian công việc sử dụng Query. Hy vọng bạn đã hiểu cách tự động thêm tổng vào dữ liệu đã lọc trong Google Sheets. Enjoy!
