Làm sạch dữ liệu với các hàm văn bản

Dữ liệu mà bạn thu được từ nhiều nguồn khác nhau không ở dạng sẵn sàng để phân tích. Trong chương này, bạn sẽ hiểu cách chuẩn bị dữ liệu của bạn ở dạng văn bản để phân tích.

Ban đầu, bạn cần làm sạch dữ liệu. Làm sạch dữ liệu bao gồm xóa các ký tự không mong muốn khỏi văn bản. Tiếp theo, bạn cần cấu trúc dữ liệu theo dạng bạn yêu cầu để phân tích thêm. Bạn có thể làm điều tương tự bằng cách -

Tìm các mẫu văn bản cần thiết với các chức năng văn bản.
Trích xuất giá trị dữ liệu từ văn bản.
Định dạng dữ liệu với các hàm văn bản.
Thực thi các thao tác dữ liệu với các hàm văn bản.

Xóa các ký tự không mong muốn khỏi văn bản

Khi bạn nhập dữ liệu từ một ứng dụng khác, nó có thể có các ký tự không in được và / hoặc khoảng trắng thừa. Các khoảng trống thừa có thể là -

dấu cách hàng đầu và / hoặc
khoảng trống thừa giữa các từ.

Nếu bạn sắp xếp hoặc phân tích dữ liệu như vậy, bạn sẽ nhận được kết quả sai.

Hãy xem xét ví dụ sau:

Đây là dữ liệu thô mà bạn có được về thông tin sản phẩm có chứa ID sản phẩm, mô tả sản phẩm và giá. Ký tự “|” tách trường trong mỗi hàng.

Khi bạn nhập dữ liệu này vào trang tính Excel, nó trông như sau:

Khi bạn quan sát, toàn bộ dữ liệu nằm trong một cột duy nhất. Bạn cần cấu trúc dữ liệu này để thực hiện phân tích dữ liệu. Tuy nhiên, ban đầu bạn cần làm sạch dữ liệu.

Bạn cần xóa mọi ký tự không in được và khoảng trắng thừa có thể có trong dữ liệu. Bạn có thể sử dụng chức năng CLEAN và chức năng TRIM cho mục đích này.

Không.	Mô tả chức năng
1.	CLEAN Xóa tất cả các ký tự không in được khỏi văn bản
2.	TRIM Xóa khoảng trắng khỏi văn bản

Chọn các ô C3 - C11.
Nhập = TRIM (CLEAN (B3)) rồi nhấn CTRL + Enter.

Công thức được điền vào các ô C3 - C11.

Kết quả sẽ như hình dưới đây -

Tìm các mẫu văn bản bắt buộc với các hàm văn bản

Để cấu trúc dữ liệu của mình, bạn có thể phải thực hiện đối sánh Mẫu văn bản nhất định dựa trên đó bạn có thể trích xuất Giá trị dữ liệu. Một số Hàm văn bản hữu ích cho mục đích này là:

Không.	Mô tả chức năng
1.	EXACT Kiểm tra xem hai giá trị văn bản có giống nhau không
2.	FIND Tìm một giá trị văn bản trong một giá trị văn bản khác (phân biệt chữ hoa chữ thường)
3.	SEARCH Tìm một giá trị văn bản trong một giá trị văn bản khác (không phân biệt chữ hoa chữ thường)

Trích xuất giá trị dữ liệu từ văn bản

Bạn cần trích xuất dữ liệu cần thiết từ văn bản để cấu trúc giống nhau. Giả sử trong ví dụ trên, bạn cần đặt dữ liệu vào ba cột - ProductID, Product_Description và Price.

Bạn có thể trích xuất dữ liệu theo một trong những cách sau:

Trích xuất giá trị dữ liệu với trình hướng dẫn chuyển đổi văn bản thành cột
Trích xuất giá trị dữ liệu bằng hàm văn bản
Trích xuất giá trị dữ liệu với Flash Fill

Trích xuất giá trị dữ liệu với trình hướng dẫn chuyển đổi văn bản thành cột

Bạn có thể dùng Convert Text to Columns Wizard để trích xuất Giá trị Dữ liệu vào các cột Excel nếu các trường của bạn -

Được phân cách bằng một ký tự hoặc
Căn chỉnh trong các cột có dấu cách giữa mỗi trường.

Trong ví dụ trên, các trường được phân tách bằng ký tự “|”. Do đó, bạn có thể sử dụngConvert Text to Columns Thuật sĩ.

Chọn dữ liệu.
Sao chép và dán các giá trị vào cùng một nơi. Nếu không thì,Convert Text to Columns lấy các chức năng thay vì chính dữ liệu làm đầu vào.

Chọn dữ liệu.
Bấm vào Text to Columns bên trong Data Tools nhóm dưới Data Tab trên Ribbon.

Step 1 - Trình hướng dẫn Chuyển đổi Văn bản thành Cột - Bước 1/3 xuất hiện.

Chọn Delimited.
Bấm tiếp.

Step 2 - Trình hướng dẫn Chuyển đổi Văn bản thành Cột - Bước 2/3 xuất hiện.

Dưới Delimiters, lựa chọn Other.
Trong hộp bên cạnh Other, nhập ký tự |
Nhấp chuột Next.

Step 3 - Trình hướng dẫn Chuyển đổi Văn bản thành Cột - Bước 3/3 xuất hiện.

Trong màn hình này, bạn có thể chọn từng cột dữ liệu của mình trong trình hướng dẫn và đặt định dạng cho cột đó.

Đối với Destination, chọn ô D3.
Bạn có thể bấm vào Advanced, và thiết lập Decimal Separator và Thousands Separator bên trong Advanced Text Import Settings hộp thoại xuất hiện.
Nhấp chuột Finish.

Dữ liệu của bạn, được chuyển đổi thành các cột sẽ xuất hiện trong ba Cột - D, E và F.

Đặt tên cho các tiêu đề Cột là ProductID, Product_Description và Price.

Trích xuất giá trị dữ liệu bằng hàm văn bản

Giả sử các trường trong dữ liệu của bạn không được phân tách bằng ký tự cũng như không được căn chỉnh trong các cột có dấu cách giữa mỗi trường, bạn có thể sử dụng các hàm văn bản để trích xuất các giá trị dữ liệu. Ngay cả trong trường hợp các trường được phân tách, bạn vẫn có thể sử dụng các hàm văn bản để trích xuất dữ liệu.

Một số hàm văn bản hữu ích cho mục đích này là:

Không.	Mô tả chức năng
1.	LEFT Trả về các ký tự ngoài cùng bên trái từ một giá trị văn bản
2.	RIGHT Trả về các ký tự ngoài cùng bên phải từ một giá trị văn bản
3.	MID Trả về một số ký tự cụ thể từ một chuỗi văn bản bắt đầu từ vị trí bạn chỉ định
4.	LEN Trả về số ký tự trong một chuỗi văn bản

Bạn cũng có thể kết hợp hai hoặc nhiều hàm văn bản này theo dữ liệu bạn có trong tay, để trích xuất các giá trị dữ liệu cần thiết. Ví dụ: sử dụng kết hợp các hàm LEFT, RIGHT và VALUE hoặc sử dụng kết hợp các hàm FIND, LEFT, LEN và MID.

Trong ví dụ trên,

Tất cả các ký tự còn lại đầu tiên | đặt tên ProductID.
Tất cả các ký tự phải đến thứ hai | cho biết tên Giá.
Tất cả các ký tự nằm giữa ký tự đầu tiên | và thứ hai | đặt tên Product_Description.
Mỗi | có khoảng trắng trước và sau.

Quan sát thông tin này, bạn có thể trích xuất các giá trị dữ liệu bằng các bước sau:

Tìm vị trí của đầu tiên | -First | Position
- Bạn có thể sử dụng hàm TÌM
Tìm vị trí thứ hai | -Second | Position
- Bạn có thể sử dụng lại hàm FIND
Bắt đầu (First | Position - 2) Các ký tự của Văn bản cung cấp ProductID
- Bạn có thể sử dụng hàm LEFT
(First | Position + 2) đến (Second | Position - 2) Các ký tự của Văn bản cho Product_Description
- Bạn có thể sử dụng Hàm MID
(Second | Position + 2) Các ký tự cuối của văn bản đưa ra giá
- Bạn có thể sử dụng hàm RIGHT

Kết quả sẽ như hình dưới đây -

Bạn có thể thấy rằng các giá trị trong cột giá là giá trị văn bản. Để thực hiện các phép tính trên các giá trị này, bạn phải định dạng các ô tương ứng. Bạn có thể xem phần dưới đây để hiểu định dạng văn bản.

Trích xuất giá trị dữ liệu với Flash Fill

Sử dụng Excel Flash Filllà một cách khác để trích xuất các giá trị dữ liệu từ văn bản. Tuy nhiên, điều này chỉ hoạt động khi Excel có thể tìm thấy một mẫu trong dữ liệu.

Step 1 - Tạo ba cột cho ProductID, Product_Description và Giá bên cạnh dữ liệu.

Step 2 - Sao chép và dán các giá trị cho C3, D3 và E3 từ B3.

Step 3 - Chọn ô C3 và nhấp vào Flash Fill bên trong Data Tools nhóm trên Datachuyển hướng. Tất cả các giá trị cho ProductID được lấp đầy.

Step 4- Lặp lại các bước đã cho ở trên cho Product_Description và Price. Dữ liệu được điền.

Định dạng dữ liệu với các hàm văn bản

Excel có một số hàm văn bản cài sẵn mà bạn có thể sử dụng để định dạng dữ liệu có chứa văn bản. Chúng bao gồm -

Functions that format the Text as per your need -

Không.	Mô tả chức năng
1.	LOWER Chuyển đổi văn bản thành chữ thường

Không.	Mô tả chức năng
1.	UPPER Chuyển văn bản thành chữ hoa
2.	PROPER Viết hoa chữ cái đầu tiên trong mỗi từ của giá trị văn bản

Functions that convert and/or format the Numbers as Text -

Không.	Mô tả chức năng
1.	DOLLAR Chuyển đổi một số thành văn bản, sử dụng định dạng tiền tệ $ (đô la)
2.	FIXED Định dạng một số dưới dạng văn bản với một số thập phân cố định
3.	TEXT Định dạng một số và chuyển nó thành văn bản

Functions that convert the Text to Numbers -

Không.	Mô tả chức năng
1.	VALUE Chuyển đối số văn bản thành một số

Executing Data Operations with the Text Functions

Bạn có thể phải thực hiện các Thao tác văn bản nhất định trên Dữ liệu của mình. Ví dụ: nếu ID đăng nhập cho Nhân viên được thay đổi thành Định dạng mới trong Tổ chức, dựa trên Thay đổi định dạng, thì có thể phải thực hiện Thay thế văn bản.

Các Hàm Văn bản sau đây giúp bạn thực hiện các Thao tác với Văn bản trên dữ liệu của bạn có chứa Văn bản -

Không.	Mô tả chức năng
1.	REPLACE Thay thế các ký tự trong văn bản
2.	SUBSTITUTE Thay thế văn bản mới cho văn bản cũ trong một chuỗi văn bản
3.	CONCATENATE Nối một số mục văn bản thành một mục văn bản
4.	CONCAT Kết hợp văn bản từ nhiều phạm vi và / hoặc chuỗi, nhưng nó không cung cấp đối số dấu phân tách hoặc bỏ quaEmpty.
5.	TEXTJOIN Kết hợp văn bản từ nhiều phạm vi và / hoặc chuỗi và bao gồm dấu phân cách bạn chỉ định giữa mỗi giá trị văn bản sẽ được kết hợp. Nếu dấu phân cách là một chuỗi văn bản trống, hàm này sẽ nối các phạm vi một cách hiệu quả.
6.	REPT Lặp lại văn bản một số lần nhất định