Với nhập liệu và tổ chức dữ liệu, Microsoft Excel hiện nay cũng thực hiện dễ dàng phân tích dữ liệu mức độ sơ cấp đến trung cấp. Đối với sử dụng hàng ngày, nó cung cấp các công cụ chức năng đi rất xa. Làm sạch dữ liệu (Data cleaning) là bước chủ yếu cho bất kỳ phương pháp phân tích dữ liệu nào. Nó bao gồm loại bỏ, chuẩn bị hoặc chuyển đổi giá trị không mong muốn hoặc bất quy tắc thành giá trị có thể sử dụng. Trong hướng dẫn này, chúng ta sẽ thảo luận các kỹ thuật làm sạch dữ liệu khác nhau và cách thực hiện trong Microsoft Excel.
Bạn có thể tải tệp thực hành (workbook) dùng cho bàidemo từ liên kết dưới đây.
19 Kỹ thuật Làm sạch Dữ liệu trong Excel Sẽ Hữu ích
Chúng ta sẽ thảo luận tổng cộng mười chín kỹ thuật làm sạch dữ liệu trong Excel. Tất cả đều quan trọng ở mức độ khác nhau. Theo dõi để xem từng kỹ thuật hoạt động như thế nào hoặc tìm kỹ thuật bạn cần từ mục lục.
1. Xóa các hàng trùng lặp (Remove Duplicate Rows)
Dữ liệu của bạn có thể có hàng trùng lặp, bất kể lý do. Đa số thời gian bạn cần loại bỏ hàng trùng. Xưa kia, xóa dữ liệu trùng là việc thủ công — dù có thể dùng kỹ thuật nâng cao. Nhưng với lệnh Xóa trùng lặp (Remove Duplicates), việc loại bỏ trùng lặp giờ dễ dàng. Lệnh này được giới thiệu từ Excel 2007.
Hãy nhân bản cột bên cạnh rồi xóa trùng để so sánh sau.
Các bước xóa trùng lặp:
Các bước:
- Đầu tiên, chọn cột muốn xóa trùng.
- Vào tab Dữ liệu (Data) trên Ribbon.
- Chọn Xóa trùng lặp (Remove Duplicates) từ nhóm Công cụ dữ liệu (Data Tools).
- Sau đó, chọn tùy chọn Tiếp tục với lựa chọn hiện tại (Continue with the current selection) trong hộp thoại và nhấn Xóa trùng lặp.
- Tiếp theo, nhấn OK.
Thao tác này xóa tất cả bản trùng khỏi lựa chọn.
2. Làm nổi bật giá trị trùng lặp (Highlight Duplicate Values)
Đôi khi thay vì xóa hoàn toàn, làm nổi bật giá trị trùng lại hữu ích. Ở đây chúng ta trình bày các kịch bản và phương pháp khác nhau.
Sử dụng Định dạng có điều kiện (Conditional Formatting)
Phần đầu, dùng tính năng Định dạng có điều kiện để làm nổi bật giá trị trùng. Áp dụng trên tập dữ liệu sau:
Các bước:
Các bước:
- Thứ nhất, chọn ô cần kiểm tra trùng. Ví dụ: D5:D18.
- Thứ hai, vào Trang chủ (Home) > chọn Định dạng có điều kiện (Conditional Formatting) > Quy tắc làm nổi bật ô (Highlight Cells Rules) > chọn Giá trị trùng lặp (Duplicate Values).
- Cuối cùng, cửa sổ Giá trị trùng lặp hiện ra.
- Thứ ba, chọn Trùng lặp (Duplicate).
- Thứ tư, chọn tùy chọn màu trong giá trị với (values with). Ví dụ: Nền đỏ nhạt, chữ đỏ đậm (Light Red Fill with Dark Red Text).
- Kết quả: các giá trị trùng trong cột D được làm nổi bật.
Làm nổi bật trùng lặp bao gồm lần xuất hiện đầu tiên
Cùng tập dữ liệu, giờ làm nổi bật trùng lặp kể cả lần đầu tiên. Giống phương pháp trước nhưng dùng hàm COUNTIF. Các bước:
Các bước:
- Thứ nhất, mở cửa sổ Quy tắc định dạng mới (New Formatting Rule) qua Định dạng có điều kiện.
- Thứ hai, chọn Sử dụng công thức xác định ô cần định dạng (Use a formula to determine which cells to format).
- Thứ ba, nhập công thức sau vào ô công thức.
=COUNTIF($D$5:$E$16,$D5)>1
- Kết quả: cột D được làm nổi bật cho trùng lặp, kể cả lần xuất hiện đầu tiên.
Làm nổi bật trùng lặp loại trừ lần xuất hiện đầu tiên
Kỹ thuật hữu ích khác: xác định giá trị trùng không làm nổi bật lần đầu tiên. Hữu ích khi chỉ muốn quan sát giá trị lặp lại. Cũng dùng hàm COUNTIF.
Các bước:
- Vào Quy tắc định dạng mới qua Định dạng có điều kiện như trước.
- Chọn Sử dụng công thức xác định ô cần định dạng.
- Nhập công thức:
=COUNTIF($D$5:$D5,$D5)>1
- Nhấn OK, tất cả trùng lặp trừ lần đầu sẽ được làm nổi bật.
3. Tách văn bản thành nhiều ô (Split Text into Multiple Cells)
Khi nhập dữ liệu từ nguồn khác, có thể nhiều giá trị bị nhập chung một cột. Hình dưới minh họa trường hợp này.
Tính năng Văn bản thành cột (Text to Columns) giúp tách văn bản này.
Các bước:
- Chọn phạm vi cần tách.
- Vào tab Dữ liệu (Data) trên Ribbon.
- Từ nhóm Công cụ dữ liệu, chọn Văn bản thành cột (Text to Columns).
- Chọn Có dấu phân cách (Delimited) trong hộp tiếp theo.
- Nhấn Tiếp theo (Next), hộp khác hiện ra.
- Chọn Khoảng trắng (Space) dưới Dấu phân cách (Delimiter) vì văn bản tách bằng khoảng trắng.
- Nhấn Tiếp theo.
- Nhấn Hoàn tất (Finish).
- Văn bản đã được tách.
- Làm vài điều chỉnh để trình bày đẹp hơn.
Đây là cách tách văn bản thành các ô khác nhau cùng các kỹ thuật làm sạch dữ liệu khác.
4. Chuyển đổi dữ liệu bằng công thức (Transforming Data with Formulas)
Đôi khi bạn muốn chữ cái trong văn bản thống nhất chữ hoa/thường. Excel không có cách trực tiếp đổi chữ, nhưng dễ dàng làm bằng công thức. Ba công thức đổi chữ:
- UPPER: Chuyển thành CHỮ HOA.
- LOWER: Chuyển thành chữ thường.
- PROPER: Chuyển thành Chữ cái đầu viết hoa (Proper Case — chữ cái đầu mỗi từ viết hoa, như tên riêng).
Sử dụng tập dữ liệu sau cho tất cả ví dụ:
Đổi thành Chữ HOA
Dùng hàm UPPER. Các bước:
- Chọn ô C5.
- Nhập công thức:
=UPPER(B5)
- Nhấn Enter.
- Chọn lại ô, kéo thanh tràn (fill handle) để điền công thức cho các ô còn lại.
Đổi thành chữ thường
Tương tự, chuyển tất cả thành chữ thường:
- Chọn ô D5.
- Nhập công thức:
=LOWER(B5)
- Nhấn Enter.
- Kéo thanh tràn điền công thức.
Đổi thành Chữ Cái Đầu Viết Hoa (Proper Case)
Proper Case: chữ cái đầu mỗi từ viết hoa, các chữ khác thường. Dùng hàm PROPER:
- Chọn ô E5.
- Nhập công thức:
=PROPER(B5)
- Nhấn Enter.
- Kéo thanh tràn điền công thức cho các ô còn lại.
5. Xóa khoảng trắng thừa (Remove Extra Spaces)
Đảm bảo văn bản không có khoảng trắng thừa rất quan trọng. Không thể nhìn thấy ký tự khoảng trắng ở cuối chuỗi. Khoảng trắng thừa gây rắc rối, nhất khi so sánh chuỗi. Ví dụ: "July" khác "July " (có dấu cách cuối). Thứ nhất 4 ký tự, thứ hai 5 ký tự.
Hai kỹ thuật xóa khoảng trắng thừa:
Sử dụng tập dữ liệu sau:
Dùng hàm TRIM
Hàm TRIM xóa tất cả khoảng trắng đầu và cuối chuỗi. Cũng thay thế nhiều khoảng trắng liên tiếp bằng một khoảng trắng đơn.
Các bước:
- Thêm cột Dữ liệu cập nhật (Updated Data) hiển thị kết quả.
- Chọn ô D5.
- Nhập =TRIM và chọn ô C5 làm đối số đầu. Đóng ngoặc.
- Công thức:
=TRIM(C5) - Nhấn Enter.
- Kéo thanh tràn đến ô cuối.
Khoảng trắng không cần thiết đã bị xóa, chỉ còn khoảng trắng đơn giữa các từ.
Dùng tính năng Tìm & Thay thế (Find & Replace):
Cách dùng lệnh Tìm & Thay thế để xóa tất cả khoảng trắng.
Các bước:
- Chọn dữ liệu cần xóa khoảng trắng thừa.
- Vào tab Trang chủ (Home).
- Từ nhóm Chỉnh sửa (Editing), vào Tìm & Chọn (Find & Select).
- Chọn Thay thế (Replace) từ menu thả xuống.
- Hộp thoại hiện ra.
- Nhập một khoảng trắng vào trường Tìm gì (Find what).
- Để trống trường Thay bằng (Replace with).
- Nhấn Thay thế tất cả (Replace All).
- Hộp thoại hiện số lần thay thế.
- Nhấn OK.
- Đóng hộp thoại.
- Kết quả đạt được.
6. Xóa ký tự lạ (Remove Strange Characters)
Thường khi nhập dữ liệu vào Excel, bạn thấy ký tự lạ (đôi khi không in được). Dùng hàm CLEAN để xóa tất cả ký tự không in được khỏi văn bản. Nếu dữ liệu ở ô A2, dùng:
=CLEAN(A2)
Hàm CLEAN đôi khi bỏ sót ký tự Unicode không in. Nó được lập trình xóa 32 ký tự không in đầu tiên trong mã ASCII 7-bit. Xem Trợ giúp Excel để biết cách xóa ký tự Unicode không in.
7. Chuyển đổi giá trị (Convert Values)
Kỹ thuật quan trọng khác: chuyển đổi giá trị trong tập dữ liệu sang giá trị khác. Đôi khi cần chuyển đổi giữa các hệ đo lường. Ví dụ: nhập file có đơn vị ounce lỏng (fl oz), cần thể hiện bằng mililit. Hàm CONVERT của Excel thực hiện loại chuyển đổi này và nhiều loại khác.
Hàm này rất hữu ích, xử lý được hầu hết đơn vị đo phổ biến: khối lượng, khoảng cách, thời gian, áp suất, lực, năng lượng, công suất, từ tính, nhiệt độ, thể tích, lỏng, diện tích, bit/byte, tốc độ.
Minh họa: xem tập dữ liệu và áp dụng. Đây là tập dữ liệu:
Các bước đổi nhiệt độ sang Fahrenheit:
Các bước:
- Chọn ô đầu ra D5.
- Nhập công thức:
=CONVERT(C5,"C","F")
- Nhấn Enter.
- Kéo thanh tràn đến cuối cột để chuyển đổi tất cả giá trị.
Đây là cách chuyển đổi giá trị sang đơn vị khác. Đối với chuyển đổi đơn vị khác, xem bài viết này.
8. Làm nổi bật lỗi (Highlight Errors)
Do thiếu công thức, chuỗi hoặc giá trị đúng, bảng tính báo lỗi. Tìm thủ công từng cái tốn thời gian. Dưới đây kỹ thuật dễ dàng tìm và làm nổi bật lỗi.
Giả sử tập dữ liệu có lỗi ở vài ô. Làm nổi bật bằng Định dạng có điều kiện và tính năng Chuyển đến đặc biệt (Go to Special).
Xem bảng tính sau:
Các bước làm nổi bật lỗi:
Các bước:
- Trước hết, chọn toàn bộ tập dữ liệu, nhấn Quy tắc mới (New Rule) từ tùy chọn Định dạng có điều kiện.
- Trong loại quy tắc, chọn Chỉ định dạng ô chứa (Format Only cells that contain) rồi chọn Lỗi (Errors) từ menu thả xuống.
- Nhấn OK.
Kết quả cuối: Excel làm nổi bật các giá trị lỗi.
Đây là cách làm nổi bật lỗi như một kỹ thuật làm sạch dữ liệu trong Excel.
9. Gộp cột (Join Columns)
Gộp cột là kỹ thuật đôi khi cần khi làm sạch hoặc sắp xếp lại dữ liệu. Minh họa với tập dữ liệu sau:
Các bước gộp văn bản từ các ô riêng lẻ:
Các bước:
- Chọn ô E5.
- Nhập công thức:
=B5&" "&D5&" "&C5
- Nhấn Enter.
- Kéo thanh tràn đến cuối cột để áp dụng cho các ô còn lại.
Đây là cách gộp hai hoặc nhiều cột trong Excel.
10. Sắp xếp lại cột (Rearrange Columns)
Đôi khi cần sắp xếp lại thứ tự cột. Cách thông thường: tạo cột trống, kéo cột khác vào, xóa cột trống sau. Dưới đây cách dễ hơn trên tập dữ liệu:
Các bước:
- Chọn cột muốn di chuyển bằng cách nhấn tiêu đề cột.
- Cắt cột (Cut) bằng menu chuột phải hoặc Ctrl+X. Đường viền chấm hiện ra.
- Chuột phải vào tiêu đề cột trước vị trí muốn đặt cột vừa cắt.
- Chọn Chèn ô đã cắt (Insert Cut Cells) từ menu.
- Cột trước sẽ được chèn vào đây.
Đây là cách sắp xếp lại cột như một phần của kỹ thuật làm sạch dữ liệu.
11. Xáo trộn hàng (Randomize Rows)
Phần này nói về xáo trộn hàng. Dù không phổ biến trong hầu hết trường hợp, vẫn là thủ thuật tiện nếu muốn xáo trộn tập dữ liệu hoặc thay đổi thứ tự danh sách.
Thực hiện trên tập dữ liệu sau:
Các bước xáo trộn hàng và thay đổi thứ tự:
Các bước:
- Trước hết, tạo cột mới sau cột B. Nhấp chữ cái tiêu đề cột C, toàn bộ cột C được chọn.
- Chuột phải, chọn Chèn (Insert), cột mới tạo ra.
- Đặt tên cột mới (ví dụ: Số ngẫu nhiên - Random Number). Chọn ô đầu tiên (C5), nhập công thức:
=RAND()
- Nhấn ENTER, số ngẫu nhiên (<1) hiện ở C5.
- Kéo thanh tràn đến cuối cột để điền công thức cho các ô còn lại.
- Khi ô C5 đang chọn, vào tab Trang chủ (Home).
- Vào nhóm Chỉnh sửa (Editing), nhấn Sắp xếp & Lọc (Sort & Filter).
- Chọn Sắp xếp từ nhỏ đến lớn (Sort Smallest to Largest).
- Ngay lập tức, các hàng xáo trộn. Không chỉ cột C, tất cả cột cũng thay đổi ngẫu nhiên theo hàng.
Đây là các bước khi có tập dữ liệu nhiều cột và muốn xáo trộn hàng sao cho tất cả cột cùng hàng xáo trộn giá trị.
12. Trích xuất tên tệp từ URL (Extract File Name from URL)
Đôi khi bạn có danh sách URL và chỉ cần tên tệp. Cách sau trích xuất tên tệp từ URL. Giả sử ô B5 chứa URL: https://example.com/assets/images/horse.jpg.
Cần công thức kết hợp nhiều hàm: RIGHT, LEN, FIND, SUBSTITUTE. Đừng lo, có giải thích sau. Công thức:
Các bước:
- Chọn ô đặt tên tệp, ví dụ C5.
- Nhập công thức:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))))))
- Nhấn Enter.
🔎 Phân tích công thức
👉SUBSTITUTE(B5,"/","") xóa tất cả "/" khỏi URL. Trả về: http:www.example.comassetsimageshorse.jpg.👉LEN(SUBSTITUTE(B5,"/","")) trả về độ dài chuỗi trên.👉LEN(B5) trả về độ dài chuỗi gốc trong B5 (46 ở đây).👉LEN(B5)-LEN(SUBSTITUTE(B5,"/","")) cho biết số lượng "/" đã bị xóa.👉SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))) thay thế tất cả "/" trong B5 bằng "*" với số lần xuất hiện từ kết quả trước.👉FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))) tìm vị trí "*" trong chuỗi hiện tại.👉LEN(B5)-FIND(...) là hiệu giữa độ dài gốc và vị trí trên.👉Cuối cùng, RIGHT(B5,...) trích xuất số ký tự đó từ bên phải ô.
13. Khớp văn bản trong danh sách (Match Text in List)
Xem hình sau. Chúng ta tìm người đã nghỉ việc từ phía bên trái. Danh sách số thành viên nghỉ việc ở bên phải.
Ví dụ đơn giản. Dữ liệu trong phạm vi B5:D133. Mục tiêu: xác định hàng trong vùng dữ liệu xuất hiện trong danh sách Thành viên nghỉ việc (Resigned Members) ở cột G. Có thể xóa các hàng không cần thiết sau. Cần kết hợp hàm IF và COUNTIF.
Các bước:
- Chọn ô E5.
- Nhập công thức:
=IF(COUNTIF($G$5:$G$25,C5),"Nghỉ việc","")
- Nhấn Enter.
- Nhấp đúp thanh tràn góc dưới phải ô. Hoặc kéo chuột. Vì dữ liệu lớn, dùng nhấp đúp.
- Bảng tính sẽ trông như sau.
Đây là cách khớp văn bản như một phần của kỹ thuật phân tích dữ liệu trong Excel.
14. Chuyển dữ liệu dọc sang ngang (Change Vertical Data to Horizontal)
Chuyển đổi (transpose) dữ liệu dọc sang ngang và ngược lại là kỹ thuật phổ biến trong phân tích dữ liệu. Giờ nói về chuyển dữ liệu dọc sang ngang.
Sử dụng tập dữ liệu sau:
Dùng tùy chọn Dán đặc biệt (Paste Special)
Cách dễ nhất chuyển cột dọc thành hàng ngang: dùng Dán đặc biệt (Paste Special). Cách này giữ nguyên định dạng, không cần định dạng lại. Các bước:
- Chọn phạm vi muốn chuyển sang hàng ngang. Ví dụ: B4:C10.
- Nhấn Ctrl+C sao chép.
- Chọn ô nơi muốn dán ngang. Ví dụ: ô B12.
Kết quả: cột dọc thành hàng ngang.
Dùng hàm TRANSPOSE:
Có thể dùng hàm Excel chuyển cột dọc sang hàng ngang. Ở đây dùng hàm TRANSPOSE. Ưu điểm: hàng ngang cập nhật động nếu dữ liệu gốc thay đổi. Nhược điểm: không giữ định dạng gốc, cần định dạng lại.
Các bước:
- Chọn ô đặt công thức.
- Nhập công thức:
=TRANSPOSE(B4:C10)
- Nhấn Enter.
Đây là cách chuyển dữ liệu dọc sang ngang dùng hàm TRANSPOSE.
15. Điền ô trống (Fill Blank Cells)
Một trong những kỹ thuật quan trọng nhất trong mọi phân tích dữ liệu: điền giá trị trống, hoặc bằng trung bình (dữ liệu số) hoặc giá trị từ hàng trước/sau.
Đôi khi để ô trống để hiểu đó là giá trị đầu tiên sẽ điền. Nhưng đôi khi gây rắc rối khi lọc/sắp xếp. Tốt hơn điền đầy, và có cách dễ.
Tập dữ liệu:
Hai phương pháp đơn giản:
Dùng lệnh Điền (Fill Command)
Nếu dữ liệu nhỏ, điền thủ công hoặc dùng lệnh. Các bước:
- Chọn ô B6.
- Vào Trang chủ (Home).
- Từ nhóm Chỉnh sửa, nhấn Điền (Fill) rồi Xuống (Down).
- Dữ liệu giờ như sau.
- Lặp lại cho các ô trống tiếp theo, lấy giá trị từ trên xuống.
- Thay vì chọn từng ô, làm luôn: chọn ô đã điền, nhấp đúp dấu cộng (+) (plus icon) của ô.
- Ngay lập tức tất cả ô trống được điền cùng giá trị.
Kết quả cuối:
Dùng phím tắt:
Cùng kết quả với phím tắt Ctrl+D. Chọn ô trống, nhấn phím tắt — ô sẽ lấy giá trị từ ô trên cùng cột. Các bước chi tiết:
- Chọn ô B6.
- Nhấn Ctrl+D.
- Chọn ô B9 nhấn Ctrl+D cho kết quả sau.
- Điền hết các ô trống bằng cách chọn và nhấn phím tắt.
16. Kiểm tra chính tả (Check Spelling)
Khi dùng Word hoặc trình soạn thảo khác, bạn thường dùng kiểm tra chính tả. Lỗi chính tả trong văn bản gây xấu hổ. Trong Excel, lỗi chính tả cũng gây vấn đề nghiêm trọng. Ví dụ: tổng hợp dữ liệu theo tháng, tên tháng sai chính tả sẽ khiến một năm có 13 tháng.
Tập dữ liệu minh họa:
Sửa phạm vi C5:C7. Các bước:
Các bước:
- Chọn phạm vi C5:C7.
- Vào tab Xem lại (Review), chọn Chính tả (Spelling) từ nhóm Kiểm tra (Proofing). Hoặc bỏ qua bước này nhấn F7.
- Chọn gợi ý đúng, nhấn Thay đổi (Change) để sửa giá trị ô. Lỗi xuất hiện theo thứ tự chọn.
- Lỗi đầu tiên đã sửa.
- Lặp lại cho tất cả lỗi để có chính tả đúng.
17. Thay thế hoặc xóa văn bản (Replace or Remove Text)
Loại bỏ và thay thế dữ liệu, nhất là giá trị ngoại lai (outliers), là nhiệm vụ quan trọng trong quy trình làm sạch dữ liệu. Phần này demo thay thế/xóa văn bản trong Excel với ba phương pháp.
Dùng tập dữ liệu sau cho tất cả:
Dùng tính năng Tìm & Thay thế (Find & Replace):
Excel có tính năng trực tiếp tìm, thay thế, xóa giá trị. Các bước:
- Chọn phạm vi cần sửa. Ví dụ: C5:C13.
- Vào Trang chủ (Home).
- Chọn Tìm & Thay thế (Find & Replace) từ nhóm Chỉnh sửa.
- Chọn Thay thế (Replace) từ menu thả xuống.
- Chuyển tab Thay thế (Replace) trong hộp thoại.
- Nhập giá trị muốn thay vào Tìm gì (Find what), giá trị mới vào Thay bằng (Replace with).
- Nhấn Thay thế tất cả (Replace All) để thay thế toàn bộ tập dữ liệu.
Kết quả sau khi xóa tất cả dấu gạch nối (hyphens):
Tận dụng Flash Fill:
Tính năng Flash Fill cũng giúp thay thế/xóa giá trị khi làm sạch dữ liệu. Với cùng tập dữ liệu, các bước:
- Nhập thủ công kết quả mong muốn (sau khi thay/xóa) ở ô đầu tiên. Ví dụ: thay dấu gạch nối thứ hai bằng hai chấm.
- Bắt đầu nhập thủ công ô thứ hai. Ngay khi bắt đầu, Excel nhận diện mẫu và gợi ý kết quả cho các ô còn lại.
Lưu ý
Nếu gợi ý không xuất hiện ở ô thứ hai, thử nhập thủ công. Đôi khi Excel gợi ý ở ô thứ ba hoặc tư nếu mẫu không rõ ràng.
- Nhấn Enter.
Bạn đã thay thế/xóa giá trị bằng tính năng tiện lợi này khi làm sạch dữ liệu.
Dùng hàm SUBSTITUTE:
Cách khác thay thế/xóa giá trị: dùng hàm SUBSTITUTE. Hàm nhận ba đối số: chuỗi, giá trị cần thay, giá trị thay thế. Các bước:
- Chọn ô C5.
- Nhập công thức:
=SUBSTITUTE(B5,"-",":",2)
- Nhấn Enter.
- Kéo thanh tràn đến cuối cột áp dụng cho các ô còn lại.
18. Thêm văn bản vào ô (Add Text to Cells)
Thêm văn bản không chỉ phổ biến trong làm sạch dữ liệu mà cả thao tác Excel chung. Có nhiều cách thêm văn bản. Minh họa với tập dữ liệu:
Các phương pháp:
Dùng ký hiệu & (Ampersand):
Ký hiệu & trong công thức nối chuỗi. Nối chuỗi trước và sau nó. Các bước:
- Chọn ô C5.
- Nhập công thức:
=B5&".mp3"
- Nhấn Enter.
- Kéo thanh tràn đến cuối cột.
Áp dụng Flash Fill:
Các bước thêm văn bản bằng Flash Fill:
- Nhập thủ công giá trị mong muốn đầu tiên ở ô C5.
- Bắt đầu điền ô thứ hai. Khi bắt đầu, Excel gợi ý mẫu.
- Khi gợi ý hiện ra, nhấn Enter.
Cách khác thêm văn bản trong Excel.
Dùng hàm CONCATENATE:
Hàm CONCATENATE như tên gọi, nối hai hoặc nhiều chuỗi. Nhận các chuỗi làm đối số. Hoạt động giống phương pháp &. Các bước:
- Chọn ô C5.
- Nhập công thức:
=CONCATENATE(B5,".mp3")
- Nhấn Enter.
- Kéo thanh tràn đến cuối cột.
19. Sửa dấu trừ ở cuối (Fix Trailing Minus Sign)
Đôi khi lấy dữ liệu từ nguồn khác, giá trị âm có dấu trừ ở cuối (trailing minus sign). Xem hình:
Excel không coi giá trị có dấu trừ cuối là số — nó xem là văn bản. Gây lỗi không chỉ khi làm sạch dữ liệu mà cả thao tác chung như dùng công thức. Cần chuyển thành định dạng số âm chuẩn của Excel.
Các bước đơn giản:
Các bước:
- Chọn phạm vi cần chuyển. (Đã sao chép để so sánh).
- Vào tab Dữ liệu (Data) trên Ribbon.
- Chọn Văn bản thành cột (Text to Columns) từ nhóm Công cụ dữ liệu.
- Nhấn trực tiếp Hoàn tất (Finish) trong trình dẫn.
Tất cả giá trị âm có dấu trừ cuối tự động chuyển sang định dạng số âm của Excel.
Thủ tục này hoạt động nhờ cài đặt mặc định trong hộp thoại Cài đặt nhập văn bản nâng cao (Advanced Text Import Settings).
Kết luận
Trên đây là những kỹ thuật làm sạch dữ liệu trong Excel hữu ích nhất cho phân tích dữ liệu. Hy vọng bạn áp dụng dễ dàng. Nếu có thắc mắc hoặc góp ý, hãy để lại bình luận.
Xem thêm hướng dẫn tại Exceldemy.com.