Sử dụng GETPIVOTDATA với Nhóm Ngày trong Google Sheets

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

Trong Google Sheets, chúng ta có thể nhóm một trường ngày bằng cách sử dụng Pivot Table theo nhiều cách khác nhau, chẳng hạn như theo Ngày, Tháng, Quý, Năm và Năm-Tháng. Hướng dẫn này khám phá cách sử dụng hàm GETPIVOTDATA với các ngày nhóm này trong Google Sheets.

Trong suốt hướng dẫn này, tôi sẽ hướng dẫn bạn qua bốn ví dụ. Cụ thể, chúng ta sẽ tạo ra bốn Pivot Table riêng biệt từ một bộ dữ liệu nguồn duy nhất và sử dụng hàm GETPIVOTDATA để truy xuất các giá trị tổng hợp cần thiết.

Rất tiếc, nhiều người dùng gặp khó khăn khi giải mã quy trình này, thường gặp lỗi công thức #REF! do tuân theo cú pháp của hàm mà không hiểu rõ. Hãy cùng khám phá những nguyên nhân phổ biến này:

  1. Không khớp cột ban đầu: Đối số original_column ideally nên phản ánh chính xác tên cột từ bộ dữ liệu nguồn. Tuy nhiên, ‘Pivot date grouping’ có thể làm thay đổi nhỏ trong Pivot Table, dẫn đến sự nhầm lẫn tiềm tàng.

  2. Định dạng Tiêu chí ngày: Dù một ngày có trải qua ‘Pivot date grouping’ hay không, cần phải chỉ định nó dưới dạng văn bản trong đối số pivot_item.

Hãy khám phá cách sử dụng đúng cách hàm GETPIVOTDATA với các ngày nhóm trong Google Sheets.

Dữ liệu và Pivot Table Mẫu

Hãy xem xét một số dữ liệu mẫu giả tưởng với ba cột.

Country Date of Travel  Number of Travelers

Giả sử dữ liệu mẫu trên nằm trong phạm vi ô A1:C7 trong “Sheet1.” Chúng ta sẽ tạo Pivot Table trong cùng một sheet bắt đầu từ ô A9. Đây là các bước cần làm:

  1. Di chuyển đến ô A9.
  2. Nhấp chuột vào “Insert” > “Pivot Table.”
  3. Trong trường “Data range”, nhập phạm vi chính xác là A1:C7.
  4. Chọn “Existing sheet” và nhập A9 vào trường cung cấp.
  5. Nhấp “Create” để chèn Pivot Table và mở bảng chỉnh sửa bên sườn của Pivot Table.
  6. Kéo và thả trường “Date of Travel” dưới Rows.
  7. Kéo và thả trường “Country” dưới Columns.
  8. Dưới Values, kéo và thả trường “Number of Travelers” và đảm bảo “Summarize by” được thiết lập thành SUM.

Để truy cập Sheets mẫu, Pivot Tables và các công thức, hãy nhấp vào nút dưới đây và làm theo hướng dẫn trên màn hình.

Sample Sheet

Sử dụng GETPIVOTDATA với Nhóm Ngày trong Rows trong Google Sheets

Chúng ta có một báo cáo Pivot Table trong Google Sheets hiện tại nhóm các ngày theo Rows. Làm thế nào để chúng ta sử dụng hàm GETPIVOTDATA với các ngày nhóm này trong Rows?

Trong ví dụ đầu tiên, chúng ta sẽ không áp dụng nhóm ngày thủ công, có nghĩa là không có nhóm Tháng, Năm, Quý, v.v.

Ví dụ 1 (Lọc theo Rows)

(Tham khảo Sheet1 trong Bảng mẫu ở trên)

Công thức sau truy xuất tổng số lượng du khách vào ngày 02/01/2023:

=GETPIVOTDATA("SUM of Number of Travelers", A9, "Date of Travel", "01/02/2023")

GETPIVOTDATA with Grouped Dates in Rows in Google Sheets

Giải thích Công thức:

Công thức tuân theo cú pháp sau:

GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, …], [pivot_item, …])

Trong đó:

  • GETPIVOTDATA: Truy xuất một giá trị từ một Pivot Table.
  • “SUM of Number of Travelers” (value_name): Đây là tên trường dữ liệu bạn muốn truy xuất, cùng với tổng hợp cụ thể (trong trường hợp này, tổng) được sử dụng trong Pivot Table.
  • A9 (any_pivot_table_cell): Đây là ô trái trên cùng của Pivot Table.
  • “Date of Travel” (original_column): Tên cột dữ liệu nguồn được sử dụng để lọc.
  • “01/02/2023” (pivot_item): Tiêu chí ngày để lọc. Phù hợp với định dạng được sử dụng trong dữ liệu nguồn.

Điểm chính:

  1. Điều chỉnh các tham chiếu ô và tên trường dựa trên cấu trúc Pivot Table thực tế của bạn.
  2. Đảm bảo định dạng ngày nhất quán giữa công thức và dữ liệu nguồn.

Khi sử dụng tham chiếu ô cho tiêu chí pivot_item (ví dụ: G2), hiển thị nó dưới dạng văn bản bằng một trong các phương pháp sau:

  • Hàm TEXT: TEXT(G2, “DD/MM/YYYY”) (điều chỉnh định dạng nếu cần).
  • Dấu ngoặc đơn: ‘ trước ngày.
  • Định dạng là Văn bản thuần: Áp dụng Format > Number > Plain Text cho ô.

Ví dụ 2 (Nhóm ngày thủ công và Lọc theo Rows)

(Tham khảo Sheet2 trong Bảng mẫu ở trên)

Ở đây, hãy sử dụng Pivot Table hiện tại với nhóm ngày.

Để nhóm ngày thủ công theo tháng trong Pivot Table:

  1. Nhấp chuột phải vào bất kỳ ngày nào trong phạm vi ô Pivot Table A11:A14.
  2. Chọn “Create pivot date group” > “Month”.

Để trích xuất số lượng du khách đến Ý (IT) vào tháng Giêng, sử dụng công thức sau:

=GETPIVOTDATA("SUM of Number of Travelers", A9, "Date of Travel - Month", "Jan", "Country", "IT")

Công thức trả về tổng số lượng du khách đến Ý vào tháng Giêng.

Grouped by Month in Pivot Table in Google Sheets

Giải thích:

  • GETPIVOTDATA: Truy xuất một giá trị từ một Pivot Table.
  • “Sum of Number of Travelers” (value_name): Chỉ định tiêu đề cột trong Pivot Table, cho biết tên trường dữ liệu mà bạn muốn truy xuất.
  • A9 (any_pivot_table_cell): Tham chiếu đến bất kỳ ô nào trong Pivot Table, thường là ô trái trên cùng.
  • “Date of Travel – Month” (original_column): Sử dụng tên này vì nó phản ánh trường ngày đã được nhóm trong Pivot Table, mặc dù khác với tên cột dữ liệu nguồn.
  • “Jan” (pivot_item): Tiêu chí ngày để lọc, phù hợp với tên tháng đã được nhóm.
  • “Country” (original_column): Tên cột dữ liệu nguồn được sử dụng để lọc.
  • “IT” (pivot_item): Quốc gia để lọc.

Điểm chính:

  1. Khi sử dụng GETPIVOTDATA với các ngày được nhóm thủ công trong Google Sheets, quan trọng là sử dụng chính xác tên trường đã được nhóm như hiển thị trong Pivot Table, ngay cả khi nó khác với tên cột dữ liệu nguồn. Trong trường hợp này, chúng ta đã sử dụng “Date of Travel – Month” thay vì “Date of Travel” (original_column).
  2. Phù hợp pivot_item cho nhóm ngày (“Jan”) với tên tháng hiển thị trong Pivot Table.
  3. Điều chỉnh các tham chiếu ô và tên trường dựa trên cấu trúc thực tế của Pivot Table.

Sử dụng GETPIVOTDATA với Nhóm Ngày trong Columns trong Google Sheets

Lần này, chúng ta sẽ sử dụng các cài đặt Pivot Table khác nhau. (5 bước đầu tiên giống như ở trên, nhưng tôi lặp lại chúng để tiết kiệm thời gian bạn cuộn lên.)

  1. Di chuyển đến ô A9.
  2. Nhấp chuột vào “Insert” > “Pivot Table.”
  3. Trong trường “Data range”, nhập phạm vi chính xác là A1:C7.
  4. Chọn “Existing sheet” và nhập A9 vào trường cung cấp.
  5. Nhấp “Create” để chèn Pivot Table và mở bảng chỉnh sửa bên sườn của Pivot Table.
  6. Kéo và thả trường “Country” dưới Rows.
  7. Kéo và thả trường “Date of Travel” dưới Columns.
  8. Dưới Values, kéo và thả trường “Number of Travelers” và đảm bảo “Summarize by” được thiết lập thành SUM.

Chúng ta sẽ thử nghiệm hàm GETPIVOTDATA trong Pivot Table này cả với và không có nhóm ngày thủ công.

Ví dụ 1 (Lọc theo Columns)

(Tham khảo Sheet3 trong Bảng mẫu ở trên)

Mặc dù bố trí khác nhau, chúng ta có thể sử dụng cùng một công thức như trong “Ví dụ 1 (Lọc theo Rows)” để lấy tổng số lượng du khách vào ngày 02/01/2023. Điều này là do Pivot Table bắt đầu từ ô A9 và chúng ta chưa sử dụng bất kỳ tham chiếu ô khác nào trong Pivot Table trong công thức.

Công thức sau sẽ trả về số lượng chuyến du lịch đến Pháp vào “01/01/2023”:

=GETPIVOTDATA("SUM of Number of Travelers", A9, "Date of Travel", "01/01/2023", "Country", "FR")

Không cần phải nói, bạn có thể sử dụng công thức này với Pivot Table được sử dụng trong “Ví dụ 1 (Lọc theo Rows)” để đạt được cùng kết quả.

GETPIVOTDATA with Grouped Dates in Columns in Google Sheets

Giải thích Công thức:

  • GETPIVOTDATA: Truy xuất một giá trị từ một Pivot Table.
  • “SUM of Number of Travelers” (value_name): Chỉ định tiêu đề cột trong Pivot Table.
  • A9 (any_pivot_table_cell): Đây là ô trái trên cùng của Pivot Table.
  • “Date of Travel” (original_column): Tên cột dữ liệu nguồn được sử dụng để lọc.
  • “01/02/2023” (pivot_item): Tiêu chí ngày để lọc.
  • “Country” (original_column): Tên cột dữ liệu nguồn được sử dụng để lọc.
  • “FR” (pivot_item): Quốc gia để lọc.

Ví dụ 2 (Nhóm ngày thủ công và Lọc theo Columns)

(Tham khảo Sheet4 trong Bảng mẫu ở trên)

Hãy áp dụng nhóm ngày thủ công và khám phá cách sử dụng hàm GETPIVOTDATA với ngày đã được nhóm trong các cột.

Trong trường hợp này, công thức được sử dụng trong “Ví dụ 2 (Nhóm ngày thủ công và Lọc theo Rows)” sẽ cho kết quả tương tự.

Trong trường hợp đó, chúng ta đã áp dụng nhóm Tháng. Ở đây, chúng ta sẽ sử dụng một nhóm khác, nói chính xác là “Năm-Tháng”.

Để nhóm ngày theo Năm-Tháng trong Pivot Table:

  1. Nhấp chuột phải vào bất kỳ ngày nào trong Pivot Table trên, tức là “Ví dụ 1 (Lọc theo Columns),” phạm vi ô B10:E10.
  2. Chọn “Create pivot date group” > “Year-Month”.

Công thức:

=GETPIVOTDATA("SUM of Number of Travelers", A9, "Date of Travel - Year-Month", "2023-Jan", "Country", "FR")

Công thức này sẽ trả về số lượng chuyến du lịch trong tháng Giêng năm 2023 đến Pháp.

Grouped by Year-Month in Pivot Table in Google Sheets

Nếu bạn nhóm Pivot Table trong “Ví dụ 2 (Nhóm ngày thủ công và Lọc theo Rows)” theo Năm-Tháng thay vì Tháng và áp dụng cùng công thức trên, bạn sẽ đạt được cùng kết quả.

Giải thích công thức:

  • GETPIVOTDATA: Truy xuất một giá trị từ một Pivot Table.
  • “Sum of Number of Travelers” (value_name): Chỉ định tiêu đề cột trong Pivot Table.
  • A9 (any_pivot_table_cell): Tham chiếu đến bất kỳ ô nào trong Pivot Table, thường là ô trái trên cùng.
  • “Date of Travel – Year-Month” (original_column): Sử dụng tên này vì nó phản ánh trường ngày đã được nhóm trong Pivot Table.
  • “2023-Jan” (pivot_item): Tiêu chí ngày để lọc, phù hợp với tên Năm-Tháng đã được nhóm.
  • “Country” (original_column): Tên cột dữ liệu nguồn được sử dụng để lọc.
  • “FR” (pivot_item): Quốc gia để lọc.

Các điểm cốt yếu về GETPIVOTDATA với Ngày được nhóm

GETPIVOTDATA là một công cụ rất hữu ích để truy xuất giá trị từ một Pivot Table. Nó có thể được sử dụng với trường ngày được nhóm trong cả Rows và Columns theo cùng một cách thức. Công thức không thay đổi khi bố trí trải qua sự điều chỉnh.

Tuy nhiên, điều quan trọng là đảm bảo Pivot Table không bị di chuyển khi điều chỉnh bố trí. Nếu bạn di chuyển nó đến một phạm vi khác, bạn cần chỉ định đúng đối số any_pivot_table_cell trong công thức. Không cần các thay đổi khác.

Dưới đây là những điểm cần nhớ khi sử dụng GETPIVOTDATA với ngày được nhóm trong Google Sheets:

  1. Bạn cần chỉ định đúng các đối số original_column và pivot_item dựa trên việc nhóm ngày.
  2. Nếu không áp dụng nhóm ngày thủ công, hãy sử dụng original_column, trong trường hợp của chúng tôi là “Date of Travel,” như là cột ban đầu và bất kỳ ngày duy nhất nào từ cột này làm pivot_item, nhưng ở cùng định dạng ngày.
  3. Nếu áp dụng nhóm ngày thủ công, hãy sử dụng original_column như nó xuất hiện trong Pivot Table và sử dụng tiêu chí pivot_item tương ứng.

Tài liệu tham khảo:

  1. Unlock the Power of GETPIVOTDATA Arrays in Google Sheets.
  2. Drill Down in Pivot Table in Google Sheets (Date Field).
  3. Extract Total and Grand Total Rows From a Pivot Table in Google Sheets.

GiGiKing

Related posts