Có thể bạn quan tâm: Bảng Lưu Chuyển Tiền Tệ Theo Phương Pháp Gián Tiếp: Cách Tạo Định Dạng Excel Chi Tiết Và Tối Ưu
Giới thiệu chung
Trong công việc quản lý dữ liệu, chúng ta thường gặp tình huống cần phân tách một cột dữ liệu lớn thành nhiều cột nhỏ hơn. Ví dụ, một cột chứa tên đầy đủ có thể cần tách thành cột tên, tên đệm và họ; hoặc một cột chứa địa chỉ có thể cần chia thành thành phố, tỉnh, địa chỉ chi tiết. Việc chuyển đổi này giúp cho dữ liệu trở nên rõ ràng, dễ phân tích và khai thác hơn.
Mặc dù Excel cung cấp nhiều phương pháp thực hiện việc chuyển cột thành nhiều cột, nhưng nhiều người vẫn chưa biết cách.Syntax kết hợp giữa công thức, tính năng “Text to Columns”, Power Query, và thậm chí VBA để đạt được mục tiêu một cách nhanh chóng và chính xác. Bài viết này sẽ hướng dẫn chi tiết từng phương pháp, từ những retaining cơ bản nhất tới những kỹ thuật nâng cao, giúp bạn linh hoạt lựa chọn công cụ phù hợp với nhu cầu thực tế.
Có thể bạn quan tâm: Có Thể Mở File Numbers Trong Excel Không?
1. Công cụ “Text to Columns” – Giải pháp nhanh nhất, dễ sử dụng
1.1 Khi nào nên dùng “Text to Columns”?
- Dữ liệu trong cột được phân cách bởi ký tự cố định (dấu phẩy, dấu chấm phẩy, dấu cách, dấu tab, v.v.).
- Bạn chỉ cần tách dữ liệu một lần, không cần tự động cập nhật khi dữ liệu thay đổi.
- Mức độ phức tạp của dữ liệu découvrez là vừa phải.
1.2 Các bước thực hiện
| Bước | Mô tả |
|---|---|
| 1 | Chọn cột cần tách, hoặc phạm vi dữ liệu. |
| 2 | Vào menu Data → Text to Columns. |
| 3 | Chọn Delimited nếu dữ liệu có ký tự phân cách, hoặc Fixed width nếu dữ liệu có độ rộng cột cố định. |
| 4 | Nhấn Next. |
| 5 | Chọn ký tự phân cách (ví dụ: ,, ;, Tab, Space), hoặc nhập ký tự tùy chỉnh. |
| 6 | Nhấn Next. |
| 7 | Chọn kiểu dữ liệu cột (General, Text, Date, Do not import). |
| 8 | Nhấn Finish. |
1.3 Mẹo thực tế
- Bảo vệ dữ liệu gốc: Trước khi thực hiện, sao chép cột gốc vào một sheet khác để tránh mất dữ liệu.
- Sử dụng “Preview”: Trước khi nhấn
Finish, xem trước kết quả để tránh lỗi phân tách. - Định dạng dữ liệu: Nếu dữ liệu cần được giữ nguyên định dạng (ví dụ, số điện thoại), chọn
Texttrong bước 7.
Có thể bạn quan tâm: Call To Undefined Method Maatwebsite Excel Load: Hướng Dẫn Giải Quyết Và Phân Tích Chi Tiết
2. Công thức TEXTSPLIT (Excel 365 & 2026) – Đơn giản, linh hoạt
2.1 Giới thiệu về TEXTSPLIT
TEXTSPLIT là một hàm mới trong Excel 365/2026, cho phép bạn chia một chuỗi thành một mảng nhiều phần tử dựa trên dấu phân cách. Định dạng:
=TEXTSPLIT(text, col_delimiter, row_delimiter, ignore_empty, match_mode, pad_with)
Trong đó:
– text: Chuỗi cần tách.
– col_delimiter: Ký tự phân cách cột.
– row_delimiter: Ký tự phân cách hàng (có thể bỏ qua nếu không cần).
– ignore_empty: Đặt TRUE để bỏ qua các phần tử rỗng.
– match_mode: Kiểu so sánh (0: chính xác, -1: trùng lắp).
– pad_with: Giá trị để điền vào ô trống.
2.2 Ví dụ thực tiễn
sources: 1. Tách tên đầy đủ thành họ, tên.
A1: "Nguyễn Văn A"
Công thức:
=TEXTSPLIT(A1, " ")
Kết quả: Ba cột – “Nguyễn”, “Văn”, “A”.
- Tách địa chỉ thành thành phố, tỉnh, chi tiết.
B1: "Hà Nội, Hà Nam, 123 Đường ABC"
Công thức:
=TEXTSPLIT(B1, ", ", , TRUE)
Kết quả: Ba cột – “Hà Nội”, “Hà Nam”, “123 Đường ABC”.
2.3 Ưu điểm và hạn chế
- Ưu điểm: Không cần chuyển đổi dữ liệu qua các bước thủ công, dễ dàng kết hợp với các hàm khác.
- Hạn chế: Chỉ có sẵn trong phiên bản Excel mới; nếu sử dụng cũ, cần dùng phương pháp khác.
3. Công thức mảng nhị phân (ARRAYFORMULA) – Phù hợp cho Excel không hỗ trợ TEXTSPLIT
Có thể bạn quan tâm: Kiểm Tra Xem Một Ô Có Chứa Văn Bản Cụ Thể Trong Excel Hay Không
3.1 Sử dụng FIND, MID, LEN, SUBSTITUTE
Khi Excel không có TEXTSPLIT, bạn có thể dùng công thức mảng để tách dữ liệu. Ví dụ: tách chuỗi “A,B,C,D” thành 4 cột.
“width: 100%|
=TRIM(MID(SUBSTITUTE($A1,”,”,REPT(” “,LEN($A1))), (COLUMN(A:A)-1)LEN($A1)+1, LEN($A1)))
Công thức này:
- `SUBSTITUTE` thay dấu phẩy bằng một dãy khoảng trắng dài.
- `MID` trích xuất từng phần tử.
- `TRIM` loại bỏ khoảng trắng thừa.
- `COLUMN(A:A)` tạo ra một dãy số 1,2,3,... để xác định vị trí cột. ### 3.2 Mở rộng cho nhiều ký tự phân asal Nếu ký tự phân cách là “;” hoặc “|”, thay dấu phẩy trong công thức bằng ký tự mong muốn. ### 3.3 Lưu ý - Công thức này làm mảng nên cần nhấn `Ctrl+Shift+Enter` (trước khi Excel 365) để xác nhận.
- Khi có nhiều dữ liệu, công thức có thể làm chậm Excel; nên copy giá trị ra cột mới và dán lại dưới dạng giá trị. ## 4. Power Query – Phương pháp tối ưu cho dữ liệu lớn và phức tạp ### 4.1 Power Query là gì? Power Query là một công cụ trong Excel cho phép xuất nhập, biến đổi dữ liệu một cách mạnh mẽ và trực quan. Với Power Query, bạn có thể tách cột, chuyển đổi dữ liệu, ghép dữ liệu, và nhiều thao tác phức tạp mà không cần viết mã. ### 4.2 Các bước thực hiện | Bước | Mô tả |
|------|-------|
| 1 | Chọn dữ liệu → қур `Data` → `From Table/Range`. |
| 2 | Trong cửa sổ Power Query Editor, chọn cột cần tách. |
| 3 | Vào tab `Transform` → `Split Column` → `By Delimiter`. |
| 4 | Chọn ký tự phân cách, tùy chọn `Split into Columns` hoặc `Split into Rows`. |
| 5 | Nhấn `OK`. |
| 6 | Khi hoàn tất, nhấn `Close & Load` để đưa dữ liệu trở lại Excel. | ### 4.3 Ưu điểm - Tự động cập nhật: Khi dữ liệu gốc thay đổi, bạn chỉ cần click `Refresh`.
- Xử lý dữ liệu lớn: Hiệu suất cao hơn so với công thức mảng.
- Tích hợp tính năng nâng cao: Tìm kiếm, lọc, nhóm, pivot, v.v. ### 4.4 Các lưu ý khi dùng Power Query - Định dạng dữ liệu: Power Query thường giữ nguyên kiểu dữ liệu, nhưng nếu muốn chuyển đổi kiểu, bạn cần chọn `Data Type`.
- Định dạng Excel: Khi lưu dữ liệu từ Power Query, bạn phải lưu dưới dạng bảng (Table). Điều này giúp giữ tính năng tự động cập nhật. ## 5. VBA – Tự động hoá quy trình tách cộtgreens ### 5.1 Khi nào nên dùng VBA? - Quy trình lặp đi lặp lại: Bạn cần thực hiện tách cột nhiều lần trên các file khác nhau.
- Tùy biến cao: Bạn muốn kiểm soát chi tiết hơn (ví dụ: tách thành cột tùy theo nội dung riêng biệt).
- Tích hợp vào workflow: Khi kết hợp với macro khác, hoặc khi xuất dữ liệu tự động. ### 5.2 Mẫu macro cơ bản ```vb
Sub SplitColumnIntoMultiple() Dim ws As Worksheet Dim txt As String Dim arr() As String Dim i As Long Set ws = ThisWorkbook.Sheets("Sheet1") ' Giả sử dữ liệu ở cột A, bắt đầu từ A2 For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row txt = ws.Cells(i, "A").Value arr = Split(txt, ",") ' Thay "," bằng ký tự phân cách mong muốn ' Ghi vào các cột B, C, D,... Dim j As Long For j = LBound(arr) To UBound(arr) ws.Cells(i, j + 2).Value = Trim(arr(j)) Next j Next i
End Sub
- Giải thích:
Splitlà hàm nhúng trong VBA để tách chuỗi.- Mảng
arrlưu các phần tử sau khi tách. - Vòng lặp
Forghi dữ liệu vào các cột tiếp theo.
5.3 Mở rộng macro
Bạn có thể:
– Điều chỉnh ký tự phân cách: Sử dụng biến delimiter.
– Định dạng dữ liệu: Chèn dữ liệu vào cột mới và áp dụng định dạng.
– Tự động tạo sheet: Tạo sheet mới cho dữ liệu đã tách.
5.4 Lưu ý khi dùng VBA
- Bảo mật: Macro Excel có thể bị chặn bởi cài đặt bảo mật. Hãy bật macro hoặc điều chỉnh cài đặt.
- Kiểm tra lỗi: Thêm
On Error Resume Nextvà kiểm tra lỗi để tránh macro bị dừng đột ngột.
6. Kỹ thuật nâng cao: Tách dữ liệu phức tạp với biểu thức tùy chỉnh
6.1 Tách dữ liệu có số lần xuất hiện thay đổi
Ví dụ: cột chứa chuỗi “A-B-C” hoặc “A-B” hoặc “A”. Để tách, bạn có thể kết hợp LEN, SUBSTITUTE, FIND.
Function SplitByDash(cell As Range) As Variant Dim txt As String txt = cell.Value Dim arr() As String arr = Split(txt, "-") SplitByDash = arr
End Function
Sau đó, trong Excel, bạn có thể dùng =INDEX(SplitByDash(A1),1) để lấy phần tử đầu tiên, =INDEX(SplitByDash(A1),2) để lấy phần tử thứ hai, v.v.
6.2 Tách dữ liệu theo mẫu (Regex)
Nếu Excel hỗ trợ regex (hoặc qua VBA), bạn có thể viết macro tách dữ liệu theo mẫu phức tạp.
Function RegexSplit(text As String, pattern As String) As Variant Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True re.Pattern = pattern Dim matches As Object Set matches = re.Execute(text) Dim arr() As String ReDim arr(matches.Count - 1) Dim i As Integer For i = 0 To matches.Count - 1 arr(i) = matches(i).Value Next i RegexSplit = arr
End Function
ತ್ಯ``` Ví dụ: `=RegexSplit(A1, "A-Za-z0-9+")` sẽ tách toàn bộ từ có chứa ký tự chữ và số. ## 7. Thực hành: Tách dữ liệu thực tế ### 7.1 Dữ liệu mẫu | ID | FullName | Address | Phone |
|----|----------|---------|-------|
| 1 | Nguyễn Văn A | Hà Nội, Hà Nam, ეჟ 123 Đường ABC | 0123-456-789 |
| 2 | Trần Thị B | TP. Hồ Chí Minh, 456 Đường XYZ | 0987-654-321 | ### <Employee> Bước 1: Tách FullName thành Họ, Tên đệm, Tên Sử dụng “Text to Columns”:
1. Chọn cột `FullName`.
2. Text to Columns → Delimited → Space → Finish. Kết quả: Ba cột `Họ`, `Tên đệm`, `Tên`. ### 7.2 Bước 2: Tách Address thành Thành phố, Tỉnh, Chi tiết Sử dụng Power Query:
1. Chọn cột `Address` → Data → From Table/Range.
2 avanço. Trong Power Query, Split Column → By Delimiter → `,` → Split into Columns.
3. Đặt lại tên cột: `Thành phố`, `Tỉnh`, `Chi tiết`.
4. Close & Load. ### 7.3 Bước 3: Tách Phone thành Mã vùng, Số điện thoại Sử dụng công thức:
```excel
=LEFT(A2,FIND("-",A2)-1) // Mã vùng
=MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2)) // Số điện thoại
Nếu có nhiều_ctrl dấu -, bạn có thể dùng TEXTSPLIT.
8. So sánh các phương pháp
| Phương pháp | Khi nào nên dùng | Ưu điểm | Hạn chế |
|---|---|---|---|
| Text to Columns | Dữ liệu cố định, nhanh chóng | Dễ dùng, không cần công thức | Không tự động cập nhật |
| TEXTSPLIT | Excel 365/2026 | Đơn giản, linh hoạt | Hạn chế phiên bản |
| Công thức mảng | Excel cũ | Tự động, nhanh | Có thể làm chậm |
| Power Query | Dữ liệu lớn, phức tạp | Tự động, mạnh mẽ | Cần học kỹ thuật |
| VBA | Quá trình lặp, tùy biến | Tự động hoá, linh hoạt | Cần lập trình |
| Regex | Mẫu phức tạp | Tính chính xác cao | Cần viết macro |
9. Lời khuyên tổng quát
- Bảo vệ dữ liệu: Luôn sao lưu trước khi thao tác.
- Kiểm tra dữ liệu: Sau khi tách, kiểm tra xem có dữ liệu lỗi, trống không.
- Tự động hóaTrees: Nếu tách thường xuyên, cân nhắc dùng Power Query hoặc తీవ VBA.
- Sử dụng công cụ phù hợp: Đừng áp dụng Power Query cho dữ liệu rất nhỏ; không cần thiết.
10. Kết luận
Việc chuyển một cột thành nhiều cột trong Excel khôngcriminator là một kỹ năng cơ bản nhưng vô cùng quan trọng. Bằng cách hiểu rõ các phương pháp khác nhau – từ “Text to Columns” đơn giản, TEXTSPLIT hiện đại, công thức mảng, Power Query mạnh mẽ, cho tới VBA linh hoạt – bạn có thể chọn công cụ phù hợp nhất với dữ liệu và quy trình làm việc của mìnhбон. Hãy thử áp dụng từng bước trong bài viết trên, điều chỉnh cho phù hợp với dữ liệu thực tế của bạn, và bạn sẽ thấy công việc quản lý dữ liệu trở nên nhanh chóng, chính xác và hiệu quả hơn rất nhiều.
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.