Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng hàm QUERY giống như Pivot Table trong Google Sheets. Hàm QUERY thực sự rất hữu ích và có thể tạo ra báo cáo tương tự như Pivot Table.
Trước khi bắt đầu, hãy xem dữ liệu mẫu mà chúng ta sẽ sử dụng trong bài viết này. Tôi đã chuẩn bị sẵn dữ liệu thực tế về lượng tiêu thụ dầu Diesel của một số phương tiện nặng. Tuy nhiên, chúng ta chỉ sử dụng một phần dữ liệu đó cho mục đích hướng dẫn.
Dựa trên dữ liệu này, tôi sẽ tạo một báo cáo tổng hợp theo từng phương tiện về lượng tiêu thụ dầu diesel bằng cách sử dụng Pivot Table và hàm QUERY.
Tạo báo cáo tổng hợp bằng Pivot Table
Dữ liệu mẫu của chúng ta được phân tán từ ô A2 đến ô C16. Để tạo báo cáo tổng hợp bằng Pivot Table, hãy chọn dải dữ liệu này và sau đó điều hướng đến Menu Dữ liệu > Bảng Tổng hợp.
Nhập vào dải dữ liệu và ô để chèn báo cáo. Sau đó, nhấp vào Tạo.
Cài đặt Bảng Tổng hợp như dưới đây sẽ tạo ra một báo cáo tổng hợp bằng Pivot Table.
Báo cáo Pivot Table và báo cáo sử dụng hàm QUERY đều hoàn toàn giống nhau và đều linh hoạt. Điều đó có nghĩa là khi bạn thay đổi dữ liệu nguồn, cả hai báo cáo đều sẽ được cập nhật.
Việc tạo báo cáo như vậy trên Google Sheets không phải là một nhiệm vụ khó khăn. Với Pivot Table, việc đó chỉ là một chuyện đùa. Tuy nhiên, sử dụng hàm QUERY, tôi tin rằng tôi có thể cung cấp cho bạn điều gì đó mới mẻ hơn.
Sử dụng hàm QUERY để tạo báo cáo tương tự như Pivot Table
Hãy xem hình ảnh dưới đây để hiểu cách sử dụng hàm QUERY giống như Pivot Table.
Hình ảnh trên có bốn hình chữ nhật được tô màu. Mỗi hình chữ nhật đại diện cho các công thức QUERY.
Với một công thức QUERY, chúng ta có thể tổng hợp dữ liệu giống như Pivot Table. Tuy nhiên, để có được Tổng cộng toàn bộ bên phải và phía dưới báo cáo, chúng ta cần ba công thức QUERY nữa.
Vì vậy, tổng cộng có bốn công thức QUERY và sau đó chúng ta sẽ lồng ghép tất cả chúng bằng cách sử dụng cặp dấu ngoặc nhọn.
Hãy xem các công thức sau để tạo báo cáo giống như Pivot Table bằng hàm QUERY trong Google Sheets:
- Công thức báo cáo tổng hợp – QUERY.
- Công thức Tổng cộng phía dưới của báo cáo tổng hợp – QUERY.
- Công thức Tổng cộng bên phải của báo cáo tổng hợp – QUERY.
- Công thức Tổng cộng góc dưới bên phải của báo cáo tổng hợp – QUERY.
Ngoài ra, nếu bạn chưa quen với cách sử dụng hàm QUERY trong Google Sheets, bạn có thể tham khảo các ví dụ về hàm QUERY trong Google Sheets.
Bước 1 – Sử dụng hàm QUERY để tạo báo cáo tổng hợp giống như Pivot Table
Vui lòng tham khảo dữ liệu mẫu ở trên.
Công thức: =QUERY(A2:C16, "SELECT C, SUM(B) GROUP BY C PIVOT A")
Trong công thức trên, cột C chứa số phương tiện. Chúng ta cần tổng hợp hoặc nhóm dữ liệu dựa trên số phương tiện này. Nếu bạn xem báo cáo Pivot Table ở đầu trang này, bạn có thể hiểu điều đó.
Chúng ta nên tổng hợp cột B vì chứa lượng dầu diesel đã đổ vào (theo đơn vị gallon).
Hy vọng bây giờ bạn đã hiểu công thức trên.
Với việc sử dụng công thức trên, kết quả sẽ như sau. Tuy nhiên, bảng này chưa đúng với báo cáo Pivot Table ở đầu trang này.
Bạn có thể so sánh bảng này với bảng Pivot Table ở đầu trang để thấy sự khác biệt. Bạn sẽ thấy ngày xuất hiện trên hàng và số phương tiện xuất hiện trên cột. Vì vậy, chúng ta có thể sử dụng hàm Transpose cùng với công thức QUERY trên như sau:
=TRANSPOSE(QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C PIVOT A"))
Như bạn có thể thấy, bây giờ bảng này gần như khớp với báo cáo Pivot Table. Tuy nhiên, chưa có phần Tổng cộng.
Đó là tất cả về phần này.
Bây giờ, chúng ta sẽ tìm hiểu cách có được Tổng cộng dưới của báo cáo trong hàm QUERY giống như Pivot Table.
Bước 2 – Sử dụng hàm QUERY để tạo Tổng cộng phía dưới
Dưới đây là công thức QUERY để có được Tổng cộng dưới của báo cáo.
=TRANSPOSE(QUERY(A2:C16,"SELECT SUM(B) GROUP BY C LABEL SUM(B) 'TỔNG CỘNG'"))
Vui lòng tham khảo dữ liệu mẫu ở trên. Công thức QUERY trên sẽ trả về kết quả sau.
Đây là một báo cáo tổng hợp theo số phương tiện. Vì vậy, tôi đã nhóm cột C và tính tổng cột B.
Ở đây, tôi đã sử dụng hàm Transpose vì chúng ta muốn Tổng cộng theo chiều ngang, không phải theo chiều dọc.
Xin lưu ý rằng, trong báo cáo mà chúng tôi tạo ra ở Bước 1, số phương tiện xuất hiện trên cột (cũng sử dụng Transpose ở đó).
Sau đó, chúng ta sẽ kết hợp công thức này với công thức đầu tiên (Bước 1).
Bước 3 – Tạo Tổng cộng bên phải của báo cáo tổng hợp
Từ báo cáo mà chúng ta đã tạo bởi công thức đầu tiên (Bước 1) hoặc báo cáo Pivot Table mà chúng ta đã tạo ở đầu trang này, chúng ta có thể thấy rằng chúng ta phải thêm một Tổng cộng nữa bên phải của báo cáo.
Công thức QUERY này sẽ làm phần đó mà không sử dụng hàm Transpose lần này.
=QUERY(A2:C16,"SELECT SUM(B) GROUP BY A LABEL SUM(B) 'TỔNG CỘNG'")
Lần này, tôi đã nhóm ngày tháng trong cột A và tính tổng lượng dầu diesel trong Gallon (cột B).
Bước 4 – Tổng cộng góc dưới bên phải
Ở đây, chúng ta chỉ cần tính tổng cột B, không phải là tổng theo phương tiện hay theo ngày tháng.
=QUERY(A2:C16,"SELECT SUM(B) LABEL SUM(B) ''")
Bước 5 – Kết hợp các công thức QUERY để tạo báo cáo tương tự như Pivot Table
Chúng ta có bốn công thức trên trong tay.
Chúng ta có thể sử dụng các công thức sau đây trong bốn ô riêng biệt không?
Không! Vì sao?
Kết quả trên hoàn toàn khớp với báo cáo Pivot Table. Tuy nhiên, chúng ta không thể sử dụng công thức QUERY theo cách này.
Lý do là khi bạn thay đổi dữ liệu gốc, điều đó có thể gây lỗi cho các công thức của bạn vì chúng có thể yêu cầu thêm không gian để mở rộng.
Vậy giải pháp là gì?
Chúng ta phải lồng ghép các công thức bằng cách sử dụng cặp ngoặc nhọn.
Nếu bạn chưa quen với cách sử dụng cặp ngoặc nhọn để tạo mảng, tôi khuyên bạn nên đầu tiên tham khảo hướng dẫn của tôi về cách sử dụng cặp ngoặc nhọn để tạo mảng trong Google Sheets.
Qua những hướng dẫn trên, hi vọng bạn đã hiểu rõ cách sử dụng cặp ngoặc nhọn.
Dưới đây là công thức cuối cùng đã kết hợp tất cả các công thức trên:
={
TRANSPOSE(QUERY(A2:C16,"SELECT C,SUM(B) GROUP BY C PIVOT A")),
QUERY(A2:C16,"SELECT SUM(B) GROUP BY A LABEL SUM(B) 'TỔNG CỘNG'"),
TRANSPOSE(QUERY(A2:C16,"SELECT SUM(B) GROUP BY C LABEL SUM(B) 'TỔNG CỘNG'")),
QUERY(A2:C16,"SELECT SUM(B) LABEL SUM(B) ''")
}Tôi đã kết hợp bốn công thức trên bằng cặp ngoặc nhọn.
Với cặp ngoặc nhọn, chúng ta có thể kết hợp dữ liệu từ hai dải khác nhau cùng một lúc hoặc chồng lên nhau.
Hy vọng bạn đã thích bài viết này và đã học được cách sử dụng hàm QUERY giống như Pivot Table.
Bằng cách sử dụng dữ liệu mẫu và các công thức trên, bạn có thể thực hành và tạo ra báo cáo tương tự như Pivot Table trong Google Sheets.
Xin chào, và hẹn gặp lại trong các bài viết tiếp theo!
