Hướng dẫn kỹ thuật làm sạch bảng dữ liệu, sửa lỗi dữ liệu trong Excel

Trong thực tế rất khó để đảm bảo bảng dữ liệu không có lỗi. Lỗi này có thể do đánh máy sai, hoặc Excel hiểu sai về kiểu dữ liệu, hoặc dữ liệu bạn nhận được từ người khác... Do vậy kỹ năng làm sạch bảng dữ liệu là không thể thiếu khi bạn làm việc trên Excel. Kỹ năng này bao gồm:

1. Phát hiện các lỗi sai trong bảng dữ liệu

2. Sửa các lỗi sai về đúng

Như trong chương 1 chúng ta đã biết có 3 loại dữ liệu hay gặp là:

• Kiểu Text: là dạng văn bản, có chứa các ký tự chữ

• Kiểu Number: là dạng số, chỉ chứa các con số (có thể kèm theo các ký tự về tiền tệ, %, các dấu ngăn cách phần thập phân, phần nghìn)

• Kiểu Date: là dạng ngày tháng, thời gian. Gồm các con số kết hợp dấu ngăn cách thời gian như dấu gạch ngang ( - ), gạch chéo ( / )  hoặc dấu hai chấm ( : )

Sau đây chúng ta cùng tìm hiểu cụ thể cách làm với các kiểu dữ liệu nhé.

Dữ liệu kiểu Text

Dữ liệu này thường khó phát hiện lỗi bởi nó không có tính quy luật. Đặc biệt trong các bảng dữ liệu lớn thì càng khó khăn hơn, bởi không thể làm điều này bằng mắt thường.

Tuy nhiên may mắn là không phải dữ liệu Text nào cũng phải kiểm tra và sửa lỗi. Bạn chỉ cần kiểm tra trên những cột chứa nội dung quan trọng, phục vụ cho việc lập báo cáo thôi. Những cột này có đặc điểm là dữ liệu có tính lặp lại theo 1 danh sách nhất định. 

Các bước kiểm tra như sau:

Bước 1: tạo bộ lọc (Filter) cho bảng dữ liệu. Bước này sẽ giúp bạn giảm bớt được các nội dung cần đánh giá. Các nội dung trong bộ lọc đã được giản lược đi những nội dung giống nhau (mỗi nội dung giống nhau chỉ xuất hiện 1 lần)

Bước 2: một số đặc điểm lỗi hay gặp:

• Lỗi dấu cách: có thể là sử dụng nhiều hơn 1 dấu cách giữa các từ hoặc có dấu cách thừa ở sau từ cuối cùng trong đoạn text (đặc biệt lỗi thừa dấu cách ở cuối rất hay gặp nhưng khó nhận ra). Lỗi này khiến 1 từ được lặp lại nhiều lần trong bộ lọc:

Lỗi ký tự text về dấu cách trong bộ lọc

• Viết sai chính tả, thiếu dấu khi viết tiếng việt. Những lỗi này khó phát hiện và chỉ có thể nhận biết bằng mắt thường.

• Viết khác font chữ. Khi sử dụng bộ gõ tiếng việt, nhiều người không chú ý việc sử dụng bộ gõ nào. Có 3 bộ gõ hay dùng là TCVN3, VNI và Unicode. Dù cùng 1 nội dung nhưng sử dụng bộ gõ khác nhau thì về bản chất các ký tự text này khác nhau. Bạn cần hiểu rõ các bộ gõ này sử dụng font chữ nào và thiết lập font chữ phù hợp:

• Bộ gõ Unicode: sử dụng các font Arial, Times New Roman, Calibri

• Bộ gõ VNI: sử dụng các font bắt đầu với từ VNI

• Bộ gõ TCVN3: sử dụng các font bắt đầu với từ .Vn

Khi đó để phát hiện có lỗi font chữ không, bạn sẽ thiết lập định dạng toàn bộ bảng dữ liệu về thống nhất hiển thị theo 1 font chữ, ví dụ là Times New Roman. Khi đó các đoạn Text được tạo ra bởi các bộ gõ khác sẽ không hiển thị đúng tiếng việt nữa (bị lỗi font).

Định dạng thống nhất font chữ

Cách khắc phục sai sót

Bước 1: Chọn toàn bộ cột dữ liệu (chọn ô đầu tiên trong cột, sau đó bấm phím tắt Ctrl + Shift + Phím mũi tên đi xuống - down arrow)

Bước 2: Copy nội dung được chọn tại Bước 1, dán ra 1 vị trí khác, Sheet khác

Bước 3: Tại vùng kết quả của Bước 2, thực hiện xóa bỏ dữ liệu trùng lặp với Data > Remove Duplicate

Vị trí công cụ Remove Duplicates trên thẻ Data

Bước 4: Dựa trên danh sách còn lại ở bước 3, bạn nhập lại theo 1 danh sách đúng chính tả

Như vậy bạn sẽ có 1 bảng gồm 2 cột:

• Cột thứ 1: Danh sách sai tạo ra ở Bước 3

• Cột thứ 2: Danh sách đúng tạo ra ở Bước 4

Bước 5: Trong bảng dữ liệu, bạn tạo 1 cột phụ. Mục đích là sử dụng công thức để tham chiếu tới các dữ liệu đúng.

=VLOOKUP( Dữ liệu gốc, Bảng tham chiếu, 2, 0)

Trong đó:

• Dữ liệu gốc: là dữ liệu trong cột chứa dữ liệu cần sửa (tại Bước 1)

• Bảng tham chiếu: là bảng trong Bước 4. Chú ý phải cố định tọa độ tham chiếu tới bảng này.

Bước 6: Kéo công thức (filldown) xuống toàn bộ nội dung theo bảng dữ liệu

Bước 7: Copy kết quả trong cột phụ, dán phần giá trị (Paste Special > Value) vào cột dữ liệu gốc.

Bước 8: Xóa cột phụ, hoàn tất thao tác.

Trong các thao tác trên, quan trọng nhất là bước 3, 4, 5. Mục đích là để tạo ra 1 bảng làm cơ sở gồm cả danh sách sai và danh sách đúng. Bạn chỉ có thể sửa chính tả trên 1 số ít dữ liệu, khó có thể sửa trên số lượng lớn. Do vậy cần lọc bỏ các giá trị trùng lặp trước, sau đó mới sửa lại danh sách.

Dữ liệu kiểu Number

Dữ liệu kiểu Number khá dễ để nhận diện và phát hiện sai sót, bởi nó có tính quy luật và có những đặc trưng riêng. Những sai sót hay gặp với dữ liệu Number thường là:

• Sử dụng dấu ngăn cách phần thập phân, phần nghìn không đúng. Việc này khiến dữ liệu của bạn nhìn giống Số nhưng không phải kiểu số.

• Viết chữ O thay vì số 0. Vì 2 vị trí phím này gần nhau và cũng giống nhau nên rất dễ viết nhầm.

• Dữ liệu chứa các con số nhưng là kiểu Text. Loại lỗi này khó phát hiện bằng mắt thường, bạn cần phải dùng hàm kiểm tra kiểu dữ liệu mới phát hiện được.

Các bước phát hiện sai sót

Bước 1: Tạo 1 cột phụ bên cạnh cột muốn kiểm tra

Bước 2: Tại cột phụ, bạn dùng hàm ISNUMBER để xem dữ liệu tại cột muốn kiểm tra có đúng kiểu Number hay không. Nếu kết quả là False thì không phải kiểu Number.

Bước 3: Sử dụng bộ lọc Filter lọc những kết quả False trên cột phụ.

Cách khắc phục sai sót

Dựa trên kết quả thu được trong quá trình kiểm tra, bạn sẽ xem xét quy luật và sửa những lỗi đó.

• Nếu có xen lẫn các ký tự dấu chấm, dấu phẩy một cách cố ý (bản chất dữ liệu số khi nhập vào Excel sẽ không yêu cầu nhập kèm các ký tự này), bạn hãy xóa các dấu đó đi với chức năng Find & Replace của Excel

• Nếu sử dụng chữ O thay vì số 0, hãy sửa trực tiếp ký tự này (trường hợp này rất ít xảy ra nên có thể sửa trực tiếp từng lỗi)

• Nếu lỗi là sai kiểu dữ liệu (ngoài các trường hợp trên mà vẫn có lỗi) thì bạn sửa bằng cách sau:

• Bước 1: dùng cột phụ, tại ô đầu tiên trong cột phụ bạn dùng hàm Value như sau: =VALUE( dữ liệu gốc )

Trong đó dữ liệu gốc là ô trong cột dữ liệu gốc. Hàm Value sẽ chuyển dữ liệu về dạng Number

• Bước 2: Kéo công thức tới hết cột phụ

• Bước 3: Sao chép và dán phần giá trị (Copy và Paste Special > Value) từ cột phụ vào trong cột chính.

• Bước 4: Xóa cột phụ, hoàn tất thao tác

Tham khảo: Cách phân biệt dạng dữ liệu trong Excel với nhóm hàm IsNumber, IsNonText, IsText

Dữ liệu kiểu Date

Dữ liệu ngày tháng, thời gian là loại dữ liệu thường xuyên sử dụng và cũng hay có sai sót nhất. Do chúng ta không để ý tới việc thiết lập chuẩn ngày tháng trong Region dẫn tới việc nhập sai về thứ tự các số ngày, số tháng trong chuỗi ngày tháng năm (nội dung này đã được nói rõ tại chương 1).

Việc thiết lập thời gian lại tùy thuộc vào từng máy tính. Khi bạn đưa file Excel sang 1 máy tính có thiết lập thời gian khác thì dữ liệu ngày tháng lại tự động thay đổi theo máy tính mới. Việc này gây ra nhiều sai sót, nhầm lẫn trong quá trình thu thập dữ liệu thời gian.

Các bước phát hiện sai sót

Có 2 bước cơ bản để kiểm tra dữ liệu thời gian có sai sót hay không:

• Bước 1: Kiểm tra bằng bộ lọc Filter. Khi bấm chọn bộ lọc trên cột chứa dữ liệu ngày tháng, những dữ liệu đúng sẽ được tự động gộp nhóm theo Năm, Tháng, Ngày. Những dữ liệu không được gộp vào nhóm thường là dữ liệu sai.

• Bước 2: Kiểm tra bằng hàm ISNUMBER. Nếu kết quả ra FALSE thì đó không phải dữ liệu thời gian.

Cách khắc phục sai sót

Về bản chất dữ liệu thời gian là dữ liệu kiểu số (number) nhưng có cách định dạng riêng (theo dạng Date). Do đó cách xử lý lỗi của dữ liệu Date giống với Number, đó là dùng hàm Value.

Sau khi thu được kết quả, bạn chỉ cần định dạng lại dữ liệu đó về dạng ngày tháng là được.

Tham khảo: Cách thiết lập định dạng ngày tháng chuẩn khi làm việc trên Excel

Bài học rút ra

1. Luôn kiểm tra lại nội dung trong bảng dữ liệu để đề phòng sai sót. Nếu dữ liệu sai thì bạn không thể làm ra được báo cáo đúng.

2. Mỗi kiểu dữ liệu lại có 1 cách kiểm tra và sửa khác nhau. Hãy học thuộc và luyện tập kỹ các kỹ thuật tìm và sửa lỗi này, vì thực tế đôi khi phức tạp hơn nhiều so với ví dụ trong bài viết này.

   
   

   Ngoài ra các bạn có thể tham khảo khóa học EXG01 - Tuyệt đỉnh Excel của Gitiho để nâng cao hiệu quả làm việc trên Excel nhé. Đây là khóa học cung cấp rất đầy đủ và chi tiết các kiến thức về Excel, sử dụng Excel vào báo cáo. Bạn sẽ thấy rõ sự khác biệt sau khi tham gia khóa học này.