Các hàm Excel 365

Ngày tạo 19/04/2023

- 11.061 Lượt xem

1. HÀM TEXTSPLIT TRONG EXCEL

Hàm TEXTSPLIT trong Excel phân tách các chuỗi văn bản bằng 1 dấu phân cách nhất định trên các cột hoặc là hàng bạn muốn.

Kết quả là 1 mảng tự động tràn vào nhiều ô.

Hàm này có tới 6 đối số, chỉ có 2 đối số đầu tiên là bắt buộc.

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Text (bắt buộc) - văn bản cần tách. Có thể được cung cấp dưới dạng tham chiếu chuỗi hoặc ô.

Col_delimiter (bắt buộc) - (các) ký tự cho biết vị trí phân chia văn bản trên các cột. Nếu bỏ qua, row_delimiter phải được xác định.

Row_delimiter (tùy chọn) - (các) ký tự cho biết nơi phân chia văn bản trên các hàng.

Ignore_empty (tùy chọn) - chỉ định có bỏ qua các giá trị trống hay không:

- FALSE (mặc định) - tạo các ô trống cho các dấu phân cách liên tiếp không có giá trị ở giữa.

- TRUE - bỏ qua các giá trị trống, tức là không tạo các ô trống cho hai hoặc nhiều dấu phân cách liên tiếp.

Match_mode (tùy chọn) - xác định phân biệt chữ hoa chữ thường cho dấu phân cách. Được bật theo mặc định.

- 0 (mặc định) - phân biệt chữ hoa chữ thường

- 1 - phân biệt chữ hoa chữ thường

Pad_with (tùy chọn) - một giá trị để sử dụng thay cho các giá trị bị thiếu trong mảng hai chiều. Mặc định là lỗi #N/A.

Ví dụ: để chia một chuỗi văn bản trong A2 thành nhiều ô bằng cách sử dụng dấu phẩy và dấu cách làm dấu phân cách, công thức là:

=TEXTSPLIT(A2, "@")

2. TÍNH KHẢ DỤNG CỦA HÀM TEXTSPLIT TRONG EXCEL

Hàm TEXTSPLIT hiện chỉ có sẵn trong phiên bản Excel 365 (Windows và Mac) và phiên bản Excel dành cho web (office.com)

Trong trường hợp bạn muốn sử dụng hàm này thì có thể tham khảo video hướng dẫn đăng ký Free tài khoản tại office.com của ERX Việt Nam.

3. CÔNG THỨC TEXTSPLIT CƠ BẢN ĐỂ TÁCH Ô TRONG EXCEL

Để bắt đầu, hãy xem cách sử dụng công thức TEXTSPLIT ở dạng đơn giản nhất để phân tách một chuỗi văn bản bằng một dấu phân cách cụ thể.

a. Tách một ô theo chiều ngang trên các cột

Để chia nội dung của một ô đã cho thành nhiều cột, hãy cung cấp một tham chiếu đến ô chứa chuỗi gốc cho đối số (văn bản) đầu tiên và dấu phân cách đánh dấu điểm sẽ xảy ra quá trình phân tách cho đối số (col_delimiter) thứ hai.

Ví dụ: để phân tách chuỗi trong A2 theo chiều ngang bằng dấu phẩy, công thức là:

b. Tách một ô theo chiều dọc giữa các hàng

Để chia văn bản thành nhiều hàng, đối số thứ ba (row_delimiter) là nơi bạn đặt dấu phân cách. Đối số thứ hai (col_delimiter) được bỏ qua trong trường hợp này.

Chẳng hạn, để tách các giá trị trong A2 thành các hàng khác nhau, công thức là:

Xin lưu ý rằng, trong cả hai trường hợp, công thức chỉ được nhập vào một ô (C2). Trong các ô lân cận, các giá trị được trả về sẽ tự động tràn ra. Mảng kết quả (được gọi là phạm vi tràn) được đánh dấu bằng đường viền màu xanh cho biết mọi thứ bên trong nó được tính theo công thức ở ô phía trên bên trái.

4. TÁCH CHUỖI THÀNH CỘT VÀ HÀNG CÙNG MỘT LÚC

Để tách một chuỗi văn bản thành các hàng và cột cùng một lúc, hãy xác định cả hai dấu phân cách trong công thức TEXTSPLIT của bạn.

Ví dụ: để chia chuỗi văn bản trong A2 thành các cột và hàng, chúng tôi cung cấp:

- Dấu hai chấm và cách (": ") cho col_delimiter

- Dấu phẩy và dấu cách (", ") cho row_delimiter

Công thức hoàn chỉnh có dạng này:

Kết quả là một mảng 2-D bao gồm 2 cột và 3 hàng.

5. TÁCH CÁC Ô BẰNG NHIỀU DẤU PHÂN CÁCH

Để xử lý nhiều dấu phân cách hoặc không nhất quán trong chuỗi nguồn, hãy sử dụng hằng số mảng như {"x","y","z"} cho đối số dấu phân cách.

Trong ảnh chụp màn hình bên dưới, văn bản trong khổ A2 được phân tách bằng cả dấu phẩy (",") và dấu chấm phẩy (“;") có và không có dấu cách. Để chia chuỗi theo chiều dọc thành các hàng theo cả 4 biến thể của dấu phân cách, công thức là:

=TEXTSPLIT(A2, , {",",", ",";","; "})

Hoặc, bạn có thể chỉ bao gồm dấu phẩy (",") và dấu chấm phẩy (“;") trong mảng, sau đó loại bỏ khoảng trắng thừa với sự trợ giúp của hàm TRIM:

=TRIM(TEXTSPLIT(A2, , {",",";"}))

6. TÁCH VĂN BẢN BỎ QUA CÁC GIÁ TRỊ TRỐNG

Nếu chuỗi chứa hai hoặc nhiều dấu phân cách liên tiếp không có giá trị giữa chúng, bạn có thể chọn có bỏ qua các giá trị trống đó hay không. Hành vi này được kiểm soát bởi tham số thứ tư là ignore_empty, mặc định là FALSE.

Theo mặc định, hàm TEXTSPLIT không bỏ qua các giá trị trống.

Trong bảng mẫu này, có 1 số dữ liệu bị thiếu trong một số chuỗi. Công thức TEXTSPLIT với đối số ignore_empty được bỏ qua hoặc được đặt thành FALSE xử lý trường hợp này một cách hoàn hảo, tạo một ô trống cho mỗi giá trị trống.

Trong trường hợp chuỗi của bạn chứa dữ liệu đồng nhất, có thể có lý do để bỏ qua các giá trị trống. Đối với điều này, hãy đặt đối số ignore_empty thành TRUE hoặc 1.

Chẳng hạn, để chia các chuỗi bên dưới, đặt mỗi kỹ năng vào một ô riêng biệt không có khoảng trống, công thức sẽ là:

=TEXTSPLIT(A2, ", ", ,TRUE)

Trong trường hợp này, các giá trị bị thiếu giữa các dấu phân cách liên tiếp bị bỏ qua hoàn toàn:

7. TÁCH Ô CÓ PHÂN BIỆT CHỮ HOA CHỮ THƯỜNG HOẶC CHỮ HOA CHỮ THƯỜNG HAY KHÔNG?

Để kiểm soát phân biệt chữ hoa chữ thường của dấu phân cách, hãy sử dụng đối số thứ năm, match_mode.

Theo mặc định, match_mode được đặt thành 0, làm cho TEXTSPLIT phân biệt chữ hoa chữ thường.

Trong ví dụ này, các số được phân tách bằng chữ "x" viết thường và chữ "X" viết hoa.

Công thức có phân biệt chữ hoa chữ thường mặc định chỉ chấp nhận chữ thường "x" làm dấu phân cách:

=TEXTSPLIT(A2, " x ")

Xin lưu ý rằng dấu phân cách có khoảng trắng ở cả hai bên của chữ " x " để ngăn cách khoảng trắng ở đầu và cuối trong kết quả.

Để tắt phân biệt chữ hoa chữ thường, bạn cung cấp 1 cho match_mode để buộc công thức TEXTSPLIT bỏ qua chữ hoa chữ thường:

=TEXTSPLIT(A2, " x ", , ,1)

Bây giờ, tất cả các chuỗi được phân tách chính xác bằng một trong hai dấu phân cách:

8. THAY THẾ CÁC GIÁ TRỊ THIẾU BẰNG GIÁ TRỊ BẠN MUỐN

Đối số cuối cùng của hàm TEXTSPLIT, pad_with, rất hữu ích trong trường hợp một hoặc nhiều giá trị bị thiếu trong chuỗi nguồn. Theo mặc định, khi một chuỗi như vậy được chia thành cả cột và hàng, Excel sẽ trả về lỗi #N/A thay vì các giá trị bị thiếu để không xáo trộn cấu trúc của mảng hai chiều.

Trong chuỗi bên dưới, không có ":" (col_delimiter) sau "Lương". Để giữ tính toàn vẹn của mảng kết quả, TEXTSPLIT xuất ra #N/A bên cạnh "Lương".

Để làm cho kết quả thân thiện hơn với người dùng, bạn có thể thay thế lỗi #N/A bằng bất kỳ giá trị nào bạn muốn. Chỉ cần nhập giá trị mong muốn vào đối số pad_with.

Trong trường hợp của chúng tôi, đó có thể là dấu gạch nối ("-"):

Bây giờ bạn đã học cách sử dụng thực tế từng đối số của hàm TEXTSPLIT, hãy thảo luận về một số ví dụ nâng cao có thể giúp bạn đối phó với những thách thức không hề nhỏ trong bảng tính Excel của mình.

9. CHIA NGÀY THÀNH NGÀY, THÁNG VÀ NĂM

Để chia ngày thành các đơn vị riêng lẻ, trước tiên, bạn cần chuyển đổi ngày thành văn bản vì hàm TEXTSPLIT xử lý chuỗi văn bản trong khi ngày trong Excel là số.

Cách dễ nhất để chuyển đổi một giá trị số thành văn bản là sử dụng hàm TEXT. Chỉ cần đảm bảo cung cấp mã định dạng thích hợp cho ngày của bạn.

Trong trường hợp của chúng tôi, công thức là:

=TEXT(A2, "dd/mm/yyyy")

Bước tiếp theo là lồng hàm trên vào đối số thứ nhất của TEXTSPLIT và nhập dấu phân cách tương ứng cho đối số thứ 2 hoặc thứ 3, tùy thuộc vào việc bạn đang phân tách giữa các cột hay hàng. Trong ví dụ này, các đơn vị ngày được phân tách bằng dấu gạch chéo, vì vậy chúng tôi sử dụng "/" cho đối số col_delimiter:

=TEXTSPLIT(TEXT(A2, "dd/mm/yyyy"), "/")

10. TÁCH Ô VÀ LOẠI BỎ MỘT SỐ KÝ TỰ

Hãy tưởng tượng điều này: bạn đã chia một chuỗi dài thành nhiều phần, nhưng mảng kết quả vẫn chứa một số ký tự không mong muốn, chẳng hạn như dấu ngoặc đơn trong ảnh chụp màn hình bên dưới:

Để loại bỏ dấu ngoặc đơn mở và đóng cùng một lúc, hãy lồng hai hàm SUBSTITUTE vào nhau (mỗi hàm thay thế một dấu ngoặc đơn bằng một chuỗi trống) và sử dụng công thức TEXTSPLIT cho đối số văn bản của SUBSTITUTE bên trong:

11. TÁCH CHUỖI BỎ QUA CÁC GIÁ TRỊ NHẤT ĐỊNH

Giả sử bạn muốn tách các chuỗi bên dưới thành 4 cột: Tên, Họ, Điểm và Kết quả. Vấn đề là một số chuỗi chứa tiêu đề "Mr." hoặc "Ms.", do đó tất cả các kết quả đều không chính xác.