Unpivot trong Google Sheets là kỹ thuật chuyển đổi dữ liệu từ dạng bảng ngang sang dạng bảng dọc. Thao tác này tưởng chừng đơn giản nhưng lại khá phức tạp và đòi hỏi sự am hiểu về các công thức trong Google Sheets. Bài viết này sẽ hướng dẫn bạn chi tiết cách unpivot dữ liệu với nhiều giải pháp khác nhau, từ đơn giản đến phức tạp, giúp bạn dễ dàng áp dụng và xử lý dữ liệu hiệu quả.
Bạn đã bao giờ gặp khó khăn khi cần phân tích dữ liệu được sắp xếp theo chiều ngang, khiến việc theo dõi và so sánh trở nên phức tạp? Việc unpivot dữ liệu sẽ giúp bạn giải quyết vấn đề này, biến dữ liệu từ dạng khó hiểu sang dạng dễ phân tích hơn. Hãy cùng ProSkills khám phá các phương pháp unpivot dữ liệu trong Google Sheets nhé!
Unpivot Dữ Liệu – Giải Pháp 1: Từng Bước Chi Tiết
Giả sử bạn có bảng dữ liệu khách hàng và sản phẩm như hình dưới:
Mục tiêu là chuyển đổi bảng dữ liệu này sang dạng dọc như sau:
Chúng ta sẽ thực hiện unpivot trên Sheet2, bắt đầu bằng việc tạo tiêu đề cột “Customer”, “Product” và “Value” ở hàng 1 (A1, B1, C1).
Tạo Cột “Customer”
- Đếm số cột và số hàng: Sử dụng
COUNTAđể đếm số cột (trừ cột A) và số hàng (trừ hàng 1) trong Sheet1. - Tạo dãy số: Dùng
SEQUENCEđể tạo dãy số từ 1 đến tổng số ô dữ liệu (số hàng * số cột). - Làm tròn lên: Sử dụng
ROUNDUPvà chia cho số hàng để có được số khách hàng lặp lại. - Tra cứu khách hàng: Sử dụng
HLOOKUPđể tra cứu tên khách hàng tương ứng với số thứ tự. Công thức cuối cùng cho cột Customer (A2) sẽ là:=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))
Tạo Cột “Product”
- Tạo dãy số: Sử dụng
SEQUENCEvàMODđể tạo dãy số sản phẩm lặp lại. - Điều chỉnh vị trí: Cộng thêm 2 để khớp với vị trí sản phẩm trong Sheet1.
- Tra cứu sản phẩm: Sử dụng
VLOOKUPđể tra cứu tên sản phẩm tương ứng. Công thức cho cột Product (B2) sẽ là:=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))
Tạo Cột “Value”
Sử dụng INDEX, MATCH để tra cứu giá trị tương ứng với khách hàng và sản phẩm. Công thức cho cột Value (C2) sẽ là:
=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 để áp dụng cho tất cả các hàng.
Unpivot Dữ Liệu – Giải Pháp 2: Sử dụng VLOOKUP và Công Thức Mảng
Giải pháp này tập trung vào việc tối ưu cột “Value” bằng công thức mảng và VLOOKUP. Chúng ta sẽ sử dụng mảng lặp lại cho search_key và column_index trong hàm VLOOKUP. Dữ liệu được thiết lập bằng cách thêm cột tìm kiếm phía trước. Công thức cho cột “Value” (C2) sẽ là:
=ArrayFormula(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))Unpivot Dữ Liệu – Giải Pháp 3: Công Thức Mảng Hoàn Chỉnh
Giải pháp này kết hợp cả ba cột “Customer”, “Product”, “Value” thành một công thức mảng duy nhất. Sử dụng QUERY để loại bỏ giá trị null. Công thức hoàn chỉnh sẽ là:
=ArrayFormula({"Customer","Product","Value"; QUERY({HLOOKUP(...),VLOOKUP(...),VLOOKUP(...)},"SELECT * WHERE Col3 IS NOT NULL")})(Thay thế “…” bằng các công thức tương ứng từ Giải pháp 1 và 2)
Unpivot Dữ Liệu – Giải Pháp 4: Kỹ Thuật Nối Chuỗi và Tách Chuỗi
Đây là giải pháp nâng cao sử dụng kỹ thuật nối chuỗi với ký tự đặc biệt và sau đó tách chuỗi để tạo bảng unpivot. Công thức hoàn chỉnh sẽ là:
=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)})Kết Luận
Bài viết đã hướng dẫn bạn 4 cách unpivot dữ liệu trong Google Sheets, từ cơ bản đến nâng cao. Tùy vào nhu cầu và mức độ phức tạp của dữ liệu, bạn có thể lựa chọn phương pháp phù hợp. Hy vọng những kiến thức này sẽ giúp bạn xử lý dữ liệu hiệu quả hơn. Nếu bạn có bất kỳ câu hỏi nào, hãy để lại bình luận bên dưới, ProSkills sẽ hỗ trợ bạn!
FAQs về Unpivot trong Google Sheets
1. Unpivot dữ liệu là gì?
Unpivot dữ liệu là quá trình chuyển đổi dữ liệu từ định dạng bảng rộng (wide format) sang định dạng bảng dài (long format). Trong bảng rộng, các giá trị thuộc tính được thể hiện thành các cột riêng biệt, trong khi ở bảng dài, các giá trị thuộc tính được gom lại thành một cột duy nhất, và một cột khác sẽ chứa tên thuộc tính tương ứng.
2. Tại sao cần unpivot dữ liệu?
Unpivot dữ liệu giúp đơn giản hóa việc phân tích và trực quan hóa dữ liệu, đặc biệt là khi làm việc với các công cụ biểu đồ hoặc phân tích thống kê. Định dạng bảng dài thường được yêu cầu bởi nhiều công cụ phân tích dữ liệu.
3. Khi nào nên sử dụng công thức mảng trong unpivot?
Công thức mảng giúp unpivot dữ liệu một cách tự động và hiệu quả, đặc biệt khi xử lý một lượng lớn dữ liệu. Nó giúp tránh việc phải kéo công thức xuống thủ công cho từng hàng.
4. Làm thế nào để chọn ký tự phân tách trong Giải pháp 4?
Bạn có thể chọn bất kỳ ký tự nào không xuất hiện trong dữ liệu gốc của bạn làm ký tự phân tách. Ký tự “?” được sử dụng trong ví dụ chỉ là một lựa chọn, bạn có thể thay thế bằng ký tự khác nếu cần.
5. Có cách nào khác để unpivot dữ liệu ngoài các giải pháp đã nêu không?
Ngoài các giải pháp sử dụng công thức, Google Sheets cũng cung cấp tính năng “Unpivot” trong menu “Data” (Dữ liệu) giúp bạn thực hiện thao tác này một cách trực quan. Tuy nhiên, việc sử dụng công thức vẫn mang lại sự linh hoạt và kiểm soát tốt hơn đối với quá trình unpivot.
