Bạn đã từng thử thêm tổng chạy trong Bảng Tổng hợp của Google Sheets mà không thành công? Dưới đây là những lý do và cách làm:
Trước khi thêm tổng chạy vào Bảng Tổng hợp của Google Sheets, hãy xem xét những điểm chính sau, chúng ta sẽ thảo luận chi tiết về chúng trong bài hướng dẫn này:
- Đảm bảo dữ liệu nguồn được sắp xếp theo cùng một thứ tự như các trường nhóm trong Bảng Tổng hợp để tính toán tổng chạy chính xác.
- Thêm một cột trợ giúp trong dữ liệu nguồn để hiển thị tổng chạy trong Bảng Tổng hợp. Cột này sẽ có một công thức mảng trong ô ở phía trên cùng.
- Đặt các trường chỉ trong phần ROWS của Bảng Tổng hợp. Chỉ bao gồm các trường trong cả phần ROWS và phần COLUMNS nếu bạn thoải mái với việc có các ô trống giữa các tổng chạy.
Ví dụ, giả sử có hai danh mục: A và B. A có sản xuất trong tháng 1, tháng 2, tháng 4 và tháng 5, trong khi B có sản xuất từ tháng 1 đến tháng 5, mỗi tháng với một lượng sản phẩm là 5 tấn. Tổng chạy kết quả trong Bảng Tổng hợp có thể xuất hiện như sau:
Tuy nhiên, lưu ý rằng giá trị trong ô trống (tháng 3) nên lý tưởng là 10, tiếp tục giá trị của tháng trước. Rất tiếc, điều này không xảy ra.
Chú ý rằng việc giải quyết những điều cần xem xét này là rất quan trọng để thành công trong việc thêm tổng chạy vào Bảng Tổng hợp của Google Sheets.
Những ưu điểm khi bao gồm tổng chạy trong Bảng Tổng hợp của Google Sheets
-
Tạo điều kiện so sánh dễ dàng:
- Thêm tổng chạy cho phép so sánh dễ dàng các giá trị tích lũy qua các danh mục hoặc nhóm khác nhau.
-
Mở rộng và thu gọn dữ liệu:
- Người dùng có thể mở rộng hoặc thu gọn dữ liệu (thu gọn) trong Bảng Tổng hợp, cung cấp tính linh hoạt trong việc khám phá dữ liệu.
-
Sử dụng tính năng Nhóm Bảng Tổng hợp:
- Nếu dữ liệu nguồn của bạn bao gồm một trường ngày (cột), bạn có thể sử dụng tính năng “Tạo nhóm bảng tổng hợp” bên trong Bảng Tổng hợp. Tổng tích lũy sẽ tự động đáp ứng với việc nhóm này.
-
Hiểu rõ các xu hướng liên tục:
- Những tính năng này cùng nhau giúp hiểu rõ các xu hướng và mô hình theo trình tự, mang lại những thông tin quý giá về dữ liệu phát triển.
Bằng cách kết hợp tổng chạy và sử dụng các tính năng của Bảng Tổng hợp trong Google Sheets, người dùng có thể nâng cao khả năng phân tích, so sánh và hiểu các xu hướng dữ liệu một cách hiệu quả hơn.
Bây giờ, tôi sẽ hướng dẫn bạn qua hai ví dụ để giúp bạn làm quen với việc thêm tổng tích lũy vào Bảng Tổng hợp của Google Sheets.
Việc sử dụng hai ví dụ là quan trọng vì mức độ nhóm trong phần ROWS và COLUMNS có thể khác nhau tùy thuộc vào dữ liệu nguồn cụ thể của bạn. Ngoài ra, bạn cần điều chỉnh các cột trong công thức cột trợ giúp dựa trên tập dữ liệu cụ thể của bạn.
Dưới đây là bảng mẫu của tôi trong chế độ sao chép:
Sample Sheet
Thêm Tổng chạy trong Bảng Tổng hợp được nhóm theo hàng
Dữ liệu mẫu cung cấp bao gồm các vật liệu cung cấp cho hai dự án khác nhau từ các nguồn riêng biệt, cùng với số lượng tương ứng. Có hai loại vật liệu: Roadbase và Sand.
Cấu trúc dữ liệu: Dự án, Vật liệu và Nguồn trong các cột A đến C, và Số lượng cung cấp trong cột D.
Tạo Bảng Tổng hợp với Tổng chạy tích lũy: Hướng dẫn từng bước
Tôi đã tổ chức các bước thành bốn danh mục: Tạo Bảng Tổng hợp, Sắp xếp dữ liệu, Thêm Công thức và Cài đặt cuối cùng để thêm Tổng chạy vào Bảng Tổng hợp.
1. Tạo Bảng Tổng hợp
Để tạo Bảng Tổng hợp, làm theo các bước sau:
- Chọn phạm vi A1: E.
- Nhấp vào Chèn> Bảng Tổng hợp.
- Chọn ‘Sheet hiện có’ và chọn G1 trong trường ở dưới, sau đó nhấp ‘Tạo’.
- Thêm các trường như hình dưới.
Chúng tôi đã thêm Dự án, Vật liệu và Nguồn trong khi bỏ chọn ‘Hiển thị Tổng’ trong mỗi trường. Bạn có thể chọn kiểm tra hoặc bỏ chọn ‘Nhãn hàng đầu’ trong các trường này khi cần thiết.
Ngoài ra, tôi đã bao gồm trường ‘Dự án’ trong phần bộ lọc để loại bỏ các hàng trống khỏi báo cáo. Bước này không cần thiết nếu bạn sử dụng phạm vi dữ liệu thực tế A1: E19. Tuy nhiên, vì chúng ta đã sử dụng phạm vi lớn hơn, A1: E1000, bao gồm nhiều hàng trống khác nhau, việc lọc này trở nên quan trọng.
Lưu ý: Nếu bạn mới sử dụng Bảng Tổng hợp, hãy tham khảo hướng dẫn này: Cách Tạo Bảng Tổng hợp để Tóm tắt Dữ liệu trong Google Sheets.
2. Sắp xếp dữ liệu nguồn
Đảm bảo rằng bạn sắp xếp dữ liệu theo thứ tự bạn đã thêm các trường vào Bảng Tổng hợp, cụ thể là sắp xếp theo ‘Dự án’, ‘Vật liệu’ và ‘Nguồn’.
- Chọn phạm vi A1: D19.
- Nhấp vào Dữ liệu> Sắp xếp phạm vi> Tùy chọn sắp xếp phạm vi nâng cao.
- Kiểm tra “Dữ liệu có hàng tiêu đề” và sắp xếp theo ‘Dự án’, ‘Vật liệu’ và ‘Nguồn’.
3. Thêm Công thức cho Cột trợ giúp
Tiếp theo, chúng ta sẽ sử dụng một công thức mảng trong hàng đầu tiên của cột trợ giúp, ở đây là ô E2. Trong công thức này, chúng ta sẽ áp dụng nhóm cho các trường ngoại trừ trường cuối cùng trong phần ROWS. Nói cách khác, chúng ta sẽ sử dụng các trường ‘Dự án’ và ‘Vật liệu’ trong công thức, loại trừ trường ‘Nguồn’.
Trong ô E2, hãy chèn công thức mảng sau:
= ARRAYFORMULA (MAP (A2: A, B2: B, LAMBDA (a, b, NẾU (a = "",, SUMIFS (D2: D, A2: A, a, B2: B, b, ROW (A2: A), "<="& ROW (a))))))
Công thức được cung cấp trả về tổng chạy được nhóm theo A2: A và B2: B, loại trừ C2: C như đã đề cập trước đó.
Cấu trúc của công thức:
Hãy bắt đầu với cú pháp của hàm SUMIFS.
SUMIFS (sum_range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...])
Trong đó:
- sum_range: D2: D
- criteria_range1: A2: A
- criterion1: a
- criteria_range2: B2: B
- criterion2: b
- criteria_range3: ROW (A2: A)
- criterion3: “<=”& ROW (a)
Công thức tính tổng phạm vi D2: D nếu A2: A bằng a, B2: B bằng b và ROW (A2: A) nhỏ hơn hoặc bằng với ROW (a). Nhận dạng a và b tương ứng với A2 và B2 trong hàng đầu tiên, A3 và B3 trong hàng thứ hai, và cứ tiếp tục như vậy. Hàm MAP được sử dụng cho mục đích này.
Tham khảo bài hướng dẫn của tôi, Tổng chạy với Nhiều Phân loại con, để có một giải thích chi tiết về công thức. Ở đây, tôi sẽ giúp bạn chỉnh sửa công thức để thêm hoặc loại bỏ các trường nhóm.
Công thức hiện tại sử dụng hai trường (cột) cho nhóm: A2: A và B2: B. Tôi đã chỉ định các mảng này trong MAP như MAP (A2: A, B2: B, và trong SUMIFS, sử dụng A2: A, a, B2: B, b,).
Nếu bạn có một trường khác nữa (cột), ví dụ, C2: C, bạn có thể tích hợp nó như MAP (A2: A, B2: B, C2: C, và A2: A, a, B2: B, b, C2: C, c,).
Tuân theo một phương pháp tương tự để thêm nhiều cấp độ vào nhóm hoặc loại bỏ các cấp độ hiện có.
Khi thêm hoặc loại bỏ cột trong công thức, đảm bảo tính ổn định trong việc sắp xếp và cũng trong phần ROWS của Bảng Tổng hợp. Nếu có 4 trường trong phần ROWS, sẽ có 4 cột để sắp xếp và 3 cột trong công thức cho nhóm.
4. Bước cuối cùng để thêm Tổng chạy vào Bảng Tổng hợp
Tiếp theo, điều hướng đến bảng chỉnh sửa Bảng Tổng hợp và thêm trường ‘Tổng chạy’ dưới phần GIÁ TRỊ. Chọn hàm MAX từ danh sách xổ xuống trong ‘Tóm tắt theo’.
Nhấp đúp vào ô J1 và thay thế “MAX của Tổng chạy” thành “Tổng chạy”.
Phương pháp này cho phép bạn dễ dàng thêm tổng chạy vào Bảng Tổng hợp khi sử dụng các trường độc quyền trong phần ROWS của Google Sheets.
Thêm Tổng chạy trong Bảng Tổng hợp được nhóm theo hàng và cột
Bạn có thể thêm tổng chạy vào hàng trong Bảng Tổng hợp trong Google Sheets?
Trong ví dụ trên, các tổng tích lũy được thêm vào một cột trong Bảng Tổng hợp vì chúng tôi đã nhóm dữ liệu theo hàng.
Nếu chúng ta sử dụng các trường trong phần ROWS và COLUMNS của bảng chỉnh sửa Bảng Tổng hợp, thì tổng chạy cần được thêm vào nhiều cột và chúng ta đọc chúng từng dòng.
Hãy học điều này thông qua một ví dụ. Ở đây, chúng ta cũng có dữ liệu trong A1: D, trong đó A, B, C và D chứa Ngày, Danh mục, Danh mục phụ và Số lượng, tương ứng.
Xem cài đặt trình chỉnh sửa Bảng Tổng hợp như sau.
Tôi đã thêm các trường ‘Danh mục’ vào phần ROWS, và ‘Danh mục phụ’ và ‘Ngày’ vào phần COLUMNS. Ngoài ra, tôi đã bao gồm trường ‘Danh mục’ trong phần bộ lọc để lọc các hàng trống trong phạm vi đang được đánh giá.
Công thức và Cài đặt Bổ sung
Sắp xếp dữ liệu theo thứ tự Danh mục> Danh mục phụ> Ngày. Điều này tương ứng với các trường trong ROWS và COLUMNS của chúng ta. Điều này tương tự như cài đặt trước đó của chúng ta.
- Chọn A1: D13 và nhấp chuột phải vào Dữ liệu> Sắp xếp phạm vi> Tùy chọn sắp xếp phạm vi nâng cao.
- Kiểm tra “Dữ liệu có hàng tiêu đề.”
- Sắp xếp theo Danh mục> Danh mục phụ> Ngày.
Sử dụng các trường ‘Danh mục’ và ‘Danh mục phụ’ cho nhóm trong công thức. Giống như trước, chúng ta không cần xem xét trường cuối cùng trong nhóm; ở đây, nó là “Ngày”.
Chèn công thức sau vào ô E2:
= ARRAYFORMULA (MAP (B2: B, C2: C, LAMBDA (b, c, NẾU (b = "",, SUMIFS (D2: D, B2: B, b, C2: C, c, ROW (A2: A), "<="& ROW (b))))))
Để thêm tổng chạy vào Bảng Tổng hợp, bạn cần bốn cài đặt khác nhau để hoàn thành.
- Nhấp chuột phải vào bất kỳ ngày nào trong báo cáo Bảng Tổng hợp và chọn Tạo nhóm ngày Pivot> Tháng (bạn có thể chọn Năm, Quý, v.v., tùy thuộc vào yêu cầu của bạn).
- Mở bảng chỉnh sửa Bảng Tổng hợp và thêm trường ‘Tổng chạy’ dưới phần GIÁ TRỊ.
- Chọn MAX trong phần “Tóm tắt theo”.
- Nhấp đúp vào ô G1 và thay thế “MAX của Tổng chạy” thành “Tổng chạy”.
Lưu ý bổ sung
Nếu bạn nhóm theo ROWS và COLUMNS, chỉ bao gồm một trường duy nhất trong phần ROWS của Bảng Tổng hợp. Thêm một trường sẽ dẫn đến một báo cáo Bảng Tổng hợp chi tiết, điều này có thể không hữu ích vì tổng chạy được sắp xếp theo hàng ngang.
Đôi khi bạn có thể gặp vấn đề với ô trống, như đã giải thích ở đầu hướng dẫn này.
Kết luận chính
Tổng chạy cung cấp những thông tin quý giá về theo dõi hiệu suất và phân tích so sánh. Kết hợp chúng vào Bảng Tổng hợp trong Google Sheets nâng cao tính hữu ích của chúng.
Trong hướng dẫn này, chúng tôi đã đề cập cách tích hợp tổng chạy vào Bảng Tổng hợp trong Google Sheets. Dưới đây là kết luận chính:
- Bắt đầu bằng cách tạo Bảng Tổng hợp mong muốn.
- Sắp xếp dữ liệu nguồn theo thứ tự nhóm trong bảng chỉnh sửa Bảng Tổng hợp.
- Trong các cột trợ giúp, sử dụng công thức để tính tổng tích lũy dựa trên nhóm đã chọn. Nhóm này nên khớp với thứ tự sắp xếp, nhưng loại trừ trường cuối cùng.
- Dưới phần GIÁ TRỊ, thêm trường cột trợ giúp tổng chạy và tóm tắt bằng MAX. Không cần sử dụng Trường tính toán.
Đó là tất cả!
