Trong kỷ nguyên số, Microsoft Excel đã trở thành một công cụ không thể thiếu cho bất kỳ ai làm việc với dữ liệu. Tuy nhiên, một trong những thách thức phổ biến nhất mà người dùng thường gặp phải là việc tổ chức và làm sạch dữ liệu, đặc biệt là khi cần tách thông tin từ một ô duy nhất thành nhiều cột riêng biệt. Việc thực hiện thủ công công việc này không chỉ tốn thời gian mà còn tiềm ẩn nguy cơ sai sót cao, đặc biệt với các 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à linh hoạt – từ các tính năng tích hợp sẵn, quy trình tự động hóa cho đến các hàm chuyên biệt – giúp bạn giải quyết nhiệm vụ phân tách dữ liệu một cách hiệu quả và chính xác. Bài viết này của trangtincongnghe.net sẽ đi sâu vào từng phương pháp, hướng dẫn bạn từng bước cách tối ưu hóa quy trình làm việc và tận dụng tối đa sức mạnh của Excel để quản lý dữ liệu một cách chuyên nghiệp nhất. Hãy cùng khám phá những công cụ này để biến công việc tẻ nhạt thành một quy trình nhanh chóng và đáng tin cậy.
1. Sử Dụng Công Cụ Text to Columns (Văn bản thành Cột)
Công cụ “Text to Columns” là một tính năng tích hợp sẵn trong Excel, cung cấp một giao diện hộp thoại trực quan, hướng dẫn bạn từng bước trong quá trình tách dữ liệu. Đây là lựa chọn lý tưởng nếu bạn muốn kiểm soát chi tiết quá trình phân tách và làm việc trong môi trường có cấu trúc.
Ví dụ, giả sử bạn có một danh sách họ và 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.
Để thực hiện, đầu 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), chuyển đến tab “Data” (Dữ liệu) và nhấp vào “Text to Columns” (Văn bản thành Cột) trong nhóm “Data Tools” (Công cụ Dữ liệu).
Chọn dữ liệu và công cụ Text to Columns trong Excel
Trong 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) hiện ra, chọn tùy chọn “Delimited” (Phân tách bởi ký tự) và nhấp vào “Next” (Tiếp theo). Phương pháp này phù hợp khi các phần dữ liệu bạn muốn tách được phân cách bởi một ký tự cụ thể (dấu phẩy, khoảng trắng, dấu chấm phẩy, v.v.).
Chọn Delimited trong Convert Text To Column Wizard của Excel
Tách Dữ Liệu Với Ký Tự Phân Tách (Delimiter)
Một delimiter (ký tự phân tách) là một ký tự, biểu tượng hoặc khoảng trắng dùng để phân chia các mục trong một chuỗi. Trong ví dụ về họ và tên, các tên trong Cột A được phân tách bằng một 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) làm ký tự phân tách. Bạn có thể xem trước cách dữ liệu sẽ được tách trong phần “Data preview” (Xem trước dữ liệu) ở cuối hộp thoại. Sau khi hài lòng với kết quả xem trước, nhấp vào “Next” (Tiếp theo).
Chọn Comma và Space làm ký tự phân tách trong Convert Text To Columns Wizard của Excel
Tiếp theo, hãy xóa mọi thông tin trong trường “Destination” (Ô đích) và 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à sau đó nhấp vào “Finish” (Hoàn tất).
Chọn ô B2 làm điểm đến cho dữ liệu được 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ọ và 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.
Xử Lý Các Trường Hợp Đặc Biệt (Tên Kép, Khoảng Trắng Thừa)
Tuy nhiên, trong một số trường hợp, bạn có thể gặp phải dữ liệu không nhất quán. Ví dụ, một người có hai tên đệm hoặc hai họ. Trong tình huống này, việc chọn khoảng trắng làm ký tự phân tách sẽ khiến các tên kép này bị tách ra thành các cột riêng biệt, mặc dù bạn muốn giữ chúng cùng nhau.
Danh sách tên trong bảng Excel với tên có nhiều hơn một từ được tô sáng
Để khắc phục vấn đề này, trong “Convert Text to Columns Wizard”, bạn chỉ nên chọn “Comma” (Dấu phẩy) làm ký tự phân tách – không chọn “Space” (Khoảng trắng).
Chỉ chọn Comma làm ký tự phân tách trong Convert Text To Columns Wizard của 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 (First name) có thể 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à ký tự phân tách.
Để loại bỏ các khoảng trắng thừa này, bạn có thể mở rộng bảng thêm một cột sang phải để tạo một cột mới 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 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à tham chiếu có cấu trúc đến cột “First name” trong bảng của bạn.
Sử dụng hàm TRIM trong Excel để loại bỏ khoảng trắng thừa từ 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. Đồng thời, vì dữ liệu đang ở trong một bảng Excel đã định dạng, công thức sẽ tự động được áp dụng cho các ô còn lại trong cột.
Tiếp theo, chọn các tên riêng vừa được tạo, và nhấn Ctrl+C để sao chép chúng. 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. Bước này rất quan trọng để chuyển kết quả công thức thành giá trị cố định, tránh lỗi khi xóa cột gốc.
Chọn Values trong hộp thoại Paste Special của Microsoft Excel
Cuối cùng, xóa cột chứa các khoảng trắng thừa trước tên riêng (C) và cột gốc chứa tên đầy đủ (A) để hoàn thành bảng của bạn.
2. Tận Dụng Tính Năng Flash Fill (Tự Động Điền Nhanh)
Microsoft Excel sở hữu nhiều công cụ mạnh mẽ giúp tự động hóa các quy trình nhàm chán, và Flash Fill là một trong số đó. Nếu việc sử dụng “Text to Columns Wizard” có vẻ phức tạp hoặc tốn 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, Flash Fill là một cách sao chép và dán dữ liệu vào các ô mới một cách thông minh hơn.
Đầu tiên, chọn ô mà bạn muốn chứa phần tử tách đầu tiên (ví dụ, ô B2), nhập giá trị mà bạn muốn Excel học theo (ví dụ, “Smith” nếu đó là họ đầu tiên trong danh sách của bạn) một cách thủ công và nhấn Enter.
Tiếp theo, trên tab “Data” (Dữ liệu) trên thanh công cụ, nhấp vào “Flash Fill” trong nhóm “Data Tools” (Công cụ Dữ liệu). Nếu bạn thích sử dụng phím tắt, hãy nhấn Ctrl+E.
Chọn Flash Fill trong tab Data trên 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 đã chọn. Trong ví dụ này, nó sẽ tự động điền phần còn lại của họ từ dữ liệu gốc xuống cột B một cách chính xác.
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.
Tên riêng còn lại trong cột C được điền hoàn chỉnh bằng công cụ Flash Fill của 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ụ đã 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 đang làm việc với nhiều hàng dữ liệu, tốt hơn hết bạn nên 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 xác minh kết quả một cách chặt chẽ.
3. Áp Dụng Các Hàm Excel Tích Hợp (Built-in Functions)
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 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 mà không làm mất kết quả đã tách.
Hàm TEXTSPLIT (Tách Văn Bản)
Hàm TEXTSPLIT của Excel cho phép bạn chia tất cả nội dung trong một ô thành các cột khác nhau dựa trên một hoặc nhiều ký tự phân tá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 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 vùng dữ liệu thông thường.
Trong ví dụ này, giả sử bạn muốn tách tên thủ đô và tên 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.
Để làm điều này bằng hàm TEXTSPLIT, trong ô B1, hãy 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 ", " (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à ký tự phân tách.
Sử dụng hàm TEXTSPLIT trong Microsoft Excel để tách thủ đô và bang thành các cột riêng biệt
Khi bạn nhấn Enter và chọn ô mà bạ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, nhưng kết quả đã tự động “tràn” (spilled over) sang ô C2.
Vì dữ liệu nằm trong một phạm vi không được định dạng là bảng Excel, bạn cần điền phần còn lại của cột thủ công. Để làm điều này, hãy nhấp và kéo núm điền (fill handle) ở góc dưới bên phải của ô B2 xuống cuối phạm vi dữ liệu.
Núm điền trong Excel được dùng để sao chép công thức từ B2 xuống B10
Hàm TEXTBEFORE và TEXTAFTER (Văn bản Trước/Sau)
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 ký tự phân tách. Ngoài ra, 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ụ trên, để trích xuất tên thủ đô, trong ô B2, hãy nhập công thức:
=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 ", " 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 sẽ được coi là ký tự phân tách. Lần này, chỉ văn bản trước ký tự phân tách được trả về trong kết quả khi bạn nhấn Enter.
Để trích xuất tên bang trong ô C2, bạn nhập công thức:
=TEXTAFTER([@[Capital and State]],", ")và nhấn Enter.
Bên cạnh 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 nhất thiết phải nằm trong các cột liền kề.
Hàm LEFT, MID, và RIGHT (Trái, Giữa, Phải)
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 hữu ích nếu bạn có một tập dữ liệu với 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à ký tự 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ột B, C, và D tương ứng.
Đầu tiên, để trích xuất các quốc gia, trong ô 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à 3 yêu cầu Excel trích xuất ba ký tự đầu tiên.
Quốc gia được trích xuất từ mã trong Excel bằng hàm LEFT
Bây giờ, trong ô 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, 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 công thức:
=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.
Xếp hạng được trích xuất từ mã trong Excel bằng hàm MID
Bây giờ, bạn đã thành công tách dữ liệu trong Cột A thành ba cột riêng biệt.
4. Khai Thác Power Query Editor
Nhiều người cho rằng Power Query Editor của Excel quá phức tạp, 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 với các tác vụ làm sạch dữ liệu phức tạp.
Trong ví dụ này, giả sử bạn có một bảng Excel chứa các đội NFL và bạn muốn tách chúng thành khu vực và tên đội tương ứng.
Đầu tiên, chọn bất kỳ ô nào trong dữ liệu của bạn. Trên tab “Data” (Dữ liệu) trên thanh công cụ, nhấp vào “From Table/Range” (Từ Bảng/Phạm vi) trong nhóm “Get & Transform Data” (Nhậ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 Or Range trong tab Data được tô sáng
Thao tác này sẽ khởi chạy Power Query Editor – nơi phép thuật xảy ra!
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 Ký tự phân tách).
Tiêu đề cột trong Power Query Editor của Excel được chọn qua nhấp chuột phải, và By Delimited trong tùy chọn Split Column được chọn
Sau đó, trong hộp thoại hiện ra, chọn ký tự phân tách – trong trường hợp này là một khoảng trắng. Xem xét dữ liệu để xác định bạn muốn tách dữ liệu tại ký tự phân tách nào. Vì một số tên 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 ký tự phân tách bên phải nhất (Right-most delimiter).
Hộp thoại Split Column By Delimiter trong Power Query Editor của Excel với khoảng trắng được chọn làm ký tự phân tách và Right-most delimiter được chọn làm vị trí
Bây giờ, khi bạn nhấp “OK”, bạn sẽ thấy tên các đội được chia thành hai cột tại ký tự phân tách cuối cùng. Tại thời điểm này, bạn có thể nhấp đúp vào các tiêu đề cột mới để đổi tên chúng thành “Region” (Khu vực) và “Team Name” (Tên đội).
Đổi tên cột 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 đã tách sẽ mở ra trong một bảng 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 các bước bạn đã tạo trong Power Query Editor.
Kết Luận
Việc tách dữ liệu thành nhiều cột trong Excel không chỉ là một kỹ năng cơ bản mà còn là chìa khóa để làm sạch, tổ chức và phân tích dữ liệu hiệu quả hơn. Từ công cụ “Text to Columns” trực quan, tính năng “Flash Fill” thông minh, sự linh hoạt của các hàm TEXTSPLIT, TEXTBEFORE/TEXTAFTER, LEFT/MID/RIGHT, cho đến sức mạnh vượt trội của Power Query Editor, Excel đã trang bị cho người dùng một bộ công cụ đa dạng để giải quyết mọi thách thức về phân tách dữ liệu.
Mỗi phương pháp đều có ưu điểm và kịch bản sử dụng riêng. “Text to Columns” lý tưởng cho các tác vụ tách dựa trên ký tự phân tách có cấu trúc rõ ràng. “Flash Fill” tỏa sáng với các tập dữ liệu nhỏ cần tách nhanh chóng dựa trên mẫu. Các hàm Excel cung cấp sự kiểm soát chi tiết và khả năng tự động hóa bằng công thức, trong khi Power Query Editor là lựa chọn tối ưu cho việc xử lý các tập dữ liệu lớn, phức tạp và cần quy trình làm sạch thường xuyên.
Nắm vững các kỹ thuật này không chỉ giúp bạn tiết kiệm thời gian đáng kể mà còn nâng cao độ chính xác của dữ liệu, từ đó đưa ra các quyết định sáng suốt hơn. Hãy thử nghiệm và áp dụng các phương pháp này vào công việc hàng ngày của bạn. Đừng quên truy cập trangtincongnghe.net để khám phá thêm nhiều thủ thuật Excel và kiến thức công nghệ hữu ích khác! Bạn có thắc mắc hoặc kinh nghiệm sử dụng các công cụ này? Hãy chia sẻ ý kiến của bạn trong phần bình luận bên dưới nhé!
