Nếu bạn là một trong những người đọc thường xuyên của tôi, chắc hẳn bạn đã biết cách đơn giản nhất để đảo ngược bảng dữ liệu, còn được gọi là đảo ngược Pivot, trong Google Sheets. Lý do là, trong một bài viết trước đây về 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. Tôi xin ghi công cho ông ấy trong bài viết này. Gần đây, tôi đã đăng về việc sử dụng 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 để đảo ngược bảng (reverse pivot) trong Google Sheets.
Phải đọc: Cách sử dụng chức năng FLATTEN trong Google Sheets.
Ngoài FLATTEN, chúng ta chỉ cần SPLIT và dấu & là một ArrayFormula cho việc này. Tôi chắc chắn rằng đây là cách đơn giản nhất để đảo ngược 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 (điều này 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 dữ liệu lớn như vậy.
Cách đơn giản nhất để đảo ngược bảng dữ liệu trong Google Sheets
Dữ liệu mẫu để đảo ngược pivot
Ở đây, ví dụ, tôi sẽ nhập một bảng phù hợp cho bài kiểm tra của chúng tôi từ trang Wiki bằng cách sử dụng công thức IMPORTHTML sau đây.
Công thức:
=array_constrain( IMPORTHTML( "https://en.wikipedia.org/wiki/International_wheat_production_statistics", "table",1 ),6,5 )Đối với thông tin của bạn, 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 khẩu.
Dữ liệu Mẫu Đã Nhập:
Ảnh số 01
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 2017. Tên các quốc gia được ghi trong cột A và sản xuất theo năm được ghi trong các cột B đến E. Hãy xem cách để đảo ngược dữ liệu trên vào Google Sheets. Nghĩa là, tên các quốc gia trong cột đầu tiên, năm trong cột thứ hai và số lượng trong cột thứ ba như sau.
Ảnh số 02
Công thức đảo ngược pivot trong Google Sheets
Dưới đây là công thức đơn giản (nhập vào ô I2) để đảo ngược pivot một bảng trong Google Sheets.
Công thức đảo ngược pivot:
=ARRAYFORMULA( split( flatten( A2:A6&"|"&B1:E1&"|"&B2:E6 ),"|" ) )Nếu bạn muốn nhãn trường như những gì ở ô I2:K2 (vui lòng xem ảnh bên trên), hãy chèn cái này {"Quốc gia", "Năm", "Số lượng trong triệu tấn”} 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)
Hàm FLATTEN là chìa khóa. 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 dò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). Ký tự “|” (bạn có thể sử dụng bất kỳ ký tự khác như dấu ngã) được chèn vào giữa để dễ dàng phân tách dữ liệu đã được kết hợp. Nếu bạn muốn, bạn có thể sửa đổi phạm vi kết hợp trong FLATTEN như; kết hợp dò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 làm theo cách này để đảo ngược bộ dữ liệu trên vào 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, đó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 trước đây với MMULT – Sử dụng Đúng MMULT trong 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. Con 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 bộ dữ liệu đảo ngược pivot trong Google Sheets?
Trong ví dụ trên, chúng ta chỉ có một cột cố định duy nhất và đó là cột A chứa ‘Quốc gia’. 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 bộ dữ liệu đảo ngược pivot trong Google Sheets. Đối với điều đó, chúng ta phải sửa đổi công thức đảo ngược pivot trên một chút. Tôi có một cách (công thức) linh hoạt để làm điều đó. Nghĩa là chúng ta có thể bao gồm bất kỳ số cột cố định nào với một điều chỉnh nhỏ trong công thức đảo ngược pivot trên. Đây là dữ liệu mẫu mới của tôi. Tôi đã bổ sung thêm một cột có tên “Lục địa” vào bộ dữ liệu mẫu trước đó của chúng tôi (vui lòng xem ảnh số 01 cho bộ dữ liệu cũ).
Ảnh số 04
Nếu bạn bỏ qua cột ‘Lục địa’ B, theo đúng công thức đảo ngược pivot trước đây của chúng ta, 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ả đầu ra sẽ tương tự như ảnh số 02 ở trên. Hãy xem cách bao gồm nhiều hơn một cột cố định trong bộ dữ liệu đảo ngược pivot trong Google Sheets.
Bước
Trong công thức trên, A2:A6 là một cột cố định duy nhất. Nhưng điều chúng ta muốn là A2:B6. Thay thế A2:A6 bằng công thức dưới đây mà bắt đầu bằng transpose.
transpose(query(transpose(A2:B6&"|"),,9^9))Vì vậy, công thức để đảo ngược bộ dữ liệu với nhiều hơn một (ở đây là hai) cột cố định sẽ như sau.
Công thức đảo ngược pivot nhiều cột cố định:
=ARRAYFORMULA( split( flatten( transpose(query(transpose(A2:B6&"|"),,9^9))&"|"&C1:F1&"|"&C2:F6 ),"|" ) )
Ảnh số 05
Bây giờ bạn có thể dễ dàng thêm cột bằng cách thay đổi A2:A6. Tôi nghĩ đến 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 đảo ngược Pivot
Lợi ích chính của việc đảo ngược một bộ dữ liệu là nó mang lại tính linh hoạt cho việc xử lý dữ liệu với các chức năng như SUMIF, QUERY, FILTER, VLOOKUP, vv. 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 Trung Quốc trong các năm 2014-2017. Chúng ta có thể sử dụng SUMIF như dưới đây (dữ liệu theo ảnh số 03).
=sumif(J2:J21,"China",K2:K21)Vui lòng xem ảnh trên 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 đảo ngược pivot, thì SUMIF trên sẽ trả về 0 do không khớ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ả:
Quốc gia tổng Số lượng trong triệu tấn
Ở đây, bạn cảm thấy thoải mái thay thế I1:K21 bằng công thức đảo ngược pivot chính nó.
Đó là tất cả về cách đảo ngược một bộ dữ liệu trong Google Sheets. Cảm ơn vì đã ở lại. Chúc bạn vui vẻ!
Tài liệu ví dụ 300420
