Trong công việc và học tập, việc xử lý dữ liệu trong Microsoft Excel là một kỹ năng thiết yếu. Tuy nhiên, khi dữ liệu được tổng hợp trong một ô duy nhất và bạn cần phân chia chúng ra nhiều cột riêng biệt, việc thực hiện thủ công sẽ tiêu tốn rất nhiều thời gian và dễ gây ra sai sót. 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 quy trình tự động hóa, giúp bạn hoàn thành nhiệm vụ này một cách nhanh chóng và chính xác.
Bài viết này từ Trithuccongnghe.net sẽ đi sâu vào các phương pháp tối ưu nhất để tách dữ liệu trong Excel, giúp bạn làm chủ kỹ năng xử lý bảng tính, nâng cao hiệu suất làm việc và khẳng định chuyên môn trong lĩnh vực công nghệ thông tin. Chúng ta sẽ khám phá từng công cụ một cách chi tiết, kèm theo các ví dụ thực tế và lưu ý quan trọng để bạn có thể áp dụng ngay lập tức vào công việc của mình.
1. Sử dụng Công cụ Text to Columns (Văn bản thành Cột)
Text to Columns là một trong những công cụ tích hợp mạnh mẽ của Excel, đặc biệt hữu ích khi bạn muốn tách dữ liệu dựa trên một hoặc nhiều dấu phân cách (delimiter). Phương pháp này phù hợp nếu bạn muốn một hộp thoại hướng dẫn từng bước để đảm bảo quy trình chính xác.
Ví dụ, giả sử bạn có một danh sách họ tên đầy đủ trong Cột A (ví dụ: “Nguyễn, Văn A”) và muốn tách thành “Họ” ở Cột B và “Tên đệm + Tên” ở Cột C.
Để 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 Ribbon, vào tab Data (Dữ liệu) và nhấp vào “Text to Columns” (Văn bản thành Cột).
Chọn dữ liệu và công cụ Text to Columns trong tab Data của Excel
Trong hộp thoại “Convert Text to Columns Wizard” (Chuyển đổi văn bản thành cột), chọn tùy chọn “Delimited” (Phân tách bằng dấu phân cách) rồi nhấp vào “Next” (Tiếp theo).
Chọn tùy chọn Delimited trong hộp thoại Convert Text to Columns của Excel
Một dấu phân cách là ký tự, biểu tượng hoặc khoảng trắng được sử dụng để phân tách các mục trong một chuỗi. Trong ví dụ này, các 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 đó, hãy chọn cả hai tùy chọn này: “Comma” (Dấu phẩy) và “Space” (Khoảng trắng). Bạn có thể xem trước kết quả sẽ ảnh hưởng đến dữ liệu của mình như thế nào trong phần xem trước ở cuối hộp thoại. Nếu đã hài lòng, nhấp vào “Next” (Tiếp theo).
Chọn dấu phẩy và khoảng trắng làm dấu phân cách trong Text to Columns Excel
Tiếp theo, xóa mọi thông tin trong trường Destination (Điểm đích), chọn ô mà bạn muốn dữ liệu đã tách bắt đầu hiển thị – trong trường hợp này là ô B2 – rồi nhấp vào “Finish” (Hoàn tất).
Thiết lập ô B2 làm điểm đến cho dữ liệu sau khi tách trong Excel
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ọ tên đầy đủ đã được tách thành họ và tên 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 tên đầy đủ ban đầu.
Tuy nhiên, trong một số trường hợp, dữ liệu có thể không tuân theo một quy tắc phân tách đơn giản. Ví dụ, một người có thể có tên đệm và tên riêng hoặc họ kép, khiến việc chọn khoảng trắng làm dấu phân cách sẽ tách các phần tên này thành các cột riêng biệt, dù bạn muốn giữ chúng lại với nhau.
Danh sách tên trong Excel với trường hợp họ hoặc tên có nhiều từ
Để khắc phục điều này, bạn chỉ cần chọn dấu phẩy làm dấu phân cách – không chọn khoảng trắng – trong Convert Text to Columns Wizard.
Chỉ chọn dấu phẩy làm dấu phân cách để tách họ tên trong Excel
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 thường bị thêm một khoảng trắng ở đầu, vì bạn đã không yêu cầu Excel coi khoảng trắng là dấu phân cách.
Để loại bỏ các khoảng trắng này, hãy mở rộng bảng thêm một cột sang phải để tạo một cột khác cho tên đã sửa. Sau đó, tại ô D2, nhập công thức sau:
=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 giữa các từ), và [@ [First name]] là một tham chiếu có cấu trúc đến cột trong bảng có tên “First names” (Tên riêng).
Sử dụng hàm TRIM để loại bỏ khoảng trắng thừa sau khi tách dữ liệu 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ó khoảng trắng thừa. Do dữ liệu được định dạng trong một bảng Excel, công thức sẽ tự động được sao chép vào các ô còn lại của cột.
Tiếp theo, chọn các tên riêng vừa được tạo, rồi 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), chọn “Values” (Giá trị) bằng cách nhấn V, rồi nhấn Enter.
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 trước tên riêng (C) và cột gốc chứa họ tên đầy đủ (A) để hoàn thành bảng của bạn.
2. Sử dụng Tính năng Flash Fill Tự động
Microsoft Excel sở hữu nhiều công cụ tự động hóa các quy trình tẻ nhạt, và Flash Fill là một trong số những công cụ hữu ích nhất. Nếu việc sử dụng Text to Columns Wizard mất quá nhiều thời gian, công cụ trực quan này sẽ giúp tăng tốc mọi thứ. Về cơ bản, đó 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, chọn ô mà bạn muốn đặt phần tử đầu tiên đã tách (trong trường hợp này là ô B2), nhập giá trị (trong trường hợp này là Smith nếu bạn đang tách họ “Smith, John”) theo cách thủ công và nhấn Enter.
Tiếp theo, trong tab Data (Dữ liệu) trên thanh Ribbon, nhấp vào “Flash Fill” (Điền nhanh).
Vị trí công cụ Flash Fill trong tab Data của Excel
Nếu bạn là người dùng thành thạo và ưa thích các phím tắt, bạn có thể nhấn Ctrl+E để kích hoạt Flash Fill.
Khi bạn chạy Flash Fill, Excel sẽ tìm kiếm các mẫu trong dữ liệu và cố gắng áp dụng chúng cho phần còn lại của dữ liệu trong phạm vi. Trong ví dụ tách họ tên, nó sẽ tự động điền đầy đủ các họ còn lại từ dữ liệu gốc xuống Cột B.
Sau đó, lặp lại quy trình tương tự cho các tên riêng ở Cột C – nhập giá trị đầu tiên để thiết lập mẫu, nhấn Enter, rồi nhấn Ctrl+E.
Hoàn tất việc tách tên bằng Flash Fill tự động trong Excel
Flash Fill là công cụ lý tưởng cho các tập dữ liệu nhỏ, nơi bạn có thể nhanh chóng kiểm tra xem công cụ có xác định đúng các mẫu trong dữ liệu của mình hay không. Tuy nhiên, nếu bạn đang làm việc với nhiều hà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 – vì chúng không yêu cầu bạn phải kiểm tra kỹ lưỡng kết quả.
3. Sử dụng 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 các hàm tích hợp sẵn của Microsoft Excel. Nếu 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, 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 muốn xóa dữ liệu gốc.
Hàm TEXTSPLIT
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).
Mặc dù các hàm mảng động 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, nhưng hãy lưu ý rằng chúng không tương thích với các bảng Excel đã đị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 dữ liệu thông thường.
Trong ví dụ này, giả sử bạn muốn tách tên 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.
Để thực hiện điều này bằng TEXTSPLIT, tại ô B1, hãy nhập:
=TEXTSPLIT(A2,", ")Trong đó A2 là ô chứa văn bản bạn muốn tách, và sự kết hợp dấu phẩy + khoảng trắng trong dấu ngoặc kép báo cho Excel biết rằng các ký tự này cùng nhau tạo thành dấu phân cách.
Ví dụ sử dụng hàm TEXTSPLIT để tách thủ đô và tiểu bang 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.
Vì dữ liệu nằm trong một phạm vi 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 (điểm điền tự động) ở góc dưới bên phải của ô B2 xuống cuối phạm vi dữ liệu.
Kéo Fill Handle trong Excel để sao chép công thức TEXTSPLIT xuống các ô khác
Hàm TEXTBEFORE và TEXTAFTER
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. Hơn nữa, vì chúng không phải là hàm mảng động, bạn có thể sử dụng chúng trong dữ liệu được định dạng dưới dạng bảng.
Sử dụng cùng ví dụ như trên, bắt đầu với các thủ đô tiểu bang, tại ô B2, hãy nhập:
=TEXTBEFORE([@[Capital and State]],", ")Trong đó [@ [Capital and State]] là 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 dấu phẩy + khoảng trắng bên trong dấu ngoặc kép báo 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.
Sau đó, để trích xuất các tên tiểu bang trong ô C2, hãy nhập:
=TEXTAFTER([@[Capital and State]],", ")và nhấn Enter.
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ề.
Hàm LEFT, MID và RIGHT
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í nhất định trong chuỗi ký tự. Điều này đặc biệt tiện lợi nếu bạn có một tập dữ liệu gồm các giá trị có cấu trúc nhất quán, chẳng hạn 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.
Đầu tiên, để trích xuất các quốc gia, tại ô B2, nhập:
=LEFT([@Code],3)Trong đó, LEFT([@Code] báo cho Excel đọc các ô trong cột “Code” từ bên trái, và số 3 báo cho Excel trích xuất ba ký tự đầu tiên.
Trích xuất mã quốc gia bằng hàm LEFT trong Excel
Bây giờ, tại ô D2, 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)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:
=MID([@Code],4,1)Trong đó, 4 báo cho Excel bắt đầu trích xuất từ ký tự thứ tư, và 1 báo cho Excel 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 bằng hàm MID trong Excel
Giờ đây, bạn đã tách thành công dữ liệu trong Cột A thành ba cột riêng biệt.
4. Sử dụ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 bộ dữ liệu lớn và cần tự động hóa.
Trong ví dụ này, giả sử bạn có một bảng Excel chứa tên các đội NFL khác nhau và bạn muốn tách chúng thành vùng và tên đội tương ứng.
Đầu tiên, chọn bất kỳ ô nào trong vùng dữ liệu. Sau đó, trong tab Data (Dữ liệu) trên thanh Ribbon, nhấp vào “From Table/Range” (Từ Bảng/Phạm vi) trong nhóm “Get & Transform Data” (Lấy & Chuyển đổi dữ liệu).
Chọn dữ liệu và công cụ From Table/Range trong tab Data của Excel
Thao tác này sẽ khởi chạy Power Query Editor, nơi mọi “phép thuật” xử lý dữ liệu sẽ diễn ra!
Nhấp chuột phải vào tiêu đề cột bạn muốn tách, rồi chọn “Split Column” (Tách cột) > “By Delimiter” (Theo dấu phân cách).
Tùy chọn Split Column By Delimiter trong Power Query Editor của Excel
Sau đó, trong hộp thoại, chọn dấu phân cách – trong trường hợp này là khoảng trắng – và xem xét dữ liệu để xác định bạn muốn tách dữ liệu tại dấu phân cách nào. Vì một số vùng 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 theo dấu phân cách nằm ở “Right-most delimiter” (Dấu phân cách tận cùng bên phải).
Thiết lập dấu phân cách và vị trí tách trong Power Query Editor
Bây giờ, khi bạn nhấp vào “OK”, bạn sẽ thấy tên các độ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 các tiêu đề cột mới để đổi tên chúng cho phù hợp.
Đổi tên các cột đã tách trong giao diện Power Query Editor
Cuối cùng, nhấp vào nửa trên của biểu tượng “Close & Load” (Đóng và 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 trang tính mới.
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 khác vào cuối bảng, Excel sẽ tự động tách đội này theo 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 quan trọng giúp tối ưu hóa quá trình xử lý và phân tích thông tin. Dù bạn ưu tiên sự đơn giản của Flash Fill, sự linh hoạt của các hàm TEXTSPLIT, TEXTBEFORE, TEXTAFTER, LEFT, MID, RIGHT, hay sức mạnh tự động hóa của Text to Columns và Power Query Editor, Excel đều cung cấp những giải pháp hiệu quả cho mọi nhu cầu.
Trithuccongnghe.net hy vọng bài viết này đã cung cấp cho bạn cái nhìn toàn diện và hướng dẫn chi tiết về các phương pháp tách dữ liệu trong Excel, giúp bạn áp dụng vào công việc một cách tự tin và chuyên nghiệp hơn. Đừng ngại thử nghiệm từng phương pháp để tìm ra lựa chọn phù hợp nhất với loại dữ liệu và quy trình làm việc của bạn. Việc nắm vững các công cụ này không chỉ giúp bạn tiết kiệm thời gian mà còn nâng cao chất lượng và độ chính xác của các báo cáo, phân tích.
Hãy tiếp tục khám phá các tính năng mạnh mẽ khác của Excel và chia sẻ kinh nghiệm của bạn trong phần bình luận bên dưới nhé!
