Cách thêm tổng chạy trong Pivot Table trên Google Sheets

GiGiKing·Tháng ba 18, 2024·0 comments

Bạn đã từng thử thêm tổng chạy vào Pivot Table trên Google Sheets mà không thành công? Dưới đây là những lý do và cách thực hiện:

Trước khi thêm tổng chạy vào Pivot Table trên Google Sheets, hãy xem xét những điểm quan trọng sau đây, chúng ta sẽ thảo luận chi tiết về chúng trong hướng dẫn này:

  1. Đảm bảo dữ liệu nguồn được sắp xếp theo cùng thứ tự với các trường nhóm trong Pivot Table để tính toán tổng chạy chính xác.
  2. Thêm một cột trợ giúp vào dữ liệu nguồn để hiển thị tổng chạy trong Pivot Table. Cột này sẽ có một công thức mảng trong ô trên cùng.
  3. Đặt các trường chỉ trong phần ROWS của Pivot Table. Bao gồm các trường trong cả phần ROWS và COLUMNS chỉ khi 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ụ, hãy xem xét 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 sản lượng là 5 tấn. Tổng chạy kết quả trong Pivot Table có thể xuất hiện như sau:

Pivot Table Example

Tuy nhiên, hãy lưu ý rằng giá trị trong ô trống (tháng 3) nên là 10, tiếp tục giá trị của tháng trước. Thật không may, điều này không xảy ra.

Hãy nhớ rằng việc giải quyết những điều này là rất quan trọng để thành công khi thêm tổng chạy vào Pivot Table trên Google Sheets.

Lợi ích khi bao gồm tổng chạy trong Pivot Table trên Google Sheets

  1. Dễ dàng so sánh: Thêm tổng chạy cho phép so sánh dễ dàng các giá trị tích lũy giữa các danh mục hoặc nhóm khác nhau.
  2. 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 (điều hướng chi tiết) trong một Pivot Table, mang lại sự linh hoạt trong khám phá dữ liệu.
  3. Sử dụng tính năng nhóm trong Pivot Table: 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 một nhóm bảng số liệu” trong Pivot Table. Tổng chạy sẽ tự động phản ứng với nhóm này.
  4. Hiểu các xu hướng theo thứ tự: Những tính năng này cùng nhau hỗ trợ trong việc hiểu các xu hướng và mẫu theo thứ tự tuần 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 Pivot Table trên 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.

Trong bài viết này, 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 Pivot Table trên Google Sheets.

Việc sử dụng hai ví dụ là rất 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ủa bạn. Ngoài ra, bạn có thể 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:

Sample Sheet

Thêm tổng chạy trong Pivot Table được nhóm theo hàng

Dữ liệu mẫu cung cấp bao gồm vật liệu cung cấp cho hai dự án khác nhau từ các nguồn khác nhau, cùng với số lượng của chú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.

Running Total in Pivot Table Grouped by Rows

Tạo Pivot Table và tính tổng tích lũy: Hướng dẫn bước từng bước

Tôi đã tổ chức các bước thành bốn loại: Tạo Pivot Table, 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 Pivot Table.

1. Tạo Pivot Table

Để tạo Pivot Table, làm theo các bước sau:

  1. Chọn phạm vi A1:E.
  2. Nhấp vào Insert > Pivot Table.
  3. Chọn ‘Existing Sheet’ và chọn ô G1 dưới đây, sau đó nhấp vào ‘Create’.
  4. Thêm các trường như hình ảnh minh họa dưới đây.

Chúng ta đã thêm Dự án, Vật liệu và Nguồn trong khi bỏ chọn ‘Show Total’ trong mỗi trường. Bạn có thể chọn để chọn hoặc bỏ chọn ‘Repeat row labels’ trong các trường này nếu cần.

Ngoài ra, tôi đã bao gồm trường ‘Dự án’ dưới phần mục FILTERS để 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 một 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 cần thiết.

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 trong Pivot Table – cụ thể là sắp xếp theo ‘Dự án’, ‘Vật liệu’ và ‘Nguồn’.

  1. Chọn phạm vi A1:D19.
  2. Nhấp chuột vào Data > Sort range > Advanced sorting range options.
  3. Chọn “Data has header row” và sắp xếp theo ‘Dự án’, ‘Vật liệu’ và ‘Nguồn’.

Sorting data for a cumulative sum in a Pivot Table

3. Thêm công thức cho cột trợ giúp

Tiếp theo, chúng ta sẽ áp 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, chèn công thức mảng sau:

=ARRAYFORMULA(MAP( A2:A, B2:B, LAMBDA(a, b, IF(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 ROW(a). Các chỉ số 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.

Hãy xem hướng dẫn của tôi, Tổng chạy với Nhiều Phụ loại, để hiểu rõ hơn về cấu trúc công thức. Ở đây, tôi sẽ hướng dẫn 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 là 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 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,.

Tiếp tục 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ác cột trong công thức, đảm bảo tính nhất quán trong việc sắp xếp và cũng trong phần ROWS của Pivot Table. Nếu có 4 trường trong phần ROWS, sẽ có 4 cột cho việc sắp xếp và 3 cột trong công thức cho nhóm.

4. Các bước cuối cùng để thêm tổng chạy vào Pivot Table

Tiếp theo, vào bảng điều khiển chỉnh sửa Pivot Table và thêm trường ‘Tổng chạy’ dưới phần VALUES. Chọn hàm MAX từ menu thả xuống trong phần “Summarize by”.

Nhấp đúp vào ô J1 và thay thế “MAX of Running Total” bằng “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 Pivot Table khi chỉ sử dụng các trường chỉ dưới phần ROWS trong Google Sheets.

Thêm tổng chạy trong Pivot Table được nhóm theo hàng và cột

Còn việc thêm tổng chạy trong hàng vào Pivot Table trên Google Sheets thì sao?

Trong ví dụ trên, tổng tích lũy được thêm vào một cột trong Pivot Table vì chúng ta đã 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 điều khiển Pivot Table, thì tổng chạy cần được thêm vào dưới nhiều cột và chúng ta đọc chúng theo từng hàng.

Hãy học điều này 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, Phụ loại và Số lượng, tương ứng.

Running Total in Pivot Table Grouped by Rows and Columns

Hãy xem các thiết lập bảng điều khiển Pivot Table.

Tôi đã thêm các trường ‘Danh mục’ trong phần ROWS, và ‘Phụ loại’ và ‘Ngày’ trong phần COLUMNS. Ngoài ra, tôi đã bao gồm trường ‘Danh mục’ trong phần mục FILTER để lọc bỏ các hàng trống trong phạm vi đang được đánh giá.

Công thức và Các thiết lập bổ sung

Sắp xếp dữ liệu theo thứ tự Danh mục > Phụ loại > Ngày. Điều này tuân theo các trường trong phần ROWS và COLUMNS. Điều này giống với các cài đặt trước đó của chúng ta.

  1. Chọn A1:D13 và nhấp chuột vào Data > Sort range > Advanced range sorting options.
  2. Kiểm tra “Data has header row.”
  3. Sắp xếp theo Danh mục > Phụ loại > Ngày.

Chúng ta sử dụng trường ‘Danh mục’ và ‘Phụ loại’ cho nhóm trong công thức. Như trước, chúng ta không cần xem xét trường cuối cùng trong nhóm; ở đây, là “Ngày”.

Chèn công thức sau vào ô E2:

=ARRAYFORMULA(MAP( B2:B, C2:C, LAMBDA(b, c, IF(b="", , SUMIFS(D2:D, B2:B, b, C2:C, c, ROW(A2:A), "<="&ROW(b)) ))))

Để thêm tổng chạy vào Pivot Table, bạn cần thêm bốn cài đặt nữa để hoàn thành.

  1. Nhấp chuột phải vào bất kỳ ngày nào trong báo cáo Pivot Table 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).
  2. Mở bảng điều khiển Pivot Table và thêm trường ‘Tổng chạy’ dưới phần VALUES.
  3. Chọn Max trong “Summarize by.”
  4. Nhấp đúp vào ô G1 và thay thế “MAX of Running Total” bằng “Tổng chạy”.

Ghi chú bổ sung

Nếu bạn nhóm theo ROWS và COLUMNS, chỉ cần bao gồm một trường trong phần ROWS của Pivot Table. Thêm một trường sẽ dẫn đến việc điều hướng chi tiết trong báo cáo Pivot Table, điều này có thể không hữu ích vì tổng tích lũ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 quan trọng

Tổng chạy cung cấp thông tin quý giá cho việc theo dõi hiệu suất và phân tích so sánh. Kết hợp chúng vào Pivot Table trên 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 đã tìm hiểu cách tích hợp tổng chạy vào Pivot Table trên Google Sheets. Đây là những điểm quan trọng:

  1. Bắt đầu bằng cách tạo Pivot Table mong muốn.
  2. Đảm bảo sắp xếp dữ liệu nguồn theo thứ tự nhóm trong bảng điều khiển Pivot Table.
  3. Trong một cột trợ giúp, sử dụng một công thức để tính toán tổng chạy dựa trên nhóm đã chọn. Nhóm này nên phù hợp với thứ tự sắp xếp nhưng không bao gồm trường cuối cùng.
  4. Dưới phần VALUES, thêm trường cột trợ giúp tổng chạy và tóm tắt theo MAX. Không cần sử dụng Trường Tính toán.

Đó là tất cả!

GiGiKing

Related posts