Cách làm sạch dữ liệu trong Excel: Hướng dẫn cho người mới bắt đầu

Các thành phần của dữ liệu sạch là gì?

• Tính chính xác (Accuracy): Dữ liệu của bạn hiển thị chính xác các giá trị trong thế giới thực mà nó đại diện. Nó đảm bảo rằng thông tin được hiển thị là chính xác và không có lỗi, phản ánh đúng tình trạng thực của dữ liệu. Ví dụ, dữ liệu chính xác sẽ có: Thông tin chính xác và không lỗi, giá trị số học đúng, dữ liệu văn bản không lỗi chính tả và ngày tháng chính xác.

• Tính đầy đủ (Completeness): Dữ liệu đầy đủ chứa tất cả thông tin cần thiết cho việc phân tích. Nếu dữ liệu của bạn không đầy đủ và thiếu các chi tiết quan trọng, điều này có thể làm sai lệch kết quả. Hãy đảm bảo điền vào các khoảng trống hoặc tính đến các giá trị bị thiếu trong phân tích của bạn. Để giải quyết vấn đề dữ liệu bị thiếu, hãy đảm bảo: Nhập giá trị dựa trên các quan sát khác, thay thế các giá trị bị thiếu bằng các giá trị giữ chỗ và loại bỏ các bản ghi không đầy đủ.

• Tính nhất quán (Consistency): Dữ liệu duy trì sự đồng đều qua các bộ dữ liệu và khoảng thời gian khác nhau. Dữ liệu sạch sẽ chứa các định dạng và đơn vị đo lường giống nhau xuyên suốt.

• Tính đồng nhất (Uniformity): Tính đồng nhất hay tiêu chuẩn hóa có nghĩa là tất cả dữ liệu sẽ thể hiện một định dạng và cấu trúc mạch lạc — nó phải thuộc cùng một kiểu dữ liệu hoặc một danh mục. Điều này sẽ bao gồm việc sử dụng cùng một đơn vị đo lường hoặc định dạng ngày tháng và gán nhãn chúng theo danh mục.

• Tính hợp lệ (Validity): Các giá trị dữ liệu nằm trong phạm vi chấp nhận được đã xác định trước và tuân thủ các mẫu dự kiến. Ví dụ, nếu một mục nhập tuổi hợp lệ phải nằm trong khoảng từ 0 đến 120 tuổi, các quy tắc và kiểm tra xác thực sẽ được áp dụng để đảm bảo dữ liệu đáp ứng tiêu chí này. Điều này ngăn chặn các giá trị ngoại lệ và các mục nhập sai lệch làm sai lệch kết quả.

Cách làm sạch dữ liệu trong Excel

• Làm sạch cơ bản: Giải quyết các vấn đề phổ biến như khoảng trắng thừa, ô trống và lỗi chính tả để đảm bảo một bộ dữ liệu sạch và nhất quán.

• Xử lý lỗi và xác thực: Tập trung vào việc xác định và sửa lỗi, đồng thời đảm bảo dữ liệu duy trì tính toàn vẹn bằng cách loại bỏ các bản ghi trùng lặp.

• Thao tác với văn bản: Thao tác và định dạng dữ liệu văn bản để đảm bảo nó đáp ứng yêu cầu của bạn, bao gồm việc nối chuỗi và thay đổi kiểu chữ.

• Chuyển đổi dữ liệu: Sử dụng các kỹ thuật để sắp xếp lại và định hình lại dữ liệu của bạn để phân tích tốt hơn, bao gồm phân tách văn bản và sử dụng các công cụ như Flash Fill.

• Sửa lỗi số và ngày tháng: Sửa và chuẩn hóa dữ liệu số và ngày tháng để đảm bảo chúng chính xác và được định dạng nhất quán.

• Quản lý dữ liệu nâng cao: Bao gồm việc đối chiếu và kết hợp các bộ dữ liệu để tạo ra một bộ dữ liệu toàn diện và gắn kết cho việc phân tích.

Làm sạch dữ liệu cơ bản trong Excel

Loại bỏ khoảng trắng thừa

1. Chọn phạm vi ô mà bạn muốn loại bỏ khoảng trắng thừa.

2. Sử dụng phím tắt Ctrl + H để mở hộp thoại Tìm và Thay thế.

3. Trong ô Find what, nhấn phím cách hai lần để nhập hai khoảng trắng.

4. Trong ô Replace with, nhấn phím cách một lần để nhập một khoảng trắng.

5. Nhấp vào Replace All.

6. Lặp lại quy trình cho đến khi không còn tìm thấy khoảng trắng kép nào.

1. Tạo một cột mới cho dữ liệu đã được làm sạch.

2. Nhập công thức =TRIM(ô_có_khoảng_trắng_thừa) vào ô đầu tiên của cột mới.

3. Nhấp đúp vào góc dưới bên phải của ô để áp dụng công thức cho các hàng còn lại.

4. Sao chép dữ liệu đã làm sạch và dán dưới dạng giá trị (paste as values) để loại bỏ công thức.

Chọn và xử lý tất cả các ô trống

1. Bôi đen phạm vi ô mà bạn muốn tìm và xử lý các ô trống.

2. Nhấn Ctrl + G để mở hộp thoại Go To.

3. Nhấp vào nút “Special…”. Thao tác này sẽ mở hộp thoại Go To Special.

4. Chọn tùy chọn Blanks và nhấp OK. Bây giờ, bạn sẽ thấy tất cả các ô trống trong phạm vi đã chọn được chọn.

5. Bây giờ bạn có thể nhập một giá trị hoặc công thức. Nhấn Ctrl + Enter.

Kiểm tra chính tả (Spell checking)

1. Kiểm tra toàn bộ trang tính hoặc một phạm vi cụ thể.

2. Chuyển đến tab Review trên thanh Ribbon.

3. Nhấp vào nút Spelling trong nhóm Proofing.

4. Hộp thoại Spelling sẽ mở ra, hiển thị từ sai chính tả đầu tiên được phát hiện và các đề xuất sửa lỗi.

5. Xem xét và sửa các từ sai chính tả khi cần thiết.

Xử lý lỗi và xác thực trong Excel

Tô sáng lỗi

1. Chọn phạm vi ô bạn muốn kiểm tra lỗi.

2. Chuyển đến tab Home trên thanh Ribbon.

3. Trong nhóm Styles, nhấp vào Conditional Formatting.

4. Chọn New Rule từ menu thả xuống.

5. Chọn Use a formula to determine which cells to format.

6. Nhập công thức =ISERROR(ô) vào trường Format values where this formula is true.

7. Nhấp vào nút Format… để chọn các tùy chọn định dạng mong muốn của bạn.

8. Nhấp OK sau khi chọn các tùy chọn định dạng của bạn.

9. Nhấp OK một lần nữa trong hộp thoại New Formatting Rule để áp dụng quy tắc.

Xóa dữ liệu trùng lặp (Remove duplicates)

1. Chọn trang tính hoặc phạm vi ô mà bạn muốn xóa dữ liệu trùng lặp.

2. Chuyển đến tab Data trên thanh Ribbon.

3. Nhấp vào Remove Duplicates trong nhóm Data Tools.

4. Trong hộp thoại Remove Duplicates, chỉ định cột nào cần kiểm tra dữ liệu trùng lặp.

5. Nhấp OK. Excel sẽ hiển thị một thông báo cho biết số lượng giá trị trùng lặp đã được xóa.

Xóa các hàng trùng lặp

1. Chọn phạm vi mà bạn muốn xóa các hàng trùng lặp.

2. Chuyển đến tab Data trên thanh Ribbon và nhấp vào Advanced trong nhóm Sort & Filter.

3. Trong hộp thoại Advanced Filter, chọn Copy to another location.

4. Đặt List range thành phạm vi đã chọn của bạn.

5. Đặt trường Copy to thành ô mà bạn muốn sao chép các hàng duy nhất đến.

6. Đánh dấu vào ô Unique records only rồi nhấp OK.

Các thao tác với văn bản trong Excel

Nối chuỗi (Concatenate)

1. Chọn ô mà bạn muốn kết quả nối chuỗi xuất hiện.

2. Nhập =CONCATENATE().

3. Chọn các ô bạn muốn nối, được phân tách bằng dấu phẩy hoặc toán tử &.

4. Nhấn Enter để xem kết quả.

Thay đổi kiểu chữ của văn bản

1. Tạo một cột mới cho văn bản đã được chuyển đổi.

2. Nhập công thức vào một ô: =UPPER() (IN HOA), =LOWER() (in thường) hoặc =PROPER() (Viết Hoa Chữ Cái Đầu).

3. Nhấn Enter để áp dụng công thức.

4. Kéo tay cầm điền (fill handle) để áp dụng công thức cho các ô khác nếu cần.

Loại bỏ các ký tự không in được khỏi văn bản

1. Xác định các ký tự không in được bạn cần loại bỏ.

2. Chọn ô chứa văn bản có ký tự không in được.

3. Trong một ô mới, sử dụng hàm sau: =CLEAN(văn_bản).

4. Để loại bỏ khoảng trắng thừa và các ký tự không in được, hãy sử dụng: =TRIM(CLEAN(văn_bản)).

5. Sao chép và dán văn bản đã làm sạch để thay thế bản gốc.

Chuyển đổi dữ liệu trong Excel

Phân tách dữ liệu từ văn bản sang cột (Text to Columns)

1. Chọn dữ liệu bạn muốn tách.

2. Chuyển đến tab Data và nhấp vào Text to Columns.

3. Chọn Delimited (phân tách bởi ký tự) hoặc Fixed width (chiều rộng cố định) và nhấp Next.

4. Đối với Delimited, chọn các ký tự phân tách mà dữ liệu của bạn sử dụng và nhấp Next.

5. Đối với Fixed width, đặt các điểm ngắt cột trong cửa sổ Data Preview và nhấp Next.

6. Chọn định dạng dữ liệu cho mỗi cột.

7. Nhấp Finish.

Flash Fill (Điền nhanh)

1. Nhập dữ liệu theo một mẫu vào một ô bên cạnh dữ liệu hiện có của bạn.

2. Cung cấp một ví dụ khác trong ô tiếp theo để giúp Excel phát hiện mẫu.

3. Chọn ô có ví dụ.

4. Chuyển đến tab Data trên thanh Ribbon.

5. Nhấp vào Flash Fill trong nhóm Data Tools.

6. Excel sẽ tự động điền các ô còn lại dựa trên mẫu đã phát hiện.

Gộp và tách cột

1. Chuyển đến tab Home, và nhấp vào menu thả xuống Merge & Center trong nhóm Alignment.

2. Chọn tùy chọn gộp ưa thích của bạn.

1. Chuyển đến tab Data và nhấp vào Text to Columns trong nhóm Data Tools.

2. Chọn delimited hoặc fixed width dựa trên nhu cầu dữ liệu của bạn.

3. Chọn nơi đến cho dữ liệu đã tách và nhấp Finish.

Chuyển đổi và sắp xếp lại các cột và hàng (Transpose)

1. Bôi đen dữ liệu bạn muốn chuyển đổi (bao gồm cả tiêu đề nếu cần).

2. Nhấp chuột phải vào vùng chọn và chọn Copy hoặc nhấn Ctrl+C.

3. Chọn ô mà dữ liệu đã chuyển đổi sẽ bắt đầu.

4. Nhấp chuột phải vào ô đích, chọn Paste Special và sau đó chọn Transpose.

1. Nhấp vào tiêu đề cột để chọn toàn bộ cột bạn muốn di chuyển.

2. Nhấp chuột phải và chọn Cut hoặc nhấn Ctrl+X.

3. Chọn cột nơi bạn muốn di chuyển cột đã cắt đến, nhấp chuột phải vào tiêu đề cột và chọn Insert Cut Cells.

4. Tương tự đối với hàng, chọn và cắt hàng bạn muốn chuyển đến một hàng khác và dán nó vào đó.

Sửa lỗi số và ngày tháng trong Excel

Sửa số và định dạng số

1. Chọn các ô chứa các số bạn cần sửa.

2. Chuyển đến tab Home, nhấp vào menu thả xuống Number trong nhóm Number và chọn định dạng số thích hợp (ví dụ: General, Number, Currency).

Sửa ngày tháng và thời gian

1. Bôi đen các ô chứa ngày tháng.

2. Chuyển đến tab Home.

3. Nhấp vào menu thả xuống Number Format và chọn Short Date hoặc Long Date.

Quản lý dữ liệu nâng cao trong Excel

Đối chiếu dữ liệu bảng bằng cách nối hoặc khớp (Joining or Matching)

1. Đảm bảo cả hai bảng đều có thể truy cập trên cùng một trang tính.

2. Chọn ô mà bạn muốn hiển thị dữ liệu đã khớp.

3. Sử dụng hàm sau: =VLOOKUP(giá_trị_tìm_kiếm, vùng_bảng, số_thứ_tự_cột, FALSE)

4. Kéo tay cầm điền để sao chép công thức cho các ô khác khi cần.

1. Chọn ô mà bạn muốn hiển thị dữ liệu đã khớp.

2. Sử dụng sự kết hợp của các hàm này: =INDEX(vùng_kết_quả, MATCH(giá_trị_tìm_kiếm, vùng_tìm_kiếm, 0))

3. Kéo tay cầm điền để sao chép công thức cho các ô khác khi cần.

Lời kết