Ký tự đại diện trong Excel: Công thức sử dụng tìm, thay thế và lọc

Nhấn Enter để hiển thị kết quả.

Ký tự đại diện trong Excel là giải pháp giúp bạn tìm thấy điều gì đó không chắc chắn trong tệp dữ liệu và nếu bạn không 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ề các 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

Các ký tự đại diện là các ký tự đặc biệt có thể được thay thế cho bất kỳ ký tự nào trong Excel. Có 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 loại ký tự phổ biến:

  • Dấu hoa thị

Dấu 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.

Nhân vật

Mô tả nội dung

ch *

So khớp 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à một 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 các kết quả phù hợp từng phần.

Nhân vật

Mô tả nội dung

?

Khớp với bất kỳ mục nhập nào có chứa 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

Giá cả phù hợp, 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 ký tự đại diện sẽ hủy ký tự đại diện và biến nó thành dấu hoa thị theo nghĩa đen (~ *), dấu hỏi nghĩa đen (~?) Hoặc dấu ngã. theo nghĩa đen (~~).

Nhân vật

Mô tả nội dung

* ~?

Tìm bất kỳ đầu vào 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 bất kỳ dữ liệu nào có chứa 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, nhấn Ctrl + F để xuất hiện bảng Tìm và Thay thế> Sau đó nhập ký tự tìm kiếm ?? – ?? > Nhấn Tìm tất cả và bạn sẽ thấy kết quả tìm kiếm với các ký tự đại diện như hình.

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

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

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

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

Bảng ví dụ minh họa.

Bảng ví dụ minh họa.

Bước 1: Nhấn Ctrl + H và trong mục Tìm gì> Nhập biểu thức ký tự đại diện cần tìm để thay thế, trong mục Thay thế bằng> Nhập nội dung thay thế> Nhấn Thay thế Tất cả để thay đổi nội dung cũ.

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

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

Bước 2: Kết quả sau khi bấm Replace All.

Kết quả.

Kết quả.

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.  Đó là, một dấu hoa thị hoặc dấu chấm hỏi theo nghĩa đen, thêm dấu ngã (~) vào ký tự đầu vào tìm kiếm của bạn.  Ở đây mình sẽ nhập ký tự ~ * vào ô tìm kiếm để dễ hình dung.

Bạn nhấn Ctrl + F để hiện ra bảng Find and Replace> Nhập ký tự ~ * vào Find What> Nhấn Find All, kết quả sẽ hiển thị như hình.

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

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

III. Lọc dữ liệu bằng 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 một điều kiện. Cách tốt nhất và nhanh nhất trong trường hợp này là sử dụng bộ lọc, bằng cách nhấn Ctrl + Shift + L.

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

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

STT

Tên chức năng

Mô tả nội dung

Đầu tiên

AVERAGEIF

Hàm giúp tìm giá trị trung bình (trung bình) của các ô đáp ứng điều kiện xác đị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ố lượng ô 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

Chức năng này giúp thực hiện tra cứu theo chiều dọc với các kết quả phù hợp từng phần.

số 8

HLOOKUP

Chức năng giúp thực hiện tra cứu theo chiều ngang với các kết quả khớp từng 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.

mười

CHỈ TRẬN ĐẤU

Hàm tìm một kết quả phù hợp một phần và trả về vị trí tương đối.

11

CHỈ XMATCH

Là một kế thừa hiện đại của hàm MATCH cũng hỗ trợ đối sánh 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, mời các bạn tham khảo: Cách sử dụng hàm COUNTIF trong Excel

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

Hình minh họa.

Hình minh họa.

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ông thức ký tự đại diện COUNTIF trong Excel

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

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

Kết quả.

Kết quả.

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

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

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

Hình minh họa.

Hình minh họa.

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ông thức VLOOKUP ký tự đại diện trong Excel

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

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

Kết quả.

Kết quả.

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

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

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

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

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

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

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

Giống như trên. Bộ lọc tự động của Excel không có vấn đề gì khi lọc ra các số có chứa “4”.

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

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

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

Sử dụng các ký tự đại diện với các số (cho dù 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 dạng 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 chứa

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.

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

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

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

Kết quả.

Kết quả.

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 chúng thành 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ế, 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ể. Sau đây mình sẽ có 2 ví dụ minh họa bên dưới để các 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 chức năng:
  • SUMPRODUCT, ISNUMBER, SEARCH: Đây là các lệnh chức năng.
  • A3: A7: Là vùng dữ liệu tìm kiếm.

-: Là dấu toán tử để chuyển TRUE, FALSE lần lượt thành 1 và 0.

Công thức ký tự đại diện trong Excel cho số

Công thức ký tự đại diện trong Excel cho số

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

Kết quả.

Kết quả.

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 đối với các số nhưng sẽ không thành công đối với 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 xử lý các số đó chứ không phải ngày tháng được hiển thị trong ô.

Để vượt qua trở ngại này, hãy sử dụng hàm TEXT để chuyển ngày thành chuỗi văn bản. Sau đó, nạp các chuỗi vào hàm TÌM KIẾM. Tôi sẽ nói 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 chức năng:
  • SUMPRODUCT, ISNUMBER, SEARCH, TEXT: Đây là các lệnh chức năng.
  • C3: C7: Là vùng dữ liệu tìm kiếm.
  • mmddyyyy: Đếm ngày và tháng trong vùng dữ liệu.

-: Là dấu toán tử để chuyển TRUE, FALSE lần lượt thành 1 và 0.

Công thức ký tự đại diện cho ngày tháng

Công thức ký tự đại diện cho ngày tháng

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

Nhấn phím Enter để hiển thị kết quả.

Nhấn phím Enter để hiển thị kết quả. 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 ích cho các bạn thực hiện trong công việc cũng như học tập và nếu 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 nhé.

Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://toshiba-copier.vn để biết thêm các thông tin chi tiết hơn nhé.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *