Từ viết sai chính tả, khoảng trắng ở cuối cứng đầu, tiền tố không mong muốn, viết hoa chữ thường không đúng và ký tự không in ra sẽ tạo ấn tượng ban đầu không tốt. Và đó thậm chí còn không phải là danh sách đầy đủ các cách mà dữ liệu của bạn có thể bị bẩn. Xắn tay áo lên. Đã đến lúc phải dọn dẹp trang tính của bạn một cách nghiêm túc bằng Microsoft Excel.
Thông tin cơ bản về việc dọn dẹp dữ liệu của bạn
Không phải lúc nào bạn cũng có quyền kiểm soát định dạng và loại 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 bạn có thể phân tích dữ liệu, bạn thường cần phải dọn sạch dữ liệu. May mắn là Excel có nhiều tính năng để giúp bạn nhận dữ liệu ở định dạng chính xác mà bạn muốn. Đôi khi, tác vụ rất đơ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ừ viế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 một số trường hợp khác, bạn có thể cần điều chỉnh một hoặc nhiều cột bằng cách sử dụng công thức để chuyển đổi các giá trị đã nhập thành 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 sử dụng công thức, điền vào cột mới, chuyển đổi công thức của cột mới đó thành giá trị, rồi loại bỏ cột ban đầu.
Các bước cơ bản để dọn sạch dữ liệu như sau:
Nhập dữ liệu từ nguồn dữ liệu bên 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.
Hãy đả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ự nhau trong mỗi cột, tất cả các cột và hàng đều 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.
Thực hiện các tác vụ không yêu cầu thao tác trên cột trước tiê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, hãy thực hiện các tác vụ yêu cầu thao tác với 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ẽ chuyể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 toán được tự động tạo với các giá trị được điền vào.
- Chọn cột mới (B), sao chép nó, 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), vốn sẽ chuyển đổi cột mới từ B thành A.
Để dọn dẹp định kỳ cùng một nguồn dữ liệu, hãy cân nhắc việc ghi lại macro hoặc viết mã để 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 được viết bởi nhà cung cấp bên thứ ba, được liệt kê trong mục Nhà cung cấp bên thứ ba mà bạn có thể cân nhắc sử dụng nếu bạn không có thời gian hoặc tài nguyên để tự động hóa quy trình.
| Thông tin Bổ sung | Mô tả |
|---|---|
| Điền dữ liệu tự động vào các ô trong trang tính | Hướng dẫn cách sử dụng lệnh Điền . |
|
Tạo và định dạng bảng Đổi kích cỡ cho bảng bằng cách thêm hoặc loại bỏ hàng và cột Sử dụng các cột được tính trong bảng Excel |
Hướng dẫn cách tạo bảng Excel và thêm hoặc xóa cột hoặc cột được tính. |
| Tạo macro | 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. |
Kiểm tra chính tả
Bạn có thể sử dụng bộ kiểm tra 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 dùng nhất quán, chẳng hạn như tên sản phẩm hoặc tên công ty, bằng cách thêm các giá trị đó vào từ điển tùy chỉnh.
| Thông tin Bổ sung | Mô tả |
|---|---|
| Kiểm tra chính tả và ngữ pháp | Cho biết cách sửa các từ viết sai chính tả trong một 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. |
Loại bỏ các hàng trùng lặp
Hàng trùng lặp là sự cố thường gặp 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à như mong muốn trước khi loại bỏ các giá trị trùng lặp.
| Thông tin Bổ sung | 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 liên quan chặt chẽ: cách lọc các hàng duy nhất và cách loại bỏ các hàng trùng lặp. |
Tìm và thay thế văn bản
Bạn có thể muốn loại bỏ chuỗi đứng đầu phổ biến, chẳng hạn như nhãn theo sau là dấu hai chấm và dấu cách hoặc hậu tố, chẳng hạn như cụm từ trong 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 các phiên bản của văn bản đó, rồi thay thế bằng không có văn bản hoặc văn bản khác.
| Thông tin Bổ sung | Mô tả |
|---|---|
|
Kiểm tra xem một ô có chứa văn bản không (không phân biệt chữ hoa, chữ thường) Kiểm tra xem một ô có chứa văn bản không (phân biệt 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. |
| Loại bỏ ký tự khỏi văn bản | Trình bày cách sử dụng lệnh Thay thế và một số hàm để loại bỏ văn bản. |
| Tìm hoặc thay thế văn bản và số trên trang tính | Hiển thị cách sử dụng hộp thoại Tìm vàThay thế . |
|
Các hàm FIND, FINDB Các hàm SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
Đâ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 phụ 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. |
Thay đổi kiểu chữ hoa/thường
Đôi khi văn bản được bao gồm trong một túi hỗn hợp, đặc biệt là khi liên quan đến trường hợp văn bản. Bằng cách sử dụng một hoặc nhiều hàm trong số ba hàm Case, 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 viết hoa ký tự đầu tiên, chẳng hạn như tên hoặc tiêu đề sách.
| Thông tin Bổ sung | Mô tả |
|---|---|
| Thay đổi kiểu chữ hoa/thường | Trình bày cách sử dụng ba hàm Case. |
| LOWER | Chuyển đổi tất cả chữ hoa trong chuỗi văn bản thành chữ thường. |
| PROPER | 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. |
| UPPER | Chuyển đổi văn bản thành chữ hoa. |
Loại bỏ khoảng trắng và ký tự không in ra khỏi văn bản
Đôi khi giá trị văn bản chứa ký tự cách dòng, đằng sau hoặc nhiều ký tự dấu cách được nhúng (giá trị bộ ký tự Unicode 32 và 160) hoặc ký tự không in (các giá trị bộ ký tự Unicode từ 0 đến 31, 127, 129, 141, 143, 144 và 157). Các ký tự này đôi khi có thể tạo 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 bên ngoài, người dùng có thể mắc lỗi in bằng cách vô tình thêm các ký tự khoảng trắng hoặc dữ liệu văn bản được nhập từ các nguồn bên ngoài có thể chứa các ký tự không in được nhúng trong văn bản. Do người ta không dễ nhận thấy những ký tự nà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.
| Thông tin Bổ sung | Mô tả |
|---|---|
| CODE | Trả về mã dạng số cho ký tự đầu tiên trong chuỗi văn bản. |
| CLEAN | Loại bỏ 32 ký tự không in được đầu tiên trong mã ASCII 7 bit (các giá trị từ 0 đến hết 31) khỏi văn bản. |
| TRIM | Loại bỏ ký tự dấu cách ASCII 7 bit (giá trị 32) khỏi văn bản. |
| SUBSTITUTE | 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à hàm TRIM và CLEAN đã được thiết kế. |
Cố định các số và các ký hiệu số
Có hai vấn đề chính với các số có thể yêu cầu bạn dọn dẹp dữ liệu: số đã vô tình được nhập dưới dạng văn bản và dấu trừ cần được thay đổi theo tiêu chuẩn cho tổ chức của bạn.
| Thông tin Bổ sung | Mô tả |
|---|---|
| Chuyển đổi số được lưu trữ dưới dạng văn bản sang số | 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 vốn có thể gây ra vấn đề với các phép tính hoặc tạo ra thứ tự sắp xếp lẫn lộn, sang định dạng số. |
| DOLLAR | Chuyển đổi số thành định dạng văn bản và áp dụng biểu tượng tiền tệ. |
| VĂN BẢN | Chuyển đổi một giá trị thành văn bản trong một định dạng số cụ thể. |
| ĐÃ KHẮC PHỤC | Làm tròn số tới số vị trí thập phân đã xác định, định dạng số theo định dạng thập phân bằng cách sử dụng dấu chấm và dấu phẩy và trả về kết quả dạng văn bản. |
| GIÁ TRỊ | Chuyển đổi một chuỗi văn bản đại diện cho một số thành một số. |
Cố định ngày và giờ
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 nên ngày và giờ thường cần được chuyển đổi và định dạng lại.
| Thông tin Bổ sung | Mô tả |
|---|---|
| Thay đổi hệ thống ngày, định dạng hoặc cách giải thích năm có hai chữ số | Mô tả cách hệ thống ngày hoạt động trong Office Excel. |
| Chuyển đổi thời gian | Cho thấy 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 có thể gây ra vấn đề với các phép tính hoặc tạo ra thứ tự sắp xếp lẫn lộn, thành định dạng ngày tháng. |
| NGÀY | Trả về số sê-ri lần lượt đạ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. |
| Hàm DATEVALUE | Chuyển đổi ngày được thể hiện bằng văn bản thành số sê-ri. |
| TIME | 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. |
| Hàm TIMEVALUE | 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). |
Phối và tách cột
Nhiệm vụ phổ biến sau khi nhập dữ liệu từ nguồn dữ liệu bên ngoài là phối hai hoặc nhiều cột thành mộ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 phân tách cột chứa họ tên đầy đủ 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, thành phố, khu vực và mã bưu chính riêng biệt. Phần nghịch đảo cũng có thể đúng. Bạn có thể muốn phối cột Họ và Tên vào cột Họ và Tên, 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ị chung bổ sung có thể yêu cầu phối thành 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 Bổ sung | Mô tả |
|---|---|
|
Kết hợp họ và tên Kết hợp văn bản và số Kết hợp văn bản với ngày hoặc giờ Kết hợp hai hoặc nhiều cột bằng cách sử dụng hàm |
Hiển thị ví dụ điển hình về việc kết hợp giá trị từ hai cột trở lên. |
| 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 để chia tách cột dựa trên nhiều dấu tách chung khác nhau. |
| Tách văn bản thành các cột khác nhau bằng cách sử dụng hàm | 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 cột trở lên. |
| Kết hợp hoặc tách nội dung của các ô | Hướng dẫn cách sử dụng hàm CONCATENATE, toán tử & (dấu "và") và Trình hướng dẫn Chuyển Văn bản thành Cột. |
| Phối ô hoặc tách ô được phối | Hướng dẫn cách sử dụng các lệnh Phối Ô, Phối Chéo và Phối và Căn giữa . |
| Hàm CONCATENATE | Kết hợp hai hay nhiều chuỗi văn bản thành một chuỗi văn bản. |
Chuyển đổi và sắp xếp lại các cột và hàng
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, đơn lẻ. Đôi khi, bạn có thể muốn đặt các hàng thành cột và các cột trở thành hàng. Trong những lúc khác, dữ liệu thậm chí không có cấu trúc theo định dạng 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 dạng bảng sang định dạng dạng bảng.
| Thông tin Bổ sung | Mô tả |
|---|---|
| Hàm TRANSPOSE | Trả về một phạm vi các ô dọc dưới dạng một phạm vi ngang hoặc ngược lại. |
Điều hòa dữ liệu bảng bằng cách ghép nối hoặc khớp
Đô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 bảng trở lên được nối. Điều này có thể liên quan đến việc kết hợp 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 bảng và tìm các hàng không khớp.
| Thông tin Bổ sung | Mô tả |
|---|---|
| Tra cứu các giá trị trong một danh sách dữ liệu | Cho thấy những cách tra cứu dữ liệu thông thường bằng cách dùng các hàm tra cứu. |
| LOOKUP | Trả về một giá trị từ phạm vi gồm 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. |
| HLOOKUP | Tìm kiếm một giá trị trong hàng trên cùng của bảng hoặc một mảng giá trị, sau đó trả về giá trị trong cùng cột từ hàng mà bạn chỉ định trong bảng hoặc mảng. |
| Hàm VLOOKUP | Tìm kiếm giá trị trong cột đầu tiên của mảng bảng và trả về giá trị trong cùng hàng từ một cột khác trong mảng bảng. |
| CHỈ MỤC | 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 hàm INDEX: dạng mảng và dạng tham chiếu. |
| MATCH | Trả về vị trí tương đối của một mục trong mảng khớp với một 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 đó. |
| OFFSET | 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ề. |
Nhà cung cấp bên thứ ba
Sau đây là một phần danh sách các nhà cung cấp bên thứ ba có các sản phẩm được sử dụng để dọn 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 của bên thứ ba.