programming excel

Làm cách nào để chia dữ liệu thành nhiều hàng trong excel?

Bạn có thể cần chia một giá trị trong ô của mình thành hai hoặc nhiều cột theo dấu cách, ký tự hoặc dấu phân cách khác. Điều này có thể được thực hiện dễ dàng với một vài cú nhấp chuột. Mặt khác, một số người dùng cần chia ô thành các hàng. Mặc dù Excel không có sẵn chức năng này nhưng bạn có thể sử dụng một vài hàm để thực hiện công việc. Đọc tiếp để tìm hiểu tất cả các tùy chọn có sẵn để tách ô trong Excel

Mục lục

Làm cách nào để tách ô trong Excel?

Bạn có thể tách ô trong Excel với các tùy chọn sau

Sử dụng một chức năng bản địa
Sử dụng kết hợp các hàm Excel
Truy vấn nguồn
Visual Basic cho Ứng dụng [VBA]

Đối với bài viết này, chúng tôi đã quyết định nhóm các trường hợp sử dụng không phải theo tùy chọn mà theo kết quả mong đợi. Vì vậy, chúng tôi sẽ bao gồm hai nhiệm vụ sử thi. tách ô thành cột và tách ô thành hàng. Mỗi tác vụ này sẽ bao gồm các tùy chọn để phân chia theo các dấu phân cách khác nhau và các yêu cầu cụ thể khác.

Đối với các ví dụ bên dưới, chúng tôi đã nhập dữ liệu từ các nguồn khác nhau như Airtable, Google Sheets và BigQuery bằng Coupler. io. Đó là một giải pháp mạnh mẽ để đưa dữ liệu vào Excel từ nhiều ứng dụng và API web mà không cần viết mã. Lợi ích chính là bạn có thể đồng bộ hóa sổ làm việc của mình với nguồn dữ liệu theo lịch trình tùy chỉnh.

Kiểm tra tích hợp Microsoft Excel có sẵn

Hãy kiểm tra từng tùy chọn để bạn có thể chọn tùy chọn phù hợp nhất với nhiệm vụ của mình

Cách tách một ô thành hai ô trong Excel

Chúng ta sẽ bắt đầu với một ví dụ đơn giản về tách tên đầy đủ,

8054 Old Shore Parkway
Houston
United States

1, thành các ô họ và tên. Chuyển đến dải băng Dữ liệu và nhấp vào Văn bản thành Cột

Một trình hướng dẫn sẽ mở ra, nơi bạn sẽ cần thực hiện ba bước

Chọn cách chia dữ liệu của bạn. bằng dấu phân cách hoặc bằng chiều rộng cố định

Đặt dấu phân cách hoặc độ rộng trường tùy thuộc vào phương pháp tách mà bạn đã chọn trước đó. Trong ví dụ của chúng tôi, chúng tôi cần chia ô theo dấu cách làm dấu phân cách

Chọn định dạng ngày và ô đích. Nếu bạn muốn giữ giá trị nguồn, hãy chọn một ô khác làm đích

Khi bạn nhấp vào “Hoàn tất“, ô của bạn sẽ được chia thành số lượng ô tùy thuộc vào số lượng dấu phân cách trong giá trị nguồn của bạn. Trong ví dụ của chúng tôi, chúng tôi có một dấu phân cách, vì vậy ô được chia thành hai ô

Tách văn bản trong ô Excel thành nhiều ô theo khoảng trắng

Nếu một ô họ và tên gồm từ 3 tên trở lên thì số ô được tách sẽ nhiều hơn. Ví dụ: hãy tách tên đầy đủ của Pablo Picasso bằng cách sử dụng khoảng trắng làm dấu phân cách

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

Kết quả là chúng ta đã chia văn bản trong ô Excel thành nhiều ô [20. ] bằng dấu cách.

Excel chia ô theo ký tự

Việc tách các ô theo ký tự có thể hữu ích nếu bạn cần tách biệt, chẳng hạn như tên người dùng và tên miền trong địa chỉ email. Giả sử chúng ta có một người dùng hư cấu với địa chỉ email sau

8054 Old Shore Parkway
Houston
United States

Trong trường hợp này, dấu phân cách là một ký tự.

8054 Old Shore Parkway
Houston
United States

3. Vì vậy, chúng ta cần phải xác định điều này.

của bạn đi

Excel tách dữ liệu trong ô bằng ngắt dòng

Điều gì sẽ xảy ra nếu ô của bạn chứa một vài dòng như địa chỉ và bạn muốn mỗi dòng nằm trong ô riêng của mình?

8054 Old Shore Parkway
Houston
United States

Để tách chuỗi này, bạn cần chỉ định ngắt dòng làm dấu phân cách. Để thực hiện việc này, hãy chọn Khác làm dấu phân cách và nhấn Ctrl + J

Đây là kết quả

Cách tách ô đã gộp trong Excel

Tách các ô đã hợp nhất là một khái niệm hơi khác, vì nó không ảnh hưởng đến nội dung của các ô. Bạn có thể thực hiện việc này dễ dàng bằng nút Hợp nhất & Trung tâm

Chọn các ô đã hợp nhất, mở các tùy chọn thả xuống và nhấp vào Hủy hợp nhất các ô

Tách ô thành hàng trong Excel

Thật không may, không có một nút hoặc chức năng nào để chia ô thành hàng hoặc chia ô theo chiều dọc trong Excel. Tuy nhiên, bạn có thể làm điều này với sự trợ giúp của các hàm Excel hoặc Power Query. Nhưng trước tiên, hãy xem phương pháp dễ dàng này để tách các ô thành các hàng

Tách ô Excel thành hai hàng dễ dàng

Bạn sẽ cần thực hiện hai bước

Tách ô của bạn bằng nút Văn bản thành Cột như chúng tôi đã giải thích trước đây.

Sao chép các ô kết quả và dán chúng bằng Paste special => Transpose

Đây là cách đơn giản nhất để tách ô thành hàng trong Excel nhưng thủ công. Hãy cùng khám phá một số tùy chọn để tự động hóa quy trình

Tách ô theo chiều dọc trong Excel bằng công thức

Hãy lấy ví dụ trước về việc tách tên đầy đủ.

8054 Old Shore Parkway
Houston
United States

Chúng ta sẽ cần hai công thức cho mỗi hàng

Đầu tiên

= LEFT[cell, FIND[" ",cell] - 1]

Công thức này trích xuất một chuỗi trước dấu phân cách đã chỉ định.

8054 Old Shore Parkway
Houston
United States

2. Thứ hai

= RIGHT[cell, LEN[cell] - FIND[" ",cell]]

Công thức này trích xuất một chuỗi sau dấu phân cách đã chỉ định.

Nếu bạn có hai hoặc nhiều dấu phân cách, thì cả hai công thức sẽ chỉ hoạt động với dấu phân cách đầu tiên, như thế này

Nếu vì lý do nào đó bạn cần tách một ô bằng dấu phân cách thứ hai hoặc thứ ba, bạn cần cập nhật công thức TÌM, lưu ý logic sau

=FIND["delimiter",cell,starting_position]

```
8054 Old Shore Parkway
Houston
United States
```
6 – chỉ định vị trí bắt đầu [ký tự] để bỏ qua các dấu phân cách không cần thiết

Trong ví dụ của chúng tôi, để chia ô bằng dấu phân cách thứ hai, các công thức sẽ như thế này

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

Chia ô trong Excel thành nhiều hàng

Thật không may, những công thức này không áp dụng cho trường hợp “Pablo Picasso”, trong đó số lượng dấu phân cách là 19. Đối với những trường hợp như vậy, công thức sau hoạt động tốt hơn

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

Điều này chuyển đổi nội dung ô thành một chuỗi XML và sau đó trích xuất dữ liệu từ nó. Công thức này phải được thực thi dưới dạng công thức mảng; . Đây là cách nó hoạt động với tên đầy đủ của Pablo Picasso

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

Bạn cũng có thể sử dụng công thức này để trích xuất các đoạn cụ thể trong chuỗi của mình bằng cách chỉ định chúng như sau.

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

Ví dụ: để trích xuất tên thứ ba từ tên đầy đủ của Picasso, chúng tôi cập nhật công thức của mình như sau

8054 Old Shore Parkway
Houston
United States

Cách tách ô thành hàng hoặc cột trong Excel một cách linh hoạt mà không cần công thức

Nếu bạn không muốn làm rối tung các công thức cho các tác vụ phân tách của mình, nhưng bạn muốn có một cách tiếp cận năng động, hãy xem phương pháp Power Query. Hãy xem ví dụ về Pablo Picasso của chúng tôi

Chuyển đến Dữ liệu => Từ Bảng

Chọn ô có dữ liệu của bạn và nhấp vào “OK“

Sau đó, Power Query Editor sẽ mở. Sau đó chọn Tách cột => Theo dấu phân cách

Chọn dấu phân cách của bạn, nhấp vào “Tùy chọn nâng cao“, chọn Chia thành các hàng và chọn “Không” làm Ký tự trích dẫn

Dữ liệu sẽ được chia thành các hàng. Bây giờ bạn cần lưu và tải nó. Đối với điều này, nhấp vào
- “Close and Load” để tải dữ liệu trong một trang tính mới
- “Close & Load To…” để chỉ định đích tùy chỉnh cho dữ liệu của bạn

của bạn đi

Bây giờ, mọi giá trị được thêm vào cột A sẽ được chia thành các hàng theo khoảng trắng. Chỉ cần nhấp vào làm mới để xem các giá trị phân chia

Hãy xem, với Power Query, bạn cũng có thể chia các ô thành các cột bằng các dấu phân cách khác nhau

Thưởng. Cách tách ô theo đường chéo trong Excel

Chia theo đường chéo, người dùng thường có nghĩa là chia ô có đường viền chéo như thế này

Điều này không chia một ô thành các cột hoặc hàng, nhưng tạo ra sự phân tách trực quan của hai giá trị trong ô. Đây là cách bạn có thể làm điều này

Nhấp chuột phải vào ô bạn muốn chia theo đường chéo và chọn Format Cells…

Trong cửa sổ hộp thoại mở, chuyển đến tab Border và nhấp vào đường viền chéo.

Bấm OK và bạn sẽ thấy một đường chia ô đã chọn theo đường chéo. Nhưng đây không hoàn toàn là cách chúng ta muốn

Để khắc phục điều này, chúng ta cần gán hiệu ứng phông chữ Superscript cho một giá trị trong ô và Subscript cho giá trị còn lại. Chọn một từ – trong trường hợp của chúng tôi, đây là “Mithun“. Sau đó chuyển đến tab Trang chủ và nhấp vào Cài đặt Phông chữ – một mũi tên nhỏ ở góc dưới cùng bên phải của phần Phông chữ.

Trong hộp thoại, đánh dấu vào hiệu ứng phông chữ Superscript và nhấn OK.

Đây là cách nó trông bây giờ

Bây giờ, làm tương tự với giá trị thứ hai bằng tùy chọn Subscript

Và ở đó bạn đi

Có các tùy chọn khác để tách ô trong Excel không?

Trong Excel không có chức năng chuyên dụng để tách dữ liệu như SPLIT trong Google Sheets. Tuy nhiên, bằng cách sử dụng Excel VBA, bạn có thể xây dựng một chức năng VBA tùy chỉnh để giải quyết các tác vụ phân tách cụ thể của mình. Điều này yêu cầu kiến thức về Microsoft Visual Basic for Applications.

Tuy nhiên, đối với hầu hết các trường hợp, các tùy chọn được mô tả trước đây sẽ thực hiện công việc. Chúc bạn tách dữ liệu thành công