Bài viết này sẽ giúp bạn hiểu cách phân chia đúng các giá trị phân cách bằng dấu phẩy trong một bảng đa cột trong Google Sheets.
Google Sheets cung cấp hai tùy chọn tích hợp để phân chia giá trị trong một cột dựa trên các dấu phân tách.
- Sử dụng hàm Split.
- Sử dụng lệnh ‘Split text to columns’ trong menu Data.
Phương pháp đầu tiên sử dụng công thức, và việc tuân theo nó mang lại lợi ích của việc sử dụng công thức.
Ví dụ, chúng ta có thể sử dụng hàm Transpose với Split để thay đổi định dạng dữ liệu sau khi phân chia.
Để phân chia các giá trị phân cách bằng dấu phẩy trong một bảng đa cột, chúng ta sẽ sử dụng hàm Split trong Google Sheets.
Giả sử chúng ta muốn gán cùng một điểm số cho nhiều người. Trong trường hợp đó, chúng ta có thể phân tách tên bằng dấu phẩy trong một ô và điểm số trong một ô khác.
Ví dụ, điểm số của các ứng viên A, B và C là 95. Điểm số của ứng viên D là 94. Chúng ta có thể nhập dữ liệu của họ theo hai cách.
Loại 1:
Ô tên: A, B, C
Ô điểm số: 95
Loại 2:
Ô tên: A, B, C, D
Ô điểm số: 95, 94
Loại 2 có lợi ích chính là chúng ta có thể tập hợp dữ liệu loại 2 bằng cách sử dụng câu lệnh Query vì định dạng nó giống như một bảng cơ sở dữ liệu.
Hơn nữa, trong tập dữ liệu như vậy, chúng ta có thể sử dụng hàm Sumif hoặc hàm Lookup một cách thuận tiện.
Đối với quan điểm của tôi, một trong những lợi ích của dữ liệu loại 1 là nó tiết kiệm một số hàng. Vì vậy, khi in dữ liệu, chúng ta có thể sử dụng ít giấy hơn.
Giả sử nhân viên nhập dữ liệu của chúng ta đã nhập dữ liệu theo kiểu loại 1. Chúng ta có thể định dạng nó thành loại 2 được không?
Đúng! Hãy bắt đầu với những kiến thức cơ bản để phân chia các giá trị phân cách bằng dấu phẩy trong Google Sheets.
Phân Chia Các Giá Trị Phân Cách Bằng Dấu Phẩy Trên Nhiều Dòng (Cơ Bản)
Nếu chúng ta chỉ có một cột duy nhất, ví dụ, cột đầu tiên trong loại 1, chúng ta có thể sử dụng các hàm Textjoin, Split và Trim như một công thức mảng như sau.
Công thức A:
=ArrayFormula(transpose(trim(split(TEXTJOIN(",",true,A1:A3),","))))
Dưới đây là giải thích cho công thức này:
- Hàm Textjoin: Kết hợp tất cả các giá trị trong cột A1:A3 thành một chuỗi. Chúng ta nên kiểm tra dấu phân cách hiện có trong cột A và sử dụng chúng để kết hợp các giá trị.
- Hàm Split: Phân chia các giá trị. Kết quả sẽ nằm trong một hàng duy nhất.
- Hàm Trim: Loại bỏ khoảng trắng. Ví dụ, xem giá trị trong ô A1. Có một khoảng trắng sau mỗi dấu phẩy. Khi công thức phân chia, giá trị đó vẫn còn tồn tại với các giá trị phân chia.
- Hàm Transpose: Thay đổi hướng của các giá trị từ hàng thành cột.
Phân Chia Các Giá Trị Phân Cách Bằng Dấu Phẩy Trong Một Bảng Đa Cột
Đây là chủ đề chính của bài hướng dẫn này. Hãy tìm hiểu nó và công thức thay thế (có hai giải pháp) chi tiết.
Bảng Hai Cột
Tôi sẽ bắt đầu với một bảng hai cột.
Tôi sẽ cố gắng giải thích nó chi tiết. Điều đó sẽ giúp bạn thay đổi công thức để phù hợp với hơn hai cột trong bảng.
Dữ liệu mẫu để phân chia nằm ở ô A2:B6. Nhưng tôi sẽ sử dụng một phạm vi lớn A2:B1000 trong công thức.
Tìm công thức dưới đây trong ô D2.
Công thức B:
=Query(ArrayFormula(split(trim(flatten(split(A2:A1000,",")&"🐠"&B2:B1000)),"🐠")),"Select * where Col2 is not null")
Kết quả trên là một ví dụ về phân chia các giá trị phân cách bằng dấu phẩy trong một bảng đa cột trong Google Sheets.
Một số bạn có thể đọc và hiểu công thức. Đối với những người muốn tìm hiểu từng bước, hãy đọc giải thích công thức dưới đây.
Các Bước
Trong các bước, tôi sẽ sử dụng phạm vi thực sự A2:B6, không phải A2:B1000. Điều đó sẽ giúp bạn dễ dàng theo dõi các bước.
-
Công thức dưới đây trước tiên phân chia các giá trị trong A2:A6 và sau đó thêm (kết hợp) các giá trị trong B2:B6 với tất cả các giá trị đã phân chia.
Để các giá trị trong cả hai cột riêng biệt, tôi đã sử dụng dấu phân tách là dấu cá (fish delimiter).
=ArrayFormula(split(A2:A6,",")&"🐠"&B2:B6)Tôi đã sử dụng ký tự 🐠 làm dấu phân tách để làm cho công thức thú vị hơn. Bạn có thể sử dụng bất kỳ dấu phân tách nào bạn thích, chẳng hạn như ký tự chống đứng trên bàn phím.
-
Có một hàm ‘chính thức’ khá mới trong Google Sheets gọi là Flatten.
Bằng cách bao đóng công thức trên bằng Flatten, chúng ta có thể biến nhiều giá trị cột thành một cột duy nhất như dưới đây.
=ArrayFormula(FLATTEN(split(A2:A6,",")&"🐠"&B2:B6)) -
Bằng cách sử dụng hàm Trim, chúng ta có thể loại bỏ dấu cách không mong muốn trong các giá trị.
=ArrayFormula(trim(FLATTEN(split(A2:A6,",")&"🐠"&B2:B6))) -
Hãy phân chia đầu ra này lần nữa. Lần này, dấu phân tách là ký tự “cá”.
=ArrayFormula(split(trim(FLATTEN(split(A2:A6,",")&"🐠"&B2:B6)),"🐠")) -
Hãy lọc chỉ các hàng trong đầu ra mà cột 2 không có giá trị. Đối với điều đó, chúng ta có thể sử dụng hàm Query.
Đây là công thức cuối cùng của tôi. Tìm cùng một công thức (công thức B) ngay dưới tiêu đề “Split Các Giá Trị Trong Một Bảng Đa Cột Trong Google Sheets”.
Bảng Ba Cột Trở Lên
Tôi có một bảng ba cột. Điều đó có nghĩa là lần này có một cột bổ sung.
Vì vậy, chúng ta cần thực hiện hai thay đổi cho công thức. Những thay đổi đó nằm ở bước thứ hai và bước thứ năm.
Dưới đây là công thức và kết quả tương ứng.
Công thức C:
=Query(ArrayFormula(split(trim(flatten(split(A2:A1000,",")&"🐠"&B2:B1000&"🐠"&C2:C1000)),"🐠")),"Select * where Col3 is not null")
Với Các thay đổi?
Trong bước thứ hai, tôi đã thêm phần &”🐠”&C2:C1000 để bao gồm cột thứ ba.
Nếu bạn có một cột nữa, lặp lại phần này và thay đổi tham chiếu cột từ C2:C1000 thành D2:D1000.
Trong bước thứ năm, thay vì Được chọn nơi Col2 không null, chúng tôi sử dụng Được chọn nơi Col3 không null.
Nếu bạn có một cột thứ tư trong bảng của bạn, thay thế phần này bằng Được chọn * nơi Col4 không null.
Điều này, chúng ta có thể phân chia và tổ chức dữ liệu trong Google Sheets.
Công Thức Thay Thế Khác Để Phân Chia Các Giá Trị Phân Cách Bằng Dấu Phẩy Trong Một Bảng Đa Cột
Ở đầu, tôi đã đề cập rằng Flatten là một hàm mới. Điều đó không có nghĩa là không có giải pháp nào trong Google Sheets để phân chia các giá trị phân cách bằng dấu phẩy trong một bảng đa cột trong Google Sheets.
Chúng ta có thể thay thế Công thức B ở trên bằng công thức dưới đây mà không liên quan đến Flatten.
=ArrayFormula(split(transpose(trim(split(textjoin("🌼",1,if(len(A2:A1000),REGEXREPLACE(A2:A1000&",",",","🌼"&B2:B1000&","),)),","))),"🌼"))
Còn công thức C?
Dưới đây là công thức thay thế của nó.
=ArrayFormula(split(transpose(trim(split(textjoin("🌼",1,if(len(A2:A1000),REGEXREPLACE(A2:A1000&",",",","🌼"&B2:B1000&"🌼"&C2:C&","),)),","))),"🌼"))
Nếu bạn đang học Google Sheets, thì có thể bạn muốn một giải thích cho các công thức trên.
Bạn có thể tìm thấy nó ở đây – Công Thức Phân Chia Và Phân Loại – Google Sheets.
Cảm ơn vì đã đọc bài viết. Chúc bạn thú vị!
