Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Đây là bài đăng thứ hai trong số hai bài đăng có liên quan trình bày cách sử dụng Power Query để xử lý các hàng và danh sách được phân tách. Trong bài đăng đầu tiên, chúng tôi đã kết hợp các hàng thành một danh sách được phân tách. Trong bài đăng này, chúng tôi sẽ làm ngược lại và chuyển đổi một danh sách được phân tách thành các hàng. Chà, chúng ta còn chờ gì nữa… bắt đầu nào

Khách quan

Giả sử bản xuất của chúng tôi chứa một danh sách các giá trị được phân tách, như được hiển thị bên dưới

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

2. Chọn tất cả các ô có chứa văn bản được phân tách bằng dấu phẩy

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng
Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

3. Bây giờ hãy chuyển đến Dữ liệu> Văn bản thành Cột

Quảng cáo

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

4. Chọn Phân tách và nhấp vào Tiếp theo

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

5. Đánh dấu vào tùy chọn Dấu phẩy, sau đó nhấn Kết thúc

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

6. Văn bản được phân tách bằng dấu phẩy đã được chuyển thành hàng

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Như bạn có thể nhận thấy, tính năng này không chỉ giới hạn ở dấu phẩy. Nó cũng có thể chạy từ các dấu phân cách khác, chẳng hạn như tab, dấu chấm phẩy, dấu cách và hầu như bất kỳ thứ gì khác. Tôi hy vọng hướng dẫn này sẽ giúp bạn

Đôi khi, khi làm việc trong Microsoft Excel, chúng ta cần chia các giá trị liên tục được phân tách bằng dấu phẩy của một cột thành các cột hoặc hàng khác nhau. Khi chúng tôi nhập dữ liệu được tạo từ các ứng dụng khác trong excel, tất cả dữ liệu có thể được đặt trong một cột duy nhất; . Ngoài ra, chúng tôi có thể cần trích xuất một phần dữ liệu nhất định dựa trên yêu cầu. Bài viết này sẽ hướng dẫn bạn chia các giá trị được phân tách bằng dấu phẩy thành nhiều cột/hàng bằng một số hàm và tính năng


Mục lục

Tải sách bài tập thực hành

Bạn có thể tải xuống sách bài tập thực hành mà chúng tôi đã sử dụng để chuẩn bị bài viết này

Tách các giá trị được phân tách bằng dấu phẩy thành hàng hoặc cột. xlsm


5 phương pháp để tách các giá trị được phân tách bằng dấu phẩy thành hàng hoặc cột trong Excel

1. Tách dữ liệu thành hàng/cột bằng dấu phẩy bằng tính năng 'Chuyển văn bản thành cột' trong Excel

1. 1. Tách các giá trị thành các cột

Trước hết mình sẽ sử dụng tính năng Text to Columns của excel để chia dữ liệu thành nhiều cột. Đây là phương pháp dễ nhất để tách dữ liệu thành các cột. Giả sử, chúng ta có một tập dữ liệu chứa một số loại trái cây nằm trong một cột, được phân tách bằng dấu phẩy. Mỗi ô có 3 quả. Bây giờ, mình sẽ chia hoa quả của cột B thành 3 cột khác nhau (cột C, D & E)

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Thực hiện theo các bước dưới đây để thực hiện nhiệm vụ

bước

  • Đầu tiên, chọn toàn bộ tập dữ liệu và vào Data > Data Tools > Text to Columns

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Kết quả là Trình hướng dẫn chuyển văn bản thành cột xuất hiện. Bây giờ, từ phần Kiểu dữ liệu gốc, chọn Phân cách và nhấp vào Tiếp theo

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Sau đó, từ phần Dấu phân cách, hãy đánh dấu vào Dấu phẩy và nhấn Tiếp theo

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Sau đó, chọn Vị trí đích (ở đây là Ô C5) và nhấn Kết thúc

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Cuối cùng sau khi đóng Text to Columns Wizard ta sẽ được kết quả như bên dưới. Tất cả dữ liệu được phân tách bằng dấu phẩy được chia thành các cột C, D và E

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Đọc thêm. Cách chia dữ liệu trong một ô Excel thành nhiều cột (5 phương pháp)


1. 2. Chia thành hàng bằng cách sử dụng văn bản thành cột

Bây giờ, tôi sẽ chia các giá trị được phân tách bằng dấu phẩy thành nhiều hàng. Giả sử, tôi có tập dữ liệu bên dưới chứa một số tên trái cây. Trước khi đặt các giá trị này thành nhiều hàng, tôi sẽ chia chúng thành các cột bằng tính năng Chuyển văn bản thành cột

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

bước

  • Đầu tiên chọn Cell B5, vào Data > Text to Columns
  • Sau đó, từ Trình hướng dẫn Văn bản thành Cột, chọn Kiểu dữ liệu gốc. Được phân cách và nhấp vào Tiếp theo
  • Bây giờ chọn loại Dấu phân cách. Dấu phẩy và nhấp vào Tiếp theo
  • Sau đó, chọn ô Đích (ở đây là Ô C5) và nhấn Kết thúc
  • Kết quả là, bạn sẽ nhận được đầu ra bên dưới. Bây giờ, tôi sẽ đặt dữ liệu kết quả thành nhiều hàng. Để làm điều đó sao chép phạm vi C5. G5

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Sau đó, nhấp chuột phải vào Ô B7 và chọn Chuyển vị từ Tùy chọn Dán (xem ảnh chụp màn hình)

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Cuối cùng, chúng tôi sẽ nhận được kết quả mà chúng tôi đang tìm kiếm, tất cả các giá trị được phân tách bằng dấu phẩy được chia thành các hàng từ 7 đến 11

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Đọc thêm. Cách chia nhỏ dữ liệu trong Excel (5 cách)


2. Excel Power Query để phân tách các giá trị được phân tách bằng dấu phẩy thành các cột hoặc hàng

2. 1. Power Query để tách các giá trị thành các cột

Lần này, tôi sẽ sử dụng excel Power Query để chia dữ liệu được phân tách bằng dấu phẩy thành nhiều cột. Thực hiện theo các hướng dẫn dưới đây để thực hiện nhiệm vụ

bước

  • Đầu tiên, đặt con trỏ vào bất kỳ ô nào trong tập dữ liệu hiện có. Sau đó vào Data > From Table/Range (nhóm Get & Transform Data)

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Do đó, Excel sẽ yêu cầu bạn chuyển đổi phạm vi dữ liệu thành bảng. Kiểm tra phạm vi dữ liệu và nhấn OK để tạo bảng

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Kết quả là cửa sổ Power Query Editor xuất hiện với bảng bên dưới. Bây giờ, từ cửa sổ Power Query Editor, hãy chuyển đến Trang chủ > Tách cột > Theo dấu phân cách

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Sau đó hộp thoại Split Column by Delimiter xuất hiện. Chọn Dấu phẩy từ Chọn hoặc nhập dấu phân cách và nhấn OK (xem ảnh chụp màn hình)

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Kết quả là excel chia bảng thành 3 cột như bên dưới. Bây giờ, để đóng Power Query Editor, hãy đi tới Trang chủ > Đóng & Tải > Đóng & Tải

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Cuối cùng, đây là kết quả cuối cùng chúng ta nhận được. Tất cả dữ liệu được phân tách bằng dấu phẩy được chia thành 3 cột của bảng

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Đọc thêm. Cách chia dữ liệu thành nhiều cột trong Excel


2. 2. Chia dữ liệu thành hàng

Ở đây, tôi sẽ chia các giá trị được phân tách bằng dấu phẩy thành nhiều hàng bằng cách sử dụng

bước

  • Chúng tôi có một số loại trái cây được phân tách bằng dấu phẩy trong Ô B5 và C5. Để áp dụng Power Query cho các giá trị này, hãy bấm vào Ô B5 hoặc C5, rồi chuyển đến Dữ liệu > Từ Bảng/Phạm vi

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Tiếp theo hộp thoại Create Table hiện lên, đánh dấu vào dãy bảng và nhấn OK. Do đó, bảng bên dưới sẽ được tạo trong cửa sổ Power Query Editor
  • Sau đó chọn bảng, vào Trang chủ > Tách cột > Theo dấu phân cách

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Bây giờ hộp thoại Chia cột theo dấu phân cách xuất hiện. Từ phần Chọn hoặc nhập dấu phân cách, chọn Dấu phẩy,  chuyển đến Tùy chọn nâng cao và nhấp vào Hàng từ trường. Chia thành. Khi bạn hoàn tất, nhấn OK

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Khi nhấn OK, chúng tôi sẽ nhận được đầu ra bên dưới. Vào Home > Close & Load > Close and Load để hiển thị kết quả trên bảng tính excel

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Cuối cùng, sau đây là kết quả cuối cùng chúng tôi nhận được. Tất cả các giá trị được phân tách bằng dấu phẩy được chia thành các hàng từ 2 đến 7

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng


3. Kết hợp các hàm LEFT, RIGHT, MID, FIND và LEN để tách các giá trị được phân tách bằng dấu phẩy thành các cột

Chúng ta có thể sử dụng các hàm excel để tách dữ liệu được phân tách bằng dấu phẩy thành nhiều cột. Về cơ bản, chúng ta có thể trích xuất các phần dữ liệu trong các cột khác nhau bằng cách sử dụng kết hợp các hàm excel dựa trên dấu phân cách (dấu phẩy, dấu cách, dấu chấm phẩy). Trong phương pháp này, tôi sẽ trích xuất dữ liệu từ 3 vị trí từ một chuỗi văn bản liên tục nằm trong cột B và đặt chúng vào nhiều cột

3. 1. Tìm từ đầu tiên

Ban đầu, tôi sẽ trích xuất từ ​​đầu tiên trong chuỗi văn bản liên tục bằng hàm LEFT và FIND

bước

  • Nhập công thức dưới đây vào Ô C5 và nhấn Enter từ bàn phím

=LEFT(B5,FIND(",", B5)-1)

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Khi nhập công thức, Excel sẽ trả về 'Ngày' là từ đầu tiên của Ô B5

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Ở đây, hàm FIND trả về vị trí của dấu phẩy đầu tiên. Sau đó, hàm LEFT trích từ đầu tiên trước dấu phẩy đầu tiên


3. 2. Trích xuất từ ​​​​thứ 2

Bây giờ, tôi sẽ sử dụng kết hợp các hàm MID và FIND để trích xuất từ ​​thứ hai từ Ô B5

bước

  • Nhập công thức sau vào ô D5 và nhấn Enter

=MID(B5,FIND(",",B5)+1,FIND(",",B5,FIND(",",B5)+1)-FIND(",",B5)-1)

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Kết quả là, công thức trên trả về Grapes;

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Ở đây, hàm MID trả về các ký tự từ giữa chuỗi văn bản của Ô B5. và hàm FIND trả về vị trí của chuỗi thứ 2 trong Ô B5


3. 3. Tìm từ thứ 3

Giả sử, tôi sẽ trích xuất từ ​​thứ 3 từ Ô B5 dựa trên vị trí của dấu phẩy. Trong khi giải nén từ thứ 3, tôi sẽ sử dụng các hàm RIGHT, LEN và FIND

bước

  • Nhập công thức dưới đây vào Ô D5. Sau đó nhấn Enter

=RIGHT(B5, LEN(B5)-FIND(",", B5,FIND(",",B5)+1))

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Khi bạn nhấn Enter, excel sẽ trả về Dưa hấu là từ thứ 3 của dữ liệu liên tục của chúng tôi trong Ô B5

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Ở đây, hàm LEN trả về độ dài của Ô B5. Sau đó, hàm FIND trả về vị trí của dấu phẩy trong Ô B5. Sau đó, tùy thuộc vào kết quả của hàm FIND và LEN, hàm RIGHT sẽ trích xuất từ ​​ngoài cùng bên phải từ Ô B5

  • Vì tôi đã nhận được tất cả các từ thứ 1, thứ 2 và thứ 3 trong các cột khác nhau cho hàng đầu tiên, bây giờ tôi sẽ cố gắng đạt được kết quả tương tự cho các hàng còn lại. Để làm điều đó, hãy chọn phạm vi C5. D5 và sử dụng công cụ Fill Handle (+)

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Cuối cùng, đây là kết quả cuối cùng chúng ta sẽ nhận được

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng


4. Excel VBA để tách các giá trị được phân tách bằng dấu phẩy thành các cột hoặc hàng

4. 1. VBA để chia các giá trị thành các cột

Bạn có thể chia dữ liệu được phân tách bằng dấu phẩy thành nhiều cột bằng mã VBA đơn giản

bước

  • Trước tiên, hãy chuyển đến trang tính mà bạn muốn chia dữ liệu. Tiếp theo click chuột phải vào tên sheet và click View Code để xuất hiện cửa sổ VBA

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Sau đó nhập mã dưới đây vào Mô-đun và Chạy mã bằng cách sử dụng phím F5
Sub SplitData()

Dim Range() As String, Count As Long, x As Variant
For r = 5 To 10
Range = Split(Cells(r, 2), ",")
Count = 3
For Each x In Range
Cells(r, Count) = x
Count = Count + 1
Next x
Next r

End Sub

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

Ở đây 'r' chỉ ra các hàng có chứa dữ liệu. Mặt khác, ‘Count=3‘ biểu thị cột C, là cột đầu tiên hiển thị dữ liệu phân tách

  • Khi bạn chạy mã, dữ liệu được phân tách bằng dấu phẩy trong cột B được chia thành các cột C, D và E như bên dưới

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng


4. 2. Chia các giá trị thành các hàng

Bây giờ tôi sẽ chia các giá trị được phân tách bằng dấu phẩy thành các hàng khác nhau bằng excel VBA. Thực hiện theo các bước dưới đây để thực hiện nhiệm vụ

bước

  • Trước tiên, hãy chuyển đến trang tính nơi bạn có dữ liệu và nhấp chuột phải vào Mã xem

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Kết quả là cửa sổ VBA xuất hiện. Viết mã dưới đây vào Mô-đun và rin mã bằng cách nhấn F5 trên bàn phím
Sub SplitRows()

Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim N As Long
Dim address As String
Dim update As Boolean
Dim ret As Variant
On Error Resume Next
address = Application.ActiveWindow.RangeSelection.address
Set rng = Application.InputBox("Please enter a range", "Input Box", address, , , , , 8)
Set rng = Application.Intersect(rng, rng.Worksheet.UsedRange)
If rng Is Nothing Then Exit Sub
If rng.Columns.Count > 1 Then
MsgBox "Cannot select more than one column"
Exit Sub
End If
Set rng1 = Application.InputBox("Destination Cell", "Input Box", , , , , , 8)
Set rng1 = rng1.Range("A1")
If rng1 Is Nothing Then Exit Sub
update = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each cell In rng
ret = Split(cell.Value, ",")
rng1.Worksheet.Range(rng1.Offset(N, 0), rng1.Offset(N + UBound(ret, 1), 0)) = Application.WorksheetFunction.Transpose(ret)
N = N + UBound(ret, 1) + 1
Next
Application.ScreenUpdating = update

End Sub

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Bây giờ khi bạn chạy mã, hộp nhập bên dưới sẽ xuất hiện, nhập phạm vi dữ liệu bên dưới và nhấn OK

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Kết quả là một hộp nhập liệu khác sẽ xuất hiện. Chèn ô đích vào đó và nhấn OK

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Cuối cùng, chúng ta sẽ nhận được đầu ra dưới đây. Tất cả các giá trị được phân tách bằng dấu phẩy trong tập dữ liệu của chúng tôi được chia thành các hàng từ 8 đến 13

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng


5. Sử dụng Excel Flash Fill để tách các giá trị được phân tách bằng dấu phẩy thành các cột khác nhau

Chúng tôi có thể nhập một phần dữ liệu được phân tách bằng dấu phẩy vào một cột khác và sau đó áp dụng tính năng Flash Fill để lấy phần còn lại của dữ liệu có cùng mẫu

bước

  • Nhập 'Ngày' vào Ô C5. Sau này, khi bạn bắt đầu nhập 'R' vào Ô C6, excel sẽ hiểu rằng tôi muốn trái cây ở vị trí đầu tiên trong tất cả các hàng

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng

  • Chỉ cần nhấn Enter để có kết quả bên dưới. Giờ đây, bạn có thể áp dụng phương pháp tương tự này để chia các giá trị được phân tách bằng dấu phẩy khác thành nhiều cột

Danh sách Excel được phân tách bằng dấu phẩy thành các hàng


Phần kết luận

Trong bài viết trên, tôi đã cố gắng thảo luận một số phương pháp để chia các giá trị được phân tách bằng dấu phẩy thành các hàng hoặc cột trong excel một cách công phu. Hy vọng rằng, những phương pháp và giải thích này sẽ đủ để giải quyết vấn đề của bạn. Vui lòng cho tôi biết nếu bạn có bất kỳ thắc mắc nào

Làm cách nào để chuyển đổi chuỗi Văn bản được phân tách bằng dấu phẩy thành danh sách Hàng trong Excel?

Hãy dùng thử. .
Chọn ô hoặc cột chứa văn bản bạn muốn tách
Chọn Dữ liệu > Văn bản thành Cột
Trong Trình hướng dẫn Chuyển đổi Văn bản thành Cột, chọn Phân cách > Tiếp theo
Chọn Dấu phân cách cho dữ liệu của bạn. .
Chọn Tiếp theo
Chọn Đích trong trang tính của bạn, đây là nơi bạn muốn dữ liệu phân tách xuất hiện

Làm cách nào để chuyển đổi cột thành hàng trong Excel với danh sách được phân tách bằng dấu phẩy?

Câu trả lời của bạn .
Nhập công thức =CONCATENATE(TRANSPOSE(A1. A7)&",") trong một ô trống liền kề với dữ liệu ban đầu của danh sách, ví dụ: ô C1. .
Nhấn phím F9 để tô sáng TRANSPOSE(A1. A7)&"," trong công thức
Xóa dấu ngoặc nhọn và nhấn phím Enter khỏi công thức