Trong thế giới dữ liệu ngày nay, việc xử lý và sắp xếp thông tin trong Microsoft Excel là một kỹ năng thiết yếu. Một trong những tác vụ phổ biến nhất mà người dùng thường gặp phải là tách dữ liệu trong Excel từ một ô thành nhiều cột khác nhau. Thực hiện thủ công công việc này không chỉ tốn rất nhiều thời gian mà còn dễ gây ra lỗi, đặc biệt khi làm việc 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ẽ – từ các tính năng tích hợp sẵn và quy trình tự động đến các hàm dễ sử dụng – để thực hiện nhiệm vụ sắp xếp dữ liệu này một cách hiệu quả và chính xác.
Bài viết này của tinhoccongnghe.net sẽ hướng dẫn bạn từ A đến Z các phương pháp tách dữ liệu trong Excel, giúp bạn nắm vững mọi công cụ và kỹ thuật cần thiết để tối ưu hóa công việc của mình. Dù bạn là người mới bắt đầu hay một người dùng Excel lâu năm, các hướng dẫn chi tiết dưới đây sẽ giúp bạn giải quyết mọi thách thức về tách cột, từ đơn giản đến phức tạp, đảm bảo dữ liệu của bạn luôn được tổ chức một cách hoàn hảo.
Sử Dụ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ụ tích hợp sẵn Text to Columns. Phương pháp này đặc biệt hữu ích nếu bạn thích làm việc với hộp thoại có hướng dẫn từng bước.
Để minh họa, 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 riêng ở Cột C.
Bảng Excel ví dụ với họ tên đầy đủ tại cột A và các cột trống cho họ và tên riêng
Để thực hiện điều này, trước tiên, bạn hãy chọn các ô trong Cột A chứa dữ liệu cần tách. Sau đó, trên tab Data của thanh công cụ (ribbon), nhấp vào Text to Columns trong nhóm Data Tools.
Chọn vùng dữ liệu cần tách và công cụ Text to Columns trên tab Data trong Excel
Trong Convert Text to Columns Wizard (Hộp thoại chuyển văn bản thành cột), chọn tùy chọn Delimited (phân cách bởi ký tự), sau đó nhấp vào Next.
Lựa chọn tùy chọn Delimited trong hộp thoại Convert Text to Columns Wizard của Excel
Ký tự 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 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 cách dữ liệu của mình sẽ được tách ở phần Data preview phía dưới hộp thoại. Nếu hài lòng, nhấp vào Next.
Chọn dấu phân cách là dấu phẩy và khoảng trắng để tách dữ liệu họ tên trong Excel
Tiếp theo, bạn cần xác định vị trí mà kết quả sẽ được đặt. 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 hiển thị—trong trường hợp này là ô B2—và nhấp vào Finish.
Thiết lập ô đích là B2 để kết quả tách dữ liệu hiển thị 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 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 tên đầy đủ ban đầu.
Kết quả tách họ và tên riêng thành công trong Excel sau khi sử dụng Text to Columns
Tuy nhiên, trong ví dụ này, một người có hai tên riêng và một người khác có hai họ. Trong trường hợp này, việc chọn khoảng trắng làm dấu phân cách sẽ chia các tên ké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.
Bảng dữ liệu Excel minh họa các trường hợp tên riêng hoặc họ có nhiều từ
Để khắc phục vấn đề này, chỉ chọn dấu phân cách Comma (dấu phẩy)—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, tránh mất 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ị dính 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ột tên riêng trong Excel bị thừa khoảng trắng ở đầu sau khi tách dữ liệu
Để 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 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 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ấu trúc đến cột “First name” trong bảng của bạn.
Áp dụng hàm TRIM trong Excel để loại bỏ khoảng trắng thừa ở đầu chuỗi
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. Ngoài ra, vì dữ liệu nằm trong một bảng Excel đã định dạng, công thức sẽ tự động được sao chép vào các ô còn lại trong cột.
Kết quả tên riêng đã được chuẩn hóa, loại bỏ 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 chúng. Bây giờ, nhấn Ctrl+Alt+V để mở hộp thoại Paste Special, sau đó nhấn V để chọn Values (Giá trị), rồi nhấn Enter.
Chọn tùy 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 các 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.
Bảng Excel hoàn chỉnh với họ và tên riêng được tách chính xác thành hai cột
Sử Dụng Công Cụ Flash Fill Tự Động
Microsoft Excel có nhiều công cụ để tự động hóa các quy trình tẻ nhạt, và Flash Fill là một trong những tính năng hữu ích nhất. Nếu việc sử dụng Text to Columns Wizard để tách dữ liệu trong Excel 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, đó 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 chứa yếu 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) theo cách thủ công, và nhấn Enter.
Nhập thủ công giá trị "Smith" vào ô đầu tiên để hướng dẫn Flash Fill trong Excel
Tiếp theo, trên tab Data của thanh công cụ (ribbon), nhấp vào Flash Fill trong nhóm Data Tools.
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.
Kích hoạt công cụ Flash Fill trên tab Data của Excel để 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 còn lại của dữ liệu trong phạm vi đã chọn. Trong ví dụ này, nó đã tự động điền thành công các họ còn lại từ dữ liệu gốc xuống Cột B.
Flash Fill tự động điền các họ còn lại vào cột B dựa trên mẫu đã nhập 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.
Flash Fill tự động hoàn thành việc tách tên riêng vào cột C trong Excel
Flash Fill là công cụ tốt nhất để sử dụng trên các bộ 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, 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 kiểm tra kết quả quá kỹ lưỡng.
Sử Dụng Các Hàm Tích Hợp Sẵn Của Excel
Một cách khác để tách dữ liệu trong Excel thành nhiều cột là sử dụng một số hàm 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ẽ liên kết với các giá trị gốc, 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.
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 “spilled arrays” 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 (formatted Excel tables). Đ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 (regular ranges).
Trong ví dụ này, giả sử bạn muốn tách tên thủ đô và bang trong Cột A thành Cột B và Cột C bằng cách sử dụng một công thức duy nhất.
Bảng Excel với dữ liệu thủ đô và bang cần tách thành các cột riêng biệt bằng hàm TEXTSPLIT
Để thực hiện điều này bằng hàm TEXTSPLIT, trong ô B2, nhập công thức:
=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 được đặt trong dấu ngoặc kép 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.
Công thức TEXTSPLIT để tách tên thủ đô và bang thành hai cộ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ả đã “spill over” (tràn) sang ô C2.
Minh họa Spilled Array với đường viền xanh lam khi sử dụng hàm TEXTSPLIT trong Excel
Vì dữ liệu nằm trong một vùng không được định dạng thành 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 điểm điều khiển điền (fill handle) ở góc dưới bên phải của ô B2 xuống cuối vùng dữ liệu.
Sử dụng Fill Handle để sao chép công thức TEXTSPLIT xuống các ô bên dưới trong Excel
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 cụ thể. 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 ví dụ tương tự như trên, bắt đầu với tên thủ đô, trong ô B2, nhập:
=TEXTBEFORE([@[Capital and State]],", ")Trong đó, [@[Capital and State]] là một tham chiếu 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 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.
Công thức TEXTBEFORE giúp trích xuất tên thủ đô vào cột B trong Excel
Để trích xuất tên bang vào ô C2, bạn nhập:
=TEXTAFTER([@[Capital and State]],", ")và nhấn Enter.
Công thức TEXTAFTER được sử dụng để trích xuất tên 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ề.
Hàm LEFT, MID và RIGHT
Các hàm LEFT, MID và RIGHT của Excel cho phép bạn tách dữ liệu trong Excel dựa trên vị trí ký tự trong chuỗi. Điều này đặc biệt tiện lợi nếu bạn có một bộ 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ã.
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 thứ 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.
Bảng Excel chứa các mã code cấu trúc cố định cần tách thành ba phần riêng biệt
Đầu tiên, để trích xuất quốc gia, trong ô B2, nhập:
=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 yêu cầu Excel trích xuất ba ký tự đầu tiên.
Sử dụng hàm LEFT trong Excel để trích xuất 3 ký tự đầu tiên của mã code
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)
Áp dụng hàm RIGHT trong Excel để lấy 3 ký tự cuối cùng của mã ID
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 đó, số 4 yêu cầu Excel bắt đầu trích xuất từ ký tự thứ tư, và số 1 yêu cầu Excel chỉ trích xuất một ký tự duy nhất.
Trích xuất ký tự xếp hạng ở giữa chuỗi bằng hàm MID trong Excel
Bây giờ, bạn đã thành công tách dữ liệu trong Excel từ Cột A thành ba cột riêng biệt.
Sử Dụng 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.
Trong ví dụ này, giả sử bạn có bảng Excel này chứa các độ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.
Dữ liệu danh sách đội NFL cần tách thành khu vực và tên đội bằng Power Query Editor
Đầu tiên, chọn bất kỳ ô nào trong dữ liệu, và trên tab Data của thanh công cụ (ribbon), nhấp vào From Table/Range trong nhóm Get & Transform Data.
Chọn tùy chọn From Table/Range trên tab Data để đưa dữ liệu vào Power Query Editor
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 (trong ví dụ này là cột “NFL Team”), và nhấp Split Column > By Delimiter.
Thực hiệ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à một khoảng trắng—và xem xét dữ liệu để tìm ra các dấu phân cách mà 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 bởi dấu phân cách Right-most delimiter (phân cách bên phải nhất).
Cấu hình hộp thoại Split Column By Delimiter trong Power Query: chọn khoảng trắng 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, nhấp đúp vào tiêu đề cột mới để đổi tên chúng thành “Region” và “Team Name” (hoặc tên phù hợp).
Đổi tên các cột đã tách trong Power Query Editor của Excel để dễ quản lý
Cuối cùng, nhấp vào nửa trên của biểu tượng Close & Load ở 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.
Hoàn tất quy trình bằng cách chọn Close And Load trong Power Query Editor Excel
Nếu dữ liệu gốc thay đổi, hãy nhớ nhấp Refresh trong tab Table Design 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 trong Excel là một kỹ năng quan trọng giúp tối ưu hóa việc quản lý và phân tích thông tin. Qua bài viết này, tinhoccongnghe.net đã hướng dẫn bạn bốn phương pháp chính để thực hiện tác vụ này một cách hiệu quả: từ công cụ Text to Columns thân thiện, Flash Fill tự động thông minh, đến các hàm Excel mạnh mẽ như TEXTSPLIT, TEXTBEFORE/TEXTAFTER, LEFT/MID/RIGHT, và cuối cùng là Power Query Editor tiên tiến.
Mỗi phương pháp đều có những ưu điểm riêng, phù hợp với các tình huống và yêu cầu khác nhau. Việc lựa chọn công cụ phù hợp sẽ phụ thuộc vào cấu trúc dữ liệu của bạn và mức độ phức tạp của tác vụ. Dù bạn cần xử lý dữ liệu đơn giản hay phức tạp, Excel luôn cung cấp các giải pháp mạnh mẽ để giúp bạn sắp xếp dữ liệu một cách linh hoạt và chính xác.
Việc tách cột trong Excel không chỉ là cách duy nhất để sắp xếp lại dữ liệu. Bạn cũng có thể hợp nhất dữ liệu từ hai cột thành một, chia các hàng xen kẽ thành hai cột, hoặc xoay dữ liệu từ dọc sang ngang. Bất kể bạn muốn tổ chức dữ liệu của mình theo cách nào, Excel luôn có một công cụ hoặc hàm tích hợp sẵn để hỗ trợ bạn trên hành trình khám phá dữ liệu. Hãy tiếp tục theo dõi tinhoccongnghe.net để cập nhật thêm nhiều thủ thuật và hướng dẫn Excel chuyên sâu, giúp bạn trở thành một chuyên gia xử lý dữ liệu thực thụ!
