Tách dữ liệu - Mẹo Excel

Mục lục

Cách tách một cột dữ liệu Excel thành hai cột. Cách phân tích cú pháp dữ liệu trong Excel.

Xem video

  • Phương pháp đầu tiên của Bill sử dụng Văn bản thành Cột (tìm thấy trên tab Dữ liệu).
  • Ở bước 1, chọn phân tách. Trong bước 2, chọn một khoảng trắng. Bỏ qua bước 3 bằng cách nhấp vào kết thúc.
  • Văn bản sẽ tách ra ở mỗi khoảng trắng, vì vậy bất kỳ thứ gì có ba từ sẽ kết thúc trong 3 ô. Đặt chúng lại với nhau bằng =TEXTJOIN(" ",True,B2:E2)hoặc
  • với =B2&" "&C2&" "&D2
  • Phương pháp đầu tiên của Mike sử dụng Power Query. Power Query là Get & Transform vào năm 2016 hoặc tải xuống miễn phí cho 2010 hoặc 2013.
  • Đầu tiên, chuyển đổi dữ liệu của bạn thành một bảng bằng Ctrl + T. Sau đó, trong Power Query, từ Bảng. Cột Tách, bằng Dấu phân cách. Chọn Dấu cách rồi chọn dấu phân cách ngoài cùng bên trái.
  • Bạn có thể đổi tên cột bằng cách bấm đúp!
  • Close & Load To… và chọn một vị trí mới trên trang tính.
  • Phương pháp thứ hai của Bill là sử dụng Flash Fill. Nhập tiêu đề mới vào A, B & C. Flash Fill sẽ không hoạt động nếu bạn không có tiêu đề! Nhập một mẫu cho hai hàng đầu tiên.
  • Chuyển đến ô trống đầu tiên trong B và nhấn Ctrl + E. Lặp lại cho cột C.
  • Phương pháp thứ hai của Mike là sử dụng các công thức sau:
  • Đối với phần đầu tiên, sử dụng =LEFT(A2,SEARCH(" ",A2)-1)
  • Đối với phần thứ hai, sử dụng =SUBSTITUTE(A2,B2&" ","")

Bản ghi video

(Âm nhạc)

Bill Jelen: Này, chào mừng bạn đã quay trở lại, đã đến lúc dành cho một Podcast Dueling Excel khác. Tôi là Bill Jelen đến từ. Tôi sẽ được tham gia bởi Mike Girvin từ Excel Is Fun. Đây là của chúng tôi

Tập 182: Tách dữ liệu từ một ô để xuất hiện trong hai ô.

Được rồi, câu hỏi hôm nay do Tom gửi. Có cách nào để dễ dàng tách dữ liệu trong một ô để dữ liệu xuất hiện trong hai ô không? Ví dụ, 123 Main Street, anh ta muốn 123 trong một ô và Main Street trong một ô khác; hoặc, Howard và Howard và sau đó là Kết thúc. Tôi đã dành vô số giờ để phân tách loại dữ liệu này. Tôi đánh giá cao nhận được phản hồi từ công ty của bạn trong khi có rất nhiều cách khác nhau để thực hiện việc này.

Điều đầu tiên tôi sẽ làm là chọn tất cả Dữ liệu, Ctrl + Shift + Mũi tên Xuống rồi đến Dữ liệu, Văn bản thành Cột. Văn bản thành Cột ở Bước 1, dữ liệu được phân tách. Nó được phân cách bằng dấu cách và sau đó chỉ cần nhấp vào Kết thúc. Bây giờ, đây là một rắc rối với phương pháp này là nếu bạn có 123 Main Street, nó sẽ kết thúc trong 3 ô thay vì 2 ô. Ồ, Power Query sẽ khiến việc này trở nên dễ dàng hơn rất nhiều nhưng chúng ta đang ở đây. Được rồi, vì vậy những gì tôi sẽ làm là tôi sẽ đi ra xa bên phải của Dữ liệu nơi tôi biết rằng ngoài nơi mọi thứ được xây dựng. Nếu tôi đang sử dụng Office 365, tôi sẽ sử dụng TEXTJOIN. TEXTJOIN, điều tuyệt vời đó, dấu phân cách là một dấu cách. Bỏ qua các ô trống True và sau đó là các ô mà tôi muốn nối với nhau như vậy, và tôi chỉ cần sao chép tất cả các ô đó xuống, Ctrl + V. Tôi sẽ sao chép Ctrl + C rồi đến Trang chủ, Dán,Dán làm Giá trị và tại thời điểm này, tôi có thể xóa 3 cột phụ này.

Ahh, nhưng không ai có Office 365, phải không? Vì vậy, nếu bạn không có Office 365, bạn phải làm = điều này & “” & điều đó, sau đó nếu có thêm “” & điều đó và nếu có nhiều hơn, hãy tiếp tục. Trong trường hợp này, nó vô nghĩa vì không có gì hơn trong D nhưng bạn có ý tưởng. Ctrl + C, sao chép nó xuống hàng dữ liệu cuối cùng, Ctrl + V rồi Ctrl + C, Alt + ESV để tạo các giá trị B đó. Và chúng tôi ở đó, được rồi. Mike hãy xem những gì bạn có.

Mike Girvin: Cảm ơn ,. Này, bạn đã gửi cho tôi một câu hỏi dễ dàng ở đây vì bạn đã đề cập đến Get & Transform Power Query, Văn bản thành Cột cũ chỉ cho phép bạn nói khoảng trắng ở mỗi ký tự, phải không? Chà, nếu chúng ta sử dụng Power Query, chúng ta có thể sử dụng Dấu phân cách đó và nói, "Này, chỉ cần tách ở lần xuất hiện đầu tiên."

Bây giờ, để đưa Dữ liệu này vào Trình soạn thảo Truy vấn, chúng ta phải chuyển đổi nó thành một bảng Excel. Vì vậy, tôi đi tới Chèn, Bảng hoặc tôi sử dụng Ctrl + T. Bảng của tôi có tiêu đề, nút OK được đánh dấu để tôi có thể nhấp vào bảng đó bằng chuột hoặc chỉ cần nhấn Enter. Bây giờ tôi muốn đặt tên cho Bảng này vì vậy tôi sẽ đến đây, OriginalData và Enter. Bây giờ, đây là một bảng Excel, chúng ta có thể tìm đến Dữ liệu và ở đó là Từ Bảng. Điều đó sẽ đưa nó từ Excel vào Trình chỉnh sửa. Cột được chọn: Tab Home Ribbon, chúng ta có thể nói Split Column by Delimiter hoặc đến đây và nhấp chuột phải, Split Column by Delimiter. Từ menu thả xuống, chúng ta có thể nói, này, hãy sử dụng Dấu cách và nhìn vào dấu này Ở dấu phân cách ngoài cùng bên trái. Khi tôi nhấp vào OK, BOOM! Nó đây. Bây giờ, tôi sẽ đặt tên cho cả hai cột này: bấm đúp vào Phần 1 Enter, bấm đúp vào Phần 2 và Enter. Hiện nay,Tôi có thể vào đây hoặc Close & Load, Close & Load To và tôi có thể chọn vị trí đặt cái này. Tôi chắc chắn muốn kết xuất nó dưới dạng Bảng, Trang tính mới, Trang tính hiện có. Đánh dấu điều này, nhấp vào nút thu gọn. Tôi sẽ nói D1, nhấp vào OK sau đó nhấp vào Tải. Và chúng tôi bắt đầu, Đầu ra truy vấn nguồn của chúng tôi.

Được rồi, quay lại.

Bill Jelen: Ồ, Mike, Power Query thật tuyệt vời! Vâng, đó là một cách tuyệt vời để đi. Đây là một cái khác mà nó có thể hoạt động nếu bạn có Excel 2013 hoặc mới hơn.

Và những gì chúng ta sẽ làm là bước ra đây và nói Phần đầu tiên và sau đó là Phần thứ hai. Đảm bảo đặt những tiêu đề này mà nếu bạn không đặt những tiêu đề đó, chúng không phải như vậy nhưng chúng phải có tiêu đề hoặc nó sẽ không hoạt động. Tôi sẽ đặt 123 và Main Street, sau đó chúng tôi sẽ đặt Howard and End, như vậy. Bây giờ chúng ta có một mẫu nhỏ đẹp ở đó, hãy xuất hiện ở đây trong Tab Dữ liệu và Flash Fill là Ctrl + E, nhấn Ctrl + E ngay tại đó và sau đó nhấn Ctrl + E ngay tại đó. Điều tuyệt vời là, chúng ta không phải nối dữ liệu với nhau như trong ví dụ của tôi. Được rồi, Mike, trở lại với bạn.

Mike Girvin: Ding-ding-ding. Đó là người chiến thắng không nghi ngờ gì nữa. Flash Fill là cách để đi đến đó. Lưu ý, chúng tôi không phải chuyển đổi nó thành một bảng hoặc mở bất kỳ hộp thoại nào; chỉ cần nhập một vài ví dụ và sau đó Ctrl + E.

Được rồi, chúng ta có thể làm điều đó với các công thức mặc dù Flash Fill có thể sẽ nhanh hơn. Hãy xem này, mẫu giống như ô danh sách này được sử dụng trong Flash Fill là mọi thứ trước khoảng trắng đầu tiên và sau đó là mọi thứ sau đó. Vậy này, tôi sẽ sử dụng hàm LEFT, Text ở ngay đó và bao nhiêu ký tự từ bên trái? Chà, tôi sẽ tìm kiếm khoảng trống đó - 1 2 3 4 bằng cách sử dụng chức năng TÌM KIẾM, Tìm Văn bản, khoảng trắng và “”, trong đó. Bây giờ, hãy lưu ý rằng Tìm kiếm sẽ đếm trên đầu ngón tay 1 2 3 4 và điều đó sẽ đến không gian mà tôi muốn, không gian đó nên tôi -1) Ctrl + Enter, nhấp đúp và gửi nó xuống. Vì vậy, điều đó luôn có được mọi thứ trước khoảng trống đầu tiên.

Bây giờ, hãy lưu ý rằng chúng tôi đã có văn bản ở đây để tôi có thể sử dụng hàm SUBSTITUTE. Văn bản mà tôi sẽ xem qua là Dữ liệu đầy đủ, Dấu phẩy, Văn bản cũ mà tôi muốn tìm kiếm và sau đó là SUBSTITUTE. Không có gì gần như là 1 2 3. Tôi thực sự muốn thêm Dấu cách mà tôi vừa lấy ra trong công thức trước đó, trở lại. Bây giờ, nó sẽ tìm kiếm 1 2 3, Dấu cách và sau đó là Howard, Dấu cách, v.v., Dấu phẩy và sau đó văn bản mới mà tôi muốn thay thế. Vâng, để nói với SUBSTITUTE rằng bạn không muốn thay thế nó bằng gì, bạn nói "" không có khoảng trắng ở giữa, Dấu ngoặc đơn đóng và điều đó sẽ hoạt động. Ctrl + Enter, nhấp đúp và gửi nó xuống. Ổn thỏa? Chỉ cần ném nó trở lại.

Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.

Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.

My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.

And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.

For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.

Được rồi, tôi muốn cảm ơn mọi người đã ghé qua. Chúng tôi sẽ gặp lại bạn vào lần tới với một Podcast khác của Dueling Excel và Excel thật thú vị.

Tải tập tin

Tải file mẫu tại đây: Duel182.xlsm

thú vị bài viết...