Bạn không cần cài đặt bất kỳ tiện ích hay Apps Script nào để unstack dữ liệu trong Google Sheets. Tất cả những gì bạn cần là sử dụng công thức mảng tùy chỉnh của tôi.
Trước khi bắt đầu unstack dữ liệu theo các nhóm tương ứng trong Google Sheets, hãy để tôi giải thích thuật ngữ ‘unstacking data’.
Ví dụ:
Cột A và B chứa dữ liệu được stack lên nhau. Đây là các quốc gia tham gia giải bóng đá và nhóm mà chúng thuộc về. Cột A chứa tên quốc gia và cột B chứa nhóm mà quốc gia đó thuộc về.
Tôi đã unstack dữ liệu này từ cột D đến K. Bây giờ các quốc gia được đặt vào các nhóm tương ứng của chúng.
Làm cách nào để unstack dữ liệu để nhóm tương tự như trên trong Google Sheets?
Công thức Unstack Dữ liệu theo Nhóm trong Google Sheets
Bạn có thể sử dụng công thức sau để unstack dữ liệu theo nhóm trong Google Sheets.
=transpose(ArrayFormula(trim(sort(split(transpose(query({transpose(unique((B1:B)));ArrayFormula(IF(len(A1:A),IF(transpose(unique(B1:B))=B1:B,"-"&A1:A,""),""))},,1000)),"-")))))Cập nhật: Bây giờ chúng ta có thể sử dụng hàm Bycol cùng với hàm Filter để dễ dàng unstack dữ liệu thành các nhóm. Bạn sẽ tìm thấy công thức đó ở cuối bài viết này.
Trong công thức này, tôi đã xem xét phạm vi dữ liệu stack trong ô A1:B, tức là cột A và B, theo như ảnh chụp trên.
Vì vậy, bạn có thể sử dụng công thức này trong bất kỳ cột nào khác. Ở đây, tôi đang sử dụng công thức này trong ô C1.
Nếu bạn muốn, bạn có thể giới hạn (đóng) phạm vi dữ liệu stack trong công thức này, tức là A1:A đến A1:A20 và B1:B đến B1:B20.
Nhưng tôi đã chọn phạm vi mở vì tôi muốn unstack bất kỳ hàng mới nào vào các nhóm tương ứng mà không cần thay đổi công thức.
Công thức này là sự kết hợp của các chức năng khác nhau trong Google Sheets.
Bạn có thể tham khảo Hướng dẫn về Các chức năng Google Sheets của tôi để tìm các hướng dẫn cho tất cả các chức năng được sử dụng trong công thức trên.
Để bạn có một chút ý tưởng về cách hoạt động của công thức này, hãy để tôi giải thích một số phần trong công thức unstacking này cho bạn.
Bạn có thể bỏ qua những gợi ý dưới đây và sử dụng công thức mà không cần quá lo lắng.
Các bước liên quan
Hãy nhìn vào phần được làm nổi bật màu xanh Cyan trong công thức trên.
Đó là bản chất của công thức của tôi để unstack dữ liệu thành các nhóm tương ứng trong Google Sheets.
Nếu bạn hỏi tôi làm thế nào để Unstack dữ liệu thành Nhóm trong Google Sheets, tôi khuyên bạn sử dụng phần được làm nổi bật ở trên. Đó là đủ!
Chú ý số 1:
Khi bạn kiểm tra một phần của công thức chính, bạn có thể cần sử dụng thêm một công thức mảng.
Điều này là vì có thể có một công thức mảng nằm ở đâu đó trong công thức chính áp dụng cho nhiều phần riêng biệt trong nó.
Áp dụng cho phần được làm nổi bật màu xanh Cyan của chúng tôi.
=ArrayFormula(IF(transpose(unique(B1:B20))=B1:B20,A1:A20,""))Chú ý số 2: Tôi đã sử dụng một phạm vi đóng ở đây vì nó chỉ để kiểm tra.
Công thức đơn giản này đã đủ để unstack dữ liệu trong Google Sheets chưa?
Đúng rồi! Nhưng nó thiếu phần phong cách. Kết quả sẽ như sau.
Công thức này thực hiện unstacking nhưng bỏ qua các hàng (để trống các ô) trong tất cả các cột.
Bạn có thể thấy việc sử dụng Hàm Truy vấn trong công thức chính (được làm nổi bật màu cam).
Hãy để tôi giải thích vai trò của Hàm Truy vấn để unstack dữ liệu thành các nhóm trong Google Sheets.
Tôi chỉ sử dụng phạm vi A1:B8, hai nhóm, cho việc kiểm tra.
=query({transpose(unique((B1:B8)));ArrayFormula(IF(len(A1:A8),IF(transpose(unique(B1:B8))=B1:B8,"-"&A1:A8,""),""))},,1000)Công thức Truy vấn kết hợp dữ liệu được stack trả về bởi phần màu xanh Cyan của công thức như sau.
Kết quả:
Nhóm A – Nga – Ả Rập Xê Út – Ai Cập – Uruguay
Nhóm B – Bồ Đào Nha – Tây Ban Nha – Morocco – Iran
Với sự trợ giúp của các chức năng như Trim, Sort, Split và Transpose, tôi đã sắp xếp lại dữ liệu unstacked và loại bỏ việc bỏ qua các hàng.
Hãy xem cách công thức sắp xếp một tập dữ liệu khác dưới đây.
Trong ví dụ đầu tiên, cả hai cột trong dữ liệu stack đều chứa văn bản. Ở đây, cột thứ hai chứa các số.
Trong các tham chiếu ô D1:F4, bạn có thể thấy rằng sinh viên được nhóm theo xếp hạng của họ.
Unstack Dữ liệu bằng BYCOL và FILTER trong Google Sheets – Mới!
Google đã ra mắt một số chức năng trong Sheets vào cuối năm 2022.
Hầu hết chúng là Lambda Helper Functions. Bycol là một trong số đó.
Chúng ta có thể sử dụng chức năng này với hàm Filter để unstack dữ liệu thành các nhóm trong Google Sheets. Điều này yêu cầu hai bước.
Trong bước đầu tiên, hãy duy nhất hóa cột nhóm bằng công thức sau.
=transpose(unique(B:B))Ở C2 (giả sử Unique trên ở ô C1), chèn công thức sau.
=bycol(C1:J1,lambda(r,filter(A1:A,B1:B=r)))Cả hai công thức trên đều là công thức mảng. Nếu có giá trị trong phạm vi mở rộng nó, nó có thể trả về giá trị lỗi #REF!.
Bảng ví dụ 6223
