8 Công Thức Giúp Xử Lý Dữ Liệu Hiệu Quả

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é!