Cách thực hiện Unpivot trong Google sheets bằng công thức

Unpivot trong Google Sheets là thao tác chuyển một bảng dữ liệu được sắp xếp theo chiều ngang thành một bảng theo chiều dọc.

Giả sử chúng ta có một bản như bên dưới

Chúng ta có thể chuyển nó sang chiều dọc như sau:

Trông có vẻ đơn giản. Nhưng thật sự, để có thể chuyển đổi định dạng bảng dữ liệu từ chiều ngang sang chiều dọc không dễ dàng, thậm chí là tương đối khó với nhiều công thức phức tạp.

Trong bài biết này, chúng tôi sẽ hướng dẫn bạn thực hiện chuyển đổi định dạng bảng từ chiều ngang sang chiều dọc bằng một công thức, tương đối khó. Tuy nhiên sau khi thực hiện thành công thao tác này, bạn sẽ có thêm một cái nhìn khác về Google Sheets và chúng tôi tin chắc, bạn sẽ học được thêm những điều mới mẻ và thú vị.

Unpivot trong Google Sheets - Giải pháp 1

Chúng ta sẽ tạo bảng dữ liệu định dạng theo phương ngang như thể hiện trong hình ảnh đầu tiên ở phía trên của bài viết này, trong Sheet1 của Google sheets.

Hãy nhớ rằng, những gì chúng ta đang cố gắng làm ở đây là chuyển đổi bảng dữ liệu theo chiều ngang thành bảng dữ liệu theo chiều đọc. Đầu ra của các công thức sẽ giống như hình ảnh thứ 2 ở trên.

Nói cách khác, chúng ta cần tạo 16 hàng để tính đến các cặp khác nhau của  Customer và Product, chẳng hạn như Customer 1 + Product 1, Customer 1 + Product 2,... cứ như thế tiếp tục cho Customer 4 + Product 4.

Để bạn có thể theo dõi bài viết dễ dàng hơn, chúng tôi đã đính kèm file dữ liệu mẫu với tất cả các công thức và phương án thực hiện được đề cập trong bài viết này, ngay bên dưới.

Cột Customer

Để bắt đầu, bạn hãy tạo sheet 2 thêm một hàng tiêu đề đơn giản ở hàng 1, với “Customer”, “Product” và “Value” trong các ô A1, B1 và ​​C1 tương ứng.

Hãy tạo một công thức để điền vào cột Customer. Trong ô A2, hãy nhập:

=COUNTA(Sheet1!$1:$1)

Công thức này cho kết quả là số lượng cột - 4 - trong tập dữ liệu rộng của chúng ta (như hình ảnh đầu tiên của bài đăng này). Tương tự, công thức tiếp theo này sẽ cung cấp số lượng hàng - 3 - trong tập dữ liệu rộng.

=COUNTA(Sheet1!$A:$A)

Nhân hai giá trị này với nhau sẽ cho chúng ta số giá trị trong bảng - 12 - tương ứng với số hàng chúng ta cần trong bảng dữ liệu cao mới của mình

=COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1)

Vậy nên, chúng ta hãy tạo 12 hàng mới này. Kết hợp với hàm SEQUENCE , bắt đầu từ 1:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

Bây giờ chia nó cho số hàng:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)

Câu trả lời là 0,3333333 nhưng chúng ta đã mất 12 hàng…  Vì vậy hãy biến nó thành một Công thức Mảng để thay đổi kết quả một chút.

=ArrayFormula(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))

Hãy làm tròn tất cả các số thập phân đó đến số nguyên gần nhất, như sau:

=ArrayFormula(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)))

Bây giờ chúng ta có vectơ cột 1,1,1,2,2,2,3,3,3,4,4,4 với các vị trí lặp lại, đó chính là những gì chúng ta cần.

Bạn hãy để nguyên công thức này và hãy chuyển sang ô B2 để xây dựng phần tiếp theo. Hãy tạo một bảng các tiêu đề cột mà chúng tôi có thể "tra cứu" với các vị trí lặp lại đó. 

Hãy bắt đầu với công thức này trong B2:

=ArrayFormula(COLUMN(Sheet1!$1:$1))

Hãy thử công thức này trong B3:

=ArrayFormula(Sheet1!$1:$1)

Kết hợp chúng trong ô B2 như sau:

=ArrayFormula({COLUMN(Sheet1!$1:$1);Sheet1!$1:$1})

Và xóa công thức trong ô B3.

Đầu ra trông giống nhau, nhưng nó được tạo bằng một công thức duy nhất.

Bây giờ chúng ta có thể sử dụng hàm HLOOKUP để tra cứu các vị trí đó trong mảng dữ liệu mà chúng ta đã tạo.

Thay đổi công thức của chúng tôi trong ô A2 thành:

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)),{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

Vì chúng ta chưa tính tới ô trống A1 trong công thức này, nên kết quả vẫn chưa hoàn hảo lắm. Chúng ta sẽ thay đổi công thức một chút, vì các vị trí lập lại của chúng ta thật sự bắt đầu từ ô thứ 2, nên ta cần cộng thêm 1 vào công thức này, như bên dưới

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))m

Như vậy, chúng ta vừa hoàn thiện xong column 1 Customers, bước tiếp theo là thiết lập column 2, côt Products

Cột Product

Y tưởng ở đây cũng tương tự như trong cột Customer, nhưng nó có vẻ khác một chút vì chúng ta sẽ thực hiện tra cứu theo chiều dọc.

Vì vậy, đầu tiên hãy xóa các ô B2 và B3.

Bắt đầu với công thức SEQUENCE này trong ô B2:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

Lần này chúng ta muốn một chuỗi giống như 1,2,3,1,2,3,1,2,3,... tức là lặp lại.

=MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A))

Sau đó, hãy chuyển nó thành một công thức mảng

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A)))

Kết quả có được là 1,2,0,1,2,0,... Chúng ta cần sửa thứ tự bằng cách trừ đi 1

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A)))

Bây giờ, kết quả trả về là 0,1,2,0,1,2,...

Thêm 2 vào ô này để có được các vị trí lặp lại như chúng ta muốn 2,3,4,2,3,4 (một lần nữa, chúng ta bắt đầu từ 2 vì có ô trống trong A1 của tập dữ liệu ban đầu của chúng ta).

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2)

Bước tiếp theo là bắt đầu một công thức mới trong ô C2. Xây dựng một mảng để tra cứu theo chiều dọc với công thức này.

=ArrayFormula({ROW(Sheet1!$A:$A),Sheet1!$A:$A})

Chúng ta có thể sử dụng hàm VLOOKUP để kết hợp công thức trên vào công thức trong ô B2, 

=ArrayFormula(VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2))

Bây giờ, cột Products đã kết nối hoàn hảo với cột Customers.

Cột Value

Rất may điều này đơn giản hơn nhiều, sử dụng cấu trúc INDEX / MATCH / MATCH chuẩn để tra cứu từng cặp.

Độ lệch hàng trong hàm INDEX được tìm thấy bằng cách đối sánh sản phẩm với các danh mục sản phẩm trong cột A của dữ liệu ban đầu của chúng tôi, tức là

=MATCH(B2,Sheet1!$A:$A,0)

Độ lệch cột được tìm thấy bằng cách khớp với khách hàng, tức là

=MATCH(A2,Sheet1!$1:$1,0)

Đưa cả hai vào hàm INDEX:

=INDEX(Sheet1!$1:$1000,MATCH(B2,Sheet1!$A:$A,0),MATCH(A2,Sheet1!$1:$1,0))

Kéo công thức này xuống cột để điền vào tất cả các hàng.

Unpivot trong Google Sheets - Giải pháp 2

Bỏ qua các cột công thức mảng Customer và Product, chúng ta hãy tập trung hoàn toàn vào cột Value. Chúng ta đã để lại giải pháp 1 với công thức INDEX / MATCH / MATCH và chúng ta phải kéo xuống công thức xuống các cột, bởi vì nó không phải là một công thức mảng.

Nếu bạn không thích cách làm việc thủ công như thế, hãy cùng viết một công thức mảng khác để thay thế. Hãy nghĩ về hàm VLOOKUP tiêu chuẩn:

=VLOOKUP( search_key, data, column_index, false )

Là search_key mảng lặp lại 2,3,4,2,3,4,2,3,4, ... được tạo bằng cách sử dụng cấu trúc công thức giống như phần đầu tiên của công thức Product từ Giải pháp 1.

Là column_index mảng lặp lại 2,2,2,3,3,3,4,4,4,... được tạo bằng cách sử dụng cấu trúc công thức giống như phần đầu tiên của công thức Customer từ Giải pháp 1.

Khi kết hợp chúng vào VLOOKUP, bạn đang tìm kiếm 2 và trả về cột 2, sau đó tìm kiếm 3 trả về cột 2, tìm kiếm 4 trả về cột 2, sau đó tìm kiếm 2 trả về cột 3,...

Nói cách khác, duyệt qua mảng các giá trị và lấy từng giá trị một.

Dữ liệu cần được thiết lập bằng cách thêm cột tìm kiếm ở phía trước, cột này được thực hiện bằng cách sử dụng cấu trúc theo nghĩa đen của mảng dấu ngoặc nhọn, như sau:

=ArrayFormula({ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000})

VLOOKUP, bạn đang tìm kiếm 2 và trả về cột 2, sau đó tìm kiếm 3 trả về cột 2, tìm kiếm 4 trả về cột 2, sau đó tìm kiếm 2 trả về cột 3,...

Nói cách khác, duyệt qua mảng các giá trị và lấy từng giá trị một.

Dữ liệu cần được thiết lập bằng cách thêm cột tìm kiếm ở phía trước, cột này được thực hiện bằng cách sử dụng cấu trúc theo nghĩa đen của mảng dấu ngoặc nhọn, như sau:

=ArrayFormula({ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000})

Như vậy, bạn đã hoàn thiện cột Value với một công thức mảng

Unpivot trong Google Sheets - Giải pháp 3

Trong giải pháp này, tất cả những gì chúng ta làm là kết hợp ba cột với nhau thành một công thức mảng khổng lồ, duy nhất. Bắt đầu việc kết hợp cả ba cột

= ArrayFormula({ Customer_Formula, Product_Formula, Values_Formula })

Tiếp theo, chúng ta sẽ bọc nó bằng một hàm QUERY để loại bỏ các giá trị null:

= ArrayFormula( QUERY( { Customer_Formula, Product_Formula, Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" ))

Cấu trúc mảng đầy đủ, với hàng tiêu đề tĩnh được thêm vào, là:

=ArrayFormula( {"Customer","Product","Value";QUERY( { Customer_Formula , Product_Formula , Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" )})

Sau đó, chúng tôi có thể chỉ cần kết hợp các công thức Customer_Formula, Product_Formula và Values_Formula để tạo một công thức hoàn chỉnh duy nhất.

=ArrayFormula({"Customer","Product","Value"; QUERY({HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1,2)-2,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000},ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1)
},"SELECT * WHERE Col3 IS NOT NULL")})

Unpivot trong Google Sheets - Giải pháp 4

Giải pháp 4 này vô cùng thú vị, bạn có nhận ra điều kỳ diệu nào trong công thức ngay bên dưới hay không?

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )), , 500000)), , 500000)),"?"))),"?"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

Những chú cá nhiệt đới đang làm gì trong công thức của chúng ta thế này? Và cả những trái ớt nhỏ nữa?

Như tôi đã nói ở trên, công thức này vô cùng thú vị. Bạn sẽ nhanh chóng khám phá ra tầm quan trọng của những chú cá nhỏ đó ngay bên dưới.

Hãy xem lại tập dữ liệu của chúng ta

Bây giờ, bạn hãy nhập thử công thức này trong ô H1:

=QUERY(A1:E4,"SELECT A",4)

Và đây là kết quả

Công thức này giúp nối nối các giá trị trong cột A thành một chuỗi duy nhất, bởi vì chúng ta đã yêu cầu hàm QUERY coi tất cả 4 hàng là tiêu đề. Tốt hơn nữa, bạn có thể bỏ qua hoàn toàn câu lệnh SELECT, như sau:

=QUERY(A1:E4,,4)

Dẫn đến tất cả các cột được nối với nhau:

Hàm IF trong cùng là (lưu ý value_if_false đối số trống):

=ArrayFormula(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", ))

Trả về kết quả

Đối với mỗi hàng dữ liệu, công thức kết hợp chúng sao cho mỗi ô có sự kết hợp duy nhất giữa sản phẩm, khách hàng và giá trị.

Tiếp theo, chúng tôi chuyển đổi mảng này và tham gia bằng cách sử dụng thủ thuật hàng tiêu đề QUERY thú vị ở trên:

=ArrayFormula(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )),,500000))

Điều này mang lại lỗi #REF! lỗi, với thông báo "Kết quả không được tự động mở rộng, vui lòng chèn thêm cột (699)."

Đầu ra của mảng quá rộng đối với Trang tính hiện tại của chúng ta.

Kết hợp nó với một hàm chuyển vị để sửa lỗi này và lấy tất cả dữ liệu trong một cột duy nhất:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )),,500000)))

Sử dụng hàm QUERY thứ hai với thủ thuật tiêu đề này để kết hợp các giá trị này lại với nhau:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )),,500000)),,500000)))

Bây giờ về cơ bản chúng ta chỉ cần tách nó ra dựa trên ký hiệu cá “?” và ớt “?” mà chúng ta đã sử dụng để phân tách các gói dữ liệu.

Đây là lần tách và chuyển vị đầu tiên:

=ArrayFormula(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )),,500000)),,500000)),"?")))

Bây giờ, dữ liệu của chúng tôi trông giống như thế này, ngày càng gần hơn:

Sử dụng hàm TRIM để khắc phục các vấn đề về khoảng cách không đẹp mắt đó.

Tiếp theo, chia nó một lần nữa với những chú cá nhiệt đới:

=ArrayFormula(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )),,500000)),,500000)),"?"))),"?"))

Chúng ta đã gần đi đến đích. Bước tiếp theo là xóa #VALUE! với hàm IFERROR. Sử dụng  QUERY để sắp xếp lại các hàng và cột theo yêu cầu. OFFSET loại bỏ một hàng trống để hiển thị trong bảng. Công thức bây giờ trông như thế này:

=ArrayFormula(QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )),,500000)),,500000)),"?"))),"?"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1"))

Và đầu ra sẽ như sau:

Bước cuối cùng được mượn từ Giải pháp 3 ở trên, cụ thể là kết hợp hàng tiêu đề tĩnh với các ký tự mảng.

=ArrayFormula({"Customer","Product","Value"; MAIN_FORMULA })

Bây giờ chúng ta có thể chèn công thức của mình vào cấu trúc này, thay cho trình giữ chỗ MAIN_FORMULA:

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"?"&Sheet1!B1:1&"?"&Sheet1!B2:Z&"?", )), , 500000)), , 500000)),"?"))),"?"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)}

Hy vọng qua bài viết này, bạn đã khám phá thêm một cách thú vị để viết những công thức phức tạp cũng như biết cách unpivot một bảng trong Google sheets.

Bên cạnh đó, để không bỏ lỡ những thủ thuật và mẹo tin học văn phòng hữu ích khác. Hãy than gia cùng với chúng tôi ngay hôm nay.