Chia Cột Chéo Trong Excel: Kỹ Thuật, Công Thức Và Ứng Dụng Thực Tiễn

Có thể bạn quan tâm: Cách Kiểm Tra Giá Trị Có Tồn Tại Trong Một Dải Ô (range) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Giới thiệu chung về khái niệm “chia cột chéo” trong Excel

Trong môi trường làm việc với dữ liệu, việc sắp xếp, tách và phân bổ thông tin vào các ô một cách hợp lý là yếu tố then chốt giúp tăng hiệu suất và độ chính xác. “Chia cột chéo” (tiếng Anh thường gọi là split column, text to columns hoặc transpose) là một kỹ thuật cho phép người dùng tách một cột dữ liệu thành nhiều cột dựa trên một ký tự phân tách (delimiter) hoặc dựa trên vị trí ký tự cố định. Ngoài ra, trong một số trường hợp “chia cột chéo” còn ám chỉ việc chuyển dữ liệu từ dạng cột sang dạng hàng (hoặc ngược lại) – hay còn gọi là transpose.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ cơ bản đến nâng cao, về cách thực hiện chia cột chéo trong Excel, bao gồm:

• Các phương pháp sử dụng tính năng Text to Columns (Văn bản sang Cột).
• Sử dụng Công thức (FORMULA) như LEFT, RIGHT, MID, FIND, SEARCH, TEXTSPLIT, FILTERXML.
• Sử dụng Power Query (Get & Transform) để tách dữ liệu phức tạp.
• Sử dụng VBA Macro để tự động hoá quy trình chia cột chéo.
• Một số mẹo và lưu ý khi làm việc với dữ liệu đa dạng (ngôn ngữ, ký tự Unicode, dữ liệu thiếu, v.v.).

Mục tiêu cuối cùng là giúp bạn nắm vững mọi công cụ cần thiết để xử lý bất kỳ dạng dữ liệu nào trong Excel một cách nhanh chóng và chính xác.

Có thể bạn quan tâm: Cách Tìm Số Trùng Lặp Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Khi nào cần chia cột chéo? Các ví dụ thực tiễn

2.1. Dữ liệu nhập khẩu từ hệ thống khác

Ví dụ: Bạn nhận được một file CSV (Comma Separated Values) chứa thông tin khách hàng dưới dạng:

Họ,Tên,Email,Điện thoại
Nguyễn,An,an.nguyen@example.com,0123456789
Trần,Thị B,thib.tran@example.com,0987654321

Khi mở file trong Excel, toàn bộ dữ liệu có thể xuất hiện trong một cột duy nhất nếu Excel không tự động nhận dạng dấu phẩy là delimiter. Bạn cần chia cột chéo để tách từng trường vào cột riêng.

2.2. Mã sản phẩm kết hợp thông tin

Một công ty sản xuất có mã sản phẩm dạng ABC-2026-001, trong đó:

• ABC là mã nhóm sản phẩm,
• 2026 là năm sản xuất,
• 001 là số thứ tự.

Bạn muốn tách các phần này ra thành ba cột riêng để dễ dàng lọc và báo cáo.

2.3. Địa chỉ bao gồm các thành phần

Địa chỉ thường có dạng Số nhà, Đường, Quận, Thành phố. Khi dữ liệu nhập vào chỉ có một cột “Địa chỉ”, việc chia cột chéo giúp tách thành các thành phần để phân tích địa lý.

2.4. Chuyển dữ liệu từ cột sang hàng (Transpose)

Trong một bảng thống kê, bạn có 12 tháng nằm ở một cột và muốn chuyển chúng thành hàng để vẽ biểu đồ. Đây là một dạng “chia cột chéo” – chuyển chiều dữ liệu.

Có thể bạn quan tâm: Kiểm Tra Giá Trị True/false Trong Excel: Hướng Dẫn Toàn Diện Và Ứng Dụng Thực Tiễn

3. Sử dụng tính năng Text to Columns (Văn bản sang Cột)

3.1. Các bước thực hiện cơ bản

1. Chọn cột dữ liệu cần tách.
2. Vào Data → Text to Columns (Dữ liệu → Văn bản sang Cột).

3. Chọn Delimited (Dấu phân tách) hoặc Fixed width (Vị trí cố định) tùy theo dữ liệu.

4. Delimited: bạn chỉ định ký tự phân tách như dấu phẩy ,, dấu chấm phẩy ;, dấu cách, tab, hoặc ký tự tự định nghĩa.

5. Fixed width: đặt các đường ngắt vị trí cố định (ví dụ: 3 ký tự đầu là nhóm, 4 ký tự tiếp theo là năm).

6. Nhấn Next, chọn/dễ chỉnh các delimiter hoặc vị trí ngắt.

7. Xác định định dạng dữ liệu cho mỗi cột (General, Text, Date, v.v.).
8. Nhấn Finish để hoàn tất.

3.2. Ví dụ chi tiết: Tách mã sản phẩm ABC-2026-001

• Bước 1: Chọn cột chứa mã.
• Bước 2: Data → Text to Columns → Delimited → Next.
• Bước 3: Tick Other và nhập dấu - vào ô.
• Bước 4: Xem trước kết quả: 3 cột ABC, 2026, 001.
• Bước 5: Đặt định dạng cho cột “Năm” là Date (năm) hoặc General.
• Bước 6: Click Finish.

3.3. Lưu ý khi dùng Text to Columns

4. Tách cột chéo bằng công thức Excel

Khi dữ liệu có độ phức tạp cao, hoặc cần tự động cập nhật khi dữ liệu thay đổi, công thức là giải pháp mạnh mẽ.

4.1. Các hàm cơ bản

• LEFT(text, num_chars): Lấy ký tự bên trái.
• RIGHT(text, num_chars): Lấy ký tự bên phải.
• MID(text, start_num, num_chars): Lấy ký tự từ vị trí giữa.
• FIND(find_text, within_text, start_num): Tìm vị trí xuất hiện đầu tiên của ký tự.
• SEARCH(find_text, within_text, start_num): Tương tự FIND nhưng không phân biệt hoa thường.

4.1.1. Tách mã sản phẩm bằng công thức

Giả sử ô A2 chứa ABC-2026-001.

=LEFT(A2, FIND("-", A2)-1) // Kết quả: ABC
=MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1) - FIND("-", A2) - 1) // 2026
=RIGHT(A2, LEN(A2) - FIND("@", SUBSTITUTE(A2, "-", "@", 2))) // 001

Giải thích:

• FIND("-", A2) trả về vị trí dấu - đầu tiên.
• SUBSTITUTE(A2, "-", "@", 2) thay dấu - thứ hai bằng @ để dùng FIND("@", …) xác định vị trí dấu - thứ hai.

4.2. Hàm TEXTSPLIT (Excel 365/2026)

Excel 365 đã cung cấp hàm mới TEXTSPLIT, cho phép tách chuỗi theo delimiter một cách trực quan.

=TEXTSPLIT(A2, "-")

Kết quả sẽ là một mảng 3 cột: {"ABC","2026","001"}. Khi nhập vào ô B2, Excel sẽ tự động spill (lan truyền) ra các ô bên phải.

Bạn có thể chỉ định row_delimiter và column_delimiter để tách đa chiều:

=TEXTSPLIT(A2, "-", , TRUE) // Tách thành hàng (row) nếu muốn

4.3. Hàm FILTERXML (đối với dữ liệu dạng XML/HTML)

Khi dữ liệu có ký tự đặc biệt hoặc định dạng phức tạp, một cách hay là biến chuỗi thành XML tạm thời, sau đó dùng FILTERXML để trích xuất.

=FILTERXML("<t><s>" & SUBSTITUTE(A2, "-", "</s><s>") & "</s></t>", "//s1") // Lấy phần 1
=FILTERXML("<t><s>" & SUBSTITUTE(A2, "-", "</s><s>") & "</s></t>", "//s2") // Lấy phần 2

4.4. Kết hợp LET và LAMBDA (Excel 365)

Để giảm lặp lại và tăng hiệu suất, bạn có thể định nghĩa một hàm tùy chỉnh bằng LAMBDA:

=LET( txt, A2, delim, "-", parts, TEXTSPLIT(txt, delim), LAMBDA(i, INDEX(parts, i))(1) // Trả về phần thứ 1
)

Bạn có thể lưu hàm LAMBDA dưới tên “SplitPart” trong Formulas → Name Manager để tái sử dụng:

=SplitPart(A2, "-", 2) // Trả về phần thứ 2

5. Sử dụng Power Query (Get & Transform) để chia cột chéo

Power Query là công cụ mạnh mẽ để ETL (Extract, Transform, Load). Khi dữ liệu cần làm sạch, chuẩn hoá, hoặc tách dựa trên nhiều delimiter, Power Query là lựa chọn tối ưu.

Có thể bạn quan tâm: Chi Phí Đám Cưới Excel: Cách Lập Bảng Ngân Sách Hoàn Chỉnh Và Quản Lý Chi Tiêu

5.1. Các bước cơ bản

1. Select cột chứa dữ liệu → Data → From Table/Range.
2. Power Query Editor mở ra.
3. Chọn cột → Home → Split Column → By Delimiter.
4. Chọn delimiter (Comma, Space, Custom, v.v.) và cách tách:
5. Each occurrence of the delimiter (Mỗi lần xuất hiện).
6. At the left-most delimiter (Ở lần đầu).
7. At the right-most delimiter (Ở lần cuối).
8. Xác nhận, các cột mới sẽ xuất hiện.
9. Nếu cần, sử dụng Transform → Extract → Text Before/After Delimiter để lấy phần trước hoặc sau một delimiter cụ thể.
10. Khi hoàn tất, nhấn Close & Load để đưa dữ liệu vào worksheet.

5.2. Tách dữ liệu dựa trên nhiều delimiter

Nếu dữ liệu có nhiều ký tự phân tách (ví dụ: ABC|2026/001), bạn có thể:

• Split by delimiter lần đầu với |.
• Sau đó, trên cột mới, Split by delimiter với /.

Hoặc sử dụng Advanced Editor để viết M code:

let Source = Excel.CurrentWorkbook(){Name="Table1"}Content, Split1 = Table.SplitColumn(Source, "Mã", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Mã_Groups","Mã_Rest"}), Split2 = Table.SplitColumn(Split1, "Mã_Rest", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Năm","STT"})
in Split2

5.3. Xử lý dữ liệu thiếu và lỗi

Power Query cung cấp các công cụ:

• Remove Errors: Loại bỏ hàng có lỗi.
• Replace Values: Thay thế giá trị rỗng bằng một ký tự placeholder.
• Fill Down/Up: Điền dữ liệu thiếu dựa trên giá trị trên/dưới.

5.4. Lưu ý khi dùng Power Query

6. Tự động hoá bằng VBA Macro

Khi công việc tách cột chéo phải thực hiện hàng ngày hoặc trên nhiều file, viết macro là cách tiết kiệm thời gian.

6.1. Macro cơ bản: Tách dấu gạch ngang -

Sub SplitColumnByHyphen() Dim rng As Range, cell As Range Dim arr() As String Dim i As Long, lastCol As Long ' Chọn cột muốn tách (ví dụ cột A) Set rng = Application.InputBox("Chọn cột cần tách:", Type:=8) For Each cell In rng.Cells If Len(cell.Value) > 0 Then arr = Split(cell.Value, "-") For i = LBound(arr) To UBound(arr) cell.Offset(0, i).Value = arr(i) Next i End If Next cell
End Sub

Giải thích: Macro yêu cầu người dùng chọn vùng dữ liệu, sau đó dùng hàm Split để tách theo ký tự - và ghi các phần vào các cột kế bên.

6.2. Macro nâng cao: Dùng TextToColumns trong VBA

Sub VBA_TextToColumns() Dim ws As Worksheet Set ws = ActiveSheet With ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) .TextToColumns Destination:=.Cells(1, 1), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=False, _ Space:=False, _ Other:=True, _ OtherChar:="-" End With
End Sub

Thay vì tự viết vòng lặp, chúng ta gọi trực tiếp tính năng TextToColumns qua VBA, giúp xử lý nhanh hơn và giữ nguyên các tùy chọn định dạng.

6.3. Đặt macro vào nút (Button)

1. Insert → Shapes → Button (vẽ nút).
2. Click chuột phải → Assign Macro → Chọn macro đã viết.
3. Lưu file dưới định dạng .xlsm để giữ macro.

6.4. Lưu ý bảo mật

• Khi chia sẻ file macro, người nhận cần Enable macros để chạy.
• Đánh dấu macro là Digital Signature nếu muốn tăng độ tin cậy.

7. Các trường hợp đặc biệt và giải pháp

7.1. Dữ liệu có ký tự Unicode (tiếng Việt, tiếng Trung…)

• Problem: Text to Columns đôi khi không nhận diện đúng dấu cách khi có dấu accent.
• Solution: Dùng TRIM, CLEAN trong công thức hoặc Power Query → Transform → Clean để loại bỏ ký tự không hiển thị.

7.2. Địa chỉ email có dấu “@” và dấu “.”

Khi tách email thành phần tên người dùng và miền, bạn có thể dùng Two-step delimiter:

1. Split by “@” → cột 1 = tên người dùng, cột 2 = miền.
2. Split cột 2 bằng “.” → tách thành tên miền và phần mở rộng.

7.3. Dữ liệu không đồng nhất (có một số dòng thiếu delimiter)

• Power Query: Khi dùng Split Column, Power Query sẽ tạo các cột trống cho những dòng thiếu. Bạn có thể dùng Fill Down hoặc Replace Values để xử lý.
• Công thức: Kết hợp IFERROR để tránh lỗi #VALUE!.

=IFERROR(LEFT(A2, FIND("-", A2)-1), A2) // Nếu không có dấu -, trả về toàn bộ chuỗi

7.4. Định dạng ngày tháng không đồng nhất

Khi ngày tháng được ghi dưới dạng dd/mm/yyyy và mm-dd-yyyy, bạn cần:

1. Text to Columns → tách thành ngày, tháng, năm.
2. Sử dụng DATE để tạo lại ngày chuẩn:

=DATE(RIGHT(C2,4), MID(C2,4,2), LEFT(C2,2))

8. So sánh các phương pháp và lựa chọn tối ưu

Khi nào nên dùng gì?

• Dữ liệu đơn giản, một lần tách → Text to Columns nhanh nhất.
• Cần công thức để tự động cập nhật → dùng TEXTSPLIT (nếu có) hoặc kết hợp LEFT/MID/FIND.
• Dữ liệu phức tạp, nhiều delimiter, cần làm sạch → Power Query là lựa chọn tốt nhất.
• Công việc lặp lại hàng ngày, áp dụng trên nhiều file → VBA Macro tiết kiệm thời gian.

9. Thực hành: Bài tập tổng hợp

Bài 1: Tách danh sách sản phẩm

Yêu cầu: Tạo 3 cột Mã nhóm, Năm, STT bằng công thức.

Giải pháp:

• Mã nhóm (B2): =LEFT(A2, FIND("-", A2)-1)
• Năm (C2): =MID(A2, FIND("-", A2)+1, 4)
• STT (D2): =RIGHT(A2, LEN(A2) - FIND("@", SUBSTITUTE(A2, "-", "@", 2)))

Kéo công thức xuống các dòng còn lại.

Bài 2: Tách địa chỉ thành Số nhà, Đường, Quận, Thành phố bằng Power Query.

1. Chọn bảng → Data → From Table/Range.
2. Split Column → By Delimiter → , → Each occurrence.
3. Đổi tên các cột tương ứng.
4. Close & Load.

Bài 3: Tự động tách email thành Tên người dùng và Tên miền bằng VBA.

Sub SplitEmail() Dim rng As Range, cell As Range, arr() As String Set rng = Application.InputBox("Chọn cột email:", Type:=8) For Each cell In rng If InStr(cell.Value, "@") > 0 Then arr = Split(cell.Value, "@") cell.Offset(0, 1).Value = arr(0) 'Tên người dùng cell.Offset(0, 2).Value = arr(1) 'Tên miền End If Next cell
End Sub

10. Kết luận

“Chia cột chéo” trong Excel không chỉ là một thao tác cơ bản mà còn là nền tảng để làm sạch, chuẩn hoá và khai thác dữ liệu một cách hiệu quả. Từ các kỹ thuật nhanh gọn như Text to Columns, đến các công thức linh hoạt như LEFT, MID, TEXTSPLIT, cho tới các công cụ mạnh mẽ Power Query và VBA, mỗi phương pháp đều có ưu và nhược điểm riêng, phù hợp với từng tình huống thực tiễn.

Hãy lựa chọn công cụ phù hợp với khối lượng dữ liệu, độ phức tạp và tần suất thực hiện. Khi áp dụng đúng, bạn sẽ:

• Giảm thiểu lỗi nhập liệu.
• Tăng tốc độ xử lý và báo cáo.
• Đảm bảo dữ liệu chuẩn hoá, sẵn sàng cho các bước phân tích tiếp theo (pivot, biểu đồ, mô hình dữ liệu).

Hy vọng bài viết đã cung cấp cho bạn một bộ công cụ toàn diện để chia cột chéo trong Excel một cách chuyên nghiệp. Chúc bạn thành công trong mọi dự án dữ liệu!

Tiến sĩ Lê Thống Nhất

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ẻ.