Trong quá trình làm việc với Microsoft Excel, việc xử lý dữ liệu lớn, đặc biệt là khi cần tách nội dung từ một ô thành nhiều cột khác nhau, thường là một thách thức. Thao tác thủ công không chỉ tốn thời gian mà còn dễ dẫn đến sai sót, gây ảnh hưởng đến tính chính xác của báo cáo và phân tích. Với vai trò là một chuyên gia tại webthuthuat.net, chúng tôi hiểu rõ tầm quan trọng của việc tối ưu hóa quy trình làm việc và tự động hóa các tác vụ lặp đi lặp lại. May mắn thay, Excel cung cấp nhiều công cụ và hàm mạnh mẽ, từ các tính năng tích hợp sẵn đến các hàm linh hoạt, giúp bạn thực hiện công việc này một cách hiệu quả, nhanh chóng và chính xác.
Bài viết này sẽ đi sâu vào bốn phương pháp hàng đầu để tách dữ liệu trong Excel thành nhiều cột, bao gồm sử dụng công cụ Text to Columns, tính năng Flash Fill tự động, các hàm Excel tích hợp như TEXTSPLIT, TEXTBEFORE, TEXTAFTER, LEFT, MID, RIGHT, và cuối cùng là công cụ Power Query Editor mạnh mẽ. Mỗi phương pháp sẽ được trình bày chi tiết với ví dụ minh họa cụ thể, giúp bạn dễ dàng áp dụng vào công việc thực tế của mình. Hãy cùng khám phá và biến Excel trở thành trợ thủ đắc lực trong công việc quản lý dữ liệu của bạn.
1. Sử Dụng Công Cụ Text to Columns – Hướng Dẫn Từng Bước
Công cụ Text to Columns (Văn bản thành Cột) là một tính năng tích hợp sẵn trong Excel, được thiết kế để tách dữ liệu từ một cột thành nhiều cột dựa trên một dấu phân cách cụ thể hoặc theo chiều rộng cố định. Phương pháp này rất hữu ích nếu bạn muốn một giao diện trực quan, từng bước hướng dẫn qua quy trình.
Ví dụ, giả sử bạn có một danh sách họ tên đầy đủ trong Cột A và muốn tách chúng thành Họ (Cột B) và Tên (Cột C).
Bảng Excel có cột Họ tên đầy đủ và hai cột trống để tách Họ và Tên
Để thực hiện điều này, trước tiên, hãy chọn các ô chứa dữ liệu bạn muốn tách trong Cột A. Sau đó, trên thanh công cụ ribbon, truy cập tab Data (Dữ liệu) và nhấp vào Text to Columns (Văn bản thành Cột).
Lựa chọn công cụ Text to Columns trong tab Data của Excel
Hộp thoại Convert Text to Columns Wizard (Trình hướng dẫn Chuyển đổi Văn bản thành Cột) sẽ xuất hiện. Tại bước đầu tiên, chọn Delimited (Phân cách) và nhấp Next (Tiếp theo). Tùy chọn này cho phép bạn xác định một ký tự, ký hiệu, hoặc khoảng trắng làm dấu phân tách giữa các phần dữ liệu.
Chọn tùy chọn Delimited trong hộp thoại Convert Text to Columns Wizard của Excel
Trong ví dụ này, tên trong Cột A được phân tách bằng dấu phẩy và một khoảng trắng. Do đó, bạn cần chọn cả hai tùy chọn Comma (Dấu phẩy) và Space (Khoảng trắng) trong phần Delimiters (Dấu phân cách). Bạn có thể xem trước cách dữ liệu của mình sẽ được tách ở phần Preview (Xem trước) phía dưới hộp thoại. Nếu đã hài lòng, nhấp Next (Tiếp theo).
Tiếp theo, hãy xóa bất kỳ thông tin nào trong trường Destination (Ô đích), sau đó chọn ô mà bạn muốn dữ liệu đã tách được đặt vào – trong trường hợp này là ô B2 – và nhấp Finish (Hoàn tất).
Chọn ô B2 làm đích đến cho dữ liệu đã tách trong Convert Text to Columns Wizard
Nếu bạn muốn thay thế dữ liệu gốc bằng phiên bản đã tách, hãy chọn ô trên cùng bên trái của dữ liệu gốc trong trường Destination. Trong ví dụ này, đó sẽ là ô A2.
Bây giờ, họ và tên đầy đủ đã được tách thành họ và tên riêng trong các cột tương ứng. Nếu cần, bạn có thể xóa dữ liệu gốc trong Cột A, vì các tên đã tách không còn liên kết với dữ liệu gốc nữa.
Kết quả tách họ tên trong Excel, với Họ ở cột B và Tên ở cột C
Tuy nhiên, trong một số trường hợp, chẳng hạn như có người có hai tên hoặc hai họ, việc chọn dấu phân cách là khoảng trắng có thể tách các từ kép này ra thành các cột riêng biệt, dù bạn muốn giữ chúng lại với nhau.
Ví dụ về tên kép trong Excel bị tách không mong muốn khi sử dụng khoảng trắng làm dấu phân cách
Để khắc phục vấn đề này, chỉ chọn dấu phân cách là Comma (Dấu phẩy) – không chọn khoảng trắng – trong Convert Text to Columns Wizard.
Chỉ chọn Comma làm dấu phân cách trong Convert Text to Columns Wizard để xử lý tên kép
Sau khi quá trình tách hoàn tất, bạn sẽ nhận thấy rằng các tên riêng ở Cột C bị thừa một khoảng trắng ở đầu, vì bạn không yêu cầu Excel xem khoảng trắng là dấu phân cách.
Tên riêng ở cột C bị thừa khoảng trắng ở đầu sau khi tách dữ liệu trong Excel
Để loại bỏ các khoảng trắng thừa này, hãy mở rộng bảng thêm một cột về phía phải để tạo một cột mới cho tên đã được sửa. Sau đó, tại ô D2, nhập công thức:
=TRIM([@[First name]])Trong đó, hàm TRIM loại bỏ tất cả các khoảng trắng thừa từ một chuỗi văn bản (ngoại trừ các khoảng trắng duy nhất giữa các từ), và [@[First name]] là một tham chiếu có cấu trúc đến cột “First names” (Tên riêng) trong bảng.
Sử dụng hàm TRIM để loại bỏ khoảng trắng thừa từ cột Tên riêng trong Excel
Khi bạn nhấn Enter, các tên riêng sẽ xuất hiện trong Cột D mà không còn khoảng trắng thừa ở đầu. Ngoài ra, vì dữ liệu đang ở trong một bảng Excel đã định dạng, công thức sẽ tự động được sao chép xuống các ô còn lại trong cột.
Cột D hiển thị tên riêng đã được làm sạch khoảng trắng bằng hàm TRIM trong Excel
Tiếp theo, chọn các tên riêng vừa được tạo trong Cột D và nhấn Ctrl+C để sao chép. Sau đó, nhấn Ctrl+Alt+V để mở hộp thoại Paste Special (Dán Đặc biệt), rồi nhấn V để chọn “Values” (Giá trị), và cuối cùng nhấn Enter. Bước này giúp chuyển kết quả công thức thành giá trị cố định.
Chọn "Values" trong hộp thoại Paste Special của Excel để dán giá trị
Cuối cùng, xóa cột chứa khoảng trắng ở đầu (C) và cột gốc chứa họ tên đầy đủ (A) để hoàn thiện bảng dữ liệu của bạn.
Bảng Excel cuối cùng với Họ ở cột A và Tên ở cột B sau khi đã tách và làm sạch dữ liệu
2. Tách Dữ Liệu Nhanh Chóng Với Flash Fill Tự Động
Microsoft Excel có nhiều công cụ giúp tự động hóa các quy trình nhàm chán, và Flash Fill là một trong những tính năng hữu ích nhất, đặc biệt khi bạn cần tách dữ liệu theo một mẫu cụ thể. Nếu việc sử dụng Text to Columns Wizard tốn quá nhiều thời gian cho các tác vụ đơn giản, Flash Fill sẽ giúp tăng tốc đáng kể. Về bản chất, đây là một cách thông minh hơn để sao chép và dán dữ liệu vào các ô mới.
Đầu tiên, hãy chọn ô mà bạn muốn đặt phần tử dữ liệu đầu tiên đã được tách (trong trường hợp này là ô B2), nhập giá trị mong muốn (ví dụ: Smith), và nhấn Enter. Flash Fill sẽ học theo mẫu bạn vừa nhập.
Nhập tên "Smith" vào ô đầu tiên của cột Họ trong bảng Excel
Tiếp theo, trong tab Data (Dữ liệu) trên ribbon, nhấp vào Flash Fill. Nếu bạn là người yêu thích các phím tắt của Microsoft Excel, bạn có thể nhanh chóng sử dụng tổ hợp phím Ctrl+E.
Lựa chọn Flash Fill trong tab Data của Excel
Khi bạn chạy Flash Fill, Excel sẽ tìm kiếm các mẫu trong dữ liệu của bạn và cố gắng áp dụng chúng cho các dữ liệu còn lại trong phạm vi. Trong ví dụ này, nó đã thành công trong việc điền các họ còn lại từ dữ liệu gốc xuống Cột B.
Cột Họ được hoàn thành tự động bằng tính năng Flash Fill trong Excel
Sau đó, lặp lại quy trình tương tự cho các tên riêng trong Cột C – nhập giá trị đầu tiên để thiết lập mẫu, nhấn Enter, sau đó nhấn Ctrl+E.
Cột Tên riêng được hoàn thành tự động bằng tính năng Flash Fill trong Excel
Flash Fill hoạt động tốt nhất trên các tập dữ liệu nhỏ, nơi bạn có thể nhanh chóng kiểm tra xem công cụ đã xác định đúng các mẫu trong dữ liệu của bạn hay chưa. Tuy nhiên, nếu bạn làm việc với hàng trăm hoặc hàng nghìn dòng dữ liệu, hãy sử dụng một công cụ đáng tin cậy hơn – như các hàm tách văn bản của Excel – mà không yêu cầu bạn phải xác minh kết quả một cách chặt chẽ.
3. Khai Thác Sức Mạnh Của Các Hàm Excel Tích Hợp
Một cách khác để tách dữ liệu thành nhiều cột là sử dụng một số hàm tích hợp sẵn của Microsoft Excel. Khi chọn phương pháp này, hãy nhớ rằng các giá trị đã tách sẽ được liên kết với các giá trị gốc. Điều này có nghĩa là bạn cần sao chép (Ctrl+C) và dán chúng dưới dạng giá trị (Ctrl+Alt+V > V) nếu bạn muốn xóa dữ liệu gốc.
3.1. Hàm TEXTSPLIT – Chia Chuỗi Toàn Diện
Hàm TEXTSPLIT của Excel chia tất cả nội dung trong một ô thành các cột khác nhau theo một dấu phân cách mà bạn chỉ định, và trả về kết quả dưới dạng một mảng tràn (spilled array).
Lưu ý rằng, các hàm mảng động (dynamic array functions) tạo ra mảng tràn giúp tiết kiệm thời gian bằng cách trả về nhiều kết quả từ một công thức. Tuy nhiên, chúng không tương thích với các bảng Excel đã được định dạng. Điều này có nghĩa là bạn chỉ có thể sử dụng TEXTSPLIT trong các phạm vi thông thường, không phải trong một Table.
Trong ví dụ này, giả sử bạn muốn tách các thủ đô và tiểu bang trong Cột A thành Cột B và Cột C chỉ bằng một công thức duy nhất.
Danh sách các thủ đô và tiểu bang ở cột A, và các cột trống để tách dữ liệu bằng hàm TEXTSPLIT
Để thực hiện điều này bằng hàm TEXTSPLIT, tại ô B1, bạn nhập công thức:
=TEXTSPLIT(A2,", ")Trong đó, A2 là ô chứa văn bản bạn muốn tách, và sự kết hợp của ", " (dấu phẩy + khoảng trắng) được đặt trong dấu ngoặc kép cho Excel biết rằng đây là dấu phân cách.
Sử dụng hàm TEXTSPLIT để tách thủ đô và tiểu bang vào các cột riêng biệt trong Excel
Khi bạn nhấn Enter và chọn ô vừa nhập công thức, bạn sẽ thấy một đường viền màu xanh lam bao quanh kết quả. Điều này có nghĩa là mặc dù bạn chỉ nhập công thức vào ô B2, kết quả đã “tràn” sang ô C2.
Mảng tràn (spilled array) được thể hiện bằng đường viền xanh lam quanh các ô bị ảnh hưởng trong Excel
Vì dữ liệu không được định dạng dưới dạng bảng Excel, bạn cần điền phần còn lại của cột theo cách thủ công. Để làm điều này, hãy nhấp và kéo Fill Handle (ô vuông nhỏ ở góc dưới bên phải ô B2) xuống cuối phạm vi dữ liệu.
Kéo Fill Handle để sao chép công thức TEXTSPLIT xuống các ô còn lại trong phạm vi dữ liệu Excel
3.2. Hàm TEXTBEFORE và TEXTAFTER – Kiểm Soát Chính Xác
Trong khi TEXTSPLIT chia tất cả nội dung của một ô, các hàm TEXTBEFORE và TEXTAFTER cho phép bạn chọn xem bạn muốn trích xuất thông tin trước hay sau một dấu phân cách. Ngoài ra, vì chúng không phải là các hàm mảng động, bạn có thể sử dụng chúng với dữ liệu được định dạng dưới dạng bảng.
Sử dụng cùng ví dụ trên về thủ đô và tiểu bang, bắt đầu với thủ đô, tại ô B2, nhập công thức:
=TEXTBEFORE([@[Capital and State]],", ")Trong đó, [@[Capital and State]] là một tham chiếu có cấu trúc đến cột chứa các ô bạn muốn tách, và sự kết hợp ", " bên trong dấu ngoặc kép cho Excel biết rằng dấu phẩy theo sau bởi một khoảng trắng nên được coi là dấu phân cách. Lần này, chỉ văn bản trước dấu phân cách được trả về trong kết quả khi bạn nhấn Enter.
Sử dụng hàm TEXTBEFORE để trích xuất tên thủ đô vào cột B trong Excel
Để trích xuất tên tiểu bang tại ô C2, bạn nhập công thức:
=TEXTAFTER([@[Capital and State]],", ")và nhấn Enter.
Sử dụng hàm TEXTAFTER để trích xuất tên tiểu bang vào cột C trong Excel
Bên cạnh khả năng tương thích với các bảng Excel, một lợi ích của việc sử dụng TEXTBEFORE và TEXTAFTER thay vì TEXTSPLIT là kết quả không cần phải nằm trong các cột liền kề. Điều này mang lại sự linh hoạt cao hơn trong việc sắp xếp dữ liệu.
3.3. Hàm LEFT, MID, và RIGHT – Tách Theo Vị Trí Ký Tự
Các hàm LEFT, MID, và RIGHT của Excel cho phép bạn tách nội dung của một ô tại các vị trí cụ thể trong chuỗi ký tự. Điều này đặc biệt tiện dụng nếu bạn có một tập dữ liệu với các giá trị có cấu trúc đồng nhất, ví dụ như số sê-ri hoặc mã sản phẩm.
Trong ví dụ này, ba ký tự đầu tiên đại diện cho quốc gia, ba ký tự cuối cùng đại diện cho ID, và X hoặc Y ở giữa đại diện cho xếp hạng. Mục tiêu của bạn là tách ba yếu tố này thành các cột B, C và D tương ứng.
Danh sách mã sản phẩm ở cột A và các cột trống để tách các yếu tố mã
Đầu tiên, để trích xuất các quốc gia, tại ô B2, nhập công thức:
=LEFT([@Code],3)Trong đó, LEFT([@Code] yêu cầu Excel đọc các ô trong cột Code từ bên trái, và số 3 cho Excel biết rằng bạn muốn trích xuất ba ký tự đầu tiên.
Trích xuất mã quốc gia từ chuỗi mã bằng hàm LEFT trong Excel
Bây giờ, tại ô D2, hãy làm theo nguyên tắc tương tự, nhưng sử dụng hàm RIGHT để trích xuất ba ký tự cuối cùng:
=RIGHT([@Code],3)
Trích xuất ID từ chuỗi mã bằng hàm RIGHT trong Excel
Cuối cùng, tại ô C2, bạn muốn trích xuất ký tự ở giữa, là ký tự thứ tư trong chuỗi. Để làm điều này, nhập công thức:
=MID([@Code],4,1)Trong đó, số 4 yêu cầu Excel bắt đầu trích xuất từ ký tự thứ tư, và số 1 cho Excel biết rằng bạn chỉ muốn trích xuất một ký tự duy nhất.
Trích xuất xếp hạng từ chuỗi mã bằng hàm MID trong Excel
Bây giờ, bạn đã thành công trong việc tách dữ liệu trong Cột A thành ba cột riêng biệt.
4. Chuyển Đổi Dữ Liệu Mạnh Mẽ Bằng Power Query Editor
Nhiều người cho rằng Power Query Editor của Excel quá phức tạp để sử dụng – tuy nhiên, nó được thiết kế đặc biệt để thân thiện với người dùng và là một cách tuyệt vời để tách dữ liệu thành nhiều cột, đặc biệt là với các tập dữ liệu lớn hoặc có cấu trúc phức tạp hơn.
Trong ví dụ này, giả sử bạn có một bảng Excel chứa danh sách các đội bóng bầu dục NFL (National Football League) và bạn muốn tách chúng thành khu vực (Region) và tên đội (Team Name) tương ứng.
Danh sách các đội NFL, khu vực và liên đoàn trong bảng Excel
Đầu tiên, chọn bất kỳ ô nào trong vùng dữ liệu, và trong tab Data (Dữ liệu) trên ribbon, nhấp vào From Table/Range (Từ Bảng/Phạm vi) trong nhóm Get & Transform Data (Truy xuất & Chuyển đổi Dữ liệu).
Chọn From Table/Range trong tab Data để mở Power Query Editor
Thao tác này sẽ khởi chạy Power Query Editor, nơi mọi phép biến đổi dữ liệu mạnh mẽ được thực hiện!
Trong Power Query Editor, nhấp chuột phải vào tiêu đề cột bạn muốn tách, sau đó chọn Split Column (Tách cột) > By Delimiter (Theo dấu phân cách).
Nhấp chuột phải vào tiêu đề cột và chọn Split Column > By Delimiter trong Power Query Editor
Sau đó, trong hộp thoại Split Column by Delimiter (Tách cột theo Dấu phân cách), chọn dấu phân cách – trong trường hợp này là Space (Khoảng trắng) – và xem xét dữ liệu để xác định tại vị trí nào bạn muốn dữ liệu được tách. Vì một số khu vực trong tên đội NFL có nhiều hơn một từ, nhưng tất cả các hậu tố của chúng chỉ chứa một từ, văn bản cần được chia bởi dấu phân cách right-most delimiter (dấu phân cách cuối cùng bên phải).
Cấu hình Split Column By Delimiter trong Power Query Editor, chọn Space và Right-most delimiter
Bây giờ, khi bạn nhấp OK, bạn sẽ thấy tên đội được chia thành hai cột tại dấu phân cách cuối cùng. Tại thời điểm này, hãy nhấp đúp vào tiêu đề cột mới để đổi tên chúng thành các tên ý nghĩa hơn như “Region” và “Team Name”.
Đổi tên các cột đã tách trong Power Query Editor của Excel
Cuối cùng, nhấp vào nửa trên của biểu tượng Close & Load (Đóng & Tải) ở góc trên bên trái của Power Query Editor, và dữ liệu mới đã tách sẽ được mở trong một bảng mới trên một trang tính mới trong Excel.
Nút Close & Load trong Power Query Editor của Excel
Nếu dữ liệu gốc thay đổi, hãy đảm bảo nhấp vào Refresh (Làm mới) trong tab Table Design (Thiết kế Bảng) sau khi chọn bảng được tạo thông qua Power Query Editor. Ví dụ, nếu bạn thêm một đội mới vào cuối bảng, Excel sẽ tự động tách đội này theo các bước bạn đã tạo trong Power Query Editor.
Kết Luận
Việc tách dữ liệu trong Excel thành nhiều cột là một kỹ năng thiết yếu giúp tối ưu hóa quy trình làm việc và tăng cường hiệu quả xử lý thông tin. Như chúng ta đã khám phá, Excel cung cấp một loạt các công cụ mạnh mẽ và linh hoạt để thực hiện tác vụ này, từ các tùy chọn trực quan như Text to Columns và Flash Fill, đến các hàm công thức chuyên sâu như TEXTSPLIT, TEXTBEFORE, TEXTAFTER, LEFT, MID, RIGHT, và công cụ biến đổi dữ liệu chuyên nghiệp Power Query Editor.
Mỗi phương pháp đều có những ưu điểm riêng và phù hợp với các loại dữ liệu và tình huống khác nhau. Việc lựa chọn công cụ phù hợp sẽ giúp bạn tiết kiệm thời gian đáng kể, giảm thiểu sai sót và đảm bảo tính chính xác của dữ liệu. Flash Fill lý tưởng cho các tập dữ liệu nhỏ với mẫu rõ ràng; Text to Columns cung cấp hướng dẫn từng bước; các hàm Excel mang lại sự linh hoạt và kiểm soát chính xác; và Power Query Editor là lựa chọn tối ưu cho dữ liệu lớn, phức tạp và cần tự động cập nhật.
Tại webthuthuat.net, chúng tôi luôn nỗ lực mang đến những hướng dẫn chi tiết và hữu ích nhất để bạn có thể làm chủ công nghệ. Đừng ngần ngại thực hành các kỹ thuật đã học và khám phá thêm các tính năng tuyệt vời khác của Excel. Việc sắp xếp và biến đổi dữ liệu chỉ là một phần nhỏ trong vô vàn khả năng của Excel. Nếu bạn thấy bài viết này hữu ích, hãy chia sẻ nó đến những người cần và để lại bình luận phía dưới về trải nghiệm của bạn hoặc bất kỳ câu hỏi nào. Chúng tôi luôn sẵn lòng hỗ trợ bạn trên hành trình khám phá và làm chủ công nghệ.
