Nếu bạn là một trong những độc giả thường xuyên của tôi, chắc hẳn bạn đã biết cách đơn giản nhất để unpivot dữ liệu, còn được gọi là reverse pivot, trong Google Sheets.
Nguyên nhân là, trong một bài viết trước đây của tôi về việc làm phẳng dữ liệu, một trong những độc giả của tôi, ông S K Srivastava, đã chia sẻ ý tưởng tuyệt vời này một cách ngắn gọn. Trong bài viết này, tôi xin ghi nhận công lao của ông ấy.
Gần đây, tôi đã đăng về việc sử dụng một hàm chưa chính thức trong Google Sheets có tên FLATTEN. Chúng ta có thể sử dụng hàm này để unpivot (reverse pivot) một bảng trong Google Sheets.
Đọc thêm: Cách sử dụng hàm FLATTEN trong Google Sheets.
Ngoài FLATTEN, chúng ta chỉ cần SPLIT và dấu & làm ArrayFormula cho việc này.
Tôi chắc chắn rằng đây là cách đơn giản nhất để reverse pivot một bảng mà không cần sử dụng Google Apps Script.
Lưu ý: Nếu dữ liệu pivot của bạn rất lớn như 1000+ hàng (có thể rất hiếm), công thức này có thể không hoạt động vì hàm SPLIT thường gặp vấn đề với một lượng dữ liệu lớn như vậy.
Cách Đơn Giản Nhất để Unpivot Dữ Liệu Trong Google Sheets
Dữ Liệu Ví Dụ để Reverse Pivot
Ở đây, ví dụ, tôi đang nhập một bảng phù hợp cho bài kiểm tra của chúng tôi từ trang Wiki này bằng công thức IMPORTHTML sau đây.
=array_constrain( IMPORTHTML( "https://en.wikipedia.org/wiki/International_wheat_production_statistics", "table",1 ),6,5 )Để biết thêm thông tin, Array_Constrain là một phần của công thức IMPORTHTML để giới hạn kích thước của bảng được nhập.
Dữ Liệu Ví Dụ Đã Được Nhập:
Dữ liệu hiển thị sản xuất lúa mì trên toàn thế giới từ năm 2014 đến năm 2017. Tên các quốc gia được hiển thị trong cột A và sản xuất theo năm được hiển thị trong các cột B đến E.
Hãy xem cách unpivot dữ liệu trên đây trong Google Sheets. Tôi có nghĩa là hiển thị tên quốc gia trong cột đầu tiên, các năm trong cột thứ hai và số lượng trong cột thứ ba như sau.
Công Thức Unpivot Trong Google Sheets
Dưới đây là công thức đơn giản (nhập vào ô I2) để reverse pivot một bảng trong Google Sheets.
=ARRAYFORMULA( split( flatten( A2:A6&"|"&B1:E1&"|"&B2:E6 ),"|" ) )Nếu bạn muốn có nhãn trường như những gì ở ô I2:K2 (vui lòng tham khảo hình ảnh phía trên), hãy chèn điều này {"Country", "Year", "Qty. in Million MT"; ngay sau dấu bằng trong công thức và đóng công thức với }.
Giải thích Công Thức (Cách Sử Dụng Công Thức)
Hàm FLATTEN là yếu tố chính. Bên trong công thức FLATTEN, kết hợp cột đầu tiên trong phạm vi (A2:A6) với hàng đầu tiên trong phạm vi (B1:E1) và sau đó kết hợp phần còn lại của dữ liệu (B2:E6).
Dấu | (bạn có thể sử dụng bất kỳ ký tự khác như ~) được chèn vào giữa để tạo điều kiện tách dữ liệu đã được kết hợp.
Nếu bạn muốn, bạn có thể thay đổi phạm vi kết hợp trong FLATTEN như; kết hợp hàng đầu tiên trong phạm vi (B1:E1) với cột đầu tiên trong phạm vi (A2:A6) và sau đó phần còn lại của dữ liệu (B2:E6).
Nếu bạn tuân theo cách này để unpivot tập dữ liệu trên đây trong Google Sheets, nó sẽ trông như dưới đây.
Chúng Ta Có Thể Sử Dụng Phạm Vi Mở Thay Vì Phạm Vi Đóng Trong Flatten?
Sử dụng A2:A và B2:E thay vì A2:A6 và B2:E6 có thể gây ra một số vấn đề như lỗi #REF! trong công thức, đóng băng sheet của bạn hoặc có thể chèn hàng trống hàng trăm.
Ở đây, chúng ta có thể sử dụng một kỹ thuật mà tôi đã thành công sử dụng trước đó với MMULT – Sử dụng Đúng của MMULT trong số hàng Vô Hạn trong Google Sheets.
Đó là gì?
Thay thế A2:A6 bằng phạm vi động (vô hạn / mở);
array_constrain(A2:A,MATCH(2,1/(A2:A<>""),1),1)Thay thế B2:E6 bằng phạm vi động (vô hạn / mở);
array_constrain(B2:E,MATCH(2,1/(A2:A<>""),1),4)Có 4 cột trong phạm vi B2:E6. Số 4 trong phần cuối của công thức đại diện cho tổng số cột này.
Làm thế nào để Bao Gồm Nhiều Hơn Một Cột Cố Định trong Tập Dữ Liệu Unpivot Trong Google Sheets?
Trong ví dụ trên, chúng ta chỉ có một cột cố định và đó là cột A chứa ‘Country’.
Nếu chúng ta muốn, chúng ta có thể bao gồm nhiều hơn một cột cố định trong tập dữ liệu unpivot trong Google Sheets. Để làm được điều đó, chúng ta phải sửa đổi công thức trên một chút. Tôi có một cách (công thức) động để thực hiện điều đó.
Tôi có nghĩa là chúng ta có thể bao gồm bất kỳ số lượng cột cố định nào với một sửa đổi nhỏ trong công thức reverse pivot ở trên.
Đây là dữ liệu ví dụ mới của tôi. Tôi đã bổ sung một cột bổ sung có tên ‘Continent’ vào tập dữ liệu ví dụ trước đây của chúng tôi (vui lòng tham khảo Hình ảnh số 01 cho tập dữ liệu cũ).
Nếu bạn bỏ qua cột ‘Continent’ ở cột B, theo cùng một công thức reverse pivot trước đây, công thức (1 cột cố định) cho phạm vi này sẽ như sau.
=ARRAYFORMULA( split( flatten( A2:A6&"|"&C1:F1&"|"&C2:F6 ),"|" ) )Tôi đã bỏ qua B2:B6 và kết quả là đầu ra unpivot sẽ giống với Hình ảnh số 02 phía trên.
Hãy xem cách bao gồm nhiều cột cố định trong tập dữ liệu unpivot trong Google Sheets.
Các Bước
Trong công thức trên, A2:A6 là một và chỉ một cột cố định của chúng ta. Nhưng điều chúng ta muốn là A2:B6. Thay thế A2:A6 trong công thức trên bằng công thức dưới đây bắt đầu bằng ‘transpose’.
transpose(query(transpose(A2:B6&"|"),,9^9))Vì vậy, công thức để unpivot một tập dữ liệu với nhiều hơn một (ở đây là hai) cột cố định sẽ như sau.
=ARRAYFORMULA( split( flatten( transpose(query(transpose(A2:B6&"|"),,9^9))&"|"&C1:F1&"|"&C2:F6 ),"|" ) )Bạn có thể dễ dàng làm cho thêm các cột cố định bằng cách thay đổi A2:A6. Tôi có nghĩa là A2:B6 như trên cho hai cột, A2:C6 cho ba cột, và vân vân.
Lợi Ích của Reverse Pivot
Lợi ích chính của việc reverse pivot một tập dữ liệu là nó mang lại tính linh hoạt trong việc thao tác với dữ liệu bằng các hàm như SUMIF, QUERY, FILTER, VLOOKUP, v.v.
Dưới đây là hai ví dụ. Hãy bắt đầu bằng SUMIF.
Gỉa sử chúng ta muốn tính tổng sản lượng lúa mì tại Trung Quốc trong những năm 2014-2017. Chúng ta có thể sử dụng SUMIF như sau (dữ liệu như hình ảnh số 03).
=sumif(J2:J21,"China",K2:K21)Xin vui lòng xem hình ảnh phía trên để biết về phạm vi được sử dụng trong SUMIF.
Lưu ý: Nếu bạn sử dụng ” | ” thay vì “|” trong công thức unpivot dataset, thì SUMIF trước đó sẽ trả về 0 do không phù hợp với tiêu chí.
Trong ví dụ tiếp theo, tôi sử dụng QUERY để nhóm các quốc gia và tổng sản lượng lúa mì.
=query({I1:K21},"Select Col2, Sum(Col3) group by Col2")Kết quả:
Country sum Qty. in Million MT
Ở đây, bạn tự do thay thế I1:K21 bằng công thức unpivot dataset chính nó.
Đó là tất cả về cách unpivot một tập dữ liệu trong Google Sheets. Cảm ơn đã theo dõi. Chúc bạn có những giờ phút thú vị!
Sử dụng Crawlan.com để tìm hiểu thêm về SEO và nâng cao hiệu suất của trang web của bạn!
