Mười cách trên cùng để làm sạch dữ liệu của bạn

Lưu ý: Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

Các từ viết sai chính tả, dấu cách không khớp với, tiền tố không mong muốn, các trường hợp không đúng và các ký tự không in được tạo ra Ấn tượng đầu tiên xấu. Và đó thậm chí không có danh sách đầy đủ các cách thức dữ liệu của bạn có thể bị bẩn. Cuộn lên tay áo của bạn. Đó là thời gian cho một số mùa xuân chính-làm sạch các trang tính của bạn với Microsoft Excel.

Bạn không luôn có quyền kiểm soát định dạng và kiểu dữ liệu bạn nhập từ nguồn dữ liệu 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 dọn sạch nó. May mắn thay, Excel có nhiều tính năng để giúp bạn nhận được dữ liệu theo đị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 của 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.

Tại thời điểm khác, bạn có thể cần thao tác 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ị được nhập vào các giá trị mới. Ví dụ, nếu bạn muốn loại bỏ không gian trống, bạn có thể tạo một cột mới để làm sạch dữ liệu bằng cách sử dụng công thức, hãy đ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 để làm sạch dữ liệu như sau:

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

  2. Tạo một bản sao lưu của dữ liệu gốc trong một sổ làm việc riêng.

  3. Đảm bảo rằng dữ liệu nằm trong định dạng bảng của 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 được 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 dùng bảng Excel.

  4. Thực hiện các tác vụ không yêu cầu thao tác cột đầu tiên, 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 nhiệm vụ yêu cầu thao tác cột. Các bước chung để thao tác một cột là:

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

    2. Thêm công thức để chuyển đổi dữ liệu ở phía trên cùng của cột mới (B).

    3. Điền công thức xuống trong cột mới (B). Trong bảng Excel, một cột được tính sẽ được tự động tạo với giá trị được điền vào.

    4. Chọn cột mới (B), sao chép nó, rồi dán làm giá trị vào cột mới (B).

    5. Loại bỏ cột gốc (A), vốn đã chuyển đổi cột mới từ B thành A.

Để làm sạch theo định kỳ nguồn dữ liệu tương tự, 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 được viết bởi nhà cung cấp bên thứ ba, đượ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 bạn không có thời gian hoặc tài nguyên để tự động hóa quy trình này.

Thông tin khác

Mô tả

Tổng quan về kết nối (nhập) dữ liệu

Mô tả tất cả các cách nhập dữ liệu bên ngoài vào Office Excel.

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

Hiển thị 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ác cột được tính toán.

Tạo macro

Hiển thị một số 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 bộ kiểm tra chính tả để không chỉ tìm thấy các từ viết sai chính tả, nhưng để 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 từ điển tùy chỉnh.

Thông tin khác

Mô tả

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

Hiển thị cách sửa các từ sai chính tả trên một trang tính.

Sử dụng từ điển tùy chỉnh để thêm từ vào bộ kiểm tra chính tả

Giải thích cách sử dụng từ điển tùy chỉnh.

Các hàng trùng lặp là một vấn đề phổ biến khi bạn nhập dữ liệu. Một ý kiến hay để lọc các giá trị duy nhất trước tiên để xác nhận rằng kết quả là những gì bạn muốn trước khi bạn 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 thủ tục liên quan chặt chẽ: cách lọc cho các hàng duy 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 hàng đầu chung, chẳng hạn như nhãn dấu hai chấm và dấu cách, hoặc một hậu tố, chẳng hạn như một cụm từ ngoặc đơn vào 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 kiếm các trường hợp của văn bản đó rồi thay thế bằng 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 (

không phân biệt chữ hoa/thường)nếu một ô có chứa văn bản ( phân biệt chữ hoa/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

Hiển thị 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ìmthay thế .

Tìm

kiếm, tìm kiếm findb, searchb

thay thế, replaceb

thay thế

trái,

bên phải, lề phải, rightb

len, lenb Mid, midb

Đây là những hàm mà bạn có thể 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 một chuỗi hoặc xác định độ dài của chuỗi.

Đôi khi văn bản có trong một túi hỗn hợp, đặc biệt là khi trường hợp văn bản có liên quan. Sử dụng một hoặc nhiều hàm Three 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 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

Hiển thị cách sử dụng ba hàm Case.

LOWER

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

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

UPPER

Chuyển đổi văn bản thành chữ hoa.

Đôi khi các giá trị văn bản có chứa hàng đầu, dấu hoặc nhiều ký tự không gian nhúng (_ z0z_ ký tự đặt giá trị 32 và 160), hoặc các ký tự không in (ký tự Unicode đặt giá trị từ 0 đến 31, 127, 129, 141, 143, 144, và 157). Những ký tự này đôi khi có thể gây ra 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ể thực hiện lỗi biểu thị bằng cách vô tình thêm các ký tự không gian bổ sung hoặc nhập dữ liệu văn bản 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. Vì các ký tự này không dễ dàng nhận thấy, các kết quả không mong muốn có thể khó hiểu. Để loại bỏ các 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, sạch và thay thế.

Thông tin khác

Mô tả

Hiển thị cách loại bỏ tất cả các ký tự không gian và không in ra từ bộ ký tự Unicode.

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

DỌN DẸP

Loại bỏ các ký tự không in 32 đầu tiên trong mã ASCII 7 bit (các giá trị từ 0 đến 31) từ văn bản.

TRIM

Loại bỏ ký tự không gian ASCII 7-bit (giá trị 32) từ văn bản.

Sân

Bạn có thể sử dụng hàm Replace để thay thế các ký tự Unicode giá trị cao hơn (các giá trị 127, 129, 141, 143, 144, 157 và 160) bằng các ký tự có chứa trong các các tính năng TRIM và CLEAN được thiết kế.

Có hai vấn đề chính với các số có thể yêu cầu bạn xóa dữ liệu: số vô tình được nhập dưới dạng văn bản và dấu âm cần được thay đổi thành tiêu chuẩn cho tổ chức của bạn.

Thông tin khác

Mô tả

Chuyển đổi số được lưu trữ dưới dạng văn bản sang số

Hiển thị 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, có thể gây ra vấn đề với các phép tính hoặc tạo ra các đơn hàng sắp xếp khó hiểu, đến định dạng số.

TỆ

Chuyển đổi một 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 một số tới số 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ả là 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ố.

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 các 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 dấu gạch ngang, ngày tháng và thời gian thường cần đượ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 hai chữ số năm diễn giải

Mô tả cách hệ thống ngày làm việc trong Office Excel.

Chuyển đổi thời gian

Hiển thị 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

Hiển thị cách chuyển đổi các ngày đượ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 các đơn hàng sắp xếp khó hiểu, đến định dạng ngày.

DATE

Trả về số sê-ri tuần 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 một ngày được đại diện bởi 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ị khác nhau, từ 0 (không) đến 0,99999999, đại diện cho thời gian từ 0:00:00 (12:00:00 sáng) đến 23:59:59 (11:59:59).

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 vào một hoặc phân 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 một cột có chứa tên đầy đủ thành tên và họ. Hoặc, bạn có thể muốn phân tách một cột có chứa một trường địa chỉ vào các cột mã đường phố, thành phố, khu vực và mã bưu chính. Ngược lại cũng có thể là đúng. Bạn có thể muốn phối cột tên và họ vào cột tên đầy đủ hoặc kết hợp các cột địa chỉ riêng rẽ 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 tên

đầu tiên và cuối cùngkế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 thị các ví dụ điển hình về 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

Hiển thị cách sử dụng trình hướng dẫn này để phân tách các cột dựa trên các 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

Hiển thị cách sử dụng các hàm LEFT, MID, RIGHT, SEARCH và LEN để phân tách một 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 ô

Hiển thị cách sử dụng toán tử hàm CONCATENATE, _ Amp_(dấu và), rồi chuyển đổi văn bản thành trình hướng dẫn cột.

Phối ô hoặc tách ô được phối

Hiển thị cách sử dụng các ô phối, phối quaphối và lệnh 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.

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 đơn, hai chiều phẳng. Đôi khi bạn có thể muốn thực hiện các hàng trở thành cột và các cột trở thành hàng. Tại thời điểm khác, dữ liệu thậm chí không có cấu trúc theo định dạng bảng, và bạn cần một cách để chuyển đổi dữ liệu từ không phải là một định dạng bảng.

Thông tin khác

Mô tả

Hàm TRANSPOSE

Trả về một phạm vi ô đứng dọc dưới dạng một dải 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 với hai hoặc nhiều bảng được gia nhập. Ví dụ: điều này có thể liên quan đến việc làm trang tính 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 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 thông thường để tìm kiếm dữ liệu bằng cách sử dụng các hàm Lookup.

CỨU

Trả về một giá trị trong 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 biểu mẫu cú pháp: biểu mẫu véc-tơ và biểu mẫu mảng.

HLOOKUP

Tìm kiếm một giá trị ở hàng trên cùng của bảng hoặc một mảng các giá trị, rồi trả về một giá trị trong cùng một cột từ một hàng bạn xác đị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ột 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 biểu mẫu của hàm INDEX: biểu mẫu mảng và biểu mẫu 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 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ề.

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

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

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

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×