Nhập dữ liệu vào Excel từ các nguồn khác có thể thực sự khiến bạn đau đầu, đặc biệt nếu bạn đang sao chép và dán từ một nguồn trên Internet. Dữ liệu được xuất từ máy tính lớn;
Các tùy chọn Nhập và Phân tích cú pháp của Excel sử dụng Trình hướng dẫn để hướng dẫn bạn qua các quy trình này. Chỉ cần làm theo hướng dẫn trên màn hình. Sau khi dữ liệu được nhập, thách thức là làm thế nào để phân tích cú pháp dữ liệu đúng cách, đặc biệt nếu thông tin trong mỗi trường được phân tích cú pháp có nhiều từ, nhiều dấu chấm câu, ký tự đặc biệt hoặc các biến chứng khác
Nhập dữ liệu
1. Mở một bảng tính Excel trống và sử dụng các hướng dẫn từ “Mẹo chuyên nghiệp về Excel. Nhập và phân tích dữ liệu. ” Lưu ý rằng tất cả thông tin nhập vào một trường, vì vậy bạn phải phân tích cú pháp dữ liệu để chia chuỗi văn bản này thành các trường riêng biệt. Lưu ý rằng việc phân tích cú pháp dữ liệu được sử dụng trong bài viết tham chiếu rất dễ dàng vì mỗi trường chứa các bản ghi giống nhau
2. Ví dụ của chúng tôi, chúng tôi sẽ chỉ nhập một tá bản ghi mà bạn có thể chỉnh sửa theo cách thủ công trong vài phút. Nhưng kỹ thuật mà tôi sẽ chỉ cho bạn sẽ hiệu quả để chỉnh sửa hàng nghìn bản ghi. Ghi chú. Trong phần còn lại của bài viết này, giả sử bảng tính của bạn có 1.000 bản ghi. Các trường cho cơ sở dữ liệu này là Tên, Tiêu đề, Vị trí chi nhánh, Thành phố và Bang;
Tất cả sáu trường được nhập vào một cột
Trước khi bạn phân tích cú pháp cơ sở dữ liệu
Nếu bạn phân tích cú pháp cơ sở dữ liệu ngay bây giờ, kết quả sẽ là một mớ hỗn độn, bởi vì một số tên có lời chào hoặc tiêu đề. Một số có tên đệm hoặc tên viết tắt, một số có hậu tố và/hoặc công nhận. Ngoài ra, các trường khác chứa từ một đến năm từ trên mỗi bản ghi. Và, bởi vì dấu phân cách duy nhất là khoảng trắng, Excel sẽ tạo một trường hoặc cột mới cho mỗi từ. Dọn dẹp mớ hỗn độn này bao gồm một số bước.
Cơ sở dữ liệu được phân tích cú pháp trước khi tổ chức lại
A. Đầu tiên, trích xuất từ cuối cùng
1. Hãy trích xuất từ cuối cùng trong mỗi chuỗi văn bản, sau đó xóa từ đó khỏi cơ sở dữ liệu gốc. Trong trường hợp này, đó là trạng thái
2. Di chuyển con trỏ đến ô P5 và nhập công thức này
=TRIM[RIGHT[SUBSTITUTE[A5,” “,REPT[“ “,100]],100]]
3. Sao chép công thức từ P5 đến P6 đến P1000
4. Tiếp theo, đánh dấu phạm vi, chọn Sao chép, di chuyển một cột sang bên phải và chọn Dán Giá trị. Nếu bạn không thực hiện việc này, thông tin "trạng thái" mà bạn trích xuất sẽ thay đổi dựa trên thông tin mới, được phân tích cú pháp trong A5
5. Xóa cột P
Trích xuất từ cuối cùng trong mỗi chuỗi văn bản
B. Xóa từ cuối cùng trong mỗi bản ghi/chuỗi văn bản
1. Di chuyển con trỏ của bạn đến cột B và nhập công thức này
=LEFT[TRIM[A5],FIND[“~”,SUBSTITUTE[A5,” “,”~”,LEN[TRIM[A5]]-LEN[SUBSTITUTE[TRIM[A5],” “,””]]]
Lưu ý rằng tất cả dữ liệu từ A5 ngoại trừ Trạng thái hiện nằm trong B5
2. Sao chép công thức này từ B5 xuống B6 đến B1000
3. Lưu ý rằng cột B bây giờ là một công thức, cột này phải được chuyển đổi thành văn bản
4. Tiếp theo, đánh dấu phạm vi [B5. B1000], chọn Sao chép, di chuyển một cột sang bên phải [cột C] và chọn Dán Giá trị
5. Xóa cột A và B. Mọi thứ di chuyển sang trái và cơ sở dữ liệu một lần nữa nằm trong cột A
Xóa từ cuối cùng trong mỗi chuỗi văn bản
C. Sử dụng dữ liệu lặp đi lặp lại để trích xuất thành phố
Phần thông tin nhất quán duy nhất trong cơ sở dữ liệu này là từ Chi nhánh. Hãy sử dụng thông tin đó để trích xuất thành phố. Có một số cách để thực hiện chức năng này. Đây chỉ là một lựa chọn
1. Trước tiên, hãy chèn một ký tự chẳng hạn như dấu cộng [+] vào công thức của chúng tôi để đơn giản hóa quy trình Tìm & Thay thế
2. Di chuyển con trỏ của bạn đến vị trí Trang đầu
3. Trong tab Trang chủ, nhấp vào nút Tìm & Chọn. Chọn Thay thế từ danh sách
4. Trong hộp thoại Tìm & Thay thế, hãy nhập từ Chi nhánh theo sau là khoảng trắng trong hộp Tìm trường gì
5. Trong hộp trường Thay thế bằng, hãy nhập từ Chi nhánh, sau đó là khoảng trắng và sau đó là dấu +
6. Để kiểm tra kết quả của bạn, hãy nhấp vào nút Tìm Tiếp theo, sau đó nhấp vào nút Thay thế. Nếu bạn tự tin rằng kết quả của mình sẽ chính xác, chỉ cần nhấp vào nút Thay thế Tất cả
7. Lưu ý rằng Excel đã đặt dấu + giữa mỗi thành phố và từ Chi nhánh
8. Tiếp theo, nhập công thức này vào ô M5. =RIGHT[A5,LEN[A5]-FIND[“+”,A5]]
9. Đánh dấu phạm vi và sao chép công thức trong M5 xuống từ M6 đến M1000
10. Lưu ý rằng cột M bây giờ là một công thức, phải được chuyển đổi thành văn bản
11. Tiếp theo, đánh dấu phạm vi [M5. M1000], chọn Sao chép, di chuyển một cột sang trái [cột L] và chọn Dán Giá trị
12. Xóa cột M [mọi thứ di chuyển sang trái], sau đó mở rộng cột L để chứa thành phố dài nhất trong phạm vi
Sử dụng dữ liệu lặp đi lặp lại để trích xuất thành phố
D. Xóa thành phố khỏi chuỗi văn bản cơ sở dữ liệu
1. Di chuyển con trỏ đến ô B5 và nhập công thức này
=LEFT[A5, TÌM[“+”, A5&”+”]-1]
Lưu ý rằng tất cả dữ liệu từ A5 ngoại trừ Thành phố hiện nằm trong B5
2. Sao chép công thức này từ B5 xuống B6 đến B1000
3. Lưu ý rằng cột B bây giờ là một công thức, cột này phải được chuyển đổi thành văn bản
4. Đánh dấu phạm vi [B5. B1000], chọn Sao chép, di chuyển một cột sang bên phải [cột C] và chọn Dán Giá trị
5. Xóa cột A và B. Mọi thứ di chuyển sang trái và cơ sở dữ liệu một lần nữa nằm trong cột A
Lưu ý rằng Thành phố đã chuyển sang cột J và Tiểu bang cư trú ở cột K
Xóa thành phố khỏi chuỗi cơ sở dữ liệu
E. Trước khi bạn phân tích cú pháp, hãy đếm các cột
Sử dụng công thức này để đếm số cột/trường mà Excel sẽ cần để phân tích dữ liệu của bạn
Ghi chú. Chỉ để chắc chắn rằng bạn có đủ không gian làm việc trên bảng tính của mình để thao tác dữ liệu. Bạn luôn có thể Chèn hoặc Xóa cột và hàng sau
1. Trước khi bạn phân tích dữ liệu, hãy di chuyển con trỏ ra ô M5 và nhập công thức này
=IF[LEN[TRIM[A5]]=0,0,LEN[TRIM[A5]]-LEN[SUBSTITUTE[A5,” “,””]]+1]
2. Sao chép công thức từ M5 [ô đầu tiên trong cơ sở dữ liệu của bạn] sang M6 và xuống ô cuối cùng trong cơ sở dữ liệu của bạn; . g. , M1000
3. Đánh dấu phạm vi, chọn Sao chép, di chuyển một cột sang bên phải và chọn Dán Giá trị
4. Tiếp theo, xóa cột M [công thức] và mọi thứ sẽ di chuyển một cột sang trái
5. Sắp xếp các giá trị trong cột M mới theo thứ tự Giảm dần [lớn nhất đến nhỏ nhất]. Số lớn nhất trong phạm vi này hiển thị số cột bạn sẽ cần [để làm việc] khi dữ liệu được phân tích cú pháp. Trong trường hợp này, đó là 11 cột. A đến K, bắt đầu từ Hàng 5
Ghi chú. Bởi vì cột J và K đã được sử dụng và chúng tôi cần một vài cột để làm việc, hãy chèn sáu cột mới vào bất kỳ đâu giữa B và K, đẩy Thành phố và Bang ra ngoài cột P và Q
Sử dụng một công thức để khám phá có bao nhiêu cột sau khi phân tích dữ liệu
Và bây giờ chúng tôi phân tích dữ liệu
Tiếp theo, phân tích cú pháp dữ liệu bằng Trình hướng dẫn Văn bản thành Cột
1. Đánh dấu cơ sở dữ liệu [trong trường hợp này, chỉ cột A]
2. Chọn Dữ liệu > Văn bản thành Cột
3. Trong Trình hướng dẫn chuyển văn bản thành cột [Bước 1], chọn nút Được phân tách, sau đó nhấp vào Tiếp theo
Chọn Phân cách từ Trình hướng dẫn Văn bản thành Cột Bước 1
4. Chọn hộp Space trong Wizard [Bước 2], sau đó nhấp vào Tiếp theo
5. Ở Bước 3, nhấp vào cột đầu tiên và từ bảng Định dạng dữ liệu cột, nhấp vào nút Văn bản. Nhấp vào cột tiếp theo và chọn lại nút Văn bản, cứ như vậy cho đến hết, sau đó nhấp vào Kết thúc
Ghi chú. Nếu cột chứa ngày, hãy nhấp vào nút Ngày. Đối với văn bản, bấm vào nút Văn bản; . Nếu bạn không chắc chắn, hãy sử dụng nút Chung cho tất cả các trường vì nút này chuyển đổi các trường ngày thành ngày, giá trị số thành số và mọi thứ khác thành văn bản. Cũng lưu ý rằng bạn có thể chọn nút Không nhập cột [Bỏ qua] để bỏ qua các trường không cần thiết
Cũng lưu ý địa chỉ ô trong hộp Trường đích. Giá trị mặc định là ô đầu tiên trong cột đầu tiên của phạm vi được đánh dấu của bạn; . g. , $A$5. Mặc định là bình thường. Tuy nhiên, bạn có thể chọn một địa chỉ ô khác, ví dụ: nếu bạn muốn cơ sở dữ liệu nằm ở một nơi khác trên trang tính này, trang tính khác hoặc trong một sổ làm việc khác
Chọn Dấu phân cách và Văn bản—hoặc Chung—cho Định dạng Dữ liệu Cột của bạn
Tiếp theo, sắp xếp dữ liệu
Nó vẫn là một mớ hỗn độn, nhưng nó tốt hơn nhiều so với trước đây. Bây giờ bạn có sáu trường trải rộng trên 11 cột. Tại sao? . Bây giờ chúng ta phải đặt tất cả lại với nhau theo một trình tự hợp lý
A. Sắp xếp và CONCATENATE
1. Sắp xếp cơ sở dữ liệu theo cột A. Lưu ý rằng trường tên không bao giờ vượt quá bốn cột
2. Di chuyển con trỏ của bạn đến L5. Chọn Công thức > Văn bản > CONCATENATE. Trong bảng Concatenate trong cửa sổ hộp thoại Đối số chức năng, có nhiều hộp trường Văn bản. Điền chúng như sau
- Nhấp vào A5 trong Text1
- Nhập khoảng trắng được bao quanh bởi dấu ngoặc kép trong Text2
- Tiếp theo, nhấp vào B5 trong Text3
- Nhập một không gian khác được bao quanh bởi dấu ngoặc kép trong Text4.
- Nhấp vào C5 trong Text5
- Nhập một không gian khác được bao quanh bởi dấu ngoặc kép trong Text6
- Cuối cùng, nhấp vào D5 trong Text7, sau đó nhấp vào OK.
- Hoặc, nhập công thức này trong L5. =CONCATENATE[A5,” “,B5,” “,C5,” “,D5]
Một số kết quả sẽ chứa toàn bộ trường tên. Những người khác sẽ bao gồm tên cộng với một phần của tiêu đề
4. Chèn một bảng tính mới [WS mới] và sao chép cột L. Di chuyển sang trang tính mới và Dán Giá trị vào A5
5. Đồng thời sao chép cột P [Thành phố] và Q [Bang] sang cột D và E trong WS Mới
Sử dụng hàm Concatenate để nối các trường đã phân tích cú pháp
Lưu ý rằng chỉ có chín tiêu đề. CEO, CTO, CFO, COO, Chủ tịch, Quản lý cấp cao, Quản lý, Giám đốc và Trưởng phòng. Một số chức danh gắn liền với các phòng ban như Trưởng phòng Marketing, Giám đốc Quan hệ công chúng, CTO Khoa học thông tin, v.v. Vì vậy, để an toàn, chúng tôi cũng sẽ thêm các phòng ban hiện hành vào công thức của mình
Cũng lưu ý. Tôi không biết số hàm Thay thế tối đa được phép cho mỗi công thức. Tôi biết rằng vào năm 2003, nó bị giới hạn ở bảy phiên bản, nhưng trong các phiên bản Office 2007, 2010 và 2016, nó còn nhiều hơn nữa. Đối với mục đích của tôi, tôi giới hạn ở mức 10, bởi vì bất cứ điều gì nhiều hơn thế sẽ quá khó để chỉnh sửa
B. Sử dụng chức năng SUBSTITUTE để trích xuất tiêu đề và bài báo
Điều đó nói rằng, hãy nhập công thức sau vào ô D5 của bảng tính WS mới
=SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[A5,”COO”,””],”CFO”,””],”CTO”,””],”CEO”
6. Nhập công thức này vào ô G5
=SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[D5,”Nội vụ”,””],”Quan hệ công chúng”,””],”Kinh doanh”,””],”Tài chính”
7. Công thức đầu tiên [đề cập đến A5] được nhập vào D5 và xóa các tiêu đề. Công thức thứ hai [đề cập đến D5] được nhập vào G5 và loại bỏ các bộ phận và mạo từ [và, của, v.v. ]
8. Sao chép công thức từ G5 đến G6 đến G1000
9. Đánh dấu phạm vi, chọn Sao chép, chuyển đến ô A5 và chọn Dán Giá trị. Bây giờ bạn có tên được tách ra khỏi tiêu đề
10. Xóa cột D và G
Sử dụng chức năng SUBSTITUTE để xóa tiêu đề và bài viết
C. CONCATENATE các trường còn lại
1. Quay lại cơ sở dữ liệu được phân tích cú pháp ban đầu. Các trường trải dài từ A5 đến K5
2. Di chuyển con trỏ của bạn đến L5 và nhập công thức này. =CONCATENATE[C5,” “,D5,” “,E5,” “,F5,” “,G5,” “,H5,” “,I5,” “,J5,” “,K5]. Hàm này hợp nhất các tiêu đề và nhánh
3. Sao chép công thức trong L5 đến L6 đến L1000
4. Tiếp theo, di chuyển con trỏ đến ô R5 và nhập công thức này. =TRIM[RIGHT[SUBSTITUTE[TRIM[L5],” “,REPT[“ “,60]],180]]
Lưu ý quan trọng. Hai công thức này cũng hoạt động cho bước này
=MID[L5,[LEN[R5]+2],99]
=IF[ISERROR[TÌM KIẾM[R5,L5,1]],L5,RIGHT[L5,LEN[L5]-LEN[R5]]]
5. Sao chép công thức này trong R5] sang R6 đến R1000. Và bây giờ bạn có thông tin chi nhánh
6. Đánh dấu R5 đến R1000 và chọn Sao chép. Di chuyển đến bảng tính WS mới và Dán Giá trị vào cột C
Trích xuất thông tin Chi nhánh từ cơ sở dữ liệu
D. Trích xuất tiêu đề, và thực hiện
Tất cả những gì còn lại phải làm bây giờ là trích xuất các tiêu đề và sao chép chúng vào WS Mới
1. Di chuyển con trỏ đến ô T5. [Lưu ý rằng các vị trí ô là tùy ý]
2. Nhập công thức này. =SUBSTITUTE[L5,R5,””]
3. Sao chép công thức từ T5 xuống T6 đến T1000
4. Lưu ý rằng các tiêu đề được trích xuất từ các tiêu đề kết hợp và tên chi nhánh trong cột L
Trích xuất tiêu đề từ các trường kết hợp trong cột L
5. Sao chép các tiêu đề từ cột T vào cột B của WS Mới. Đảm bảo sử dụng Dán > Đặc biệt > Giá trị
Giờ đây, tất cả các cột có nhiều từ được tách thành các trường chính xác để bạn có thể làm việc với dữ liệu theo trường—tạo danh sách gửi thư cho Trộn Thư, in phong bì hoặc nhãn, phối vào Outlook hoặc sử dụng thông tin trong cơ sở dữ liệu khác.