VietNetwork.Vn

Bạn có nhiều sự lựa chọn!

Chia nội dung của một ô thành nhiều cột theo cách thủ công trong Microsoft Excel sẽ mất quá nhiều thời gian và có thể dẫn đến lỗi. May mắn thay, chương trình cung cấp nhiều cách—từ các công cụ tích hợp và quy trình tự động đến các hàm dễ sử dụng—để thực hiện tác vụ sắp xếp dữ liệu này.


1. Sử dụng Công cụ Chuyển văn bản thành Cột

Một cách để chia dữ liệu thành nhiều cột trong Microsoft Excel là sử dụng công cụ Text To Columns tích hợp sẵn. Phương pháp này rất tiện lợi nếu bạn thích làm việc trong hộp thoại hướng dẫn bạn thực hiện quy trình.

Ví dụ, giả sử bạn muốn tách tên trong cột A thành họ và tên trong cột B và C tương ứng.


Để thực hiện việc này, trước tiên, hãy chọn các ô trong cột A và trong tab Dữ liệu trên ruy-băng, nhấp vào "Văn bản thành cột".


Trong Trình hướng dẫn Chuyển văn bản thành cột, chọn "Phân cách" và nhấp vào "Tiếp theo".


Dấu phân cách là một ký tự, ký hiệu 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à khoảng trắng, vì vậy hãy chọn cả hai tùy chọn này. Bạn có thể xem điều này sẽ ảnh hưởng đến dữ liệu của mình như thế nào trong bản xem trước ở cuối hộp thoại. Nếu bạn hài lòng, hãy nhấp vào "Tiếp theo".


Tiếp theo, xóa mọi thông tin trong trường Đích, chọn ô mà bạn muốn chia dữ liệu—trong trường hợp này là ô B2—và nhấp vào "Hoàn tất".


Nếu bạn muốn thay thế dữ liệu gốc bằng phiên bản đã chia, hãy chọn ô trên cùng bên trái của dữ liệu gốc trong trường Đích. Trong ví dụ này, đó sẽ là ô A2.

Bây giờ, tên đầy đủ được chia 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ì tên đã chia không được liên kết theo bất kỳ cách nào với tên đầy đủ.


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 dấu phân cách sẽ chia những 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.


Để khắc phục điều này, chỉ chọn dấu phẩy phân cách—không phải dấu cách phân cách—trong Trình hướng dẫn chuyển văn bản thành cột.


Tuy nhiên, sau khi quá trình phân tách hoàn tất, bạn sẽ thấy tên được đặt sau một khoảng trắng vì bạn không yêu cầu Excel coi khoảng trắng là dấu phân cách.


Để xóa các khoảng trắng này, hãy mở rộng bảng thêm một cột về bên phải để tạo một cột khác cho các tên đã sửa. Sau đó, trong ô D2, nhập:

=TRIM([@[First name]])
trong đó hàm TRIM xóa tất cả các khoảng trắng khỏi chuỗi văn bản (trừ khoảng trắng giữa các từ) và [@[Tên]] là tham chiếu có cấu trúc đến cột trong bảng có tên là "Tên".


Khi bạn nhấn Enter, tên đầu tiên sẽ xuất hiện trong cột D, nhưng lần này, không có khoảng trắng ở đầu. Ngoài ra, vì dữ liệu nằm trong bảng Excel được định dạng, công thức được tự động sao chép trong các ô còn lại của cột.


Tiếp theo, chọn tên mới tạo 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", rồi Enter.


Cuối cùng, xóa cột chứa khoảng trắng trước tên (C) và cột gốc chứa tên đầy đủ (A) để hoàn thiện bảng của bạn.


2. Sử dụng công cụ Tự động điền Flash

Microsoft Excel có nhiều công cụ giúp tự động hóa các quy trình tẻ nhạt và Flash Fill là một trong những công cụ hữu ích nhất.

Nếu sử dụng Trình hướng dẫn Text To Columns để chia dữ liệu thành nhiều cột 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ề bản chất, đây là 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 phần tử chia đầu tiên xuất hiện (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.


Tiếp theo, trong tab Dữ liệu trên ruy-băng, nhấp vào "Điền nhanh".

Nếu bạn muốn sử dụng phím tắt của Microsoft Excel, hãy nhấn Ctrl+E.


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 vào dữ liệu còn lại trong phạm vi. Trong ví dụ này, nó điền thành công phần còn lại của họ từ dữ liệu gốc xuống cột B.


Sau đó, lặp lại quy trình này cho tên đầu tiên 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 được sử dụ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 đ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 chia 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ẽ.

3. Sử dụng các hàm Excel tích hợp sẵn

Một cách khác để chia dữ liệu 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 cách này, hãy nhớ rằng các giá trị chia 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 bạn muốn xóa dữ liệu gốc.

3.1. TEXTSPLIT

Hàm TEXTSPLIT của Excel chia toàn bộ nội dung trong một ô thành các cột khác nhau theo 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.

Trong khi các hàm mảng động tạo ra các mảng tràn 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 đượ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.

Trong ví dụ này, giả sử bạn muốn chia chữ hoa và chữ thường trong cột A thành cột B và C bằng một công thức duy nhất.


Để thực hiện việc 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 đượ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.


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 màu xanh 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 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. Để thực hiện việc này, hãy nhấp và kéo tay cầm điền ở góc dưới bên phải của ô B2 xuống cuối phạm vi.


3.2. 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 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 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 bằng thủ phủ của các tiểu bang, trong ô 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à 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à khoảng trắng nên được coi là dấu phân cách. Tuy nhiên, lần này, chỉ có 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.


Vì vậy, để trích xuất họ 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, 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 ở các cột liền kề.

3.3. LEFT, MID, và RIGHT

Các hàm LEFT, MID và RIGHT của Excel cho phép bạn chia nội dung của ô tại các vị trí nhất định trong chuỗi ký tự. Điều này đặc biệt hữu ích nếu bạn có tập dữ liệu gồm các giá trị nhất quán về mặt cấu trúc, 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 một thứ hạng. Mục tiêu của bạn là chia 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, trong ô B2, hãy 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à 3 yêu cầu Excel trích xuất ba ký tự đầu tiên.


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)

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. Để thực hiện việc này, hãy nhập:

=MID([@Code],4,1)
trong đó 4 yêu cầu Excel bắt đầu trích xuất từ ký tự thứ tư và 1 yêu cầu Excel chỉ muốn trích xuất một ký tự duy nhất.


Bây giờ, bạn đã chia thành công dữ liệu trong cột A thành ba cột riêng biệt.

4. Sử dụng Trình soạn thảo Power Query

Nhiều người cho 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 để phân chia dữ liệu thành nhiều cột.

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 chia các đội này thành các khu vực và tên đội tương ứng.


Đầu tiên, chọn bất kỳ ô nào trong dữ liệu và trong tab Dữ liệu trên ruy-băng, nhấp vào "Từ Bảng/Phạm vi" trong nhóm Lấy và chuyển đổi dữ liệu.


Thao tác này sẽ khởi chạy Power Query Editor, nơi điều kỳ diệu xảy ra!

Nhấp chuột phải vào tiêu đề của cột mà bạn muốn tách và nhấp vào Tách cột > Theo dấu phân cách.


Sau đó, trong hộp thoại, hãy chọn dấu phân cách—trong trường hợp này, đó là khoảng trắng—và xem lại dữ liệu để tìm ra dấu phân cách nào bạn muốn dữ liệu được chia. 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ừ, nên văn bản cần được chia theo dấu phân cách ngoài cùng bên phải.


Bây giờ, khi bạn nhấp vào "OK", bạn sẽ thấy tên nhóm đượ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.


Cuối cùng, nhấp vào nửa trên của biểu tượng "Đóng và tải" ở góc trên bên trái của Trình soạn thảo Power Query và dữ liệu vừa tách sẽ mở 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 "Làm mới" trong tab Thiết kế bảng sau khi chọn bảng được tạo thông qua Trình soạn thảo Power Query. Ví dụ: nếu bạn thêm một nhóm khác vào cuối bảng, Excel sẽ tự động chia nhóm này theo bước bạn đã tạo trong Trình soạn thảo Power Query.

Chia dữ liệu thành nhiều cột không phải là cách duy nhất để sắp xếp lại dữ liệu trong Excel. Ví dụ, bạn có thể hợp nhất dữ liệu từ hai cột thành một cột, chia các hàng xen kẽ thành hai cột hoặc biến dữ liệu theo chiều dọc thành chiều ngang. Bất kể bạn muốn sắp xếp dữ liệu của mình như thế nào, có thể có một công cụ Excel tích hợp sẽ giúp bạn thực hiện điều đó.