Những từ sai chính tả, khoảng trắng cứng đầu, tiền tố không mong muốn, trường hợp không đúng và các ký tự không in được tạo ấn tượng đầu tiên xấu. Và đó thậm chí không phải là một danh sách đầy đủ các cách dữ liệu của bạn có thể bị bẩn. Cuộn tay áo lên. Đã đến lúc phải dọn dẹp một số trang tính lớn trong Microsoft Excel.
Bạn không phải lúc nào cũng có quyền kiểm soát định dạng và kiểu dữ liệu mà bạn nhập từ nguồn dữ liệu bên ngoài, chẳng hạn như cơ sở dữ liệu, tệp văn bản hoặc trang Web. Trước khi có thể phân tích dữ liệu, bạn thường cần dọn dẹp dữ liệu. Rất may là Excel có nhiều tính năng để giúp bạn lấy dữ liệu ở định dạng chính xác mà bạn muốn. Đôi khi, nhiệm vụ là đơn giản và có một tính năng cụ thể thực hiện công việc cho bạn. Ví dụ: bạn có thể dễ dàng sử dụng Bộ kiểm tra Chính tả để dọn sạch các từ sai chính tả trong các cột có chứa chú thích hoặc mô tả. Hoặc nếu bạn muốn loại bỏ các hàng trùng lặp, bạn có thể nhanh chóng thực hiện điều này bằng cách sử dụng hộp thoại Loại bỏ Trùng lặp.
Trong những lúc khác, bạn có thể cần phải điều chỉnh một hoặc nhiều cột bằng cách dùng công thức để chuyển đổi các giá trị đã nhập sang các giá trị mới. Ví dụ, nếu bạn muốn loại bỏ khoảng trắng ở cuối, bạn có thể tạo cột mới để làm sạch dữ liệu bằng cách dùng công thức, điền vào cột mới, chuyển đổi công thức mới của cột đó thành giá trị, rồi loại bỏ cột ban đầu.
Các bước cơ bản để làm sạch dữ liệu như sau:
-
Nhập dữ liệu từ nguồn dữ liệu ngoài.
-
Tạo bản sao lưu của dữ liệu gốc trong một sổ làm việc riêng biệt.
-
Đảm bảo rằng dữ liệu có định dạng bảng gồm các hàng và cột với: dữ liệu tương tự trong mỗi cột, tất cả các cột và hàng hiển thị và không có hàng trống trong phạm vi. Để có kết quả tốt nhất, hãy sử dụng bảng Excel.
-
Trước tiên, hãy thực hiện những tác vụ không yêu cầu thao tác cột, chẳng hạn như kiểm tra chính tả hoặc sử dụng hộp thoại Tìm và Thay thế.
-
Tiếp theo, thực hiện các tác vụ yêu cầu thao tác cột. Các bước chung để thao tác với cột là:
-
Chèn cột mới (B) bên cạnh cột ban đầu (A) cần làm sạch.
-
Thêm công thức sẽ biến đổi dữ liệu ở đầu cột mới (B).
-
Điền công thức vào cột mới (B). Trong bảng Excel, cột được tính sẽ tự động được tạo với các giá trị được điền.
-
Chọn cột mới (B), sao chép cột, rồi dán dưới dạng giá trị vào cột mới (B).
-
Loại bỏ cột ban đầu (A), chuyển đổi cột mới từ B thành A.
-
Để định kỳ dọn sạch cùng một nguồn dữ liệu, hãy cân nhắc việc ghi một macro hoặc mã viết để tự động hóa toàn bộ quy trình. Ngoài ra còn có một số phần bổ trợ bên ngoài do nhà cung cấp bên thứ ba viết, được liệt kê trong phần Nhà cung cấp bên thứ ba, mà bạn có thể cân nhắc sử dụng nếu không có thời gian hoặc tài nguyên để tự động hóa quy trình.
|
Thông tin khác |
Mô tả |
|---|---|
|
Cho biết cách sử dụng lệnh Điền . |
|
|
Tạo và định dạng bảng
|
Hiển thị cách tạo bảng Excel và thêm hoặc xóa cột hoặc cột được tính. |
|
Hiển thị một vài cách để tự động hóa các tác vụ lặp lại bằng cách sử dụng macro. |
Bạn có thể sử dụng trình soát chính tả để không chỉ tìm các từ sai chính tả mà còn để tìm các giá trị không được sử dụng nhất quán, chẳng hạn như tên sản phẩm hoặc công ty, bằng cách thêm các giá trị đó vào một từ điển tùy chỉnh.
|
Xem thêm thông tin |
Mô tả |
|---|---|
|
Hướng dẫn cách sửa các từ sai chính tả trên trang tính. |
|
|
Sử dụng từ điển tùy chỉnh để thêm từ vào trình soát chính tả |
Giải thích cách sử dụng từ điển tùy chỉnh. |
Hàng trùng lặp là vấn đề phổ biến khi bạn nhập dữ liệu. Bạn nên lọc các giá trị duy nhất trước để xác nhận kết quả là điều bạn muốn trước khi loại bỏ các giá trị trùng lặp.
|
Xem thêm thông tin |
Mô tả |
|---|---|
|
Lọc các giá trị đơn nhất hoặc loại bỏ những giá trị trùng lặp |
Hiển thị hai quy trình có liên quan chặt chẽ: cách lọc các hàng đơn nhất và cách loại bỏ các hàng trùng lặp. |
Bạn có thể muốn loại bỏ một chuỗi ở đầu phổ biến, chẳng hạn như nhãn có dấu hai chấm và dấu cách hoặc hậu tố, chẳng hạn như cụm từ dấu ngoặc đơn ở cuối chuỗi lỗi thời hoặc không cần thiết. Bạn có thể thực hiện điều này bằng cách tìm phiên bản của văn bản đó, rồi thay thế văn bản đó mà không có văn bản hoặc văn bản khác.
|
Thông tin khác |
Mô tả |
|---|---|
|
Kiểm tra xem ô có chứa văn bản (không nhạy cảm chữ hoa chữ thường)
|
Hiển thị cách sử dụng lệnh Tìm và một số hàm để tìm văn bản. |
|
Cho biết cách sử dụng lệnh Thay thế và một số hàm để loại bỏ văn bản. |
|
|
Hiển thị cách sử dụng hộp thoại Tìm và Thay thế. |
|
|
FIND, FINDB
|
Đây là các hàm mà bạn có thể sử dụng để thực hiện các tác vụ thao tác chuỗi khác nhau, chẳng hạn như tìm và thay thế chuỗi con trong chuỗi, trích xuất các phần của chuỗi hoặc xác định độ dài của chuỗi. |
Đôi khi văn bản đi kèm trong một túi hỗn hợp, đặc biệt là khi trường hợp của văn bản được quan tâm. Sử dụng một hoặc nhiều hàm trong số ba hàm Trường hợp, bạn có thể chuyển đổi văn bản thành chữ thường, chẳng hạn như địa chỉ email, chữ hoa, chẳng hạn như mã sản phẩm hoặc kiểu chữ thích hợp, chẳng hạn như tên hoặc tiêu đề sách.
|
Xem thêm thông tin |
Mô tả |
|---|---|
|
Cho biết cách sử dụng ba hàm Case. |
|
|
Chuyển đổi tất cả các chữ hoa trong một chuỗi văn bản thành chữ thường. |
|
|
Viết hoa chữ thứ nhất trong một chuỗi văn bản và bất kỳ chữ nào trong văn bản đứng sau một ký tự không phải là chữ. Chuyển đổi tất cả các chữ khác thành chữ thường. |
|
|
Chuyển đổi văn bản thành chữ hoa. |
Đôi khi giá trị văn bản chứa các ký tự khoảng trắng ở đầu, ở cuối hoặc nhiều dấu cách nhúng (Unicode giá trị tập ký tự 32 và 160) hoặc các ký tự không in ra (giá trị tập ký tự Unicode từ 0 đến 31, 127, 129, 141, 143, 144 và 157). Các ký tự này đôi khi có thể gây ra các kết quả không mong muốn khi bạn sắp xếp, lọc hoặc tìm kiếm. Ví dụ: trong nguồn dữ liệu ngoài, người dùng có thể gây ra lỗi đánh máy bằng cách vô tình thêm ký tự khoảng trắng thừa hoặc dữ liệu văn bản đã nhập từ nguồn bên ngoài có thể chứa ký tự không in được nhúng trong văn bản. Vì các ký tự này không dễ nhận thấy, kết quả không mong muốn có thể khó hiểu. Để loại bỏ những ký tự không mong muốn này, bạn có thể sử dụng kết hợp các hàm TRIM, CLEAN và SUBSTITUTE.
|
Xem thêm thông tin |
Mô tả |
|---|---|
|
Trả về mã dạng số cho ký tự đầu tiên trong chuỗi văn bản. |
|
|
Loại bỏ 32 ký tự không in được đầu tiên trong mã ASCII 7 bit (giá trị từ 0 đến 31) khỏi văn bản. |
|
|
Loại bỏ ký tự dấu cách ASCII 7 bit (giá trị 32) khỏi văn bản. |
|
|
Bạn có thể sử dụng hàm SUBSTITUTE để thay thế các ký tự Unicode có giá trị cao hơn (giá trị 127, 129, 141, 143, 144, 157 và 160) bằng các ký tự ASCII 7 bit mà các hàm TRIM và CLEAN được thiết kế. |
Có hai sự cố chính với các số có thể yêu cầu bạn phải dọn sạch dữ liệu: số đã vô tình được nhập dưới dạng văn bản và cần thay đổi dấu âm thành tiêu chuẩn cho tổ chức của bạn.
|
Xem thêm thông tin |
Mô tả |
|---|---|
|
Cho biết cách chuyển đổi các số được định dạng và lưu trữ trong các ô dưới dạng văn bản, điều này có thể gây ra vấn đề cho việc tính toán hoặc tạo ra thứ tự sắp xếp gây nhầm lẫn, sang định dạng số. |
|
|
Chuyển đổi số thành định dạng văn bản và áp dụng ký hiệu tiền tệ. |
|
|
Chuyển đổi một giá trị thành văn bản theo định dạng số cụ thể. |
|
|
Làm tròn số tới số thập phân đã xác định, định dạng số ở định dạng thập phân bằng cách dùng dấu chấm và dấu phẩy và trả về kết quả dưới dạng văn bản. |
|
|
Chuyển đổi một chuỗi văn bản đại diện cho một số thành một số. |
Vì có rất nhiều định dạng ngày khác nhau và vì các định dạng này có thể bị nhầm lẫn với mã phần được đánh số hoặc các chuỗi khác có chứa dấu gạch chéo hoặc gạch nối, ngày và giờ thường cần phải được chuyển đổi và định dạng lại.
|
Xem thêm thông tin |
Mô tả |
|---|---|
|
Thay đổi hệ thống ngày, định dạng hoặc diễn giải năm gồm hai chữ số |
Mô tả cách hoạt động của hệ thống ngày trong Office Excel. |
|
Cho biết cách chuyển đổi giữa các đơn vị thời gian khác nhau. |
|
|
Chuyển đổi ngày tháng được lưu dưới dạng văn bản sang dạng ngày tháng |
Cho biết cách chuyển đổi ngày tháng được định dạng và lưu trữ trong các ô dưới dạng văn bản, điều này có thể gây ra vấn đề cho việc tính toán hoặc tạo ra thứ tự sắp xếp gây nhầm lẫn, sang định dạng ngày tháng. |
|
Trả về số sê-ri liên tiếp đại diện cho một ngày cụ thể. Nếu định dạng của ô là Chung trước khi hàm được nhập vào, thì kết quả sẽ có định dạng ngày. |
|
|
Chuyển đổi ngày tháng được thể hiện bằng văn bản thành số sê-ri. |
|
|
Trả về số thập phân cho một giá trị thời gian cụ thể. Nếu định dạng của ô là Chung trước khi hàm được nhập vào, thì kết quả sẽ có định dạng ngày. |
|
|
Trả về số thập phân của thời gian được thể hiện bằng một chuỗi văn bản. Số thập phân là một giá trị từ 0 (không) đến 0,999999999, thể hiện các khoảng thời gian từ 0:00:00 (12:00:00 SA) đến 23:59:59 (11:59:59 CH). |
Một tác vụ phổ biến sau khi nhập dữ liệu từ nguồn dữ liệu ngoài là phối hai hoặc nhiều cột thành một cột hoặc tách một cột thành hai hoặc nhiều cột. Ví dụ: bạn có thể muốn tách cột có chứa họ và tên thành họ và tên. Hoặc bạn có thể muốn tách cột có chứa trường địa chỉ thành các cột đường phố, thành phố, khu vực và mã bưu chính riêng biệt. Mặt sau cũng có thể đúng. Bạn có thể muốn phối cột Họ và Tên vào cột Họ hoặc kết hợp các cột địa chỉ riêng biệt thành một cột. Các giá trị phổ biến bổ sung có thể yêu cầu sáp nhập vào một cột hoặc tách thành nhiều cột bao gồm mã sản phẩm, đường dẫn tệp và địa chỉ Giao thức Internet (IP).
|
Thông tin khác |
Mô tả |
|---|---|
|
Kết hợp họ và tên
|
Hiện ví dụ điển hình về việc kết hợp các giá trị từ hai hoặc nhiều cột. |
|
Tách văn bản thành cột khác nhau với Trình hướng dẫn Chuyển đổi Văn bản thành Cột |
Hướng dẫn cách sử dụng trình hướng dẫn này để tách cột dựa trên nhiều dấu tách phổ biến khác nhau. |
|
Hướng dẫn cách dùng các hàm LEFT, MID, RIGHT, SEARCH và LEN để tách cột tên thành hai hoặc nhiều cột. |
|
|
Hướng dẫn cách sử dụng hàm CONCATENATE, & tử (dấu và) và Trình hướng dẫn Chuyển đổi Văn bản thành Cột. |
|
|
Hướng dẫn cách sử dụng các lệnh Phối Ô, Phối Ngang, Phối và Căn giữa. |
|
|
Nối hai hoặc nhiều chuỗi văn bản thành một chuỗi văn bản. |
Hầu hết các tính năng phân tích và định dạng trong Office Excel giả định rằng dữ liệu tồn tại trong một bảng hai chiều phẳng duy nhất. Đôi khi bạn có thể muốn biến hàng thành cột và cột trở thành hàng. Vào những thời điểm khác, dữ liệu thậm chí không được cấu trúc ở định dạng bảng và bạn cần một cách để chuyển đổi dữ liệu từ định dạng không theo bảng sang định dạng dạng bảng.
|
Xem thêm thông tin |
Mô tả |
|---|---|
|
Trả về phạm vi ô dọc dưới dạng một phạm vi ngang hoặc ngược lại. |
|
Đôi khi, người quản trị cơ sở dữ liệu sử dụng Office Excel để tìm và sửa lỗi khớp khi hai hoặc nhiều bảng được kết nối. Điều này có thể bao gồm việc điều hòa hai bảng từ các trang tính khác nhau, ví dụ: để xem tất cả các bản ghi trong cả hai bảng hoặc so sánh các bảng và tìm các hàng không khớp.
|
Thông tin khác |
Mô tả |
|---|---|
|
Hiển thị các cách tra cứu dữ liệu phổ biến bằng cách sử dụng các hàm tra cứu. |
|
|
Trả về một giá trị từ phạm vi một hàng hoặc một cột hoặc từ một mảng. Hàm LOOKUP có hai dạng cú pháp: dạng vector và dạng mảng. |
|
|
Tìm kiếm một giá trị trong hàng trên cùng của một bảng hoặc một mảng giá trị, rồi trả về một giá trị trong cùng một cột từ một hàng mà bạn chỉ định trong bảng hoặc mảng. |
|
|
Tìm kiếm một giá trị trong cột đầu tiên của mảng bảng và trả về một giá trị trong cùng một hàng từ một cột khác trong mảng bảng. |
|
|
Trả về một giá trị hoặc tham chiếu tới một giá trị từ trong một bảng hoặc phạm vi. Có hai dạng của hàm INDEX: dạng mảng và dạng tham chiếu. |
|
|
Trả về vị trí tương đối của một mục trong một mảng khớp với giá trị đã xác định theo thứ tự đã xác định. Hãy dùng hàm MATCH thay cho một trong các hàm LOOKUP khi bạn cần biết vị trí của một mục trong một phạm vi thay vì chính mục đó. |
|
|
Trả về tham chiếu tới một phạm vi cách một ô hoặc phạm vi ô một số hàng và một số cột đã xác định. Tham chiếu được trả về có thể là một ô đơn hoặc một phạm vi ô. Bạn có thể chỉ rõ số hàng và số cột cần trả về. |
Sau đây là một phần danh sách các nhà cung cấp bên thứ ba có sản phẩm được sử dụng để dọn sạch dữ liệu theo nhiều cách khác nhau.
Lưu ý: Microsoft không cung cấp hỗ trợ cho các sản phẩm bên thứ ba.
|
Trình cung cấp |
Sản phẩm |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
AddinTools |
|
|
WinPure |
