Hướng dẫn, thủ thuật về Thủ thuật văn phòng

Ký tự đại diện trong Excel là giải pháp giúp bạn tìm kiếm thứ gì đó không chắc chắn trong file dữ liệu và nếu bạn chưa biết cách thực hiện. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về ký tự đại diện cũng như cách thực hiện nhé.

I. Các ký tự đại diện trong Excel

Ký tự đại diện là loại ký tự đặc biệt có thể thay thế cho bất cứ ký tự nào trong Excel. Nghĩa là, khi bạn không biết một ký tự chính xác thì bạn có thể sử dụng ký tự đại diện ở vị trí đó.

Trong Excel có hai dạng ký tự phổ biến:

• Dấu hoa thị (*)
• Dấu chấm hỏi (?) hoặc dấu ngã (~)

1. Dấu hoa thị dưới dạng ký tự đại diện

Dấu hoa thị (*) là ký tự đại diện chung nhất có thể đại diện cho bất kỳ số ký tự nào .

Ký tự

Mô tả nội dung

ch*

Khớp với bất kỳ từ nào bắt đầu bằng “ch” chẳng hạn như Charles, séc, cờ vua,…

*ch

Thay thế bất kỳ chuỗi văn bản nào kết thúc bằng “ch”, chẳng hạn như March, inch, fetch,…

*ch*

Đại diện cho bất kỳ từ nào có chứa “ch” ở bất kỳ vị trí nào như Chad, nhức đầu, vòm,…

2. Dấu chấm hỏi dưới dạng ký tự đại diện

Dấu chấm hỏi (?) là ký tự đại diện cho bất kỳ ký tự đơn nào . Ký tự này có thể giúp cụ thể hơn khi tìm kiếm kết quả phù hợp từng phần.

Ký tự

Mô tả nội dung

?

Khớp với bất kỳ mục nhập nào có chứa một ký tự, ví dụ: “a”, “1”, “-“,…

??

Thay thế hai ký tự bất kỳ, ví dụ:”as”, “12”, “a@”,…

??? – ???

Đại diện cho bất kỳ chuỗi nào có chứa 2 nhóm 3 ký tự được phân tách bằng dấu gạch ngang như ABC-DEF, ABC-123, 111-222,…

pri? e

Phù hợp với giá cả, niềm tự hào, giải thưởng và những thứ tương tự.

3. Dấu ngã làm ký tự đại diện nullifier

Dấu ngã (~) được đặt trước một ký tự đại diện sẽ hủy tác dụng của ký tự đại diện và biến ký tự đó thành dấu sao theo nghĩa đen (~ *), dấu hỏi nghĩa đen (~?) hoặc dấu ngã theo nghĩa đen (~~).

Ký tự

Mô tả nội dung

* ~?

Tìm bất kỳ dữ liệu nhập nào kết thúc bằng dấu chấm hỏi, ví dụ: Cái gì?, Có ai ở đó không?,…

* ~ **

Tìm thấy bất kỳ dữ liệu chứa một dấu hoa thị, ví dụ: * 1, * 11 *, 1-Mar-2020 *,…

II. Tìm và thay thế các ký tự đại diện trong Excel

1. Cách tìm kiếm bằng ký tự đại diện

Trong bảng tính Excel bạn nhấn tổ hợp phím Ctrl + F để xuất hiện bảng Find and Replace > Sau đó, nhập ký tự tìm kiếm ??-?? > Nhấn chọn Find all bạn sẽ thấy kết quả tìm kiếm bằng ký tự đại diện như hình minh họa.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

2. Cách thay thế bằng ký tự đại diện

Ví dụ ban đầu mình có bảng dữ liệu như hình.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 1: Nhấn tổ hợp phím Ctrl + H và ở mục Find what > Nhập biểu thức ký tự đại diện cần tìm để thay thế, ở mục Replace with > Nhập nội dung thay thế > Nhấn chọn Replace All để thay đổi nội dung cũ.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 2: Kết quả sau khi nhấn chọn Replace All.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

3. Cách tìm và thay thế các ký tự đại diện

Để tìm một ký tự mà Excel nhận dạng là ký tự đại diện. Nghĩa là dấu hoa thị hoặc dấu chấm hỏi theo nghĩa đen bạn hãy thêm dấu ngã (~) vào ký tự nhập tìm kiếm của bạn. Ở đây mình sẽ nhập ký tự ~ * vào ô tìm kiếm để bạn dễ hình dung.

Bạn bấm tổ hợp phím Ctrl + F để hiển thị bảng Find and Replace > Nhập ký tự ~ * vào mục Find What > Nhấn chọn Find All kết quả sẽ hiển thị như hình minh họa.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

III. Lọc dữ liệu bằng các ký tự đại diện trong Excel

Các ký tự đại diện trong Excel cũng rất hữu ích khi bạn có một cột dữ liệu khổng lồ và muốn lọc dữ liệu đó dựa trên điều kiện. Cách tốt nhất và nhanh nhất với trường hợp này là bạn sử dụng bộ lọc, bằng cách nhấn tổ hợp phím Ctrl + Shift + L.

Để hiểu rõ hơn về cách tạo cũng như cách khắc phục bạn hãy tham khảo bài viết: Cách khắc phục lỗi bộ lọc trong Excel

IV. Công thức Excel với ký tự đại diện

STT

Tên hàm

Mô tả nội dung

1

AVERAGEIF

Hàm giúp tìm giá trị trung bình (trung bình cộng) của các ô đáp ứng điều kiện được chỉ định.

2

AVERAGEIFS

Hàm trả về giá trị trung bình của các ô đáp ứng nhiều điều kiện trong hàm.

3

COUNTIF

Hàm giúp đếm số lượng ô dựa trên một điều kiện.

4

COUNTIFS

Hàm giúp đếm số ô dựa trên nhiều điều kiện.

5

SUMIF

Hàm giúp tính tổng các ô có điều kiện.

6

SUMIFS

Hàm giúp tính tổng các ô có nhiều điều kiện. Giống như SUMIF trong ví dụ trên chấp nhận các ký tự đại diện.

7

VLOOKUP

Hàm giúp thực hiện tra cứu theo chiều dọc với khớp một phần.

8

HLOOKUP

Hàm giúp thực hiện tra cứu theo chiều ngang với khớp một phần.

9

XLOOKUP

Hàm giúp thực hiện tra cứu đối sánh từng phần cả trong một cột và một hàng.

10

MATCH

Hàm giúp tìm một phần đối sánh và trả về vị trí tương đối.

11

XMATCH

Là một kế thừa hiện đại của chức năng MATCH cũng hỗ trợ khớp ký tự đại diện.

1. Công thức ký tự đại diện COUNTIF trong Excel

Để hiểu rõ hơn về hàm COUNTIF trong Excel bạn tham khảo: Cách sử dụng hàm COUNTIF trong Excel

Ví dụ mình có bảng dữ liệu minh họa như hình và cần tìm kiếm ô chứa ký tự B.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 1: Bạn nhập hàm =COUNTIF(A2:A7,”*”&C9&”*”) vào ô tham chiếu kết quả trong bảng dữ liệu.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

2. Công thức VLOOKUP ký tự đại diện trong Excel

Để hiểu rõ hơn về hàm VLOOKUP trong Excel bạn tham khảo: Cách sử dụng hàm VLOOKUP trong Excel

Ví dụ mình có bảng dữ liệu minh họa như hình và cần tìm kiếm ô chứa ký tự A-.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 1: Bạn nhập hàm =VLOOKUP(D9&”*”, $A$3:$B$7, 2, FALSE) vào ô tham chiếu kết quả trong bảng dữ liệu.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

V. Ký tự đại diện Excel cho các số

Với tính năng Find and Replace (Tìm và thay thế) cũng như sử dụng Bộ lọc thì các ký tự đại diện đều hoạt động tốt cho cả dữ liệu văn bản và số.

1. Tìm và thay thế bằng số ký tự đại diện

Trong bảng tính Excel bạn nhấn tổ hợp phím Ctrl + F để xuất hiện bảng Find and Replace > Sau đó, nhập ký tự tìm kiếm *4* > Nhấn chọn Find all bạn sẽ thấy kết quả tìm kiếm bằng ký tự đại diện như hình minh họa.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

2. Lọc với số ký tự đại diện

Tương tự như trên. Bộ lọc tự động của Excel không có vấn đề gì với việc lọc các số có chứa “4”.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

3. Tại sao ký tự đại diện Excel không hoạt động với các số trong công thức

Việc sử dụng các ký tự đại diện cùng với các số (bất kể bạn bao quanh số bằng các ký tự đại diện hay nối một tham chiếu ô) sẽ chuyển đổi một giá trị số thành một chuỗi văn bản. Kết quả là Excel không nhận ra một chuỗi trong một dải số.

Ví dụ: Công thức dưới đây có thể đếm số chuỗi có chứa “4” nhưng không thể xác định chữ số 4 trong một số.

Bước 1: Bạn nhập hàm =COUNTIF(A2:A7,”*”&C8&”*” ) vào ô tham chiếu kết quả trong bảng dữ liệu.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

4. Cách làm cho các ký tự đại diện hoạt động cho các số

Cách tốt nhất để làm cho các ký tự đại diện hoạt động cho các số là chuyển đổi chúng thành dạng văn bản. Sau đó, thực hiện tương tự như các hàm đã nêu ở trên.

Tuy nhiên, trong trường hợp phương pháp này không được chấp nhận trên thực tế thì bắt buộc bạn phải đưa ra công thức riêng cho từng trường hợp cụ thể. Ở đây mình sẽ có 2 ví dụ minh họa bên dưới để bạn dễ hình dung.

Ví dụ 1. Công thức ký tự đại diện trong Excel cho các số

Trong bảng mẫu bên dưới, giả sử bạn muốn tính xem có bao nhiêu số trong phạm vi A2: A7 chứa “3”

Bước 1: Bạn nhập hàm =SUMPRODUCT(-(ISNUMBER(SEARCH(“3”,A3:A7)))) vào ô tham chiếu kết quả trong bảng dữ liệu.

Giải thích hàm:

• SUMPRODUCT, ISNUMBER, SEARCH: Là các lệnh hàm.
• A3:A7: Là vùng dữ liệu tìm kiếm.
• “3”: Là giá trị cần tìm.
• –: Là dấu toán tử nhằm chuyển đổi TRUE, FALSE thành số 1 và 0 tương ứng.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Ví dụ 2. Công thức ký tự đại diện cho ngày tháng

Công thức SUMPRODUCT được thảo luận ở trên hoạt động tốt cho các con số nhưng sẽ không thành công cho các ngày. Bởi vì bên trong Excel lưu trữ ngày tháng dưới dạng số sê-ri và công thức sẽ xử lý những số đó chứ không phải ngày tháng được hiển thị trong ô.

Để vượt qua trở ngại này bạn hãy sử dụng hàm TEXT để chuyển đổi ngày tháng thành chuỗi văn bản. Sau đó nạp các chuỗi vào hàm SEARCH. Mình sẽ làm cụ thể để bạn dễ hình dung.

Bước 1: Bạn nhập hàm =SUMPRODUCT(-(ISNUMBER(SEARCH(“4”,TEXT(C3:C7, “mmddyyyy”))))) vào ô tham chiếu kết quả trong bảng dữ liệu.

Giải thích hàm:

• SUMPRODUCT, ISNUMBER, SEARCH, TEXT: Là các lệnh hàm.
• C3:C7: Là vùng dữ liệu tìm kiếm.
• mmddyyyy: Là đếm ngày, tháng năm có trong vùng dữ liệu.
• “4”: Là giá trị cần tìm.
• –: Là dấu toán tử nhằm chuyển đổi TRUE, FALSE thành số 1 và 0 tương ứng.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Củ sạc được thiết kế 4 cổng sạc tiện dụng

Trên đây là bài viết về Ký tự đại diện trong Excel: Công thức sử dụng tìm, thay thế và lọc . Hy vọng bài viết sẽ giúp bạn thực hiện được trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.