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 một bộ dữ liệu (còn được gọi là reverse pivot) trong Google Sheets.
Lý do là, trong một trong những bài viết trước đây của tôi về việc làm phẳng bộ 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. Tôi muốn ghi công cho ông ta trong bài viết này.
Gần đây, tôi đã đăng về việc sử dụng một chức năng không chính thức trong Google Sheets gọi là FLATTEN. Chúng ta có thể sử dụng chức năng này để unpivot (reverse pivot) một bảng trong Google Sheets.
Đọc thêm: Cách Sử Dụng Chức Năng FLATTEN trong Google Sheets
Ngoài FLATTEN, chúng ta chỉ cần SPLIT và ký hiệu & để tạo một ArrayFormula cho công thứ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+ dòng (điều này có thể rất hiếm), công thức này có thể không hoạt động vì chức năng SPLIT thường gặp vấn đề với một tập hợp dữ liệu lớn như vậy.
Cách Dễ Nhất để Unpivot một Bộ Dữ Liệu trong Google Sheets
Dữ Liệu Mẫu để Unpivot
Ở đây, ví dụ, tôi đang nhập một bảng phù hợp để thử nghiệm từ trang Wiki này bằng công thức IMPORTHTML sau:
Công thức:
=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 đã nhập.
Dữ Liệu Mẫu Đã Nhập:
Dữ liệu cho thấy sản xuất lúa mì trên toàn thế giới từ năm 2014 đến 2017. Tên các quốc gia nằm trong cột A và sản xuất theo năm nằm trong các cột B đến E.
Hãy xem làm thế nào để unpivot dữ liệu trên trong Google Sheets. Tức là, đưa tên quốc gia vào cột đầu tiên, năm vào cột thứ hai và số lượng vào 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 (ghi vào ô I2) để reverse pivot một bảng trong Google Sheets.
Công Thức Unpivot:
=ARRAYFORMULA(SPLIT(FLATTEN(A2:A6&”|”&B1:E1&”|”&B2:E6),”|”))
Nếu bạn muốn có nhãn trường như những cái trong ô I2:K2 (vui lòng xem hình ảnh phía trên), hãy thêm {“Country”, “Year”, “Qty. in Million MT”} vào ngay sau dấu bằng trong công thức và đóng công thức bằng }.
Giải Thích Công Thức (Cách Sử Dụng Công Thức)
Chức năng FLATTEN là chìa khóa. Bên trong công thức FLATTEN, hợp nhất 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 đó hợp nhất 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 hợp nhất 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 bộ dữ liệu trên trong Google Sheets, nó sẽ trông như sau:
Có Thể Sử Dụng Các Phạm Vi Mở Thay Vì Các 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, làm đóng băng bảng 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 đã sử dụng thành công trong MMULT – Proper Use of MMULT in Infinite Rows in 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ợi Ích của Reverse Pivot
Lợi ích chính của việc reverse pivot một bộ dữ liệu là nó mang lại tính linh hoạt khi xử lý dữ liệu với các chức năng như SUMIF, QUERY, FILTER, VLOOKUP, v.v.
Dưới đây là hai ví dụ. Hãy bắt đầu với SUMIF.
Giả sử chúng ta muốn tính tổng sản lượng lúa mì tại quốc gia Trung Quốc trong những năm 2014-2017. Chúng ta có thể sử dụng SUMIF như sau (dữ liệu theo hình ảnh phía trên).
=SUMIF(J2:J21,”China”,K2:K21)
Hãy xem hình ảnh phía trên để biết 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 bộ dữ liệu đã được unpivot, thì SUMIF ở trên sẽ trả về 0 do không khớp tiêu chí.
Trong ví dụ tiếp theo, tôi sử dụng QUERY để nhóm các quốc gia và tính 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 thoải mái thay thế I1:K21 bằng công thức unpivot chính nó.
Đó là tất cả về cách unpivot một bộ dữ liệu trong Google Sheets. Cảm ơn đã theo dõi. Chúc bạn thích thú!
Tài liệu mẫu: Example Sheet 300420
