Mười Cách Hàng đầu để Dọn Sạch Dữ Liệu Của Bạn - Microsoft Support

Trang chủ » File Excel Làm Sạch Dữ Liệu » Mười Cách Hàng đầu để Dọn Sạch Dữ Liệu Của Bạn - Microsoft Support

Có thể bạn quan tâm

Bỏ qua để tới nội dung chính Đăng nhập với Microsoft Đăng nhập hoặc tạo một tài khoản. Xin chào, Chọn một tài khoản khác. Bạn có nhiều tài khoản Chọn tài khoản bạn muốn đăng nhập. Áp dụng choExcel cho Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016 Microsoft365.com My Office for iPhone

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.

Thông tin cơ bản về việc làm sạch dữ liệu của bạn

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:

  1. Nhập dữ liệu từ nguồn dữ liệu ngoài.

  2. 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.

  3. Đả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.

  4. 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ế.

  5. 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à:

    1. Chèn cột mới (B) bên cạnh cột ban đầu (A) cần làm sạch.

    2. Thêm công thức sẽ biến đổi dữ liệu ở đầu cột mới (B).

    3. Đ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.

    4. 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).

    5. 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ả

Điền dữ liệu tự động vào các ô trong trang tính

Cho biết cách sử dụng lệnh Điền .

Tạo và định dạng bảng Đổi kích cỡ 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

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.

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 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.

Thông tin khác

Mô tả

Kiểm tra chính tả và ngữ pháp

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.

Loại bỏ hàng trùng lặp

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.

Thông tin khác

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.

Tìm và thay thế văn bản

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) hay khôngKiểm tra xem ô có chứa văn bản (phân biệt chữ hoa chữ thường) hay khô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

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.

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ế.

FIND, FINDB TÌM KIẾM, SEARCHB REPLACE, REPLACEB THAY THẾ 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 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.

Thay đổi kiểu chữ hoa/thường của văn bản

Đô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.

Thông tin khác

Mô tả

Thay đổi kiểu chữ hoa/thường

Cho biết cách sử dụng ba hàm Case.

LOWER

Chuyển đổi tất cả các chữ hoa trong một chuỗi văn bản thành chữ thường.

ĐÚNG ĐẮN

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 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ả

MÃ

Trả về mã dạng số cho ký tự đầu tiên trong chuỗi văn bản.

SẠCH

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.

TRIM

Loại bỏ ký tự dấu cách ASCII 7 bit (giá trị 32) khỏi văn bản.

THAY THẾ

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ế.

Sửa các số và dấu hiệu số

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ả

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, đ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ố.

ĐÔ LA

Chuyển đổi số thành định dạng văn bản và áp dụng ký hiệu tiền tệ.

TEXT

Chuyển đổi một giá trị thành văn bản theo định dạng số cụ thể.

ĐÃ KHẮC PHỤC

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.

VALUE

Chuyển đổi một chuỗi văn bản đại diện cho một số thành một số.

Sửa 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, ngày và giờ thường cần phải được chuyển đổi và định dạng lại.

Thông tin khác

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.

Chuyển đổi thời gian

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.

DATE

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.

Hàm DATEVALUE

Chuyển đổi ngày tháng đượ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

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 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 một hàm

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.

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 hoặc nhiều cột.

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, & tử (dấu và) và Trình hướng dẫn Chuyển đổi 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 Ngang, Phối và Căn giữa.

Hàm CONCATENATE

Nối hai hoặc 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 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ả

Hàm TRANSPOSE

Trả về phạm vi ô 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 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 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ả

Tra cứu các giá trị trong một danh sách dữ liệu

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.

LOOKUP

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.

HÀM HLOOKUP

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.

Hàm VLOOKUP

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.

INDEX

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.

MATCH

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 đó.

BÙ ĐẮP

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ó 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.

Ultimate Suite for Excel, Trình hướng dẫn Phối Bảng, Trình hướng dẫn Loại bỏ Trùng lặp, Trình hướng dẫn Hợp nhất Trang tính, Trình hướng dẫn Kết hợp Hàng, Dọn dẹp Ô, Trình tạo Ngẫu nhiên, Phối Ô, Công cụ Nhanh dành cho Excel, Bộ sắp xếp Ngẫu nhiên, Tìm Nâng cao & Thay thế, Trình tìm Trùng lặp Mờ, Tên Tách, Trình hướng dẫn Tách Bảng, Trình quản lý Sổ làm việc

Add-Ins.com

Trình tìm kiếm Trùng lặp

AddinTools

Hỗ trợ AddinTools

WinPure

ListCleaner Lite ListCleaner Pro

Đầu Trang

Facebook LinkedIn Email

Bạn cần thêm trợ giúp?

Bạn muốn xem các tùy chọn khác?

Khám pháCộng đồng

Khám phá các lợi ích của gói đăng ký, xem qua các khóa đào tạo, tìm hiểu cách bảo mật thiết bị của bạn và hơn thế nữa.

Lợi ích đăng ký Microsoft 365

Nội dung đào tạo về Microsoft 365

Bảo mật Microsoft

Trung tâm trợ năng

Cộng đồng giúp bạn đặt và trả lời các câu hỏi, cung cấp phản hồi và lắng nghe ý kiến từ các chuyên gia có kiến thức phong phú.

Hỏi Cộng đồng Microsoft

Cộng đồng Kỹ thuật Microsoft

Người dùng Nội bộ Windows

Người dùng nội bộ Microsoft 365

Thông tin này có hữu ích không?

Có Không (Gửi ý kiến phản hồi cho Microsoft để chúng tôi có thể trợ giúp.) Bạn hài lòng đến đâu với chất lượng dịch thuật? Điều gì ảnh hưởng đến trải nghiệm của bạn? Đã giải quyết vấn đề của tôi Hướng dẫn Rõ ràng Dễ theo dõi Không có thuật ngữ Hình ảnh có ích Chất lượng dịch thuật Không khớp với màn hình của tôi Hướng dẫn không chính xác Quá kỹ thuật Không đủ thông tin Không đủ hình ảnh Chất lượng dịch thuật Bất kỳ thông tin phản hồi bổ sung? (Không bắt buộc) Gửi phản hồi Khi nhấn gửi, phản hồi của bạn sẽ được sử dụng để cải thiện các sản phẩm và dịch vụ của Microsoft. Người quản trị CNTT của bạn sẽ có thể thu thập dữ liệu này. Điều khoản về quyền riêng tư.

Cảm ơn phản hồi của bạn!

×

Từ khóa » File Excel Làm Sạch Dữ Liệu