Bạn đã từng tổ chức và tính tổng các mục như thế nào, và làm thế nào để nhận được phần trăm phân phối của tổng lớn trong Google Sheets Query tương tự như Pivot Table ‘% của tổng lớn’ của nó?
Bài viết này mô tả phần phân phối phần trăm của các mục đã được nhóm bằng cách sử dụng công thức Query.
Đối với các mục chưa được nhóm, không có yêu cầu về Query. Tôi đã trình bày chi tiết ở đây – Tính phần trăm tổng trong Google Sheets [Làm thế nào].
Để hiểu cách lấy / tính phần trăm của tổng lớn bằng cách sử dụng Pivot Table trong Google Sheets, công thức Query sẽ được đề cập sau đó.
Với 4 bước, bạn có thể nhóm dữ liệu trong mảng B1: C5 và lấy phần trăm của tổng lớn bằng cách sử dụng Pivot Table như sau.
Cách tính phần trăm tổng lớn trong Pivot Table cho Google Sheets
- Chọn mảng B1: C5. Tất nhiên, nếu phạm vi dữ liệu của bạn khác, hãy chọn phạm vi đó.
- Nhấp vào menu ‘Dữ liệu’ và chọn mục menu ‘Bảng dữ liệu trực quan’.
- Làm theo hướng dẫn trên màn hình để tạo Pivot Table trong ‘Trang mới’ hoặc ‘Trang hiện có’.
- Tôi đang tạo Pivot Table trong ô “Trang hiện có” ô E1. Bạn có thể thấy ảnh chụp màn hình ở trên.
- Như một ghi chú phụ, để xóa Pivot Table, hãy nhấp vào ô E1 và nhấn nút xóa.
Sau khi hoàn thành các bước trên, bước tiếp theo là thêm các trường và hàm cần thiết trong bảng điều khiển Pivot (sidebar). Một lần nữa xem ảnh chụp màn hình ở trên để lấy cài đặt yêu cầu như sau.
- Thêm trường ‘Mục’ cho ‘Hàng’.
- Thêm trường ‘Số tiền’ cho ‘Giá trị’.
- Dưới ‘Tổng kết bởi’, chọn hàm SUM nếu chưa chọn, và thay đổi ‘Mặc định’ thành ‘% của tổng lớn’ dưới ‘Hiển thị như’.
Đó là tất cả.
Bây giờ hãy xem cách tính phần trăm (phân phối) của tổng lớn trong Google Sheets Query tương tự như Pivot Table ở trên.
Cách tính phần trăm tổng lớn của các mục đã được nhóm bằng công thức Query
Lần đầu tiên tiếp xúc với Query? Vui lòng đọc bài hướng dẫn về hàm Query của tôi trước.
Do chủ đề của chúng tôi là tính phần trăm tổng lớn trong việc nhóm Google Sheets Query, rõ ràng chúng ta phải sử dụng mệnh đề ‘Nhóm theo’ trong Query.
Để nhóm và tổng hợp các dữ liệu trên, chúng ta có thể sử dụng công thức Query như dưới đây.
=query({B1:C5},"Chọn Col1, Tổng(Col2) nhóm theo Col1")
Bạn muốn xem công thức này trả về gì?
| Mục | Tổng số Tiền |
|---|---|
| Táo | 250 |
| Xoài | 200 |
| Cam | 250 |
Nhưng những gì chúng ta muốn không phải là tổng số theo từng nhóm. Chúng ta muốn phần trăm của tổng lớn tương tự như Pivot Table như trên.
Phân phối phần trăm của tổng lớn thực ra là tổng lớn/tổng mỗi mục.
Ví dụ, phần trăm của tổng lớn của các mục “Táo”, “Xoài” và “Cam” có thể được tính như sau.
=250/(250+200+250)
=200/(250+200+250)
=250/(250+200+250)
Trong đó, công thức Query trên trả về tổng số cá nhân (tổng được nhóm) là phần đầu tiên (trước toán tử chia) của 3 công thức trên.
Để có được phần thứ hai, chúng ta có thể sử dụng hàm SUM như sau.
=sum(C2:C5)
Chúng ta có thể bao gồm hàm SUM này trong Query như dưới đây.
=query({B1:C5},"Chọn Col1, Tổng(Col2)/"&sum(C2:C5)&" nhóm theo Col1")
Công thức Query này sẽ trả về phần trăm của tổng lớn của các mục đã được nhóm. Nhưng đó không phải là công thức cuối cùng của tôi!
Tại sao?
Xem ảnh chụp màn hình dưới đây.
Chúng ta phải thêm một số mệnh đề bổ sung nữa vào công thức để nó bao gồm hàng vô hạn và thu được định dạng đầu ra phần trăm.
Định dạng số thành phần trăm trong kết quả Query
Để định dạng số (đầu ra Query) thành phần trăm, không cần thay đổi công thức.
Chọn loạt số (đầu ra Query) trong kết quả I2: I và định dạng nó thành phần trăm (menu Định dạng, Số, Phần trăm).
Lưu ý: Những gợi ý dưới đây chỉ là lựa chọn. Bạn có thể bỏ qua hoặc đọc thêm để biết thêm ý tưởng về định dạng kết quả Query.
Nếu bạn quan tâm rất nhiều để tránh định dạng thủ công, bạn có thể bao gồm định dạng phần trăm trong Query. Đây là công thức đã cập nhật!
=query({B1:C5},"Chọn Col1, Tổng(Col2)/"&Sum(C2:C5)&" nhóm theo Col1 định dạng Tổng(Col2)/"&Sum(C2:C5)&"'0.00%'")
Thêm hàng tổng phần trăm vào Kết quả Query
Bạn thiếu một hàng phần trăm tổng ở cuối kết quả Query, phải không? Đây là công thức.
={query({B1:C5},"Chọn Col1, Tổng(Col2)/"&Sum(C2:C5)&" nhóm theo Col1 định dạng Tổng(Col2)/"&Sum(C2:C5)&"'0.00%'");"Tổng cộng",100%}
Tôi vừa thêm hàng tổng cộng vào kết quả Query bằng cách sử dụng chuỗi “Tổng cộng” và 100% bên trong dấu ngoặc nhọn như một mảng.
Tôi nghĩ chúng ta nên thay đổi hai điều vào công thức, nhưng không cần thiết.
Xem nhãn trong ô I1. Bạn có thể tùy chỉnh nhãn đó bằng cách sử dụng Mệnh đề Nhãn trong công thức Query như sau.
={query({B1:C5},"Chọn Col1, Tổng(Col2)/"&Sum(C2:C5)&" nhóm theo Col1 nhãn Tổng(Col2)/"&Sum(C2:C5)&"'TỔNG Số tiền' định dạng Tổng(Col2)/"&Sum(C2:C5)&"'0.00%'");"Tổng cộng",100%}
Cuối cùng, hãy đảm bảo công thức bao phủ các hàng vô hạn.
={query({B1:C},"Chọn Col1, Tổng(Col2)/"&Sum(C2:C)&" trong đó Col1 không phải null nhóm theo Col1 nhãn Tổng(Col2)/"&Sum(C2:C5)&"'TỔNG Số tiền' định dạng Tổng(Col2)/"&Sum(C2:C5)&"'0.00%'");"Tổng cộng",100%}
Xem Mệnh đề Ở đâu trước khi Nhóm theo.
Ông có thể nhận được phần trăm của tổng lớn trong Google Sheets Query liên quan đến việc nhóm này.
Cảm ơn đã đọc, thưởng thức!
