Trong môi trường làm việc hiện đại, Microsoft Excel là một công cụ không thể thiếu để quản lý và phân tích dữ liệu. Tuy nhiên, một trong những thách thức phổ biến mà nhiều người dùng gặp phải là khi dữ liệu quan trọng nằm gọn trong một ô duy nhất, đòi hỏi phải được tách ra thành nhiều cột riêng biệt để dễ dàng xử lý và phân tích hơn. Việc thực hiện tác vụ này theo cách thủ công không chỉ tốn rất nhiều thời gian mà còn tiềm ẩn nguy cơ cao mắc lỗi, đặc biệt với những tập dữ liệu lớn. May mắn thay, Excel cung cấp một loạt các công cụ mạnh mẽ và tính năng tự động hóa – từ các công cụ tích hợp sẵn cho đến các hàm chuyên dụng – giúp bạn thực hiện công việc tách dữ liệu thành nhiều cột một cách nhanh chóng, chính xác và hiệu quả.
Bài viết này của Thời Báo Công Nghệ sẽ đi sâu vào hướng dẫn chi tiết các phương pháp tách dữ liệu trong Excel, bao gồm sử dụng công cụ Text to Columns, tính năng Flash Fill thông minh, các hàm văn bản tích hợp như TEXTSPLIT, TEXTBEFORE, TEXTAFTER, LEFT, MID, RIGHT, và cả trình chỉnh sửa Power Query Editor mạnh mẽ. 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. Dù bạn là người mới bắt đầu hay một người dùng Excel chuyên nghiệp muốn tối ưu hóa quy trình làm việc của mình, bạn sẽ tìm thấy một giải pháp phù hợp để quản lý dữ liệu hiệu quả hơn. Hãy cùng khám phá và nâng cao kỹ năng xử lý dữ liệu Excel của bạn ngay hôm nay!
Tách Dữ Liệu Bằng Công Cụ Text to Columns
Một trong những cách phổ biến và dễ tiếp cận nhất để tách dữ liệu thành nhiều cột trong Microsoft Excel là sử dụng công cụ Text to Columns (Chuyển Văn bản thành Cột). Phương pháp này đặc biệt hữu ích nếu bạn thích làm việc trong một hộp thoại hướng dẫn từng bước rõ ràng, giúp bạn kiểm soát quá trình tách dữ liệu một cách trực quan.
Ví dụ, hãy tưởng tượng 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 hiển thị cột A chứa họ tên đầy đủ, cột B và C được chuẩn bị để tách thành "Họ" và "Tên".
Để thực hiện điều này, trước tiên, bạn hãy chọn các ô chứa dữ liệu 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).
Dữ liệu trong bảng Excel được chọn và công cụ Text To Columns trên tab Data được làm nổi bật.
Trong cửa sổ Convert Text to Columns Wizard (Trình hướng dẫn Chuyển Văn bản thành Cột), chọn “Delimited” (Phân cách bằng dấu), sau đó nhấp “Next” (Tiếp theo).
Tùy chọn "Delimited" được chọn trong hộp thoại Convert Text to Columns Wizard của Excel.
Dấu phân cách (delimiter) là một ký tự, biểu tượng hoặc khoảng trắng được 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 cá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 cách dữ liệu sẽ được tách ở phần dưới của hộp thoại. Nếu đã hài lòng, nhấp “Next”.
Dấu phẩy và dấu cách được chọn làm dấu phân cách (delimiter) trong hộp thoại Convert Text to Columns Wizard của Excel.
Tiếp theo, xóa mọi thông tin trong trường Destination (Vị trí đích), sau đó chọn ô mà bạn muốn dữ liệu đã tách hiển thị – trong trường hợp này là ô B2 – và nhấp “Finish” (Hoàn tất).
Ô B2 được chọn làm vị trí đích (Destination) trong hộp thoại Convert Text to Columns Wizard của Excel để hiển thị dữ liệu đã tách.
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 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 ở cột A, vì các tên đã tách không còn liên kết với họ tên đầy đủ nữa.
Các họ tên đầy đủ trong cột A của Excel đã được tách thành họ trong cột B và tên trong cột C.
Tuy nhiên, trong ví dụ này, có một người có hai tên riêng và một người có hai họ. Trong trường hợp này, việc chọn dấu phân cách là khoảng trắng sẽ tách những tên ghép này thành các cột riêng biệt, mặc dù bạn muốn giữ chúng lại với nhau.
Danh sách tên trong bảng Excel với các tên có nhiều hơn một từ ở họ hoặc tên riêng được làm nổi bật, gây ra lỗi khi tách.
Để khắc phục điều này, bạn chỉ cần chọn dấu phân cách là Comma (Dấu phẩy) – không chọn khoảng trắng – trong cửa sổ Convert Text to Columns Wizard.
Chỉ dấu phẩy được chọn làm dấu phân cách (delimiter) trong Convert Text to Columns Wizard để xử lý tên có nhiều từ.
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 bị thừa 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.
Các tên riêng trong cột C của Excel bị thừa dấu cách ở đầu mỗi ô sau khi tách.
Để 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 sang phải để tạo một cột khác cho các tên riêng đã được sửa. Sau đó, trong ô 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 khỏi chuỗi văn bản (ngoại trừ các khoảng trắng giữa các từ), và [@[First name]] là tham chiếu có cấu trúc đến cột trong bảng có tên “First name” (Tên riêng).
Hàm TRIM đang được sử dụng trong Excel để loại bỏ khoảng trắng thừa ở đầu các tên trong một cột.
Khi bạn nhấn Enter, các tên riêng sẽ xuất hiện trong cột D, nhưng lần này, không còn khoảng trắng thừa ở đầu. Hơn nữa, vì dữ liệu nằm trong một bảng Excel đã định dạng, công thức sẽ tự động được điền vào các ô còn lại của cột.
Danh sách tên trong cột D của Excel đã được làm sạch khoảng trắng thừa bằng hàm TRIM từ dữ liệu cột C.
Tiếp theo, chọn các tên riêng mới tạo ra và nhấn Ctrl+C để sao chép chúng. Bây giờ, nhấn Ctrl+Alt+V để mở hộp thoại Paste Special (Dán Đặc biệt), sau đó nhấn V để chọn “Values” (Giá trị), và cuối cùng là Enter.
Tùy chọn "Values" (Giá trị) được chọn trong hộp thoại Paste Special của Microsoft Excel để dán dữ liệu.
Cuối cùng, xóa cột chứa khoảng trắng thừa trước các tên riêng (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 với các họ đã được tách vào cột A và tên vào cột B một cách gọn gàng.
Sử Dụng Tính Năng Flash Fill Tự Động
Microsoft Excel có nhiều công cụ giúp tự động hóa các quy trình vốn tẻ nhạt, và Flash Fill là một trong những tính năng hữu ích nhất trong số đó. Nếu việc sử dụng Text To Columns Wizard để tách dữ liệu mất quá nhiều thời gian, công cụ trực quan này sẽ giúp bạn tăng tốc đáng kể. Về cơ bản, đâ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, 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ị (ví dụ: Smith) theo cách thủ công và nhấn Enter.
Bảng Excel hiển thị "Smith" được nhập vào ô đầu tiên của cột Họ, chuẩn bị cho tính năng Flash Fill.
Tiếp theo, trên tab Data (Dữ liệu) của thanh ribbon, nhấp vào “Flash Fill”.
Nếu bạn thích sử dụng các phím tắt của Microsoft Excel, hãy nhấn Ctrl+E.
Công cụ Flash Fill trên tab Data của dải ribbon Excel được chọn để tự động tách dữ liệu.
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 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ác họ còn lại trong cột B được tự động điền đầy đủ bằng công cụ Flash Fill của 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ác tên riêng còn lại trong cột C được tự động điền đầy đủ bằng công cụ Flash Fill của 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 chính xác các mẫu trong dữ liệu của mình hay chưa. 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 – mà không yêu cầu bạn phải xác minh kết quả chặt chẽ.
Tách Dữ Liệu Với 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. Nếu bạn 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 mà không làm mất kết quả đã tách.
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 động (spilled array).
Mặc dù các hàm mảng động tạo ra kết quả tràn (spilled arrays) giúp tiết kiệm thời gian bằng cách trả về nhiều hơn một kết quả từ một công thức, 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 bằng một công thức duy nhất.
Danh sách thủ đô và tiểu bang trong cột A của Microsoft Excel, với cột B và C được chuẩn bị để tách thành các tập dữ liệu riêng lẻ.
Để thực hiện điều này bằng TEXTSPLIT, trong ô B1, hãy nhập:
=TEXTSPLIT(A2,", ")Trong đó, A2 là ô chứa văn bản bạn muốn tách, và tổ hợp dấu phẩy + khoảng trắng nằm trong dấu ngoặc kép cho Excel biết rằng đây là dấu phân cách.
Hàm TEXTSPLIT trong Microsoft Excel được sử dụng để tách thủ đô và tiểu bang thành các cột riêng biệt.
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ột mảng động (spilled array) trong Microsoft Excel được chỉ định bằng đường viền xanh lam xung quanh các ô bị ảnh hưởng.
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 tay nắm điền (fill handle) ở góc dưới bên phải của ô B2 xuống cuối phạm vi.
Tay nắm điền (fill handle) trong Excel được sử dụng để sao chép công thức từ ô B2 xuống các ô từ B3 đến B10.
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 liệu 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 tên thủ đô, trong ô B2, 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à tổ hợp dấu phẩy + khoảng trắng bên trong dấu ngoặc kép cho Excel biết rằng dấu phẩy theo sau là một khoảng trắng nên được coi là dấu phân cách. Tuy nhiên, lần này, chỉ văn bản trước dấu phân cách mới được trả về trong kết quả khi bạn nhấn Enter.
Hàm TEXTBEFORE trong Excel được sử dụng để trích xuất tên thủ đô vào cột B.
Vì vậy, để trích xuất tên các tiểu bang trong ô C2, hãy nhập:
=TEXTAFTER([@[Capital and State]],", ")và nhấn Enter.
Hàm TEXTAFTER trong Excel được sử dụng để trích xuất tên tiểu bang vào cột C.
Cùng với khả năng tương thích với 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 dụng 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ã.
Trong ví dụ này, ba ký tự đầu tiên đại diện cho một quốc gia, ba ký tự cuối cùng đại diện cho một ID và X hoặc Y ở giữa đại diện cho thứ hạng. Mục tiêu của bạn là tách ba thành phần này thành các cột B, C và D tương ứng.
Danh sách các mã số trong Excel, với các cột kế tiếp được chuẩn bị để tách mã thành ba thành phần khác nhau.
Đầu tiên, để trích xuất tên các quốc gia, trong ô B2, nhập:
=LEFT([@Code],3)Trong đó, LEFT([@Code] cho Excel biết đọc các ô trong cột “Code” từ bên trái, và 3 cho Excel biết trích xuất ba ký tự đầu tiên.
Tên quốc gia được trích xuất từ một mã trong Excel bằng cách sử dụng hàm LEFT.
Bây giờ, trong ô 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)
ID được trích xuất từ một mã trong Excel bằng cách sử dụng hàm RIGHT.
Cuối cùng, trong ô 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 cho Excel biết bắt đầu trích xuất từ ký tự thứ tư, và 1 cho Excel biết rằng bạn chỉ muốn trích xuất một ký tự duy nhất.
Thứ hạng được trích xuất từ một mã trong Excel bằng cách sử dụng hàm MID.
Bây giờ, bạn đã tách thành công dữ liệu trong cột A thành ba cột riêng biệt.
Tách Dữ Liệu Qua Power Query Editor
Nhiều người tin rằng Power Query Editor của Excel quá phức tạp đối với họ – 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 khi bạn cần tự động hóa quy trình.
Trong ví dụ này, giả sử bạn có bảng Excel chứa nhiều đội NFL khác nhau, và bạn muốn tách chúng thành khu vực và tên đội tương ứng.
Danh sách các đội NFL, khu vực và liên đoàn của họ được hiển thị trong bảng Excel, sẵn sàng để xử lý.
Đầu tiên, chọn bất kỳ ô nào trong dữ liệu, và trên 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 (Truy vấn & Chuyển đổi Dữ liệu).
Một ô trong tập dữ liệu Excel được chọn và tùy chọn "From Table/Range" (Từ Bảng/Phạm vi) trong tab Data được làm nổi bật.
Thao tác này sẽ khởi chạy Power Query Editor, nơi “phép màu” sẽ xảy ra!
Nhấp chuột phải vào tiêu đề cột bạn muốn tách, sau đó nhấp vào Split Column (Tách Cột) > By Delimiter (Theo Dấu phân cách).
Tiêu đề cột trong Power Query Editor của Excel được chọn qua chuột phải, và tùy chọn "By Delimiter" (Theo Dấu phân cách) trong "Split Column" (Tách Cột) được chọn.
Sau đó, trong hộp thoại, chọn dấu phân cách – trong trường hợp này là một khoảng trắng – và xem lại dữ liệu để xác định tại dấu phân cách nào bạn muốn tách dữ liệu. 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 theo dấu phân cách ngoài cùng bên phải.
Hộp thoại Split Column By Delimiter trong Power Query Editor của Excel, với dấu cách được chọn làm dấu phân cách và tùy chọn "Right-most delimiter" (Dấu phân cách ngoài cùng bên phải) được chọn làm vị trí tách.
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, nhấp đúp vào tiêu đề cột mới để đổi tên chúng cho phù hợp.
Các cột đang được đổi tên trong Power Query Editor của Excel để phản ánh nội dung đã tách.
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 được tách sẽ mở ra trong một trang tính mới.
Nút "Close And Load" (Đóng và Tải) trong Power Query Editor của Excel được chọn để đưa dữ liệu đã xử lý trở lại trang tính Excel.
Nếu dữ liệu gốc thay đổi, hãy nhớ 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 từ một ô thành nhiều cột trong Excel là một kỹ năng xử lý dữ liệu cơ bản nhưng cực kỳ quan trọng. Như chúng ta đã thấy, Microsoft Excel không chỉ cung cấp một mà là nhiều phương pháp mạnh mẽ để thực hiện tác vụ này, từ các công cụ trực quan như Text to Columns và Flash Fill đến các hàm văn bản linh hoạt như TEXTSPLIT, TEXTBEFORE, TEXTAFTER, LEFT, MID, RIGHT, và cả Power Query Editor chuyên nghiệp. Mỗi phương pháp đều có ưu điểm riêng và phù hợp với các tình huống dữ liệu cụ thể, giúp người dùng tiết kiệm thời gian, giảm thiểu lỗi và nâng cao hiệu quả công việc.
Lựa chọn phương pháp phù hợp nhất phụ thuộc vào cấu trúc dữ liệu của bạn, kích thước tập dữ liệu và mức độ tự động hóa bạn mong muốn. Dù bạn cần tách họ tên, địa danh, mã sản phẩm hay bất kỳ chuỗi văn bản nào khác, Excel luôn có một công cụ hoặc hàm có thể hỗ trợ bạn. Việc nắm vững các kỹ thuật này sẽ giúp bạn quản lý và sắp xếp dữ liệu một cách hiệu quả hơn, từ đó đưa ra các phân tích và quyết định chính xác.
Đừng quên ghé thăm chuyên mục thủ thuật của Thời Báo Công Nghệ để khám phá thêm nhiều mẹo và hướng dẫn hữu ích khác về Excel và các công nghệ mới nhất, giúp bạn làm chủ công việc và cuộc sống kỹ thuật số!
