8 Công Thức Giúp Xử Lý Dữ Liệu Hiệu Quả
Hàng này công việc kinh doanh của bạn tạo ra hàng triệu dữ liệu và hầu hết chúng không được sắp xếp theo cấu trúc, thậm chí công nghệ tốt nhất trên thế giới hiện nay cũng không thể cho ra những dữ liệu sẵn có để bạn có thể dựa vào chúng mà ra quyết định ngay được. Đấy là lý do vì sao mà chúng ta phải phụ thuộc nhiều vào MS Excel từ những điều đơn giản đến phức tạp nhất. Bài viết này sẽ giúp bạn hiểu thêm về các hàm văn bản trong Excel và làm cách nào để áp dụng chúng một cách hiệu quả khi phân tích dữ liệu theo hệ thống.
Video hướng dẫn
Tải file thực hành tại đây.
Nối chuỗi
Có 2 cách để nối các chuỗi với nhau trong các ô:
Cách 1: Ta sử dụng dấu "&" để nối
Ví dụ sử dụng dấu "&" trong hình dưới:
=B11&" "&C11
Giải thích: B11, C11 là 2 ô chứa chuỗi cần nối và được ngăn cách với nhau bởi dấu khoảng trắng " "
Cách 2: Ta dùng hàm CONCATENATE để nối
Ví dụ sử dụng hàm trong hình dưới:
=CONCATENATE(B11," ",C11)
Thiết lập kiểu chữ
- Dùng hàm TRIM: Xóa khoảng trắng bị thừa
Ta dùng hàm vào hình dưới đây:
=TRIM(B17)
Giải thích: B17: là ô chứa dữ liệu gốc cần xóa khoảng trắng.
- Dùng hàm Proper: Viết in hoa ký tự đầu mỗi từ và viết thường các kí tự còn lại.
Ta dùng hàm vào hình dưới đây:
=PROPER(TRIM(B17))
Giải thích: Kết hợp với hàm TRIM để xóa khoảng trắng nếu dữ liệu gốc đang thừa khoảng trắng, B17 là ô chứa dữ liệu ban đầu.
- Dùng hàm Upper: Viết in hoa tất cả các kí tự.
Ta dùng hàm vào hình dưới đây:
=UPPER(TRIM(B17))
Giải thích: Kết hợp với hàm TRIM để xóa khoảng trắng nếu dữ liệu gốc đang thừa khoảng trắng, B17 là ô chứa dữ liệu ban đầu.
- Dùng hàm Lower: Viết chữ thường tất cả các kí tự.
Ta dùng hàm vào hình dưới đây:
=LOWER(TRIM(B17))
Giải thích: Kết hợp với hàm TRIM để xóa khoảng trắng nếu dữ liệu gốc đang thừa khoảng trắng, B17 là ô chứa dữ liệu ban đầu.
Tách dữ liệu
Tách dữ liệu bên trái
- Sử dụng hàm Left: Để lấy ký tự bên trái.
Ta sử dụng hàm vào hình dưới đây:
=LEFT(B23,2)
Giải thích: B23: ô chứa dữ liệu cần lấy
2: Số kí tự muốn lấy là 2
Tách dữ liệu nằm giữa
- Sử dụng hàm MID: Để lấy ký tự ở giữa.
Ta sử dụng hàm vào hình dưới đây:
=MID(B23,4,3)
Giải thích: B23: ô chứa dữ liệu cần lấy
4: Ký tự muốn lấy bắt đầu từ vị trí thứ 4
2: Số kí tự muốn lấy là 3
Tách dữ liệu bên phải
- Sử dụng hàm RIGHT: để lấy ký tự bên phải.
Ta sử dụng hàm vào hình dưới đây:
=RIGHT(B23,4)
Giải thích: B23: ô chứa dữ liệu cần lấy
4: Số ký tự muốn lấy là 3
Vì mình đang tính số lượng nên ta bỏ bớt số 0 ở trước đi bằng cách nhân thêm *1 để chuyển từ dạng text sang dạng số:
=RIGHT(B23,4)*1
Tách 1 phần dữ liệu
Ví dụ như hình dưới đây:
Để lấy số lượng trong bảng ta dùng công thức:
=MID(B32,SEARCH("-",B32,1)+1,2)
Giải thích: B32: ô chứa dữ liệu cần lấy số lượng
Dùng hàm Search để tìm kiếm vị trí ký tự bắt đầu lấy trong ô, trong đó "-":tìm ký tự "-" trong ô B32 và cộng thêm 1 để xác định vị trí ký tự bắt đầu lấy trong ô B32.
2: Số kí tự muốn lấy.
Tương tự như tính số lượng ở câu trên, ta nhân thêm 1 để chuyển từ kiểu Text sang kiểu ký tự số:
=MID(B32,SEARCH("-",B32,1)+1,2)*1
Đếm số từ
Ví dụ như hình dưới đây:
Để đếm số từ trong dữ liệu ở hình trên ta dùng công thức như sau:
=(LEN(B38)-LEN(SUBSTITUTE(B38," ","")))+1
Giải thích: Len(B38): Đếm số ký tự trong ô dữ liệu.
Để đếm số ký tự không chứa dấu cách ta dùng hàm LEN(SUBSTITUTE(B38," ",""). Trong đó: Hàm SUBSTITUTE để thay thế dữ liệu chứa dấu khoảng trắng sang dữ liệu không chứa khoảng trắng.
Ngắt dòng
Để ngắt dòng các chuỗi ký tự, ta dùng hàm CHAR(10)
Ta sử dụng hàm vào hình dưới đây:
=B44&CHAR(10)&" "&C44
Giải thích: B44, C44 là 2 ô chứa ký tự chuỗi muốn ngắt.
Lưu ý: Phải chọn Wrap Text để dữ liệu ngắt xuống dòng dưới.
Đệm số 0 phía sau
Ví dụ như hình dưới đây:
Để thêm số 0 ở đằng sau dữ liệu gốc. Ví dụ muốn dữ liệu trong cột đó mỗi ô có 6 chữ số. Ta gõ công thức như sau:
=LEFT(B50&"000000",6)
Giải thích: B50: ô chứa dữ liệu gốc ban đầu.
Để thêm số 0 phía sau ta dùng dấu "&" để nối ô chứa dữ liệu ban đầu với dãy số 0.
Dùng hàm LEFT để tách lấy 6 số ở phía bên trái dữ liệu.
Đệm số 0 phía trước
Ví dụ như hình dưới đây:
Để thêm số 0 ở đằng trước dữ liệu gốc. Ví dụ muốn dữ liệu trong cột đó mỗi ô có 6 chữ số. Ta gõ công thức như sau:
=RIGHT("000000"&B56,6)
Giải thích: B56: ô chứa dữ liệu gốc ban đầu.
Để thêm số 0 phía trước ta dùng dấu "&" để nối ô chứa dữ liệu ban đầu với dãy số 0.
Dùng hàm RIGHT để tách lấy 6 số ở phía bên phải dữ liệu.
Vậy là mình đã hướng dẫn xong cho các bạn 8 công thức giúp xử lý dữ liệu hiệu quả. Chúc các bạn thực hiện thành công. Nếu thấy hay đừng quên đánh giá bài viết bên dưới nhé!
CHO ĐIỂM BÀI VIẾT NÀY