Courses
Introduction to Excel
4 giờ
236.2K
Làm sạch dữ liệu cơ bản trong Excel
Hãy cùng xem phần làm sạch cơ bản, tập trung vào các vấn đề thường gặp như loại bỏ khoảng trắng thừa.
Loại bỏ khoảng trắng thừa
Khoảng trắng ở cuối hoặc thừa có thể gây khó chịu hoặc gây vấn đề vì dẫn đến dữ liệu không nhất quán khi phân tích và phát sinh lỗi định dạng.
Có hai cách chính để loại bỏ khoảng trắng thừa trong Excel. Hãy cùng xem cả hai.
Loại bỏ khoảng trắng thừa bằng Tìm và Thay thế
Dưới đây là các bước để loại bỏ khoảng trắng thừa.
Chọn phạm vi ô bạn muốn loại bỏ khoảng trắng thừa.
Dùng phím tắt Ctrl + H để mở hộp thoại Find and Replace.
Trong ô Find what, nhấn phím cách hai lần để nhập hai khoảng trắng.
Trong ô Replace with, nhấn phím cách một lần để nhập một khoảng trắng.
Nhấp Replace All.
Lặp lại cho đến khi không còn khoảng trắng kép.
Hộp thoại Tìm và Thay thế. Nguồn: Tác giả
Loại bỏ khoảng trắng thừa bằng TRIM
Tạo một cột mới cho dữ liệu đã làm sạch.
Nhập công thức
=TRIM(cell_with_extra_spaces)vào ô đầu tiên của cột mới.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.
Sao chép dữ liệu đã làm sạch và dán dưới dạng giá trị để loại bỏ công thức.
Chọn và xử lý tất cả các ô trống
Ô trống có thể gây vấn đề do làm hỏng công thức, dẫn đến lỗi tính toán và kết quả phân tích không chính xác. Đây là cách xử lý.
Tô sáng phạm vi ô nơi bạn muốn tìm và xử lý ô trống.
Nhấn Ctrl + G để mở hộp thoại Go To.
Nhấp nút “Special…”. Hộp thoại Go To Special sẽ mở ra.
Chọn tùy chọn Blanks và nhấp OK. Lúc này, tất cả ô trống trong phạm vi đã chọn sẽ được chọn.
Bây giờ bạn có thể nhập một giá trị hoặc công thức. Nhấn Ctrl + Enter.
Chọn và xử lý ô trống. Nguồn: Tác giả
Kiểm tra chính tả
Từ viết sai chính tả có thể làm dữ liệu trông thiếu chuyên nghiệp và khó hiểu, vì vậy việc sửa chúng là quan trọng.
Kiểm tra toàn bộ trang tính hoặc một phạm vi cụ thể.
Vào thẻ Review trên Ribbon.
Nhấp nút Spelling trong nhóm Proofing.
Hộp thoại Spelling sẽ mở, hiển thị từ sai chính tả đầu tiên được phát hiện và gợi ý chỉnh sửa.
Xem lại và sửa các từ sai chính tả khi cần.
Kiểm tra chính tả. Nguồn: Tác giả
Xử lý lỗi và xác thực trong Excel
Hãy cùng xem phần xử lý lỗi, bao gồm các tác vụ như tô sáng lỗi hoặc loại bỏ trùng lặp.
Tô sáng lỗi
Lỗi trong dữ liệu có thể dẫn đến kết quả sai và quyết định kém, vì vậy cần xác định và xử lý chúng. Hãy xem cách thực hiện.
Chọn phạm vi ô bạn muốn kiểm tra lỗi.
Vào thẻ Home trên Ribbon.
Trong nhóm Styles, nhấp Conditional Formatting.
Chọn New Rule từ menu xổ xuống.
Chọn Use a formula để xác định ô cần định dạng.
Nhập công thức
=ISERROR(cell)vào trường Format values where this formula is true.Nhấp nút Format… để chọn tùy chọn định dạng mong muốn.
Nhấp OK sau khi chọn xong định dạng.
Nhấp OK lần nữa trong hộp thoại New Formatting Rule để áp dụng quy tắc.
Sử dụng định dạng có điều kiện. Nguồn: Tác giả
Loại bỏ trùng lặp
Mục nhập trùng lặp có thể làm sai lệch phân tích và thông tin chiết xuất, vì vậy loại bỏ chúng giúp đảm bảo độ chính xác của dữ liệu.
Chọn trang tính hoặc phạm vi ô bạn muốn loại bỏ trùng lặp.
Vào thẻ Data trên Ribbon.
Nhấp Remove Duplicates trong nhóm Data Tools.
Trong hộp thoại Remove Duplicates, chỉ định cột cần kiểm tra trùng lặp.
Nhấp OK. Excel sẽ hiển thị thông báo số lượng giá trị trùng đã được loại bỏ.
Loại bỏ các hàng trùng
Các hàng trùng lặp có thể làm rối dữ liệu và ảnh hưởng đến độ tin cậy của phân tích. Đây là cách xử lý.
Chọn phạm vi bạn muốn loại bỏ các hàng trùng lặp.
Vào thẻ Data trên Ribbon và nhấp Advanced trong nhóm Sort & Filter.
Trong hộp thoại Advanced Filter, chọn Copy to another location.
Đặt List range bằng phạm vi đã chọn.
Đặt trường Copy to là ô nơi bạn muốn sao chép các hàng duy nhất.
Chọn Unique Records Only rồi nhấp OK.
Thao tác văn bản trong Excel
Hãy cùng xem các thao tác văn bản như nối chuỗi.
Nối chuỗi
Kết hợp nhiều chuỗi văn bản vào một ô giúp tạo dữ liệu có ý nghĩa và có tổ chức hơn bằng cách gộp thông tin từ nhiều nguồn thành một định dạng mạch lạc.
- Chọn ô nơi bạn muốn hiển thị kết quả nối chuỗi.
- Nhập
=CONCATENATE(). - Chọn các ô cần nối, phân tách bằng dấu phẩy hoặc toán tử &.
- Nhấn Enter để xem kết quả.
Nối các ô. Nguồn: Tác giả
Thay đổi kiểu chữ của văn bản
Kiểu chữ đồng nhất giúp cải thiện khả năng đọc và duy trì tính nhất quán của dữ liệu. Hãy xem cách điều chỉnh.
Tạo một cột mới cho văn bản đã chuyển đổi.
Nhập công thức vào ô:
=UPPER()hoặc=LOWER()hoặc=PROPER()- Nhấn Enter để áp dụng công thức.
Kéo chốt điền để áp dụng công thức cho các ô khác nếu cần.
Thay đổi kiểu chữ văn bản. Nguồn: Tác giả
Loại bỏ ký tự không in khỏi văn bản
Các ký tự không in như tab, xuống dòng và ký tự đặc biệt có thể gây vấn đề vì làm gián đoạn xử lý dữ liệu, khiến khó phân tích hoặc trực quan hóa chính xác.
Xác định các ký tự không in cần loại bỏ.
Chọn ô chứa văn bản có ký tự không in.
Trong một ô mới, dùng hàm:
=CLEAN(text).Để loại bỏ cả khoảng trắng thừa và ký tự không in, dùng:
=TRIM(CLEAN(text)).Sao chép và dán văn bản đã làm sạch để thay thế bản gốc.
Dùng hàm CLEAN trong Excel. Nguồn: Tác giả
Biến đổi dữ liệu trong Excel
Các kỹ thuật biến đổi dữ liệu được dùng để tổ chức và định hình lại dữ liệu nhằm 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.
Phân tách dữ liệu từ văn bản thành cột
Tách dữ liệu văn bản thành các cột riêng giúp dễ phân tích từng thành phần và đảm bảo mỗi mẩu thông tin được phân loại rõ ràng. Đây là thao tác ngược với nối chuỗi và hữu ích để chia nhỏ dữ liệu phức tạp thành các phần dễ xử lý. Cách thực hiện như sau.
Chọn dữ liệu bạn muốn tách.
Vào thẻ Data và nhấp Text to Columns.
Chọn Delimited hoặc Fixed Width rồi nhấp Next.
Với Delimited, chọn các dấu phân tách mà dữ liệu dùng rồi nhấp Next.
Với Fixed Width, đặt điểm ngắt cột trong cửa sổ Data Preview rồi nhấp Next.
Chọn định dạng dữ liệu cho từng cột.
Nhấp Finish.
Dùng Text to Columns để phân tách dữ liệu. Nguồn: Tác giả
Flash Fill
Flash Fill tự động điền giá trị dựa trên các mẫu nhận diện từ dữ liệu của bạn. Cách sử dụng Flash Fill trong Excel:
Nhập dữ liệu theo một mẫu vào ô cạnh dữ liệu hiện có.
Cung cấp ví dụ khác ở ô kế tiếp để Excel nhận diện mẫu.
Chọn ô chứa ví dụ.
Vào thẻ Data trên Ribbon.
Nhấp Flash Fill trong nhóm Data Tools.
Excel sẽ tự động điền các ô còn lại dựa trên mẫu đã nhận diện.
Gộp và tách cột
Gộp và tách cột giúp sắp xếp dữ liệu theo cách phù hợp nhất với nhu cầu phân tích của bạn. Thực hiện gộp ô theo các bước sau:
- Vào thẻ Home, nhấp menu thả xuống Merge & Center trong nhóm Alignment .
- Chọn tùy chọn gộp bạn muốn.
Thực hiện tách cột theo các bước sau:
- Vào thẻ Data và nhấp Text to Columns trong nhóm Data Tools .
- Chọn delimited hoặc fixed width tùy theo nhu cầu dữ liệu.
- Chọn vị trí đích cho dữ liệu đã tách và nhấp Finish.
Biến đổi và sắp xếp lại cột và hàng
Sắp xếp lại dữ liệu giúp trình bày theo định dạng logic và dễ tiếp cận hơn. Để biến đổi hàng thành hàng và cột thành cột:
Tô sáng dữ liệu bạn muốn biến đổi (bao gồm tiêu đề nếu cần).
Nhấp phải vùng chọn và chọn Copy hoặc nhấn Ctrl+C.
Chọn ô nơi dữ liệu biến đổi sẽ bắt đầu.
Nhấp phải ô đích, chọn Paste Special rồi chọn Transpose.
Bạn cũng có thể dùng Ctrl+Alt+V để mở hộp thoại Paste Special, sau đó tích chọn Transpose và nhấp OK.
Để sắp xếp lại cột/hàng:
Nhấp tiêu đề cột để chọn toàn bộ cột cần di chuyển.
Nhấp phải và chọn Cut hoặc nhấn Ctrl+X.
Chọn cột nơi bạn muốn chuyển cột đã cắt đến, nhấp phải tiêu đề cột và chọn Insert Cut Cells.
Tương tự với hàng, chọn và cắt hàng cần chuyển đến hàng khác rồi dán vào đó.
Chỉnh sửa số và ngày tháng trong Excel
Chỉnh sửa số và ngày tháng bao gồm việc 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.
Sửa số và ký hiệu số
Định dạng số không đúng có thể gây ra hiểu nhầm, lỗi tính toán, cũng như vấn đề khi sắp xếp và so sánh dữ liệu.
- Chọn các ô chứa số cần sửa.
- Vào thẻ Home, nhấp menu thả xuống Number trong nhóm Number và chọn định dạng số phù hợp (ví dụ: General, Number, Currency).
Sửa ngày và giờ
Định dạng ngày tháng đúng là yếu tố then chốt cho phân tích và báo cáo theo thời gian chính xác.
Tô sáng các ô chứa ngày tháng.
Vào thẻ Home.
Nhấp menu thả xuống Number Format và chọn Short Date hoặc Long Date.
Năm bước của quy trình làm sạch dữ liệu là gì?
Sự khác nhau giữa làm sạch dữ liệu và biến đổi dữ liệu là gì?
Chủ đề